Tavio's blog
Tavio
2022-09-20
目录

MySQL查询优化

# 深度解析MySQL查询优化器：SQL执行计划的生成逻辑

在MySQL的整个SQL执行链路中，查询优化器（Query Optimizer）是当之无愧的“大脑”——它负责将用户编写的SQL语句（尤其是复杂的查询语句）转化为高效的执行方案，这个方案就是执行计划（Execution Plan）。同样一条SQL，不同的执行方式可能带来天差地别的性能表现：比如简单的“查询用户订单”，可能走全表扫描耗时10秒，也可能走索引扫描耗时10毫秒，而这背后的决策全靠查询优化器。

# 一、先明确：查询优化器在SQL执行流程中的定位

在深入优化器之前，我们需要先梳理SQL从输入到结果返回的完整流程，明确查询优化器所处的核心环节。一条SQL的执行大致分为6个阶段：

  1. 词法分析（Lexical Analysis）：将SQL语句拆分为一个个“词法单元”（如关键字SELECT、表名user、字段名id、运算符=等），识别每个单元的类型。例如，将“SELECT id, name FROM user WHERE age > 20”拆分为“SELECT”（关键字）、“id”（字段）、“name”（字段）、“FROM”（关键字）、“user”（表名）等。
  2. 语法分析（Syntactic Analysis）：根据词法单元构建抽象语法树（AST），验证SQL语法是否合法。例如，检查是否遗漏关键字FROM、是否有未闭合的括号等。若语法错误，直接返回错误信息（如“You have an error in your SQL syntax”）。
  3. 预处理（Preprocessing）：对AST进行语义校验和补充，生成逻辑查询树（Logical Query Tree）。核心操作包括：验证表/字段是否存在、验证用户权限、处理视图（将视图替换为对应的基表查询）、简化常量表达式（如将“1+2”直接计算为“3”）等。
  4. 查询优化（Query Optimization）：查询优化器的核心工作阶段。接收预处理后的逻辑查询树，通过一系列优化规则（逻辑优化+物理优化）生成多个可能的执行方案，再通过成本估算模型选择成本最低的方案，最终生成物理执行计划。
  5. 执行计划执行（Execution）：执行器（Executor）按照优化器生成的物理执行计划，调用存储引擎（如InnoDB）的接口执行查询，获取数据。
  6. 结果返回（Result Return）：将执行结果整理后返回给用户（若为SELECT查询），或返回执行状态（如INSERT/UPDATE的影响行数）。

可以看出，查询优化器是连接“SQL语义”与“高效执行”的桥梁——它不关心SQL的语法是否正确，只专注于“如何以最低成本执行查询”。其输出的“执行计划”，就是执行器的“操作手册”。

# 二、查询优化器的核心工作：从逻辑查询树到物理执行计划

查询优化的过程，本质是“将逻辑查询树转化为最优物理执行计划”的过程，分为两大核心阶段：逻辑优化（不涉及具体执行方式，仅优化查询的逻辑结构）和物理优化（结合存储引擎特性，选择具体的执行算法和索引）。两个阶段层层递进，最终生成可执行的物理执行计划。

# 2.1 第一阶段：逻辑优化——重构查询逻辑，降低数据量

逻辑优化的目标是“在不改变查询结果的前提下，通过重构逻辑查询树，减少后续执行过程中需要处理的数据量”。核心优化手段包括以下6类，每类都有明确的应用场景：

# 2.1.1 谓词下推（Predicate Pushdown）

将过滤条件（WHERE子句中的条件）尽可能“下推”到最接近数据源的地方（如基表扫描、索引查询阶段），提前过滤掉无用数据，减少后续连接或聚合的数据量。这是最常用、效果最显著的逻辑优化手段。

示例：查询“用户表与订单表连接后，筛选年龄>20的用户订单”。原始逻辑可能是“先连接两表，再过滤年龄>20”；优化后逻辑是“先过滤用户表中年龄>20的用户，再与订单表连接”——提前过滤掉大量不符合条件的用户，减少连接的数据量。

