TECH

MySQL慢查询优化实战指南：从索引原则到代价模型

系统梳理MySQL索引设计原则、慢查询优化基本步骤，以及基于代价模型的索引选择与推荐思路。

2023/12/214 min read

MySQL性能优化

慢查询是MySQL性能优化中最常见也最具挑战性的问题。一条慢SQL可能拖垮整个系统，而优化方案却往往让人无从下手。在十三Tech的数据库优化实践中，我们总结了一套从索引设计到代价模型分析的系统方法。本文将分享MySQL慢查询优化的核心原则与进阶思路，帮助你建立科学、可复现的SQL优化方法论。

建立索引的几个原则

最左前缀匹配原则
=和in可以乱序
尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)
索引列不能参与计算，保持列“干净”
尽量扩展索引，不要新建索引

慢查询优化基本步骤

先运行看看是否真的很慢，注意设置SQL_NO_CACHE
where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
order by limit 形式的sql语句让排序的表优先查
加索引时参照建索引的几大原则
观察结果，不符合预期继续从0分析

进阶-基于代价的推荐

基于代价的索引选择

使用 SQL select * from sync_test1 where name like 'Bobby%' and dt > '2021-07-06'为例

table

Create Table: CREATE TABLE `sync_test1` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `cid` int(11) NOT NULL, 
    `phone` int(11) NOT NULL, 
    `name` varchar(10) NOT NULL, 
    `address` varchar(255) DEFAULT NULL, 
    `dt` datetime DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    KEY `IX_name` (`name`), 
    KEY `IX_dt` (`dt`), 
    KEY `IX_dt_name` (`dt`,`name`), 
    KEY `IX_name_dt` (`name`,`dt`) 
) ENGINE=InnoDB

通过执行explain看出MySQL最终选择了IX_name索引

根据代价模型来推演一下代价的计算过程：

走全表扫描的代价：io_cost + cpu_cost = （数据页个数 * io_block_read_cost）+ (数据行数 * row_evaluate_cost + 1.1) = （data_length / block_size + 1）+ (rows * 0.2 + 1.1)
走二级索引IX_name的代价：io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01)
走二级索引IX_dt的代价：io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01)
走二级索引IX_dt_name的代价: io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01)
走二级索引IX_name_dt的代价：io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01)

基于代价的索引推荐思路

想借助MySQL优化器给慢查询计算出最佳索引，那么需要真实地在业务表上添加所有候选索引，对于线上业务来说，直接添加索引的时间空间成本太高，是不可接受的

MySQL优化器选最佳索引用到的数据是索引元数据和统计数据，所以我们想是否可以通过给它提供候选索引的这些数据，而非真实添加索引的这种方式来实现

MySQL本身就支持自定义存储引擎，所以索引推荐思路是构建一个支持虚假索引的存储引擎，在它上面建立包含候选索引的空表，再采集样本数据，计算出统计数据提供给优化器，让优化器选出最优索引，整个调用关系如下图所示：

总结

MySQL慢查询优化是一项系统工程，既需要遵循索引设计的基本原则，也需要掌握基于代价模型的分析能力。从最左前缀匹配到区分度计算，从EXPLAIN分析到自定义存储引擎的虚假索引推荐——优化的层次逐步深入，但核心始终不变：理解MySQL优化器的行为逻辑，用数据驱动决策。记住，没有放之四海而皆准的优化方案，只有针对具体场景的精准调优。更多MySQL性能优化实战，敬请关注十三Tech。