包学包会,sql优化全过程,刨根分析

本文主要说明 join group by order by算法和explain执行计划过程

一条sql语句的执行过程

-连接器- 分析器- 优化器- 执行器

sql的优化过程就处于优化器这个阶段,包括逻辑转换,去掉多余的sql语句部分,成本优化等

优化器的目的:

-寻找出一条最小成本执行最快的sql语句

成本分为:

-I/O成本,读取写入的页数,innodb引擎下页为16kb,磁盘页为4kb

-CPU成本,对比的记录数

join的过程中有3种算法分别是

嵌套循环连接(Nested-Loop Join)NLJ算法:适合都是索引字段的情况下,先拿外层(驱动表)的全部数据,然后一次一行比较外层(被驱动表)的数据进行关联

比较次数 = 外层数据*内层数据(非索引字段的情况下效率低)

索引嵌套循环连接(Index Nested-Loop Join)INLJ算法:相对于NLJ算法,内层循环是使用索引树的高度决定循环次数的(效率高),有的情况下需要回表。

比较次数 = 外层数据*索引树高度

块嵌套循环连接(Block Nested-Loop Join)BNL算法:如果关联的字段不是索引或者有一个字段不是索引,Mysql就会采用此算法,关联时会将外层(驱动表)的所有数据都读入Join Buffer中(默认为256k,通过参数join_buffer_size来设置,最大值一般默认为4G,如果驱动表的数据过大,那么就有分批读入分段放,就会存在多次读入驱动表),然后扫描内层(被驱动表),将被驱动表的数据一行行取出来跟join_buffer中的驱动表数据做批量对比。

join小结:

-当我们不使用Index Nested-Loop Join算法时,默认使用的是Block Nested-Loop Join算法,来减少嵌套的循环次数。有索引的情况下NLJ比BLJ算法性能更高

join优化方案:
  1. 减少不避免的查询字段(减少join buffer的数据,减少外层循环的次数)

  1. 为被驱动表的匹配条件增加索引(减少内层循环的次数)

  1. 用小结果集驱动大结果集(减少外层的循环次数)

  1. 可以考虑增大join buffer size(增大驱动表的数据量,避免多次读入表,减少外层循环次数)

  1. 尽量使用覆盖索引,减少回表的次数

排序算法:单路排序和双路排序(没有索引的情况下)

Mysql通过系统变量max_length_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪个排序算法

如果小于则使用单路排序:mysql会一次性取出所有字段放入sort_buffer(默认1M)中进行排序。如果数量量大于sort_buff,那么就会使用filesort(严重影响性能)

如果大于则使用双路排序:mysql会取出要排序的字段跟主键值,排完序后,再进行回表查询

排序总结:

-要尽量查询需要的字段,减少双路排序,使用Using index排序,避免Using filesort

排序优化方案:

a. order by 字段使用索引

b. 使用where order by group by条件组合的时候,尽量使用联合索引,满足最左前缀法则

c. 使用覆盖索引

记一次sql调优过程:

当时通过慢查询log里看到了一条sql语句执行了3s多,这条sql语句关联了几张表进行查询,用explain计划执行了这条sql语句发现有2张表在type那一列为null(至少达到range最好到ref级别),同时在Extra列下出现Using filesort ,Using temporary这俩个严重影响性能的提示

解析下为什么出现Using filesort,Using temporary

Using filesort:在order by排序字段未加索引,或者在单路排序的情况下数据量太大已经超过了sort buffer容量

Using temporary:order by group by后面的字段并非都来自查询的第一张表中的字段,就会创建临时表后使用filesort排序

小结:

-出现Using temporary伴随出现Using filesort,Using filesort是可以单独出现的

解决方案:

在where order by group by的字段加上索引,能使用联合索引或者覆盖索引的尽量使用

减少不必要的字段查询,增加join buff中的数据量,减少外层的嵌套循环次数对比

如果加上索引后发现仍然没有走索引,排除索引失效的原因,可以强制使用force索引

--SQL优化之后,性能提升不大的情况下考虑:

修改表结构,进行一定的字段冗余

考虑Mysql的读写分离

考虑引入分布式缓存如redis来存储热门数据等

或者考虑引入第三方框架如sharding-jdbc等来进行分库分表

牛逼点的可以跟你老板说升级硬件,看你老板会抽你不

号外知识点:优化器的漏判和误判

漏判:优化器需要比较不同的执行方案选出最优的,但是在查询表的数量增加的情况下,能执行的计划呈指数增长,索引优化器不可能遍历所有的执行方案。

就如上文介绍的,明明已经排除了索引失效的情况,但是还是没有走索引,这里就存在优化器漏判的情况

误判:优化器在选择执行过程的时候,有一个索引区分度值,值越大,越容易被选中

在innodb引擎下,索引区分度值 = 默认选择N个数据页,统计索引中的不同值数量,求平均值,在与这个索引的数据页相乘得到结果,当变更数据大于1/N时,会重新计算索引区分度值

索引会根据索引区分度值会预判扫描行数,同时回表的代价也会考虑进去

总结:所以优化器选择的执行方案并不一定是最优的

  • 2
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

半生程序员

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值