MySQL慢查询分析与优化实战指南

20XX/XX/XX汇报人:XXXMySQL慢查询分析与优化实战指南CONTENTS目录01

慢查询基础认知02

慢查询识别工具与配置03

执行计划深度解析04

索引优化核心策略CONTENTS目录05

SQL语句优化技巧06

实战案例深度剖析07

监控与持续优化体系慢查询基础认知01慢查询定义与业务影响慢查询的定义慢查询指执行时间超过预设阈值（默认10秒，生产环境通常设为1-2秒）的SQL语句。阈值可通过long_query_time参数调整，如电商秒杀场景可设为500ms。慢查询的核心危害慢查询会导致资源消耗增加（CPU/IO/内存占用飙升）、用户体验下降（响应延迟）、并发能力降低（阻塞其他查询），严重时引发系统雪崩。隐性慢查询的识别单条执行时间未超阈值但高频调用（如每秒上千次）的SQL，累计耗时会严重占用资源，需结合执行频率综合判断。慢查询分类：显性与隐性

显性慢查询：执行时间超限指执行时间超过预设阈值（如long_query_time=1秒）的SQL语句，可通过慢查询日志直接捕获，典型表现为查询耗时显著超过业务预期。

隐性慢查询：高频低耗累积单条执行时间未达阈值，但调用频率极高（如每秒上千次），累计消耗大量CPU、IO资源，可能导致系统整体性能下降，需通过PerformanceSchema等工具监控识别。性能指标基准参考

CPU使用率健康阈值用户空间CPU使用率(<70%)，系统内核CPU使用率(<20%)，CPU等待I/O时间百分比(<30%)，空闲CPU百分比(>10%)。

内存与交换空间指标已使用交换空间大小理想值为0，空闲物理内存大小应>系统内存的10%，每秒从交换空间换入换出内存的块数理想值均为0。

磁盘I/O性能指标磁盘设备利用率(<100%)，平均I/O队列长度(<1)，平均每次I/O操作等待时间(<20ms)，结合磁盘类型评估每秒读写块数(如SSD应>1000块/秒)。

数据库内部关键指标Innodb_buffer_pool_wait_free应为0，慢查询数量应控制在总查询数的0.1%以内，连接数应低于max_connections的80%。慢查询识别工具与配置02慢查询日志配置方法

临时配置（会话级生效）通过SQL命令动态开启：SETGLOBALslow_query_log=ON;设置阈值：SETGLOBALlong_query_time=1;记录未用索引查询：SETGLOBALlog_queries_not_using_indexes=ON;注意：需重新连接会话生效，MySQL服务重启后失效。

永久配置（配置文件生效）编辑MySQL配置文件（f或my.ini），在[mysqld]节点添加：slow_query_log=1;slow_query_log_file=/var/log/mysql/mysql-slow.log;long_query_time=1;log_queries_not_using_indexes=1;配置后需重启MySQL服务使设置永久生效。

核心参数说明slow_query_log：是否开启慢查询日志（1/ON为开启，0/OFF为关闭）；slow_query_log_file：日志文件存储路径；long_query_time：慢查询时间阈值（单位：秒，支持小数如0.5）；log_queries_not_using_indexes：是否记录未使用索引的查询（1/ON为开启）。动态配置与永久生效方案

临时开启慢查询（会话级配置）通过SQL命令动态开启慢查询日志：SETGLOBALslow_query_log=ON;设置阈值：SETGLOBALlong_query_time=1;记录未使用索引查询：SETGLOBALlog_queries_not_using_indexes=ON;注意：配置后需重新连接数据库生效，MySQL服务重启后失效。

永久配置（配置文件修改）编辑MySQL配置文件（f或my.ini），在[mysqld]节点添加：slow_query_log=1;slow_query_log_file=/var/log/mysql/mysql-slow.log;long_query_time=1;log_queries_not_using_indexes=1;配置后需重启MySQL服务使设置永久生效。

配置验证与生效确认执行SHOWVARIABLESLIKE'slow_query_log%';检查慢查询日志状态；执行SHOWVARIABLESLIKE'long_query_time';确认阈值设置。对于永久配置，可通过systemctlrestartmysqld（Linux）或重启服务（Windows）验证生效情况。mysqldumpslow工具使用指南

01工具简介与核心功能mysqldumpslow是MySQL自带的慢查询日志分析工具，支持按执行时间、次数、锁等待等维度统计慢查询，帮助快速定位高频低效SQL。

02基础语法与常用参数基本语法：mysqldumpslow[options]log_file。关键参数：-s（排序方式：t=时间/c=次数/l=锁等待），-tN（显示前N条），-g（正则过滤）。

