mysql语句优化

优化目标

1.减少 IO 次数

IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

2.降低 CPU 计算

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标

优化方法

1. 学会使用 EXPLAIN 关键字查看执行计划

 

       在性能要求比较高的场景中,杜绝查询中使用临时表

1、未带索引的字段上的group by操作。

2、UNION查询。

3、查询语句中的子查询。

4、部分order by操作，例如distinct和order by一起使用且distinct和order by同一个字段。再例如某些情况下group by和order by字段不同。

具体是否用到临时表，可以通过explain来查看，查看Extra列的结果，如果出现Using temporary则需要注意。

 

where条件优化

where执行顺序是从左往右执行的，遵守原则：排除越多数据的条件放在第一个。

很多时候查询优化器能帮助我们判定 WHERE 条件后的执行顺序,但我们仍应该将选择性高(过滤数据多)的条件放置在 WHERE 语句中的前面，尤其对于复杂的SQL 语句

 

limit分页优化

数据量超过百万、千万, limit 1000000,10特别慢

select * from (select id from table limit 1000000,10) a left join table b on a.id=b.id

 

区分count(column)、count(*)和count(1)的区别

count(column) 统计column非空的量

count(*) 统计所得结果集行数

count(1)和count(*)执行优化器的优化是完全一样的，并没有count (1)会比count (*)快这个说法。

 

in、or和union all的使用场景

对于索引列来最好使用union all，因复杂的查询【包含运算等】将使or、in放弃索引而全表扫描，除非你能确定or、in会使用索引。

对于只有非索引字段来说你就老老实实的用or 或者in，因为非索引字段本来要全表扫描而union all 只成倍增加表扫描的次数。

 

in 和 not in 也要慎用，否则会导致全表扫描

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

 

很多时候用 exists 代替 in 是一个好的选择

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

 

尽量避免前置百分号

Mysql遵循最左前缀原则,like前置百分号会导致全表扫描

 

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

select id from t where num/2=100

应改为:

select id from t where num=100*2

 

创建索引

当索引列有大量数据重复时，SQL查询可能不会去利用索引,如dr字段都是Y、N, 即使在dr上建了索引也对查询效率起不了作用

 

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

 

优化insert语句

一次性执行多个insert语句，尽量使用insert into table values (1,2,3),(2,2,3),(3,3,4)

 

 

定期分析表、优化表和检查表

分析表: ANALYZE TABLE table_name;

优化表: OPTIMIZE TABLE table_name;

检查表: CHECK TABLE table_name;

注意： analyze、check、optimize执行期间将对表进行锁定，因此一定注意要在MySQL数据库不繁忙的时候执行相关的操作。