文章目录

《MySQL索引核心面试背诵清单》 + EXPLAIN执行计划完整教程

一、MySQL索引核心面试问答清单（可直接背诵）

模块1：基础概念与底层原理

  1. Q：什么是索引？本质是什么？
    A：索引是数据库为加速查询，对数据表一列或多列的值进行排序的数据结构。本质是通过空间换时间，将无序数据变为有序，减少磁盘I/O次数。

  2. Q：InnoDB为什么用B+树作为索引默认数据结构，而不是B树/哈希表？
    A：

    • 对比B树：B+树所有数据存在叶子节点，非叶子节点仅存索引键，相同磁盘页能存更多索引，树高更低（通常3-4层），磁盘I/O更少；叶子节点形成双向链表，范围查询更高效。
    • 对比哈希表：哈希表仅支持等值查询，不支持范围查询、排序和分组；存在哈希冲突问题。

  3. Q：聚簇索引与非聚簇索引（二级索引）的核心区别？
    A：

    对比维度 聚簇索引 非聚簇索引
    存储内容 完整数据行 索引键+主键值
    物理顺序 与数据行物理顺序一致 与数据行物理顺序无关
    数量 一个表只能有1个 一个表可以有多个
    查询速度 更快（无需回表） 较慢（通常需要回表）

  4. Q：什么是回表查询？如何避免？
    A：通过二级索引找到主键后，再通过聚簇索引获取完整数据行的过程。避免方法：使用覆盖索引（查询所需的所有列都包含在索引中）。

  5. Q：什么是覆盖索引？有什么优势？
    A：索引包含了查询需要的所有列（SELECT、WHERE、JOIN、ORDER BY、GROUP BY涉及的列）。优势：无需回表，减少磁盘I/O；避免聚簇索引的随机I/O，性能提升显著。

模块2：最左前缀匹配原则（核心必考）

  1. Q：什么是最左前缀匹配原则？
    A：联合索引是按列顺序构建的B+树，查询条件必须从索引的最左前列开始，且不能跳过中间列，否则无法使用该索引的全部或部分。

  2. Q：最左前缀匹配原则的底层原理是什么？
    A：联合索引的B+树首先按第一列排序，第一列相同再按第二列排序，以此类推。跳过前面的列，后面的列在B+树中是无序的，无法进行高效查找。

  3. Q：联合索引idx_a_b_c(a,b,c)，以下查询能用到索引的哪些列？

    • WHERE a=1 AND b=2 AND c=3 → ✅ a+b+c（完整使用）
    • WHERE a=1 AND b>2 AND c=3 → ⚠️ a+b（范围查询截断c）
    • WHERE a=1 AND c=3 → ⚠️ 仅a（跳过中间列b）
    • WHERE b=2 AND c=3 → ❌ 完全不用（跳过最左列a）
    • WHERE b=2 AND a=1 → ✅ a+b（优化器自动调整顺序）

  4. Q：为什么范围查询（>、<、BETWEEN）会截断后面的索引列？
    A：范围查询会得到一个区间的结果，在这个区间内，后面的列是无序的，无法利用B+树的有序性进行快速查找。

  5. Q：MySQL优化器会自动调整WHERE条件的顺序吗？
    A：会。对于等值查询条件，优化器会自动调整列的顺序以匹配联合索引的最左前缀。但范围查询条件的顺序无法调整。

模块3：索引失效场景（高频必考）

  1. Q：列举至少10种常见的索引失效场景？
    A：

    1. 违反最左前缀匹配原则（跳过最左列或中间列）
    2. 在索引列上使用函数、表达式或算术运算
    3. 使用不等于操作符（!=、<>、NOT）
    4. 使用IS NOT NULL（IS NULL可以使用索引）
    5. 字符串不加引号导致隐式类型转换
    6. LIKE通配符开头（%xxx或%xxx%）
    7. OR连接的条件中存在非索引列
    8. MySQL优化器判断全表扫描比使用索引更快（如小表、返回数据占比>30%）
    9. JOIN关联条件的数据类型不一致
    10. 使用前缀索引时，ORDER BY/GROUP BY无法使用索引

  2. Q：字符串不加引号为什么会导致索引失效？
    A：当索引列是字符串类型，查询条件传入数值时，MySQL会对索引列执行CAST(col AS SIGNED)隐式转换，相当于在索引列上使用了函数，导致索引失效。

  3. Q：LIKE 'xxx%'可以用索引，LIKE '%xxx'为什么不行？
    A：LIKE 'xxx%'是前缀匹配，符合最左前缀原则，B+树可以快速定位以xxx开头的记录；LIKE '%xxx'是后缀匹配，字符串中间或结尾的内容在B+树中是无序的，无法使用索引。

  4. Q：OR连接的条件中存在非索引列，为什么整个查询都不用索引？
    A：因为OR需要满足任意一个条件，如果其中一个条件没有索引，就必须进行全表扫描来获取满足该条件的记录，此时使用索引反而会增加开销。

  5. Q：IS NULL和IS NOT NULL都不能使用索引吗？
    A：不是。IS NULL可以使用索引；IS NOT NULL通常会导致索引失效，但如果索引列中NULL值占比很高（如>90%），MySQL可能会选择使用索引。

