Shulou(Shulou.com)05/31 Report--

This article introduces how to understand the parameters and meaning of the MySQL implementation plan. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

This paper is the analysis of the MySQL execution plan, and mainly explains the parameters and meaning of the MySQL execution plan.

Possible_keys

Displays one or more indexes that may be applied to this table

If there is an index on the fields involved in the query, the index will be listed, but it may not be actually used by the query (4 indexes may be created by yourself, at the time of execution

Probably based on internal automatic judgment, only 3 are used).

First create the index CREATE INDEX IDX_EMP_01 ON employee (dep_id); CREATE INDEX IDX_EMP_02 ON employee (dep_id,NAME); view the index mysql > show index from employee +-+ -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +- +- -+ | employee | 0 | PRIMARY | 1 | id | A | 8 | NULL | NULL | | BTREE | YES | NULL | | employee | 1 | IDX_EMP_01 | 1 | dep_id | A | 4 | NULL | NULL | YES | BTREE | YES | NULL | | employee | 1 | IDX_EMP_02 | 1 | dep_id | A | 4 | NULL | NULL | YES | BTREE | NULL | | employee | 1 | IDX_EMP_02 | 2 | name | A | 8 | NULL | NULL | YES | BTREE | YES | NULL | +-+- -+ -+ 4 rows in set (0.06 sec)

You can see that there are three indexes on the table.

Make a query and view the execution plan:

EXPLAIN SELECT IDFROM EMPLOYEEWHERE DEP_ID = 1 AND NAME = 'Luban' +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | SIMPLE | EMPLOYEE | NULL | ref | IDX_EMP_01 IDX_EMP_02 | IDX_EMP_02 | 68 | const Const | 1 | 100.00 | Using index | + -+ 1 row in set 1 warning (0.05sec)

There are three indexes on the table, of which index IDX_EMP_01 is a single-column index of dep_id, and IDX_EMP_02 is a composite index of column dep_id and column name

Indexes may be used when querying, and because there is a filter condition dep_id=1, both indexes will be considered because the first column of the index is dep_id

At this time, the possiable_keys is IDX_EMP_01 and IDX_EMP_02.

Key

The index actually used, if NULL, no index is used

If an override index is used in a query, it appears only in the key list.

The relationship between possible_keys and key: possiable_keys indicates which indexes should be used in theory, and key indicates which indexes are actually used.

It's the same as above:

EXPLAIN SELECT IDFROM EMPLOYEEWHERE DEP_ID = 1 AND NAME = 'Luban' +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | SIMPLE | EMPLOYEE | NULL | ref | IDX_EMP_01 IDX_EMP_02 | IDX_EMP_02 | 68 | const Const | 1 | 100.00 | Using index | + -+ 1 row in set 1 warning (0.05sec)

Two indexes are shown in possiable_keys, but not both indexes will be used, and it is up to the optimizer to decide which one to use.

Through key, we can find that the index IDX_EMP_02 is used, because there are both dep_id and name conditions in where.

Therefore, the use of this index is the most reasonable and efficient.

Through key, you can find the use of the real index of the table.

Key_len

Represents the number of bytes used in the index, which can be used to calculate the index length used in the query.

EXPLAIN SELECT ID FROM EMPLOYEE WHERE DEP_ID = 1 +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | EMPLOYEE | NULL | ref | IDX_EMP_01 IDX_EMP_02 | IDX_EMP_01 | 5 | const | 3 | 100.00 | Using index | + -+ 1 row in set 1 warning (0.00 sec)

You can see that when only the first column of the index is used, the length is 5 bytes.

EXPLAIN SELECT IDFROM EMPLOYEEWHERE DEP_ID = 1 AND NAME = 'Luban' +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | SIMPLE | EMPLOYEE | NULL | ref | IDX_EMP_01 IDX_EMP_02 | IDX_EMP_02 | 68 | const Const | 1 | 100.00 | Using index | + -+ 1 row in set 1 warning (0.05sec)

When the entire index is used, the size of the index is shown, and key_len has changed from 5 to 68, indicating that the number of bytes occupied by the name field in the index is 63.

Also, the value displayed by key_len is the specific size of the index in where, excluding the index used by order by or group by.

Ref

Whether the index is introduced and which indexes are referenced.

The ref column is used to show which column or constant to use with key to select the appropriate row from the table. It displays the name of the column (or const), which is null most of the time.

EXPLAIN SELECT D.ID, D.ADDRESSFROM DEPARTMENT D, EMPLOYEE EWHERE D.ID = E.DEP_ID +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | D | NULL | ALL | PRIMARY | NULL | 5 | | 100.00 | NULL | | 1 | SIMPLE | E | NULL | ref | IDX_EMP_01 | IDX_EMP_02 | IDX_EMP_01 | 5 | demo.D.id | 2 | 100.00 | Using index | + -+ 2 rows in set 1 warning (0.01 sec)

Ref is used to indicate whether the index is used and which index is used. As can be seen from the execution plan, when table d is scanned, no index is used, and type is all.

