项目中常用的 19 条 MySQL 优化技巧

点击上方SQL数据库开发，关注获取SQL视频教程

作者：zhangqh

来源：segmentfault.com/a/1190000012155267

本文我们来谈谈项目中常用的MySQL优化方法，共19条，具体如下：

一、EXPLAIN

做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。

下面来个简单的示例，标注（1、2、3、4、5）我们要重点关注的数据：

• type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
• key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式
• key_len列，索引长度。
• rows列，扫描行数。该值是个预估值。
• extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

二、SQL 语句中 IN 包含的值不应过多

三、SELECT语句务必指明字段名称

四、当只需要一条数据的时候，使用limit 1

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

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

七、尽量用 union all 代替 union

八、不使用ORDER BY RAND()

select id from `dynamic` 
order by rand() limit 1000;

上面的SQL语句，可优化为：

select id from `dynamic` t1
join 
(select rand() *
       (select max(id) from `dynamic`) as nid
) t2
on t1.id > t2.nid
limit 1000;

九、区分in和exists、not in和not exists

select * from 表A
where id in (select id from 表B)

select * from 表A
where exists(
select * from 表B
where 表B.id=表A.id
)

select colname … from A表
where a.id not in 
(select b.id from B表)

select colname … from A表
left join B表 on a.id = b.id
where b.id is null

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

select id,name from product
limit 866613, 20

select id,name from product
where id> 866612 
limit 20

十一、分段查询

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

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

ALTER TABLE `dynamic_201606` 
ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

select id,fnum,fdst from dynamic_201606
where match(user_name)
against('zhangsan' in boolean mode);

注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

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

select user_id,user_project
from user_base
where age*2=36;

中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：

select user_id,user_project
from user_base
where age=36/2;

十五、避免隐式类型转换

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

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

十八、注意范围查询语句

十九、关于 JOIN 优化

select * from A
left join B on B.name = A.name
where B.name is null
union all
select * from B;

——End——

后台回复关键字：资料领取，获取一份精心整理的技术干货
后台回复关键字：进群，带你进入高手如云的交流群。
推荐阅读
知乎上这个程序员火了，竟是因为给老板修了一 次U盘...
为什么下载电影时，经常会卡在99%？
为什么魂斗罗只有128KB却可以实现那么长的剧情？
一个吊打百度网盘的开源神器，竟然是99年妹子开发的！！！

点击「阅读原文」了解SQL训练营