模块4：索引设计原则（最佳实践）

  1. Q：如何设计一个好的联合索引？
    A：遵循以下优先级：

    1. 最左前缀优先：将最常用的等值查询列放在最左边
    2. 高选择性优先：在满足最左前缀的前提下，将选择性高的列放在前面
    3. 范围查询放最后：因为范围查询会截断后面的列
    4. 覆盖索引优先：尽量包含查询所需的所有列，避免回表
    5. 排序分组匹配：ORDER BY/GROUP BY的列顺序与索引顺序一致

  2. Q：联合索引和多个单列索引哪个更好？为什么？
    A：联合索引更好。原因：

    • 多个单列索引在多条件查询时，MySQL通常只能选择其中一个最优的索引，无法同时使用多个
    • 联合索引可以利用最左前缀原则，支持更多的查询组合
    • 联合索引占用的空间通常比多个单列索引更小
    • 联合索引更容易实现覆盖索引，避免回表

  3. Q：什么是前缀索引？适用场景和缺点是什么？
    A：对字符串列的前N个字符建立的索引。

    • 适用场景：长字符串列（如VARCHAR(255)），且前N个字符的选择性足够高
    • 优点：减少索引占用的空间，提高索引插入和更新的速度
    • 缺点：无法使用覆盖索引；ORDER BY和GROUP BY无法使用前缀索引

  4. Q：索引越多越好吗？为什么？
    A：不是。过多的索引会带来以下问题：

    • 增加数据插入、更新、删除的开销（每次修改都需要更新所有相关索引）
    • 占用更多的磁盘空间
    • 增加MySQL优化器选择索引的时间，可能导致优化器选择错误的索引

  5. Q：什么是冗余索引？如何发现？
    A：如果索引A包含了索引B的所有列，那么索引B就是冗余索引。例如，有了联合索引idx_a_b(a,b)，单独的索引idx_a(a)就是冗余的。

    • 发现方法：使用sys.schema_redundant_indexes视图；手动分析索引结构

模块5：实战优化与进阶问题

  1. Q：慢查询优化的基本步骤是什么？
    A：

    1. 开启慢查询日志，捕获慢SQL
    2. 使用EXPLAIN分析执行计划，确认索引是否被正确使用
    3. 分析索引失效的原因，优化SQL语句
    4. 调整或创建合适的索引（优先考虑联合索引和覆盖索引）
    5. 验证优化效果，重复上述步骤

  2. Q：如何避免回表查询？
    A：

    1. 使用覆盖索引：将查询所需的所有列都包含在索引中
    2. 尽量使用主键查询：主键索引是聚簇索引，本身就包含完整数据行
    3. 避免使用SELECT *：只查询需要的列，更容易实现覆盖索引

  3. Q：为什么性别列不适合建立索引？
    A：因为性别列的选择性极低（只有男/女/未知3个值），索引的区分度很差。查询时返回的数据量占比很高，MySQL优化器会选择全表扫描而不是使用索引。

  4. Q：小表为什么不需要建立索引？
    A：对于数据量很小的表（通常小于1000行），全表扫描的速度比使用索引更快。因为使用索引需要先读取索引页，再读取数据页，反而增加了磁盘I/O次数。

二、EXPLAIN执行计划完整教程（索引分析必备）

2.1 EXPLAIN基本语法

EXPLAIN SELECT * FROM user WHERE name = 'zhangsan' AND age = 30;
  • 作用：模拟MySQL优化器执行SQL语句的过程，分析查询语句的执行计划
  • 输出：12个字段，核心字段为id、select_type、type、key、rows、Extra

2.2 EXPLAIN输出字段全解

字段名 含义 核心关注点
id 查询的序列号，标识查询执行的顺序 id越大越先执行；id相同从上到下执行
select_type 查询的类型 SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION
table 输出行所引用的表
type 访问类型，判断查询性能的核心指标 从最优到最差：system > const > eq_ref > ref > range > index > ALL
possible_keys 可能用到的索引 仅作参考，不代表实际会使用
key 实际用到的索引 NULL表示没有使用索引
key_len 使用的索引长度 可用于判断联合索引使用了哪些列
ref 与索引比较的列或常量 const表示常量匹配，func表示函数匹配
rows 估算的要检查的行数 数值越小越好，与实际行数可能有偏差
filtered 按表条件过滤的行百分比 数值越大越好
Extra 额外信息 包含索引使用、排序、临时表等关键信息

2.3 核心字段深度解析

1. type字段（访问类型，性能从优到劣）
级别 含义 场景 性能评级
system 表只有一行（系统表） 如查询mysql系统表 ★★★★★（最优）
const 主键或唯一索引的等值查询 WHERE id=1 ★★★★★
eq_ref 主键或唯一索引的关联查询 JOIN user ON user.id=order.user_id ★★★★☆
ref 非唯一索引的等值查询 WHERE name='zhangsan' ★★★★☆
range 索引范围查询 WHERE age>20、WHERE id BETWEEN 1 AND 10 ★★★☆☆
index 全索引扫描 遍历整个索引树，比全表扫描快但仍较差 ★★☆☆☆
ALL 全表扫描 没有使用任何索引 ★☆☆☆☆（最差）

