Shulou(Shulou.com)06/01 Report--

This article is to share with you about the role of explain in mysql, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The purpose of mysql explain is to:

In our daily work, we sometimes slow down the query to record some SQL statements that have been executed for a long time, and finding these SQL statements does not mean we are done. Sometimes we often use the explain command to check the execution plan of these SQL statements, to see if the SQL statement has been indexed, and whether it has done a full table scan, which can be viewed through the explain command. So we take a closer look at MySQL's cost-based optimizer and get a lot of details about access policies that might be considered by the optimizer and which policies are expected to be adopted by the optimizer when running SQL statements.

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 | 1 | NULL | + -+

(1) id column:

(1) the same execution order of id: 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 | 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 sequence number will increase by itself, and the higher the id value, the higher the priority, and the first 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 | 1 | Using where | +-+-- | -+ (3), Id is the same and different At the same time, there are mysql > EXPLAIN-> select * from (select * from tb_order tb1 where tb1.id = 1) s1jiggle tbaked user tb2 where s1.tb_user_id = tb2.id +-+ | id | select_type | table | type | possible_keys | key | | key_len | ref | rows | Extra | +-- + | 1 | PRIMARY | | System | NULL | 1 | NULL | 1 | PRIMARY | tb2 | const | PRIMARY | 4 | const | 1 | NULL | 2 | DERIVED | tb1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | + -+ derived2: table 2 indicates that the derived table tb1 is derived from id=2

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

1. SIMPLE: simple select query. Subquery or UNION is not included in SQL.

2. PRIMARY: the query contains complex subqueries, and the outermost query is marked as PRIMARY

3. SUBQUERY: subqueries are included in the select or WHERE list

4. DERIVED: subqueries included in the FROM list are marked as DERIVED (derived tables), and MYSQL recursively executes these subqueries to put the result set into the zero-hour table.

5. UNION: if the second SELECT appears after the UNION, the marked bit UNION; if the UNION is included in the subquery of the FROM clause, the outer SELECT will be marked as DERIVED

6. UNION RESULT: the select that gets the result from the UNION table

(3) table column: which table is the row of data about?

(4) type column: access types from good to bad 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 const type, which usually does not appear in the business.

2. Const: the data is found at one time through the index. This type is mainly used to compare primary key or unique indexes, because only one row of data is matched, so it is very fast; if the primary key is placed after the WHERE statement, Mysql can convert the query to a constant.

3. Eq_ref: unique index scan, with only one record in the table matching for each index key. Common in primary key or unique index scans.

4. Ref: a non-unique index scan that returns matching a single worthy of all rows, which is essentially an index access, which returns all rows that match a single value, that is, it may find multiple pieces of qualified data, so it is a mixture of lookup and scanning.

This type means that mysql will quickly find a qualified index according to a specific algorithm, instead of scanning every data in the index, that is, you usually understand that using an index query will retrieve the data more quickly. In order to achieve this kind of search, the index has requirements, in order to achieve this fast search algorithm, the index must meet the specific data structure. To put it simply, the data of the index field must be ordered in order to achieve this type of search and to make use of the index.

5. Range: only rows in a given range are retrieved, using an index to select rows. The key column shows which index is used. Generally, between, between, and between appear in your WHERE statements.

, in, etc., this given range scan is better than a full table scan. Because it only needs to start at one point of the index and end at another point, it doesn't have to scan all the indexes.

6. Index:FUll Index Scan scans through the index tree (index: this type means that mysql scans the entire index. In order to use this type of index, there is no special requirement for this index, as long as it is an index, or part of a composite index, mysql may scan it as an index type. However, the disadvantage is that it is not efficient. Mysql will find the last data one by one from the first data in the index until it finds an index that meets the judgment criteria.

7. ALL: full table scan to get 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 in the fields involved in the query, the index is listed, but not necessarily used by the query.

(6) keys column: the index actually used. If NULL, the index is not used. If an override index is used in the query, it appears only in the key list. Override index: the number of fields after select is the same as the number of fields we indexed.

(7) ken_ Lenn column: indicates the number of bytes used in the index, which can be used to calculate the index length used in the query. Without losing accuracy, the shorter the length, the better. The value displayed by key_len is the maximum possible length of the index field, not the actual length used, that is, the key_len is calculated based on the table definition, not retrieved within the table.

(8) ref column: shows which column of the index is used and, if possible, a constant. Which columns or constants are used to find values on indexed columns.

(IX) rows column (how many rows in each table are queried by the optimizer): based on the statistical information of the table and the selection of indexes, roughly estimate the number of rows that need to be read to find the required records.

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

1. Using filesort (file sorting): mysql cannot read according to the given index order 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 | 1 | Using filesort | +- -+ 1 row in set (0.00 sec) indicates that order_number is the only index column in the table However, order by does not use this index column to sort, so mysql use has to sort with another column. 2. Using temporary:Mysql uses temporary tables to store intermediate results, which are common in sorting order by and grouping query group by. 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 | 1 | Using temporary Using filesort | +-+ 1 row in set (0.00 sec) 3. Using index indicates that the corresponding select operation uses an overlay index It is efficient to avoid accessing the data rows of the table. If Using where appears at the same time, the index is used to perform the lookup of the index key value. If no using where appears at the same time, it indicates that the index is used to read data rather than perform lookup actions. 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 lookup 5, Using join buffer: indicates that the current sql uses connection caching. 6. Impossible where: the where sentence is always false, and mysql cannot get the data row. 7. Select tables optimized away:8, distinct: the above is the role of explain in mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.