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

落日映苍穹つ 2024-02-05 19:08 85阅读 0赞

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

#### 目录 ####

*  1. 避免使用 select \*，务必指明字段名称
 *  2. 用 union all 代替 union
 *  3. 小表驱动大表
 *  4. 批量操作
 *  5. 当只需要一条数据的时候，使用limit 1
 *  6. IN 包含的值不应过多
 *  7. 增量查询
 *  8. 高效的分页
 *  9. 用连接查询代替子查询
 *  10. join 的表不宜过多
 *  11. join 时要注意
 *  12. 控制索引的数量
 *  13. 选择合理的字段类型
 *  14. 提升 group by 的效率
 *   *  WHERE 和 HAVING 区别
 *  15. 如果排序字段没有用到索引，就尽量少排序
 *  16. 如果限制条件中其他字段没有索引，尽量少用 or
 *  17. 避免在 where 子句中对字段进行 null 值判断
 *  18. 避免在 where 子句中对字段进行表达式操作
 *  19. 不建议使用%前缀模糊查询
 *  20. 避免隐式类型转换
 *  21. 对于联合索引来说，要遵守最左前缀法则
 *  22. 索引优化
 *  23. 必要时可以使用 force index 来强制查询走某个索引

## 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 的执行顺序：

![在这里插入图片描述][f919a5f77c574c72b9de9e3143ee5b32.png]

## 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 优化思路][MYSQL_MYSQL _ _ SQL]
3.  如果建立索引但没有走索引，那么可能索引失效：[【MYSQL】MYSQL 的学习教程（四）之索引失效场景][MYSQL_MYSQL]

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

[f919a5f77c574c72b9de9e3143ee5b32.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b1fed7e41c3644de9551f69e155cd030.png
[MYSQL_MYSQL _ _ SQL]: https://blog.csdn.net/sco5282/article/details/135166069?spm=1001.2014.3001.5501
[MYSQL_MYSQL]: https://blog.csdn.net/sco5282/article/details/135081370?spm=1001.2014.3001.5501