优化目标：至少达到range级别，最好是ref级别。

2. key_len字段（索引长度计算）
  • 作用：判断联合索引实际使用了哪些列
  • 计算规则：
    • 整数类型：TINYINT=1，SMALLINT=2，INT=4，BIGINT=8
    • 字符串类型：CHAR(n)=n字符集字节数，VARCHAR(n)=n字符集字节数+2（变长字段）
    • NULL值：额外加1字节
  • 示例：联合索引idx_name_age(name VARCHAR(20) NOT NULL, age INT NULL)，字符集utf8mb4（4字节）
    • key_len=80 → 仅使用了name列（20*4=80）
    • key_len=85 → 使用了name+age列（80+4+1=85）
3. Extra字段（关键额外信息）
常见值 含义 评价 优化建议
Using index 使用了覆盖索引，无需回表 ✅ 非常好 保持
Using where 使用了WHERE条件过滤数据 中性 结合索引使用
Using filesort 无法使用索引排序，需要额外排序 ❌ 差 调整索引顺序匹配ORDER BY
Using temporary 使用了临时表存储中间结果 ❌ 很差 调整索引顺序匹配GROUP BY/ORDER BY
Using index condition 索引条件下推（ICP），在索引层过滤数据 ✅ 好 MySQL 5.6+默认开启
Impossible WHERE WHERE条件永远为假 中性 检查SQL逻辑

2.4 实战案例：用EXPLAIN分析索引失效与优化

案例背景：用户表user(id INT PRIMARY KEY, name VARCHAR(20), age INT, create_time DATETIME)，建立联合索引idx_name_age(name, age)。

案例1：违反最左前缀原则
EXPLAIN SELECT * FROM user WHERE age = 30;
  • 执行计划：type=ALL，key=NULL，Extra=Using where
  • 分析：跳过了最左列name，索引完全失效，全表扫描
  • 优化：调整索引为idx_age_name(age, name)，或在查询条件中加入name
案例2：索引列上使用函数
EXPLAIN SELECT * FROM user WHERE YEAR(create_time) = 2023;
  • 执行计划：type=ALL，key=NULL，Extra=Using where
  • 分析：在create_time列上使用了YEAR()函数，索引失效
  • 优化：改写为范围查询 
    SELECT * FROM user WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
案例3：范围查询截断后面的列
EXPLAIN SELECT * FROM user WHERE name = 'zhangsan' AND age > 30;
  • 执行计划：type=range，key=idx_name_age，key_len=85
  • 分析：使用了name+age列，因为age是范围查询，后面没有其他列，所以是最优情况
  • 扩展：如果索引是idx_name_age_create_time(name, age, create_time)，则create_time列无法使用
案例4：覆盖索引优化
-- 原始查询
EXPLAIN SELECT id, name, age FROM user WHERE name = 'zhangsan' AND age = 30;
  • 执行计划：type=ref，key=idx_name_age，Extra=Using index
  • 分析：查询的列id、name、age都包含在索引中（id是主键，自动包含在二级索引中），使用了覆盖索引，无需回表

2.5 EXPLAIN使用技巧与注意事项

  1. EXPLAIN只能分析SELECT语句：不能分析INSERT、UPDATE、DELETE语句（可以将其转换为SELECT语句分析）
  2. rows是估算值：不是精确值，与实际行数可能有偏差，仅供参考
  3. 使用EXPLAIN ANALYZE：MySQL 8.0.18+支持，可以实际执行SQL并输出更准确的执行计划
  4. 关注Extra字段：很多性能问题都体现在Extra字段中，特别是Using filesort和Using temporary
  5. 多次执行EXPLAIN：MySQL优化器可能会根据数据分布选择不同的执行计划

《MySQL 8.0 索引新特性速查表》

一、速查表总览

特性名称 引入版本 核心价值 适用场景
不可见索引 8.0.0 索引"软删除"，安全测试索引变更 索引灰度发布、冗余索引清理
降序索引 8.0.0 原生支持降序存储，优化排序查询 ORDER BY DESC、混合排序场景
函数索引 8.0.13 基于表达式创建索引，解决函数导致的索引失效 函数查询、JSON字段查询、计算字段
索引跳跃扫描 8.0.13 联合索引非首列也能走索引，提升索引利用率 联合索引首列基数低、仅查询后缀列
多值索引 8.0.17 为JSON数组创建索引，加速JSON数组查询 JSON数组包含、成员匹配查询
并行DDL 8.0.27 多线程创建/重建索引，大表操作提速3-5倍 千万级以上大表索引变更
直方图统计 8.0.0 精准统计数据分布，优化器生成更优执行计划 数据倾斜列、非索引列过滤条件

二、核心新特性详解

1. 不可见索引（Invisible Indexes）

核心功能：将索引标记为对优化器不可见，但索引数据仍会正常维护。相当于给索引提供了"软删除"功能。

使用语法：

-- 创建不可见索引
CREATE INDEX idx_name ON users(name) INVISIBLE;

