mysql 性能优化（五）避免索引失效原则及常见优化方法

一、避免索引失效原则

一般而言数据库表中字段都应该是not null，设置非空，即使存在空值，可以设置默认值替代，这样方便使用索引，优化性能

sql优化是一种概率层面的优化，不是百分百的，至于是否使用了我们的优化，需要通过explain进行推测 因sql优化器可能会进行修改执行的sql

• in可能导致失效
• 不要在索引上进行任何操作（计算，函数，类型转换等 ），否则索引失效
• 复合索引
  • 复合索引满足最佳左前缀，不要跨列和无序使用，如果左侧失效，则右侧全部失效。
    • 复合索引设定最好将区分度大的字段放到左侧，区分度小的放到右侧：例如索引中含有name，status，因status状态字段一般就是0或1，所以区分度不大；name是多样的区分度比较大。在where时第一层就把name过滤了一大部分，减少扫描行数，提升效率
    • 复合索引如果使用order by等需要考虑复合索引中包含此排序字段，要结合mysql，执行顺序，优化尽量达到索引覆盖
  • 尽量使用全索引匹配
    例如x是一个表的索引 ：select …where x*3 = 6;这样会导致索引失效
    
  • 不能使用!=、<>、 is null、is not null，否则自身以及右侧索引全部失效
  • 有时使用<或> 也可能导致自身以及右侧索引全部失效
• 一般而言范围查询（<、>、in ）之后的索引失效
• 尽量Extra，是using index（使用索引覆盖），不会出错是百分百的
  • 例如复合索引a,b,c：select a,b,c from…where a=… and b= …;
• like 尽量以“常量”开头不要以“%”开头，否则索引失效（using index索引覆盖可以挽救一部分，优先级可能达不到很高，但是有提升）
• 尽量不使用类型转换（显示/隐式）
  • 例如查询name（字符串）但是使用的却是select * from … where name =123;程序底层将123转换"123"发生类型转换
• 尽量不使用or，否则导致前后索引都失效

索引优化，是一个大部分情况使用的结论，但有sql优化器等原因，该结论不是100%正确。

二、常见优化方法

1、exist和in

select …from table where exist/in(子查询);

• 如果主查询的数据量大则用in效率更高
• 如果子查询的数据量大则用exist效率更高

说明

• exists：将主查询的结果放到子查询中进行校验（如果有数据则匹配），如果校验复合则保留数据，不符合则不保留

  select * from table t where exists(select o.id from table1 o where t.id = o.tid)

• in：确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

  select * from table t where t.id in (1,2,3)

2、order by优化

• using filesort两种算法（根据IO的次数）
  • 双路排序（mysql4.1之前双路排序）：两次IO（扫描2次磁盘，第一次扫描排序字段，进行排序，排序动作是在buffer（缓冲区 ）中进行的，第二次扫描其他字段）
  • 单路排序（mysql4.1之后单路排序）：只读取一次全部字段，在buffer中找出排序字段，进行排序。但此种但路排序，有一定隐患（不一定是真的“单路”一次IO，有可能多次IO）
    • 如果数据量特别大，超过buffer大小，则无法一次性将所有字段读出，因此会进行多次读取（分片读取）
    • 单路排序比双路排序会占用更多的buffer
    • 单路排序在使用时，如果数据量特别大，可以考虑buffer的容量大小

      set max_length_for_sort_data = 1024; #单位是字节byte

如果max_length_for_sort_data值太低则mysql 会自动从单路切换到双路

提升order by查询的策略

1. 选择使用单路、双路；调整max_length_for_sort_data大小
2. 避免select * … 防止多余的计算，以及不能索引覆盖 using index；
3. 复合索引不要跨列使用；避免using filesort
4. 保证全部的排序字段，顺序一致性（都是升序，或者降序）

3、单值索引时间优化

table 表中created_date（时间类型datetime：2021-09-08 23:23:12）是单值索引，常见sql写法date()函数查询年月日时间

SELECT * FROM table WHERE DATE(created_date) = '2021-09-08'

优化方案：created_date改为范围查询

SELECT * FROM table WHERE created_date > '2021-09-08 00:00:00' and created_date < '2021-09-08 23:59:59'

4、复合索引

alter table book add index idx_bta (bid, typeid, authorid);

• 注意不能使用范围查询，会导致索引失效：bid =1 and typeid > 5 and authorid = 6;这样会导致索引只使用了bid和typeid。范围之后的字段都不会走索引

5、distinct优化using temporary

一般distinct出现using temporary是因为使用了临时表去重导致的，直接对 distinct 字段加索引即可优化掉using temporary，因为直接走了索引树不需要操作临时表

6、like “%值%”，应用场景中必须使用前后模糊匹配优化

例如：复合索引(name ,age, position)

• 针对这种场景优化可以使用索引覆盖方式（Using Index）查询，也会使用索引的

select name ,age, position from user where name like '%张%'

• 或者借助其他工具，例如搜索引擎ES

7、可以多看看阿里开发手册，有很多优化策略