Detailed explanation of the role of explain in MySQL

1. MYSQL index

Index: A data structure that helps MySQL retrieve data efficiently. Used to improve search efficiency and can be compared to a dictionary. It can be simply understood as a sorted, fast-search data structure.

The role of the index: to facilitate query and sorting (so adding an index will affect the where statement and order by sorting statement).

In addition to the data, the database also maintains data structures that satisfy specific search algorithms and that reference the data in some way. This allows advanced search algorithms to be implemented on these data structures. These data structures are indexes.

The index itself is also very large and it is impossible to store it all in memory, so the index is often stored on disk in the form of index files.

When we talk about indexes, unless otherwise specified, they are usually B-tree indexes. (Clustered indexes, composite indexes, prefix indexes, and unique indexes are all B+ tree indexes by default). In addition to B tree indexes, there are also hash indexes.

advantage:

A. Improve data retrieval efficiency and reduce database IO costs
B. Sorting data by index column reduces data sorting cost and CPU consumption.

shortcoming:

A. The index is also a table that stores the primary key and index fields and points to the records of the entity table, so the index also takes up space.
B. When performing INSERT, UPDATE, or DELETE operations on a table, MYSQL not only updates the data, but also saves the corresponding information of the index column fields added to the index file each time it is updated.

In the actual production environment, we need to analyze step by step, optimize and establish the best index, and optimize our query conditions.

Index classification:

1. Single-value index: An index contains only one field, and a table can have multiple single-column indexes.
2. The value of the unique index column must be unique, but null values ​​are allowed.
3. Composite index: an index contains multiple columns

It is recommended to create no more than 5 indexes for a table.

grammar:

1. CREATE [UNIQUE] INDEX indexName ON myTable (columnName(length));
2. ALTER myTable Add [UNIQUE] INDEX [indexName] ON (columnName(length));

Delete: DROP INDEX [indexName] ON myTable;

View: SHOW INDEX FROM table_name\G;

2. The role of EXPLAIN

EXPLAIN: Simulate how the MySQL optimizer executes SQL query statements, so that you can know how MySQL processes your SQL statements. Analyze the performance bottleneck of your query statement or table structure.

mysql> explain select * from tb_user;
+----+-------------+---------+------+---------------+------+---------+------+------+------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+------+
| 1 | SIMPLE | tb_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+------+

(a) id column:

(1) The execution order of the same id is from top to bottom

mysql> explain 
  -> SELECT * FROM tb_order tb1
  -> LEFT JOIN tb_product tb2 ON tb1.tb_product_id = tb2.id
  -> LEFT JOIN tb_user tb3 ON tb1.tb_user_id = tb3.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| 1 | SIMPLE | tb1 | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
| 1 | SIMPLE | tb2 | eq_ref | PRIMARY | PRIMARY | 4 | product.tb1.tb_product_id | 1 | NULL |
| 1 | SIMPLE | tb3 | eq_ref | PRIMARY | PRIMARY | 4 | product.tb1.tb_user_id | 1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+

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

mysql> EXPLAIN
  -> select * from tb_product tb1 where tb1.id = (select tb_product_id from tb_order tb2 where id = tb2.id =1);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | tb1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | SUBQUERY | tb2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

(3) The ids are the same and different, and exist at the same time

mysql> EXPLAIN 
  -> select * from(select * from tb_order tb1 where tb1.id =1) s1,tb_user tb2 where s1.tb_user_id = tb2.id;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 1 | PRIMARY | tb2 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | DERIVED | tb1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

Derived2: Derived table 2 means that the derived table is table tb1 with id=2

(ii) select_type column: the operation type of the data read operation

1. SIMPLE: Simple select query, SQL does not contain subqueries or UNION.
2. PRIMARY: The query contains complex subqueries, and the outermost query is marked as PRIMARY
3. SUBQUERY: The subquery is included in the select or WHERE list. 4. DERIVED: The subquery included in the FROM list will be marked as DERIVED (derived table). MYSQL will recursively execute these subqueries and put the result set into the temporary table.
5. UNION: If the second SELECT appears after UNION, it is marked as UNION; if UNION is included in a subquery in the FROM clause, the outer SELECT will be marked as DERIVED
6. UNION RESULT: select to get results from the UNION table