-- 修改现有索引可见性
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;
ALTER TABLE users ALTER INDEX idx_name VISIBLE;

-- 临时启用所有不可见索引（会话级别）
SET SESSION optimizer_switch="use_invisible_indexes=on";

优势：

  • 安全测试删除索引的影响，避免误删后重建大表索引的高昂成本
  • 支持索引灰度发布，逐步验证新索引效果
  • 保留索引但不影响现有查询计划

注意事项：

  • 主键索引不能设置为不可见
  • 唯一约束依赖的索引也不能设置为不可见
  • 不可见索引仍会占用磁盘空间并影响DML性能

典型应用：

-- 索引下线流程
ALTER TABLE large_table ALTER INDEX idx_old INVISIBLE; -- 先隐藏
-- 观察1-2周业务无影响后再删除
ALTER TABLE large_table DROP INDEX idx_old;

2. 降序索引（Descending Indexes）

核心功能：真正支持B+树索引的降序存储，而不是像5.7那样语法支持但实际仍为升序。

使用语法：

-- 创建混合排序的联合索引
CREATE INDEX idx_order_date_customer ON orders(order_date DESC, customer_id ASC);

-- 查询优化效果
EXPLAIN SELECT * FROM orders ORDER BY order_date DESC, customer_id ASC;
-- Extra: Using index（无filesort）

优势：

  • 避免ORDER BY DESC查询时的反向扫描和额外排序（Using filesort）
  • 支持混合排序（部分列升序，部分列降序）
  • 提升分页查询和TOP-N查询的性能

注意事项：

  • 仅InnoDB存储引擎支持降序索引
  • 仅BTREE索引支持降序
  • MySQL 8.0不再对GROUP BY操作进行隐式排序，需要显式指定ORDER BY

性能对比：

  • 5.7：ORDER BY DESC → 升序索引反向扫描 + filesort
  • 8.0：ORDER BY DESC → 降序索引正向扫描（性能提升2-3倍）

3. 函数索引（Functional Indexes）

核心功能：支持基于函数或表达式的结果创建索引，解决了"在索引列上使用函数导致索引失效"的经典问题。

使用语法：

-- 基于函数创建索引
CREATE INDEX idx_upper_name ON users((UPPER(name)));

-- 基于表达式创建索引
CREATE INDEX idx_price_discount ON products((price * discount));

-- 联合索引中包含函数部分
CREATE INDEX idx_name_age ON users((UPPER(name)), age);

-- JSON字段函数索引
CREATE INDEX idx_json_zip ON orders((data->>'$.zipcode'));

优势：

  • 加速包含函数或表达式的查询
  • 无需创建额外的虚拟列（底层基于虚拟列实现）
  • 支持JSON字段的高效查询

注意事项：

  • 必须使用确定性函数（相同输入总是产生相同输出）
  • 不能使用用户定义函数
  • 函数索引不能是主键索引
  • 函数索引不支持前缀索引

典型应用：

-- 优化前（索引失效）
SELECT * FROM users WHERE YEAR(create_time) = 2023;

-- 优化后（函数索引）
CREATE INDEX idx_year_create ON users((YEAR(create_time)));
SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 索引生效

4. 索引跳跃扫描（Index Skip Scan）

核心功能：优化器可以在联合索引的非首列上执行范围扫描，即使查询条件中没有包含最左前缀列。

触发条件：

  • 联合索引首列的基数极低（不同值数量少）
  • 查询条件仅包含联合索引的后缀列
  • 优化器估算跳跃扫描的代价低于全表扫描

工作原理：
对联合索引(a, b)，查询WHERE b = ?时，优化器会：

  1. 扫描a列的所有不同值
  2. 对每个a值，执行一次b = ?的范围扫描
  3. 合并所有结果

使用示例：

-- 创建联合索引
CREATE INDEX idx_dept_position ON employees(department, position);

-- 查询所有经理（仅使用position列）
EXPLAIN SELECT * FROM employees WHERE position = 'Manager';
-- type: range, Extra: Using index for skip scan

注意事项：

  • 这是优化器自动选择的优化，无需手动开启
  • 不能替代正确的索引设计
  • 仅适用于首列基数低的场景

5. 多值索引（Multi-Valued Indexes）

核心功能：为JSON数组创建二级索引，单个数据记录可以对应多个索引条目。

使用语法：

-- 为JSON数组创建多值索引
CREATE INDEX idx_tags ON products((CAST(data->'$.tags' AS JSON ARRAY)));

-- 查询包含特定标签的产品
SELECT * FROM products WHERE JSON_CONTAINS(data->'$.tags', '"electronics"');

-- 查询数组中任意元素满足条件的记录
SELECT * FROM products WHERE JSON_OVERLAPS(data->'$.tags', '["electronics", "books"]');

优势：

  • 大幅提升JSON数组查询的性能
  • 支持JSON_CONTAINS、JSON_OVERLAPS等函数
  • 无需将JSON数组拆分为单独的表

注意事项：

  • 仅支持JSON数组类型
  • 只能作为联合索引的最后一列
  • 不支持唯一约束

6. 并行DDL（Parallel DDL）

