MySQL（3）索引实践一

一、索引下推：
对于辅助的联合索引(name,age)，正常情况按照最左前缀原则，SELECT * FROM user WHERE name like 'xiao%'
AND age = 22  这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age是无序的，无法很好的利用索引。
在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'xiao' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索
引上找出相应的记录，再比对age字段的值是否符合。
MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可
以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'xiao' 开头的索引之后，同时还会在索引里过
滤age字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据，这就是为什么like 'xiao%' 后面的age也会走索引的原因

二、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、代码先行，索引后上
2、尽量建立联合索引，覆盖条件查询
3、不要在小基数上建立索引，比如性别
4、长字符串使用前缀索引，比如index（name（20）），这样mysql会先到索引树里根据name字段的前20个字符去搜索，定位到前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来的完成name字段值进行比对，但是order by name 不能走索引
5、where 与 order by 冲突优先where
6、mysql默认慢sql为10s，基于查询慢sql优化
7、where 多条件，范围查询放最后，如果有多个范围查询，可以修改第一个范围查询为in（），或者数据库做加一个字段范围标识，转换为等值查询，根据具体
情况去优化
8、select * from user force index（idx） 可以用force index 强制使用索引
9、in和or在表数据量比较大的情况下会走索引，在表记录不多的情况下会选择全表扫描
10、like 'kk%' 或者 >= 后面条件一般情况下都会走索引，使用了索引下推
11、查看order by 是否走索引，可以看 Extra字段，如果是 Using index是走了索引，如果是 Using filesort 使用了文件排序是没有走索引
12、查看sql执行情况的可以打开trace
set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
你的执行语句
SELECT * FROM information_schema.OPTIMIZER_TRACE;
生产环境关闭，会影响性能
set session optimizer_trace="enabled=off"; ‐‐关闭trace