image

SQL性能优化

一、SQL的执行顺序

顺序：FROM——ON——JOIN——WHERE——GROUP BY——SUM、COUNT——HAVING——SELECT——DISTINCT——ORDER BY——LIMIT

与写SQL的顺序不同，SQL的执行顺序并不是从select开始，而是从from开始

1、FROM：先去获取from里面的表，拿到对应的数据，生成虚拟表1。

2、ON：对虚拟表1应用ON筛选，符合条件的数据生成虚拟表2。

3、JOIN：根据JOIN的类型去执行相对应的操作，获取对应的数据，生成虚拟表3。

4、WHERE：对虚拟表3的数据进行条件过滤，符合记录的数据生成虚拟表4。

5、GROUP BY：根据group by中的列，对虚拟表4进行数据分组操作，生成虚拟表5。

6、CUBE|ROLLUP（聚合函数使用）：主要是使用相关的聚合函数，生成虚拟表6。

7、HAVING：对虚拟表6的数据过滤，生成虚拟表7，这个过滤是在where中无法完成的，同时count（expr）返回不为NULL的行数，而count（1）和count（*）是会返回包括NULL在内的行数。

8、SELECT：选择指定的列，生成虚拟表8。

9、DISTINCT：数据去重，生成虚拟表9。

10、ORDER BY：对虚拟表9中的数据进行指定列的排序，生成虚拟表10。

11、LIMIT：取出指定行的记录，生成虚拟表11，返回给查询用户。

以上是SQL各关键词的执行顺序，如果在一条SQL语句里面你没有用到某个关键词那就不会被执行了。理解SQL的逻辑执行顺序对我们在实际写SQL的过程中也会有帮助的。

二、执行计划——EXPLAIN

执行计划，是SQL在数据库中执行时的表现情况,通常用于SQL性能分析,优化等场景。在MySQL使用 explain 关键字来查看SQL的执行计划。

基本的语法：EXPLAIN(select * from table)

在常规SQL语句前面加上EXPLAIN即可

运行结果：

image

参数解释：

1、id：数字越大越先执行，一样大则从上往下执行，如果为NULL则表示是结果集，不需要用来查询。

2、select_type：

simple：不需要union的操作或者是不包含子查询的简单select语句。

primary：需要union操作或者含有子查询的select语句。

union：连接两个select查询，第一个查询是dervied派生表，第二个及后面的表select_type都是union。

dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响。

union result：包含union的结果集。

subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery。

dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。

derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。

3、table

表名，如果是用了别名，则显示别名

4、type

依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

system：表中只有一行数据或者是空表。

const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。

eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。

ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。

fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。

ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。

unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值。

index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引。

index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

5、possible_keys：查询可能使用到的索引。

6、key：查询真正使用到的索引。

7、key_len：用于处理查询的索引长度。

8、ref：常数等值查询显示const，连接查询则显示表的关联字段。

9、rows：执行计划中估算的扫描行数，不是精确值。

10、filtered：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例。

11、extra：该字段信息较多，这里就不一一叙述了。

在实际的使用过程中我们需要重点去关注type、key、key_len、rows、extra这几个参数，type要努力优化到range级别，all要尽量少的出现，在查询的过程中要尽量使用索引，提高效率，在extra里面出现Using filesort, Using temporary是不太好的，要去优化提高性能。

三、优化TIPS

1、尽量少用select *

因为会增加不必要的消耗，select 后面直接加上需要的字段名。

2、IN 包含的值不应过多

IN本身这个操作消耗就比较高，如果IN里面是连续的数值，则可以用between代替，IN里面的字段如果是添加了索引，效率还是可以的，目前测试一万以内还是可以，但是超过了结果可能会有点爆炸，不要问我为什么

3、in和exists、not in 和 not exists

exists以外层表为驱动表，先被访问，适合于外表小而内表大的情况。

in则是先执行子查询，适合外表大而内表小的情况，

一般情况是不推荐使用not in，因为效率非常低，

eg：

1)select * from table_a where table_a.id not in (select table_b.id from table_b)

2)select * from table_a left join table_b on table_a.id = table_b.id where table_b.id is null

语句2的效率是要高于语句1的，SQL的结果是获取到在table_a中存在但是table_b中不存在的数据，如果直接用not in是不走索引的，而且在table_b比较大的时候效率会非常低，实际工作中我试了一下直接not in，然后数据达到一万条的时候大概需要150S左右才能查出数据（感谢DBA和运维不杀之恩），我采取的方法是，先查出两个表的交集，这样得到的表会小很多，而且是用的in，效率会高很多，然后再用not in，最终的效果也是一样，但是时间只要2.56S，然后采取语句2的关联表来处理，时间缩短到了1.42S，基本上效率是比较高的，当然理想的是在1S内。

4、尽量少用or，同时尽量用union all 代替union

or两边的字段如果有不走索引的会导致整个的查询不走索引，从而导致效率低下，这时可以使用union all或者union，而两者的区别是union是将两个结果合并之后再进行唯一性的过滤操作，效率会比union all低很多，但是union all需要两个数据集没有重复的数据。

5、分段和分页查询

在扫描行数较多的情况下可以采取分段查询，循环遍历，结果合并处理，

使用合理的分页方式，在数据表量级逐渐增加的时候，limit分页查询的效率会降低。

1）select id,col from table limit 888888,1000

2）select id,col from table where id > 888887 limit 1000

取前一页的最大行数的id，然后根据这个id来限制下一页的起点。

6、不建议使用%前缀模糊查询

like "%abc"和like "%abc%"会导致索引失效而进行全文搜索。

如果你还有什么比较好的优化tips欢迎分享！