MySQL 排序，分组，Limit的优化策略

目录

1. MySQL 中的两种排序方式

2. 排序优化策略

2.1 对排序字段添加索引

2.2 可以和WHERT字段创建联合索引

2.3 优化 FilerSort 排序方式

3. 分组优化策略

3.1 能WHERE不HAVING

3.2 减少ORDER BY，GROUP BY，DISTINCT

3.3 遵照最左前缀法则

4. Limit 优化策略

---

1. MySQL 中的两种排序方式

在MySQL中，主要支持两种排序方式，分别是 FileSort 和 Index。

Index：索引排序，就是我们给排序的字段添加了索引，因为索引本身就是有序的，所以我们在根据排序的时候就非常省时间了，不需要进行重排序，直接取出数据即可，效率很高。

FileSort：文件排序，在查询到数据之后，因为没有设置索引，所以CPU就需要在内存中进行排序，排好序之后再将数据进行返回，而且数据量如果较大，排序花费时间也会变长；并且，如果数据量非常大，内存中装不下，还需要多次IO操作，先读取一部分数据排序，再读取一部分数据排序，效率较低。

2. 排序优化策略

2.1 对排序字段添加索引

从上面两种排序方式不难看出，Index 索引排序明显是要比 FileSort 内存排序效率要高的，因此我们最好能够在排序字段上添加索引，这样在查询的时候就取出来的就是有序数据，省去了排序时间；

如下所示，我查询 employees 员工表并通过 salary 薪水字段排序，此时还没有给 salary 字段设置索引，查询到107条数据，花费 0.024秒；

 我现在给 salary 薪水字段设置一个普通索引，然后再去做一遍查询

 可以看到，再添加过索引之后，让然查询到了107条数据，时间缩短为0.017秒，可能同学们觉得没有什么差别，这只是因为数据量小的原因，只有一百多条记录，如果有上千条上万条数据，花费的时间一下子就拉开距离了。

2.2 可以和WHERT字段创建联合索引

在SQL语句中，排序通常也会出现WHERE过滤字段，在这种情况下，我们可以考虑给WHERE过滤字段和ORDER BY排序字段建立一个联合索引。如果二者是同一个字段，那就更完美了，就给这个字段建立独立索引；如果是两个字段，建立联合索引，但要注意WHERE过滤如果是范围查找，会导致联合索引中后续索引失效，那么即便设置了排序字段索引，也是用不上的。在设置联合索引时一定要注意满足最左前缀原则，保证索引能够生效。

如下，此时 department_id 和 salary 字段都有索引，但是没有联合索引，所以查询的时候只会用到 department_id 这个字段的索引，

此时我给 department_id 和 salary 建立联合索引再次查询 

查询得到相同的结果，使用联合索引时间0.017秒，比单独使用 department_id索引快了 0.02秒； 

2.3 优化 FilerSort 排序方式

有些时候，我们无法避免的会出现 FileSort 内存排序，其实内存排序有两种方式，分别是双路排序和单路排序。

双路排序：扫描两次磁盘，数据库会先将需要排序的字段IO加载到内存中进行排序，经过排序之后再根据排好序的字段再次IO将完整数据查询出来；

单路排序：数据库会一次性将全部数据加载到内存，然后进行排序，并且在IO的时候是顺序IO读取，读取过后再排序，比双路排序要好。因为双路排序在第二次IO读取数据的时候是根据排好序的顺序读取数据的，是随机IO，明显没有顺序IO要快。但如果数据量较大，就对内存要求较高，但现在内存技术发展迅速，内存已经不值钱了，所以通常建议采用单路排序。

3. 分组优化策略

3.1 能WHERE不HAVING

HAVING也是一个过滤关键字，它后面可以使用聚合函数再次过滤，但是建议能在WHERE后面写的过滤条件就不要写在HAVING后面，WHERE过滤之后剩下的少量数据无论是排序还是分组都只会花费很少的时间，所以能WHERE过滤的数据就不要用HAVING。

3.2 减少ORDER BY，GROUP BY，DISTINCT

对于数据库而言，排序，分组，去重这些操作都是比较繁琐耗费资源的，如果将所有操作全部放在数据库中，非常容易出现慢查询，因此我们可以考虑将这些操作放在程序端去做，数据库查询到数据之后，使用程序代码进行排序分组去重；

3.3 遵照最左前缀法则

GROUP BY使用索引的规则几乎与ORDER BY一样，尽量遵循索引最左前缀原则；

4. Limit 优化策略

有些极端情况，如下，我取第十万条记录之后的十条记录，这种情况下数据库就会把所有的数据全部加载到内存中，分页排序之后只取第一万条记录之后的十条记录，做了大量的无用功。

SELECT * FROM employees ORDER BY employee_id LIMIT 10000,10;

那么我们就可以对上面的SQL做修改，直接使用WHERE过滤前一万条数据，从第10001条记录开始取。提高效率，但实际上这种情况很少发生，如果真的有有这种需求，建议直接将10000作为WHERE的一个过滤条件；

SELECT * FROM employees WHERE employee_id > 10000 LIMIT 10000,10;