1. MySQL慢查询的本质与诊断方法当数据库响应时间超过预期阈值时通常默认超过10秒的查询会被标记为慢查询MySQL会将这些查询记录在慢查询日志中。但慢查询的本质不仅仅是执行时间长更核心的是资源占用率高、并发场景下容易引发雪崩效应的查询操作。1.1 慢查询的三大典型特征全表扫描通过EXPLAIN查看执行计划时出现typeALL的情况。我曾处理过一个案例某电商平台的商品搜索功能在没有正确使用索引时单次查询扫描了600万行数据。临时表与文件排序当Extra列出现Using temporary; Using filesort时说明查询需要创建临时表或在磁盘进行排序。某社交平台的feed流查询就因此导致CPU飙升至90%。不合理的数据访问包括N1查询问题ORM框架常见、大字段查询如SELECT * 包含TEXT类型字段等。一个内容管理系统就因为频繁查询文章全文导致IOPS超标。1.2 诊断工具链配置实操-- 启用慢查询日志需重启服务 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; -- 阈值设为2秒 SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log; -- 推荐配置my.cnf [mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2 log_queries_not_using_indexes 1 -- 记录未使用索引的查询 log_throttle_queries_not_using_indexes 10 -- 限制每分钟记录数量使用pt-query-digest工具分析日志pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt1.3 执行计划深度解读EXPLAIN输出关键字段解析字段危险值优化方向typeALL必须增加索引rows1000检查索引有效性ExtraUsing filesort优化ORDER BYkeyNULL缺失合适索引典型案例某金融系统交易记录查询优化前后对比-- 优化前执行时间3.8秒 EXPLAIN SELECT * FROM transactions WHERE user_id100 AND statuscompleted ORDER BY create_time DESC; -- 优化后执行时间0.02秒 ALTER TABLE transactions ADD INDEX idx_user_status_time (user_id, status, create_time);2. 索引优化实战策略2.1 B树索引设计原则最左前缀原则联合索引(a,b,c)只能支持a、ab、abc三种查询条件组合。某物流系统将省份城市区域设为联合索引后路由查询效率提升20倍。基数选择性字段不同值的数量/总行数应大于10%。例如性别字段就不适合单独建索引。覆盖索引当EXPLAIN的Extra出现Using index时表示查询只需访问索引。某CMS系统通过创建(title, publish_time)覆盖索引减少了80%的IO操作。2.2 索引避坑指南隐式类型转换VARCHAR字段用数字查询会导致索引失效-- 错误示例phone是varchar但用数字查询 SELECT * FROM users WHERE phone 13800138000; -- 正确写法 SELECT * FROM users WHERE phone 13800138000;函数操作索引列使用函数会导致失效-- 错误示例 SELECT * FROM orders WHERE DATE(create_time) 2023-01-01; -- 优化方案 SELECT * FROM orders WHERE create_time 2023-01-01 00:00:00 AND create_time 2023-01-02 00:00:00;2.3 索引维护策略-- 查看索引使用情况 SELECT * FROM sys.schema_unused_indexes; -- 定期优化表重建索引 OPTIMIZE TABLE orders; -- 在线DDL工具使用避免锁表 pt-online-schema-change --alter DROP INDEX idx_name Ddatabase,ttable3. 查询重写与架构优化3.1 SQL语句重构技巧分页优化避免使用LIMIT 100000,10-- 低效写法 SELECT * FROM articles ORDER BY id DESC LIMIT 100000, 10; -- 优化方案前提是id连续 SELECT * FROM articles WHERE id (SELECT id FROM articles ORDER BY id DESC LIMIT 100000, 1) ORDER BY id DESC LIMIT 10;JOIN优化某电商平台商品搜索改造案例-- 原始查询执行8秒 SELECT p.* FROM products p JOIN categories c ON p.category_id c.id WHERE c.name 电子产品 AND p.price 5000; -- 优化方案执行0.2秒 SELECT p.* FROM products p WHERE p.category_id IN ( SELECT id FROM categories WHERE name 电子产品 ) AND p.price 5000;3.2 数据库参数调优关键参数配置建议针对8核32GB内存的数据库服务器[mysqld] innodb_buffer_pool_size 24G # 总内存的70-80% innodb_log_file_size 2G # 日志文件大小 innodb_flush_log_at_trx_commit 2 # 非金融业务可放宽 sync_binlog 1000 # 批量同步 table_open_cache 4000 # 根据连接数调整4. 分库分表架构设计4.1 拆分策略选型对比策略类型适用场景优点缺点水平拆分单表数据量大扩展性强跨分片查询复杂垂直拆分字段访问差异大冷热分离需要业务改造哈希取模数据分布均匀简单直接扩容困难范围分片有明显范围特征易于扩容可能热点问题4.2 ShardingSphere实战案例某社交平台用户数据分片配置示例# 分片规则配置 rules: - !SHARDING tables: user: actualDataNodes: ds_${0..1}.user_${0..15} tableStrategy: standard: shardingColumn: user_id preciseAlgorithmClassName: org.apache.shardingsphere.sharding.algorithm.sharding.mod.HashModShardingAlgorithm props: sharding-count: 16 defaultDatabaseStrategy: standard: shardingColumn: user_id preciseAlgorithmClassName: org.apache.shardingsphere.sharding.algorithm.sharding.mod.HashModShardingAlgorithm props: sharding-count: 24.3 分库分表后的挑战应对分布式ID生成推荐使用Snowflake算法某IoT平台实测可支持每秒2万次ID生成跨库JOIN解决方案字段冗余将常用查询字段冗余到主表数据异构通过CDC工具同步到ES等搜索引擎内存计算先查分片数据再在应用层合并分布式事务// 使用Seata的AT模式示例 GlobalTransactional public void placeOrder(Order order) { orderDao.create(order); storageService.deduct(order.getCommodityCode(), order.getCount()); accountService.debit(order.getUserId(), order.getMoney()); }5. 替代方案与特殊场景处理5.1 读写分离架构某新闻门户网站的读写分离配置-- 主库配置 [mysqld] server-id 1 log-bin mysql-bin binlog-format ROW -- 从库配置 [mysqld] server-id 2 relay-log mysql-relay-bin read-only 1使用ProxySQL实现读写分离路由-- 配置规则 INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,master,3306); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,slave1,3306); -- 读写分离规则 INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (1,1,^SELECT.*FOR UPDATE,10,1),(2,1,^SELECT,20,1);5.2 冷热数据分离某电商平台的订单归档方案-- 创建归档表使用TokuDB引擎 CREATE TABLE orders_archive ( id BIGINT PRIMARY KEY, user_id INT, -- 其他字段 ) ENGINETokuDB; -- 数据迁移存储过程 DELIMITER // CREATE PROCEDURE archive_orders(IN cutoff_date DATE) BEGIN INSERT INTO orders_archive SELECT * FROM orders WHERE create_time cutoff_date; DELETE FROM orders WHERE create_time cutoff_date; END // DELIMITER ;6. 监控与持续优化体系6.1 性能监控指标看板关键监控项清单QPS/TPS波动连接数使用率Threads_connected/max_connections缓存命中率Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests慢查询增长率复制延迟Seconds_Behind_MasterPrometheus监控配置示例scrape_configs: - job_name: mysql static_configs: - targets: [mysql-exporter:9104] params: collect[]: - global_status - info_schema.innodb_metrics - slave_status6.2 压力测试方法论使用sysbench进行基准测试# 准备测试数据 sysbench oltp_read_write \ --db-drivermysql \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-usertest \ --mysql-passwordtest \ --mysql-dbsbtest \ --tables10 \ --table-size1000000 \ prepare # 执行测试 sysbench oltp_read_write \ --threads32 \ --time300 \ --report-interval10 \ run测试结果关键指标解读95%延迟应100msTPS波动范围不超过平均值的20%错误率必须为0
1. MySQL慢查询的本质与诊断方法当数据库响应时间超过预期阈值时通常默认超过10秒的查询会被标记为慢查询MySQL会将这些查询记录在慢查询日志中。但慢查询的本质不仅仅是执行时间长更核心的是资源占用率高、并发场景下容易引发雪崩效应的查询操作。1.1 慢查询的三大典型特征全表扫描通过EXPLAIN查看执行计划时出现typeALL的情况。我曾处理过一个案例某电商平台的商品搜索功能在没有正确使用索引时单次查询扫描了600万行数据。临时表与文件排序当Extra列出现Using temporary; Using filesort时说明查询需要创建临时表或在磁盘进行排序。某社交平台的feed流查询就因此导致CPU飙升至90%。不合理的数据访问包括N1查询问题ORM框架常见、大字段查询如SELECT * 包含TEXT类型字段等。一个内容管理系统就因为频繁查询文章全文导致IOPS超标。1.2 诊断工具链配置实操-- 启用慢查询日志需重启服务 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; -- 阈值设为2秒 SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log; -- 推荐配置my.cnf [mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2 log_queries_not_using_indexes 1 -- 记录未使用索引的查询 log_throttle_queries_not_using_indexes 10 -- 限制每分钟记录数量使用pt-query-digest工具分析日志pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt1.3 执行计划深度解读EXPLAIN输出关键字段解析字段危险值优化方向typeALL必须增加索引rows1000检查索引有效性ExtraUsing filesort优化ORDER BYkeyNULL缺失合适索引典型案例某金融系统交易记录查询优化前后对比-- 优化前执行时间3.8秒 EXPLAIN SELECT * FROM transactions WHERE user_id100 AND statuscompleted ORDER BY create_time DESC; -- 优化后执行时间0.02秒 ALTER TABLE transactions ADD INDEX idx_user_status_time (user_id, status, create_time);2. 索引优化实战策略2.1 B树索引设计原则最左前缀原则联合索引(a,b,c)只能支持a、ab、abc三种查询条件组合。某物流系统将省份城市区域设为联合索引后路由查询效率提升20倍。基数选择性字段不同值的数量/总行数应大于10%。例如性别字段就不适合单独建索引。覆盖索引当EXPLAIN的Extra出现Using index时表示查询只需访问索引。某CMS系统通过创建(title, publish_time)覆盖索引减少了80%的IO操作。2.2 索引避坑指南隐式类型转换VARCHAR字段用数字查询会导致索引失效-- 错误示例phone是varchar但用数字查询 SELECT * FROM users WHERE phone 13800138000; -- 正确写法 SELECT * FROM users WHERE phone 13800138000;函数操作索引列使用函数会导致失效-- 错误示例 SELECT * FROM orders WHERE DATE(create_time) 2023-01-01; -- 优化方案 SELECT * FROM orders WHERE create_time 2023-01-01 00:00:00 AND create_time 2023-01-02 00:00:00;2.3 索引维护策略-- 查看索引使用情况 SELECT * FROM sys.schema_unused_indexes; -- 定期优化表重建索引 OPTIMIZE TABLE orders; -- 在线DDL工具使用避免锁表 pt-online-schema-change --alter DROP INDEX idx_name Ddatabase,ttable3. 查询重写与架构优化3.1 SQL语句重构技巧分页优化避免使用LIMIT 100000,10-- 低效写法 SELECT * FROM articles ORDER BY id DESC LIMIT 100000, 10; -- 优化方案前提是id连续 SELECT * FROM articles WHERE id (SELECT id FROM articles ORDER BY id DESC LIMIT 100000, 1) ORDER BY id DESC LIMIT 10;JOIN优化某电商平台商品搜索改造案例-- 原始查询执行8秒 SELECT p.* FROM products p JOIN categories c ON p.category_id c.id WHERE c.name 电子产品 AND p.price 5000; -- 优化方案执行0.2秒 SELECT p.* FROM products p WHERE p.category_id IN ( SELECT id FROM categories WHERE name 电子产品 ) AND p.price 5000;3.2 数据库参数调优关键参数配置建议针对8核32GB内存的数据库服务器[mysqld] innodb_buffer_pool_size 24G # 总内存的70-80% innodb_log_file_size 2G # 日志文件大小 innodb_flush_log_at_trx_commit 2 # 非金融业务可放宽 sync_binlog 1000 # 批量同步 table_open_cache 4000 # 根据连接数调整4. 分库分表架构设计4.1 拆分策略选型对比策略类型适用场景优点缺点水平拆分单表数据量大扩展性强跨分片查询复杂垂直拆分字段访问差异大冷热分离需要业务改造哈希取模数据分布均匀简单直接扩容困难范围分片有明显范围特征易于扩容可能热点问题4.2 ShardingSphere实战案例某社交平台用户数据分片配置示例# 分片规则配置 rules: - !SHARDING tables: user: actualDataNodes: ds_${0..1}.user_${0..15} tableStrategy: standard: shardingColumn: user_id preciseAlgorithmClassName: org.apache.shardingsphere.sharding.algorithm.sharding.mod.HashModShardingAlgorithm props: sharding-count: 16 defaultDatabaseStrategy: standard: shardingColumn: user_id preciseAlgorithmClassName: org.apache.shardingsphere.sharding.algorithm.sharding.mod.HashModShardingAlgorithm props: sharding-count: 24.3 分库分表后的挑战应对分布式ID生成推荐使用Snowflake算法某IoT平台实测可支持每秒2万次ID生成跨库JOIN解决方案字段冗余将常用查询字段冗余到主表数据异构通过CDC工具同步到ES等搜索引擎内存计算先查分片数据再在应用层合并分布式事务// 使用Seata的AT模式示例 GlobalTransactional public void placeOrder(Order order) { orderDao.create(order); storageService.deduct(order.getCommodityCode(), order.getCount()); accountService.debit(order.getUserId(), order.getMoney()); }5. 替代方案与特殊场景处理5.1 读写分离架构某新闻门户网站的读写分离配置-- 主库配置 [mysqld] server-id 1 log-bin mysql-bin binlog-format ROW -- 从库配置 [mysqld] server-id 2 relay-log mysql-relay-bin read-only 1使用ProxySQL实现读写分离路由-- 配置规则 INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,master,3306); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,slave1,3306); -- 读写分离规则 INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (1,1,^SELECT.*FOR UPDATE,10,1),(2,1,^SELECT,20,1);5.2 冷热数据分离某电商平台的订单归档方案-- 创建归档表使用TokuDB引擎 CREATE TABLE orders_archive ( id BIGINT PRIMARY KEY, user_id INT, -- 其他字段 ) ENGINETokuDB; -- 数据迁移存储过程 DELIMITER // CREATE PROCEDURE archive_orders(IN cutoff_date DATE) BEGIN INSERT INTO orders_archive SELECT * FROM orders WHERE create_time cutoff_date; DELETE FROM orders WHERE create_time cutoff_date; END // DELIMITER ;6. 监控与持续优化体系6.1 性能监控指标看板关键监控项清单QPS/TPS波动连接数使用率Threads_connected/max_connections缓存命中率Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests慢查询增长率复制延迟Seconds_Behind_MasterPrometheus监控配置示例scrape_configs: - job_name: mysql static_configs: - targets: [mysql-exporter:9104] params: collect[]: - global_status - info_schema.innodb_metrics - slave_status6.2 压力测试方法论使用sysbench进行基准测试# 准备测试数据 sysbench oltp_read_write \ --db-drivermysql \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-usertest \ --mysql-passwordtest \ --mysql-dbsbtest \ --tables10 \ --table-size1000000 \ prepare # 执行测试 sysbench oltp_read_write \ --threads32 \ --time300 \ --report-interval10 \ run测试结果关键指标解读95%延迟应100msTPS波动范围不超过平均值的20%错误率必须为0
)
