今天来学习一下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的
发表回复