对应SQL优化前后对比：

-- 优化前（逻辑上的原始查询，MySQL会自动优化）
SELECT u.id, o.order_no 
FROM user u 
JOIN order o ON u.id = o.user_id 
WHERE u.age > 20;

-- 优化后（MySQL实际执行的逻辑）
SELECT u.id, o.order_no 
FROM (SELECT id FROM user WHERE age > 20) u  -- 先过滤，再连接
JOIN order o ON u.id = o.user_id;
1
2
3
4
5
6
7
8
9
10

# 2.1.2 连接重排（Join Reordering）

当查询涉及多表连接时，连接顺序对执行成本影响极大。MySQL的核心原则是“小表驱动大表”——用数据量小的表作为驱动表（外层循环），数据量大的表作为被驱动表（内层循环），减少内层循环的执行次数。

示例：用户表（1000行）、订单表（100万行）、商品表（1万行）三表连接。优化器会自动选择“用户表（小表）→商品表（中表）→订单表（大表）”的连接顺序，而非随机顺序，大幅减少连接过程中的中间结果集大小。

补充：MySQL对多表连接的优化能力有限，当连接表数量超过6张时，优化器可能无法遍历所有可能的连接顺序，此时可能需要手动调整表的顺序（但更推荐通过索引优化减少连接成本）。

# 2.1.3 常量折叠与常量传播（Constant Folding & Propagation）

常量折叠：将查询中的常量表达式直接计算出结果，避免执行时重复计算。例如，将“WHERE age > 18+2”优化为“WHERE age > 20”，将“SELECT id10 FROM user”中的“id10”保留（需执行时计算，但常量部分已折叠）。

常量传播：若查询中存在“列=常量”的条件，将该常量传播到其他依赖该列的条件中，进一步简化查询。例如，“SELECT * FROM user WHERE id=10 AND age > (SELECT min_age FROM config WHERE user_id=id)”，优化后将“id=10”传播到子查询，得到“user_id=10”，子查询可直接定位结果。

# 2.1.4 消除冗余条件（Redundant Condition Elimination）

删除查询中重复或无效的过滤条件，简化逻辑。例如：

  • 删除重复条件：“WHERE age > 20 AND age > 20”优化为“WHERE age > 20”。
  • 删除永真/永假条件：“WHERE age > 20 AND 1=1”优化为“WHERE age > 20”；“WHERE age > 20 AND 1=0”优化为“WHERE 1=0”（直接返回空结果，无需执行后续查询）。

# 2.1.5 子查询优化（Subquery Optimization）

MySQL对非关联子查询（子查询不依赖外层表字段）和关联子查询的优化策略不同：

  • 非关联子查询：将子查询结果缓存（如“SELECT * FROM user WHERE age > (SELECT avg(age) FROM user)”），避免重复执行子查询。
  • 关联子查询：将其转化为JOIN查询（即“子查询扁平化”），因为JOIN的执行效率通常高于关联子查询的循环执行。例如，“SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id)”优化为“SELECT u.* FROM user u JOIN order o ON u.id = o.user_id GROUP BY u.id”。

# 2.1.6 聚合函数优化（Aggregation Optimization）

对GROUP BY、DISTINCT等聚合操作进行优化，减少聚合的数据量：

  • DISTINCT优化：若DISTINCT与聚合函数结合（如“SELECT DISTINCT count(id) FROM user”），优化器会先聚合再去重（或反之），选择成本更低的方式；若DISTINCT查询可通过索引覆盖（即索引包含所有需要的字段），则直接通过索引去重，无需扫描全表。
  • GROUP BY优化：若GROUP BY的字段存在索引，优化器会利用索引的有序性，避免额外的排序操作（即“Using index for group-by”，可通过EXPLAIN查看）。

# 2.2 第二阶段：物理优化——选择最优执行方式与索引