Is a full table scan, so the ref value is null. When scanning table e, type is ref, indicating that the index is used, and you can see through key that the index is actually used

So the ref column needs to be marked, the index is referenced, and the index is referenced to the id field of the d table.

Rows

The rows column shows the number of rows that the mysql parser believes must be scanned to perform this SQL. This value is an estimated value, not a specific value, and is usually smaller than the actual value.

Before indexing:

EXPLAIN SELECT D.ID, E.DEP_IDFROM DEPARTMENT D, EMPLOYEE EWHERE D.ID = E.DEP_ID +- -+- -+ | 1 | SIMPLE | D | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using index | 1 | SIMPLE | E | NULL | ALL | NULL | 8 | 12.50 | Using where Using join buffer (Block Nested Loop) | +- -+ 2 rows in set 1 warning (0.00 sec)

Through the execution plan, we can see that table d is scanned by full index scan, the whole index needs to be scanned, and table e is scanned by full table scan.

Therefore, the entire dataset needs to be scanned. The d table has 5 rows and the e table has 8 rows, so it is shown as 5 and 8 in the rows.

Create an index on the dep_ id column of table e:

Create index idx_employee_01 on employee (dep_id) Query again: +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | D | NULL | index | PRIMARY | PRIMARY | 4 | NULL | | 5 | 100.00 | Using index | | 1 | SIMPLE | E | NULL | ref | idx_employee_01 | idx_employee_01 | 5 | demo.D.id | 2 | 100.00 | Using index | +-- +-| -+ 2 rows in set 1 warning (0.00 sec)

Through the execution plan, we can see that table d is scanned by full index scan, and the whole index needs to be scanned; table e is scanned by ref non-unique index.

Only part of the data in the index needs to be scanned. Table d has 5 rows of data, so rows is 5. For e-table, scan data that meets the criteria, rows is 2.

Data in Table d: +-+ | id | deptName | address | +-+ | 1 | Research and Development Department (RD) | level 2 | | 2 | personnel Department (HR) | level 3 | 3 | Marketing Department (MK) | | Tier 4 | | 4 | MIS | Tier 5 | | 5 | Finance Department (FD) | Tier 6 | +-+ 5 rows in set (0.07 sec) Table e data: +-+-- | +-+-+ | id | name | dep_id | age | salary | cus_id | +-- +-+ | 1 | Luban | 1 | 10 | 1000.00 | 1 | 2 | Descendants | | 1 | 20 | 2000.00 | 1 | 3 | Sun Shangxiang | 1 | 20 | 2500.00 | 1 | 4 | Kai | 4 | 20 | 3000.00 | 1 | 5 | Dian Wei | 4 | 40 | 3500.00 | 2 | 6 | Diao Chan | 6 | 20 | 5000.00 | 1 | 7 | Sun Bin | 6 | 50 | 5000.00 | 1 | 8 | Cai Wenji | 30 | 35 | 4000.00 | 1 | +-+ 8 rows in set (0.00 sec) dep_id query for id of table d and table e: +-+-- -DEP_ID | 1 | 1 | 1 | 1 | 1 | 1 | 4 | 4 | 4 | 4 | +-+-+ 5 rows in set (0.00 sec)

Ref is to scan the index of the transmitted data. The id of table d has five rows, and there are multiple dep_ id values of e. Only 1 and 4 meet the conditions when matching equivalents.

For the advanced operation of table d, scan these 5 rows of data first, and then pass 1 and 4 to e-table, so for e-table, only 1 and 4 need to be indexed, and only two rows need to be scanned.

Filtered

The proportion of the number of records that satisfy the query, note that it is the percentage, not the specific number of records; the higher the value, the better. The value of the filtered column depends on statistics and is not very accurate.

When scanning the entire table:

EXPLAIN SELECT * FROM EMPLOYEE +-+ | id | select _ type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | EMPLOYEE | NULL | ALL | NULL | 8 | 100.00 | NULL | + -+ 1 row in set 1 warning (0.00 sec)

When a full table scan is performed, all the data will be scanned, with a filtered of 100. 0.

When making a range query:

EXPLAINSELECT * FROM EMPLOYEE WHERE DEP_ID > 4 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+-+ -+ | 1 | SIMPLE | EMPLOYEE | NULL | ALL | NULL | 8 | 33.33 | Using where | +- +-+-+ 1 row in set 1 warning (0.00 sec)

Query the data whose dep_id is greater than 4, which is also a full table scan, and return 3 rows of data. The optimizer estimates that 1/3 of the data is returned, marked as 33.33.

Once again, make a query:

EXPLAINSELECT * FROM EMPLOYEE WHERE DEP_ID > 1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+-+ -+ | 1 | SIMPLE | EMPLOYEE | NULL | ALL | NULL | 8 | 33.33 | Using where | +- +-+-+ 1 row in set 1 warning (0.00 sec)

Query the data whose dep_id is greater than 1, which is also a full table scan, and the returned data is 5 rows, but the filterd is still determined to be 33.33.

On how to understand the various parameters and meaning of the MySQL implementation plan is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.