当前位置:Gxlcms > 数据库问题 > MySQL(五)MySQL索引调优【范围、排序、索引设计原则】

MySQL(五)MySQL索引调优【范围、排序、索引设计原则】

时间:2021-07-01 10:21:17 帮助过:14人阅读

联合索引第一个字段用范围不会走索引

  第一个字段就是范围查询时,可能会导致全表扫而不用索引,因为mysql可能认为第一个条件就是范围查询会导致结果集很大,还要回表,索性就干脆直接全表查询了。

-- 第一个条件就用到范围查询,可能会导致全表扫描,而不走索引
EXPLAIN SELECT * FROM t_employees WHERE name > 有梦想 AND age = 27 AND position =manager;

  我们可以尝试以下手段进行调整:

强制走索引

-- 强制声明使用索引idx_name_age_position
SELECT * FROM t_employees force index(idx_name_age_position) WHERE name > 有梦想 AND age = 27 AND position =manager;

  PS:虽然使用了强制走索引让联合索引第一个字段范围查找也走索引,扫描的行rows看上去也少了点,但是最终查找效率不一定比全表扫描高,因为select*在使用二级索引时需要回表,而回表效率不高

使用覆盖索引优化

-- 当我们使用覆盖索引,只查询我们需要的字段而不是selecct*时,这时候是可以用上第一个字段的索引的
EXPLAIN SELECT name,age,position FROM t_employees WHERE name > 有梦想 AND age = 27 AND position =manager;

  PS:如果某个字段一般取值都是查范围的,比如类似age【年龄】的这种查询条件,那么在联合索引建立的时候把它放在后面建【范围查询后面的字段不会走索引!】

in和or会根据数据量大小调整查询策略

  in和or在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描

like ‘有梦想%’ 这种形式一般情况都会走索引

  在MySQL(三)索引优化规约这篇文章里我们一起分析过like形式的查询条件是否能够走索引,而不以占位符开头时,是可以走索引的,这里涉及到一个索引下推的概念。

二、索引下推

  背景:对于辅助的联合索引(name,age,position),正常情况按照最左前缀原则:

SELECT * FROM t_employees WHERE name like 有梦想% AND age = 27 AND position =manager 

  这种情况只会走name字段索引,因为根据name字段过滤完,得到的索引行里的age和position是无序的,无法很好的利用索引。

MySQL5.6之前版本

  查询只能在联合索引里匹配到名字是 ‘有梦想‘ 开头的索引,然后拿这些索引对应的主键逐个回表,到主键索引上找出相应的记录,再比对age和position这两个字段的值是否符合。

MySQL 5.6及之后

  引入了索引下推优化,可以在索引遍历过程中,对索引中包含的所有字段先做判断,过滤掉不符合条件的记录之后再回表,可以有效的减少回表次数。

索引下推的作用

  使用了索引下推优化后,上面那个查询在联合索引里匹配到名字是 ‘有梦想‘ 开头的索引之后,同时还会在索引里过滤age和position这两个字段,拿着过滤完剩下的索引对应的主键id再回表查整行数据。

  好处:索引下推会减少回表次数

  PS:对于innodb引擎的表索引下推只能用于二级索引,innodb的主键索引(聚簇索引)树叶子节点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果。

三、Order by与Group by优化

  • 1、能用覆盖索引尽量用覆盖索引
  • 2、MySQL支持两种方式的排序filesort【磁盘文件排序】index【扫描索引排序】,Using index是指MySQL扫描索引本身完成排序。
  • 3、order by满足两种情况会使用索引:
    • 1) order by语句满足索引最左前列法则。
    • 2) 使用where子句与order by子句条件列组合满足索引最左前列法则。
  • 4、尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最左前缀法则
  • 5、如果order by的条件不在索引列上,就会产生Using filesort。
  • 6、group by与order by很类似,其实质是先排序后分组,遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。

  PS:where高于having,能写在where中的限定条件就不要去having限定了。

  PS:核心还是想办法优化让SQL语句满足最左前缀法则

  PS:计算执行成本时不会真正的去执行sql语句,而是算的预估值。

四、filesort文件排序

排序方式

