MySQL

【MySQL】索引命中

Posted by 西维蜀黍 on 2020-09-07, Last Modified on 2023-09-22

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

FEATURED TAGS
algorithm algorithmproblem architecturalpattern architecture aws c# cachesystem codis compile concurrentcontrol database dataformat datastructure debug design designpattern distributedsystem django docker domain engineering freebsd git golang grafana hackintosh hadoop hardware hexo http hugo ios iot java javaee javascript kafka kubernetes linux linuxcommand linuxio lock macos markdown microservices mysql nas network networkprogramming nginx node.js npm oop openwrt operatingsystem padavan performance programming prometheus protobuf python redis router security shell software testing spring sql systemdesign truenas ubuntu vmware vpn windows wmware wordpress xml zookeeper
FRIENDS

TOC