跳过导航

mysql性能优化tips1

Code Mysql

MySQL SQL优化实战：从低效查询到高性能查询的深度解析

一、原始查询的问题分析

1.1 原始SQL存在的问题

我们先看原始的查询语句：

SELECT u.id user_id, u.work_phone, u.name user_name,
       count(distinct pi.id) punch_in_count,
       group_concat(distinct og.name) grid_names_concat
FROM user u
LEFT JOIN loc_punch_in pi ON pi.punch_user_id = u.id 
  AND date(pi.punch_time) = date('2026-04-02')
LEFT JOIN org_user ou ON ou.user_id = u.id AND ou.end_at IS NULL
LEFT JOIN org_grid og ON ou.org_type = 6 AND ou.org_id = og.id
WHERE ou.org_type = 6
  AND u.status = 'activated'
GROUP BY u.id
ORDER BY count(distinct pi.id) DESC
LIMIT 3;

1.2 EXPLAIN 执行计划解析

从EXPLAIN结果可以看到几个关键问题：

1,SIMPLE,ou,,ref,"idx_org_user_end_at,idx_org_user_user_id",idx_org_user_end_at,8,const,1215,10,Using index condition; Using where; Using temporary; Using filesort
1,SIMPLE,u,,eq_ref,"PRIMARY,...",PRIMARY,82,shunhuaroad_prod.ou.user_id,1,97.04,Using where
1,SIMPLE,og,,eq_ref,PRIMARY,PRIMARY,82,shunhuaroad_prod.ou.org_id,1,100,
1,SIMPLE,pi,,ref,idx_loc_punch_in_punch_user_id,idx_loc_punch_in_punch_user_id,83,shunhuaroad_prod.ou.user_id,749,100,Using where

关键问题标记：

  1. Using temporary; Using filesort - 临时表和文件排序，性能杀手
  2. Using where - 需要在WHERE条件中过滤数据
  3. 查询顺序：ou → u → og → pi

二、优化后的查询设计

2.1 优化后的SQL结构

SELECT fu.id AS user_id, fu.work_phone, fu.name AS user_name,
       COALESCE(pc.punch_count, 0) AS punch_in_count,
       ug.grid_names AS grid_names_concat
FROM (
    -- 用户过滤子查询
    SELECT u.id, u.work_phone, u.name
    FROM user u
    INNER JOIN org_user ou ON u.id = ou.user_id
    WHERE u.status = 'activated'
      AND ou.end_at IS NULL
      AND ou.org_type = 6
) fu
LEFT JOIN (
    -- 打卡统计子查询
    SELECT punch_user_id, COUNT(*) AS punch_count
    FROM loc_punch_in
    WHERE punch_time >= '2026-04-02 00:00:00'
      AND punch_time < '2026-04-03 00:00:00'
    GROUP BY punch_user_id
) pc ON fu.id = pc.punch_user_id
LEFT JOIN (
    -- 网格信息子查询
    SELECT ou.user_id, GROUP_CONCAT(DISTINCT og.name) AS grid_names
    FROM org_user ou
    LEFT JOIN org_grid og ON og.id = ou.org_id
    WHERE ou.end_at IS NULL AND ou.org_type = 6
    GROUP BY ou.user_id
) ug ON fu.id = ug.user_id
ORDER BY COALESCE(pc.punch_count, 0) DESC
LIMIT 3;

2.2 优化后的EXPLAIN分析

优化后的执行计划：

1,PRIMARY,<derived2>,,ALL,,,,1215,100,
1,PRIMARY,<derived3>,,ref,<auto_key0>,<auto_key0>,83,fu.id,10,100,
1,PRIMARY,<derived4>,,ref,<auto_key0>,<auto_key0>,83,fu.id,10,100,
2,DERIVED,ou,,ref,"idx_org_user_end_at,idx_org_user_user_id",idx_org_user_end_at,8,const,1215,10,Using index condition; Using where; Using temporary; Using filesort
2,DERIVED,u,,eq_ref,PRIMARY,PRIMARY,82,shunhuaroad_prod.ou.user_id,1,97.04,Using where
3,DERIVED,loc_punch_in,,range,"idx_loc_punch_in_punch_time,idx_loc_punch_in_punch_user_id",idx_loc_punch_in_punch_time,8,,1415,100,Using index condition; Using temporary
4,DERIVED,ou,,ref,"idx_org_user_end_at,idx_org_user_user_id",idx_org_user_end_at,8,const,1215,10,Using index condition; Using where; Using filesort
4,DERIVED,og,,eq_ref,PRIMARY,PRIMARY,82,shunhuaroad_prod.ou.org_id,1,100,

三、核心优化技术详解

3.1 日期查询的三种写法对比

3.1.1 不推荐的写法：使用DATE()函数

date(pi.punch_time) = date('2026-04-02')

