浅谈MySQL优化技巧

今天来学习一下 MySQL 优化技巧。

1、Explain

善用 explain 查看 SQL 执行计划

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

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

• SELECT * 增加很多不必要的消耗（cpu、io、内存、网络带宽）
• 使用 SELECT * 无法使用覆盖索引

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

MySQL 对于 IN 做了相应的优化，即将IN 中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from table_name where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。

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

• 当你只想要返回一条数据时，加上 LIMIT 1 可以增加性能。这样一样，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
• 这是为了使 EXPLAIN 中 type 列达到 const 类型

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

对于 null 的判断会导致引擎放弃使用索引而进行全表扫描。

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

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

7、避免在 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;

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

举列来说索引含有字段id,name,school，可以直接用 id 字段，也可以 id,name 这样的顺序，但是 name，school 都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

9、注意范围查询语句

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

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

select id,name from table_name limit 866613, 20

使用上述 sql 语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用 limit 分页查询会越来越慢。

优化的方法如下：可以取前一页的最大行数的 id，然后根据这个最大的 id 来限制下一页的起点。比如此列中，上一页最大的 id 是 866612。sql 可以采用如下的写法。

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

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

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

参考

面试官：给我谈谈你平时是如何优化 sql 的