1. 先从“MySQL数据库性能分析”到底要解决什么问题说起做后端开发或者运维的人迟早都会遇到这么一天线上数据库突然CPU飙高、慢查询报警刷屏、接口响应从几十毫秒涨到几秒钟。这时候打开终端连上MySQL看着几百个连接堆积在那里脑海里只剩一个念头——先重启试试。但我先说一句可能不太中听的话遇到性能问题第一时间重启数据库是最亏的做法。重启确实能把积累的连接清掉慢查询也暂时消失了但问题本身完全没有被解决。第二天同一时间同样的告警又来一遍因为引发性能问题的SQL还是那几条索引还是没建数据量还在涨。真正靠谱的做法是沉下来做一次系统性的“MySQL数据库性能分析”搞清楚瓶颈到底出在哪个环节再对症下药。这篇内容我打算按实际排查顺序展开不讲虚的从“思路怎么搭”“工具怎么用”“参数怎么调”三层来拆尽量贴近一线干活场景。适合刚接手数据库维护的同学也适合写业务代码但经常被慢查询坑到的开发。里面涉及的SQL、命令、参数都是我自己在真实环境里验证过的可以直接拿去用。要知道MySQL数据库性能分析从来不是单一动作而是一条完整链路先建立性能基线和监控再通过慢查询日志和性能视图定位具体SQL然后用EXPLAIN分析执行计划最后落到索引优化和参数调整上。每一步都有对应的工具和手段下面一个一个说。2. 性能分析的几个前置动作2.1 先搞清楚当前实例的健康状态拿到一台出问题的MySQL不要急着看慢查询。第一步应该是看一眼这个实例整体处于什么状态。我常用的几条命令分享出来供参考mysql SHOW GLOBAL STATUS LIKE Threads_connected; mysql SHOW GLOBAL STATUS LIKE Threads_running; mysql SHOW GLOBAL STATUS LIKE Questions; mysql SHOW GLOBAL STATUS LIKE Slow_queries; mysql SHOW ENGINE INNODB STATUS\G;这几条分别对应连接数、正在执行的线程数、累计查询数、慢查询累计数和InnoDB引擎的内部状态。其中最关心的两个指标是Threads_running和Slow_queries。Threads_running如果长期大于CPU核数的若干倍说明系统确实在“忙”大量查询在争抢CPU资源。Slow_queries是累计值单看意义不大但如果拿它做两次快照算出一个时间段的增量然后再除以时间窗口的秒数就能得到“每秒产生多少慢查询”这样的实时速率这个数字才是有效的。另外SHOW ENGINE INNODB STATUS里面有一段LATEST DETECTED DEADLOCK和TRANSACTIONS信息在出现锁等待或者死锁时这里是第一现场的完整记录。平时没什么感觉真遇到问题的时候再去找数据库早就把状态刷过去了。2.2 开启性能分析必需的基础设施很多性能分析手段依赖历史数据所以“出事之前就把监控打开”这件事特别重要。这里说的基础设施主要包含三层慢查询日志——记录执行时间超过阈值的SQL这是定位问题最直接的依据。performance_schema——MySQL自带的一套性能采集引擎记录等待事件、锁信息、IO统计等。sys schema——基于performance_schema封装好的视图查询起来比直接查原始表方便很多比如sys.session能看到当前所有连接在干什么。慢查询日志的开启方式直接写在配置文件里再重启或者运行时动态开启都可以。需要注意的是动态开启在重启后会自动失效所以建议两边都配置。slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1这里的long_query_time我习惯设成1秒。有些团队为了保守会设成2甚至5秒但那样会把很多“不算最慢但确实有问题”的SQL漏掉。如果担心日志量太大可以用log_queries_not_using_indexes来重点抓那种全表扫描的查询这个后面会专门展开讲。2.3 确定性能基线的意义做性能分析最怕的就是“没有参照”。一条SQL跑500毫秒看起来挺快但如果这个表正常的查询都在20毫秒以内那500毫秒已经是严重退化了。反过来一个报表查询跑5秒如果历史一直都是这样用户也能接受那它就不一定是当前故障的根因。所以平时就要养成记录基线的习惯。不用很复杂定期执行一次下面这条命令把关键指标存到本地文件里就能形成一个最简单的趋势数据mysql -uroot -p -e SHOW GLOBAL STATUS /backup/mysql_status_$(date %F).txt后面出了性能问题直接对比当天的数据和历史数据很多疑点立刻就能排除掉。这个习惯成本极低收益却是实实在在的。3. 定位慢SQL从哪里找到真正的“元凶”3.1 慢查询日志的分析方法慢查询日志是纯文本格式每天可能产生几百兆甚至上G的内容。肉眼一页一页翻肯定不现实推荐两个思路思路一用mysqldumpslow做粗筛MySQL自带的mysqldumpslow工具虽然简单但在大部分场景下够用了。它的核心能力是把结构相似、只是参数不同的SQL归并成一条然后按总执行时间或平均执行时间排序。mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.log解释一下参数-s at按平均查询时间排序-t 20只显示前20条mysqldumpslow会自动把具体数值替换成N比如WHERE id 123会归并成WHERE id N这样同一个模板的SQL就会聚合在一起统计方便看出哪些“类型的SQL”整体耗时最多。思路二用pt-query-digest做细看pt-query-digest是Percona Toolkit里最常用的工具之一分析结果比mysqldumpslow详细得多。它会生成一份报告包含SQL模板的整体分布、每个模板的执行次数、平均耗时、响应时间占比等。最重要的一点是它能直接告诉我们哪些SQL贡献了系统绝大部分的负载。pt-query-digest /var/log/mysql/mysql-slow.log digest_report.txt报告前面会有一个“Profile”段落按响应时间总和倒序排列前几行就是最值得关注的SQL模板。我见过非常多案例问题其实就集中在一两条SQL上把它们优化掉数据库压力立减70%以上。3.2 没有慢查询日志时用information_schema救急有些情况比较特殊业务方说慢但你没权限开慢查询日志或者日志还没落盘就被轮转清掉了。这时候还有一条路就是从performance_schema和sys库找线索。-- 查看当前正在执行的所有SQL SELECT * FROM sys.session WHERE command Query\G; -- 查看消耗IO最多的前10个文件 SELECT * FROM sys.io_global_by_file_by_bytes ORDER BY total DESC LIMIT 10; -- 查看热点事件等待类 SELECT * FROM sys.latest_file_io;sys.session这个视图尤其好用相当于给MySQL装了一个“实时top命令”。能看到每个连接正在跑的SQL、连接来源、执行时间、状态等。数据库卡死的时候连上这个视图基本可以当场抓住“肇事SQL”。3.3 慢SQL日志的典型字段解读一条典型的慢查询记录长这样# Query_time: 3.876512 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 1023456 SET timestamp1699000000; SELECT * FROM orders WHERE customer_id 8888 ORDER BY create_time DESC LIMIT 10;关键信息集中在第一行Query_timeSQL从开始到结束的总耗时Lock_time等待锁的时间Rows_sent最终返回给客户端的行数Rows_examined引擎扫描过的行数Rows_examined和Rows_sent的差距越大说明扫描越浪费。比如上面这条扫描了102万行只返回10行基本上可以断定没走索引或者索引没建对。这个差距就是优化的核心线索。4. EXPLAIN执行计划MySQL性能分析的核心技能4.1 为什么执行计划是必须掌握的一环慢查询日志告诉我们“哪条SQL慢”但没说“为什么慢”。要回答为什么必须看MySQL是怎么执行这条SQL的。优化器会根据统计信息、索引情况、表大小等因素生成一个执行计划而执行计划直接决定了查询会用哪条索引、扫描多少行、是否需要临时表。用EXPLAIN关键字加在SQL前面就能看到这条SQL的执行计划。例如EXPLAIN SELECT * FROM orders WHERE customer_id 8888 ORDER BY create_time DESC LIMIT 10\G;输出结果里最需要关注的是下面几列type访问类型。从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就是全表扫描基本是性能问题的最大嫌疑。key实际用到的索引。如果为NULL说明这条SQL没用上任何索引。rows优化器估算需要扫描的行数。这个数字越大性能越差。Extra额外信息。出现Using filesort表示排序没有走索引出现Using temporary表示用了临时表两个都是性能杀手。4.2 一个实战案例从typeALL到typeref的优化为了讲清楚我构造一个简化场景。有一张订单表结构大致如下CREATE TABLE orders ( id int NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, customer_id int NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;业务反馈“查询某用户的订单列表很慢”执行计划查出来是typekeyrowsExtraALLNULL1200000Using filesorttypeALL表示全表扫了120万行keyNULL表示没有索引可用Using filesort说明排序也只能额外做三重debuff叠满不慢才怪。优化方式很直接加一条复合索引ALTER TABLE orders ADD INDEX idx_customer_create (customer_id, create_time);再加一个索引顺序的说明customer_id用于筛选create_time用于排序把它们组合成一个复合索引可以同时满足WHERE customer_id ?的等值过滤和ORDER BY create_time的有序排序最终MySQL就能直接从索引里按顺序取数据避免filesort。执行计划立刻变成typekeyrowsExtrarefidx_customer_create58NULL扫描行数从120万降到58排序也没了那句慢SQL直接从3秒多降到10毫秒以内。这个案例是MySQL数据库性能分析最典型的路径。4.3 常见EXPLAIN结果误区看执行计划最忌讳“想当然”。举几个我实际踩过的坑看到key有值就觉得没问题。不一定比如key用到的是idx_status但type列的ref和range差别很大。如果WHERE status 1的选择性很差比如90%的数据都是status1优化器可能觉得用索引还不如全表扫这时候Extra容易出现Using where配合全表扫描。rows是个估算值不是精确值。如果表没有及时更新统计信息ANALYZE TABLE之后执行计划可能会大变。所以执行计划不准的时候先试试刷新统计信息。Using index condition不代表完美它只是说明索引下推生效了具体快不快还要看rows。Using temporary; Using filesort两连出现多半是GROUP BY或者DISTINCT没有匹配索引顺序。复合索引里字段顺序不同结果也可能完全不同。4.4 高效执行计划的几条实操经验总结根据这几年的经验遇到执行计划问题我一般按优先级做这几件事确认过滤条件里的列都有索引且索引顺序符合最左前缀原则尽量把排序字段也放进复合索引消灭Using filesort避免在索引列上做函数运算比如WHERE DATE(create_time) 2024-01-01会直接废掉索引正确写法是WHERE create_time 2024-01-01 AND create_time 2024-01-02注意隐式类型转换比如WHERE mobile 13800138000如果mobile是varchar类型MySQL会把索引列转成数字索引直接失效分页深翻页LIMIT 100000, 20性能差可以用延迟关联或游标分页替代这些细节不需要背多翻几次执行计划自然就有感觉了。5. 索引设计MySQL性能分析绕不开的核心落脚点5.1 什么时候该加索引什么时候不该加加索引确实能加速查询但索引不是越多越好。每个索引在写入时都要额外维护B树结构写入频繁的表如果索引过多INSERT/UPDATE性能会明显下降。除此之外索引还占用磁盘空间InnoDB的二级索引每个都要占用额外的存储。我自己的判断标准基本是三条表的读多写少且某列经常出现在WHERE、JOIN、ORDER BY、GROUP BY里那值得加索引某列的选择性太差比如性别字段只有两个值分布接近50%加索引意义不大索引数量控制在5个以内超过这个数要慎重考虑选择性怎么算用一句SQL就能看个大概SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;比值越接近1说明这个列做索引的效果越好。比值很低比如0.01左右说明这个列上的重复值很多走索引还不如直接扫表。5.2 复合索引的最左前缀原则复合索引是一个非常容易用错的东西。MySQL的B树索引在复合索引场景下是按照“从左到右”的字段顺序来组织数据的。也就是说一个(a, b, c)的复合索引能加速a、a,b、a,b,c三种查询条件但是没法直接加速只用b或只用c的查询。换句话说复合索引中的字段顺序决定了它能覆盖哪些查询场景。在设计时通常遵循一个经验法则等值条件的列放前面排序的列紧跟其后范围查询的列放最后比如查询条件是WHERE status 1 AND category_id 5 ORDER BY create_time DESC优先建(status, category_id, create_time)这种顺序的索引而不建议反过来。5.3 覆盖索引的价值覆盖索引是指“查询需要的所有列在索引树的叶子节点里都能找到”这样InnoDB就不需要回表取数据了。回表意味着从二级索引找到主键值之后还要根据主键再去聚簇索引里查一遍量大的时候开销非常明显。覆盖索引可以显著降低Rows_examined。举个例子用户表有id, name, email, status几列如果统计某个状态的用户数量SQL是SELECT COUNT(*) FROM users WHERE status 1;如果只有idx_status这个索引那InnoDB直接从索引树里数就行不需要回表因为COUNT(*)只需要知道有多少行索引里就包含了状态信息。但如果查询某列不在索引里就必须回表性能差距在千万级表上会非常明显。实战中我经常会把高频查询的所有列都放进复合索引里用空间换时间。5.4 索引失效的几种常见场景把容易踩的索引失效场景整理成一张表方便对照排查场景示例失效原因索引列参与运算WHERE age 1 20表达式导致索引顺序被破坏索引列用函数WHERE DATE(create_time) 2024-01-01函数作用在索引列上隐式类型转换WHERE mobile 13800138000varchar列与数字比较LIKE以通配符开头WHERE name LIKE %小明%无法利用B树有序性前缀匹配OR连接非索引列WHERE id 1 OR name 小明优化器无法有效合并索引字符串类型不引号WHERE varchar_col 123数字会被转为字符串导致索引失效的可能遇到执行计划明明显示有索引但查询还是慢的情况优先按这张表逐条排查。5.5 索引维护的好习惯索引不是建完就不管了。长期运行之后由于频繁删除和更新索引可能会出现碎片。碎片率高的索引扫描效率会明显下降。重建索引可以用ALTER TABLE table_name ENGINEInnoDB;或者对InnoDB执行OPTIMIZE TABLE table_name;不过要注意OPTIMIZE TABLE在表特别大的时候会锁表建议放在业务低峰期操作。日常维护上我更推荐人工巡检时观察一下索引使用情况从sys.schema_unused_indexes可以看到哪些索引从未被使用过这些没用的索引可以直接清掉减少写入开销。SELECT * FROM sys.schema_unused_indexes;6. 参数调优MySQL数据库性能分析的“下半场”6.1 SQL和索引优化完了才轮到参数有些同学一上来就改各种参数什么innodb_buffer_pool_size、max_connections改完一看效果有限。根因很简单SQL本身写得烂参数再大也扛不住全表扫描的消耗。所以调参有一个前提条件——先把慢SQL清理掉再谈参数优化。这个顺序一定不能反。6.2 几个关键参数的通俗理解MySQL的配置参数非常多但性能分析中真正高频修改的其实就那么几个。我按思维方式来解读innodb_buffer_pool_size这个参数决定了InnoDB在内存里能缓存多少数据和索引。如果这个值太小MySQL会频繁把磁盘块读进内存又因为内存不够而把其他块刷回磁盘造成大量磁盘IO。一般经验是把这个值设为物理内存的60%~75%但要预留出操作系统和其他进程的用量。max_connections这个参数控制最大连接数。很多人一看到Too many connections就拼命调大这个值。但如果是慢SQL占着连接不放调大只是把雪崩往后推了几分钟。更合理的做法是先查SHOW PROCESSLIST看看连接都在干什么如果都是Sleep状态那就是连接池配置问题如果都是Query状态且执行时间很长那优先去优化那几条SQL。innodb_io_capacity / innodb_io_capacity_max这两个参数决定了InnoDB刷脏页的能力。如果写密集业务把innodb_io_capacity设得很低脏页刷不过去整个写入流程会卡住。SSD一般可以设2000以上机械盘建议保守一点设400左右。tmp_table_size / max_heap_table_size这两个值决定内存临时表的上限。如果GROUP BY或者ORDER BY产生的临时表超过了这个值就会落到磁盘临时表性能暴跌。性能分析中如果发现大量Created_tmp_disk_tables状态值增长很快就可以考虑调大这两个参数。6.3 调参前后如何验证效果参数调完不能凭感觉说“好像快了一点”。我惯用的做法是调参前记录一组基准数据Questions、Slow_queries、Innodb_rows_read、Innodb_data_reads等修改参数重启实例有些参数可以动态修改不需要重启比如SET GLOBAL innodb_buffer_pool_size 8589934592让系统跑一段时间再次取相同指标对比增量变化用数据说话才能确定这次调整到底是有效还是负优化。6.4 动态修改与配置文件修改的双重确认MySQL部分参数支持SET GLOBAL在线修改但这类修改在重启后会失效。如果确认参数值有效务必同步改到配置文件里否则下次重启又回到原样等于白调。举个例子SET GLOBAL innodb_buffer_pool_size 8589934592;修改后在/etc/mysql/my.cnf里也需要同步这部分内容[mysqld] innodb_buffer_pool_size 8589934592双重确认这件事看起来小但很多人真的会忘记导致调参后到第二天一切回到解放前。7. 实战排查从一个真实案例看完整分析过程7.1 问题现象有次接手一个业务系统的数据库现象是每天上午10点到11点之间应用频繁超时监控面板上数据库CPU使用率接近100%。打开慢查询日志一看满屏都是同一类SQLSELECT * FROM logistics_track WHERE order_id 123456789 ORDER BY track_time DESC LIMIT 50;乍一看这个SQL条件很简单order_id上也有索引怎么会慢7.2 逐步排查过程先看执行计划EXPLAIN SELECT * FROM logistics_track WHERE order_id 123456789 ORDER BY track_time DESC LIMIT 50\G;结果出来typerefkeyidx_order_idrows86000。也就是说虽然用了索引但某个order_id对应的记录多达8万多条索引帮我们快速定位到这批数据但排序只能走filesort然后还要从8万多条里取前50条。再看看表结构路由表是大宽表字段特别多存储了一些长文本。虽然只取最近50条但SELECT *会把这50条里所有列全部查出来其中包含好几个大字段IO开销翻了几倍。7.3 优化方案这个案例的优化并不复杂做了两件事第一把索引改成复合索引覆盖“筛选排序”两个需求ALTER TABLE logistics_track ADD INDEX idx_order_track_time (order_id, track_time);这样ORDER BY可以直接用到索引顺序filesort消失。第二把SELECT *改成只查业务真正需要的字段避免取出大字段。前端列表页确实只需要track_time、location、status等几个字段完全没有必要把完整轨迹内容都拉出来。优化后执行计划变成typekeyrowsExtrarefidx_order_track_time86000Using index注意这里Using index代表数据库能够从索引本身取得所需数据无需回表。因为查询列都在idx_order_track_time索引里直接覆盖了查询需求。实际接口耗时从1.8秒降到了60毫秒数据库CPU峰值也掉到了30%以下。整个分析过程没有用到任何花哨工具就是慢查询日志加EXPLAIN两条路走到底。7.4 这个案例给到的三点启发字段选择是性能的一部分。SELECT *看着省事但在大宽表上代价很高。能少取列就少取列覆盖索引才有意义。不要只看有没有索引还要看索引有没有覆盖排序和查询列。typeref确实比ALL好但rows太大依然会慢。性能分析是一条链路从日志到执行计划再到表结构每一步都互相印证而不是靠拍脑袋。8. 日常巡检与监控体系的搭建建议8.1 监控项怎么选很多团队在监控上走了两个极端一个是什么都不监控出了事再救火另一个是什么都监控图表拉了一屏真出事时根本不知道看哪个。我个人建议优先盯住这几个指标指标意义危险阈值参考Threads_running当前正在执行查询的线程数持续超过CPU核数Threads_connected当前连接数逼近max_connectionsSlow_queries速率每秒新增慢查询数持续大于0Innodb_row_lock_waits行锁等待次数持续增长QPS/TPS每秒查询数/事务数与历史基线对比突变Buffer pool命中率缓存命中率低于95%需关注8.2 搭建最小可用监控体系如果公司暂时没有完善的监控平台可以用最朴素的手段搭一套最小可用的。核心思路是一个cron脚本定期采集状态值一个日志文件持续追加记录一条告警在关键指标超过阈值时发出来。比如用cron每5分钟执行一次采集脚本*/5 * * * * mysql -uroot -p*** -e SHOW GLOBAL STATUS /var/log/mysql_status.log 21再配合一个简单的Shell脚本做阈值判断。实际上线时也可以直接用Prometheus加mysqld_exporter替代这套手工方案。对中小团队来说Prometheusmysqld_exporterGrafana配合告警规则已经是很稳健的组合了。8.3 巡检报告的周期与重点我建议定期做一次数据库巡检节奏可以按周或者按月。巡检重点包括以下内容慢查询日志中Top 10的SQL逐一分析执行计划是否有退化索引使用情况清理从未使用的索引表碎片率看是否需要OPTIMIZE TABLE连接数趋势判断是否有连接泄漏磁盘空间剩余容量是否充足主从复制延迟状态这套动作下来大多数性能隐患会在变成事故之前就被消灭掉。9. 总结一下MySQL数据库性能分析的个人体会做了这么多年MySQL性能分析最大的感受是这个领域没有银弹也没有一条命令能解决所有问题。所谓的高手无非是掌握了完整的方法链知道什么时候看什么数据能从蛛丝马迹里定位到问题源头。最后分享一个我自己常用的兜底习惯每次上线新的SQL或者新功能之前都先跑一遍EXPLAIN确认执行计划的type不是ALL、没有Using filesort、rows在可接受范围再放上线。这个习惯帮我挡掉了至少一半的线上数据库事故。如果你还没有这个习惯建议从今天开始就把它加到发布流程里。即使你现在负责的项目体量还小这个动作的成本也极低但收益会在数据量增长之后体现得越来越明显。 
长河君 · 2026/9/24 19:55:48 · 阅读 1.2k MySQL数据库性能分析实战：从慢查询定位到索引优化
1. 先从“MySQL数据库性能分析”到底要解决什么问题说起做后端开发或者运维的人迟早都会遇到这么一天线上数据库突然CPU飙高、慢查询报警刷屏、接口响应从几十毫秒涨到几秒钟。这时候打开终端连上MySQL看着几百个连接堆积在那里脑海里只剩一个念头——先重启试试。但我先说一句可能不太中听的话遇到性能问题第一时间重启数据库是最亏的做法。重启确实能把积累的连接清掉慢查询也暂时消失了但问题本身完全没有被解决。第二天同一时间同样的告警又来一遍因为引发性能问题的SQL还是那几条索引还是没建数据量还在涨。真正靠谱的做法是沉下来做一次系统性的“MySQL数据库性能分析”搞清楚瓶颈到底出在哪个环节再对症下药。这篇内容我打算按实际排查顺序展开不讲虚的从“思路怎么搭”“工具怎么用”“参数怎么调”三层来拆尽量贴近一线干活场景。适合刚接手数据库维护的同学也适合写业务代码但经常被慢查询坑到的开发。里面涉及的SQL、命令、参数都是我自己在真实环境里验证过的可以直接拿去用。要知道MySQL数据库性能分析从来不是单一动作而是一条完整链路先建立性能基线和监控再通过慢查询日志和性能视图定位具体SQL然后用EXPLAIN分析执行计划最后落到索引优化和参数调整上。每一步都有对应的工具和手段下面一个一个说。2. 性能分析的几个前置动作2.1 先搞清楚当前实例的健康状态拿到一台出问题的MySQL不要急着看慢查询。第一步应该是看一眼这个实例整体处于什么状态。我常用的几条命令分享出来供参考mysql SHOW GLOBAL STATUS LIKE Threads_connected; mysql SHOW GLOBAL STATUS LIKE Threads_running; mysql SHOW GLOBAL STATUS LIKE Questions; mysql SHOW GLOBAL STATUS LIKE Slow_queries; mysql SHOW ENGINE INNODB STATUS\G;这几条分别对应连接数、正在执行的线程数、累计查询数、慢查询累计数和InnoDB引擎的内部状态。其中最关心的两个指标是Threads_running和Slow_queries。Threads_running如果长期大于CPU核数的若干倍说明系统确实在“忙”大量查询在争抢CPU资源。Slow_queries是累计值单看意义不大但如果拿它做两次快照算出一个时间段的增量然后再除以时间窗口的秒数就能得到“每秒产生多少慢查询”这样的实时速率这个数字才是有效的。另外SHOW ENGINE INNODB STATUS里面有一段LATEST DETECTED DEADLOCK和TRANSACTIONS信息在出现锁等待或者死锁时这里是第一现场的完整记录。平时没什么感觉真遇到问题的时候再去找数据库早就把状态刷过去了。2.2 开启性能分析必需的基础设施很多性能分析手段依赖历史数据所以“出事之前就把监控打开”这件事特别重要。这里说的基础设施主要包含三层慢查询日志——记录执行时间超过阈值的SQL这是定位问题最直接的依据。performance_schema——MySQL自带的一套性能采集引擎记录等待事件、锁信息、IO统计等。sys schema——基于performance_schema封装好的视图查询起来比直接查原始表方便很多比如sys.session能看到当前所有连接在干什么。慢查询日志的开启方式直接写在配置文件里再重启或者运行时动态开启都可以。需要注意的是动态开启在重启后会自动失效所以建议两边都配置。slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1这里的long_query_time我习惯设成1秒。有些团队为了保守会设成2甚至5秒但那样会把很多“不算最慢但确实有问题”的SQL漏掉。如果担心日志量太大可以用log_queries_not_using_indexes来重点抓那种全表扫描的查询这个后面会专门展开讲。2.3 确定性能基线的意义做性能分析最怕的就是“没有参照”。一条SQL跑500毫秒看起来挺快但如果这个表正常的查询都在20毫秒以内那500毫秒已经是严重退化了。反过来一个报表查询跑5秒如果历史一直都是这样用户也能接受那它就不一定是当前故障的根因。所以平时就要养成记录基线的习惯。不用很复杂定期执行一次下面这条命令把关键指标存到本地文件里就能形成一个最简单的趋势数据mysql -uroot -p -e SHOW GLOBAL STATUS /backup/mysql_status_$(date %F).txt后面出了性能问题直接对比当天的数据和历史数据很多疑点立刻就能排除掉。这个习惯成本极低收益却是实实在在的。3. 定位慢SQL从哪里找到真正的“元凶”3.1 慢查询日志的分析方法慢查询日志是纯文本格式每天可能产生几百兆甚至上G的内容。肉眼一页一页翻肯定不现实推荐两个思路思路一用mysqldumpslow做粗筛MySQL自带的mysqldumpslow工具虽然简单但在大部分场景下够用了。它的核心能力是把结构相似、只是参数不同的SQL归并成一条然后按总执行时间或平均执行时间排序。mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.log解释一下参数-s at按平均查询时间排序-t 20只显示前20条mysqldumpslow会自动把具体数值替换成N比如WHERE id 123会归并成WHERE id N这样同一个模板的SQL就会聚合在一起统计方便看出哪些“类型的SQL”整体耗时最多。思路二用pt-query-digest做细看pt-query-digest是Percona Toolkit里最常用的工具之一分析结果比mysqldumpslow详细得多。它会生成一份报告包含SQL模板的整体分布、每个模板的执行次数、平均耗时、响应时间占比等。最重要的一点是它能直接告诉我们哪些SQL贡献了系统绝大部分的负载。pt-query-digest /var/log/mysql/mysql-slow.log digest_report.txt报告前面会有一个“Profile”段落按响应时间总和倒序排列前几行就是最值得关注的SQL模板。我见过非常多案例问题其实就集中在一两条SQL上把它们优化掉数据库压力立减70%以上。3.2 没有慢查询日志时用information_schema救急有些情况比较特殊业务方说慢但你没权限开慢查询日志或者日志还没落盘就被轮转清掉了。这时候还有一条路就是从performance_schema和sys库找线索。-- 查看当前正在执行的所有SQL SELECT * FROM sys.session WHERE command Query\G; -- 查看消耗IO最多的前10个文件 SELECT * FROM sys.io_global_by_file_by_bytes ORDER BY total DESC LIMIT 10; -- 查看热点事件等待类 SELECT * FROM sys.latest_file_io;sys.session这个视图尤其好用相当于给MySQL装了一个“实时top命令”。能看到每个连接正在跑的SQL、连接来源、执行时间、状态等。数据库卡死的时候连上这个视图基本可以当场抓住“肇事SQL”。3.3 慢SQL日志的典型字段解读一条典型的慢查询记录长这样# Query_time: 3.876512 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 1023456 SET timestamp1699000000; SELECT * FROM orders WHERE customer_id 8888 ORDER BY create_time DESC LIMIT 10;关键信息集中在第一行Query_timeSQL从开始到结束的总耗时Lock_time等待锁的时间Rows_sent最终返回给客户端的行数Rows_examined引擎扫描过的行数Rows_examined和Rows_sent的差距越大说明扫描越浪费。比如上面这条扫描了102万行只返回10行基本上可以断定没走索引或者索引没建对。这个差距就是优化的核心线索。4. EXPLAIN执行计划MySQL性能分析的核心技能4.1 为什么执行计划是必须掌握的一环慢查询日志告诉我们“哪条SQL慢”但没说“为什么慢”。要回答为什么必须看MySQL是怎么执行这条SQL的。优化器会根据统计信息、索引情况、表大小等因素生成一个执行计划而执行计划直接决定了查询会用哪条索引、扫描多少行、是否需要临时表。用EXPLAIN关键字加在SQL前面就能看到这条SQL的执行计划。例如EXPLAIN SELECT * FROM orders WHERE customer_id 8888 ORDER BY create_time DESC LIMIT 10\G;输出结果里最需要关注的是下面几列type访问类型。从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就是全表扫描基本是性能问题的最大嫌疑。key实际用到的索引。如果为NULL说明这条SQL没用上任何索引。rows优化器估算需要扫描的行数。这个数字越大性能越差。Extra额外信息。出现Using filesort表示排序没有走索引出现Using temporary表示用了临时表两个都是性能杀手。4.2 一个实战案例从typeALL到typeref的优化为了讲清楚我构造一个简化场景。有一张订单表结构大致如下CREATE TABLE orders ( id int NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, customer_id int NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB;业务反馈“查询某用户的订单列表很慢”执行计划查出来是typekeyrowsExtraALLNULL1200000Using filesorttypeALL表示全表扫了120万行keyNULL表示没有索引可用Using filesort说明排序也只能额外做三重debuff叠满不慢才怪。优化方式很直接加一条复合索引ALTER TABLE orders ADD INDEX idx_customer_create (customer_id, create_time);再加一个索引顺序的说明customer_id用于筛选create_time用于排序把它们组合成一个复合索引可以同时满足WHERE customer_id ?的等值过滤和ORDER BY create_time的有序排序最终MySQL就能直接从索引里按顺序取数据避免filesort。执行计划立刻变成typekeyrowsExtrarefidx_customer_create58NULL扫描行数从120万降到58排序也没了那句慢SQL直接从3秒多降到10毫秒以内。这个案例是MySQL数据库性能分析最典型的路径。4.3 常见EXPLAIN结果误区看执行计划最忌讳“想当然”。举几个我实际踩过的坑看到key有值就觉得没问题。不一定比如key用到的是idx_status但type列的ref和range差别很大。如果WHERE status 1的选择性很差比如90%的数据都是status1优化器可能觉得用索引还不如全表扫这时候Extra容易出现Using where配合全表扫描。rows是个估算值不是精确值。如果表没有及时更新统计信息ANALYZE TABLE之后执行计划可能会大变。所以执行计划不准的时候先试试刷新统计信息。Using index condition不代表完美它只是说明索引下推生效了具体快不快还要看rows。Using temporary; Using filesort两连出现多半是GROUP BY或者DISTINCT没有匹配索引顺序。复合索引里字段顺序不同结果也可能完全不同。4.4 高效执行计划的几条实操经验总结根据这几年的经验遇到执行计划问题我一般按优先级做这几件事确认过滤条件里的列都有索引且索引顺序符合最左前缀原则尽量把排序字段也放进复合索引消灭Using filesort避免在索引列上做函数运算比如WHERE DATE(create_time) 2024-01-01会直接废掉索引正确写法是WHERE create_time 2024-01-01 AND create_time 2024-01-02注意隐式类型转换比如WHERE mobile 13800138000如果mobile是varchar类型MySQL会把索引列转成数字索引直接失效分页深翻页LIMIT 100000, 20性能差可以用延迟关联或游标分页替代这些细节不需要背多翻几次执行计划自然就有感觉了。5. 索引设计MySQL性能分析绕不开的核心落脚点5.1 什么时候该加索引什么时候不该加加索引确实能加速查询但索引不是越多越好。每个索引在写入时都要额外维护B树结构写入频繁的表如果索引过多INSERT/UPDATE性能会明显下降。除此之外索引还占用磁盘空间InnoDB的二级索引每个都要占用额外的存储。我自己的判断标准基本是三条表的读多写少且某列经常出现在WHERE、JOIN、ORDER BY、GROUP BY里那值得加索引某列的选择性太差比如性别字段只有两个值分布接近50%加索引意义不大索引数量控制在5个以内超过这个数要慎重考虑选择性怎么算用一句SQL就能看个大概SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;比值越接近1说明这个列做索引的效果越好。比值很低比如0.01左右说明这个列上的重复值很多走索引还不如直接扫表。5.2 复合索引的最左前缀原则复合索引是一个非常容易用错的东西。MySQL的B树索引在复合索引场景下是按照“从左到右”的字段顺序来组织数据的。也就是说一个(a, b, c)的复合索引能加速a、a,b、a,b,c三种查询条件但是没法直接加速只用b或只用c的查询。换句话说复合索引中的字段顺序决定了它能覆盖哪些查询场景。在设计时通常遵循一个经验法则等值条件的列放前面排序的列紧跟其后范围查询的列放最后比如查询条件是WHERE status 1 AND category_id 5 ORDER BY create_time DESC优先建(status, category_id, create_time)这种顺序的索引而不建议反过来。5.3 覆盖索引的价值覆盖索引是指“查询需要的所有列在索引树的叶子节点里都能找到”这样InnoDB就不需要回表取数据了。回表意味着从二级索引找到主键值之后还要根据主键再去聚簇索引里查一遍量大的时候开销非常明显。覆盖索引可以显著降低Rows_examined。举个例子用户表有id, name, email, status几列如果统计某个状态的用户数量SQL是SELECT COUNT(*) FROM users WHERE status 1;如果只有idx_status这个索引那InnoDB直接从索引树里数就行不需要回表因为COUNT(*)只需要知道有多少行索引里就包含了状态信息。但如果查询某列不在索引里就必须回表性能差距在千万级表上会非常明显。实战中我经常会把高频查询的所有列都放进复合索引里用空间换时间。5.4 索引失效的几种常见场景把容易踩的索引失效场景整理成一张表方便对照排查场景示例失效原因索引列参与运算WHERE age 1 20表达式导致索引顺序被破坏索引列用函数WHERE DATE(create_time) 2024-01-01函数作用在索引列上隐式类型转换WHERE mobile 13800138000varchar列与数字比较LIKE以通配符开头WHERE name LIKE %小明%无法利用B树有序性前缀匹配OR连接非索引列WHERE id 1 OR name 小明优化器无法有效合并索引字符串类型不引号WHERE varchar_col 123数字会被转为字符串导致索引失效的可能遇到执行计划明明显示有索引但查询还是慢的情况优先按这张表逐条排查。5.5 索引维护的好习惯索引不是建完就不管了。长期运行之后由于频繁删除和更新索引可能会出现碎片。碎片率高的索引扫描效率会明显下降。重建索引可以用ALTER TABLE table_name ENGINEInnoDB;或者对InnoDB执行OPTIMIZE TABLE table_name;不过要注意OPTIMIZE TABLE在表特别大的时候会锁表建议放在业务低峰期操作。日常维护上我更推荐人工巡检时观察一下索引使用情况从sys.schema_unused_indexes可以看到哪些索引从未被使用过这些没用的索引可以直接清掉减少写入开销。SELECT * FROM sys.schema_unused_indexes;6. 参数调优MySQL数据库性能分析的“下半场”6.1 SQL和索引优化完了才轮到参数有些同学一上来就改各种参数什么innodb_buffer_pool_size、max_connections改完一看效果有限。根因很简单SQL本身写得烂参数再大也扛不住全表扫描的消耗。所以调参有一个前提条件——先把慢SQL清理掉再谈参数优化。这个顺序一定不能反。6.2 几个关键参数的通俗理解MySQL的配置参数非常多但性能分析中真正高频修改的其实就那么几个。我按思维方式来解读innodb_buffer_pool_size这个参数决定了InnoDB在内存里能缓存多少数据和索引。如果这个值太小MySQL会频繁把磁盘块读进内存又因为内存不够而把其他块刷回磁盘造成大量磁盘IO。一般经验是把这个值设为物理内存的60%~75%但要预留出操作系统和其他进程的用量。max_connections这个参数控制最大连接数。很多人一看到Too many connections就拼命调大这个值。但如果是慢SQL占着连接不放调大只是把雪崩往后推了几分钟。更合理的做法是先查SHOW PROCESSLIST看看连接都在干什么如果都是Sleep状态那就是连接池配置问题如果都是Query状态且执行时间很长那优先去优化那几条SQL。innodb_io_capacity / innodb_io_capacity_max这两个参数决定了InnoDB刷脏页的能力。如果写密集业务把innodb_io_capacity设得很低脏页刷不过去整个写入流程会卡住。SSD一般可以设2000以上机械盘建议保守一点设400左右。tmp_table_size / max_heap_table_size这两个值决定内存临时表的上限。如果GROUP BY或者ORDER BY产生的临时表超过了这个值就会落到磁盘临时表性能暴跌。性能分析中如果发现大量Created_tmp_disk_tables状态值增长很快就可以考虑调大这两个参数。6.3 调参前后如何验证效果参数调完不能凭感觉说“好像快了一点”。我惯用的做法是调参前记录一组基准数据Questions、Slow_queries、Innodb_rows_read、Innodb_data_reads等修改参数重启实例有些参数可以动态修改不需要重启比如SET GLOBAL innodb_buffer_pool_size 8589934592让系统跑一段时间再次取相同指标对比增量变化用数据说话才能确定这次调整到底是有效还是负优化。6.4 动态修改与配置文件修改的双重确认MySQL部分参数支持SET GLOBAL在线修改但这类修改在重启后会失效。如果确认参数值有效务必同步改到配置文件里否则下次重启又回到原样等于白调。举个例子SET GLOBAL innodb_buffer_pool_size 8589934592;修改后在/etc/mysql/my.cnf里也需要同步这部分内容[mysqld] innodb_buffer_pool_size 8589934592双重确认这件事看起来小但很多人真的会忘记导致调参后到第二天一切回到解放前。7. 实战排查从一个真实案例看完整分析过程7.1 问题现象有次接手一个业务系统的数据库现象是每天上午10点到11点之间应用频繁超时监控面板上数据库CPU使用率接近100%。打开慢查询日志一看满屏都是同一类SQLSELECT * FROM logistics_track WHERE order_id 123456789 ORDER BY track_time DESC LIMIT 50;乍一看这个SQL条件很简单order_id上也有索引怎么会慢7.2 逐步排查过程先看执行计划EXPLAIN SELECT * FROM logistics_track WHERE order_id 123456789 ORDER BY track_time DESC LIMIT 50\G;结果出来typerefkeyidx_order_idrows86000。也就是说虽然用了索引但某个order_id对应的记录多达8万多条索引帮我们快速定位到这批数据但排序只能走filesort然后还要从8万多条里取前50条。再看看表结构路由表是大宽表字段特别多存储了一些长文本。虽然只取最近50条但SELECT *会把这50条里所有列全部查出来其中包含好几个大字段IO开销翻了几倍。7.3 优化方案这个案例的优化并不复杂做了两件事第一把索引改成复合索引覆盖“筛选排序”两个需求ALTER TABLE logistics_track ADD INDEX idx_order_track_time (order_id, track_time);这样ORDER BY可以直接用到索引顺序filesort消失。第二把SELECT *改成只查业务真正需要的字段避免取出大字段。前端列表页确实只需要track_time、location、status等几个字段完全没有必要把完整轨迹内容都拉出来。优化后执行计划变成typekeyrowsExtrarefidx_order_track_time86000Using index注意这里Using index代表数据库能够从索引本身取得所需数据无需回表。因为查询列都在idx_order_track_time索引里直接覆盖了查询需求。实际接口耗时从1.8秒降到了60毫秒数据库CPU峰值也掉到了30%以下。整个分析过程没有用到任何花哨工具就是慢查询日志加EXPLAIN两条路走到底。7.4 这个案例给到的三点启发字段选择是性能的一部分。SELECT *看着省事但在大宽表上代价很高。能少取列就少取列覆盖索引才有意义。不要只看有没有索引还要看索引有没有覆盖排序和查询列。typeref确实比ALL好但rows太大依然会慢。性能分析是一条链路从日志到执行计划再到表结构每一步都互相印证而不是靠拍脑袋。8. 日常巡检与监控体系的搭建建议8.1 监控项怎么选很多团队在监控上走了两个极端一个是什么都不监控出了事再救火另一个是什么都监控图表拉了一屏真出事时根本不知道看哪个。我个人建议优先盯住这几个指标指标意义危险阈值参考Threads_running当前正在执行查询的线程数持续超过CPU核数Threads_connected当前连接数逼近max_connectionsSlow_queries速率每秒新增慢查询数持续大于0Innodb_row_lock_waits行锁等待次数持续增长QPS/TPS每秒查询数/事务数与历史基线对比突变Buffer pool命中率缓存命中率低于95%需关注8.2 搭建最小可用监控体系如果公司暂时没有完善的监控平台可以用最朴素的手段搭一套最小可用的。核心思路是一个cron脚本定期采集状态值一个日志文件持续追加记录一条告警在关键指标超过阈值时发出来。比如用cron每5分钟执行一次采集脚本*/5 * * * * mysql -uroot -p*** -e SHOW GLOBAL STATUS /var/log/mysql_status.log 21再配合一个简单的Shell脚本做阈值判断。实际上线时也可以直接用Prometheus加mysqld_exporter替代这套手工方案。对中小团队来说Prometheusmysqld_exporterGrafana配合告警规则已经是很稳健的组合了。8.3 巡检报告的周期与重点我建议定期做一次数据库巡检节奏可以按周或者按月。巡检重点包括以下内容慢查询日志中Top 10的SQL逐一分析执行计划是否有退化索引使用情况清理从未使用的索引表碎片率看是否需要OPTIMIZE TABLE连接数趋势判断是否有连接泄漏磁盘空间剩余容量是否充足主从复制延迟状态这套动作下来大多数性能隐患会在变成事故之前就被消灭掉。9. 总结一下MySQL数据库性能分析的个人体会做了这么多年MySQL性能分析最大的感受是这个领域没有银弹也没有一条命令能解决所有问题。所谓的高手无非是掌握了完整的方法链知道什么时候看什么数据能从蛛丝马迹里定位到问题源头。最后分享一个我自己常用的兜底习惯每次上线新的SQL或者新功能之前都先跑一遍EXPLAIN确认执行计划的type不是ALL、没有Using filesort、rows在可接受范围再放上线。这个习惯帮我挡掉了至少一半的线上数据库事故。如果你还没有这个习惯建议从今天开始就把它加到发布流程里。即使你现在负责的项目体量还小这个动作的成本也极低但收益会在数据量增长之后体现得越来越明显。