Order by 与 group by 的优化
case1:分析下列SQL是否使用到索引
EXPLAIN SELECT FROM employees2 WHERE name = ‘lilei’ AND position = ‘dev’ ORDER BY age;
EXPLAIN SELECT FROM employees2 WHERE name = ‘lilei’ ORDER BY position ;
可以看到，执行结果1中，虽然显示的索引长度为74，表面上看起来，ORDER BY age，好像是没走索引 的
在Extra中可以观察到，其实是使用了索引的，因为存在Using index condition字段
我们看在第一个条件中，name并非范围查询，它是一个等值。在索引树上，name相等的情况下，age是有序排列的
所以在结果1中，当name为等值的时候，age是一定会走索引的。
同理，我们观察结果2，发现结果2的Extra中存在Using filesort，这表示结果2没有使用到索引排序，而是走的文件
排序（Using filesort代表不走索引，走文件排序）
因为在SQL2中，在name为等值的情况下，将age跳过，直接去对position进行查找，那么position无法有序的在
索引树中进行排列，，因为age不确定，如果age可以确定，那么根据索引下推，position应该也是会走索引
所以在age不确定的情况下，position无法走索引
排序的方式是按照建立联合索引的顺序来进行排序的
如下SQL:
EXPLAIN SELECT FROM employees2 WHERE name = ‘lilei’ ORDER BY position,age;
EXPLAIN SELECT FROM employees2 WHERE name = ‘lilei’ ORDER BY age,position;
通过执行结果可以看到，只有name字段走了索引，如果先按position排序，那么不会走索引，如果是先按照
age进行排序，那么是可以正常走索引的，因为age为常量，在排序中已经被优化，索引的顺序未颠倒，不会出现
Using filesort 的情况。如下图所示为联合索引大体存储结构
我们再来看第三种情况 in() ：
EXPLAIN SELECT * FROM employees2 WHERE name in (‘lilei’,’syp’) ORDER BY age,position;
在name in(‘lilei’,’syp’) 的时候，为什么没有用的到排序？
后面两个字段要有序前提是第一个字段要相等，根据索引下推，第一个字段为等值的情况下，后面两个字段才有可能是有序的。
在上面的SQL语句中，我现在要找两个值，一个是lilei,一个是syp,吧这两个值的结果拼到一起，但我们现在要将两个结果集
一起排序说白了这两个结果集的第一个字段不相等，所以后面的字段不是有序的，所以无法走索引。
第四种情况
如下SQL
EXPLAIN SELECT FROM employees2 WHERE name > ‘a’ ORDER BY name;
可以从执行结果看到，这种情况也没有走索引。按照我们对索引树的理解，这种情况应该是可以走索引的，先在索引树上找到A
后面的结果应该都是有序的，都可以走索引，为什么会走的ALL（全表扫描）呢？
这种情况通常是由于数据量太大的原因，mysql认为这种查询走索引的效率还要涉及回表，不如直接走全表扫描效率高，
所以默认会走全表扫描。针对这种情况，我们可以使用覆盖索引进行优化，如SQL语句：
EXPLAIN SELECT name,age,position FROM employees2 WHERE name > ‘a’ ORDER BY name;
*优化总结：
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index
效率高，filesort效率低。
2、order by满足两种情况会使用Using index。
1) order by语句使用索引最左前列。
2) 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中
的限定条件就不要去having限定了。
Using filesort文件排序原理详解
filesort文件排序方式
单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可
以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,
packed_additional_fields >
双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行
数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具
可以看到sort_mode信息里显示< sort_key, rowid >
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来
判断使用哪种排序模式。
如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模∙式。
索引设计原则
1、代码先行，索引后上
不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？
这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立
索引。
2、联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的
where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原
则。
3、不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，
其值不是男就是女，那么该字段的基数就是2。
如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没
法进行快速的二分查找，那用索引就没有太大的意义了。
一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查
找的优势来。
4、长字符串我们可以采用前缀索引
尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会
比较小，此时你在搜索的时候性能也会比较好一点。
当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立
索引，哪怕多占用一些磁盘空间也是有必要的。
对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个
字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY
index(name(20),age,position)。
此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name
字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来
完整的name字段值进行比对。
但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排
序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。
5、where与order by冲突时优先where
在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到
底是让where去用上索引，还是让order by用上索引?一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可
能会小很多。
6、基于慢sql查询做优化
可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。
关于慢sql查询不清楚的可以参考这篇文章：https://blog.csdn.net/qq_40884473/article/details/89455740
若有收获，就点个赞吧
0 人点赞
