Mysql调优--索引04（油炸圣女果的学习日记第四期）

本期是一些常见的sql深入优化。

Order by与Group by优化

Case1：

分析：

利用最左前缀法则：中间字段不能断，因此查询用到了 name索引 ，从key_len=74也能看出，age索引列用在 排序 过程中，因为Extra字段里没有 using filesort （排序走的索引不会体现在key_len上）

Case 2：

分析：

从explain的执行结果来看：key_len=74， 查询 使用了name索引，由于用了position进行排序，跳过了 age，出现了 Using filesort （即用的文件排序没有在索引树上排序） 。

联合索引建立时是按照name、age、position来建立的，在索引树上，name相等时，age是有序的，但是position是无序的（只有在name、age都相等的叶子结点，position才是有序的）

Case 3：

分析：

这个很常规。查找和排序都用了索引，查找只用到索引name，age和position用于排序，无 Using filesort 。

Case 4：

分析：

和Case 3中explain的执行结果一样，但是出现了 Using filesort ，因为索引的创建顺序为

name,age,position，但是排序的时候age和position 颠倒位置 了（所以， 按照索引建立时的字段顺序来查找和排序很重要 ）。

Case 5：

分析：

与Case 4对比，在Extra中并未出现 Using filesort ，因为 age为常量 ，在排序中被优化（可以理解为排序其实不用考虑age字段了），所以索引未颠倒， 不会出现Using filesort，依然使用了索引。

Case 6：

分析：

虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序， 导致与索引的 排序方式不同 ，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

Case 7：

分析：

对于排序来说，多个相等条件也是范围查询（原理等于是将两个name的索引查找到放到一起，这个时候对于整个来说age和position是无序的，如只能确保在name=lilei的叶子节点里，age、position有序）

Case 8：

分析：

条件为name>a，用name排序为什么不走索引呢？

其实还是Mysql判断你的查询条件name>a，可能查询的数据量很大，而且是select *，那么就还需要回表操作，所以mysql自己判断可能用全表扫描可能更快。

考虑到这种情况，可以用 覆盖索引优化来验证猜想（结果确实是的）。

优化总结：

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文件排序方式：

1.单路排序：是一次性取出满足条件行的所有字段，然后在 sort buffer（可以理解为排序内存区） 中进行排序；用trace工具可 以看到sort_mode信息里显示< sort_key, additional_fields >或者<sort_key,packed_additional_fields >（先取后排）

2.双路排序（又叫 回表排序模式 ）：是首先根据相应的条件取出相应的 排序字段 和 可以直接定位行

数据的行 ID ，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具 可以看到sort_mode信息里显示< sort_key, rowid >（先取部分再排序然后取全量数）

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

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查询做特定的索引优化。