一.核心总结

MySQL索引不保证在所有场景下都有效，需结合数据分布、查询条件、索引设计综合判断，通过执行计划分析+数据验证才能确定有效性。对于一些小表，MySQL可能选择全表扫描而非使用索引，因为全表扫描的开销可能更小。最终是否用上索引是根据 MySQL 成本计算决定的，评估 CPU 和 I/O 成本最终选择用辅助索引还是全表扫描。

二.理论分析

1.常见索引失效的场景

普通场景
1. 不满足最佳左前缀法则
  - 联合索引 (a,b,c)，查询未从最左字段 a 开始，或中间跳过了 b。
  - 示例：where c=1(跳过a，b)；
  - 优化：尽量以where频繁的列作为最左列的查询条件，保证部分命中。
2. 隐式类型转换
  - 字段类型与查询值不一致
  - 示例：where a（int）='123';
  - 优化：统一类型
3. 函数/计算操作
  - 索引列使用了函数或者计算，索引参与了函数或计算处理，会导致去全表扫描。
  - 示例： where a =a+1 /where LOWER(name) like 'cong%'
  - 优化：改写为范围查询等
4. OR连接非索引字段
  - 普通索引 (a) 使用or条件查询c
  - 示例： where a =1 or c=2
  - 优化：拆分查询或为字段添加索引
5. LIKE通配符开头
  - 模糊查询以%开头。因为索引是从左到右来进行排序查找的，占位符直接放在了最左边开头，可能会导致直接全表扫描，这种情况就会导致索引失效。
  - 示例： name like '% 123'
  - 优化：使用LIKE '123%'或全文索引
6. 数据区分度过低
  - 索引列重复值过多比如性别男/女
  - 优化：结合其他字段建联合索引
7. 范围查询阻断索引
  - 联合索引中范围查询后的列失效
  - 示例：WHERE a>1 AND b=2， b失效
  - 优化：调整索引顺序（如b,a）
8. ORDER BY顺序不匹配
  - 排序字段与索引顺序不一致
  - 示例：INDEX(a,b)，ORDER BY b,a
  - 优化：调整索引或排序顺序
复杂场景
1. 大表驱动小表
  - 驱动表（外层循环）：联表时首先访问的表，其数据量影响扫描次数。大表做驱动表（性能差）
  - 示例： SELECT * FROM big_table JOIN small_table
  - 优化：优先选择数据量小且WHERE条件过滤性强的表作为驱动表。
2. 字符集/排序规则不一致
  - 联表字段编码不一致
  - 示例： JOIN ON utf8_col = latin1_col
  - 优化：统一字符集
3. 临时表无索引
  - 子查询或派生表未利用索引
  - 示例：SELECT * FROM (SELECT ...) tmp
  - 优化：减少子查询，使用JOIN优化

2.执行计划分析

使用EXPLAIN 命令，通过在查询前加上EXPLAIN，可以查看 MySQL 选择的执行计划，了解是否使用了索引、使用了哪个索引、估算的行数等信息。

EXPLAIN重要字段

type 访问类型（由高到低）
- system ：系统表仅有一行
- const ：常量匹配，表最多有一个匹配行，用于主键和唯一索引
- eq_ref：表关联查询时，对于前表的每一行,后表只有一行与之匹配，用于主键和唯一索引
- ref：只使用了索引的最左前缀或者使用的索引是非唯一索引、非主键索引
- range： 范围查询（阿里手册要求达到的级别）
- index：全索引扫描
- All： 全表扫描
key 表示使用的索引名称
row 索引匹配行数
extra 额外信息
- Using filesort（尽量避免）排序未使用到索引
- Using where 没有使用索引
- Using index 使用了覆盖索引
- Using index condition 索引下推优化

三.实践验证

步骤1：创建测试表与数据

▼
plsql
复制代码
-- 创建测试表（含联合索引）
CREATE TABLE `user` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(50),
  `age` INT,
  `city` VARCHAR(50),
  `created_at` DATETIME,
  INDEX `idx_age_city` (`age`, `city`),
  INDEX `idx_name` (`name`)
) ENGINE=InnoDB;

-- 插入10万条测试数据（使用存储过程或工具生成）
DELIMITER $$
CREATE PROCEDURE insert_users()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 100000 DO
    INSERT INTO `user` (`name`, `age`, `city`, `created_at`)
    VALUES (
      CONCAT('User', i),
      FLOOR(RAND() * 100),
      CASE WHEN RAND() < 0.5 THEN 'Beijing' ELSE 'Shanghai' END,
      NOW() - INTERVAL FLOOR(RAND() * 365) DAY
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

CALL insert_users();

步骤2：执行查询并分析索引使用

▼
plsql
复制代码
-- 场景1：符合最左前缀原则（使用索引）
EXPLAIN SELECT * FROM `user` WHERE age=25 AND city='Beijing';
-- 结果：type=ref, key=idx_age_city

-- 场景2：跳过最左字段（索引失效）
EXPLAIN SELECT * FROM `user` WHERE city='Beijing';
-- 结果：type=ALL（全表扫描）

-- 场景3：对索引列使用函数（索引失效）
EXPLAIN SELECT * FROM `user` WHERE YEAR(created_at)=2023;
-- 结果：type=ALL

-- 场景4：OR连接非索引字段（索引失效）
EXPLAIN SELECT * FROM `user` WHERE age=25 OR name='User100';
-- 结果：type=ALL（若name无索引）

-- 场景5：LIKE以通配符开头（索引失效）
EXPLAIN SELECT * FROM `user` WHERE name LIKE '%User%';
-- 结果：type=ALL

四.口语回答

索引不一定有效，需要根据查询条件、索引设计来推断，具体需要explain+sql语句来判断索引是否生效。针对于某些小表，mysql可能会选择全表扫描，因为全表扫描开销更小。最终是否用上索引是根据 MySQL 成本计算决定的，评估 CPU 和 I/O 成本最终选择用辅助索引还是全表扫描。当然索引也需要合理设计，在某些情况下会导致失效，比如违背了最佳左前缀法则，举例说明（...）；使用了函数或计算，or右边的列没有使用索引，范围查询右边使用索引也会失效，隐式类型转换（举例说明...）,like右边使用模糊查询'%'等；遇见索引失效时或者明细感觉查询速度变慢时（查看慢查询日志）可以通过explain来查看执行计划，重点关注type，key，extra这三个字段，然后针对性的进行优化。

训练营4：MySQL 中使用索引一定有效吗？如何排查索引效果？