(III) Table column: Which table does the row of data refer to?

4. Type column: access type, from best to worst: system > const > eq_ref > ref > range > index > ALL

1. system: The table has only one record (equal to the system table). This is a special case of the const type and does not appear in normal business.
2. const: Find data once through the index. This type is mainly used to compare primary keys or unique indexes. Because it only matches one row of data, it is very fast. If the primary key is placed after the WHERE statement, Mysql can convert the query into a constant.
3. eq_ref: Unique index scan, for each index key, there is only one record in the table that matches it. Common in primary key or unique index scans.
4. ref: Non-unique index scan, returns all rows that match a single value. It is essentially an index access that returns all rows that match a single value, which means it may find multiple qualified data, so it is a mixture of search and scan.
5. range: retrieve only rows in a given range, using an index to select rows. The key column shows which index was used. Generally, queries such as between, <, >, and in appear in your WHERE statement. This given range scan is better than a full table scan. Because he only needs to start at one point in the index and end at another point, there is no need to scan the entire index.
6. Index: FULL Index Scan scans and traverses the index tree (scans the index of the entire table and obtains data from the index).
7. ALL full table scan obtains millions of data from disk. ALL type data is optimized as much as possible.

(V) possible_keys column: displays one or more indexes that may be applied to this table. If an index exists for a field involved in the query, the index will be listed, but it may not be actually used by the query.

(6) keys column: the index actually used. If NULL, no index is used. If a covering index is used in a query, the index only appears in the key list. Covering index: The fields after select are consistent with the number of fields we index.

(VII) ken_len column: indicates the number of bytes used in the index. This column can be used to calculate the index length used in the query. The shorter the length, the better without losing accuracy. 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.

(8) ref column: shows which column of the index is used, if possible, a constant. Which columns or constants are used to look up values ​​in the index column.

9. The rows column (how many rows of each table are queried by the optimizer): Based on the table statistics and index selection, it roughly estimates the number of rows that need to be read to find the required records.

(10) Extra column: extended attributes, but also very important information.

1. Using filesort: MySQL cannot read in the order of the given indexes in the table.

 mysql> explain select order_number from tb_order order by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | tb_order | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Note: order_number is a unique index column in the table, but order by does not use this index column for sorting, so MySQL has to use another column for sorting.

2. Using temporary: MySQL uses temporary tables to save intermediate results, which is commonly used in order by and group by queries.

mysql> explain select order_number from tb_order group by order_money;
+----+-------------+----------+------+---------------+------+------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+------+------+------+---------------------------------+
| 1 | SIMPLE | tb_order | ALL | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+------+------+------+---------------------------------+
1 row in set (0.00 sec)

3. Using index means that the corresponding select operation uses a covering index, which avoids accessing the data rows of the table and has good efficiency.

If Using where appears at the same time, it indicates that the index is used to perform the search of the index key value.

If using where is not present, the index is used to read data rather than perform a search.

mysql> explain select order_number from tb_order group by order_number;
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| 1 | SIMPLE | tb_order | index | index_order_number | index_order_number | 99 | NULL | 1 | Using index |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

4. Using where to find

5. Using join buffer: Indicates that the current SQL uses the connection cache.

6. Impossible where: The where clause is always false, and MySQL cannot retrieve the data row.

7. select tables optimized away:

8. distinct:

Summarize

The above is all the content of this article about the detailed explanation of the role of explain in Mysql. I hope it will be helpful to everyone. Interested friends can refer to: MYSQL subquery and nested query optimization example analysis, several important MySQL variables, ORACLE SQL statement optimization technical points analysis, etc. If there are any deficiencies, please leave a message and the editor will reply to you and make corrections in time. Thank you friends for supporting this site!