where和order by使用索引不一致导致慢SQL问题

慢SQL场景

  1. 数据库版本：
1
2
3
4
5
6
7

MySQL [test]> select version();
+------------+
| version()  |
+------------+
| 5.7.25-log |
+------------+
1 row in set (0.03 sec)
  1. 表结构语句如下：
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE `t_order_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT '0',
  `name` varchar(30) NOT NULL DEFAULT '',
  `time` int(11) DEFAULT '0',
  `price` decimal(26,12) unsigned DEFAULT '0.000000000000',
  `quantity` decimal(26,8) unsigned DEFAULT '0.00000000',
  `kind` int(4) DEFAULT '0',
  `order_id` varchar(64) NOT NULL DEFAULT '',
  `org_price` decimal(26,12) unsigned DEFAULT '0.000000000000',
  `org_quantity` decimal(26,8) unsigned DEFAULT '0.00000000',
  `group_order_id` varchar(24) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `index_userid` (`user_id`),
  KEY `index_group_id` (`group_order_id`),
  KEY `index_time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
  1. 表数据量大概为300w+
1
2
3
4
5
6
7

MySQL [test]> select count(1) from t_order_logs;
+----------+
| count(1) |
+----------+
|  3096393 |
+----------+
1 row in set (0.91 sec)
  1. 慢SQL示例
1
2

MySQL [test]> select * from `t_order_logs` WHERE (user_id=1 and time>=1680001635 and time< 1687777635) and name='book' ORDER BY group_order_id DESC LIMIT 30;
Empty set (23.74 sec)

执行耗时非常的长，而且这条SQL是没有返回数据, 也就是说没有满足条件的数据行，但是当有满足条件行时执行耗时正常。

通过explain分析查询SQL

1
2
3
4
5
6
7

MySQL [test]> explain select * from `t_order_logs` WHERE (user_id=1 and time>=1680001635 and time< 1687777635) and name='book' ORDER BY group_order_id DESC LIMIT 30;
+----+-------------+--------------+------------+-------+-------------------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys           | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+-------------------------+-----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_order_logs | NULL       | index | index_userid,index_time | index_group_id  | 98      | NULL |  777 |     0.05 | Using where |
+----+-------------+--------------+------------+-------+-------------------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

很明显查询没有走条件索引，直接使用index_group_id索引，导致排序整个表，自然就会很慢。

为什么没有使用其他两个索引呢，在MySQL中有个“三星索引”的概念。

MySQL中的三星索引

  • 索引将相关记录放在一起获得一星 没有必要为where中的每一条件单独建立索引，可以把条件中的多列一起形成一个组合索引。
  • 如果索引中的数据顺序和查找中的排列顺序一致获得两星 order by 中的列，与where条件中的列，都存在于索引中，且order by 的列为最前列（索引第一前缀）。
  • 如果索引中的列包含了查询中需要的全部列则获得三星 索引中的列包含了查询中需要的全部列。同时，将易变的列变在最后。这样索引包含查询所需要的数据列，不再进行回表查询。这样的索引效率是极高的。

通过三星索引知道，一条SQL只能使用一条索引，如果order by 和 where条件中的列不同，使用的索引也不同时，MySQL只会选择一个索引。

这也就是为什么这条SQL会很慢的原因，因为where条件没有使用到索引。

把order by索引修改和where条件相同来验证一下

1
2
3
4
5
6
7

MySQL [test]> explain select * from `t_order_logs` WHERE (user_id=1 and time>=1680001635 and time< 1687777635) and name='book' ORDER BY time DESC LIMIT 30;
+----+-------------+--------------+------------+-------+-------------------------+------------+---------+------+--------+----------+------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys           | key        | key_len | ref  | rows   | filtered | Extra                              |
+----+-------------+--------------+------------+-------+-------------------------+------------+---------+------+--------+----------+------------------------------------+
|  1 | SIMPLE      | t_order_logs | NULL       | range | index_userid,index_time | index_time | 5       | NULL | 118126 |     1.25 | Using index condition; Using where |
+----+-------------+--------------+------------+-------+-------------------------+------------+---------+------+--------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到使用的索引变为index_time，这时的查询速度就会很快。

解决办法可以通过修改表索引，比如针对这个查询增加组合索引，但是加索引也是个耗时操作，可能会影响业务。

也可以通过force index / ignore index / use index 强制指定使用索引，其执行效果如下：

1
2
3
4
5
6
7
8

MySQL [test]> select * from `t_order_logs` force index(index_time) WHERE (user_id=1 and time>=1680001635 and time< 1687777635) and name='book' ORDER BY group_order_id DESC LIMIT 30;
Empty set (0.17 sec)

MySQL [test]> select * from `t_order_logs` force index(index_userid) WHERE (user_id=1 and time>=1680001635 and time< 1687777635) and name='book' ORDER BY group_order_id DESC LIMIT 30;
Empty set (2.90 sec)

MySQL [test]> select * from `t_order_logs` force index(index_group_id) WHERE (user_id=1 and time>=1680001635 and time< 1687777635) and name='book' ORDER BY group_order_id DESC LIMIT 30;
Empty set (26.16 sec)

强制走where条件索引后执行结果明显优化。

参考