MySQL 数据库中的索引是提高数据库查询性能的重要工具。通过在表的一个或多个列上创建索引，可以加快数据检索的速度，但同时也会增加插入、删除和更新数据时的开销。下面是一些基本的索引概念和操作详解：

什么是索引？

索引（Index） 是对表中一列或多列的值进行排序的一种结构，用于快速定位数据，类似于书籍的目录。

• 目的：减少磁盘 I/O，避免全表扫描（type=ALL）。
• 本质：一种 空间换时间 的数据结构（通常是 B+ 树）。
• 副作用：增加写操作（INSERT/UPDATE/DELETE）开销，占用存储空间。

1. 索引类型

1.1 B-Tree 索引

• 最常见的索引类型，适用于全键值、键值范围或键值排序的查询。
• 适用于 =, >, <, >=, <= 查询，以及 LIKE 查询（其中模式字符串的开头是固定的）。

1.2 Hash 索引

• 基于哈希表的索引，适用于等值比较查询。
• 查询效率非常高，但不支持范围查询。
• 主要用于 MEMORY 存储引擎。

1.3 Fulltext 索引

• 用于全文搜索，可以快速查找文本中的关键字。
• 仅支持 CHAR、VARCHAR 或 TEXT 列。
• 主要用于 InnoDB 和 MyISAM 存储引擎。

1.4 R-Tree 索引

• 用于空间数据类型，例如地理空间数据。
• 主要用于地理空间数据类型，如 POINT、LINESTRING、POLYGON 等。

2. 创建索引

使用 CREATE INDEX 语句

CREATE INDEX index_name ON table_name (column1, column2, ...);

使用 ALTER TABLE 语句

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

3. 查看索引

使用 SHOW INDEX 语句

SHOW INDEX FROM table_name;

4. 删除索引

DROP INDEX index_name ON table_name;

或者使用：

ALTER TABLE table_name DROP INDEX index_name;

---

二、索引分类（MySQL InnoDB）

类型	说明	特点
主键索引（Primary Key）	唯一、非空，自动创建聚簇索引	数据按主键物理存储
唯一索引（Unique Index）	值唯一（可为 NULL）	防止重复数据
普通索引（Normal Index）	最基本的索引	加速查询
组合索引（Composite Index）	多列联合索引	遵循最左前缀原则
全文索引（Full-Text）	支持文本关键词搜索	适用于 MATCH ... AGAINST
前缀索引	对字符串列的前 N 个字符建索引	节省空间，但可能降低区分度

📌 InnoDB 中，主键索引 = 聚簇索引（Clustered Index），其他都是二级索引（Secondary Index），需回表查询。

---

三、索引设计黄金法则

✅ 应该建索引的情况：

1. WHERE 条件频繁出现的字段
2. JOIN 关联字段（尤其是外键）
3. ORDER BY / GROUP BY 涉及的列
4. 高选择性字段（如用户ID、手机号），避免在低区分度字段（如性别、状态）单独建索引
5. 组合索引遵循“高频优先、区分度高靠前”

❌ 不应建索引的情况：

• 表数据量极小（< 1万行）
• 字段频繁更新（DML 成本高）
• 查询结果占全表比例 > 20%（优化器可能放弃索引）
• 已有 (a,b) 索引，不要再单独建 (a)（冗余）

---

四、如何分析索引是否被使用？

1. 使用 EXPLAIN 查看执行计划

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 AND order_date >= '2025-01-01';

关键字段解读：

字段	含义	期望值
type	访问类型	ref, range 优于 index, ALL
key	实际使用的索引	非 NULL
rows	扫描行数	越小越好
Extra	额外信息	避免 Using filesort, Using temporary

✅ 理想情况：type=ref 或 range，key=idx_xxx，rows 远小于总行数。

---

2. 检查索引是否失效（常见原因）

场景	是否失效	示例
左模糊查询	✅ 失效	LIKE '%abc'
索引列参与运算	✅ 失效	WHERE price + 10 > 100
索引列使用函数	✅ 失效	WHERE YEAR(create_time) = 2025
OR 条件部分无索引	✅ 失效	WHERE a=1 OR b=2（b 无索引）
复合索引未用最左列	✅ 失效	索引 (a,b)，查询 WHERE b=2
隐式类型转换	✅ 失效	字符串字段用数字查询：WHERE phone = 138...