03典型使用场景示例1.按执行时间排序取前10条：mysqldumpslow-st-t10/var/log/mysql-slow.log；2.按执行次数排序：mysqldumpslow-sc-t5log_file；3.过滤含"ORDERBY"的慢查询：mysqldumpslow-st-g'orderby'log_file。

04输出结果解读要点关注Count（执行次数）、Time（总时间/平均时间）、Lock（锁等待时间）、Rows（扫描行数/返回行数），快速定位"执行频繁且耗时久"的SQL。pt-query-digest高级分析

工具简介与安装pt-query-digest是PerconaToolkit套件中的核心工具，提供比mysqldumpslow更详细的慢查询日志分析能力，支持统计执行频率、平均耗时、锁等待等关键指标。安装方式：Debian/Ubuntu系统可通过apt-getinstallpercona-toolkit，CentOS/RHEL系统可通过yuminstallpercona-toolkit。

核心功能与输出报告解读主要功能包括：识别重复SQL模板、按执行时间/次数/锁等待排序、生成查询指纹。报告分为总体统计（总查询数、总耗时占比）、查询详情（平均执行时间、扫描行数、影响行数）、执行计划摘要等部分，可快速定位高消耗SQL。

实战命令与参数示例基础分析命令：pt-query-digest/var/log/mysql/slow.log>slow_report.txt；按时间范围分析：pt-query-digest--since=1h/var/log/mysql/slow.log；过滤特定数据库：pt-query-digest--filter'($event->{db}eq"mydb")'slow.log。

关键指标与优化优先级确定重点关注报告中"Totaltime"占比高的查询（如超过50%）、"Rowsexamined/rowssent"比值过大（可能存在全表扫描）、"Locktime"长的SQL（锁竞争问题）。优先优化高频高耗时查询，结合执行计划调整索引或SQL结构。PerformanceSchema实时监控

PerformanceSchema简介PerformanceSchema是MySQL5.6及以上版本内置的性能监测工具，提供对数据库内部运行状态的细粒度监控，可追踪SQL执行、锁等待、内存使用等指标，相比慢查询日志能更实时地发现性能问题。

开启全量SQL监控配置通过执行以下SQL开启监控：UPDATEperformance_schema.setup_instrumentsSETENABLED='YES',TIMED='YES'WHERENAMELIKE'%statement/%';此配置将启用对所有SQL语句的执行时间跟踪。

关键监控表使用示例查询events_statements_summary_by_digest表可获取最耗时SQL摘要：SELECTDIGEST_TEXT,SUM_TIMER_WAIT,COUNT_STARFROMevents_statements_summary_by_digestORDERBYSUM_TIMER_WAITDESCLIMIT10;帮助快速定位高频高耗SQL。

与慢查询日志的互补优势PerformanceSchema提供实时监控能力，无需等待日志写入，可捕捉未达慢查询阈值但高频执行的"隐性慢查询"，与慢查询日志结合形成全方位性能监控体系。执行计划深度解析03EXPLAIN基础语法与输出结构

01EXPLAIN基础语法在SQL查询语句前添加EXPLAIN关键字，即可获取执行计划。语法格式：EXPLAINSELECT...[FROMtable_name][WHEREconditions][ORDERBY...][LIMIT...]。例如：EXPLAINSELECT*FROMordersWHEREuser_id=123ANDstatus='paid';

02核心输出字段解析执行计划包含多个关键字段，其中type表示访问类型（如ALL、ref、range等），key显示实际使用的索引，rows表示预估扫描行数，Extra提供额外优化信息（如Usingfilesort、Usingtemporary）。这些字段是判断查询效率的重要依据。

03输出结构示例典型执行计划输出包含id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra等列。例如，当type为ALL时表示全表扫描，key为NULL表示未使用索引，Extra出现Usingfilesort提示需优化排序操作。核心字段解读：type与keytype字段：索引访问类型type字段表示MySQL查询时采用的索引访问类型，性能从优到劣排序为：system>const>eq_ref>ref>range>index>ALL。理想状态应避免ALL（全表扫描）和index（索引树扫描），优先达到ref或range级别。关键type类型解析const：通过主键或唯一索引查询，返回单行数据；ref：使用非唯一索引匹配，返回多行；range：索引范围查询（如BETWEEN、IN）；ALL：全表扫描，需重点优化。key字段：实际使用的索引key字段显示查询实际命中的索引名称，NULL表示未使用索引。若possible_keys不为空但key为NULL，可能因索引选择性低或优化器判断全表扫描更高效。type与key联动分析当type为ALL且key为NULL时，需检查是否缺少索引；当type为range但key非预期索引时，可能存在索引失效（如函数操作索引列），需通过SQL重写修复。Extra信息实战分析单击此处添加正文