核心功能：多线程执行索引创建和重建操作，充分利用多核CPU和高速存储资源。

关键参数：

参数名 默认值 作用
innodb_ddl_threads 4 排序和加载阶段的并行线程数
innodb_parallel_read_threads 4 扫描聚簇索引的并行线程数
innodb_ddl_buffer_size 1GB DDL操作的缓冲区大小

使用示例：

-- 临时调整并行度创建大表索引
SET SESSION innodb_ddl_threads = 8;
SET SESSION innodb_parallel_read_threads = 8;
CREATE INDEX idx_large_column ON large_table(large_column);

注意事项：

  • 仅支持二级索引的创建和重建
  • 不支持全文索引、空间索引和包含虚拟列的索引
  • 并行度越高，对系统资源的消耗越大

7. 直方图统计（Histograms）

核心功能：统计列值的分布情况，帮助优化器更准确地估算查询条件的选择率，生成更优的执行计划。

使用语法：

-- 创建直方图（默认100个桶）
ANALYZE TABLE sales UPDATE HISTOGRAM ON price;

-- 创建指定桶数的直方图（最多1024个）
ANALYZE TABLE sales UPDATE HISTOGRAM ON price WITH 256 BUCKETS;

-- 查看直方图信息
SELECT 
  TABLE_NAME, COLUMN_NAME,
  HISTOGRAM->>'$."data-type"' AS data_type,
  JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS bucket_count
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;

-- 删除直方图
ANALYZE TABLE sales DROP HISTOGRAM ON price;

优势：

  • 解决数据倾斜导致的执行计划错误
  • 提升非索引列过滤条件的查询性能
  • 帮助优化器选择正确的JOIN顺序

注意事项：

  • 直方图不会自动更新，需要定期执行ANALYZE TABLE
  • 大表创建直方图会消耗一定的内存和CPU资源
  • 建议桶数设置为32-256之间

三、其他重要索引优化

  1. 索引条件下推（ICP）增强：支持对JSON字段和空间索引的ICP优化，进一步减少回表次数
  2. 自适应哈希索引（AHI）优化：减少哈希冲突，支持动态调整索引大小，等值查询性能提升2-3倍
  3. 隐藏主键优化：无主键表自动生成连续递增的隐藏主键，避免随机写入导致的索引碎片
  4. 原子DDL：索引创建和删除操作是原子性的，失败时不会留下部分元数据
  5. Instant DDL：支持秒级添加列，不影响索引结构和数据

四、索引管理最佳实践

  1. 新索引上线流程：

    -- 1. 创建为不可见索引
ALTER TABLE large_table ADD INDEX idx_new (col1, col2) INVISIBLE;
-- 2. 会话级别测试
SET SESSION optimizer_switch="use_invisible_indexes=on";
EXPLAIN SELECT * FROM large_table WHERE col1 = ? AND col2 = ?;
-- 3. 逐步开放
ALTER TABLE large_table ALTER INDEX idx_new VISIBLE;

  2. 索引下线流程：

    -- 1. 先隐藏索引
ALTER TABLE large_table ALTER INDEX idx_old INVISIBLE;
-- 2. 观察1-2周业务无影响
-- 3. 物理删除索引
ALTER TABLE large_table DROP INDEX idx_old;

  3. 大表索引操作：

    • 使用并行DDL提升速度
    • 业务低峰期执行
    • 调整innodb_ddl_buffer_size参数
    • 避免在业务高峰期进行索引重建

五、版本兼容性说明

  • 8.0.0：不可见索引、降序索引、直方图统计、原子DDL
  • 8.0.13：函数索引、索引跳跃扫描
  • 8.0.17：多值索引
  • 8.0.27：并行DDL（innodb_ddl_threads）
  • 8.0.29：Instant DDL支持在任意位置添加列

《MySQL 8.0索引新特性面试高频问答》 + 性能对比测试模板

一、MySQL 8.0索引新特性面试高频问答

模块1：基础与通用问题

  1. Q：MySQL 8.0相比5.7在索引方面有哪些重大改进？
    标准答案：

    • 原生支持不可见索引，实现索引"软删除"
    • 真正支持降序索引，解决混合排序性能问题
    • 新增函数索引，彻底解决函数导致的索引失效
    • 引入索引跳跃扫描，提升联合索引利用率
    • 支持多值索引，加速JSON数组查询
    • 新增并行DDL，大表索引创建速度提升3-5倍
    • 引入直方图统计，优化数据倾斜场景的执行计划

    加分点：

    • 8.0的索引优化器更加智能，能更好地处理复杂查询
    • 原子DDL保证索引操作的原子性，避免元数据损坏
    • Instant DDL支持秒级添加列，不影响索引结构

  2. Q：为什么MySQL 5.7的降序索引是"假的"？
    标准答案：
    MySQL 5.7虽然支持DESC语法，但B+树索引仍然是按升序存储的。当执行ORDER BY DESC查询时，MySQL只能从升序索引的末尾开始反向扫描，并且无法利用索引进行混合排序（部分列升序，部分列降序），必须进行额外的Using filesort操作。

    加分点：

    • 8.0的降序索引真正按降序存储数据，正向扫描即可得到降序结果
    • 8.0支持混合排序索引，如(a DESC, b ASC)，完全避免filesort
    • 对于TOP-N查询和分页查询，降序索引的性能提升尤为明显

  3. Q：MySQL 8.0的索引优化器有哪些改进？
    标准答案：

    • 支持索引跳跃扫描，能在联合索引非首列上执行范围扫描
    • 直方图统计提供更准确的数据分布信息，减少执行计划错误
    • 索引条件下推（ICP）增强，支持JSON字段和空间索引
    • 不可见索引功能，允许安全测试索引变更
    • 更好的成本模型，能更准确地估算不同索引的查询代价

