一、知识科普

问题：mysql 是如何判断一条select语句是否为慢查询呢？
答案：语句的执行时间 > long_query_time

long_query_time 是 mysql 的全局变量，select 语句的执行时间大于该值将被记录在慢查询日志中, long_query_time 的系统默认值是10秒：

mysql> show global variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.02 sec)

当然，在生产上我们不会设置这么大的值，一般会设置为1秒:

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

设置完后，需要重新连接数据库（因为当前会话不生效）

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

你也可以根据实际的业务需要，调整到1秒以下，来提升系统响应级别。

二、简单例子：

回答问题前，先看一个简单的例子
第一步：建表：

mysql> CREATE TABLE t (
   -> id int(11) NOT NULL,
   -> a int(11) DEFAULT NULL,
   -> b int(11) DEFAULT NULL,
   -> PRIMARY KEY (id),
   -> KEY a (a)
   -> ) ENGINE=InnoDB;

第二步：再插入2条数据：

mysql> insert into t values(1,1,1),(2,2,2);

第三步：三个语句及其执行计划情况：
语句1：没有使用索引：

mysql> explain select * from t;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

语句2：使用主键索引：

mysql> explain select * from t where id=2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

语句3：使用普通索引：

mysql> explain select a from t;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | a    | 5       | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

三个语句的区别：

• 语句1：不必多说，没有使用任何索引，数据一多，执行时间就会大于 long_query_time, 被记录在慢查询日志里；
• 语句2：使用了主键索引，一般情况下速度非常快，不过当cpu压力非常高，超负载运行的时候，执行时间也有可能超过 long_query_time;
• 语句3：使用了普通索引，但它遍历了整个a索引树，当表数据量很大时，执行时间也会超过 long_query_time;

【简单总结】

• 是否为慢查询，是由执行时间决定的，而这个执行时间，可能受各种外部因素影响。
• 是否使用索引与是否为慢查询，没有必然的联系。

三、深入讨论：

3.1 什么叫做 “使用了索引”?

众所周知，InnoDB 是索引组织表，所有的数据都存储在索引树上面的，比如在上例中，表t有一个主键索引，一个普通索引，表数据示意图如下：

表数据示意图

科普：
1.主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引 （clustered index）。
2.非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引 （secondary index）。

如果判断一个语句是否使用索引呢？很容易想到用 explain 结果的 key 是否为空来判断是否使用了索引，这样判断真的对吗？

如何用 explain 结果的key 是否为空来判断是否使用了索引，那么，从逻辑上说，所有在InnoDB表中的查询，都至少用了一个索引（即主键索引）。为什么呢？看下面的例子：比如：select * from t where id > 0; 你觉得它使用了索引吗？

mysql> explain select * from t where id>0;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

key列显示的是 PRIMARY,即使用了主键索引。为什么会这样呢？因为优化器认为这个语句在执行过程中需要根据主键索引定位到第一个满足id > 0的值，然后再向右遍历索引树，这也算用到了索引。

但我们知道，这个语句一定是做了全表扫描的（因为id是从1开始的），一个全表扫描的语句，你告诉我它还是用了索引，这未免难以让人接受。这也告诉我们，即便 key 列显示不为空，实际上也可能全表扫描的。

反过来想，使用这个方式来判读是否使用了索引，那么 InnoDB 只有一种情况叫做没有使用索引，那就是从主键索引的最左边叶子节点开始，向右扫描整个索引树。比如上面例子中的句子1：select * from t;

显然，这种结论是荒谬的，使用这种方式来判断是否使用了索引，也是不科学的！

我们再对比语句2（select * from t where id = 2）就能找到答案了，这条语句使用了索引的快速搜索功能，并且有效的减少了扫描的行数，这才是我们平时所说的使用了索引，也是我们判断是否使用索引的有效依据。

3.2 索引的过滤性

举个例子，有一张表 t_person，存了全国14亿人的基本信息，现在想查出年龄在10到15岁之间的小朋友基本信息，你会这么写：

select * from t_people where age between 10 and 15;

同时你会给 age 字段建索引，否则就会全表扫描；但是当你执行之后会发现，这个语句还是非常慢，因为满足这个条件的数据有1亿行；我们看下这条语句的执行过程：

执行过程

1. 首先在普通索引树上找到第一个满足 age > 10的叶子节点；
   1.1 取出叶子节点的内容id值，去到主键索引树上找到此id的叶子节点，返回整行内容；
