数据库索引优化与慢查询排查实战：1000名工人工单工单系统性能攻坚

数据库索引优化与慢查询排查实战千人施工队工单系统性能攻坚场景某建筑集团大型商业综合体项目规模1000名工人日均生成3000工单工单表累计800万记录痛点早班派工高峰期系统卡顿工单查询超时一、业务背景千人施工队的工单风暴1.1 项目概况某商业综合体项目进入主体施工阶段现场同时开工木工班组200人模板支设、拆除钢筋班组180人钢筋绑扎、焊接混凝土班组150人浇筑、养护水电安装120人预埋、管线敷设砌筑班组200人砌体施工其他辅助150人搬运、清理、质检每日工单流转06:30 早班会 → 班组长派工创建工单→ 1500 次写入 08:00-12:00 施工过程 → 进度填报、问题上报 → 3000 次更新 14:00-18:00 下午施工 → 同上 17:30 验收 → 质检员验收工单 → 1500 次状态更新 20:00 报表 → 项目经理查询统计 → 复杂聚合查询1.2 核心瓶颈表work_orders工单表-- 工单表800万记录日增3000核心瓶颈CREATETABLEwork_orders(order_idBIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT工单ID,order_codeVARCHAR(50)NOTNULLCOMMENT工单编号如 GD-20240329-MG-001,project_idINTNOTNULLCOMMENT项目ID固定值本项目为1,-- 工人与班组信息查询高频worker_idINTNOTNULLCOMMENT工人ID1000人范围,team_idINTNOTNULLCOMMENT班组ID约20个班组,team_typeENUM(木工,钢筋,混凝土,水电,砌筑,辅助)NOTNULL,-- 工单内容业务核心work_areaVARCHAR(100)COMMENT施工区域如 A区-3层-梁板,work_contentTEXTCOMMENT工作内容描述,work_typeENUM(主体,装修,安装,市政,其他)NOTNULL,-- 状态流转索引关键order_statusTINYINTNOTNULLDEFAULT0COMMENT0:待开工 1:施工中 2:待验收 3:已完成 4:已结算,quality_statusTINYINTDEFAULT0COMMENT质量验收0:未检 1:合格 2:整改 3:复检,safety_statusTINYINTDEFAULT0COMMENT安全验收0:未检 1:合格 2:隐患,-- 时间节点范围查询多plan_dateDATENOTNULLCOMMENT计划施工日期,plan_start_timeTIMECOMMENT计划开始时间,plan_end_timeTIMECOMMENT计划结束时间,actual_startDATETIMECOMMENT实际开始时间,actual_endDATETIMECOMMENT实际结束时间,-- 工程量与成本聚合计算多work_quantityDECIMAL(10,2)COMMENT工程量如 50.5 平方米,unitVARCHAR(20)COMMENT单位平方米/立方米/米/个,unit_priceDECIMAL(8,2)COMMENT单价,total_amountDECIMAL(12,2)COMMENT总金额,-- 审计字段created_byINTCOMMENT派工员ID,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,-- 初始索引仅主键INDEXidx_worker(worker_id),INDEXidx_status(order_status))ENGINEInnoDBDEFAULTCHARSETutf8mb4 ROW_FORMATDYNAMIC;-- 表统计信息模拟-- 总记录数8,000,000-- 数据大小约 4.5GB-- 索引大小约 800MB1.3 性能危机早班派工高峰系统假死周一早 06:30-07:00系统监控告警指标正常时段早班高峰状态CPU使用率25%98% 危险内存使用60%85% 警告磁盘I/O200 IOPS3500 IOPS 危险活跃连接数50280 危险慢查询数/分钟2150 危险用户反馈班组长“派工页面转圈30秒工人等着领活干着急”质检员“验收列表加载不出来活干完了验不了”项目经理“昨日完成产值报表一直超时”二、诊断阶段慢查询排查实战2.1 抓取慢查询日志-- 开启慢查询生产环境已开启调整阈值SETGLOBALlong_query_time0.5;-- 降到0.5秒捕获更多问题SETGLOBALlog_queries_not_using_indexes1;-- 查看当前慢查询SELECTDIGEST_TEXTassql_template,COUNT_STARasexec_count,AVG_TIMER_WAIT/1000000000asavg_time_ms,MAX_TIMER_WAIT/1000000000asmax_time_ms,SUM_ROWS_EXAMINEDastotal_rows_examinedFROMperformance_schema.events_statements_summary_by_digestWHERESCHEMA_NAMEconstruction_dbAND(AVG_TIMER_WAIT/1000000000500ORSUM_ROWS_EXAMINED100000)ORDERBYAVG_TIMER_WAITDESCLIMIT10;TOP 3 慢查询排名SQL类型执行次数平均耗时扫描行数问题1工人当日工单查询1200次/小时4.2s8,000,000全表扫描2班组进度统计200次/小时6.8s8,000,000临时表文件排序3待验收工单列表300次/小时3.5s5,200,000索引未命中2.2 深度剖析工人当日工单查询业务场景工人扫码查看今日我的工单-- 原SQLORM生成存在性能陷阱SELECT*FROMwork_ordersWHEREworker_id886ANDplan_date2024-03-29ANDorder_statusIN(0,1)-- 待开工或施工中ORDERBYplan_start_timeASC;EXPLAIN 分析EXPLAINSELECT*FROMwork_ordersWHEREworker_id886ANDplan_date2024-03-29ANDorder_statusIN(0,1)ORDERBYplan_start_timeASC;执行计划idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEwork_ordersrefidx_workeridx_worker4const8000Using where; Using filesort问题诊断key idx_worker只用到了 worker_id 索引rows 8000该工人历史工单8000条2年多数据Using whereplan_date 和 order_status 在Server层过滤Using filesort对8000条记录内存排序实际返回仅3条记录却扫描了8000行2.3 可视化执行计划使用 MySQL Workbench 的 Visual Explain 直观展示图示说明红色方块Full Table Scan变成了黄色Non-Unique Key Lookup但仍有Using filesort警告内存排序节点查询成本Query Cost高达2400.0三、索引优化实战从4秒到10毫秒3.1 索引设计策略千人施工队业务特征业务查询特征分析 ├─ 工人维度worker_id1000人 plan_date当日→ 高并发点查 ├─ 班组维度team_id20个 plan_date → 批量查询 ├─ 状态维度order_status5种状态→ 经常组合查询 ├─ 时间维度plan_date近3年数据→ 范围查询 ├─ 区域维度work_areaA区/B区/C区→ 模糊匹配少 └─ 复合场景班组日期状态 → 派工统计3.2 实战案例 1工人当日工单查询优化问题根源单列索引idx_worker无法支持多条件筛选优化方案-- 步骤1删除低效单列索引DROPINDEXidx_workerONwork_orders;DROPINDEXidx_statusONwork_orders;-- 步骤2创建最优联合索引-- 分析worker_id1000区分度 plan_date每日 order_status筛选-- 顺序等值查询列在前范围/排序在后CREATEINDEXidx_worker_date_status_startONwork_orders(worker_id,plan_date,order_status,plan_start_time);-- 步骤3优化查询避免SELECT *使用覆盖索引SELECTorder_id,order_code,work_area,work_content,order_status,plan_start_time,plan_end_time,work_quantity,unitFROMwork_ordersWHEREworker_id886ANDplan_date2024-03-29ANDorder_statusIN(0,1)ORDERBYplan_start_timeASC;优化后 EXPLAINidselect_typetabletypekeykey_lenrefrowsExtra1SIMPLEwork_ordersrefidx_worker_date_status_start14const,const3Using index condition; Using index关键改进rows 3精确命中3条记录该工人当日2个待开工1个施工中Using index condition索引条件下推ICP在存储引擎层过滤 statusUsing index覆盖索引无需回表查主键性能对比指标优化前优化后提升扫描行数8,000399.96%↓执行时间4,200ms12ms350倍是否回表是否覆盖索引早班高峰并发卡死流畅支持300并发3.3 实战案例 2班组进度统计优化业务场景班组长查看本班组今日各状态工单统计问题 SQL-- 原SQL统计钢筋班组今日各状态工单数量和产值SELECTorder_status,COUNT(*)asorder_count,SUM(work_quantity)astotal_quantity,SUM(total_amount)astotal_amount,AVG(total_amount)asavg_amountFROMwork_ordersWHEREteam_id3-- 钢筋一班ANDplan_date2024-03-29GROUPBYorder_status;原执行计划全表扫描800万行使用临时表和文件排序耗时6.8秒优化方案-- 创建针对性联合索引-- 分析team_id20区分度 plan_date每日 order_status分组-- 包含列work_quantity, total_amount覆盖聚合计算CREATEINDEXidx_team_date_status_coverONwork_orders(team_id,plan_date,order_status,work_quantity,total_amount);-- 优化后执行计划-- type: ref-- key: idx_team_date_status_cover-- rows: 45该班组今日45个工单-- Extra: Using index完全覆盖无需回表效果扫描行数45行该班组当日实际工单数执行时间15ms支持早班高峰200班组长同时查询3.4 实战案例 3待验收工单列表优化分页陷阱业务场景质检员查看待验收工单支持分页问题 SQL深分页性能灾难-- 第50页每页20条查看昨日待验收工单SELECT*FROMwork_ordersWHEREorder_status2-- 待验收ANDquality_status0-- 未质检ORDERBYplan_dateDESC,created_atDESCLIMIT980,20;问题分析LIMIT 980, 20需要扫描1000行再丢弃前980行order_status 2命中约15万条记录历史累积随着页码增加性能线性下降第100页直接超时优化方案延迟关联Deferred Join-- 步骤1创建支持排序的索引CREATEINDEXidx_status_quality_dateONwork_orders(order_status,quality_status,plan_date,created_at,order_id);-- 步骤2改写SQL先查ID再关联详情SELECTw.*FROMwork_orders wINNERJOIN(SELECTorder_idFROMwork_ordersWHEREorder_status2ANDquality_status0ORDERBYplan_dateDESC,created_atDESCLIMIT980,20)AStmpONw.order_idtmp.order_id;进一步优化游标分页业务推荐-- 首次查询第1页SELECTorder_id,order_code,work_area,worker_id,plan_date,created_atFROMwork_ordersWHEREorder_status2ANDquality_status0ORDERBYplan_dateDESC,created_atDESCLIMIT20;-- 记录最后一条plan_date2024-03-28, created_at2024-03-28 16:30:00, order_id7854321-- 下一页避免深分页SELECTorder_id,order_code,work_area,worker_id,plan_date,created_atFROMwork_ordersWHEREorder_status2ANDquality_status0AND(plan_date2024-03-28OR(plan_date2024-03-28ANDcreated_at2024-03-28 16:30:00)OR(plan_date2024-03-28ANDcreated_at2024-03-28 16:30:00ANDorder_id7854321))ORDERBYplan_dateDESC,created_atDESC,order_idDESCLIMIT20;分页性能对比页码优化前LIMIT优化后游标扫描行数第1页800ms15ms20第10页2.1s16ms20第50页6.8s18ms20第100页超时20ms203.5 实战案例 4派工冲突检测唯一索引优化业务场景防止同一工人在同一时间被重复派工问题并发派工时出现重复工单同一工人同一时段两个工单解决方案-- 添加唯一索引防止重复派工-- 业务规则同一工人同一天同一时段只能有一个工单ALTERTABLEwork_ordersADDUNIQUEINDEXuk_worker_date_start(worker_id,plan_date,plan_start_time);-- 派工SQL使用INSERT IGNORE或ON DUPLICATE KEY UPDATEINSERTINTOwork_orders(worker_id,plan_date,plan_start_time,work_area,work_content,order_status)VALUES(886,2024-03-29,08:00:00,A区-3层-梁板,钢筋绑扎,0)ONDUPLICATEKEYUPDATEwork_areaVALUES(work_area),-- 更新为新派工区域updated_atNOW();效果彻底杜绝重复派工利用数据库唯一性约束无需应用层锁早班高峰300并发派工无冲突四、高级优化索引下推与查询重构4.1 索引下推ICP实战场景查询钢筋班组在A区-3层且工程量大于50平方米的待开工工单-- 索引idx_team_type_area_quantity (team_id, team_type, work_area, work_quantity)SELECT*FROMwork_ordersWHEREteam_id3ANDteam_type钢筋ANDwork_areaLIKEA区-3层%ANDwork_quantity50ANDorder_status0;ICP 原理对比无ICPMySQL 5.5及以前 1. 索引命中 team_id3 AND team_type钢筋 的记录约200条 2. 回表查询200条完整记录 3. Server层过滤 work_area LIKE A区-3层% 和 work_quantity 50 有ICPMySQL 5.6 1. 索引命中 team_id3 AND team_type钢筋 2. 在存储引擎层用索引中的 work_area 和 work_quantity 过滤 3. 只需回表符合条件的记录可能只有15条验证ICP是否启用EXPLAINSELECT*FROMwork_ordersWHEREteam_id3ANDteam_type钢筋ANDwork_areaLIKEA区-3层%ANDwork_quantity50;-- Extra列显示Using index condition表示ICP生效4.2 查询重构避免索引失效反例1隐式类型转换-- 错误worker_id 是INT但传入字符串SELECT*FROMwork_ordersWHEREworker_id886;-- 实际执行CAST(worker_id AS CHAR) 886索引失效-- 正确SELECT*FROMwork_ordersWHEREworker_id886;反例2对索引列使用函数-- 错误查询本月工单SELECT*FROMwork_ordersWHEREMONTH(plan_date)3ANDYEAR(plan_date)2024;-- 索引失效全表扫描-- 正确范围查询SELECT*FROMwork_ordersWHEREplan_date2024-03-01ANDplan_date2024-04-01;反例3前导模糊查询-- 错误模糊匹配在前SELECT*FROMwork_ordersWHEREwork_areaLIKE%3层%;-- 索引失效-- 正确后缀匹配如果业务允许SELECT*FROMwork_ordersWHEREwork_areaLIKEA区-3层%;-- 走索引范围扫描五、索引维护与监控体系5.1 索引健康检查脚本-- 检查索引选择性每周执行SELECTTABLE_NAME,INDEX_NAME,COLUMN_NAME,CARDINALITY,TABLE_ROWS,ROUND(CARDINALITY/TABLE_ROWS,4)asselectivityFROMinformation_schema.STATISTICSWHERETABLE_SCHEMAconstruction_dbANDTABLE_NAMEwork_ordersORDERBYselectivityASC;-- 选择性低于0.1的索引考虑删除如order_status单列索引5.2 冗余索引清理-- 查找冗余索引已有联合索引包含单列索引SELECTredundant_index_name,dominant_index_name,sql_drop_indexFROMsys.schema_redundant_indexesWHEREtable_namework_orders;-- 示例输出-- redundant_index_name: idx_worker-- dominant_index_name: idx_worker_date_status_start-- sql_drop_index: ALTER TABLE work_orders DROP INDEX idx_worker5.3 索引使用监控-- 查看索引实际使用情况performance_schemaSELECTOBJECT_NAMEastable_name,OBJECT_SCHEMAasdb_name,INDEX_NAME,COUNT_FETCHasindex_reads,COUNT_INSERTasindex_inserts,COUNT_UPDATEasindex_updatesFROMperformance_schema.table_io_waits_summary_by_index_usageWHEREOBJECT_NAMEwork_ordersANDINDEX_NAMEISNOTNULLORDERBYCOUNT_FETCHDESC;-- 从未使用的索引考虑删除SELECT*FROMsys.schema_unused_indexesWHEREtable_namework_orders;六、实战成果早班高峰不再卡顿6.1 性能提升总览业务场景优化前优化后提升倍数早班并发支持工人当日工单查询4.2s12ms350x300班组进度统计6.8s15ms453x200待验收工单列表3.5s18ms194x150派工冲突检测超时25ms∞500产值日报生成12s800ms15x506.2 系统资源对比指标优化前早班高峰优化后早班高峰改善CPU使用率98%35%→内存使用85%60%磁盘IOPS3500450→慢查询/分钟1500-2→接口P99延迟8s120ms→6.3 索引设计黄金法则千人施工队版1. 工人维度优先worker_id 放在联合索引最左1000人高频查询 2. 日期紧随其后plan_date 支持每日派工、验收、统计 3. 状态区分场景order_status 区分待开工/施工中/待验收 4. 覆盖索引为王统计查询包含 work_quantity, total_amount 避免回表 5. 避免重复派工唯一索引 (worker_id, plan_date, plan_start_time) 6. 分页游标化深分页使用游标避免 LIMIT offset 性能陷阱七、工具箱与应急方案7.1 日常工具工具命令用途EXPLAINEXPLAIN FORMATJSON SELECT ...分析执行计划SHOW PROFILESSET profiling1; ...; SHOW PROFILES;查询耗时分析pt-query-digestpt-query-digest slow.log慢日志分析sys schemaSELECT * FROM sys.statements_with_full_table_scans全表扫描定位7.2 应急处理线上加索引不锁表-- MySQL 5.6 支持 Online DDL但大表仍需谨慎-- 使用 pt-online-schema-change 避免锁表pt-online-schema-change \--alter ADD INDEX idx_worker_date_status (worker_id, plan_date, order_status) \--execute \--max-load Threads_running50 \--critical-load Threads_running80 \--chunk-size1000 \Dconstruction_db,twork_orders;7.3 索引优化清单Checklist是否使用了覆盖索引Using index是否避免了全表扫描type ≠ ALL是否避免了文件排序Using filesort是否避免了临时表Using temporary扫描行数是否接近返回行数rows ≈ 实际返回联合索引是否符合最左前缀原则是否存在冗余索引已有联合索引包含单列索引深分页是否改为游标分页结语索引是施工队的高效调度系统在千人施工队的场景中工单表就是生产的核心枢纽。合理的索引设计就像科学的派工调度系统工人扫码查工单→ 毫秒级响应不耽误领工班组长派新活→ 实时冲突检测不重不漏质检员验收→ 列表秒开现场流转顺畅项目经理看报表→ 实时统计决策有据“好的索引让800万记录像800条一样快坏的索引让800条像800万一样慢。”—— 某建筑集团DBA附录最终索引方案work_orders表-- 核心查询索引CREATEINDEXidx_worker_date_status_startONwork_orders(worker_id,plan_date,order_status,plan_start_time);-- 班组统计索引覆盖索引CREATEINDEXidx_team_date_status_coverONwork_orders(team_id,plan_date,order_status,work_quantity,total_amount);-- 质检列表索引CREATEINDEXidx_status_quality_dateONwork_orders(order_status,quality_status,plan_date,created_at,order_id);-- 防重复派工唯一索引ALTERTABLEwork_ordersADDUNIQUEINDEXuk_worker_date_start(worker_id,plan_date,plan_start_time);-- 已删除的低效索引-- DROP INDEX idx_worker ON work_orders;-- DROP INDEX idx_status ON work_orders;本文基于 MySQL 8.0 环境适用于建筑施工现场管理系统、工单派发系统等高频写入、高并发查询场景。