MySQL性能优化：SQL优化的15个方面

MySQL性能优化：SQL优化的15个方面

引用

CSDN

1.

https://blog.csdn.net/zhangfuping123456789/article/details/146342796

本篇文章是关于MySQL数据库性能优化的系列文章的第一篇，主要介绍了SQL优化的15个方面。这些优化技巧对于数据库开发人员和运维人员来说，具有较高的实用性和参考价值。

1. 避免使用 select *

原因：

• 查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者 cpu
• 多查出来的数据，通过网络 IO 传输的过程中，也会增加数据传输的时间
• 最重要的问题：
  select *不会走覆盖索引，会出现大量的回表操作，从而导致查询 sql 性能很低

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

select name,age from user where id=1;

2. 用 union all 代替 union

1. union操作符用于合并两个或多个 select 语句的结果集。它可以从多个表中选择数据，并将结果集组合成一个结果集。使用 union 时，所有 select 语句中的列数和数据类型必须一致。

2. 语法：

SELECT column1, column2, ... FROM table1 UNION SELECT column3, column4, ... FROM table2;

3. union 操作符默认去除重复的记录，如果需要保留所有重复记录，可以使用 union all 操作符。

注意几点：
①列数必须相同：
UNION本质上是按行将多个结果集纵向拼接，只有列数一致，才能确保每一行的数据在合并后有正确的列对应关系，如：

-- 正确示例，列数相同
SELECT id, name
FROM users
UNION
SELECT product_id, product_name
FROM products;
-- 错误示例，列数不同
SELECT id, name
FROM users
UNION
SELECT product_id, product_name, price
FROM products;

②对应列数据类型兼容：除了列数相同，对应列的数据类型也必须兼容（兼容：指的是数据类型要么完全相同，要么可以在 MySQL 中进行隐式类型转换）

③列名以第一个select为准：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

4. 特点：
   ① 结果集排序：
   UNION操作后的结果集可以使用ORDER BY子句进行排序。如果没有指定ORDER BY，则结果集的顺序是不确定的。需要注意的是，ORDER BY子句通常放在最后一个SELECT语句之后，它会对整个UNION操作后的结果集进行排序。

② 性能：
UNION操作在合并结果集时可能会有一定的性能开销，特别是在处理大量数据时。这是因为 UNION 默认会去除重复的行，这个去重过程需要额外的计算资源。如果使用 UNION ALL，由于不需要去除重复行，性能可能会更好一些。因此，在实际应用中，如果能够确定合并后的结果集中不会有重复行，或者重复行对业务没有影响，那么优先选择UNION ALL可以提高查询效率。

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 左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

这个需求中，order 表有10000条数据，而 user 表有 100 条数据。order 表是大表，user 表是小表。如果 order表在左边，则用 in 关键字性能更好。总结一下：

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

不管是用 in，还是 exists 关键字，其核心思想都是用小表驱动大表。

select 1 是什么意思：
1、SELECT 1 在子查询（如 EXISTS 子句）中常用于提高查询效率。

• select 1 只是用于测试是否存在满足条件的记录，并不会实际返回数据。
• EXISTS (SELECT 1 FROM ...) 只关心是否有记录存在，不关心具体返回的值，因此 SELECT 1 只是一个占位符。

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

比如上面的 sql 语句的执行流程为：

• SELECT * FROM order 选取所有订单。
• 对于 order 表的每一行，执行 EXISTS 子查询：
• 子查询检查 user 表中是否存在 user.id = order.user_id and status = 1 的记录。
• 如果有匹配记录，EXISTS 语句返回 TRUE，那么 order 记录会被选中；否则被过滤掉。

2、为什么用 SELECT 1 而不是 SELECT *？

• 性能优化：EXISTS 只关心是否有记录匹配，不需要实际查询列的值，所以 SELECT 1 只返回一个常量，比 SELECT * 更高效。
• 避免不必要的字段读取：SELECT * 可能会读取所有列，而 SELECT 1 只是返回一个常量 1，减少数据库负载。

3、可能的优化方案：用 join 代替 exists：如果 user_id 在 user 表和 order 表上有索引，JOIN 可能更高效。

✅ 适合 status = 1 数据量较少的情况。
✅ 适合 order 表较大，但 user 表较小的情况。
❌ 若 user.status = 1 占比较高，考虑用 JOIN 以优化性能。

4. 批量操作

如果你有一批数据经过业务处理之后，需要插入数据库，该怎么办？

// 反例：在循环中逐条插入数据
for(Order order: orderList){   
    orderMapper.insert(order);
}
// 原因：多次请求数据库
// 正例：
orderMapper.insertBatch(orderList);
// 注意：不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。

5. 多用 limit

需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间。

// 反例：
select id, create_date from order where user_id=123 
    order by create_date asc;
List list = orderMapper.getOrderList();
Order order = list.get(0);
// 根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合。然后在代码中，获取第一个元素的数据，即首单的数据，就能获取首单时间。

// 正例：
select id, create_date from order where user_id=123 
    order by create_date asc limit 1;

使用 limit 1，只返回该用户下单时间最小的那一条数据即可。

此外：在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在 sql 语句最后加上 limit。例如：

update order set status=0 where id>=100 and id<200 limit 100;

这样即使误操作，比如把 id 搞错了，也不会对太多的数据造成影响。

6. in 中值不能太多

对于 批量查询 功能的接口，通常会使用 in 关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息。sql 语句如下：

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

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

1、可以在 sql 中对 in 中的数据做 limit 限制：

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

2、不过更多的是要在业务代码中加限制，伪代码如下：

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

3、还有一个方案就是：如果 ids 超过 500 条记录，可以分批用多线程去查询数据。每批只查 500 条记录，最后把查询到的数据汇总到一起返回。

不过这只是一个临时方案，不适合于 ids 实在太多的场景。因为 ids 太多，即使能快速查出数据，但如果返回的数据量太大了，网络传输也是非常消耗性能的，接口性能始终好不到哪里去。

7. 增量查询

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

反例：全量查询：

select * from user;

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

正例：增量查询：按主键 id 和时间 create_time 进行增量查询

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

每次只同步一批数据，这一批数据只有 100 条记录。每次同步完成之后，保存这 100 条数据中最大的 id 和时间，给同步下一批数据的时候用。通过这种增量查询的方式，能够提升单次查询的效率。

① 如何保存 lastId 和 lastCreateTime
方式 1：数据库记录

• 在目标数据库中，创建一个同步进度表，保存 lastId 和 lastCreateTime。
• 每次同步完成后，更新这两个值，供下一次查询使用。

方式 2：缓存存储

• 使用Redis 或本地文件记录 lastId 和 lastCreateTime，避免频繁查询数据库。

② 如何确保增量同步不漏数据

• 边界条件处理

• create_time >= #{lastCreateTime}：使用 >= 而不是 >，确保不会遗漏刚好等于 lastCreateTime 的数据。

• 但是如果数据量大，可能出现重复数据（因为 create_time 相同），需要使用 id > #{lastId} 进一步保证唯一性。

• 索引优化

• 确保 id 是主键或有索引，提升 id > #{lastId} 的查询性能。

• 在 create_time 上添加索引，避免 where create_time >= #{lastCreateTime} 变成全表扫描。