包学包会，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的数据，减少外层循环的次数）

2. 为被驱动表的匹配条件增加索引（减少内层循环的次数）

3. 用小结果集驱动大结果集（减少外层的循环次数）

4. 可以考虑增大join buffer size（增大驱动表的数据量，避免多次读入表，减少外层循环次数）

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

排序算法：单路排序和双路排序（没有索引的情况下）

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时，会重新计算索引区分度值

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

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