SQL优化案例一

 有一次开发同事问我，一条语句为什么运行这么慢，希望我能够给一下解决的办法。语句可以简化成下面这个样子：

select ftime , memberid from   f_xxxxxx_dish_ip_d where ftime >= '20190918' and ftime <= '20190919' group by ftime, memberid order by ftime desc limit 50 offset 0;

首先看一下执行计划

+----+-------------+--------------------+------------+-------+--------------------------------+-------------+---------+------+----------+----------+--------------------------------------------------------+

| id | select_type | table              | partitions | type  | possible_keys                  | key         | key_len | ref  | rows     | filtered | Extra                                                  |

+----+-------------+--------------------+------------+-------+--------------------------------+-------------+---------+------+----------+----------+--------------------------------------------------------+

|  1 | SIMPLE      | f_xxxxxx_dish_ip_d | NULL       | range | index_ftime | index_ftime | 9       | NULL | 17820877 |   100.00 | Using index condition; Using temporary; Using filesort |

+----+-------------+--------------------+------------+-------+--------------------------------+-------------+---------+------+----------+----------+--------------------------------------------------------+

从执行计划里看到，这个sql使用了ftime列上的索引，但是使用了临时表和排序，这也是这个sql这么慢的主要原因。如何避免排序呢，是不是memeberid这列导致的排序呢？因此我们尝试再建一个联合索引（ftime,memberid）。再看一下执行计划是怎样的。

+----+-------------+--------------------+------------+-------+--------------------------------+-------------+---------+------+----------+----------+--------------------------------------------------------+

| id | select_type | table              | partitions | type  | possible_keys                  | key         | key_len | ref  | rows     | filtered | Extra                                                  |

+----+-------------+--------------------+------------+-------+--------------------------------+-------------+---------+------+----------+----------+--------------------------------------------------------+

|  1 | SIMPLE      | f_xxxxxx_dish_ip_d | NULL       | range | index_ftime,idx_ftime_memberid | index_ftime | 9       | NULL | 17820877 |   100.00 | Using index condition; Using temporary; Using filesort |

+----+-------------+--------------------+------------+-------+--------------------------------+-------------+---------+------+----------+----------+--------------------------------------------------------+

What？都没有只用这个新建的索引。那如果强制它使用新建的联合索引会怎么样呢？

mysql> explain select   ftime,   memberid from   f_xxxxxx_dish_ip_d force index (idx_ftime_memberid) where   ftime >= '20190918'   and ftime <= '20190919' group by   ftime,   memberid order by   ftime desc limit   50 offset 0;

+----+-------------+--------------------+------------+-------+--------------------+--------------------+---------+------+----------+----------+--------------------------------------------------------+

| id | select_type | table              | partitions | type  | possible_keys      | key                | key_len | ref  | rows     | filtered | Extra                                                  |

+----+-------------+--------------------+------------+-------+--------------------+--------------------+---------+------+----------+----------+--------------------------------------------------------+

|  1 | SIMPLE      | f_xxxxxx_dish_ip_d | NULL       | range | idx_ftime_memberid | idx_ftime_memberid | 9       | NULL | 17820877 |   100.00 | Using index condition; Using temporary; Using filesort |

+----+-------------+--------------------+------------+-------+--------------------+--------------------+---------+------+----------+----------+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

即使使用这个联合索引，任然需要创建临时表和排序，这就比较奇怪了。

后面我发现 order by 的时候指定了降序，我尝试删掉desc再看执行计划。

mysql> explain select   ftime,   memberid from   f_xxxxxx_dish_ip_d where   ftime >= '20190918'   and ftime <= '20190919' group by   ftime,   memberid order by   ftime limit   50 offset 0;

+----+-------------+--------------------+------------+-------+--------------------------------+--------------------+---------+------+----------+----------+-----------------------+

| id | select_type | table              | partitions | type  | possible_keys                  | key                | key_len | ref  | rows     | filtered | Extra                 |

+----+-------------+--------------------+------------+-------+--------------------------------+--------------------+---------+------+----------+----------+-----------------------+

|  1 | SIMPLE      | f_xxxxxx_dish_ip_d | NULL       | range | index_ftime,idx_ftime_memberid | idx_ftime_memberid | 9       | NULL | 17820877 |   100.00 | Using index condition |

+----+-------------+--------------------+------------+-------+--------------------------------+--------------------+---------+------+----------+----------+-----------------------+

1 row in set, 1 warning (0.01 sec)

？？？ 竟然是一个desc导致的问题。

于是我去问开发，能不能把order by 排序规则改成asc，开发说不能，这个要按照时间的顺序排列，时间最早的要排在最前面。

最后sql改成了这个样子：

select ftime , memberid from f_xxxxxx_dish_ip_d where ftime >= '20190918' and ftime <= '20190919' group by ftime, memberid order by ftime desc memberid desc limit 50 offset 0;

 

所以本文一共有三个问题

第一个问题，在还没有加索引之前，这条语句这么慢是不是memberid这列没有索引导致的？

是的。因为group by ftime，memberid的时候，memberid也要排序，而索引上并没有这列，因此无法通过索引来优化排序。

第二个问题，为什么我加了联合索引它却没有使用这个索引？

因为desc导致的

第三个问题，为什么desc就慢，而asc就快？为什么加了一个desc排序的列也快呢？

对于索引（ftime，memberid），索引上的数据首先按照ftime从小到大排列，对于ftime值相同的再按照memberid从小到大排列。所以这个索引可以看作是经过 order by ftime asc,memberid asc 排序过了的。再来看看我们的sql，“group by ftime, memberid order by ftime desc”，这部分看成group by ftime desc memberid asc 。这跟我们的索引顺序 ftime asc,memberid asc 并不一致，所以要排序。那如果是 group by ftime,   memberid order by ftime desc,memberid desc 呢？这个子句可以看成 group by ftime desc memberid desc 。虽然索引是 ftime asc,member asc 的顺序，但如果从后往前扫描也不需要再排序了。