SQL性能优化实战

toZxl777

105人浏览 · 2026-07-10 16:43:12

toZxl777 · 2026-07-10 16:43:12 发布

一、引言

在企业级应用中，数据库性能是系统稳定性和用户体验的关键因素。本文基于CMS系统中的实际优化案例，深入探讨SQL性能优化的核心策略和最佳实践。

二、优化前的问题分析

2.1 性能瓶颈识别

在CMS系统的统计模块中，存在以下性能问题：

问题类型	具体表现	影响程度
函数运算导致索引失效	`DATE_FORMAT(create_time)`、`TO_DAYS(create_time)`等函数使索引失效	高
SQL注入风险	使用`${}`字符串替换而非`#{}`参数绑定	高
相关子查询性能差	SELECT子句中的行级子查询	中
COUNT(DISTINCT)滥用	对主键使用`COUNT(DISTINCT id)`	中
逻辑错误	`DATE_FORMAT(create_time, '%m')`与日期范围比较	高
索引不匹配	使用错误的字段进行过滤	高

2.2 性能测试环境

数据库：MySQL 8.0
测试数据量：50,000条记录
表名：stat_t_request_details
测试方法：对比原始SQL与优化后SQL的执行时间

三、核心优化策略

3.1 策略一：避免函数运算导致索引失效

问题：在WHERE子句中对索引列使用函数运算，导致索引失效

原始SQL：

SELECT COUNT(1) as pvCount, province as name
FROM stat_t_request_details
WHERE DATE_FORMAT(create_time, '%Y-%m-%d') BETWEEN '2024-01-01' AND '2026-12-31'
GROUP BY province;

优化后：

SELECT COUNT(1) as pvCount, province as name
FROM stat_t_request_details
WHERE create_date BETWEEN '2024-01-01' AND '2026-12-31'
GROUP BY province;

原理：

新增create_date字段（DATE类型），存储日期部分
使用create_date替代DATE_FORMAT(create_time)，使索引生效
索引：idx_html_province_time_ip_req_1 (html_code, province, create_date)

性能对比：

测试场景	原始耗时	优化后耗时	提升比例
日统计(getlist)	11,131us	470us	+95.8%
月统计(getFORMATList)	15,158us	1,461us	+90.3%
地域分布(arealDistribute)	336us	280us	+16.7%

3.2 策略二：使用参数绑定防止SQL注入

问题：使用${}进行字符串替换，存在SQL注入风险且无法使用索引

原始SQL：

<select id="selectStatTRequestDetailsDTO">
    SELECT ...
    FROM stat_t_request_details
    WHERE create_time BETWEEN ${startTime} AND ${endTime}
</select>

优化后：

<select id="selectStatTRequestDetailsDTO">
    SELECT ...
    FROM stat_t_request_details
    WHERE create_date BETWEEN #{startTime} AND #{endTime}
</select>

原理：

#{}使用预编译语句，参数通过?占位符传递
${}直接字符串拼接，存在SQL注入风险
参数绑定还能利用数据库的查询缓存

3.3 策略三：优化COUNT(DISTINCT)

问题：对主键使用COUNT(DISTINCT id)，主键本身唯一，DISTINCT是多余的

原始SQL：

SELECT COUNT(DISTINCT id) as pv
FROM stat_t_request_details
WHERE create_date = '2024-01-15';

优化后：

SELECT COUNT(id) as pv
FROM stat_t_request_details
WHERE create_date = '2024-01-15';

原理：

主键id本身具有唯一性约束，DISTINCT是多余的
COUNT(id)直接统计非空值，比COUNT(DISTINCT id)少一步去重操作

性能对比：

测试场景	原始耗时	优化后耗时	提升比例
日统计(getlist)	11,772us	381us	+96.7%

3.4 策略四：将相关子查询优化为JOIN

问题：SELECT子句中的子查询会对每行执行一次，形成N+1查询

原始SQL：

SELECT 
    request_code,
    html_code,
    (SELECT GROUP_CONCAT(referer) FROM stat_t_request_details 
     WHERE request_code = a.request_code) as referers
FROM stat_t_request_details a
WHERE create_date = CURDATE();

优化后：

SELECT 
    cc.request_code,
    cc.html_code,
    c.createurl
FROM (
    SELECT 
        MAX(request_code) as request_code,
        MAX(html_code) as html_code,
        COUNT(1) as num
    FROM stat_t_request_details a 
    WHERE create_date = CURDATE()
      AND request_code = #{requestCode} 
      AND html_code = #{htmlcode}
) cc
LEFT JOIN (
    SELECT 
        request_code, 
        html_code, 
        GROUP_CONCAT(create_time, ',', referer) as createurl
    FROM stat_t_request_details 
    WHERE create_date = CURDATE()
    GROUP BY request_code, html_code
) c ON c.request_code = cc.request_code AND c.html_code = cc.html_code;