单路排序

  是一次性取出满足条件行的所有字段,然后在sort buffer【缓存】中进行排序。

双路排序【回表排序】

  是首先根据相应的条件取出相应的排序字段和主键ID,然后在 sort buffer 中进行排序【缓存】,排序完后回表取回其它需要的字段。

如何选择排序方式?

  MySQL 通过比较系统变量max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

  • 字段的总长度小于max_length_for_sort_data ,使用单路排序模式
  • 字段的总长度大于max_length_for_sort_data ,使用双路排序模式

  PS:单双路排序大多数情况下都不用优化,系统已经做得很好了。

  PS:内存比较小,最好用双路排序,因为双路排序参与排序的字段少。

  PS:当排序数据无法全部放入缓存时,会分段放入缓存。这样排序会进行多次I/O效果很不好。

五、索引设计原则

代码先行,索引后上

  索引建立一般应等到主体业务功能开发完毕以后,把涉及到该表相关的sql都拿出来分析之后再建立索引,这样考虑起来会更全面一些。

  通常根据业务需求,建立几个联合索引就能基本覆盖到所有的业务场景了,这种方式比一开始建表就去拍脑袋考虑索引要完善得多。

  PS:我们可以根据我们的业务高发场景,具体的去设置几个联合索引来满足我们高发的业务场景,但不宜过多。【索引过多也会影响数据更新的效率

联合索引尽量覆盖条件

  首先我们如果能使用上覆盖索引的话,可以提升很多性能。在一些查询条件的设置,或者是排序场景下如果满足最左前列法则,是可以用上索引提高性能的。

  其次,如果我们查询的结果集也能被联合索引覆盖的话,那么查询的结果集会小很多【尽量避免select * 这种语句】,对性能的提高也是有帮助的。

不要在小基数字段上建立索引

  一般建立索引,尽量使用那些基数比较大的字段【即值比较多的字段】,那么才能发挥出B+树快速二分查找的优势来。

  如果我们索引建立在一个基数很小的字段上,比如性别上面【假设只有man和woman两种情况】,那么使用索引来过滤时,根本无法很好的进行过滤,也就失去了索引的意义。

长字符串我们可以采用前缀索引

  举个例子来解释:

  • 我们现在有一个地址字段address varchar(255)的字段要建立索引,假设其中有个数据:AAAAAAAAAABBBBBBBBBB.....(很长很长)
  • 那么我们可以针对前20位和其他字段一起来建立联合索引:index(address(20),age,position)
  • 这种情况下,查询前20位是可以用上索引的,相当于address like ‘前20位%‘

PS:假如要是排序的话,比如order by或者group by这种情况下是使用不了索引的,因为索引建立时只用了前20位,而不是整个字段的索引。

where与order by设计索引冲突时,优先where

  在where和order by出现索引设计冲突时,一般都是让where条件去使用索引来快速筛选出来一部分指定的数据,接着再进行排序。

  因为大多数情况基于索引进行where筛选往往可以最快速度筛选出需要的少部分数据,然后再做排序时,成本可能会小很多

基于慢sql查询做优化

  我们在程序运行中可能有些SQL会执行很慢,这些执行很慢的SQL如果超过了慢SQL的阈值会被后台记录下来,我们可以不定时去观察一下,把慢SQL拿出来进行分析,从何再次优化。

  详细的慢SQL相关概念可以参考:这里

六、小问答

Q:为什么范围查找Mysql没有用索引下推优化

  应该是Mysql认为范围查找过滤的结果集过大,like ‘KK%‘ 在绝大多数情况来看,过滤后的结果集比较小,所以这里Mysql选择给 like ‘KK%‘ 用了索引下推优化,当然这也不是绝对的,有时like ‘KK%‘ 也不一定就会走索引下推。

Q:联合索引越多不是越能覆盖各种业务场景么?为什么不能建立太多联合索引?

  因为联合索引过多虽然查询效率提升了,但是写入操作的效率会影响,因为写入操作会去维护索引树,索引越多当然维护成本越高。

MySQL(五)MySQL索引调优【范围、排序、索引设计原则】

标签:rgb   order by   margin   定时   and   冲突   blank   details   设置   

人气教程排行