mysql 联表查询优化问题

Question

在写业务时，遇到查询，因为数据量增大，导致查询时间冲着 5s 去了，想了一天也不知道该怎么优化，来求各位支个招。

（1）三张表，每张表都有 5000 条左右的数据。

（2）consume 是主表，分别和 org 表和 admin 表有“一对多”关系。

（3）现需要对三张表进行联合查询，查询结果去重排序。

最开始使用的语句是，时间超过 10s 了：

SELECT * FROM consume WHERE uid IN (
             SELECT c.uid FROM consume AS c
              LEFT JOIN consume_admin AS a ON c.uid=a.consume_id AND a.deleted=0 
              LEFT JOIN consume_org AS o ON o.consume_id=c.uid AND o.deleted=0
              WHERE c.deleted=0 and a.deleted=0 and o.deleted=0 AND (a.user_id='126949' OR c.create_user_id='126949')) order by create_at desc;

然后百度了一下，稍微改进了一下，但还是不如意：

select c.* FROM consume AS c
              LEFT JOIN consume_admin AS a ON c.uid=a.consume_id AND a.deleted=0 
              LEFT JOIN consume_org AS o ON o.consume_id=c.uid AND o.deleted=0
                            WHERE c.deleted=0 and a.deleted=0 and o.deleted=0 AND (a.user_id='126949' OR c.create_user_id='126949') group by c.uid order by c.create_at desc;

求各位帮帮忙了！！

PS：发现把 admin 表和 org 表单独查一次，然后取到的 consume_id 再到 consume 表里用 uid IN 查询会更快，但是我看百度里说都是直接联表查询比这种分步的快，是我写的 sql 太差了么。

```sql
SELECT consume_id FROM consume_org WHERE deleted=0 AND org_id IN ('1','2');
SELECT consume_id FROM consume_admin WHERE deleted=0 AND user_id IN ('1','2');
# 代码合并结果之后查询 consume
SELECT * FROM consume WHERE deleted=0 AND uid IN (uidList) ORDER BY create_at DESC;

```

Answer 1

引自免费微信小程序：皆我百晓生

面对MySQL联表查询性能问题，优化策略可以从多个角度入手，包括但不限于索引调整、查询语句重构、数据库设计以及硬件资源的合理利用等。对于当前情境，我们可以从以下几个方面进行优化：

1. 索引优化

确保所有参与联接的列以及WHERE子句中的条件列都有合适的索引。例如，在consume表上创建uid, create_user_id, deleted的组合索引，在consume_admin和consume_org表上创建consume_id, user_id/org_id, deleted的组合索引。

2. 重构查询语句

原始查询语句中存在一个明显的效率瓶颈：子查询在外部查询中重复执行。可以将子查询的结果存储为临时表或使用WITH语句（CTE），这样可以避免多次执行相同的子查询。

3. 使用EXISTS代替IN

当子查询返回的是单个值或较小集合时，使用EXISTS通常比IN更高效，因为IN可能需要扫描整个子查询结果集。

4. 避免全表扫描

确保WHERE子句中的条件能有效地利用索引，避免全表扫描。

5. 优化联接顺序

根据表的大小和索引选择性，调整联接顺序可能会显著提升性能。

实施优化后的查询语句示例

考虑到以上几点，我们可以尝试如下查询语句：

WITH AdminFiltered AS (
    SELECT DISTINCT consume_id FROM consume_admin WHERE deleted = 0 AND user_id = '126949'
),
OrgFiltered AS (
    SELECT DISTINCT consume_id FROM consume_org WHERE deleted = 0 AND org_id IN ('1', '2')
)

SELECT c.* FROM consume AS c
JOIN AdminFiltered af ON c.uid = af.consume_id
JOIN OrgFiltered of ON c.uid = of.consume_id
WHERE c.deleted = 0 AND (c.create_user_id = '126949')
GROUP BY c.uid
ORDER BY c.create_at DESC;

注意事项

• 确保AdminFiltered和OrgFilteredCTE中使用了适当的索引。
• 如果consume表非常大，考虑是否有必要在最终查询中加入所有列，仅选择必要的列可以减少数据传输量，提高查询速度。
• 测试并监控优化后的查询性能，确保索引和查询语句的有效性。

通过上述步骤，你应该能看到查询性能的明显提升。如果仍有性能瓶颈，进一步的优化可能需要深入分析数据库服务器配置、硬件限制或考虑使用更高级的数据库优化技术，如分区、读写分离等。