欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Mysql-索引失效 order by优化

程序员文章站 2024-03-23 15:20:10
...

Mysql-索引失效 order by优化

索引失效

  • 最佳左前缀法则

    如果索引了多列 要遵循最佳左前缀法则 指从查询索引的最左前列开始并且不跳过索引中的列

    Mysql-索引失效 order by优化

    Mysql-索引失效 order by优化

  • 不在索引列上做任何操作 会导致索引失效而转向全表扫描

    Mysql-索引失效 order by优化

  • 不能使用索引中范围条件右边的列

    Mysql-索引失效 order by优化

  • 尽量使用覆盖索引

    Mysql-索引失效 order by优化

  • 在使用不等于(!=或者<>)的时候 无法使用索引会导致全表扫描

    Mysql-索引失效 order by优化

  • is null 和is not null也无法使用索引

    Mysql-索引失效 order by优化

  • lile以通配符开头 会造成索引失效变成全表扫描

    百分号开头的情况下 会造成索引失效

    如果必须得用百分开头 如%abc% 这种情况 则可以利用覆盖索引解决索引失效的问题

  • 字符串不加单引号 会导致索引失效

    Mysql-索引失效 order by优化

  • 小结

    Mysql-索引失效 order by优化

  • 练习1

    Mysql-索引失效 order by优化

  • 练习2

    Mysql-索引失效 order by优化

    只用c1 一个字段索引 但是c2 c3 用于排序

  • 练习3

    Mysql-索引失效 order by优化

    第二个sql c1 c2 用于索引 c2 和 c3 用于排序

  • 练习4

    Mysql-索引失效 order by优化

order by关键字优化

  • 上手案例

    Mysql-索引失效 order by优化

  • 1.where条件限制,order by 2 字段(2字段为已建立组合索引字段,并按照组合索引的顺序排序),索引生效

Mysql-索引失效 order by优化

  • 2.where条件限制,order by 2 字段(2字段为已建立组合索引字段,但排序的顺序和组合索引的顺序不一致),出现Using filesort

    EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY name ,deptId;
    

Mysql-索引失效 order by优化

  • 3.where条件限制,order by 2 字段(其中某一字段为非组合索引字段),出现Using filesort

    EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId ,empno;
    

Mysql-索引失效 order by优化

  • 4.where条件限制,where and条件的值确定,排序条件中有该定值字段,即使order by后字段顺序和组合索引的顺序不一致(排序字段去除定值字段后剩余字段后组合索引顺序一致),此时不会出现Using filesort

    EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId,age;
    

Mysql-索引失效 order by优化

  • 5.order by后跟的排序字段是desc和asc 组合,不论排序顺序是否和组合索引顺序一致,必然会出现Using filesort

    EXPLAIN SELECT * FROM emp WHERE age = 45 ORDER BY deptId DESC,name ASC;
    

Mysql-索引失效 order by优化

  • order by索引对比

    1)无过滤条件(无where和limit)的order by 必然会出现 Using filesort
    2)过滤条件中的字段和order by 后跟的字段的顺序不一致,必然会出现 Using filesort
    3)order by后跟的字段排序即有DESC也有ASC,必然会出现Using filesort
    4)where条件的值确定,且order by后跟了跟了where条件的排序字段(order by 字段去除定值字段后剩余单字段),即使order by后跟的字段和组合索引字段顺序不一致,也不会出现Usi

  • order by索引建议

    1)尽量在索引列上完成排序操作,遵循最佳做前缀法则
    2)order by子句,尽量使用index方式排序,避免使用filesort方式

  • 无索引 order by的索引建议

    双路排序:扫描2次磁盘获取最终数据,第一次扫描读取行指针和order by字段列的值进行排序,刷选出需要的排完序的行指针,第二次扫描读取所需的全部数据
    单路排序:从磁盘中读取查询所需的全部列,在buff中进行排序,排序后进行输出,只需要扫描一次磁盘
    问题:双路排序相比单路排序会减少I/O次数,但会消耗更多的内存,如果取出的数据总大小超出sort_buffer的容量,会创建temp文件进行多路合并,反而会增加I/O次数,同理双路排序也会出现同样的问题,但单路排序的相对几率要高很多
    优化:
    1)增大sort_buffer_size
    2)增大max_length_for_sort_data
    3)减少select后跟的查询字段

  • 小结

    key a b c(a,b,c)
    
    order by能使用索引最左前缀
    - order by a
    - order by a,b
    - order by a,b,c
    - order by a DESC,b DESC,c DESC
    
    如果where使用了索引的最左前缀定义为常量  则order by能使用索引
    - WHERE a=const order by b,c
    - WHERE a=const AND b=const ORDRE BY c
    - WHERE a=const AND b>const ORDER BY b,c
    
    不能使用索引进行排序
    - ORDERE BY a ASC,b DESC,c DESC   排序不一致
    - WHERE g=const ORDER BY b,c   丢失a索引
    - WHERE a=const ORDER BY C  丢失b索引
    - WHERE a=const ORDER BY d  d不是索引的一部分