MySQL Index Optimization Explained

In daily work, we sometimes run slow queries to record some SQL statements that take a long time to execute. Finding these SQL statements does not mean that the job is done. Sometimes we often use the explain command to view the execution plan of these SQL statements to see whether the SQL statement uses an index and whether a full table scan is performed. This can be viewed through the explain command. So we have a deep understanding of MySQL's cost-based optimizer, and we can also get a lot of details about the access strategies that may be considered by the optimizer, and which strategy is expected to be adopted by the optimizer when running SQL statements.

-- Actual SQL, find employees whose username is Jefabc select * from emp where name = 'Jefabc';
-- Check whether SQL uses indexes. Just add explain in front of it. select * from emp where name = 'Jefabc'; 

The information from expain has 10 columns, namely id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

Summary Description:
id: Select an identifier
select_type: indicates the type of query.
table: table of the output result set
partitions: matching partitions
type: indicates the connection type of the table
possible_keys: indicates the possible indexes to be used when querying
key: indicates the index actually used
key_len: length of the index field
ref: comparison of columns and indexes
rows: the number of rows scanned (estimated number of rows)
filtered: The percentage of rows filtered by table conditions
Extra: Description and explanation of the execution

The following is an explanation of the possible appearance of these fields:

1. ID

SELECT identifier. This is the query sequence number of SELECT

My understanding is that it is the order in which SQL is executed. SQL is executed from large to small.

1. When the id is the same, the execution order is from top to bottom

2. If it is a subquery, the ID number will increase. The larger the ID value, the higher the priority and the earlier it will be executed.

3. If the id is the same, it can be considered as a group and executed from top to bottom; in all groups, the larger the id value, the higher the priority and the earlier it is executed.

-- View employees who work in the R&D department and whose names begin with Jef. Classic query explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = 'R&D Department'; 

2. select_type

Indicates the type of each select clause in the query

(1) SIMPLE (simple SELECT, without using UNION or subqueries, etc.)
(2) PRIMARY (the outermost query in a subquery. If the query contains any complex sub-parts, the outermost select is marked as PRIMARY)
(3) UNION (the second or subsequent SELECT statement in UNION)
(4)DEPENDENT UNION (the second or subsequent SELECT statements in a UNION depend on the outer query)
(5)UNION RESULT (the result of UNION, all subsequent selects starting from the second select in the union statement)
(6) SUBQUERY (the first SELECT in a subquery, the result does not depend on the outer query)
(7)DEPENDENT SUBQUERY (the first SELECT in the subquery, dependent on the outer query)
(8) DERIVED (derived table SELECT, subquery in FROM clause)
(9) UNCACHEABLE SUBQUERY (the result of a subquery cannot be cached and the first row of the outer link must be re-evaluated)

3. Table

Displays the name of the table in the database accessed in this step (shows which table the data in this row is about). Sometimes it is not the real table name, but may be an abbreviation, such as e and d above, or it may be the abbreviation of the result of the execution of the step

Type

The table access method indicates how MySQL finds the required rows in the table, also known as the "access type".

Common types are: ALL, index, range, ref, eq_ref, const, system, NULL (from left to right, performance from bad to good)

ALL: Full Table Scan, MySQL will traverse the entire table to find matching rows

index: Full Index Scan. The difference between index and ALL is that the index type only traverses the index tree.

range: retrieve only the rows in a given range, using an index to select the rows

ref: indicates the join matching condition of the above table, that is, which columns or constants are used to find the value on the index column

eq_ref: Similar to ref, the difference is that the index used is a unique index. For each index key value, there is only one record matching in the table. In simple terms, it is to use the primary key or unique key as the join condition in multi-table joins.

const, system: When MySQL optimizes a part of the query and converts it into a constant, it uses these types of access. If you put the primary key in the where list, MySQL can convert the query into a constant. System is a special case of the const type. When the query table has only one row, use system.

NULL: MySQL decomposes statements during optimization and does not even access tables or indexes during execution. For example, selecting the minimum value from an index column can be done with a single index lookup.

5. possible_keys

Indicates which index MySQL can use to find records in the table. If there is an index on the field involved in the query, the index will be listed, but it may not be used by the query (the index that the query can use, if there is no index, it will display null)

This column is completely independent of the order of the tables shown in the EXPLAIN output. This means that some of the keys in possible_keys cannot actually be used in the order the table is generated.
If the column is NULL, there is no associated index. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns that are suitable for indexing. If so, create an appropriate index and check the query again with EXPLAIN.

6. Key

The key column shows the key (index) that MySQL actually decided to use, which must be included in possible_keys

If no index was chosen, key is NULL. To force MySQL to use or ignore the index on the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query.

7. key_len

Indicates the number of bytes used in the index. This column can be used to calculate the length of the index used in the query (the value displayed by key_len is the maximum possible length of the index field, not the actual length used, that is, key_len is calculated based on the table definition, not retrieved from the table)

The shorter the length, the better without losing accuracy.

8. Ref

Comparison of columns and indexes, indicating the join matching conditions of the above tables, that is, which columns or constants are used to find the values ​​on the index columns

9. rows

Estimate the number of result set rows, which means that MySQL estimates the number of rows needed to find the required records based on table statistics and index selection.

10. Extra

This column contains detailed information about how MySQL solves the query. There are several cases:

Using where: Instead of reading all the information in the table, you can get the required data only through the index. This happens when all the requested columns for the table are part of the same index, which means that the MySQL server will filter after the storage engine retrieves the row.

Using temporary: Indicates that MySQL needs to use a temporary table to store the result set, which is common in sorting and grouping queries, and common group by; order by

Using filesort: When the query contains an order by operation and the sorting operation cannot be completed using the index, it is called "file sorting"

-- Test Extra's filesort
explain select * from emp order by name;

Using join buffer: This value emphasizes that no index is used when obtaining the join condition, and a join buffer is needed to store intermediate results. If this value appears, you should be aware that you may need to add an index to improve performance depending on the specific circumstances of the query.

Impossible where: This value emphasizes that the where statement will result in no qualifying rows (no possible result by collecting statistics).

Select tables optimized away: This value means that the optimizer may return only one row from the aggregate function result by using only the index.

No tables used: The query statement uses from dual or does not contain any from clauses

-- explain select now() from dual;

Summarize:

• EXPLAIN does not tell you about triggers, stored procedures, or how user-defined functions affect the query
• EXPLAIN does not consider various caches
• EXPLAIN does not show the optimization work that MySQL does when executing the query
• Some statistics are estimates and not exact values
• EXPALIN can only explain SELECT operations. Other operations must be rewritten as SELECT and then the execution plan must be viewed.

There is no possible result by collecting statistics

This is the end of this article about MySQL index optimization explain. For more relevant MySQL index optimization explain content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!