Usingfilesort：排序优化策略当Extra出现Usingfilesort时，表示MySQL无法利用索引完成排序，需额外进行文件排序操作。优化方法包括：为ORDERBY字段创建复合索引，确保排序字段包含在索引中；避免SELECT*，减少排序数据量。Usingtemporary：临时表规避方案Usingtemporary表示查询使用了临时表存储中间结果，常见于GROUPBY或DISTINCT操作。优化建议：为GROUPBY字段创建索引；拆分复杂查询，避免多表JOIN时的临时表生成；使用覆盖索引减少临时表数据量。Usingindexcondition：回表查询优化Usingindexcondition说明索引条件下推优化生效，但仍需回表查询数据。可通过创建覆盖索引（包含查询所需所有字段）消除回表，例如将单列索引扩展为包含SELECT字段的复合索引，直接从索引获取结果。Usingwhere;Usingindex：覆盖索引最佳实践此组合表示查询使用覆盖索引且通过索引条件过滤数据，无需回表。实战中应优先为高频查询设计覆盖索引，如对SELECTa,bFROMtWHEREc=?场景，创建(c,a,b)复合索引，显著提升查询效率。JSON格式执行计划应用01JSON执行计划的启用方式通过在EXPLAIN命令后添加FORMAT=JSON参数获取详细执行计划，例如：EXPLAINFORMAT=JSONSELECT*FROMordersWHEREuser_id=123;02核心字段解析重点关注"query_block"下的"access_type"（访问类型）、"rows"（预估扫描行数）、"cost_info"（成本信息）及"used_key_parts"（使用的索引列）等字段，可精准定位性能瓶颈。03实战应用场景在复杂查询优化中，JSON格式能展示传统执行计划未包含的"attached_condition"（附加条件）和"extra"详细信息，例如Usingindexcondition的具体判断逻辑，辅助解决索引失效问题。04工具集成建议可将JSON结果导入可视化工具（如PerconaToolkit的pt-visual-explain）或编写脚本解析，快速生成执行路径图，提升团队协作效率。索引优化核心策略04最左前缀原则实战应用

最左前缀原则核心定义复合索引需从最左列开始匹配，依次向右，遇到范围查询（如>、<、BETWEEN）则后续列无法使用索引。例如索引(a,b,c)，查询条件a=1ANDb=2可命中前两列，a=1ANDc=3仅能命中a列。

索引列顺序优化策略高频查询字段、高选择性字段（唯一值比例高）优先放在左侧。如用户表查询常用"城市+年龄"，城市区分度高于年龄，应创建索引(city,age)而非(age,city)。

典型失效场景与规避方法场景1：跳过最左列，如索引(a,b)，查询b=2→全表扫描；规避：补充a列条件或调整索引顺序。场景2：中间列范围查询，如索引(a,b,c)，a=1ANDb>5ANDc=3→c列无法使用索引；规避：将范围列放在索引末尾。

实战案例：电商订单表索引优化原索引(user_id,status,create_time)，查询"user_id=100ANDcreate_time>'2023-01-01'"→status列被跳过，仅命中user_id。优化为(user_id,create_time,status)，利用create_time范围查询，同时保留status列索引可用，查询效率提升300%。选择性定义与计算方法选择性是指列中唯一值数量与总行数的比值，公式为：SELECTCOUNT(DISTINCTcolumn_name)/COUNT(*)FROMtable_name。值越接近1，选择性越高，索引效果越好。高选择性列的识别原则优先选择区分度高的列（如用户ID、手机号），避免低基数字段（如性别、状态）。例如电商订单表中，user_id选择性通常高于order_status。复合索引中的列顺序策略复合索引需将高选择性列置于左侧。如查询条件为user_id=?ANDage=?，应创建(idx_user_id_age)而非(idx_age_user_id)，可快速缩小扫描范围。实战案例：选择性优化效果某用户表中，email列选择性0.92，创建索引后查询耗时从500ms降至30ms；而gender列选择性0.02，建索引后性能提升不明显，反而增加写操作开销。高选择性列索引设计覆盖索引构建技巧覆盖索引定义与优势覆盖索引是指索引包含查询所需全部字段，无需回表查询数据行。其核心优势在于减少I/O操作，直接从索引树获取结果，显著提升查询性能。复合索引字段选择策略优先包含WHERE条件列、JOIN关联列、SELECT投影列。例如对查询"SELECTname,ageFROMusersWHEREdept='IT'"，可创建复合索引(dept,name,age)实现覆盖。避免冗余字段与空间控制仅包含必要字段，避免添加低查询频率列。对长字符串字段可使用前缀索引平衡选择性与空间，如对VARCHAR(255)的email字段建立email(10)前缀索引。实战验证与维护方法使用EXPLAIN查看Extra列是否为"Usingindex"验证覆盖效果。定期通过sys.schema_unused_indexes清理未使用覆盖索引，结合业务变更调整索引字段。索引失效场景与规避

