sql性能调优整理
SQL性能优化 - 张小张的文章 - 知乎 https://zhuanlan.zhihu.com/p/46633318
一、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即可
运行结果：
参数解释：
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%"会导致索引失效而进行全文搜索。
优化你的like语句
日常开发中，如果用到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效。把% 放关键字后面，还是会走索引的
为搜索字段建索引
索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。
5.在Join表的时候使用相当类型的例，并将其索引
如果你的应用程序有很多JOIN查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。
8、应尽量避免在where子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫
10、应尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
19、不要有超过5个以上的表连接
- 连表越多，编译的时间和开销也就越大。
- 把连接表拆开成较小的几个执行，可读性更高。
- 如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。
- select * from A where exists (select 1 from B where A.deptId = B.deptId);
因为exists查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否得意保留。
那么，这样写就等价于：
select * from A,先从A表做循环
select * from B where A.deptId = B.deptId,再从B表做循环.
因此，我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exist。
24、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。
因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。