模块2：不可见索引

  1. Q：什么是不可见索引？有什么作用？
    标准答案：
    不可见索引是指对MySQL优化器不可见的索引，但索引数据仍会正常维护。它的主要作用是提供索引"软删除"功能，允许安全地测试删除索引的影响，避免误删后重建大表索引的高昂成本。

    加分点：

    • 支持索引灰度发布，逐步验证新索引效果
    • 可以保留索引但不影响现有查询计划
    • 主键索引和唯一约束依赖的索引不能设置为不可见

  2. Q：不可见索引和删除索引有什么区别？
    标准答案：

    对比维度 不可见索引 删除索引
    索引数据 仍然存在并正常维护 完全删除
    恢复时间 秒级（ALTER INDEX VISIBLE） 重新创建（大表可能需要数小时）
    对DML影响 仍然存在 消失
    风险 极低 高（误删影响业务）

  3. Q：如何使用不可见索引进行索引灰度发布？
    标准答案：

    -- 1. 创建新索引为不可见状态
ALTER TABLE orders ADD INDEX idx_customer_date(customer_id, order_date) INVISIBLE;

-- 2. 会话级别测试索引效果
SET SESSION optimizer_switch="use_invisible_indexes=on";
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date DESC;

-- 3. 验证无误后，逐步开放给所有用户
ALTER TABLE orders ALTER INDEX idx_customer_date VISIBLE;

模块3：函数索引

  1. Q：什么是函数索引？解决了什么问题？
    标准答案：
    函数索引是基于函数或表达式的结果创建的索引。它解决了MySQL长期存在的"在索引列上使用函数导致索引失效"的经典问题，允许对包含函数或表达式的查询进行优化。

    加分点：

    • 函数索引底层基于虚拟列实现，但无需显式创建虚拟列
    • 支持JSON字段的高效查询
    • 只能使用确定性函数（相同输入总是产生相同输出）

  2. Q：函数索引和虚拟列索引有什么区别？
    标准答案：
    函数索引本质上是虚拟列索引的语法糖。当创建函数索引时，MySQL会自动在表中添加一个隐藏的虚拟列，并在该列上创建索引。两者的性能完全相同，但函数索引语法更简洁，不需要修改表结构。

    加分点：

    • 函数索引的虚拟列是隐藏的，不会出现在DESCRIBE的输出中
    • 函数索引不能是主键索引
    • 函数索引不支持前缀索引

  3. Q：函数索引有哪些使用限制？
    标准答案：

    • 只能使用确定性函数，不能使用用户定义函数
    • 不能是主键索引
    • 不支持前缀索引
    • 不能在全文索引或空间索引中使用函数
    • 函数索引的表达式不能超过1024个字符

模块4：索引跳跃扫描

  1. Q：什么是索引跳跃扫描？底层原理是什么？
    标准答案：
    索引跳跃扫描是MySQL 8.0.13引入的优化器特性，允许在联合索引的非首列上执行范围扫描，即使查询条件中没有包含最左前缀列。

    底层原理：对于联合索引(a, b)，查询WHERE b = ?时，优化器会先扫描a列的所有不同值，然后对每个a值执行一次b = ?的范围扫描，最后合并所有结果。

    加分点：

    • 这是优化器自动选择的优化，无需手动开启
    • 仅适用于联合索引首列基数极低的场景
    • 不能替代正确的索引设计，但可以作为补充优化手段

  2. Q：索引跳跃扫描的触发条件是什么？
    标准答案：

    • 联合索引首列的基数极低（不同值数量少，通常小于100）
    • 查询条件仅包含联合索引的后缀列
    • 优化器估算跳跃扫描的代价低于全表扫描
    • 没有其他更优的索引可用

  3. Q：索引跳跃扫描有什么局限性？
    标准答案：

    • 仅适用于首列基数低的场景，如果首列基数高，性能反而会下降
    • 不能用于范围查询，只能用于等值查询
    • 不能替代正确的索引设计
    • 优化器可能会错误地选择跳跃扫描，需要使用EXPLAIN验证