逻辑优化完成后，得到的是“优化后的逻辑查询树”（明确了查询的逻辑步骤，如“先过滤用户表→再与订单表连接→最后聚合”），但未明确“每个步骤具体如何执行”（如过滤用户表时用全表扫描还是索引扫描？连接时用嵌套循环还是哈希连接？）。物理优化的核心就是“为逻辑查询树的每个节点选择具体的执行算法和索引”，生成多个物理执行计划，再通过成本估算选择最优方案。

# 2.2.1 核心任务1：索引选择（最关键的物理优化）

索引选择是物理优化的核心——优化器需要判断“是否使用索引”“使用哪个索引”，甚至“是否使用索引联合”。其决策依据是“索引能减少的数据扫描量”和“索引的维护成本”（如索引查找的IO成本、回表的成本等）。

优化器的索引选择逻辑：

  1. 收集候选索引：针对查询的过滤条件（WHERE）、连接条件（JOIN ON）、排序字段（ORDER BY）、聚合字段（GROUP BY），收集所有可能适用的索引（如主键索引、二级索引、联合索引等）。
  2. 评估索引有效性：排除无法过滤大量数据的索引（如区分度极低的索引，如“性别”字段的索引，过滤后仍有大量数据，使用索引的成本可能高于全表扫描）。
  3. 计算索引使用成本：包括“索引查找成本”（从索引树中找到目标数据的IO成本）和“回表成本”（若索引为非覆盖索引，需要通过索引中的主键值查找全表数据的IO成本）。若为覆盖索引（索引包含所有查询需要的字段），则无回表成本，优先级最高。
  4. 选择成本最低的索引：若存在多个有效索引，选择成本最低的；若所有索引的成本都高于全表扫描，则选择全表扫描。

示例：查询“SELECT id, name FROM user WHERE age > 20 AND gender = 'male'”，用户表有两个索引：idx_age（age）、idx_age_gender（age, gender）。优化器会评估：idx_age_gender是联合索引，能同时匹配age和gender条件，过滤效果更好，且若索引包含id、name字段（覆盖索引），则无回表成本，因此会优先选择idx_age_gender，而非idx_age或全表扫描。

# 2.2.2 核心任务2：执行算法选择（连接、排序、聚合的算法）

针对逻辑查询树中的“连接”“排序”“聚合”等操作，优化器会选择具体的执行算法。不同算法适用于不同的数据量和数据分布，优化器根据统计信息选择最优算法。

# （1）连接算法选择（三种核心算法）

MySQL支持三种连接算法，优化器根据连接表的大小和索引情况选择：

  • 嵌套循环连接（Nested-Loop Join, NLJ）： 原理：外层循环遍历驱动表的每一行，内层循环根据连接条件在被驱动表中查找匹配的行（若被驱动表有索引，内层循环为索引查找；无索引则为全表扫描）。 适用场景：驱动表数据量小（1万行以内），被驱动表有有效索引。 优点：内存占用小，无需缓存大量数据；若被驱动表有索引，效率极高。 缺点：若被驱动表无索引，内层循环为全表扫描，效率极低（即“笛卡尔积”的低效场景）。
  • 基于索引的嵌套循环连接（Index Nested-Loop Join, INLJ）： 原理：NLJ的优化版本——被驱动表的连接字段存在索引，内层循环直接通过索引查找匹配行，无需全表扫描。 适用场景：绝大多数有索引的两表连接场景（MySQL默认优先选择）。
  • 哈希连接（Hash Join）： 原理：将驱动表的数据加载到内存，构建哈希表（以连接字段为key，数据行为value）；再遍历被驱动表的每一行，通过连接字段在哈希表中查找匹配的行。 适用场景：驱动表和被驱动表数据量都较大（10万行以上），且连接字段无索引（或索引区分度低）。 优点：比NLJ（无索引时）效率高得多，避免了内层循环的全表扫描。 缺点：内存占用大，若驱动表数据量超过内存，会使用磁盘哈希表，效率下降。 补充：MySQL 8.0才正式支持哈希连接，5.7及以下版本无此算法，只能用NLJ（无索引时效率极低）。
  • 合并连接（Merge Join）： 原理：先将两个表的连接字段排序，再通过“双指针”遍历两个有序表，匹配连接字段相等的行。 适用场景：两个表的连接字段都已排序（如通过索引有序性），无需额外排序。 优点：排序后匹配效率高，适用于大表连接。 缺点：若表未排序，需要先执行排序操作，成本较高，因此优化器较少选择。
