Skip to main content
索引是帮助 MySQL 高效获取数据的有序数据结构。

​
索引结构

​
为什么用 B+Tree？

数据结构问题
二叉树顺序插入退化为链表
红黑树本质还是二叉树，层级深
B-Tree非叶子节点也存数据，单节点容量小
Hash不支持范围查询、排序
B+Tree 优势：
  • 非叶子节点只存索引，单节点可存更多 key
  • 叶子节点形成有序链表，支持范围查询
  • 层级低（3 层可存 2000W+ 数据）

​
B+Tree 结构

                    [15|30|45]              <- 根节点（只存索引）
                   /    |    \
        [5|10|15]    [20|25|30]   [35|40|45]   <- 分支节点
           |            |            |
    [1→3→5→7→10→12→15]→[17→20→23→25→28→30]→[32→35→38→40→42→45]
                                              <- 叶子节点（存数据，有序链表）

​
索引分类

​
按数据结构

类型说明支持引擎
B+Tree最常用，支持范围查询InnoDB, MyISAM
Hash精确匹配快，不支持范围Memory
Full-text全文索引InnoDB 5.6+, MyISAM

​
按逻辑分类

类型说明关键字
主键索引唯一 + 非空PRIMARY KEY
唯一索引值唯一，可以有 NULLUNIQUE
普通索引无限制INDEX
全文索引文本搜索FULLTEXT

​
InnoDB 索引分类

类型说明特点
聚簇索引主键索引，叶子节点存完整数据有且仅有一个
二级索引非主键索引，叶子节点存主键值可以有多个
聚簇索引选取规则：
  1. 有主键 → 主键索引
  2. 无主键 → 第一个 UNIQUE 非空索引
  3. 都没有 → 生成隐藏的 rowid

​
索引操作

-- 创建索引
CREATE INDEX idx_name ON user(name);
CREATE UNIQUE INDEX idx_email ON user(email);
CREATE INDEX idx_name_age ON user(name, age);  -- 联合索引

-- 查看索引
SHOW INDEX FROM user;

-- 删除索引
DROP INDEX idx_name ON user;

-- 建表时创建索引
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_name(name),
    UNIQUE INDEX idx_email(email)
);

​
回表与覆盖索引

​
回表查询

-- 假设 name 有索引
SELECT * FROM user WHERE name = '张三';

-- 执行过程：
-- 1. 在 name 的二级索引中找到 '张三' 对应的主键 id
-- 2. 根据 id 在聚簇索引中查找完整数据
-- 3. 这个过程叫"回表"

​
覆盖索引

-- 假设有联合索引 (name, age)
SELECT name, age FROM user WHERE name = '张三';

-- 执行过程：
-- 1. 在联合索引中找到 '张三'
-- 2. 索引中已包含 name 和 age，直接返回
-- 3. 无需回表，这叫"覆盖索引"
优化原则：尽量使用覆盖索引，减少 SELECT *

​
索引失效场景

场景示例原因
最左前缀不匹配idx(a,b,c) 只查 b联合索引从左匹配
索引列运算WHERE YEAR(date) = 2024函数破坏索引
类型隐式转换WHERE phone = 123（phone是字符串）类型不匹配
左模糊查询WHERE name LIKE ‘%张’无法利用索引
OR 条件无索引WHERE a=1 OR b=2（b无索引）全表扫描更快
范围查询右侧失效idx(a,b) WHERE a>1 AND b=2b 的索引失效

​
最左前缀法则

联合索引 idx(a, b, c)：
查询条件是否走索引使用的索引列
a = 1✅a
a = 1 AND b = 2✅a, b
a = 1 AND b = 2 AND c = 3✅a, b, c
b = 2❌无
a = 1 AND c = 3✅a（c失效）
a > 1 AND b = 2✅a（b失效）

​
索引设计原则

  1. 频繁查询的字段建索引
  2. 区分度高的字段优先（如手机号 > 性别）
  3. 字符串长字段用前缀索引
  4. 联合索引优于多个单列索引
  5. 索引不是越多越好，影响写入性能
  6. 避免在索引列上做运算或函数

​
前缀索引

-- 对 email 前 10 个字符建索引
CREATE INDEX idx_email ON user(email(10));

-- 选择合适的前缀长度（区分度接近原始字段）
SELECT 
    COUNT(DISTINCT email) / COUNT(*) AS full_selectivity,
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS prefix5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix10
FROM user;

​
SQL 提示

-- 建议使用索引
SELECT * FROM user USE INDEX(idx_name) WHERE name = '张三';

-- 忽略索引
SELECT * FROM user IGNORE INDEX(idx_name) WHERE name = '张三';

-- 强制使用索引
SELECT * FROM user FORCE INDEX(idx_name) WHERE name = '张三';

​
索引常见问题

​
Q1: 为什么主键建议自增？

自增主键顺序插入，性能好。非自增主键可能导致页分裂，影响性能。

​
Q2: 为什么推荐使用整型主键？

  • 整型比较快
  • 占用空间小（二级索引存主键）
  • UUID 36 字节，无序，性能差

​
Q3: 什么时候不建索引？

  • 数据量小（< 几千条）
  • 频繁更新的字段
  • 区分度低的字段（如性别）