Core content of MySQL execution plan
As we all know, mysql performs query optimization when executing queries. To put it simply, when executing, first generate an execution plan based on cost and rule optimization, and then execute the query according to the execution plan. This article mainly introduces the meaning of each output item of EXPLAIN, so as to help you better optimize sql performance!
Case table design
-- User table create table t_user ( id int primary key, login_name varchar(100), name varchar(100), age int, sex char(1), department_id int, address varchar(100) ); -- Department table create table t_department ( id int primary key, name varchar(100) ); -- Address table create table t_address ( id int primary key, addr varchar(100) ); -- Create normal index alter table t_user add index idx_dep(department_id); -- Create unique index alter table t_user add unique index idx_login_name(login_name); alter table t_user add index idx_name_age_sex(name, age, sex); -- Create composite index alter table t_address add fulltext ft_address(addr);-- Create full-text index
Detailed explanation of implementation plan
We can add the EXPLAIN keyword in front of the query statement to view the execution plan of the query. for example
mysql> EXPLAIN SELECT 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+1 row in set, 1 warning (0.01 sec)
As you can see, the execution plan contains many output columns. Let's briefly explain the general functions of each column, and then explain it in detail.
| Listing | describe |
|---|---|
| id | In a large query statement, each SELECT keyword corresponds to a unique id |
| select_type | The type of the query corresponding to the SELECT keyword |
| table | Table name |
| partitions | Matching partition information |
| type | Access method for single table |
| possible_keys | Possible indexes |
| key | Index actually used |
| key_len | Index length actually used |
| ref | When the index column equivalence query is used, the object information for equivalence matching with the index column |
| rows | Estimated number of records to be read |
| filtered | Percentage of remaining records in a table filtered by search criteria |
| Extra | Some additional information |
Column explanation
id
Each SELECT statement is automatically assigned a unique identifier
Indicates the order of operation tables in the query. There are three cases:
The same id: the execution order is from top to bottom, and the id is different: if it is a sub query, the id number will increase automatically. The larger the id, the higher the priority. The same id and different id exist at the same time
If the id column is null, it means that this is a result set and does not need to be used for query.
select_ Type (important)
Query type, which is mainly used to distinguish common query, union query (Union, union all), sub query and other complex queries.
simple
Represents a simple select query that does not require a union operation or does not contain subqueries. When there is a connection query, the outer query is simple and there is only one
mysql> explain select * from t_user; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
primary
A select that requires union operation or contains sub queries, and the select of the outermost company query_ Type is primary. And only one
mysql> explain select (select name from t_user) from t_user ; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | t_user | index | NULL | idx_dep | 5 | NULL | 1 | Using index | | 2 | SUBQUERY | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 2 rows in set (0.00 sec)
subquery
Except for the subqueries contained in the from clause, subqueries may appear elsewhere
mysql> explain select * from t_user where id = (select max(id) from t_user); +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | | 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 2 rows in set (0.01 sec)
dependent subquery
Similar to dependent union, it means that the query of this subquery is affected by the query of external tables
mysql> explain select id,name,(select name from t_department a where a.id=b.department_id) from t_user b; +----+--------------------+-------+--------+---------------+---------+---------+------------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+--------+---------------+---------+---------+------------------------+------+-------+ | 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4 | sample.b.department_id | 1 | NULL | +----+--------------------+-------+--------+---------------+---------+---------+------------------------+------+-------+
union
The first query is PRIMARY. In addition to the first table, the second subsequent table is select_ All types are union
mysql> explain select * from t_user where sex='1' union select * from t_user
-> where sex='2';
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| 1 | PRIMARY | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 2 | UNION | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
dependent union
Like Union, it appears in union or union all statements, but this query is affected by external queries
mysql> explain select * from t_user where sex in (select sex from t_user where sex='1' union select sex from t_user where sex='2'); +----+--------------------+------------+------+---------------+------+---------+------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------+------+---------------+------+---------+------+------+-----------------+ | 1 | PRIMARY | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | | 2 | DEPENDENT SUBQUERY | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | | 3 | DEPENDENT UNION | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | | NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------+---------------+------+---------+------+------+-----------------+
union result
The result set containing Union. In Union and union all statements, the id field is null because it does not need to participate in the query
derived
The subquery appearing in the from clause is also called a derived table. In other databases, it may be called an inline view or nested select
mysql> explain select * from (select * from t_user where sex='1') b; +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | NULL | | 2 | DERIVED | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
table
- The name of the query table displayed. If the query uses an alias, the alias is displayed here
- If the operation on the data table is not involved, this is displayed as null
- If it is displayed in angle brackets, it means that this is a temporary table, and the N in the back is the id in the execution plan, indicating that the result comes from this query.
If the < union M,N > enclosed in angle brackets is similar to, it is also a temporary table, indicating that the result comes from the result set with id M,N of the union query.
type summary
From good to bad:
system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery， index_subquery，range，index_merge，index，ALL
Indexes can be used for all types except all_ Except for merge, other types can only use one index.
The optimizer selects the best index
matters needing attention:
The index should use at least the range level.
Type column details
system
There is only one row of records in the table (equal to the system table). This is a special column of const type. It usually does not appear and can be ignored
mysql> explain select * from (select * from t_user where id=1) a; +----+-------------+------------+--------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------+---------+------+------+--------------------------------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 0 | const row not found | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +----+-------------+------------+--------+---------------+------+---------+------+------+--------------------------------+
Const (important)
When the unique index or primary key is used, and the return record must be the equivalent where condition of 1-line record, the type is usually const. Other databases
Also called unique index scan
mysql> explain select * from t_user where id=1; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
eq_ Ref (important)
Keyword: primary key or unique index of connection field.
This type usually appears in the join query of multiple tables, which means that each result of the previous table can only match one row of results of the subsequent table And the comparison operation of query is usually '=', which has high query efficiency
mysql> explain select a.id from t_user a left join t_department b on a.department_id=b.id; +----+-------------+-------+--------+---------------+---------+---------+------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+------------------------+------+-------------+ | 1 | SIMPLE | a | index | NULL | idx_dep | 5 | NULL | 1 | Using index | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | sample.a.department_id | 1 | Using index | +----+-------------+-------+--------+---------------+---------+---------+------------------------+------+-------------+
select * from a,b where a.id=b.id (equivalent connection)
select * from a where name = 'zs' (conditional query)
Ref (important)
For non unique indexes, use equivalent (=) to query non primary keys. Or a query that uses the leftmost prefix rule index.
-- Non unique index mysql> explain select * from t_user where department_id=1; +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | t_user | ref | idx_dep | idx_dep | 5 | const | 1 | NULL | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+ -- Equivalent non primary key connection mysql> explain select a.id from t_user a left join t_department b on a.name=b.name; +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 1 | NULL | | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+ -- leftmost prefixing mysql> explain select * from t_user where name = 'kojon'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
fulltext
For full-text index retrieval, it should be noted that full-text index has high priority. If full-text index and ordinary index exist at the same time, mysql gives priority to using full-text index regardless of the cost
# fulltext
mysql> explain select * from t_address where match(addr) against('beijing');
+----+-------------+-----------+----------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+----------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | t_address | fulltext | ft_address | ft_address | 0 | NULL | 1 | Using where |
+----+-------------+-----------+----------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)
ref_or_null
Similar to the ref method, only the comparison of null values is added. Not much is actually used.
unique_subquery
Used for in subquery in where, which returns unique values without duplicate values
index_subquery
Used for subqueries in the form of in. Auxiliary indexes or in constant lists are used. Subqueries may return duplicate values. You can use indexes to de duplicate subqueries.
Range (important)
Index range scanning is commonly used in queries using >, <, is, null, between, in, like and other operators.
mysql> # Range (important) mysql> explain select * from t_user where id>1; +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.01 sec) mysql> -- like Prefix index mysql> explain select * from t_user where name like '%x'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> -- like suffix index mysql> explain select * from t_user where login_name like 'x%'; +----+-------------+--------+-------+----------------+----------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+----------------+----------------+---------+------+------+-----------------------+ | 1 | SIMPLE | t_user | range | idx_login_name | idx_login_name | 403 | NULL | 1 | Using index condition | +----+-------------+--------+-------+----------------+----------------+---------+------+------+-----------------------+ 1 row in set (0.01 sec)
index_merge
Indicates that the query uses more than two indexes, and finally takes the intersection or union. The common conditions of and and and or use different indexes. The official sorting is in Ref_ or_ After null, but in fact, the performance may not be as good as range most of the time due to the need to read the indexes
Index (important)
Keyword: the condition is the name of the node that appears in the index tree. There may not be an exact match index.
Index full table scanning, which scans the index from beginning to end. It is common to use index columns to process queries that do not need to read data files, and queries that can use index sorting or grouping.
mysql> -- Index sheet mysql> explain select login_name from t_user; +----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | index | NULL | idx_login_name | 403 | NULL | 1 | Using index | +----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+ mysql> -- Composite index mysql> explain select age from t_user; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ mysql> #The ALL type needs to be changed to index by overwriting the index mysql> explain select login_name,age from t_user; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+
All (important)
This is the full table scan data file, and then filter it at the server layer to return qualified records.
mysql> # All (important) mysql> explain select * from t_user; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+
Back to table query
Thinking: how to use indexes?
possible_keys
One or more possible indexes for this query
key
Query the index actually used, select_type is index_ When merging, there may be more than two indexes and other select_ Only one type will appear here.
key_len
- The index length used to process the query. If it is a single column index, the entire index length is included. If it is a multi column index, the query
Queries may not always be able to use all columns. The specific index of how many columns are used will be calculated here, and those not used will be included
Column, it won't be counted here. - Pay attention to the value of this column and calculate the total length of your multi column index to know whether all columns are used.
- In addition, key_len only calculates the index length used for the where condition, and even if the index is used for sorting and grouping, the key will not be calculated_ Len.
Look at the usage of composite index
ref
If the constant equivalence query is used, const will be displayed here
If it is a join query, the execution plan of the driven table will display the associated fields of the driven table
If the condition uses an expression or function, or the condition column has an internal implicit conversion, it may be displayed here as func
rows
Here is the number of scan lines estimated in the execution plan, which is not an accurate value (InnoDB is not an accurate value, MyISAM is an accurate value, mainly because InnoDB uses MVCC concurrency mechanism)
extra (important)
This column contains very important additional information that is not suitable to be displayed in other columns. This column can display many kinds of information, including dozens of commonly used ones
no tables used
Query without from sentence or From dual query
Join queries that use not in() form subqueries or not exists operators are called anti join queries
That is, the general join query is to query the inner table first and then the outer table. The reverse join query is to query the outer table first and then the inner table.
using fi lesort (important)
This occurs when the index cannot be used for sorting. It is common in the order by and group by statements to explain that MySQL will use an external index sort instead of reading according to the index order. The sort operation that cannot be completed by index in MySQL is called "file sort"
mysql> # Using filesort (important) mysql> explain select * from t_user order by name; +----+-------------+--------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort | +----+-------------+--------+------+---------------+------+---------+------+------+----------------+
using index (important)
When querying, you do not need to query back to the table. You can obtain the queried data directly through the index.
- It means that the Covering Index is used in the corresponding SELECT query to avoid accessing the data rows of the table. The efficiency is good!
- If Using Where appears at the same time, it indicates that the index is used to find the key value of the index
- If Using Where does not appear at the same time, it indicates that the index is used to read data rather than perform lookup actions.
mysql> # Full value match overlay index mysql> explain select name,age,sex from t_user ; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+
using temporary
Indicates that a temporary table is used to store intermediate results.
MySQL uses temporary tables when querying the query results order by and group by
Temporary tables can be memory temporary tables and disk temporary tables. They cannot be seen in the execution plan. You need to check the status variable, used_tmp_table，used_tmp_disk_table can be seen.
distinct
The distinct keyword (index field) is used in the select section
mysql> explain select distinct a.id from t_user a,t_department b where a.department_id=b.id; +----+-------------+-------+--------+--------------------------------+---------+---------+------------------------+------+-------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+--------------------------------+---------+---------+------------------------+------+-------------------------------------------+ | 1 | SIMPLE | a | index | PRIMARY,idx_login_name,idx_dep | idx_dep | 5 | NULL | 1 | Using where; Using index; Using temporary | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | sample.a.department_id | 1 | Using index; Distinct | +----+-------------+-------+--------+--------------------------------+---------+---------+------------------------+------+-------------------------------------------+
using where (important)
It means that not all the records returned by the storage engine meet the query conditions and need to be filtered at the server layer.
mysql> -- Query condition has no index mysql> explain select * from t_user where address='beijing'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> -- Index failure mysql> explain select * from t_user where age=1; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> -- Index failure mysql> explain select * from t_user where id in(1,2); +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Query conditions are divided into restriction conditions and check conditions. Before 5.6, the storage engine can only scan and return data according to the restriction conditions, and then the server layer filters according to the check conditions and returns the data that really meets the query. 5.6. After X, the ICP feature is supported, and the inspection conditions can be pushed down to the storage engine layer. The data that does not meet the inspection conditions and restrictions will not be read directly, which greatly reduces the number of records scanned by the storage engine. The extra column displays using index condition
mysql> -- Index push down mysql> explain select * from t_user where name='abc'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
Index failure analysis
1. Full value matching my favorite
mysql> explain select * from t_user where name='kojon' and age=1 and sex='1';
2. Best left prefix combination index
Composite index
The leading index cannot die, and the middle index cannot be broken
If more than one column is indexed, follow the best left prefix rule. It means that the query starts at the top left of the index and does not skip the columns in the index.
Examples of errors:
Lead index dead:
mysql> -- Lead index dead mysql> explain select * from t_user where age=23; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
The intermediate index is broken (the leading index takes effect and other indexes become invalid):
mysql> -- Intermediate index break mysql> explain select * from t_user where name='aa' and sex='1'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
compare
mysql> explain select * from t_user where name='aa' and sex='1' and age=23; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ mysql> explain select * from t_user where name='aa' and sex=1 and age=23; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
3. Do not calculate on the index
mysql> #Do not perform manual / automatic conversion of these function types, which will lead to the invalidation of index calculation mysql> explain select * from t_user where login_name='xh'; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> -- Do not calculate on the index mysql> explain select * from t_user where left(login_name,1)='xh'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
4. The column on the right of the range condition is invalid
mysql> -- The column to the right of the range condition is invalid mysql> explain select * from t_user where name='abc' and age>20 and sex='1'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
5. Try to use overlay index
mysql> -- Use overlay indexes whenever possible(Query only indexed columns)，That is, the index column is consistent with the query column, reducing select * mysql> explain select * from t_user ; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ mysql> -- Overwrite index not used mysql> explain select name,login_name from t_user ; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> -- Use overlay index mysql> explain select name,age,sex from t_user ; +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL | +----+-------------+--------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> -- Use index mysql> explain select login_name from t_user ; +----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | index | NULL | idx_login_name | 403 | NULL | 1 | Using index | +----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+
6. Do not use unequal on index fields
mysql> -- Use equal to on index field mysql> explain select * from t_user where login_name='xh'; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> -- Use not equal to on index field mysql> explain select * from t_user where login_name!='xh'; +----+-------------+--------+------+----------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+----------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | idx_login_name | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+----------------+------+---------+------+------+-------------+
7. null cannot be judged on the primary key index field
mysql> # null cannot be used on a primary key field mysql> explain select * from t_user where name is null; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> # When the is null judgment is used on the index field, the index can be used mysql> explain select * from t_user where login_name is null; +----+-------------+--------+------+----------------+----------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+----------------+----------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | t_user | ref | idx_login_name | idx_login_name | 403 | const | 1 | Using index condition | +----+-------------+--------+------+----------------+----------------+---------+-------+------+-----------------------+ mysql> -- If the primary key is not empty, the index will not be used mysql> explain select * from t_user where id is not null; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
8. The index field uses like and does not start with a wildcard
mysql> -- Not to%start
mysql> explain select * from t_user where login_name like 'x%';
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-----------------------+
| 1 | SIMPLE | t_user | range | idx_login_name | idx_login_name | 403 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
mysql> -- Index field usage like Start with wildcard('%character string')It will lead to index failure and turn to full table scanning
mysql> explain select * from t_user where login_name like '%x';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
The resu lt s show that like ends with wildcards, which is equivalent to range search, and the index will not become invalid. Different from the range conditions (between, <, >, in, etc.), it will not invalidate the index on the right.
9. Single index string field
Implicit transformation problem
The index field is a string, but the query without single quotation marks will lead to index invalidation and turn to full table scanning
mysql> # Index field strings should be enclosed in single quotes mysql> explain select * from t_user where name=123; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
10. Do not use or for index fields
When the index field uses or, the index will become invalid and turn to full table scanning
mysql> # Do not use or for index fields mysql> explain select * from t_user where name='asd' or age=18; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
summary
[optimization summary formula]
Full value matching is my favorite, and the leftmost prefix should be observed;
The leading elder brother cannot die, and the middle brother cannot break;
The index column is less calculated, and the range will become invalid after that;
LIKE percentage is written to the right, and the coverage index does not write stars;
Unequal null values and or, index invalidation should be used less