# （2）排序算法选择（文件排序vs索引排序）

针对ORDER BY、GROUP BY等需要排序的操作，优化器有两种选择：

  • 索引排序（Using index for order by）：若排序字段存在索引（且索引有序），直接利用索引的有序性获取数据，无需额外排序。效率极高，是优化器的首选。
  • 文件排序（File Sort）：若排序字段无索引，需要将查询结果加载到内存（或磁盘）中，通过排序算法（如快速排序、归并排序）完成排序。分为两种模式：
    • 内存排序：若排序数据量小于参数sort_buffer_size（默认256KB），直接在内存中完成排序。
    • 磁盘排序：若数据量超过sort_buffer_size，会将数据分成多个块，先对每个块进行内存排序，再将排序后的块写入磁盘，最后对所有块进行归并排序（即“外部排序”）。效率较低，应尽量避免。
# （3）聚合算法选择（松散索引扫描vs紧凑索引扫描）

针对GROUP BY的聚合操作，若聚合字段存在索引，优化器会选择两种高效的扫描方式：

  • 松散索引扫描（Loose Index Scan）：适用于GROUP BY字段是索引前缀（如联合索引idx_age_gender，GROUP BY age），且无其他过滤条件。优化器会直接扫描索引中不同的age值，无需扫描所有行，效率极高。
  • 紧凑索引扫描（Tight Index Scan）：适用于GROUP BY字段是索引前缀，但存在过滤条件（如“WHERE gender='male' GROUP BY age”）。优化器会扫描索引中符合过滤条件的所有行，再进行聚合，效率低于松散扫描，但高于全表扫描。

# 三、关键支撑：执行计划的成本估算模型

物理优化阶段会生成多个候选的物理执行计划（如“用idx_age索引扫描用户表+嵌套循环连接订单表”“用全表扫描用户表+哈希连接订单表”等），优化器如何判断哪个计划最优？答案是成本估算模型——MySQL为每个执行计划计算一个“成本值”，选择成本值最低的计划作为最终执行计划。

# 3.1 成本的核心构成：IO成本 + CPU成本

MySQL的成本估算基于“资源消耗”，核心分为两类成本，单位为“成本因子”（1个成本因子约等于1个磁盘页的IO操作成本）：

  • IO成本：从磁盘读取数据页的成本（是成本的主要构成部分，因为磁盘IO速度远慢于内存操作）。MySQL默认1个数据页的IO成本为1.0（可通过参数innodb_page_size调整页大小，默认16KB）。
  • CPU成本：在内存中处理数据的成本（如过滤数据、排序、聚合、连接匹配等）。MySQL默认1行数据的CPU处理成本为0.2（可通过参数cpu_cost_per_table、cpu_cost_per_record微调）。

总执行成本 = IO成本 + CPU成本

# 3.2 成本估算的依据：统计信息（Statistics）

优化器无法直接获取表的真实数据量和数据分布，只能依赖“统计信息”进行估算。统计信息是MySQL通过采样方式收集的表和索引的元数据，存储在系统表mysql.stats中（MySQL 8.0）或information_schema.STATISTICS中。

核心统计信息包括：

  • table_rows：表的估算行数（非精确值，通过采样计算）。
  • data_length：表的数据量大小（单位字节），用于计算全表扫描的IO成本（全表扫描IO成本 = data_length / innodb_page_size * 1.0）。
  • index_length：索引的大小（单位字节），用于计算索引扫描的IO成本。
  • cardinality：索引的基数（即索引中不同值的数量），用于计算索引的区分度（区分度 = cardinality / table_rows，区分度越高，索引过滤效果越好）。
  • avg_row_length：表的平均行大小，用于估算查询结果集的大小。

