SQL优化详解本章导读SQL优化是数据库性能提升的核心手段一条优化后的SQL可能带来数十倍的性能提升。本章将系统讲解执行计划分析、索引设计原则、SQL改写技巧等实战内容。学习目标目标1能够解读EXPLAIN执行计划识别性能瓶颈目标2掌握索引设计原则能够设计高效的索引策略目标3掌握常用SQL改写优化技巧提升查询效率前置知识熟悉SQL基本语法了解数据库基本概念阅读时长约 25 分钟一、知识概述SQL优化是数据库性能提升的核心手段。通过分析执行计划、优化索引、改写SQL语句可以显著提升查询性能。1.1 SQL优化流程┌─────────────────────────────────────────────────────────────┐ │ SQL优化流程 │ ├─────────────────────────────────────────────────────────────┤ │ │ │ 1. 发现慢查询 │ │ └── 慢查询日志、监控告警、用户反馈 │ │ │ │ 2. 分析执行计划 │ │ └── EXPLAIN分析、定位性能瓶颈 │ │ │ │ 3. 优化方案设计 │ │ └── 索引优化、SQL改写、表结构调整 │ │ │ │ 4. 实施优化 │ │ └── 创建索引、修改SQL、调整配置 │ │ │ │ 5. 验证效果 │ │ └── 对比执行时间、资源消耗 │ │ │ └─────────────────────────────────────────────────────────────┘1.2 执行计划关键指标┌─────────────────────────────────────────────────────────────┐ │ EXPLAIN关键指标 │ ├─────────────────────────────────────────────────────────────┤ │ │ │ type访问类型从好到差 │ │ ┌─────────────┬─────────────────────────────────────┐ │ │ │ system │ 表只有一行系统表 │ │ │ │ const │ 主键或唯一索引常量查询 │ │ │ │ eq_ref │ 唯一索引关联查询 │ │ │ │ ref │ 非唯一索引查询 │ │ │ │ range │ 索引范围扫描 │ │ │ │ index │ 全索引扫描 │ │ │ │ ALL │ 全表扫描最差 │ │ │ └─────────────┴─────────────────────────────────────┘ │ │ │ │ Extra额外信息 │ │ - Using index覆盖索引性能好 │ │ - Using where需要过滤 │ │ - Using filesort文件排序需优化 │ │ - Using temporary使用临时表需优化 │ │ │ │ key实际使用的索引 │ │ rows预估扫描行数 │ │ filtered过滤百分比 │ │ │ └─────────────────────────────────────────────────────────────┘二、执行计划分析2.1 EXPLAIN使用-- -- 基本EXPLAIN使用-- -- 查看执行计划EXPLAINSELECT*FROMordersWHEREuser_id100;-- 查看详细执行计划MySQL 8.0EXPLAINANALYZESELECT*FROMordersWHEREuser_id100;-- 查看JSON格式执行计划EXPLAINFORMATJSONSELECT*FROMordersWHEREuser_id100;-- -- 执行计划示例分析-- -- 示例1: 全表扫描EXPLAINSELECT*FROMordersWHEREamount1000;/* -------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 100000 | 33.33 | Using where | -------------------------------------------------------------------------------------------------------------- */-- 问题: typeALL, 全表扫描-- 解决: 为amount字段创建索引-- 示例2: 索引范围扫描EXPLAINSELECT*FROMordersWHEREuser_id100;/* ------------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | ref | idx_user_id | idx_user_id | 8 | const | 10 | 100.00 | NULL | ------------------------------------------------------------------------------------------------------------ */-- 良好: typeref, 使用了索引-- 示例3: 覆盖索引EXPLAINSELECTuser_id,order_noFROMordersWHEREuser_id100;/* ------------------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------ | 1 | SIMPLE | orders | ref | idx_user_id | idx_user_id | 8 | const | 10 | 100.00 | Using index | ------------------------------------------------------------------------------------------------------------------ */-- 最佳: ExtraUsing index, 覆盖索引-- 示例4: 文件排序EXPLAINSELECT*FROMordersWHEREuser_id100ORDERBYcreate_time;/* --------------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | --------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | ref | idx_user_id | idx_user_id | 8 | const | 10 | 100.00 | Using filesort | --------------------------------------------------------------------------------------------------------------------- */-- 问题: ExtraUsing filesort-- 解决: 创建(user_id, create_time)联合索引2.2 慢查询日志分析-- -- 慢查询日志配置-- -- 查看慢查询配置SHOWVARIABLESLIKEslow_query%;SHOWVARIABLESLIKElong_query_time;-- 启用慢查询日志SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 超过1秒记录SETGLOBALlog_queries_not_using_indexesON;-- 记录未使用索引的查询-- -- 慢查询分析工具-- -- 使用mysqldumpslow分析-- mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log-- 使用pt-query-digest分析推荐-- pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt-- -- Performance Schema分析-- -- 查看执行次数最多的SQLSELECTDIGEST_TEXT,COUNT_STAR,SUM_TIMER_WAIT/1000000000AStotal_time_secFROMperformance_schema.events_statements_summary_by_digestORDERBYCOUNT_STARDESCLIMIT10;-- 查看平均执行时间最长的SQLSELECTDIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT/1000000000ASavg_time_sec,SUM_ROWS_EXAMINEDFROMperformance_schema.events_statements_summary_by_digestORDERBYAVG_TIMER_WAITDESCLIMIT10;三、索引优化3.1 索引设计原则-- -- 索引设计原则-- -- 1. 选择性高的列优先-- 选择性 不同值数量 / 总行数SELECTCOUNT(DISTINCTuser_id)/COUNT(*)ASselectivityFROMorders;-- 选择性接近1适合建索引-- 2. 联合索引遵循最左前缀原则-- 创建联合索引CREATEINDEXidx_user_status_timeONorders(user_id,status,create_time);-- 能使用索引的查询SELECT*FROMordersWHEREuser_id100;-- 使用索引SELECT*FROMordersWHEREuser_id100ANDstatus1;-- 使用索引SELECT*FROMordersWHEREuser_id100ANDstatus1ANDcreate_time2024-01-01;-- 使用索引-- 不能使用索引的查询SELECT*FROMordersWHEREstatus1;-- 不符合最左前缀SELECT*FROMordersWHEREcreate_time2024-01-01;-- 不符合最左前缀-- 3. 覆盖索引优化-- 创建覆盖索引避免回表CREATEINDEXidx_user_coverONorders(user_id,order_no,amount,status);-- 使用覆盖索引SELECTuser_id,order_no,amount,statusFROMordersWHEREuser_id100;-- 4. 避免索引失效-- 类型转换导致索引失效SELECT*FROMordersWHEREuser_id100;-- 字符串比较索引失效-- 函数操作导致索引失效SELECT*FROMordersWHEREDATE(create_time)2024-01-01;-- 索引失效-- 优化为范围查询SELECT*FROMordersWHEREcreate_time2024-01-01ANDcreate_time2024-01-02;-- LIKE前缀模糊导致索引失效SELECT*FROMordersWHEREorder_noLIKE%123;-- 索引失效SELECT*FROMordersWHEREorder_noLIKEORD%;-- 使用索引-- OR导致索引失效部分情况SELECT*FROMordersWHEREuser_id100ORamount1000;-- 可能失效-- 优化为UNIONSELECT*FROMordersWHEREuser_id100UNIONSELECT*FROMordersWHEREamount1000;3.2 索引维护-- -- 索引维护操作-- -- 查看表索引SHOWINDEXFROMorders;-- 查看索引使用情况SELECTOBJECT_NAMEAStable_name,INDEX_NAME,CARDINALITY,SEQ_IN_INDEXFROMinformation_schema.STATISTICSWHERETABLE_SCHEMAmydbANDTABLE_NAMEorders;-- 查看未使用的索引SELECT*FROMsys.schema_unused_indexesWHEREobject_schemamydb;-- 分析表更新统计信息ANALYZETABLEorders;-- 优化表重建表整理碎片OPTIMIZETABLEorders;-- 删除冗余索引-- 如果存在idx_user_id和idx_user_status_time前者是冗余的DROPINDEXidx_user_idONorders;四、SQL改写优化4.1 常见SQL优化-- -- 1. 避免SELECT *-- -- 不推荐SELECT*FROMordersWHEREuser_id100;-- 推荐只查询需要的列SELECTorder_id,order_no,amount,statusFROMordersWHEREuser_id100;-- -- 2. 优化分页查询-- -- 传统分页大偏移量性能差SELECT*FROMordersORDERBYidLIMIT10000,20;-- 优化方案1使用子查询SELECT*FROMorders oJOIN(SELECTidFROMordersORDERBYidLIMIT10000,20)tONo.idt.id;-- 优化方案2使用游标分页SELECT*FROMordersWHEREid10000ORDERBYidLIMIT20;-- -- 3. 优化COUNT查询-- -- COUNT(*) vs COUNT(column)-- COUNT(*)统计总行数不区分NULL-- COUNT(column)统计非NULL行数-- 估算总数大数据量表EXPLAINSELECTCOUNT(*)FROMorders;-- 或使用信息表SELECTTABLE_ROWSFROMinformation_schema.TABLESWHERETABLE_SCHEMAmydbANDTABLE_NAMEorders;-- -- 4. 优化JOIN查询-- -- 小表驱动大表SELECTo.*FROMorders oJOINusers uONo.user_idu.idWHEREu.status1;-- 确保JOIN字段有索引CREATEINDEXidx_user_idONorders(user_id);-- -- 5. 优化子查询-- -- 不推荐相关子查询SELECT*FROMorders oWHEREEXISTS(SELECT1FROMorder_itemsWHEREorder_ido.idANDproduct_id100);-- 推荐JOIN查询SELECTDISTINCTo.*FROMorders oJOINorder_items oiONo.idoi.order_idWHEREoi.product_id100;-- -- 6. 批量操作优化-- -- 批量插入INSERTINTOorders(user_id,order_no,amount)VALUES(1,ORD001,100),(2,ORD002,200),(3,ORD003,300);-- 批量更新UPDATEordersSETstatus2WHEREidIN(1,2,3);-- -- 7. 使用EXISTS替代IN-- -- 当子查询结果集较大时SELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREstatus1);-- 使用EXISTS可能更优SELECT*FROMorders oWHEREEXISTS(SELECT1FROMusers uWHEREu.ido.user_idANDu.status1);4.2 复杂查询优化-- -- 复杂查询优化示例-- -- 查询示例统计每个用户的订单数量和总金额-- 原始SQL使用子查询SELECTu.id,u.username,(SELECTCOUNT(*)FROMordersWHEREuser_idu.id)ASorder_count,(SELECTSUM(amount)FROMordersWHEREuser_idu.id)AStotal_amountFROMusers u;-- 优化后使用JOIN和GROUP BYSELECTu.id,u.username,COUNT(o.id)ASorder_count,COALESCE(SUM(o.amount),0)AStotal_amountFROMusers uLEFTJOINorders oONu.ido.user_idGROUPBYu.id,u.username;-- 查询示例查找最近30天每个商品的销售排行-- 原始SQLSELECTp.id,p.name,(SELECTSUM(oi.quantity)FROMorder_items oiJOINorders oONoi.order_ido.idWHEREoi.product_idp.idANDo.create_timeDATE_SUB(NOW(),INTERVAL30DAY))ASsales_countFROMproducts pORDERBYsales_countDESCLIMIT100;-- 优化后SELECTp.id,p.name,COALESCE(SUM(oi.quantity),0)ASsales_countFROMproducts pLEFTJOINorder_items oiONp.idoi.product_idLEFTJOINorders oONoi.order_ido.idANDo.create_timeDATE_SUB(NOW(),INTERVAL30DAY)GROUPBYp.id,p.nameORDERBYsales_countDESCLIMIT100;五、最佳实践总结5.1 SQL优化检查清单┌─────────────────────────────────────────────────────────────┐ │ SQL优化检查清单 │ ├─────────────────────────────────────────────────────────────┤ │ │ │ □ 索引优化 │ │ - 为WHERE/JOIN/ORDER BY/GROUP BY字段创建索引 │ │ - 使用联合索引遵循最左前缀原则 │ │ - 避免索引失效场景 │ │ - 定期维护索引统计信息 │ │ │ │ □ SQL改写 │ │ - 避免SELECT * │ │ - 优化大偏移量分页 │ │ - 使用JOIN替代子查询 │ │ - 批量操作替代循环操作 │ │ │ │ □ 执行计划分析 │ │ - 检查type是否使用索引 │ │ - 检查Extra是否有filesort/temporary │ │ - 评估扫描行数rows │ │ │ │ □ 监控告警 │ │ - 开启慢查询日志 │ │ - 定期分析慢查询 │ │ - 建立监控指标 │ │ │ └─────────────────────────────────────────────────────────────┘5.2 索引设计最佳实践场景推荐索引精确查询单列索引范围查询单列索引多条件查询联合索引高选择性列在前排序条件联合索引条件列排序列覆盖查询联合索引包含所有查询列六、总结SQL优化是数据库性能提升的核心手段。通过分析执行计划、合理设计索引、优化SQL语句可以显著提升查询性能。核心要点执行计划分析理解type、key、rows、Extra等关键字段索引优化遵循设计原则避免索引失效SQL改写使用JOIN、批量操作、覆盖索引等技巧持续监控慢查询日志、Performance Schema六、思考与练习思考题基础题EXPLAIN结果中的type字段有哪些取值从好到差的排序是什么进阶题联合索引(a,b,c)查询条件WHERE a1 AND c3能否使用索引为什么如何优化实战题分页查询LIMIT 100000, 10在大数据量下性能很差请给出至少两种优化方案。编程练习练习有一个订单表orders包含字段id、user_id、order_no、amount、status、create_time请完成以下任务设计索引以优化以下查询按user_id查询订单按user_id和status查询并按create_time排序统计某用户各状态的订单数量使用EXPLAIN验证索引效果编写覆盖索引优化示例章节关联前置章节线程调优详解后续章节表结构设计详解扩展阅读《高性能MySQL》第三版下一章预告下一章将讲解表结构设计包括范式与反范式设计、分库分表策略、字段类型选择等内容为数据库性能奠定良好基础。本章完