one table
Which table is this query based on.
two type
Type is the access type of query and an important indicator
The result values from the best to the worst are: system > const > Eq_ ref > ref > fulltext > ref_ or_ null > index_ merge > unique_ subquery > index_ subquery > range > index> ALL
Common:
system > const > eq_ref > ref >
range > index> ALL
Generally speaking, it is necessary to ensure that the query reaches at least the range level, preferably ref
1 system
The table has only one row of records (equal to the system table). This is a special case of const type. It usually does not appear, and this can be ignored.
mysql> explain select * from (select * from t1 where t1.id=1) s;
2 const
It means that it can be found once through the index. const is used to compare the primary key or unique index. Because only one row of data is matched, it is very fast.
If the primary key is placed in the where list, MySQL can convert the query into a constant.
mysql> explain select * from (select * from t1 where t1.id=1) s;
2 in derive2 representative Id = 2, which is the query result of id = 2.
3 eq_ref
Unique index scan. For each index key, only one record in the table matches it. Common in primary key or unique index scans.
Similar to checking the CEO of a company, there is only one.
mysql> explain select * from t1,t2 where t1.id = t2.id; +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db01.t1.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)
4 ref
Non unique index scanning returns all rows matching an individual value. It is also an index access in essence. It returns all rows matching an individual value. However, it may find multiple qualified rows, so it should belong to a mixture of search and scanning.
Similar to looking up a company's programmers, programmers have a pile.
Before unused index:
mysql> explain select * from t1,t2 where t1.content = t2.content; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
After indexing:
mysql> CREATE index idx_ctnt on t2(content); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from t1,t2 where t1.content = t2.content; +----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 1 | SIMPLE | t2 | NULL | ref | idx_ctnt | idx_ctnt | 303 | db01.t1.content | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+-----------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
5 range
Retrieve only rows in a given range, using an index to select rows.
The key column shows which index is used.
Generally, queries such as between, <, >, in, etc. appear in your where statement.
This range scan index scan is better than full table scan because it only needs to start at one point of the index and end at another point without scanning all the indexes.
mysql> explain select * from t1 where t1.id<10; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from t1 where t1.id in (10,20); +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
6 index
index And The difference between all is index Type only traverses the index tree. This is usually better than ALL Fast because index files are usually smaller than data. That is, although ALL and Index All read the whole table, but index Is read from the index, and all Is read from disk.
mysql> explain select * from t1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select id from t1; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
7 all
A full table scan traverses the entire table to find matching rows.
mysql> explain select * from t1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
8 index_merge
Multiple indexes need to be combined in the query process, which usually appears in sql with or keyword.
mysql> explain select * from t2 where t2.content is null or t2.id =10;
9 ref_or_null
When both association conditions and null values are required for a field. The query optimizer will choose to use ref_or_null connection query.
mysql> explain select * from t2 where t2.content is null or t2.content='abc'; +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | t2 | NULL | ref_or_null | idx_ctnt | idx_ctnt | 303 | const | 2 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
10 index_subquery
The index is used to associate sub queries without full table scanning.
explain select * from t2 where t2.content in (select t3.content from t3);
CREATE index idx_ctnt on t3(content); explain select * from t2 where t2.content in (select t3.content from t3);
11 unique_subquery
The join type is similar to index_subquery. Unique indexes are used in subqueries.
mysql> explain select * from t2 where t2.id in (select t3.id from t3);
Note: Generally speaking, it is necessary to ensure that the query reaches at least range level, preferably ref.
three possible_keys
Displays one or more indexes that may be applied to this table. If there is an index on the field involved in the query, the index will be listed, but it may not be actually used by the query.
mysql> explain select * from t2 where t2.content is null or t2.content='abc'; +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | t2 | NULL | ref_or_null | idx_ctnt | idx_ctnt | 303 | const | 2 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
four key
The index actually used. If NULL, no index is used.
mysql> explain select * from t2 where t2.content is null or t2.content='abc'; +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | t2 | NULL | ref_or_null | idx_ctnt | idx_ctnt | 303 | const | 2 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)