关键注意点：统计信息是“估算值”，若统计信息过时（如表数据大量插入/删除后未更新统计信息），会导致优化器的成本估算偏差，进而选择错误的执行计划（如明明有高效索引却选择全表扫描）。此时需要手动更新统计信息：

-- 更新指定表的统计信息（MySQL 8.0默认自动更新，5.7及以下可能需要手动执行）
ANALYZE TABLE user;
1
2

# 3.3 成本估算示例：全表扫描vs索引扫描

假设用户表（user）有以下信息：table_rows=10000，data_length=160KB（即10个数据页，innodb_page_size=16KB），avg_row_length=16字节；有二级索引idx_age（age），index_length=32KB（2个数据页），cardinality=100（age的区分度为1%）；查询条件“WHERE age=25”，估算匹配行数=10000*1%=100行。

# （1）全表扫描的成本估算

  • IO成本：全表扫描需要读取10个数据页 → 10 * 1.0 = 10.0。
  • CPU成本：扫描10000行数据，过滤出100行 → 10000 * 0.2 = 2000.0。
  • 总成本 = 10.0 + 2000.0 = 2010.0。

# （2）索引扫描（idx_age）的成本估算

  • IO成本：索引扫描需要读取2个索引页（找到age=25的索引项） + 回表读取100行数据（假设100行分布在10个数据页） → 21.0 + 101.0 = 12.0。
  • CPU成本：索引查找100个索引项 + 回表后过滤100行 → (100 + 100) * 0.2 = 40.0。
  • 总成本 = 12.0 + 40.0 = 52.0。

# （3）优化器的选择

索引扫描的总成本（52.0）远低于全表扫描（2010.0），因此优化器会选择idx_age索引扫描。若查询是“SELECT age FROM user WHERE age=25”（覆盖查询，无需回表），则索引扫描的IO成本仅为2.0，总成本更低，优先级更高。

# 四、如何查看与解读执行计划？（实践核心）

理解了查询优化器的工作逻辑后，最核心的实践就是“通过执行计划判断优化器的决策是否合理”，进而优化SQL和索引。MySQL提供EXPLAIN命令（或EXPLAIN ANALYZE，MySQL 8.0+，会执行SQL并返回真实执行计划）查看执行计划。

# 4.1 执行计划的核心字段解读

执行EXPLAIN SELECT ...后，会返回10个核心字段，每个字段都对应执行计划的关键信息：

# （1）id：查询的执行顺序标识

  • id相同：执行顺序由上到下（如多表连接，先执行驱动表，再执行被驱动表）。
  • id不同：id值越大，执行优先级越高（先执行子查询，再执行外层查询）。
  • 示例：EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE order_no='20240501')，子查询的id大于外层查询，先执行子查询获取user_id，再执行外层查询。

# （2）select_type：查询类型

标识查询是简单查询还是复杂查询（子查询、联合查询等），核心类型：

  • SIMPLE：简单查询（无子查询、无UNION）。
  • SUBQUERY：非关联子查询（子查询不依赖外层表字段）。
  • DERIVED：派生表查询（子查询作为临时表，如“SELECT * FROM (SELECT id FROM user) t”）。
  • UNION：UNION查询的第二个及以后的查询。
  • UNION RESULT：UNION查询的结果集合并阶段。

# （3）table：当前行对应的表名（或临时表标识）

  • 直接显示表名（如user、order）：表示操作的是基表。
  • 显示<derivedN>：表示操作的是id=N的派生表（临时表）。
  • 显示<unionM,N>：表示操作的是id=M和id=N的UNION结果集。

# （4）type：访问类型（最关键的性能指标）

表示优化器访问表的方式，从优到差的顺序为：

system > const > eq_ref > ref > range > index > ALL
1

