MySQL的索引类型

我来详细讲解MySQL的索引类型这是面试高频考点也是SQL优化的核心基础。一、按数据结构分类1. BTree索引InnoDB默认结构特点 [10 | 30 | 50] / | \ \ [3|7|9] [20|25] [40|45] [60|70|80] / | \ / \ / \ / | \ 叶子节点双向链表存储完整数据行聚簇索引或主键二级索引 关键特性 - 非叶子节点只存键值不存数据树更矮IO更少 - 叶子节点有序且双向链接范围查询高效 - 聚簇索引的叶子节点就是数据页适用场景全值匹配、范围查询、排序、分组、前缀匹配最左前缀2. Hash索引-- Memory引擎支持显式Hash索引 CREATE TABLE t ( id INT PRIMARY KEY, name VARCHAR(20), INDEX USING HASH(name) -- 显式创建 ) ENGINEMEMORY; -- InnoDB自适应Hash索引AHI自动维护不可手动创建 SHOW VARIABLES LIKE innodb_adaptive_hash_index; -- 默认ON特点等值查询O(1)无法范围查询不支持排序、最左前缀、模糊查询哈希冲突时用链表解决3. Full-Text全文索引-- 5.6 InnoDB支持之前只有MyISAM支持 CREATE TABLE articles ( id INT PRIMARY KEY, title VARCHAR(200), content TEXT, FULLTEXT INDEX idx_content(content) WITH PARSER ngram -- 中文需ngram解析器 ); -- 使用 SELECT * FROM articles WHERE MATCH(content) AGAINST(数据库优化 IN NATURAL LANGUAGE MODE);注意中文分词需配置ngram_token_size2默认2字节4. R-Tree空间索引-- 5.7 InnoDB支持用于GIS地理数据 CREATE TABLE locations ( id INT PRIMARY KEY, name VARCHAR(50), loc POINT NOT NULL, SPATIAL INDEX idx_loc(loc) ); SELECT * FROM locations WHERE MBRContains(GeomFromText(Polygon(...)), loc);二、按物理存储分类InnoDB核心1. 聚簇索引Clustered Index-- 主键就是聚簇索引数据行物理上按主键顺序存储 CREATE TABLE t ( id INT PRIMARY KEY, -- 聚簇索引 name VARCHAR(20), INDEX idx_name(name) -- 二级索引 );特性叶子节点存储完整行数据表数据本身就是索引无需额外存储只能有一个数据物理排序唯一如果没有主键选择第一个非空唯一索引 → 否则隐式创建6字节row_id2. 二级索引Secondary Index / 非聚簇索引结构 非叶子节点索引列值 叶子节点索引列值 主键值回表用 查询流程 二级索引查到主键 → 回表查聚簇索引获取完整数据覆盖索引优化如果二级索引包含所有查询列无需回表-- idx_name_age包含(name, age)两列 SELECT age FROM t WHERE name 张三; -- 覆盖索引Extra: Using index三、按功能/特性分类1. 主键索引 vs 普通索引CREATE TABLE t ( id INT PRIMARY KEY, -- 主键索引聚簇 code VARCHAR(20) UNIQUE, -- 唯一索引二级索引但约束唯一 name VARCHAR(20), INDEX idx_name(name) -- 普通索引二级索引 );类型约束数量存储主键非空 唯一1个聚簇存数据唯一唯一可空多个二级存主键普通无多个二级存主键2. 单列索引 vs 联合索引复合索引-- 单列索引 INDEX idx_name(name) -- 联合索引最左前缀原则 INDEX idx_name_age(name, age) -- 先按name排序name相同再按age排序 -- 有效查询 WHERE name 张三; -- ✓ 使用第1列 WHERE name 张三 AND age 20; -- ✓ 使用两列 WHERE name LIKE 张%; -- ✓ 前缀匹配 -- 无效查询违反最左前缀 WHERE age 20; -- ✗ 跳过第1列 WHERE name LIKE %三; -- ✗ 前导模糊索引下推ICPIndex Condition Pushdown 5.6优化在存储引擎层过滤数据减少回表-- 索引(name, age)查询 SELECT * FROM t WHERE name LIKE 张% AND age 20; -- 无ICP引擎层返回所有name张%的记录Server层过滤age -- 有ICP引擎层直接过滤name和age减少回表次数3. 前缀索引节省空间-- 对长字符串取前缀建索引 CREATE TABLE t ( email VARCHAR(255), INDEX idx_email(email(6)) -- 只索引前6个字符 ); -- 计算合适前缀长度区分度接近1 SELECT COUNT(DISTINCT LEFT(email, 6)) / COUNT(DISTINCT email) as selectivity FROM t;缺点无法用于ORDER BY、GROUP BY、覆盖索引4. 函数/表达式索引5.7-- 直接对表达式建索引 CREATE TABLE t ( id INT PRIMARY KEY, json_data JSON, INDEX idx_name((CAST(json_data-$.name AS CHAR(20)))) ); -- 或虚拟列方式推荐更直观 CREATE TABLE t ( id INT PRIMARY KEY, json_data JSON, name VARCHAR(20) AS (json_data-$.name) VIRTUAL, INDEX idx_name(name) );5. 降序索引8.0-- 8.0之前DESC会被忽略实际还是升序 -- 8.0真正支持降序索引 CREATE TABLE t ( a INT, b INT, INDEX idx_ab(a ASC, b DESC) -- a升序b降序 ); -- 优化混合排序 ORDER BY a ASC, b DESC; -- 可直接使用索引无需filesort6. 不可见索引8.0CREATE INDEX idx_test ON t(name) INVISIBLE; -- 优化器不可见用于测试 -- 软删除验证后再真正DROP ALTER TABLE t ALTER INDEX idx_test VISIBLE;7. 倒排索引8.0.13InnoDB全文索引-- 自然语言处理优化 CREATE FULLTEXT INDEX idx_content ON articles(content) WITH PARSER ngram; -- ngram分词支持中文四、索引选择决策树是否需要索引 │ ├─ 数据量小1000行→ 全表扫描更快无需索引 │ └─ 数据量大 → 继续判断 │ ├─ 等值查询为主 → Hash索引Memory或BTree │ ├─ 范围查询/排序/分组 → BTree │ ├─ 文本搜索 → 全文索引ngram解析器 │ ├─ 地理坐标 → R-Tree空间索引 │ └─ 多条件组合 → 联合索引最左前缀匹配五、高频面试追问Q为什么InnoDB用BTree不用BTree或Hash结构缺点BTree优势Hash无法范围查询不支持排序BTree叶子节点有序天然支持范围BTree非叶子节点存数据树更高BTree非叶子节点只存键更矮胖IO少红黑树高度不可控最坏O(n)BTree高度固定通常3-4层稳定O(logN)Q聚簇索引和非聚簇索引查询区别-- 聚簇索引主键查询 SELECT * FROM t WHERE id 1; -- 直接查聚簇索引1次IO如果不在Buffer Pool -- 非聚簇索引二级索引查询 SELECT * FROM t WHERE name 张三; -- 1. 查二级索引找到主键值 -- 2. 回表查聚簇索引获取完整数据 -- 2次IO回表代价Q什么是索引覆盖有什么好处-- 索引idx_name_age(name, age) SELECT age FROM t WHERE name 张三; -- 二级索引叶子节点有(name, age, 主键)无需回表查聚簇索引 好处 1. 减少回表IO 2. 减少随机读聚簇索引是随机IO 3. 避免访问聚簇索引的锁竞争Q联合索引(A,B,C)的生效情况WHERE A1 AND B2 AND C3 -- ✓ 全列使用 WHERE A1 AND B2 -- ✓ 使用A,B WHERE A1 -- ✓ 使用A WHERE B2 AND C3 -- ✗ 违反最左前缀无A WHERE A1 AND C3 -- ✓ 使用AC无法使用索引但ICP可能优化 WHERE A1 AND B2 AND C3 -- ✓ 使用A,BB是范围C无法使用 ORDER BY A,B -- ✓ 使用索引排序 ORDER BY B,A -- ✗ 排序顺序与索引不一致Q索引失效的常见场景-- 1. 函数操作 WHERE YEAR(create_time) 2024 -- ✗ 函数破坏索引 WHERE create_time BETWEEN 2024-01-01 AND 2024-12-31 -- ✓ 范围查询 -- 2. 隐式类型转换 WHERE phone 13800138000 -- ✗ phone是字符串转数字 WHERE phone 13800138000 -- ✓ -- 3. 前导模糊查询 WHERE name LIKE %三 -- ✗ WHERE name LIKE 张% -- ✓ -- 4. OR条件部分情况 WHERE id 1 OR age 20 -- ✗ id有索引age无索引可能全表扫描 -- 优化UNION ALL 或 分别查询后合并 -- 5. 不等于/NOT IN数据量大时 WHERE status ! 0 -- 可能全表扫描取决于数据分布 -- 6. 索引列参与计算 WHERE id 1 100 -- ✗ WHERE id 99 -- ✓掌握索引类型的特性和适用场景是写出高性能SQL、进行索引优化的基础。建议结合EXPLAIN分析实际执行计划验证索引使用效果。