索引列函数操作导致失效在索引列上使用函数（如WHEREYEAR(create_time)=2023）会使索引失效，导致全表扫描。应改为范围查询，如WHEREcreate_time>='2023-01-01'ANDcreate_time<'2024-01-01'。

隐式类型转换导致失效字符串字段与数字比较（如WHEREphonephone为字符串类型）会触发隐式转换，导致索引失效。需保证查询值与字段类型一致，如WHEREphone=。

前缀通配符导致失效使用LIKE'%张'等前缀通配符会使索引失效，因无法从索引头部定位。可改为后缀通配符（如LIKE'张%'）或使用全文索引、Elasticsearch等工具支持复杂搜索。

范围查询后索引列失效复合索引中，某列使用范围条件（如>、<、BETWEEN）后，其后的列无法利用索引。应将范围列放在索引尾部，如复合索引(user_id,age,status)，查询条件user_id=1ANDage>20可利用索引前两列。

OR条件导致索引失效OR连接不同列且未全部建立索引时，可能导致全表扫描。可拆分为UNION查询（如SELECT*FROMtWHEREa=1UNIONALLSELECT*FROMtWHEREb=2），确保各条件列均有索引。冗余索引识别与清理冗余索引的定义与危害

冗余索引指功能上重复或可被其他索引替代的索引，如已存在联合索引(a,b)，再创建索引(a)或(a,c)可能构成冗余。冗余索引会增加写操作(INSERT/UPDATE/DELETE)的IO开销，浪费存储空间，降低数据库性能。冗余索引识别方法

利用MySQLsys库的schema_redundant_indexes视图可直接查询冗余索引，例如执行"SELECT*FROMsys.schema_redundant_indexes;"。关注索引类型、列顺序及包含关系，如联合索引包含单列索引时，单列索引通常为冗余。冗余索引清理策略

清理前需通过慢查询日志和执行计划验证索引是否被使用。优先移除长期未使用的冗余索引，对于高频写入表可激进清理，对查询密集表需保留必要索引。清理命令示例："DROPINDEXidx_nameONtable_name;"。SQL语句优化技巧05SELECT字段精细化控制

01避免使用SELECT*SELECT*会读取所有字段，增加数据传输量和内存消耗，且无法利用覆盖索引。应明确指定所需字段，如SELECTid,nameFROMusersWHEREid=1。

02利用覆盖索引减少回表当查询字段全部包含在索引中时，MySQL可直接从索引获取数据，无需回表。例如，对索引(idx_name_age)执行SELECTname,ageFROMusersWHEREname='张三'，Extra显示Usingindex。

03控制返回数据量仅查询业务所需字段，避免传输无关数据。例如，用户列表页只需查询id、name、avatar，无需返回详细地址和联系方式，减少IO开销。JOIN查询优化策略

01小表驱动大表原则多表关联时，优先使用数据量较小的表作为驱动表，减少外层循环次数。例如：小表数据量100行，大表100万行，可减少99.99%的循环次数。

02JOIN字段索引优化确保所有JOIN关联字段均创建索引，避免全表扫描。如订单表(user_id)与用户表(id)JOIN时，需在订单表user_id字段创建索引。

03控制JOIN表数量关联表数量建议不超过3-4张，避免因中间结果集过大导致内存溢出。复杂查询可拆分为多个简单查询分步执行。

04避免使用子查询将子查询改写为JOIN操作提升性能。例如：SELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREstatus=1)可改写为orders与users表JOIN查询。