原理：

使用子查询先聚合数据，减少JOIN次数
GROUP_CONCAT一次性拼接所有referer，避免多次查询

性能对比：

测试场景	原始耗时	优化后耗时	提升比例
详情标题查询(getDetailsTitle)	1,530us	475us	+69.0%

3.5 策略五：使用EXISTS替代COUNT子查询

问题：(SELECT COUNT(1) FROM ...) > 0需要扫描整个子查询结果

原始SQL：

SELECT 
    siteid as id,
    web_name as title,
    CASE WHEN (SELECT COUNT(1) FROM sys_t_rouphtmlcode c 
               WHERE c.groupid = #{groupId} AND c.htmlcode = a.siteid) > 0 
         THEN 'true' ELSE '' END as checked
FROM stat_t_web_config a;

优化后：

SELECT 
    siteid as id,
    web_name as title,
    CASE WHEN EXISTS (
        SELECT 1 FROM sys_t_rouphtmlcode c 
        WHERE c.groupid = #{groupId} AND c.htmlcode = a.siteid
    ) THEN 'true' ELSE '' END as checked
FROM stat_t_web_config a;

原理：

EXISTS是半连接操作，找到第一条匹配就返回
COUNT(1)需要扫描所有匹配行
对于"是否存在"的判断，EXISTS效率更高

性能对比：

测试场景	原始耗时	优化后耗时	提升比例
分组查询(getrouphtmlcodes)	335us	208us	+37.9%

3.6 策略六：修复逻辑错误

问题：DATE_FORMAT(create_time, '%m')返回01-12的字符串，不能与日期范围比较

原始SQL：

SELECT COUNT(DISTINCT ip) as count, MONTH(create_time) as date
FROM stat_t_request_details
WHERE DATE_FORMAT(create_time, '%m') BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY MONTH(create_time);

优化后：

SELECT COUNT(DISTINCT ip) as count, MONTH(create_time) as date
FROM stat_t_request_details
WHERE create_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY MONTH(create_time);

原理：

%m格式符返回月份（01-12），无法与完整日期比较
使用create_date进行正确的日期范围过滤

3.7 策略七：正确选择过滤字段匹配索引

问题：使用错误的字段进行过滤，导致索引失效

原始SQL（优化前的错误版本）：

SELECT COUNT(1) as pvCount, province as name
FROM stat_t_request_details
WHERE web_app_code = 'site001'  -- 错误：应该用html_code
GROUP BY province;

优化后：

SELECT COUNT(1) as pvCount, province as name
FROM stat_t_request_details
WHERE html_code = 'site001'  -- 正确：匹配索引idx_html_province_time_ip_req_1
GROUP BY province;

原理：

表中存在索引idx_html_province_time_ip_req_1 (html_code, province, create_date)
使用web_app_code过滤无法使用该索引
使用html_code可以利用覆盖索引，无需回表

性能对比：

测试场景	错误版本耗时	修复后耗时	提升比例
地域分布(arealDistribute)	13,377us	280us	+97.9%

四、索引优化策略

4.1 索引设计原则

最左前缀原则：联合索引的最左N列可以被利用
覆盖索引：索引包含查询所需的所有列，无需回表
避免过多索引：索引加速查询但减慢写入
选择区分度高的列：区分度越高，索引筛选效果越好

4.2 核心索引设计

-- 日统计、月统计等时间范围查询
CREATE INDEX idx_create_time_only_1 ON stat_t_request_details(create_date);

-- 站点+时间范围查询（主统计查询）
CREATE INDEX idx_webapp_time_1 ON stat_t_request_details(web_app_code, create_date);

-- 栏目+时间范围查询
CREATE INDEX idx_webapp_html_time_1 ON stat_t_request_details(web_app_code, html_code, create_date);

-- 地域分布查询（覆盖索引）
CREATE INDEX idx_html_province_time_ip_req_1 
    ON stat_t_request_details(html_code, province, create_date, ip, request_code);

-- UV/IP统计查询（覆盖索引）
CREATE INDEX idx_webapp_time_ip_req_1 
    ON stat_t_request_details(web_app_code, create_date, ip, request_code);

4.3 覆盖索引的威力

覆盖索引是指索引包含查询所需的所有列，MySQL可以直接从索引中获取数据，无需回表查询主键。

示例：地域分布查询

SELECT COUNT(1) as pvCount, province as name
FROM stat_t_request_details
WHERE html_code = 'site001'
  AND create_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY province;

索引：idx_html_province_time_ip_req_1 (html_code, province, create_date, ip, request_code)

执行过程：

使用html_code = 'site001'定位索引起始位置
使用create_date范围过滤
按province分组统计，索引已排序，无需额外排序
所有数据都在索引中，无需回表

五、性能测试结果汇总

5.1 优化前后性能对比

测试编号	测试名称	原始耗时(us)	优化后耗时(us)	性能提升
测试1	getlist - 日统计	11,131	470	+95.8%
测试2	getFORMATList - 月统计	15,158	1,461	+90.3%
测试3	getDIFFList - 上月统计	16,746	1,523	+91.0%
测试4	getSUBList - 近7天统计	64,631	30,950	+52.1%
测试5	arealDistribute - 地域分布	336	280	+16.7%
测试6	queryChannelTimeTrend - 栏目时间趋势	6,854	2,994	+56.3%
测试7	queryChannelTimeTrendUV - UV时间趋势	12,968	12,196	+5.9%
测试8	getDetailsTitle - 详情标题查询	1,530	475	+69.0%
测试9	getrouphtmlcodes - 分组查询	335	208	+37.9%
测试10	queryHourTimeTrendPv - 小时趋势PV	6,132	4,322	+29.5%

5.2 优化效果分析

平均性能提升：约56%
最大性能提升：97.9%（地域分布查询修复后）
最小性能提升：5.9%（UV时间趋势，因COUNT(DISTINCT)本身开销较大）

六、常见SQL性能问题排查流程

6.1 排查步骤

┌─────────────────────────────────────────────────────────────┐
│                    SQL性能问题排查流程                        │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  发现性能问题 ─────→ 执行EXPLAIN分析                         │
│                              │                               │
│                              ▼                               │
│                    查看执行计划                              │
│                              │                               │
│              ┌───────────────┴───────────────┐              │
│              ▼                               ▼              │
│         type=ALL/RANGE                  key=NULL            │
│         (全表扫描/范围扫描)              (索引未使用)         │
│              │                               │              │
│              ▼                               ▼              │
│         添加/优化索引                   检查WHERE条件        │
│              │                               │              │
│              │                      ┌────────┴────────┐      │
│              │                      ▼                 ▼      │
│              │                 函数运算          类型不匹配    │
│              │                      │                 │      │
│              │                      └────────┬────────┘      │
│              │                               │              │
│              └───────────────┬───────────────┘              │
│                              ▼                               │
│                       重新执行EXPLAIN                        │
│                              │                               │
│                              ▼                               │
│                       验证优化效果                           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

6.2 EXPLAIN关键字段解读

字段	含义	优化目标
type	访问类型	ALL→range→ref→eq_ref→const/system
key	使用的索引	NULL→有值
rows	扫描行数	越小越好
Extra	额外信息	Using index(覆盖索引)最优

七、优化工具与技巧

7.1 常用工具

工具	用途
EXPLAIN	分析SQL执行计划
EXPLAIN ANALYZE	实际执行并分析（MySQL 8.0+）
SHOW PROFILE	分析SQL执行开销
Performance Schema	监控数据库性能

7.2 实用技巧

使用FORCE INDEX强制使用索引：当MySQL优化器选择错误时
定期分析表：ANALYZE TABLE stat_t_request_details
定期重建索引：ALTER TABLE stat_t_request_details ENGINE=InnoDB
开启慢查询日志：监控慢SQL

八、总结

SQL性能优化是一个持续迭代的过程，核心要点：

避免函数运算：WHERE子句中不要对索引列使用函数
使用参数绑定：#{}代替${}，防止SQL注入
优化COUNT操作：主键无需DISTINCT，优先使用COUNT(1)或COUNT(id)
子查询转JOIN：减少N+1查询问题
EXISTS替代COUNT："是否存在"判断使用EXISTS更高效
覆盖索引：索引包含所有查询列，避免回表
索引匹配：确保过滤条件与索引列一致

通过以上策略，CMS系统的统计查询性能平均提升了56%，部分查询提升超过90%，显著改善了系统的响应速度和用户体验。

AI硬件创业社区

智能硬件社区聚焦AI智能硬件技术生态，汇聚嵌入式AI、物联网硬件开发者，打造交流分享平台，同步全国赛事资讯、开展 OPC 核心人才招募，助力技术落地与开发者成长。

更多推荐

cover

6.6.多节点安全监护仪-WiFi-基于STM32单片机物联网设计【硬件+APP+云平台】

AI硬件创业社区

cover

【单片机毕业设计】基于 STM32/51 单片机的 AI 语音智能垃圾分类桶设计与实现，基于 SU-03T 语音识别模块的自动分类垃圾桶控制系统开发（025101）

AI硬件创业社区

cover

基于STM32单片机定位报站系统GPS北斗公交车蓝牙无线APP/WiFi无线APP/摄像头视频监控设计DIY176

AI硬件创业社区

所有评论(0)

查看更多评论

toZxl777

已为社区贡献1条内容