MYSQL优化常用策略
1. 优化数据访问
这种策略的思想是尽量减少数据访问。涵盖两方面：
一是应用程序减少对数据库数据的访问，包括访问频次和访问范围，对于数据时效性高的场景应当用中间件解决，而不是频繁访问数据库。
二是访问数据要减少实际扫描的记录数。
比如我们常会写下面这样的语句
select * from tb_orders where user_id = 123 order by create_time desc limit 20;我们选择使用投影
select id,user_id,status,create_time from tb_orders where user_id = 123 order by create_time desc limit 20;这样用不到的列是不会被扫描的，尤其是对于宽表来说，性能将提高不少。
尽早过滤是另外一种减少数据扫描的方法。
这同样又包含两种办法，一是过滤条件要早生效，这样后续的
jion/sort/agg就会越轻松。二是where使用等值/范围,而不要使用表达式包裹# 反例如下 where DATE(create_time) > '2025-12-29' # 推荐 where create_time >= '2025-12-29 00:00:00' and create_time < '2025-12-30 00:00:00'控制返回行数
LIMIT子句有一个深分页问题，这往往也是性能结症所在。
select id,create_time from orders where user_id = 123 order by create_time desc limit 100000,20;上面的语句就是典型的深分页，
limit offset,size。深分页慢的核心原因是MYSQL通常会按照
ORDER BY找到前offset + size条记录，然后再丢弃前offset记录，只返回最后的size条记录。以上例而言，要找到100001 ~ 100020条记录，数据库往往会先处理100020条记录。offset越大，scan/sort/agg的工作量越接近线性增长。即使有索引的清空下，仍要走大量的读索引+回表读取完整行的步骤，IO和CPU都会网上走。更好的替代方案是使用上一页组后一条记录的排序健作为游标条件，而不是
offset。这样MYSQL可以通过索引定位到游标位置，再向后取20条，直接跳过前面的100000行扫描。select id,create_time from orders where user_id = 123 and (create_time,id) < ('2025-12-29 10:00:00',987654) order by create_time desc,id desc limit 20; # 对应推荐索引 create index idx_tb_orders_user_time_id on tb_orders(user_id,create_time,id);
在EXPLAIN中，type尽量从ALL变成range/ref/eq_ref/const,rows明显过大的要注意，Extra中出现Using temporary也要注意,key/key_len是否和预期一样索引命中。
2. 重写SQL
重写往往要结合业务进行等效SQL的编写，既然是重写那一定是针对之前不合理的地方进行修改，主要有以下常见的重写场景：
不合理的表达式
避免在条件李对列做运算/套函数。这会让索引难以使用。比如
where col + 1 = 10或者where lower(email) = 'a@b.com'。OR改写为UNION ALL当or覆盖不同索引列的时候，可能会走全表扫描
select * from t where a = 1 or b = 2;可改写为
select * from t where a = 1 union all select * from t where b = 2;in/exists/子查询以驱动表行数最小为原则原则是小表驱动大表，减少被驱动表的扫描。
exists非常适合外表小，内表有索引快速判定存在性的场景。子查询如果会重复执行，通常改为jion或先物化结果更好。distinct/group by的开销distinct的本质是去重，通常会使用排序，而排序节点往往会借用临时表空间，这意味着要和磁盘产生读写，会降低效率。所以冗余处理和业务上的唯一定义就显得非常重要了，只要业务上能保证唯一，SQL里就不要写distinct。
类似的是
group by这种聚合节点，尽量与索引一致，减少额外排序。jion的写法这也是常见的SQL语句问题，强过滤条件应当写在jion生效之前的位置，就是先进行选择再进行连接投影。大白话就是优先缩小数据集再关联查询。
过多的表jion写法会在SQL分析阶段就产生巨大的开销，整个优化器也不一定会选出较好的执行路径。可以先进到一个事务中，然后对原SQL进行拆解，对连接表和连接条件先进行临时表物化，然后对这些小结果集的临时表再进行查询处理。必要时，这些临时表的
engin可以不选择默认的innodb,而是更激进的memory。
3. 重新设计
这一部分说明了一个具有前瞻性的数据库架构师是多么重要。往往系统性的熵增速度最快的时候就是在设计阶段。随着业务不断增加和复杂化，现代架构当然有外部处理办法，比如微服务，容器化等等手段用来抵御新入熵增带来的复杂性增加。在前期设计完善的情况下，我其实并不推荐这种办法来结构新业务的复杂度。
反范式与冗余
读多写少的情况下，查询中往往是要有很多的jion table存在，我们可以适当放弃高层范式，获得某些字段的冗余，以换取查询中jion table上的计算量减少。比如用户名，部门名等冗余在物化视图中。
冗余带来的是一致性问题，这部分可以通过中间件，触发器，约束，异步校正任务等进行规避。总的来说，反范式适合在可控范围内进行改造，权衡读服务和复杂度后，取一个工程平衡点进行改造。
水平拆分
这部分主要依赖分表和分区。
比如冷热数据分离，按照时间 周/月/年将数据分表。按照月份将订单表进行分区处理等等。
垂直拆分
将某些text/blob大字段进行垂直拆分，再用主键关联。这样做的好处是主表变窄，索引效率更高，缓冲命中更好，详情页再查扩展表。
4. 添加索引
在添加索引前需要提醒的是索引滥用，索引并不是万能的，当你在一个表字段上增加索引的时候，意味着数据操作的同时要进行索引维护，对于大表索引尤其突出，频繁的数据操作意味着索引碎片和btree变形导致的索引失效。所以在日常维护中，增加索引应当采取慎重原则。
索引创建的三条基本原则
匹配最常见的过滤条件，where子句的相关字段。
匹配最常见的排序分组，尽量避免
filesort。复合索引优先，把经常出现在一起的条件放在同一个索引里。
复合索引和最左原则
对于符合索引 KEY idx(a,b,c),MYSQL中可以高效使用这个索引的前提是你的查询条件符合从左到右的连续前缀。比如可用的前缀包括a,ab,abc。不理想的情况是只用b,bc,ac这几类。
查询中同时有等值和范围两类条件的时候，优先等值然后才是范围。比如：
where user_id = 123
and status = 'PAID'
and create_time >= '2025-12-01'推荐的索引应当是(user_id,status,create_time),而不是其他选择。
同样的等值为什么要将user_id放到前面，status放到次之的位置呢？这就涉及到选择性selective。在不破坏最左原则的前提下，选择性高的等值字段尽量靠前。status字段是地选择性，就是几个状态值，而user_id字段明显具备更高的选择性。需要注意的是，选择性在oltp场景下往往不是决定因素，这点要注意。
除了最左原则，还需要注意索引覆盖，即需要查询的字段最好有复合索引，索引上直接就可以返回内容，而不需要回表进行表内容查询。
MYSQL优化器
MYSQL的优化器本质上是一个基于代价（Cost-Based）的计划选择器，给定一条SQL，它会生产若干的执行计划，包括访问路径，连接顺序，连接算法，是否临时表/排序等，优化器会利用统计信息对每个计划进行代价估算，选择代价最小的发送给执行器。
一条SQL大致会有如下经历，Paser->Preprocess->Optimizer->Executor。
优化器的估算数据关键来自统计信息，包括表行数，索引基数，列分布信息，持久化统计等。这些信息会直接导致EXPLAIN结果的准确度，有些很久没有维护的统计信息，explain出来的rows会非常离谱。通常的修复操作是刷新统计信息：
analyze table t;优化器核心决策
优化器的核心决策有三类：
访问路径
Access Path,就是怎么读每张表。对每张表，优化器会考虑很多种读取方法，从更优到更差虽然不绝对，但是可以参考：const /system，常量表，一行命中
eq_ref/ref，用索引等值查询
range,索引范围扫描 （
BETWEEN, > , LIKE 'x%'）index,全索引扫描，这是非常容易误解的一项，当explain出现index的时候并不代表索引命中，索引命中是
ref,当索引长期没有维护的时候，它会变得冗长而低效，有些时候甚至比全表扫描更低效。ALL，全表扫描。
访问路径还要考虑是否回表，就 是二级索引命中后，要回主键取其他字段。覆盖索引本身就包含所需的列值，不需要回表。
连接顺序
多个表的
jion后的性能往往和连接顺序强相关。优化器的作用是尽量先把结果集过滤小，方便小集合驱动大集合。伪代码如下for each tuple r in tbl1 do for each tuple s in tbl2 do If r and s satisfy the jion condition Then output the tuple <r,s>tbl1就是外部表，tbl2是内部表，需要用更小的外部表去驱动内部表。减少嵌套，加速连接操作。
连接算法与额外操作
Nested Loop Join，嵌套循环，前表每行去后表进行索引探测，要求后表开销更小。
Hash Join，MYSQL8.0支持，对某些无合适索引的等值连接更具优势，受类型限制较多。
ORDER BY，有可能触发filesort。
GROUP BY / DISTINCT，有可能触发
Using temporary（临时表空间）。子查询 / 派生表 /CTE，物化
materialize还是合并merge。EXPLAIN中出现
using temporary / using filesort往往就是性能警讯。
优化器提示
写在前面
hint会将短期问题钉死在问题中，长期会引入维护与升级风险。绕过优化器本质上是鼓励坏习惯，遇到慢SQL首先会用hint，SQL改写升级后，hint成了反优化的因素，在大型复杂的项目中禁用这类特性往往是必要的。另外面对不同数量级的表，hint往往变得事与愿违，
Oracle-Style optimizer在postgresql这类数据库看来是不值得做的。了解完这个特性的弊端后，我们开始这部分的介绍。
优化器提示就是在SQL中给优化器下命令，让其在执行计划中倾向或强制使用某种策略，索引/连接顺序/连接算法/子查询策略/是否物化派生表/是否某类优化等。
当统计信息陈旧，数据倾斜，复杂jion使得优化器经常选错访问路径的时候，hint可以纠偏。这明显不是工程意义上的正确做法，工程学上优先级依旧是正确索引/可索引化写法/最新的统计信息
Hint的语法体系
Optimizer Hints
写在
select / insert / update / delete关键字后面select /** HINT1 HINT2(...)**/ ...可组合，可作用于具体表查询。
Index Hints
写在表名后面，只管索引选择
from t use index (idx1) # 建议，优化器仍可不选 from t force index (idx2) # 强烈倾向，通常会选，除非不可用 from t ignore index (idx3) # 排除某些索引这类写法仅能影响索引使用。
Hint用法
索引相关
强制索引
select id,create_time
from orders FORCE INDEX (idx_user_status_time)
where user_id = 123
and status = 'PAID'
order by create_time desc
limit 20;更精细的指定索引
select /** INDEX (o idx_user_status_time) */
o.id, o.create_time
from orders o
where ...连接顺序相关
STRAIGHT_JION
直接按照from / jion的字面顺序连接
select straight_jion b.id,b.name
from small s
jion big b
on b.id = s.id
where s.flag = 1
# 也可以hint形式
select /** JOIN_FIXED_ORDER() */ b.id,b.name
from small s
jion big b
on b.id = s.id
where s.flag = 1很明显这样的场景是强制小表驱动大表。
另外，还有更精细的写法：
JION_ORDER
jion_order(t,1t2,...)，指定表的相对顺序，但是未在列的表可能被插入到中间。select /** JOIN_ORDER(u,o,p) */ o.id,o.create_time from user u join orders o on o.user_id = u.id join payments p on p.order_id = o.id and p.state = 'SUCCESS' left join coupon_usage cu on cu.order_id = o.id where u.city_id = 1 and u.status = 'ACTIVE' and o.create_time >= '2025-12-01';优化器保证表按照u,o,p的顺序连接，但是对于表cu可能被放到任意位置。
JION_PREFIX
jion_prefix(t1,t2,...)，指定的表必须按照顺序排在计划前，其他表在后面。select /** JION_PREFIX(u) */ o.id,o.create_time from user u join orders o on o.user_id = u.id join payments p on p.order_id = o.id and p.state = 'SUCCESS' left join coupon_usage cu on cu.order_id = o.id where u.city_id = 1 and u.status = 'ACTIVE' and o.create_time >= '2025-12-01';u表必须被安排到执行计划最前，其余表由优化器决定。
JION_SUFFIX
和
jion_prefix正好相反，指定哪些表必须放在最后select /** JION_SUFFIX(cu) */ o.id,o.create_time from user u join orders o on o.user_id = u.id join payments p on p.order_id = o.id and p.state = 'SUCCESS' left join coupon_usage cu on cu.order_id = o.id where u.city_id = 1 and u.status = 'ACTIVE' and o.create_time >= '2025-12-01';cu这张表必须放在最后。
派生表策略
这类hint主要用于子查询是否转半连接semijoin,派生表derived table是合并入外层merge还是物化成临时结果materialize，最后是条件是否下推到派生表。
# 子查询半连接策略
SELECT /** SEMIJION(@subq) */ ...
where exists (select /** QB_NAME(subq) */ 1 from ...);
# 指定或禁用某些策略
# 禁用
select /** NO_SEMIJOIN(@subq1 FIRSTMATCH,LOOSESCAN) */ *
from t2
where t2.a in (
select /** QB_NAME(subq1) */ a
from t3
);
# 只允许
select /** SEMIJOIN(@subq1 MATERIALZATION,DUPSWEEDOUT) */ *
from t2
where t2.a in (
select /** QB_NAME(subq1) */ a
from t3
);
# 强制子查询物化
select id,
a in (select /** SUBQUERY(MATERIALIZATION) */ a from t1)
from t2;
# 强制in转exists
select *
from t2
where t2.a in (select /** SUBQUERY(INTOEXISTS) */ a from t1)
# 多个子查询可以分别标记，然后采用不同的派生表策略
select /**
NOSEMIJOIN(@subq_a FIRSTMATCH,LOOSESCAN)
SEMIJOIN(@subq_b MATERIALIZATION)
*/
from t2
where t2.a in (
select /** QB_NAME(subq_a) */ a
from t3
)
and exists (
select /** QB_NAME(sub_b) */ 1
from t4
where t4.k = t2.k
);执行资源保护
某些接口SQL退化，宁可超时失败也不能把连接池打满，这个时候就可以通过hint来限制执行时间
select /** MAX_EXECUTION_TIME(200) */ ...需要注意的是，这种做法只是止血，根治仍然是正确的索引与统计信息。
EXPLAIN结果
id
数值越大通常越内层，子查询/派生表等等。同一id表示同一个查询块中的多表jion。
select_type
这一行属于哪一类SELECT。常见值如下:
SIMPLE，顾名思义无子查询/UNION的简单的普通的查询。
PRIMARY,最外层查询。
SUBQUERY,子查询。
DERIVED,派生表。
UNION /UNION RESULT,UNION 相关。
table
当前访问的表名称，若是<derivedN>说明访问的是第N个派生表结果。
type
核心指标，访问方式，从好到坏排列如下:
const/system常量级，最好eq_ref主键/唯一索引等值jionref非唯一索引等值匹配range索引范围扫描index全索引扫描ALL全表扫描
possible_keys/key
可能用到的索引和实际用到的索引。若possible_key有，但key为空，说明优化器最终没有走索引。
key_len
用到索引的长度。不是简单的列的个数，而是字节长度。符合索引仅用到前面部分的时候，key_len往往偏小，依次可以观察最左原则的落地。
ref
用什么与索引比较。const与常量比较，db.tb.col则表示与前面的列比较，用于判断jion关联条件是否走对索引列。
rows
核心指标，读取行数的预估。虽然是估算，但是依旧是定位瓶颈的第一依据。一般情况都要先顶住大表的rows，如果太大说明要么统计信息陈旧，要么就是扫描太多。
filtered
预计过滤后保留的百分比，通常和rows一起查看。比如filtered=10 表示预计只留下10%。
Extra
重点关注以下关键词
Using index覆盖索引，通常是好事Using where有条件过滤，常见Using temporary用了临时表 ，常见的排序聚合操作容易产生，要特别注意。group by / distinct / union等操作容易触发内部临时表空间，用来存放中间结果。这个临时表空间有可能在内存中，也有可能在磁盘上，内存临时表受到tmp_table_size和max_heap_table_size两个参数的限制。Using filesort需要额外排序，order by 不能使用索引顺序的情况下会发生。以下三条都会触发filesortorder by 的列/顺序与可用的索引顺序不一致
group by 的实现需要排序辅助
排序列太多
同样的，内存放不下的话，会
spill到磁盘，放大额外操作的开销。Using join bufferjion不能走索引，只能走join buffer,大坑。Using index conditionICP减少回表，好事。