模块5：多值索引

  1. Q：什么是多值索引？适用场景是什么？
    标准答案：
    多值索引是为JSON数组创建的二级索引，单个数据记录可以对应多个索引条目。它主要用于加速JSON数组的包含查询和成员匹配查询。

    加分点：

    • 支持JSON_CONTAINS和JSON_OVERLAPS函数
    • 无需将JSON数组拆分为单独的表
    • 只能作为联合索引的最后一列

  2. Q：多值索引和普通索引有什么区别？
    标准答案：

    对比维度 多值索引 普通索引
    索引条目 一个数据行对应多个索引条目 一个数据行对应一个索引条目
    适用类型 仅支持JSON数组 支持所有数据类型
    唯一约束 不支持 支持
    联合索引位置 只能是最后一列 可以是任意位置

  3. Q：多值索引有哪些使用限制？
    标准答案：

    • 仅支持JSON数组类型
    • 只能作为联合索引的最后一列
    • 不支持唯一约束
    • 不支持前缀索引
    • 只能用于JSON_CONTAINS和JSON_OVERLAPS函数

模块6：并行DDL与直方图

  1. Q：MySQL 8.0的并行DDL是如何工作的？
    标准答案：
    MySQL 8.0.27引入的并行DDL使用多线程执行索引创建和重建操作。它将索引创建过程分为三个阶段：扫描阶段、排序阶段和加载阶段，每个阶段都可以并行执行。

    加分点：

    • 扫描阶段使用innodb_parallel_read_threads参数控制并行度
    • 排序和加载阶段使用innodb_ddl_threads参数控制并行度
    • 大表索引创建速度可以提升3-5倍
    • 仅支持二级索引的创建和重建

  2. Q：什么是直方图统计？解决了什么问题？
    标准答案：
    直方图统计是MySQL 8.0引入的一种数据分布统计方法，它将列值划分为多个桶，统计每个桶中的数据量。它解决了数据倾斜导致的执行计划错误问题，帮助优化器更准确地估算查询条件的选择率。

    加分点：

    • 直方图不会自动更新，需要定期执行ANALYZE TABLE
    • 最多支持1024个桶，建议设置为32-256之间
    • 特别适用于非索引列的过滤条件
    • 可以显著提升JOIN查询的性能

二、MySQL 8.0索引新特性性能对比测试模板

2.1 测试环境准备

-- 创建测试数据库
CREATE DATABASE IF NOT EXISTS index_test;
USE index_test;

-- 创建测试表
CREATE TABLE IF NOT EXISTS test_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    status TINYINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    details JSON NOT NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 插入100万条测试数据（执行时间可能较长）
INSERT INTO test_orders (customer_id, order_date, status, amount, details)
SELECT 
    FLOOR(RAND() * 1000), -- 1000个不同的客户
    DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY),
    FLOOR(RAND() * 5), -- 5种订单状态
    ROUND(RAND() * 1000, 2),
    JSON_OBJECT(
        'tags', JSON_ARRAY(
            ELT(FLOOR(RAND() * 5) + 1, 'electronics', 'clothing', 'books', 'food', 'sports'),
            ELT(FLOOR(RAND() * 3) + 1, 'new', 'used', 'refurbished')
        ),
        'zipcode', CONCAT(FLOOR(RAND() * 90000) + 10000)
    )
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t2,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t3,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t4,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t5,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
     SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) t6
LIMIT 1000000;

2.2 测试用例1：降序索引性能对比

-- 测试1：MySQL 5.7 vs 8.0 降序查询性能
-- 5.7环境
CREATE INDEX idx_order_date ON test_orders(order_date);
SELECT SQL_NO_CACHE * FROM test_orders ORDER BY order_date DESC LIMIT 1000;
-- 记录执行时间

-- 8.0环境
CREATE INDEX idx_order_date_desc ON test_orders(order_date DESC);
SELECT SQL_NO_CACHE * FROM test_orders ORDER BY order_date DESC LIMIT 1000;
-- 记录执行时间

-- 测试2：混合排序性能对比
-- 5.7环境
CREATE INDEX idx_customer_date ON test_orders(customer_id, order_date);
SELECT SQL_NO_CACHE * FROM test_orders WHERE customer_id = 123 ORDER BY order_date DESC LIMIT 100;
-- 记录执行时间（会有Using filesort）

-- 8.0环境
CREATE INDEX idx_customer_date_desc ON test_orders(customer_id, order_date DESC);
SELECT SQL_NO_CACHE * FROM test_orders WHERE customer_id = 123 ORDER BY order_date DESC LIMIT 100;
-- 记录执行时间（无Using filesort）

2.3 测试用例2：函数索引性能对比

-- 测试：函数索引 vs 无索引 vs 普通索引
-- 无索引
SELECT SQL_NO_CACHE COUNT(*) FROM test_orders WHERE YEAR(create_time) = 2023;
-- 记录执行时间

-- 普通索引（失效）
CREATE INDEX idx_create_time ON test_orders(create_time);
SELECT SQL_NO_CACHE COUNT(*) FROM test_orders WHERE YEAR(create_time) = 2023;
-- 记录执行时间（仍然全表扫描）

-- 函数索引
CREATE INDEX idx_year_create ON test_orders((YEAR(create_time)));
SELECT SQL_NO_CACHE COUNT(*) FROM test_orders WHERE YEAR(create_time) = 2023;
-- 记录执行时间（索引生效）

-- JSON字段函数索引测试
SELECT SQL_NO_CACHE COUNT(*) FROM test_orders WHERE details->>'$.zipcode' = '10001';
-- 记录执行时间

