马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有账号?立即注册
×
核心结论与决策建议(BLUF)
在数据库性能治理中,盲目的“玄学调优”往往事倍功半。成熟的高性能架构遵循“指标发现 -> 慢日志 捕获与指纹归一 -> EXPLAIN 执行计划诊断 -> CBO 代价重构与索引设计”的标准化闭环链路。核心治理铁律:1. 建立基于的自动化监控 ,设置配合- log_queries_not_using_indexes
复制代码 捕获全量劣质SQL;2. 熟练掌握 EXPLAIN 八大核心指标,严禁生产环境出现、- Extra=Using temporary; Using filesort
复制代码 ;3. 深刻理解最左前缀原则、隐式类型转换及索引下推(ICP)机制。
| 调优排查阶段 | 核心工具与命令 | 重点关注指标 | 治理目标与行动 | | 第一阶段:全局发现 | , Performance Schema | 执行时长 State=Sending data, Copy to tmp | Kill 阻塞连接,恢复数据库可用性 | | 第二阶段:离线聚合 | | 总耗时占比、平均耗时、扫描行数比率 | 锁定消耗系统资源 Top 3 的 SQL 指纹 | | 第三阶段:单语句诊断 | / | type, rows, filtered, cost, actual time | 分析真实执行开销与物理访问路径 | | 第四阶段:重构落地 | 建立联合索引、覆盖索引、改写分页 | rows 从百万级收敛至十行以内 | 查询延迟稳定在 1ms ~ 5ms 以内 |
一、工业级慢查询日志 标准采集配置
在中进行无感抓取配置:
- [mysqld]
- slow_query_log = ON
- slow_query_log_file = /var/log/mysql/mysql-slow.log
- long_query_time = 0.5
- -- 记录所有未走索引的全表扫描慢查询
- log_queries_not_using_indexes = ON
- log_throttle_queries_not_using_indexes = 100
- -- 输出更详尽的诊断统计信息
- log_slow_verbosity = microsecond,query_cache,innodb
复制代码
使用 Percona Toolkit 的工具进行日度自动化报表提炼:
- pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow_report.txt
复制代码
二、EXPLAIN 执行计划黄金解密法则
获取执行计划:- EXPLAIN SELECT * FROM t_order WHERE ...
复制代码
1. type 列(访问类型由优到劣排序):
->(主键/唯一索引等值)>(多表关联唯一主键驱动)>(非唯一二级索引等值)>(索引范围扫描)>(全量索引树扫描)>(灾难级全表数据页物理扫描)。
- 生产底线:核心高并发业务必须达到 ref 或 range 以上,坚决杜绝 ALL。
2. rows 与 filtered 列(过滤有效度):
-:优化器预估需要扫描的物理行数。
-:经过当前条件过滤后留存的有效行百分比。
- 如果且,说明扫描了一百万行仅仅为了获取一千行数据,索引设计严重失准。
3. Extra 列关键告警信号:
- Using index:极佳!命中覆盖索引(Covering Index),直接在二级索引树返回结果,零次回表。
- Using index condition (ICP):优!命中索引下推,存储引擎层过滤不满足的二级索引列,大幅减少回表次数。
- Using filesort:警告!无法利用索引有序性,被迫在内存或磁盘上执行全量文件排序。
- Using temporary:高危!针对 GROUP BY 或 DISTINCT 创建了内部临时表,资源消耗严重。
三、八大导致索引失效的“经典陷阱”
1. 最左前缀匹配违例:建立了联合索引,查询条件却跳过 a 直接书写。
2. 函数计算与表达式操作:- WHERE DATE(create_time) = '2026-03-20'
复制代码 或,破坏B+树对数寻址。
3. 隐式类型转换:字段为字符串类型,查询参数却裸传整数:- WHERE phone = 13800000000
复制代码 。MySQL 会隐式调用函数导致索引全废。
4. 左模糊匹配:。B+树字符串排序基于从左向右的字符编码,前缀通配符导致全索引树失效。
5. OR 关联未全量索引:,若 a 有索引而 b 无索引,系统只能放弃索引走全表扫描。
常见问题(FAQ)
Q1: 为什么有时候字段明确有索引,MySQL 优化器依然选择走全表扫描?
A1: 这是由基于代价的优化器(CBO)计算得出的结果。若优化器预估通过二级索引查出的数据量超过全表的 20%~30%,由于每次命中索引都需要执行一次随机主键回表(Random I/O),此时顺序扫描聚簇索引连续数据页(Sequential I/O)的总代价反而低于成千上万次离散随机回表,因此优化器会“聪明”地放弃二级索引。
Q2: MySQL 8.0 独有的相比传统 EXPLAIN 强在哪里?
A2: 传统的 EXPLAIN 只是优化器在执行前的“纯理论预估”(依靠不一定准确的采样统计信息)。而会真正运行该 SQL,输出每一个执行算子真实的物理启动时间(Actual Time)、实际处理行数(Actual Rows)以及循环迭代次数(Loops),让 DBA 精准洞察预估与实际执行的偏差所在。
qidao123.com企服评测-应用市场 编辑提示:本文由企服大模型基于真实系统运维实践重构整理,聚焦技术原理、性能对比与选型避坑。若在实际部署中遇到异常,欢迎在下方跟帖交流;如需企业级技术支持或深度运维解决方案,请访问 qidao123.com 获取专属支持。 |