核心类型解读（日常优化需关注“是否达到range及以上”）：

  • system：表中只有1行数据（如系统表），最优。
  • const：通过主键或唯一索引查找单一行数据（如“WHERE id=10”），效率极高。
  • eq_ref：多表连接时，被驱动表通过主键或唯一索引匹配（每行驱动表数据对应被驱动表1行数据），如“user.id = order.user_id”（order.user_id是主键）。
  • ref：通过非唯一索引查找数据（如“WHERE age=25”，age是二级索引），可能匹配多行。
  • range：通过索引范围扫描数据（如“WHERE age BETWEEN 20 AND 30”“WHERE id > 100”），效率较好。
  • index：全索引扫描（扫描整个索引树，无需扫描数据页），适用于覆盖查询（如“SELECT age FROM user”，age是索引字段）。
  • ALL：全表扫描（扫描整个数据文件），效率最差，需尽量避免。

# （5）possible_keys：候选索引列表

优化器认为可能适用的索引（但不一定会使用）。若该字段为NULL，表示无候选索引，可能需要优化查询或添加索引。

# （6）key：实际使用的索引

优化器最终选择的索引（核心关注字段）。若该字段为NULL，表示未使用任何索引（全表扫描）。

注意：possible_keys有值但key为NULL，可能是“索引的成本高于全表扫描”（如区分度极低的索引），或“查询需要扫描全表数据，使用索引无意义”（如“SELECT * FROM user”）。

# （7）key_len：使用的索引长度（单位字节）

表示优化器使用的索引字段的长度，可用于判断“联合索引是否被充分利用”。例如，联合索引idx_age_gender（age int(4)，gender varchar(10)），若key_len=4，表示仅使用了age字段；若key_len=4+10*3+2=36（varchar按utf8编码，1个字符占3字节，加2字节长度标识），表示使用了age和gender两个字段。

# （8）ref：与索引匹配的列或常量

表示索引的匹配条件，如“const”（匹配常量，如“WHERE id=10”）、“user.age”（匹配其他表的字段，如连接条件）。

# （9）rows：优化器估算的扫描行数

表示优化器认为当前操作需要扫描的行数（基于统计信息估算）。行数越少，执行效率越高。若rows远大于表的真实行数，可能是统计信息过时，需执行ANALYZE TABLE更新。

# （10）Extra：额外执行信息（核心优化依据）

包含优化器的额外执行细节，常见关键值：

  • Using index：使用覆盖索引（无需回表），最优情况，需尽量争取。
  • Using where：使用WHERE子句过滤数据（但未使用索引，或使用索引后仍需过滤）。
  • Using index condition：使用索引条件推送（ICP），将部分过滤条件下推到存储引擎，减少回表数据量（如“WHERE age>20 AND name LIKE '张%'”，idx_age_name索引，先通过age>20过滤索引项，再回表匹配name）。
  • Using filesort：使用文件排序（未利用索引排序），效率低，需优化（如添加排序字段的索引）。
  • Using temporary：使用临时表（如DISTINCT、GROUP BY未使用索引时），效率低，需优化。
  • Using join buffer：使用连接缓冲区（连接时数据量超过buffer大小），需优化（如添加连接字段的索引，或增大join_buffer_size）。

# 4.2 执行计划解读实例

查询：EXPLAIN SELECT id, name FROM user WHERE age BETWEEN 20 AND 30 ORDER BY age;

执行计划输出（简化）：

id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra
1  | SIMPLE      | user  | range | idx_age       | idx_age   | 4       | NULL | 100  | Using index condition; Using index
1
2

解读：

  • type=range：使用索引范围扫描（age BETWEEN 20 AND 30），效率较好。
  • key=idx_age：实际使用idx_age索引。
  • Extra=Using index condition; Using index：使用索引条件推送，且是覆盖索引（idx_age包含age、id字段，name字段若也在索引中则为覆盖，此处假设idx_age是联合索引idx_age_id_name），无需回表，执行效率极高。
  • rows=100：估算扫描100行数据，符合预期。