05使用STRAIGHT_JOIN强制顺序当优化器选择的表连接顺序不合理时，可使用STRAIGHT_JOIN指定连接顺序，例如：SELECTSTRAIGHT_JOIN*FROMsmall_tableJOINlarge_tableON...传统深分页的性能瓶颈传统深分页如"LIMIT100000,10"需扫描前100010条数据后丢弃，导致大量I/O和CPU资源浪费，随着偏移量增大，查询效率急剧下降。基于主键的游标分页法利用主键有序性，通过记录上次查询的最大ID实现分页："SELECT*FROMtableWHEREid>last_idORDERBYidLIMIT10"，避免全表扫描，适用于主键自增场景。覆盖索引优化法创建包含查询字段的复合索引，通过"SELECTidFROMtableWHEREconditionLIMIT100000,10"先获取目标ID，再关联查询详情，减少回表操作，提升效率。业务场景适配建议用户列表、订单记录等高频查询场景优先采用游标分页；后台报表等非实时场景可结合异步生成或数据预处理；避免使用OFFSET偏移量超过10000的深分页查询。深分页查询优化方案子查询改写实战子查询性能瓶颈表现多层嵌套子查询可能导致重复扫描和临时表创建，例如电商订单表中查询用户最近订单时，子查询嵌套层级超过3层会使执行时间增加300%以上。JOIN替代子查询优化将子查询改写为JOIN可减少表扫描次数，如原查询"SELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREage>30)"可优化为"SELECTo.*FROMordersoJOINusersuONo.user_id=u.idWHEREu.age>30"，经测试查询效率提升约200%。临时表缓存中间结果对复杂子查询结果创建临时表并建立索引，例如"CREATETEMPORARYTABLEtemp_user_idsASSELECTidFROMusersWHEREregister_time>'2023-01-01';CREATEINDEXidx_temp_idONtemp_user_ids(id);"，可使后续关联查询速度提升50%以上。EXISTS替代IN子查询当子查询结果集较大时，使用EXISTS代替IN可减少内存占用，如"SELECT*FROMproductspWHEREEXISTS(SELECT1FROMordersoWHEREduct_id=p.id)"比"WHEREp.idIN(SELECTproduct_idFROMorders)"平均节省40%内存使用。模糊查询性能优化前缀匹配索引利用仅当LIKE使用后缀通配符（如'张%'）时可利用B+树索引，避免前置%导致全表扫描。例如对title字段建立索引，查询"titleLIKE'MySQL%'"可命中索引，而"%索引指南"无法使用索引。覆盖索引减少回表当模糊查询仅返回索引包含字段时，可触发覆盖索引优化。例如联合索引(idx_name_age)，查询"SELECTnameFROMuserWHEREnameLIKE'%张'"可通过索引直接返回结果，无需回表。全文索引替代方案对长文本字段（如文章内容）建议使用FULLTEXT索引，通过MATCHAGAINST语法实现高效关键词搜索，替代低效的%通配符模糊查询。适用于电商商品描述、新闻内容等场景。业务逻辑优化策略通过应用层预处理（如关键词提取）、数据冗余（如存储拼音首字母）或引入搜索引擎（如Elasticsearch），从根本上避免数据库层面的复杂模糊查询，提升系统整体性能。实战案例深度剖析06案例一：电商订单表慢查询优化问题背景与慢查询现象某电商平台订单表orders数据量达500万条，执行"SELECT*FROMordersWHEREuser_id=123ANDorder_date>'2023-01-01'ORDERBYorder_dateDESC"查询耗时3.5秒，慢查询日志显示Rows_examined=28000，Rows_sent=50。执行计划分析与瓶颈定位使用EXPLAIN分析显示type=ALL（全表扫描），key=NULL，Extra=Usingfilesort。原因是user_id和order_date字段未建立有效索引，导致大量行扫描和文件排序。索引优化方案实施创建复合索引idx_user_date(user_id,order_date)，利用最左前缀原则覆盖查询条件与排序字段。执行"CREATEINDEXidx_user_dateONorders(user_id,order_date);"。优化效果验证与对比优化后查询耗时降至0.02秒，EXPLAIN显示type=range，key=idx_user_date，Extra=Usingindexcondition。扫描行数从28000减少至48，避免全表扫描和文件排序。案例二：用户表索引失效修复问题描述：深度分页性能瓶颈某系统日志表（1000万+记录）执行分页查询：SELECT*FROMlogsORDERBYidLIMIT100000,10，耗时2.3秒，扫描行数100010行，存在严重性能问题。执行计划分析：全表扫描与文件排序EXPLAIN显示type=ALL（全表扫描），Extra=Usingfilesort，未利用主键索引。因LIMIT偏移量过大，MySQL需扫描前100010行数据后丢弃前100000行，资源浪费严重。优化方案：基于主键的游标分页重构查询为：SELECT*FROMlogsWHEREid>100000ORDERBYidLIMIT10。利用主键索引定位起始位置，扫描行数仅10行，查询耗时降至12ms，性能提升190倍。适用场景与注意事项适用于有序主键的分页场景，需前端记录上次查询最大ID。若需跳页查询，可结合覆盖索引（如CREATEINDEXidx_logs_createdONlogs(created_at,id)）优化范围查询效率。案例三：日志表分页查询优化案例四：