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

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

XIII. Extra

The resulting value

There are six situations:

Using filesort 、 Using temporary 、 use index 、 using where 、 using join buffer 、 impossible where

1 、 Using filesort

Indicates that mysql uses an external index to sort the data, rather than the index order within the table

The inability to complete a sort operation with an index in Mysql is called "file sorting".

EXPLAIN SELECT * FROM EMPLOYEE ORDER BY SALARY +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | EMPLOYEE | NULL | ALL | NULL | 8 | 100.00 | Using filesort | +- -+ 1 row in set 1 warning (0.02 sec)

Sort by the salary field, and there is no index on the salary field, then the file sort is used, and the extra is using filesort.

2 、 Using temporary

Temporary tables are used to save intermediate results, and Mysql uses temporary tables when sorting query results, which are common in sorting orderby and grouping query group by.

View the data distribution of dep_id:

EXPLAIN SELECT DEP_ID, COUNT (*) FROM EMPLOYEEGROUP BY DEP_ID +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | + -+ | 1 | SIMPLE | EMPLOYEE | NULL | ALL | NULL | 8 | 100.00 | Using temporary | +- -+ 1 row in set 1 warning (0.01 sec)

The dep_id is group by, and the intermediate results need to be saved at this time, so a temporary table is used and the extra is using temporary.

3 、 use index

Indicates that the overlay index is used in the corresponding select to avoid accessing the data rows of the table, which is very efficient.

If using where appears at the same time, the index is used to look up the key value of the index.

If using where does not appear at the same time, the index is used to read data rather than perform lookup actions.

Find data in the ascending order of wages (with index):

EXPLAIN SELECT SALARY FROM EMPLOYEE ORDER BY SALARY +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | EMPLOYEE | NULL | ALL | NULL | 8 | 100.00 | Using filesort | +- -+ 1 row in set 1 warning (0.00 sec)

Use salary for sorting, and there is an index, so the data can be found through the index of the column. The index is only used to read the data, and sorting is avoided.

The extra displays as using index.

4 、 using where

Indicates that where filtering is used (the filter field does not have an index or cannot use an index).

The Dep_id field does not have an index.

EXPLAIN SELECT * 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 | 12.50 | Using where | +- +-+-+ 1 row in set 1 warning (0.00 sec)

When where is used for filtering, using where is used in extra.

Dep_id creates an index:

CREATE INDEX idx_emp_02 ON employee (dep_id); when the index is available: EXPLAIN SELECT * 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 | range | idx_emp_02 | idx_emp_02 | 5 | NULL | 5 | 100.00 | Using index condition | +- -+-+ 1 row in set 1 warning (0.00 sec)

Accessed through the index, marked as Using index condition.

When an index is not available:

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

Mark as Using where

5 、 Using index condition

When the index of the where condition is used, it is marked as Using index condition.

EXPLAIN SELECT * 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 | range | idx_emp_02 | idx_emp_02 | 5 | NULL | 5 | 100.00 | Using index condition | +- -+-+ 1 row in set 1 warning (0.00 sec)

Accessed through the index, marked as Using index condition.

6 、 impossible where

The where condition causes no rows to be returned.

EXPLAIN SELECT * FROM EMPLOYEE WHERE ID IS NULL +- -partitions | possible_keys | key | ref | rows | filtered | Extra | +-+ -+ | 1 | SIMPLE | NULL | Impossible WHERE | +- +-+-+ 1 row in set 1 warning (0.00 sec)

Id is the primary key, so there are no null values, and when you make a query with empty id, there is no result, so extra is represented as impossible where.

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.