like 关键字和索引命中
Example
Table Schema
mysql> show create table task2.core_user;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| core_user | CREATE TABLE `core_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
...
PRIMARY KEY (`id`),
UNIQUE KEY `core_user_name_2b52f76e_uniq` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看到，对 id 和 name 都有索引。
type = const - where name = “1”
mysql> EXPLAIN select * from task2.core_user where name = "1";
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | core_user | NULL | const | core_user_name_2b52f76e_uniq | core_user_name_2b52f76e_uniq | 82 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
- key 为
core_user_name_2b52f76e_uniq说明这个索引在这次查询中被用到了 type为const说明使用了唯一索引或者主键索引，而且当前执行结果一定是最多包含1行记录的where查询语句
type = range - where name LIKE “1%”
mysql> EXPLAIN select * from task2.core_user where name LIKE "1%";
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | core_user | NULL | range | core_user_name_2b52f76e_uniq | core_user_name_2b52f76e_uniq | 82 | NULL | 12 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
- key 为
core_user_name_2b52f76e_uniq说明这个索引在这次查询中被用到了 type为range说明使用索引范围扫描索引表
type = ALL - where name LIKE “%1”
mysql> EXPLAIN select * from task2.core_user where name LIKE "%1";
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | core_user | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- key 为 NULL 说明在这次查询中没使用任何索引表
type为ALL说明进行了全表扫描
type = ALL - where name LIKE “%1%”;
mysql> EXPLAIN select * from task2.core_user where name LIKE "%1%";
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | core_user | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
- key 为 NULL 说明在这次查询中没使用任何索引表
type为ALL说明进行了全表扫描
FULLTEXT 索引类型和索引命中
Example
Table Schema
mysql> show create table task2.core_user;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| core_user | CREATE TABLE `core_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
...
`words` text,
PRIMARY KEY (`id`),
UNIQUE KEY `core_user_name_2b52f76e_uniq` (`name`),
FULLTEXT KEY `core_user_words_index` (`words`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
type = fulltext - EXPLAIN select * from task2.core_user where MATCH(words) against(“hello”);
mysql> EXPLAIN select * from task2.core_user where MATCH(words) against("hello");
+----+-------------+-----------+------------+----------+-----------------------+-----------------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+----------+-----------------------+-----------------------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | core_user | NULL | fulltext | core_user_words_index | core_user_words_index | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted |
+----+-------------+-----------+------------+----------+-----------------------+-----------------------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.02 sec)
- key 为
core_user_words_index说明这个索引在这次查询中被用到了 type为fulltext说明使用了全文索引
Reference
- https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
- https://www.cnblogs.com/acm-bingzi/p/mysqlExplain.html
- https://database.51cto.com/art/202004/614332.htm