2. 循环向右扫描普通索引树上的下一个叶子节点，重复1.1的动作；
3. 直到扫描普通索引树上的下一个叶子节点的 age > 15时为止；

我们说一个语句是否使用了索引，其实是关心他的扫描行数。一个大表，不止要有索引，索引的过滤性还要足够好才行，刚才age索引的过滤性就不好。

所谓过滤性好，就是数据的区分度足够高。

索引的过滤性好了，是不是扫描行数就一定少？

下面看一个联合索引的例子：

联合索引示意图

如果你的需求是：找出姓名是张三，年龄是8的所有小朋友：

select * from t_people where name = '张三' and age = 8;

这个所有的过滤性好，而且姓名是张三，年龄是8的所有小朋友应该也不多，因此向右扫描的行数很少，查询效率也很高；
但请不要搞混淆了查询的过滤性和索引的过滤性。比如你现在的需求是：查出姓张且年龄等于8的小朋友：

select * from t_people where name like '张%' and age = 8;

在MySQL5.5及之前版本，这条语句的执行过程如下：

MySQL5.5 联合索引执行过程

1. 先在联合索引树上，找到第一个字段name是张开头的第一个叶子节点；
   1.1 取出叶子节点的内容id值，去到主键索引树上找到此id的叶子节点（此动作称作回表）
   1.1.1 找到的主键索引的叶子中的age值如果等于8，则整行数据放入结果集中，否则进入第二步；
2. 循环向右扫描联合索引树上的下一个叶子节点，重复1.1、1.2的动作；
3. 直到扫描联合索引树上的下一个叶子节点的 name不是张开头时为止；

你可以看到，这个执行过程里，最耗时间的步骤就是回表，假设全国姓张的有8000万人，就要回表8000万次。在定位第一行记录的时候，只能使用索引和联合索引的最左前缀 (最左前缀原则)

这种回表次数特别多，性能就不好，有没有优化的办法呢？MySQL5.6 版本给出了优化方案：索引条件下推（index condition pushdown）：

MySQL5.6 优化后的执行过程

1. 先在联合索引树上，找到第一个字段name是张开头，同时判断第二字段age=8的第一个叶子节点；
   1.1 取出叶子节点的内容id值，去到主键索引树上找到此id的叶子节点，返回到结果集中；
2. 循环向右扫描联合索引树上的下一个叶子节点，重复1.1的动作；
3. 直到扫描联合索引树上的下一个叶子节点的 name不是张开头时为止；

这个过程和上面的区间是：在遍历整个联合索引树时，将 age = 8 这个条件下推到索引遍历的过程中，减少回表的次数，假设8000万的姓张人中，只有100万个8岁的小朋友，那么整个查询过程中，在联合索引里要遍历8000万次，而回表只要100万次；看见index condtion pushdown的优化效果还是很明显的。但是没能绕开最左前缀原则。

还能不能再优化了？

答案是可以的，我们可以将姓作为单独一列，然后用联合索引 （姓，年龄）。这样只要100完成遍历，100万次回表；
在MySQL5.7中，还引入了虚拟列来实现动态列。

mysql> alter table t_people add name_first varchar(2) generated always as (left(name,1)), add index(name_first,age);
Query OK, 0 rows affected (0.03 sec)

查看一下表结构：

mysql> show create table t_people;
+----------+-----------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                             |
+----------+-----------------------------------------------------------------------------------------------------------+
| t_people | CREATE TABLE `t_people` (
  `id` int NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `age` int NOT NULL,
  `name_first` varchar(2) GENERATED ALWAYS AS (left(`name`,1)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `index_name_age` (`name`,`age`),
  KEY `name_first` (`name_first`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

插入2条记录：

mysql> insert into t_people(id,name,age) values(1,'张三',8),(2,'李四',20);
Query OK, 2 rows affected (0.01 sec)

查看数据：

mysql> select * from t_people;
+----+--------+-----+------------+
| id | name   | age | name_first |
+----+--------+-----+------------+
|  1 | 张三   |   8 | 张         |
|  2 | 李四   |  20 | 李         |
+----+--------+-----+------------+

虚拟列的值不能主动插入，也不能主动修改，它的值在name字段修改的时候自动修改；
有了虚拟列之后，我们的sql就可以这样写了：

mysql> explain select * from t_people where name_first='张' and age=8;
+----+-------------+----------+------------+------+---------------+------------+---------+-------------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key        | key_len | ref         | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t_people | NULL       | ref  | name_first    | name_first | 15      | const,const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------------+---------+-------------+------+----------+-------+

完结！
如有帮助，点赞鼓励 👍