---

五、索引命中率详解

索引命中率 = 索引被有效使用的程度，反映索引设计是否合理。

🔹 1. 单条 SQL 层面（开发侧）

通过 EXPLAIN 判断是否走索引（见上文）。

🔹 2. 全局统计层面（DBA 侧）

（1）InnoDB 缓冲池命中率（影响索引内存访问）

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

计算公式：

Buffer Pool Hit Rate = 
  Innodb_buffer_pool_read_requests / 
  (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads)

✅ 目标：> 99%，否则需增大 innodb_buffer_pool_size。

（2） MySQL 8.0+：索引使用统计（推荐！）

-- MySQL 8.0+
SELECT 
  TABLE_NAME,
  INDEX_NAME,
  PLAN_HIT,        -- 优化器选择该索引的次数
  EXECUTION_HIT,   -- 实际执行时使用次数
  ROWS_READ,       -- 通过该索引读取的总行数
  ROWS_READ / EXECUTION_HIT AS avg_rows_per_use  -- 平均每次扫描行数
FROM INFORMATION_SCHEMA.POLAR_INDEX_STATISTICS
WHERE TABLE_SCHEMA = 'your_db'
ORDER BY EXECUTION_HIT DESC;

• 高 PLAN_HIT + 低 EXECUTION_HIT → 索引被选中但未实际高效使用（可能因数据分布变化）
• avg_rows_per_use 很大 → 索引选择性差，考虑优化或删除

⚠️ 注意：该视图数据不持久化，重启清零；高并发下有微小误差。

（3）识别未使用索引（清理冗余）

-- MySQL 5.6+（需开启 performance_schema）
SELECT 
  object_schema AS table_schema,
  object_name AS table_name,
  index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
ORDER BY table_schema, table_name;

然后可安全删除这些“僵尸索引”。

---

六、提升索引命中率的实战策略

策略	操作
增大缓冲池	SET GLOBAL innodb_buffer_pool_size = 8G;（设为内存 50%~70%）
更新统计信息	ANALYZE TABLE your_table;（尤其在大批量更新后）
启用索引下推（ICP）	默认开启，检查：SHOW VARIABLES LIKE 'optimizer_switch';
设计覆盖索引	将 SELECT 字段也加入索引，避免回表： INDEX(customer_id, order_date, amount)
控制单表索引数	建议 ≤ 5 个，避免写性能下降
定期清理无用索引	基于 performance_schema 或业务日志分析

---

七、总结：索引优化 Checklist

•  WHERE / JOIN / ORDER BY 字段是否建了合适索引？
•  组合索引是否符合最左前缀？
•  是否存在隐式转换或函数导致索引失效？
•  EXPLAIN 显示是否走了预期索引？
•  是否有未使用的冗余索引？
•  缓冲池是否足够大？统计信息是否最新？
•  高频查询是否可通过覆盖索引避免回表？

补充章节：多列索引（Composite Index）详解

多列索引（Composite Index），也称联合索引或组合索引，是指在一个索引中包含两个或多个列。它是数据库优化中最常用也最容易被误用的索引类型之一。

一、为什么需要多列索引？

单列索引在面对多条件查询时往往效率低下。例如：

SELECT * FROM orders 
WHERE customer_id = 100 AND order_date >= '2025-01-01' AND status = 'paid';

若分别对 customer_id、order_date、status 建三个单列索引，MySQL 通常只能使用其中一个。而一个合理的多列索引可一次性高效过滤所有条件。

---

二、多列索引的底层结构（B+ 树）

以 (customer_id, order_date, status) 为例：

• 数据首先按 customer_id 排序；
• 相同 customer_id 内，再按 order_date 排序；
• 相同前两列下，再按 status 排序。

因此，该索引天然支持以下查询：

• WHERE customer_id = ?
• WHERE customer_id = ? AND order_date = ?
• WHERE customer_id = ? AND order_date >= ? AND status = ?

但不支持：

• WHERE order_date = ?（跳过第一列）
• WHERE status = ?（跳过前两列）

---

三、最左前缀原则（Leftmost Prefix Rule）

这是使用多列索引的核心规则：

查询条件必须从索引的最左列开始，并且连续使用，才能命中索引。

