资讯详情

深入了解 · 专业解读

SQL性能优化实战

发布时间：2026/7/11 21:28:39
SQL性能优化实战 一、引言在企业级应用中数据库性能是系统稳定性和用户体验的关键因素。本文基于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子句中对索引列使用函数运算导致索引失效原始SQLSELECTCOUNT(1)aspvCount,provinceasnameFROMstat_t_request_detailsWHEREDATE_FORMAT(create_time,%Y-%m-%d)BETWEEN2024-01-01AND2026-12-31GROUPBYprovince;优化后SELECTCOUNT(1)aspvCount,provinceasnameFROMstat_t_request_detailsWHEREcreate_dateBETWEEN2024-01-01AND2026-12-31GROUPBYprovince;原理新增create_date字段DATE类型存储日期部分使用create_date替代DATE_FORMAT(create_time)使索引生效索引idx_html_province_time_ip_req_1 (html_code, province, create_date)性能对比测试场景原始耗时优化后耗时提升比例日统计(getlist)11,131us470us95.8%月统计(getFORMATList)15,158us1,461us90.3%地域分布(arealDistribute)336us280us16.7%3.2 策略二使用参数绑定防止SQL注入问题使用${}进行字符串替换存在SQL注入风险且无法使用索引原始SQLselectidselectStatTRequestDetailsDTOSELECT ... FROM stat_t_request_details WHERE create_time BETWEEN ${startTime} AND ${endTime}/select优化后selectidselectStatTRequestDetailsDTOSELECT ... FROM stat_t_request_details WHERE create_date BETWEEN #{startTime} AND #{endTime}/select原理#{}使用预编译语句参数通过?占位符传递${}直接字符串拼接存在SQL注入风险参数绑定还能利用数据库的查询缓存3.3 策略三优化COUNT(DISTINCT)问题对主键使用COUNT(DISTINCT id)主键本身唯一DISTINCT是多余的原始SQLSELECTCOUNT(DISTINCTid)aspvFROMstat_t_request_detailsWHEREcreate_date2024-01-15;优化后SELECTCOUNT(id)aspvFROMstat_t_request_detailsWHEREcreate_date2024-01-15;原理主键id本身具有唯一性约束DISTINCT是多余的COUNT(id)直接统计非空值比COUNT(DISTINCT id)少一步去重操作性能对比测试场景原始耗时优化后耗时提升比例日统计(getlist)11,772us381us96.7%3.4 策略四将相关子查询优化为JOIN问题SELECT子句中的子查询会对每行执行一次形成N1查询原始SQLSELECTrequest_code,html_code,(SELECTGROUP_CONCAT(referer)FROMstat_t_request_detailsWHERErequest_codea.request_code)asreferersFROMstat_t_request_details aWHEREcreate_dateCURDATE();优化后SELECTcc.request_code,cc.html_code,c.createurlFROM(SELECTMAX(request_code)asrequest_code,MAX(html_code)ashtml_code,COUNT(1)asnumFROMstat_t_request_details aWHEREcreate_dateCURDATE()ANDrequest_code#{requestCode}ANDhtml_code#{htmlcode})ccLEFTJOIN(SELECTrequest_code,html_code,GROUP_CONCAT(create_time,,,referer)ascreateurlFROMstat_t_request_detailsWHEREcreate_dateCURDATE()GROUPBYrequest_code,html_code)cONc.request_codecc.request_codeANDc.html_codecc.html_code;原理使用子查询先聚合数据减少JOIN次数GROUP_CONCAT一次性拼接所有referer避免多次查询性能对比测试场景原始耗时优化后耗时提升比例详情标题查询(getDetailsTitle)1,530us475us69.0%3.5 策略五使用EXISTS替代COUNT子查询问题(SELECT COUNT(1) FROM ...) 0需要扫描整个子查询结果原始SQLSELECTsiteidasid,web_nameastitle,CASEWHEN(SELECTCOUNT(1)FROMsys_t_rouphtmlcode cWHEREc.groupid#{groupId} AND c.htmlcode a.siteid) 0THENtrueELSEENDascheckedFROMstat_t_web_config a;优化后SELECTsiteidasid,web_nameastitle,CASEWHENEXISTS(SELECT1FROMsys_t_rouphtmlcode cWHEREc.groupid#{groupId} AND c.htmlcode a.siteid)THENtrueELSEENDascheckedFROMstat_t_web_config a;原理EXISTS是半连接操作找到第一条匹配就返回COUNT(1)需要扫描所有匹配行对于是否存在的判断EXISTS效率更高性能对比测试场景原始耗时优化后耗时提升比例分组查询(getrouphtmlcodes)335us208us37.9%3.6 策略六修复逻辑错误问题DATE_FORMAT(create_time, %m)返回01-12的字符串不能与日期范围比较原始SQLSELECTCOUNT(DISTINCTip)ascount,MONTH(create_time)asdateFROMstat_t_request_detailsWHEREDATE_FORMAT(create_time,%m)BETWEEN2024-01-01AND2024-12-31GROUPBYMONTH(create_time);优化后SELECTCOUNT(DISTINCTip)ascount,MONTH(create_time)asdateFROMstat_t_request_detailsWHEREcreate_dateBETWEEN2024-01-01AND2024-12-31GROUPBYMONTH(create_time);原理%m格式符返回月份01-12无法与完整日期比较使用create_date进行正确的日期范围过滤3.7 策略七正确选择过滤字段匹配索引问题使用错误的字段进行过滤导致索引失效原始SQL优化前的错误版本SELECTCOUNT(1)aspvCount,provinceasnameFROMstat_t_request_detailsWHEREweb_app_codesite001-- 错误应该用html_codeGROUPBYprovince;优化后SELECTCOUNT(1)aspvCount,provinceasnameFROMstat_t_request_detailsWHEREhtml_codesite001-- 正确匹配索引idx_html_province_time_ip_req_1GROUPBYprovince;原理表中存在索引idx_html_province_time_ip_req_1 (html_code, province, create_date)使用web_app_code过滤无法使用该索引使用html_code可以利用覆盖索引无需回表性能对比测试场景错误版本耗时修复后耗时提升比例地域分布(arealDistribute)13,377us280us97.9%四、索引优化策略4.1 索引设计原则最左前缀原则联合索引的最左N列可以被利用覆盖索引索引包含查询所需的所有列无需回表避免过多索引索引加速查询但减慢写入选择区分度高的列区分度越高索引筛选效果越好4.2 核心索引设计-- 日统计、月统计等时间范围查询CREATEINDEXidx_create_time_only_1ONstat_t_request_details(create_date);-- 站点时间范围查询主统计查询CREATEINDEXidx_webapp_time_1ONstat_t_request_details(web_app_code,create_date);-- 栏目时间范围查询CREATEINDEXidx_webapp_html_time_1ONstat_t_request_details(web_app_code,html_code,create_date);-- 地域分布查询覆盖索引CREATEINDEXidx_html_province_time_ip_req_1ONstat_t_request_details(html_code,province,create_date,ip,request_code);-- UV/IP统计查询覆盖索引CREATEINDEXidx_webapp_time_ip_req_1ONstat_t_request_details(web_app_code,create_date,ip,request_code);4.3 覆盖索引的威力覆盖索引是指索引包含查询所需的所有列MySQL可以直接从索引中获取数据无需回表查询主键。示例地域分布查询SELECTCOUNT(1)aspvCount,provinceasnameFROMstat_t_request_detailsWHEREhtml_codesite001ANDcreate_dateBETWEEN2024-01-01AND2024-12-31GROUPBYprovince;索引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)性能提升测试1getlist - 日统计11,13147095.8%测试2getFORMATList - 月统计15,1581,46190.3%测试3getDIFFList - 上月统计16,7461,52391.0%测试4getSUBList - 近7天统计64,63130,95052.1%测试5arealDistribute - 地域分布33628016.7%测试6queryChannelTimeTrend - 栏目时间趋势6,8542,99456.3%测试7queryChannelTimeTrendUV - UV时间趋势12,96812,1965.9%测试8getDetailsTitle - 详情标题查询1,53047569.0%测试9getrouphtmlcodes - 分组查询33520837.9%测试10queryHourTimeTrendPv - 小时趋势PV6,1324,32229.5%5.2 优化效果分析平均性能提升约56%最大性能提升97.9%地域分布查询修复后最小性能提升5.9%UV时间趋势因COUNT(DISTINCT)本身开销较大六、常见SQL性能问题排查流程6.1 排查步骤┌─────────────────────────────────────────────────────────────┐ │ SQL性能问题排查流程 │ ├─────────────────────────────────────────────────────────────┤ │ │ │ 发现性能问题 ─────→ 执行EXPLAIN分析 │ │ │ │ │ ▼ │ │ 查看执行计划 │ │ │ │ │ ┌───────────────┴───────────────┐ │ │ ▼ ▼ │ │ typeALL/RANGE keyNULL │ │ (全表扫描/范围扫描) (索引未使用) │ │ │ │ │ │ ▼ ▼ │ │ 添加/优化索引 检查WHERE条件 │ │ │ │ │ │ │ ┌────────┴────────┐ │ │ │ ▼ ▼ │ │ │ 函数运算 类型不匹配 │ │ │ │ │ │ │ │ └────────┬────────┘ │ │ │ │ │ │ └───────────────┬───────────────┘ │ │ ▼ │ │ 重新执行EXPLAIN │ │ │ │ │ ▼ │ │ 验证优化效果 │ │ │ └─────────────────────────────────────────────────────────────┘6.2 EXPLAIN关键字段解读字段含义优化目标type访问类型ALL→range→ref→eq_ref→const/systemkey使用的索引NULL→有值rows扫描行数越小越好Extra额外信息Using index(覆盖索引)最优七、优化工具与技巧7.1 常用工具工具用途EXPLAIN分析SQL执行计划EXPLAIN ANALYZE实际执行并分析MySQL 8.0SHOW PROFILE分析SQL执行开销Performance Schema监控数据库性能7.2 实用技巧使用FORCE INDEX强制使用索引当MySQL优化器选择错误时定期分析表ANALYZE TABLE stat_t_request_details定期重建索引ALTER TABLE stat_t_request_details ENGINEInnoDB开启慢查询日志监控慢SQL八、总结SQL性能优化是一个持续迭代的过程核心要点避免函数运算WHERE子句中不要对索引列使用函数使用参数绑定#{}代替${}防止SQL注入优化COUNT操作主键无需DISTINCT优先使用COUNT(1)或COUNT(id)子查询转JOIN减少N1查询问题EXISTS替代COUNT是否存在判断使用EXISTS更高效覆盖索引索引包含所有查询列避免回表索引匹配确保过滤条件与索引列一致通过以上策略CMS系统的统计查询性能平均提升了56%部分查询提升超过90%显著改善了系统的响应速度和用户体验。

相关新闻