【知识积累】MYSQL之SQL调优

1、EXPLAIN

https://blog.csdn.net/axin1240101543/article/details/86626987

关注属性：type（访问类型 ref：最好 range：至少）、possible_keys（可能使用的索引）、key（实际使用的索引）、key_len（越小越好）、rows（越小越好）、Extra（避免temporary、避免filesort）

2、IN包含的值不宜太多

对于连续的值，建议使用between。

3、Select指定查询字段

Select columnName 代替 Select * ，Select * 增加了很多不必要的消耗（CPU、IO、内存、网络带宽），增加了使用覆盖索引的可能性。

4、当只有一条数据时，使用limit 1

explain的type属性达到const类型。

5、如果排序字段没有用到索引，就尽量少排序。

6、如果限制条件中其他字段没有索引，尽量少用or

or两边的字段有一个不是索引的话，都会造成不走索引的情况。很多时候使用union all（没有重复数据、不需要去重）或者union的方式比or会好的多。

7、尽量使用union all代替union

union是先合并结果集，然后进行合并。

8、不使用order by rand()

9、in与exists、not in与not exists

in与exists区别：驱动方式的不同，in先执行子查询，exists是以外表为驱动表，先被访问。

使用场景：in适用于外表大内表小的查询；exists适用于外表小内表大的查询。

not in与not exists ，推荐使用not exists，不仅仅是效率，not in可能存在逻辑问题。

使用left join代替not in来写SQL：

10、使用合理的分页方式以提高分页效率

优化：根据前一页最大行数id来限制下一页的起点。

11、分段查询

用户选择时间的范围过大，导致扫描行数过多，查询缓慢，这时就可以分段查询，最后将结果合并。

12、避免在where子句中对字段进行null值判断

会导致全表扫描

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

like '%name' 或者 like ‘%name%’，会导致全表扫描，但可以使用like ‘name%’；

那如果要查询怎么办？

使用全文索引！！！

https://www.cnblogs.com/martinzhang/p/3220345.html

（1）、语法：

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

（2）、检索方式：

自然语言检索： IN NATURAL LANGUAGE MODE

布尔检索： IN BOOLEAN MODE

查询扩展检索： WITH QUERY EXPANSION

（3）、条件限制：

在MySQL5.6以下，只有MyISAM表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索；

相应字段建立FULLTEXT索引。

（4）、MySQL不支持中文全文索引

14、避免在where子句中对字段进行表达式操作

15、避免隐式类型转换

where子句中出现column字段的类型和传入的类型不一致的时候发生的类型转换，建议先确定where中参数类型。

16、对于联合索引来说，要遵守最左前缀法则

创建联合索引的时候，注意索引字段顺序，常用的查询字段放在最前面。

17、必要时可以使用force index来强制查询走某个索引

mysql优化器会采取它认为合适的索引来检索sql语句，但可能它采用的索引并不是我们想要的，这就可以使用force index来强制mysql优化器使用我们制定的索引。

18、注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

19、关于JOIN优化

left join：左边为驱动表，右表为匹配表；

inner join：mysql会自动找出那个数据少的表作用驱动表；

right join：左边为匹配表，右表为驱动表。

（1）、mysql中没有full join，可以使用：

（2）、尽量使用inner join，避免left join

参与联合查询的表至少两张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下mysql会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表为驱动表。

（3）、合理使用索引

被驱动表的索引字段作为on的限制字段。

（4）、利用小表去驱动大表

如果能够减少驱动表，减少嵌套循环中的循环次数，这样能减少IO总量及CPU运算的次数。

（5）、巧用straight_join

inner join是由mysql选择驱动表，但是有些特殊情况需要选择另外一张表作为驱动表，比如有group by、order by等【Using filesort】、【Using temporary】时。straight_join来强制连接顺序，在Straight_join左边的表名就是驱动表，右边则是被驱动表。在使用straight_join有个前提条件是内连接，也就是inner join。其他连接不推荐使用straight_join，否则可能造成查询结果不准确。

这个方式有时能减少3倍时间。