✅ 能命中索引的查询（假设索引为 (A, B, C)）：

查询条件	是否命中
WHERE A = 1	✅
WHERE A = 1 AND B = 2	✅
WHERE A = 1 AND B = 2 AND C = 3	✅
WHERE A = 1 AND C = 3	⚠️ 部分命中（只用到 A，C 无法用索引）
WHERE A > 1 AND B = 2	⚠️ 只能用到 A（范围查询后列失效）

❌ 无法命中索引的查询：

查询条件	原因
WHERE B = 2	跳过最左列 A
WHERE C = 3	跳过 A、B
WHERE B = 2 AND C = 3	未包含最左列

💡 注意：ORDER BY 和 GROUP BY 同样受最左前缀约束。

---

四、多列索引中的“范围查询”陷阱

一旦在多列索引中遇到范围条件（>、<、BETWEEN、LIKE 'abc%'），其后的列将无法使用索引。

示例：

索引：(customer_id, order_date)

-- ✅ 两列都可用索引
SELECT * FROM orders 
WHERE customer_id = 100 AND order_date = '2025-01-01';

-- ⚠️ 只有 customer_id 能用索引，order_date 不能（因为 = 后是范围）
SELECT * FROM orders 
WHERE customer_id = 100 AND order_date > '2025-01-01';

-- ❌ 完全无法使用该索引
SELECT * FROM orders 
WHERE order_date > '2025-01-01';

📌 设计建议：将等值查询列放在前，范围查询列放在后。

---

五、多列索引 vs 多个单列索引

对比项	多列索引	多个单列索引
存储空间	更小（一个索引结构）	更大（多个独立索引）
查询效率	高（一次定位）	低（可能 Index Merge，或只用一个）
维护成本	低（写操作只需更新一个索引）	高（每列都要更新）
灵活性	依赖最左前缀	每列独立可用

✅ 结论：对于固定组合的查询条件，优先使用多列索引。

---

六、多列索引设计最佳实践

1. 列顺序至关重要

• 高频等值查询列放前面（如 user_id, tenant_id）
• 区分度高的列靠前（如手机号 > 性别）
• 范围查询列放最后（如时间、金额）

示例：
查询模式：WHERE shop_id = ? AND status = ? AND create_time >= ?
推荐索引：(shop_id, status, create_time)

2. 覆盖索引（Covering Index）

将 SELECT 中的字段也加入索引，避免回表：

-- 查询
SELECT customer_id, order_date, amount 
FROM orders 
WHERE shop_id = 100 AND status = 'paid';

-- 覆盖索引（无需回表）
CREATE INDEX idx_cover ON orders (shop_id, status, customer_id, order_date, amount);

✅ 优点：Extra: Using index，性能极佳。

3. 避免过度设计

• 不要盲目把所有查询字段塞进一个索引。
• 一般建议多列索引不超过 4~5 列。
• 定期通过 performance_schema 或慢查询日志验证实际使用情况。

---

七、EXPLAIN 验证多列索引效果

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 100 AND order_date > '2025-01-01';

关注：

• key：是否使用了预期的多列索引？
• key_len：使用的索引长度是否合理？（可反推用了几列）
• rows：扫描行数是否显著减少？

🔍 key_len 计算示例：
若 customer_id 是 INT(4字节)，order_date 是 DATE(3字节)，
当查询 WHERE customer_id = 100 时，key_len ≈ 4；
若 WHERE customer_id = 100 AND order_date = '...'，则 key_len ≈ 4 + 3 = 7。

---

八、常见误区

误区	正确理解
“建了 (A,B,C) 就等于有了 (A)、(A,B)、(A,B,C) 三个索引”	✅ 正确！ 这正是最左前缀的优势
“只要查询包含索引中的任意列就能用索引”	❌ 错！必须从最左列开始连续使用
“ORDER BY B, A 能用 (A,B) 索引”	❌ 不能！排序顺序必须与索引一致或逆序（仅部分引擎支持逆序）
“多列索引越多越好”	❌ 写性能下降，存储膨胀，优化器选择困难

---

总结：多列索引使用口诀

等值在前，范围在后；
高频靠左，区分度高；
最左连续，方可生效；
覆盖查询，避免回表。

结合 EXPLAIN 分析 + 实际业务查询模式，才能设计出高效、精简的多列索引。