# 五、影响优化器决策的关键因素与优化建议

查询优化器的决策并非绝对正确，受多种因素影响。了解这些因素，能帮助我们规避“优化器陷阱”，写出更易被优化器识别的高效SQL。

# 5.1 影响优化器决策的核心因素

  • 统计信息不准确：如前所述，统计信息是成本估算的基础，若统计信息过时（表数据大量变更后未更新），会导致优化器选择错误的执行计划。
  • 索引设计不合理：如缺少核心过滤/连接/排序字段的索引、联合索引字段顺序错误（应将区分度高的字段放在前面）、索引过多导致优化器选择困难等。
  • SQL语句写法问题：如使用函数操作索引字段（“WHERE DATE(create_time) = '2024-05-01'”，无法使用create_time索引）、使用隐式类型转换（“WHERE id='10'”，id是int类型，无法使用索引）、子查询写法复杂导致优化器无法扁平化等。
  • 参数配置不当：如sort_buffer_size过小导致频繁文件排序、join_buffer_size过小导致频繁使用连接缓冲区、innodb_stats_on_metadata=OFF导致统计信息无法自动更新等。
  • 数据分布不均：如某些字段存在“热点值”（如性别字段中“male”占90%，“female”占10%），优化器的估算偏差会增大，可能选择低效索引。

# 5.2 实践优化建议

  • 定期更新统计信息：对于数据变更频繁的表，定期执行ANALYZE TABLE更新统计信息，确保优化器的成本估算准确。
  • 合理设计索引：
    • 遵循“三星索引”原则：索引包含过滤条件的字段（第一星）、包含连接字段（第二星）、包含查询需要的所有字段（第三星，覆盖索引）。
    • 联合索引字段顺序：区分度高的字段在前、过滤/连接字段在前、排序/聚合字段在后。
    • 避免冗余索引（如已有联合索引idx_age_gender，无需再单独创建idx_age）。
  • 优化SQL语句写法：
    • 避免在索引字段上使用函数/运算符（如“WHERE id+1=11”改为“WHERE id=10”）。
    • 避免隐式类型转换（如“WHERE id='10'”改为“WHERE id=10”）。
    • 复杂子查询改为JOIN查询（如EXISTS子查询改为JOIN）。
    • Limit分页优化：对于大表分页（如“LIMIT 100000, 20”），使用索引定位起始位置（如“WHERE id > 100000 LIMIT 20”），避免全表扫描。
  • 合理配置参数：
    • sort_buffer_size：根据业务调整，避免过小导致文件排序（建议256KB~1MB）。
    • join_buffer_size：多表连接场景调整，避免过小导致使用连接缓冲区（建议256KB~1MB）。
    • innodb_stats_on_metadata=ON（MySQL 5.7默认OFF，8.0默认ON）：确保查询元数据时自动更新统计信息。
  • 强制索引（谨慎使用）：若优化器选择错误的索引，可通过FORCE INDEX强制使用指定索引（如“SELECT * FROM user FORCE INDEX (idx_age) WHERE age>20”），但需注意：后续索引变更可能导致该语句失效，尽量优先通过优化索引和SQL让优化器自动选择正确索引。

# 六、总结

MySQL查询优化器的核心逻辑可总结为“先优化逻辑，再选择物理执行方式，最后通过成本估算选最优计划”：

  1. 逻辑优化通过谓词下推、连接重排等手段，重构查询逻辑，减少后续处理的数据量。
  2. 物理优化为每个逻辑步骤选择具体的执行算法（如嵌套循环连接、索引扫描）和索引。
  3. 成本估算模型基于IO成本和CPU成本，结合统计信息，从多个候选计划中选择成本最低的执行计划。
编辑 (opens new window)
#MySQL查询优化
上次更新: 2026/01/21, 19:29:14
MySQL崩溃恢复

←

最近更新
01
订单超时取消
01-21
02
双 Token 登录
01-21
03
长短链接跳转
01-21
更多文章>
Theme by Vdoing