【MYSQL】MYSQL 的学习教程（九）之 23 个 SQL 优化小技巧

这篇文章从 15 个方面，分享了 sql 优化的一些小技巧，希望对你有所帮助

1. 避免使用 select *，务必指明字段名称

在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，

1. 白白浪费了数据库资源，比如：内存、cpu
2. 多查出来的数据，通过网络 IO 传输的过程中，也会增加数据传输的时间
3. 不会走覆盖索引，会出现大量的回表操作，而从导致查询 sql 的性能很低

【优化】：sql 语句查询时，只查需要用到的列，多余的列根本无需查出来

2. 用 union all 代替 union

• sql 语句使用 union 关键字后，可以获取排序、去重后的数据
• union all 关键字，可以获取所有数据，包含重复的数据

排序、去重的过程需要遍历、排序和比较，它更耗时，更消耗 cpu 资源

【优化】：如果能用 union all 的时候，尽量不用 union。除非是有些特殊的场景，比如业务场景中是不允许产生重复数据的

3. 小表驱动大表

小表驱动大表：用小表的数据集驱动大表的数据集

假如有 order 和 user 两张表，其中 order 表有 10000 条数据，而 user 表有 100 条数据
这时如果想查一下，所有有效的用户下过的订单列表

可以使用 in 关键字实现：

select * from order
where user_id in (select id from user where status=1)

也可以使用 exists 关键字实现：

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)

这种业务场景，使用 in关键字去实现业务需求，更加合适。

• 因为如果 sql 语句中包含了 in 关键字，则它会优先执行 in 里面的子查询语句，然后再执行 in 外面的语句。如果 in 里面的数据量很少，作为条件查询速度更快
• 而如果 sql 语句中包含了 exists 关键字，它优先执行 exists 左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了

【总结】：

• in 适用于左边大表，右边小表
• exists 适用于左边小表，右边大表

4. 批量操作

不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在 500 以内。如果数据多于 500，则分多批次处理

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

有时候，我们需要查询某些数据中的第一条，使用 limit 1

6. IN 包含的值不应过多

sql 语句如下：

select id,name from category
where id in (1,2,3...100000000);

如果我们不做任何限制，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。

①：使用 limit 做限制：

select id,name from category
where id in (1,2,3...100)
limit 500;

②：代码层面校验

public List<Category> getCategory(List<Long> ids) {
   if(CollectionUtils.isEmpty(ids)) {
      return null;
   }
   if(ids.size() > 500) {
      throw new BusinessException("一次最多允许查询500条记录")
   }
   return mapper.getCategoryList(ids);
}

③：分批查询
如果 ids 超过 500 条记录，可以分批用多线程去查询数据。每批只查 500 条记录，最后把查询到的数据汇总到一起返回（这只是一个临时方案，不适合于ids实在太多的场景。因为 ids 太多，即使能快速查出数据，但如果返回的数据量太大了，网络传输也是非常消耗性能的，接口性能始终好不到哪里去）

7. 增量查询

有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库

如果直接获取所有的数据，然后同步过去。这样虽说非常方便，但是带来了一个非常大的问题，就是如果数据很多的话，查询性能会非常差

【优化】：

select * from user 
where id> #{lastId} and create_time >= #{lastCreateTime} 
limit 100;

按 id 和时间升序，每次只同步一批数据，这一批数据只有 100 条记录。每次同步完成之后，保存这 100 条数据中最大的 id 和时间，给同步下一批数据的时候用

通过这种增量查询的方式，能够提升单次查询的效率

8. 高效的分页

在 mysql 中分页一般用的 limit 关键字：

select id,name,age 
from user limit 10,20;

如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题

【优化】：

select id,name,age 
from user where id > 1000000 limit 20;

先找到上次分页最大的 id，然后利用 id 上的索引查询。不过该方案，要求id是连续的，并且有序的

9. 用连接查询代替子查询

mysql 中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询 和 连接查询

子查询的例子如下：

select * from order
where user_id in (select id from user where status=1)

子查询语句可以通过 in 关键字实现，一个查询语句的条件落在另一个 select 语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句

子查询语句的优点是简单，结构化，如果涉及的表数量不多的话

但缺点是 mysql 执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗

【优化】：
这时可以改成连接查询。具体例子如下：

select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1

10. join 的表不宜过多

根据阿里巴巴开发者手册的规定，join 表的数量不应该超过 3 个

如果 join 太多，mysql 在选择索引的时候会非常复杂，很容易选错索引，并且如果没有命中中，nested loop join 就是分别从两个表读一行数据进行两两对比，复杂度是 n^2

所以我们应该尽量控制 join 表的数量

如果实现业务场景中需要查询出另外几张表中的数据，可以在 a、b、c 表中冗余专门的字段，比如：在表 a 中冗余 d_name 字段，保存需要查询出的数据

11. join 时要注意

join 使用最多的是 left join、inner join

• inner join：mysql 会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题
• left join：mysql 会默认用 left join 关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题

用 left join 关联查询时，左边要用小表，右边可以用大表。如果能用 inner join 的地方，尽量少用 left join

12. 控制索引的数量

阿里巴巴的开发者手册中规定，单表的索引数量应该尽量控制在 5 个以内，并且单个索引中的字段数不超过 5 个

mysql 使用的 B+ 树的结构来保存索引的，在 insert、update和delete 操作时，需要更新 B+ 树索引。如果索引过多，会消耗很多额外的性能

高并发系统如何优化索引数量？

能够建联合索引，就别建单个索引，可以删除无用的单个索引

将部分查询功能迁移到其他类型的数据库中，比如：ElasticSeach、HBase 等，在业务表中只需要建几个关键索引即可

13. 选择合理的字段类型

我们在选择字段类型时，应该遵循这样的原则：

1. 能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
2. 尽可能使用小的类型，比如：用 bit 存布尔值，用 tinyint 存枚举值等。
3. 长度固定的字符串字段，用 char 类型。
4. 长度可变的字符串字段，用 varchar 类型

14. 提升 group by 的效率

有很多业务场景需要使用 group by 关键字，它主要的功能是去重和分组。通常它会跟 having 一起配合使用，表示分组后再根据一定的条件过滤数据

select user_id,user_name from order
group by user_id
having user_id <= 200;

这种写法性能不好，它先把所有的订单根据用户 id 分组之后，再去过滤用户 id 大于等于 200 的用户

分组是一个相对耗时的操作，为什么我们不先缩小数据的范围之后，再分组呢？

【优化】：

select user_id,user_name from order
where user_id <= 200
group by user_id

使用 where 条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些

WHERE 和 HAVING 区别

1. WHERE 指定行对应得条件， HAVING 指定组对应的条件
2. 先执行 WHERE 子句，后执行 HAVING 子句

SQL 的执行顺序：

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

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

or 两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是 union (必要的时候)的方式来代替“or”会得到更好的效果

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

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

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

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

19. 不建议使用%前缀模糊查询

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

那如何查询%name%？

答案：使用全文索引

20. 避免隐式类型转换

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

在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面

22. 索引优化

很多时候 sql 语句，走了索引，和没有走索引，执行效率差别很大。所以索引优化被作为 sql 优化的首选

1. 查看是否建立索引
2. 查看是否走了索引：EXPLAIN 查看执行计划：【MYSQL】MYSQL 的学习教程（七）之 慢 SQL 优化思路
3. 如果建立索引但没有走索引，那么可能索引失效：【MYSQL】MYSQL 的学习教程（四）之索引失效场景

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