MySQL优化三常用优化方法

查询时，尽量指定查询的字段名

where 条件优化

尽量用 union all 来代替 union

只需要一条数据的情况

limit 分页的优化

count 的优化

优化嵌套查询

但是某些情况下，子查询的效率不高，一般使用 join 来替代子查询。

order by 的优化

查询时，尽量指定查询的字段名

我们在日常使用 select 查询时，尽量使用 select 字段名这种方式，避免直接 select*，这样增加很多不必要的消耗（cpu、io、内存、网络带宽）；而且查询效率比较低。

where 条件优化

避免在 WHERE 字句中对字段进行 NULL 判断

避免在 WHERE 中使用 != 或 <> 操作符

不建议使用 % 前缀模糊查询，例如 LIKE “%name”或者LIKE “%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

避免在 where 中对字段进行表达式操作，比如 select user_id,user_project from table_name where age*2=36 就是一种表达式操作，建议改为**select user_id,user_project from table_name where age=36/2 **

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

尽量用 union all 来代替 union

union 和 union all 的差异主要是，union需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。当然，union all 的前提条件是两个结果集没有重复数据。

只需要一条数据的情况

如果只需要一条数据的情况下，推荐使用 limit 1，这样会使执行计划中的 type 变为 const。

SQL 中 IN 包含的值不应该太多
MySQL 中对 IN 做了相应的优化，MySQL 会将全部的常量存储在一个数组里面，如果数值较多，产生的消耗也会变大，比如

select name from dual where num in(4,5,6)
像这种 SQL 语句的话，能用 between 使用就不要再使用 in 了。

limit 分页的优化

通常我们的系统会进行分页，一般情况下我们会使用 limit 加上偏移量来实现。同时还会加上 order by 语句进行排序。如果使用索引的情况下，效率一般不会有什么问题，如果没有使用索引的话，MySQL 就可能会做大量的文件排序操作。

通常我们可能会遇到比如 limit 1000 , 50 这种情况，抛弃 1000 条，只取 50 条，这样的代价非常高，如果所有页面被访问的频率相同，那么这样的查询平均需要访问半个表的数据。

要优化这种查询，要么限制分页的数量，要么优化大偏移量的性能。

优化的方法有：子查询和记录最大ID。

1、子查询，比如：

select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100

select id,title,content from items where id IN (select id from items order by id limit 900000, 10);

当偏移1000以上使用子查询法可以有效的提高性能。

2、记录最大ID，比如：

日常分页SQL语句
select id,name,content from users order by id asc limit 100000,20
扫描100020行
如果记录了上次的最大ID
select id,name,content from users where id>100073 order by id asc limit 20
扫描20行。

count 的优化

count 我们大家用的太多了，一般都用来统计某一列结果集的行数，当 MySQL 确认括号内的表达式不可能为空时，实际上就是在统计行数。

其实 count 还有另一层统计方式：统计某个列值的数量，在统计列值数量的时候，它默认不会统计 NULL 值。

我们经常犯的一个错误就是，在括号内指定一个列但是却希望统计结果集的行数。如果想要知道结果集行数的话，最好使用 count(*)。

优化嵌套查询

嵌套查询是我们经常使用的一种查询方式，这种查询方式可以使用 SELECT 语句来创建一个单独的查询结果，然后把这个结果当作嵌套语句的查询范围用在另一个查询语句中。使用时子查询可以将一个复杂的查询拆分成一个个独立的部分，逻辑上更易于理解以及代码的维护和重复使用。

但是某些情况下，子查询的效率不高，一般使用 join 来替代子查询。

使用嵌套查询的 SQL 语句进行 explain 分析如下

explain select id from member001 where id not in (select id from member002);

从 explain 的结果可以看出，主表的查询是 index ，子查询是 index_subquery ，这两个执行效率都不高。我们使用 join 来优化后的分析计划如下。

explain select t1.id from member001 t1 left join member002 t2 on t1.id = t2.id;

从 explain 分析结果可以看到，主表查询和子查询分别是 index 和 ref，而 ref 的执行效率相对较高，一般 type 的效率由高到低是 System–>const–>eq_ref–>ref–> fulltext–>ref_or_null–>index_merge–>unique_subquery–>index_subquery–>range–>index–>all 。