以下两个sql是如何使用索引的？

作者: qishuai | 来源:发表于2021-08-09 09:31 被阅读0次

以下两个sql是如何使用索引的？
1. Mysql索引数据结构详解
咱们500万条数据测试一下，如何合理使用索引加速？
SQL语句的优化
mysql优化案例
索引失效
Mysql慢查询如何优化 --- 2021-09-14
MySQL优化----SQL语句和索引优化
mysql查询语句优化
Mysql 索引优化

mysql> show create table s1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| s1    | CREATE TABLE `s1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int(11) DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_key2` (`key2`),
  KEY `idx_key1` (`key1`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> explain select key_part1 from s1 where key_part1 = "a" and key_part2 > "b" AND key_part3 > "c";
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_key_part  | idx_key_part | 606     | NULL |    1 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

该explain结果中，key字段信息表明该查询使用到了索引: idx_key_part；Extra信息里面包含了Using where和Using index，其中Using where代表该查询需要mysql server层进行判断，Using index代表该查询使用到了覆盖索引的特性，不需要回表操作。
所以该sql的执行流程是，mysql存储引擎层将满足key_part1 = "a"条件的第一条记录返回给mysql server层，由于该索引是联合索引（包含key_part1,key_part2,key_part3这三个字段），所以除了key_part1字段外，其他字段也一起会返回给server层，然后server层判断该记录是否符合查询条件，如果符合发送给客户端，否者不发送；然后server层继续向存储层要下一条记录，继续判断，直到第一条不满足key_part1 = "a"条件的记录为止，然后存储层告诉server层，查询完毕。

mysql> explain select * from s1 where key_part1 = "a" and key_part2 > "b" AND key_part3 > "c";
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | range | idx_key_part  | idx_key_part | 606     | NULL |    1 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

explain结果中，key字段信息表明该查询使用到了索引: idx_key_part；Extra信息为Using index condition说明该sql使用了innodb引擎的Index Condition PushDown的能力，即索引下推；
执行流程如下：mysql存储引擎层将满足key_part1 = "a"条件的第一条记录，继续判断该记录是否满足key_part2 > "b" AND key_part3 > "c"条件，如果满足返回给server层，否者不返回；直到第一条不满足key_part1 = "a"条件的记录为止，查询完毕。