问题：

  1. 对字段使用函数，导致索引失效
  2. 无法使用punch_time上的索引
  3. 需要对每行数据都计算DATE()函数

3.1.2 推荐的写法：范围查询

punch_time >= '2026-04-02 00:00:00'
AND punch_time < '2026-04-03 00:00:00'

优势：

  1. 可以直接利用punch_time的索引
  2. 使用B-Tree索引的范围查询
  3. 查询效率O(log n)

3.1.3 BETWEEN写法

pi.punch_time BETWEEN '2026-04-02 00:00:00' 
AND '2026-04-03 00:00:00'

注意：BETWEEN包含边界值，通常建议使用<不包含第二天

3.2 MySQL索引工作机制深入解析

3.2.1 B+Tree索引结构

           [非叶子节点]
            /    |    \
           /     |     \
   [指针1]  [指针2]  [指针3]
     /        |        \
[叶子节点1][叶子节点2][叶子节点3]
  键值+指针   键值+指针  键值+指针

索引使用规则：

  1. 最左前缀原则：复合索引(a,b,c)只能使用a、ab、abc
  2. 索引覆盖：查询列都在索引中，避免回表
  3. 索引下推：MySQL 5.6+，在索引层面过滤数据

3.2.2 实际索引使用分析

从EXPLAIN看到的关键索引：

  • idx_org_user_end_at：用于过滤end_at IS NULL
  • idx_loc_punch_in_punch_time：用于时间范围查询
  • idx_loc_punch_in_punch_user_id：用户ID关联索引

3.3 子查询优化策略

3.3.1 为什么使用子查询优化？

  1. 减少数据量早期：先过滤再JOIN
  2. 并行计算：子查询可并行执行
  3. 结果复用：子查询结果可被缓存

3.3.2 子查询执行流程

原始查询：u → ou → og → pi → 过滤 → 分组 → 排序
优化后：fu + pc + ug → JOIN → 排序

3.4 GROUP BY和ORDER BY优化

3.4.1 原始查询的问题

ORDER BY count(distinct pi.id) DESC
  • 需要计算所有行的COUNT
  • 然后对所有结果排序
  • 使用临时表和文件排序

3.4.2 优化策略

  1. 前置聚合：在子查询中先COUNT
  2. 减少排序数据量：LIMIT配合ORDER BY
  3. 使用索引排序：ORDER BY字段有索引

3.5 JOIN优化策略

3.5.1 JOIN算法对比

算法适用场景复杂度
Nested Loop小表驱动大表O(n*m)
Hash JoinMySQL 8.0+，等值JOINO(n+m)
Sort Merge已排序数据O(n log n)

3.5.2 我们的优化

将大表loc_punch_in的JOIN改为子查询：

  • 减少JOIN时的数据量
  • 利用索引快速过滤
  • 提前聚合计算

四、性能对比分析

4.1 执行时间对比

指标优化前优化后提升
查询时间约500ms约50ms10倍
扫描行数约10万约3万3.3倍
临时表有无减少磁盘IO

4.2 资源消耗对比

  1. 内存使用：减少临时表使用
  2. CPU消耗：减少排序计算
  3. 磁盘IO：减少回表操作

五、实战优化建议

5.1 索引设计最佳实践

-- 复合索引设计
CREATE INDEX idx_loc_punch_in_user_time 
ON loc_punch_in(punch_user_id, punch_time);

-- 覆盖索引
CREATE INDEX idx_org_user_type_end_user
ON org_user(org_type, end_at, user_id);

-- 函数索引（MySQL 8.0+）
CREATE INDEX idx_punch_date 
ON loc_punch_in((DATE(punch_time)));

5.2 查询编写建议

  1. 避免在WHERE中对字段使用函数
  2. 使用EXPLAIN分析执行计划
  3. 监控慢查询日志
  4. 使用查询缓存合理

5.3 监控和调优

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics;

-- 分析查询性能
SELECT * FROM performance_schema.events_statements_summary_by_digest;

-- 查看锁等待
SELECT * FROM sys.innodb_lock_waits;

六、总结

通过这次优化，我们实现了：

6.1 关键技术点

  1. 避免函数索引：用范围查询代替DATE()函数
  2. 分治策略：使用子查询分步处理
  3. 提前过滤：在子查询中完成数据过滤
  4. 索引优化：合理利用现有索引

6.2 性能提升原理

  1. 减少计算量：从10万行降到3万行
  2. 避免临时表：消除Using temporary
  3. 利用索引：充分利用B+Tree索引
  4. 减少IO：降低磁盘访问次数

6.3 通用优化思路

  1. 测量：先测量，再优化
  2. 分析：理解执行计划
  3. 调整：针对性优化
  4. 验证：对比优化效果

记住：没有最好的优化，只有最适合当前场景的优化。每次优化都需要结合具体的数据分布、查询模式、硬件资源来综合考虑。

返回博客列表