1. 为什么MySQL性能问题如此棘手MySQL作为最流行的开源关系型数据库几乎支撑着互联网半壁江山。但真正在生产环境摸爬滚打过的DBA都知道MySQL就像一辆需要精心调校的跑车——默认配置下它可能连共享单车都跑不过。我见过太多团队在业务量暴增时被三个经典问题按在地上摩擦慢查询像老牛拉车、锁等待导致线程堆积、连接池爆满引发雪崩。这些问题往往不是孤立存在而是像多米诺骨牌一样连环触发。上周刚处理过一个典型案例某电商平台大促时订单提交接口响应时间从200ms飙升到15秒。排查发现先是优惠券核销的UPDATE语句没走索引慢查询然后事务持有行锁时间过长锁等待最终导致连接池200个连接全部卡死连接爆满。这三个问题环环相扣需要系统性的解决思路。2. 慢查询数据库的慢性病2.1 慢查询日志你的第一道防线慢查询日志是MySQL内置的诊断工具通过设置long_query_time阈值建议从1秒开始记录所有执行超过该时间的SQL语句。配置方法很简单-- 查看当前设置 SHOW VARIABLES LIKE %slow_query%; -- 启用慢查询日志需重启 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;但大多数团队都犯了一个错误——只收集不分析。我建议用pt-query-digest工具定期分析慢日志# 安装Percona Toolkit wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-toolkit # 分析慢日志 pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt报告会帮你找出最耗时的SQL、执行频率最高的慢查询等关键信息。去年我们通过这个工具发现某条看似简单的SELECT竟占了80%的慢查询总量——因为它全表扫描了2000万条记录。2.2 索引一把双刃剑索引是解决慢查询的银弹错我见过太多过度索引的案例。某用户表建了15个索引导致INSERT性能下降60%。好的索引策略需要理解基数原则性别字段只有男/女两种值建索引几乎没用。而用户ID这种高基数字段才是索引的最佳选择最左前缀原则对于复合索引INDEX(a,b,c)以下查询能用上索引WHERE a1 AND b2 WHERE a1 ORDER BY b但WHERE b2就用不上覆盖索引如果查询只需要索引包含的字段可以避免回表操作。比如-- 需要回表 SELECT * FROM users WHERE age 20; -- 覆盖索引 SELECT id, age FROM users WHERE age 20;实战技巧用EXPLAIN检查执行计划时重点看type列最好到range级别、Extra列避免出现Using filesort或Using temporary3. 锁等待数据库的交通堵塞3.1 锁类型全景图MySQL的锁机制复杂得像北京早高峰锁类型特点典型场景行锁粒度最小InnoDB默认UPDATE某条具体记录时表锁MyISAM默认阻塞整个表ALTER TABLE时间隙锁锁定索引记录间的间隙REPEATABLE-READ隔离级别下意向锁表明准备获取某种锁的信号灯事务要加表锁前先获取意向锁去年我们遇到过一个死锁事务A先更新id1的记录再更新id2事务B相反顺序更新。两个事务各自持有对方需要的锁形成环路。解决方案是统一按照id升序处理。3.2 锁监控与优化查看当前锁情况-- 查看正在等待锁的事务 SELECT * FROM performance_schema.events_waits_current WHERE EVENT_NAME LIKE %lock%; -- 查看InnoDB锁状态 SHOW ENGINE INNODB STATUS;关键优化手段缩短事务把非数据库操作如API调用移出事务降低隔离级别从SERIALIZABLE降到READ COMMITTED热点数据拆分比如把计数器拆成多行用随机方式更新使用乐观锁通过version字段实现CAS操作血泪教训某金融系统在转账时用SELECT...FOR UPDATE锁住账户结果高峰期锁等待超时。后来改用乐观锁重试机制吞吐量提升了8倍。4. 连接爆满压垮数据库的最后一根稻草4.1 连接池配置艺术MySQL默认最大连接数是151这在生产环境远远不够。但盲目调大max_connections会导致内存暴涨每个连接需要约4MB内存。科学做法是计算理论最大值# 查看可用内存MB free -m # 计算最大连接数保留20%余量 max_conn (可用内存 * 0.8) / 4使用连接池中间件如HikariCP配置HikariConfig config new HikariConfig(); config.setMaximumPoolSize(50); // 不要超过mysql的max_connections config.setConnectionTimeout(30000); // 30秒获取不到连接就报错 config.setIdleTimeout(600000); // 10分钟空闲连接回收4.2 连接泄漏排查连接泄漏就像内存泄漏一样致命。排查步骤查看当前连接SHOW PROCESSLIST;重点关注Sleep状态超过60秒的连接相同SQL的大量连接来自同一IP的异常连接使用performance_schema追踪-- 启用连接追踪 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %connection%; -- 查看连接统计 SELECT * FROM performance_schema.accounts;某次事故中我们发现某服务因为异常未关闭连接导致200个连接全部泄漏。最终通过增加连接存活检测和强制回收机制解决。5. 性能调优实战从监控到优化5.1 监控指标体系完整的MySQL监控应该包括资源层CPU使用率不要超过70%内存使用关注swap使用情况磁盘IOPS特别是随机写MySQL层-- QPS/TPS SHOW GLOBAL STATUS LIKE Questions%; -- 缓存命中率 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; -- 锁等待 SHOW GLOBAL STATUS LIKE Innodb_row_lock%;业务层关键接口响应时间事务成功率查询平均耗时5.2 参数调优黄金法则my.cnf关键参数以8核32G内存为例[mysqld] # 内存相关 innodb_buffer_pool_size 24G # 70%物理内存 innodb_log_file_size 2G # 日志文件大小 innodb_flush_log_at_trx_commit 2 # 平衡安全与性能 # 连接相关 max_connections 500 thread_cache_size 50 wait_timeout 300 # 查询优化 query_cache_type 0 # 8.0已移除查询缓存 tmp_table_size 64M max_heap_table_size 64M调优不是一劳永逸的我们每个月都会根据业务变化调整参数。去年双11前通过调整innodb_io_capacity参数使写入性能提升了35%。6. 高级武器库当常规手段失效时6.1 读写分离架构当单实例无法承受压力时经典方案是读写分离主库Master← 同步 → 从库Slave ↑ [写操作] [读操作] ↗ ↖ 从库1 从库2配置步骤主库开启binlog[mysqld] log-binmysql-bin server-id1从库配置CHANGE MASTER TO MASTER_HOSTmaster_ip, MASTER_USERrepl_user, MASTER_PASSWORDpassword, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS120; START SLAVE;6.2 分库分表策略当单表超过500万行就要考虑分片。常用方案水平分表按行拆分如user表拆分为user_0到user_9垂直分库按业务拆分如订单库、用户库分离时间分片按时间归档历史数据使用ShardingSphere实现透明的分片访问# 配置分片规则 spring: shardingsphere: datasource: names: ds0,ds1 sharding: tables: t_order: actual-data-nodes: ds$-{0..1}.t_order_$-{0..15} table-strategy: inline: sharding-column: order_id algorithm-expression: t_order_$-{order_id % 16}7. 那些年我们踩过的坑隐式转换陷阱-- phone是varchar类型但用数字查询会导致全表扫描 SELECT * FROM users WHERE phone 13800138000;解决方案保持类型一致WHERE phone 13800138000事务中的慢查询BEGIN; SELECT * FROM big_table WHERE ...; -- 慢查询 UPDATE ...; -- 持有锁时间过长 COMMIT;改为先执行SELECT再开启事务连接池配置不当连接数过小导致请求排队连接数过大耗尽数据库资源最佳实践根据压测结果动态调整错误的NULL处理-- 不会使用索引 SELECT * FROM users WHERE name ! John; -- 应该用 SELECT * FROM users WHERE name IS NULL OR name ! John;每次性能调优就像侦探破案需要结合监控数据、日志分析和业务理解。最近我们通过APM工具发现某条SQL在业务高峰期执行计划突然变化原来是统计信息过时导致优化器选错索引。建立定期的ANALYZE TABLE任务后问题解决。
1. 为什么MySQL性能问题如此棘手MySQL作为最流行的开源关系型数据库几乎支撑着互联网半壁江山。但真正在生产环境摸爬滚打过的DBA都知道MySQL就像一辆需要精心调校的跑车——默认配置下它可能连共享单车都跑不过。我见过太多团队在业务量暴增时被三个经典问题按在地上摩擦慢查询像老牛拉车、锁等待导致线程堆积、连接池爆满引发雪崩。这些问题往往不是孤立存在而是像多米诺骨牌一样连环触发。上周刚处理过一个典型案例某电商平台大促时订单提交接口响应时间从200ms飙升到15秒。排查发现先是优惠券核销的UPDATE语句没走索引慢查询然后事务持有行锁时间过长锁等待最终导致连接池200个连接全部卡死连接爆满。这三个问题环环相扣需要系统性的解决思路。2. 慢查询数据库的慢性病2.1 慢查询日志你的第一道防线慢查询日志是MySQL内置的诊断工具通过设置long_query_time阈值建议从1秒开始记录所有执行超过该时间的SQL语句。配置方法很简单-- 查看当前设置 SHOW VARIABLES LIKE %slow_query%; -- 启用慢查询日志需重启 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;但大多数团队都犯了一个错误——只收集不分析。我建议用pt-query-digest工具定期分析慢日志# 安装Percona Toolkit wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-toolkit # 分析慢日志 pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt报告会帮你找出最耗时的SQL、执行频率最高的慢查询等关键信息。去年我们通过这个工具发现某条看似简单的SELECT竟占了80%的慢查询总量——因为它全表扫描了2000万条记录。2.2 索引一把双刃剑索引是解决慢查询的银弹错我见过太多过度索引的案例。某用户表建了15个索引导致INSERT性能下降60%。好的索引策略需要理解基数原则性别字段只有男/女两种值建索引几乎没用。而用户ID这种高基数字段才是索引的最佳选择最左前缀原则对于复合索引INDEX(a,b,c)以下查询能用上索引WHERE a1 AND b2 WHERE a1 ORDER BY b但WHERE b2就用不上覆盖索引如果查询只需要索引包含的字段可以避免回表操作。比如-- 需要回表 SELECT * FROM users WHERE age 20; -- 覆盖索引 SELECT id, age FROM users WHERE age 20;实战技巧用EXPLAIN检查执行计划时重点看type列最好到range级别、Extra列避免出现Using filesort或Using temporary3. 锁等待数据库的交通堵塞3.1 锁类型全景图MySQL的锁机制复杂得像北京早高峰锁类型特点典型场景行锁粒度最小InnoDB默认UPDATE某条具体记录时表锁MyISAM默认阻塞整个表ALTER TABLE时间隙锁锁定索引记录间的间隙REPEATABLE-READ隔离级别下意向锁表明准备获取某种锁的信号灯事务要加表锁前先获取意向锁去年我们遇到过一个死锁事务A先更新id1的记录再更新id2事务B相反顺序更新。两个事务各自持有对方需要的锁形成环路。解决方案是统一按照id升序处理。3.2 锁监控与优化查看当前锁情况-- 查看正在等待锁的事务 SELECT * FROM performance_schema.events_waits_current WHERE EVENT_NAME LIKE %lock%; -- 查看InnoDB锁状态 SHOW ENGINE INNODB STATUS;关键优化手段缩短事务把非数据库操作如API调用移出事务降低隔离级别从SERIALIZABLE降到READ COMMITTED热点数据拆分比如把计数器拆成多行用随机方式更新使用乐观锁通过version字段实现CAS操作血泪教训某金融系统在转账时用SELECT...FOR UPDATE锁住账户结果高峰期锁等待超时。后来改用乐观锁重试机制吞吐量提升了8倍。4. 连接爆满压垮数据库的最后一根稻草4.1 连接池配置艺术MySQL默认最大连接数是151这在生产环境远远不够。但盲目调大max_connections会导致内存暴涨每个连接需要约4MB内存。科学做法是计算理论最大值# 查看可用内存MB free -m # 计算最大连接数保留20%余量 max_conn (可用内存 * 0.8) / 4使用连接池中间件如HikariCP配置HikariConfig config new HikariConfig(); config.setMaximumPoolSize(50); // 不要超过mysql的max_connections config.setConnectionTimeout(30000); // 30秒获取不到连接就报错 config.setIdleTimeout(600000); // 10分钟空闲连接回收4.2 连接泄漏排查连接泄漏就像内存泄漏一样致命。排查步骤查看当前连接SHOW PROCESSLIST;重点关注Sleep状态超过60秒的连接相同SQL的大量连接来自同一IP的异常连接使用performance_schema追踪-- 启用连接追踪 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %connection%; -- 查看连接统计 SELECT * FROM performance_schema.accounts;某次事故中我们发现某服务因为异常未关闭连接导致200个连接全部泄漏。最终通过增加连接存活检测和强制回收机制解决。5. 性能调优实战从监控到优化5.1 监控指标体系完整的MySQL监控应该包括资源层CPU使用率不要超过70%内存使用关注swap使用情况磁盘IOPS特别是随机写MySQL层-- QPS/TPS SHOW GLOBAL STATUS LIKE Questions%; -- 缓存命中率 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; -- 锁等待 SHOW GLOBAL STATUS LIKE Innodb_row_lock%;业务层关键接口响应时间事务成功率查询平均耗时5.2 参数调优黄金法则my.cnf关键参数以8核32G内存为例[mysqld] # 内存相关 innodb_buffer_pool_size 24G # 70%物理内存 innodb_log_file_size 2G # 日志文件大小 innodb_flush_log_at_trx_commit 2 # 平衡安全与性能 # 连接相关 max_connections 500 thread_cache_size 50 wait_timeout 300 # 查询优化 query_cache_type 0 # 8.0已移除查询缓存 tmp_table_size 64M max_heap_table_size 64M调优不是一劳永逸的我们每个月都会根据业务变化调整参数。去年双11前通过调整innodb_io_capacity参数使写入性能提升了35%。6. 高级武器库当常规手段失效时6.1 读写分离架构当单实例无法承受压力时经典方案是读写分离主库Master← 同步 → 从库Slave ↑ [写操作] [读操作] ↗ ↖ 从库1 从库2配置步骤主库开启binlog[mysqld] log-binmysql-bin server-id1从库配置CHANGE MASTER TO MASTER_HOSTmaster_ip, MASTER_USERrepl_user, MASTER_PASSWORDpassword, MASTER_LOG_FILEmysql-bin.000001, MASTER_LOG_POS120; START SLAVE;6.2 分库分表策略当单表超过500万行就要考虑分片。常用方案水平分表按行拆分如user表拆分为user_0到user_9垂直分库按业务拆分如订单库、用户库分离时间分片按时间归档历史数据使用ShardingSphere实现透明的分片访问# 配置分片规则 spring: shardingsphere: datasource: names: ds0,ds1 sharding: tables: t_order: actual-data-nodes: ds$-{0..1}.t_order_$-{0..15} table-strategy: inline: sharding-column: order_id algorithm-expression: t_order_$-{order_id % 16}7. 那些年我们踩过的坑隐式转换陷阱-- phone是varchar类型但用数字查询会导致全表扫描 SELECT * FROM users WHERE phone 13800138000;解决方案保持类型一致WHERE phone 13800138000事务中的慢查询BEGIN; SELECT * FROM big_table WHERE ...; -- 慢查询 UPDATE ...; -- 持有锁时间过长 COMMIT;改为先执行SELECT再开启事务连接池配置不当连接数过小导致请求排队连接数过大耗尽数据库资源最佳实践根据压测结果动态调整错误的NULL处理-- 不会使用索引 SELECT * FROM users WHERE name ! John; -- 应该用 SELECT * FROM users WHERE name IS NULL OR name ! John;每次性能调优就像侦探破案需要结合监控数据、日志分析和业务理解。最近我们通过APM工具发现某条SQL在业务高峰期执行计划突然变化原来是统计信息过时导致优化器选错索引。建立定期的ANALYZE TABLE任务后问题解决。
