【mysql】sql查询速度不变？不同数据量下，查询速度不会变化的问题

一、前言

      如题所示，博主在测试sql的时候，发现有一条sql的速度是固定的？因为是时间搜索，所以我们通过更改时间区间来测试如下：

between '2018-05-20 00:00:00' and '2019-05-30 23:59:59        66646 rows   19s
between '2019-05-20 00:00:00' and '2019-05-30 23:59:59         9046 rows	  19s

      大概就是这个样子的，查询数据量在6W和9千的时候，速度竟然一样，而且是真的慢，这是为什么呢？刚发现时候还是挺郁闷的，不过通过这个问题也让博主学到不少东西，下面听我娓娓道来。

sql的来源是博主的上一篇文章：mysql使用group by实现组内排序实战

二、sql的explain对比

1、原sql的explain

mysql> desc select max(pay_id) as max_pay_id,sum(pay_money) as total_pay_money,receiver_id from pay_info  force index(receiver_id) where pay_date between '2019-05-20 00:00:00' and '2019-05-30 23:59:59'  and order_state > 0 and server_unique_flag > 0 group by receiver_id;
+----+-------------+----------+-------+---------------+-------------+---------+------+---------+-------------+
| id | select_type | table    | type  | possible_keys | key         | key_len | ref  | rows    | Extra       |
+----+-------------+----------+-------+---------------+-------------+---------+------+---------+-------------+
|  1 | SIMPLE      | pay_info | index | receiver_id   | receiver_id | 8       | NULL | 2974170 | Using where |
+----+-------------+----------+-------+---------------+-------------+---------+------+---------+-------------+

      大眼一瞟，用到了索引，extra没有显示其他的东西，sql优化看起来还不错，但是速度为何这么慢呢，难道是因为咱们强制指定索引的原因吗？

2、去掉force index强制索引

mysql> desc select max(pay_id) as max_pay_id,sum(pay_money) as total_pay_money,receiver_id from pay_info where pay_date between '2019-05-20 00:00:00' and '2019-05-30 23:59:59'  and order_state > 0 and server_unique_flag > 0 group by receiver_id;                          
+----+-------------+----------+-------+----------------------------------+----------+---------+------+--------+---------------------------------------------------------------------+
| id | select_type | table    | type  | possible_keys                    | key      | key_len | ref  | rows   | Extra                                                               |
+----+-------------+----------+-------+----------------------------------+----------+---------+------+--------+---------------------------------------------------------------------+
|  1 | SIMPLE      | pay_info | range | receiver_id,order_state,pay_date | pay_date | 5       | NULL | 109814 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+----------+-------+----------------------------------+----------+---------+------+--------+---------------------------------------------------------------------+

      大眼一瞟，用到了索引，但是extra显示使用了临时表和文件排序，看起来是真难看。但是执行查询看了下，速度竟然提升到了3s。这是为什么呢？明明看起来sql很差劲的

3、分析结果

      可以看到使用force index之后，explain的extra确实是好看了很多，但是为什么查询比不用force还慢呢？不加force的话，extra里面用到了临时表和文件排序呢。后来仔细看一下执行计划，用force_index之后，sql一共扫描了297W的数据。而不加force，一共扫描了10W的数据。数据量的差异过大，所以说哪怕不加force使用到了临时表也无所谓，速度也会更快一筹。

三、如何优化

1、force index 的问题吗

      其实这里的主要问题并不是force index这个语法的影响，而是force index里面的索引列的影响。我们为了消除group by的临时表和文件排序，所以选用了receiver_id这个字段作为强制索引。但是我们忽略了索引关于筛选数据的作用。receiver_id在这里基本没有起到忽略数据的作用，所以扫描行数是297W。 这里我们使用force index(pay_date)的话，扫描的函数会直线下降。

2、有人可能会说，加联合索引可以吗？

大家都知道，group by会产生临时表,order by会产生文件排序。根据sql的执行顺序，先where条件，后group by，为啥不加个联合索引，让where中的条件字段和group by的字段都包含在这个索引中呢？这样group by 就不会使用临时表了。

      答案是不可以。首先我们要了解联合索引的最左原则，范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。 我们sql的where条件里面有三个范围查询，所以加联合索引的话，也只能用到最左前缀的那个索引，而后续的字段是用不到这个联合索引的。group by中的字段也用不了这个联合索引。 博主本地测试是这个样子的，如果大家有不同的看法，一定要提出来，大家一起讨论。

参考：https://blog.csdn.net/LJFPHP/article/details/90056936

四、最终选择

1、方案

1、使用force index消除临时表，避免扫描过多数据
2、选用能尽可能的筛选数据的列，使用这个列的索引
		例如：
		order_state缩小范围有限，缩小50%
		server缩小范围10%
		pay_date如果搜索范围小的话，能缩小到1%以下，如果搜索范围大，那速度慢点也正常，何况还能用到索引。因此选用pay_date
3、在扫描行数和extra优化之间选一个更合适的方案
4、选用force index(pay_date) 之后，耗时：  19s ---> 4s
5、给where 条件的字段都加联合索引（pay_date,order_state,server_unique_flag ,receiver_id） ，经过测试效率和单纯使用pay_date相当

2、测试效果

force index(pay_date)	2018/5/20		4.01s	148w rows	
force index(receiver_id)	2018/5/20		18.76s	297w rows	
---------------------------------------------------------------
force index(pay_date)	2019/5/20		0.13s	12w   rows		
force index(receiver_id)	2019/5/20		18.63s	297w rows

      博主这里最终选用的是用pay_date这个时间索引，因为在sql里面，时间筛选是最明显的，其他的where字段辨识度都不是很高，不能起到很好的筛选效果。通过这个问题，博主才发现，原来并不是explain看起来好看就行了，很多时候是要根据实际的情况来决定的，目标只有一个，那就是让sql快一点，再快一点。

3、优秀文章

（1） https://testerhome.com/topics/16505
      利用 group by 的 Loose Index Scan 优化 sql （利用mysql自带的松散索引扫描策略，实现优化的方案，我这边因为where条件里面不是常量
，因此不能使用，但是思想很值得学习）

（2）https://blog.csdn.net/u014044812/article/details/51004754
（关于sql和MySQL的语句执行顺序）

优化水平还是有限，加油！

end