MySQL索引优化实战

MySQL索引优化实战(一) 索引下推

• 在索引遍历的过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数
• 例如: select * from table where name like 'xx%' and age = 20 and position = 'xx'
• 如果是MySQL@5.6以前，以上查询只会用到name索引，但是MySQL@5.6开始引入索引下推，也就是说从索引树根据name字段筛选完之后，通过索引下推再次根据age和position字段对比是否符合

问题: 为什么'>'不用索引，而like 'xx%'却可以用索引

• where like 'xx%'其实会用到索引下推，范围查找可能mysql认为过滤的结果集非常大，而like 'xx%'过滤后的结果集却比较小，所以选择给like使用索引下推。但是这种情况并不是绝对的，因为有时候like的结果集会比范围查找的结果集大，所以有时候like 'xx%也不一定会用到索引下推'

文件排序原理

• Using filesort文件排序原理

  • 单路排序
    • 直接把查找的结果集加载到内存中统一排序，再根据order by字段排序，占用空间大些，也就是说一次就把结果集查出来
  • 双路排序
    • 只把结果集id和排序的字段加载到内存中，在内存中占用空间小很多，也就是说需要查询两次，第一次查二级索引，第二次根据主键id回表查询结果集返回

• 小结

  • 单路排序: 只需要查询一次

  • 双路排序：需要查询两次，一次二级索引，然后回表查询后返回

  • MySQL通过比较系统变量max_length_for_sort_data(默认1024byte)(可调整)

    • < max_length_for_sort_data 使用单路

    • > max_length_for_sort_data 使用单路

Sort Buffer

• 在内存中开辟一小块区域作为sort buffer存储空间
• 如果需要排序的数据没有超过sort buffer，就直接在sort buffer中排序；如果数据超出sort buffer大小，就会创建临时文件(不是在硬盘上排序)暂存数据，然后再次将临时文件数据放到sort buffer排序
• 注意:如果全部使用sort buffer内存排序，一般情况下效率会高于磁盘文件排序(毕竟是在内存)，但不能因为这个优势就随便增大sort buffer，mysql很多参数都是做过优化，不要轻易调整

优化总结

• MySQL排序的两种方式: Using index、Using filesort，index表示扫描索引本身完成排序，filesort表示使用文件排序(效率较低)

• 尽量在索引列上完成排序，遵循索引建立时的最左前缀原则

• 能用覆盖索引尽量用覆盖索引

• group by 和 order by很类似，其实质是先排序后再分组，遵循索引的最左前缀法则。

  • 对于group by的优化如果不需要排序的可以加上order by null(禁止排序)
  • where高于having，能写where中的限定条件就不要去having限定

• 问题: 建表之后该如何设计索引

  • 主体业务代码开发完之后，把这张表相关的SQL全部拉出来评估，根据SQL语句分析哪些字段查询的时候使用次数比较多，将这些频繁出现在where后面的字段建立索引，并且尽量使用联合索引

• 优化原则

  • 代码先上，索引后上
  • 联合索引尽量覆盖条件，尽量建立联合索引，建议两三个联合索引，少建单值索引(索引占空间，MySQL大多数情况只会选择一个索引，唯一索引尽量要有)，确保联合索引字段顺序满足最左前缀原则
  • 不要在小基数字段上建立索引，比如性别等毫无意义、区分度不高的
  • 长字符串我们可以采用前缀索引，比如 key index(name(20),age,position)
    • MySQL官方经过实验，大多数业务即便结果集很长，用前缀20个字符去搜索，90%情况也能区分出来，但是这种索引有个很大的弊端，order by name 不会用到索引
  • where 和 order by冲突时优先用where
  • 基于慢SQL查询优化(依据B+Tree索引)
    • set global_long_query_time = 4 设置慢SQL查询时间
    • set global_slow_query_log 开启慢SQL查询
  • 如果经常需要用到范围字段查询，尽量放到联合索引的最后(如: name,position,age)，如果放中间，大多数情况查询时会使'范围查询'后面的索引失效，如 where name = 'xx' and age > 10 and age < 20 and position = 'xx'
  • 索引建太多也不好，因为增加、删除、更新 *** 作就会比较慢，毕竟需要去维护索引树，对于读多写少的场景可以多建立联合索引