CREATE INDEX idx_json_zip ON test_orders((details->>'$.zipcode'));
SELECT SQL_NO_CACHE COUNT(*) FROM test_orders WHERE details->>'$.zipcode' = '10001';
-- 记录执行时间

2.4 测试用例3：索引跳跃扫描性能对比

-- 测试：索引跳跃扫描 vs 全表扫描
CREATE INDEX idx_status_customer ON test_orders(status, customer_id);

-- 仅查询customer_id列（触发跳跃扫描）
EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM test_orders WHERE customer_id = 123;
-- 查看Extra字段是否有"Using index for skip scan"
-- 记录执行时间

-- 禁用跳跃扫描
SET SESSION optimizer_switch="skip_scan=off";
EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM test_orders WHERE customer_id = 123;
-- 记录执行时间（全表扫描）

2.5 测试用例4：多值索引性能对比

-- 测试：多值索引 vs 无索引
SELECT SQL_NO_CACHE COUNT(*) FROM test_orders 
WHERE JSON_CONTAINS(details->'$.tags', '"electronics"');
-- 记录执行时间

CREATE INDEX idx_tags ON test_orders((CAST(details->'$.tags' AS JSON ARRAY)));
SELECT SQL_NO_CACHE COUNT(*) FROM test_orders 
WHERE JSON_CONTAINS(details->'$.tags', '"electronics"');
-- 记录执行时间

2.6 测试用例5：并行DDL性能对比

-- 测试：不同并行度下的索引创建速度
-- 并行度=1
SET SESSION innodb_ddl_threads = 1;
SET SESSION innodb_parallel_read_threads = 1;
SET @start = NOW();
CREATE INDEX idx_amount ON test_orders(amount);
SET @end = NOW();
SELECT TIMESTAMPDIFF(SECOND, @start, @end) AS create_time_1_thread;

-- 并行度=4
DROP INDEX idx_amount ON test_orders;
SET SESSION innodb_ddl_threads = 4;
SET SESSION innodb_parallel_read_threads = 4;
SET @start = NOW();
CREATE INDEX idx_amount ON test_orders(amount);
SET @end = NOW();
SELECT TIMESTAMPDIFF(SECOND, @start, @end) AS create_time_4_threads;

-- 并行度=8
DROP INDEX idx_amount ON test_orders;
SET SESSION innodb_ddl_threads = 8;
SET SESSION innodb_parallel_read_threads = 8;
SET @start = NOW();
CREATE INDEX idx_amount ON test_orders(amount);
SET @end = NOW();
SELECT TIMESTAMPDIFF(SECOND, @start, @end) AS create_time_8_threads;

2.7 测试结果分析模板

测试用例 5.7执行时间 8.0执行时间 性能提升 关键指标
降序查询 type, Extra
混合排序 type, Extra
函数查询 type, key_len
JSON查询 type, key_len
索引跳跃扫描 - Extra
多值索引查询 - type, key_len
索引创建(1线程)
索引创建(4线程) -
索引创建(8线程) -

2.8 测试注意事项

  1. 每次测试前执行RESET QUERY CACHE（5.7）或RESET PERSIST（8.0）清除缓存
  2. 每个测试用例执行3次，取平均值
  3. 测试时确保系统负载较低，避免其他进程影响测试结果
  4. 对于大表测试，建议在业务低峰期进行
  5. 使用EXPLAIN ANALYZE（8.0.18+）获取更准确的执行时间
# mysql # 面试 # 数据库 # 后端 # sql # java # python
Logo
AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

数据与人工智能失败的根本原因

原文：towardsdatascience.com/the-root-cause-of-why-organizations-fail-with-data-ai-0095a73cf5ab?

avatar AtomGit开源社区

双连杆机械臂 RBFNN+NTSM + 自适应强化学习控制（带饱和补偿）（Matlab代码实现）

针对双连杆机械臂在模型不确定性、外部扰动以及执行器饱和约束下的高精度轨迹跟踪控制难题，本文提出一种融合径向基函数神经网络、非奇异终端滑模控制与强化学习的自适应鲁棒控制方案。该方法以非奇异终端滑模为核心控制框架，保证系统状态在有限时间内收敛并避免传统滑模的奇异性问题；采用评价 - 动作双网络强化学习结构，利用径向基函数神经网络实现对系统未知动态与不确定项的在线逼近，同时完成控制策略的自适应优化；设计

avatar AtomGit开源社区

不平衡学习的自适应合成采样方法ADASYN（Matlab代码实现）

在机器学习实际应用中，类别不平衡问题广泛存在，即数据集中某一类别的样本数量远多于另一类别（多数类与少数类），这种不平衡分布会导致模型训练偏向多数类，大幅降低少数类样本的识别精度，而少数类样本往往承载着关键信息（如欺诈检测中的欺诈样本、医疗诊断中的患病样本）。自适应合成采样方法（ADASYN）作为解决类别不平衡问题的经典过采样技术，通过自适应调整少数类样本的合成策略，重点关注难以学习的少数类样本，有

avatar AtomGit开源社区