一、Hash索引
1.1 Hash索引原理
Hash索引基于哈希表实现，通过哈希函数将索引键值映射到哈希表中的桶位置。在MySQL中，只有Memory引擎原生支持Hash索引，InnoDB支持自适应Hash索引但不由用户直接创建。
核心特性：
-
键值通过哈希函数计算得到哈希值
-
哈希值指向哈希桶，桶内可能包含多个记录指针（哈希冲突时）
-
只支持等值比较（=, IN），不支持范围查询
1.2 Hash索引的优缺点
优点：
-
查询效率极高：等值查询时间复杂度为O(1)
-
无排序开销：直接计算哈希值定位，无需维护有序性
-
内存友好：适合内存表，查询速度极快
缺点：
-
不支持范围查询：无法进行>、<、BETWEEN等查询
-
不支持部分匹配：LIKE 'abc%'无法使用
-
哈希冲突问题：不同键值可能哈希到同一位置
-
不支持排序：查询结果不能按索引顺序返回
-
存储不连续：数据在磁盘上分散存储
-- Hash索引使用示例（Memory引擎）
CREATE TABLE session_data (
session_id VARCHAR(32) PRIMARY KEY,
user_id INT,
data TEXT,
INDEX idx_user USING HASH (user_id) -- 显式创建Hash索引
) ENGINE=MEMORY;
-- 等值查询能使用Hash索引
EXPLAIN SELECT * FROM session_data WHERE user_id = 1001;
-- 结果：type=ref, key=idx_user
-- 范围查询无法使用Hash索引
EXPLAIN SELECT * FROM session_data WHERE user_id > 1000;
-- 结果：type=ALL（全表扫描）
graph TD
A[查询WHERE email='alice@example.com'] --> B[哈希函数计算]
B --> C[哈希值: 0x3A7F]
C --> D[定位哈希桶#3]
D --> E[遍历桶内链表]
E --> F{比对键值}
F -- 匹配 --> G[返回行数据]
F -- 不匹配 --> H[继续遍历]
H --> E
二、聚集索引（Clustered Index）与非聚集索引（Non-Clustered Index）
2.1 聚集索引
定义： 表数据按照索引键值的顺序物理存储。InnoDB中，主键就是聚集索引，如果没有主键则选择第一个唯一非空索引，还没有则创建隐藏的ROW_ID作为聚集索引。
特点：
-
数据即索引：叶节点包含完整的行数据
-
每表只有一个：数据只能按一种物理顺序存储
-
查询效率高：主键查询只需一次索引查找
-
范围查询高效：相邻数据物理上连续存储
-- 创建有聚集索引的表
CREATE TABLE orders (
order_id INT PRIMARY KEY, -- 聚集索引
customer_id INT,
order_date DATE,
amount DECIMAL(10,2)
) ENGINE=InnoDB;
-- 聚集索引查询
EXPLAIN SELECT * FROM orders WHERE order_id = 1001;
-- 只需一次索引查找就能获取所有列数据
2.2 非聚集索引（二级索引）
定义： 索引结构与数据行分离存储，索引叶节点存储的是主键值或行指针。
特点：
-
多级查找：先查索引，再通过主键查数据（回表）
-
每表可有多个：可创建多个非聚集索引
-
包含索引列和主键：叶节点存储索引列+主键值
-- 创建非聚集索引
CREATE INDEX idx_customer ON orders(customer_id);
-- 非聚集索引查询过程
EXPLAIN SELECT * FROM orders WHERE customer_id = 2001;
-- 1. 在idx_customer索引中查找customer_id=2001
-- 2. 获取对应的order_id（主键）
-- 3. 用order_id回表查询完整数据
graph TD
subgraph A[聚集索引结构]
A1[根节点<br>键值范围]
A2[中间节点<br>键值范围]
A3[叶节点<br>完整行数据]
A1 --> A2 --> A3
end
subgraph B[非聚集索引结构]
B1[根节点<br>索引键范围]
B2[中间节点<br>索引键范围]
B3[叶节点<br>索引键+主键]
B1 --> B2 --> B3
end
B3 --> C[回表查询]
C --> A3
三、二级索引
3.1 二级索引原理
二级索引是InnoDB中非聚集索引的别称，其叶节点包含索引列值 + 主键值。
查询过程（回表）：
-
在二级索引B+树中查找索引键
-
获取对应的主键值
-
用主键值到聚集索引中查找完整行数据
-- 创建二级索引
CREATE INDEX idx_order_date ON orders(order_date);
-- 二级索引查询示例
SELECT * FROM orders
WHERE order_date = '2024-01-15'
AND customer_id = 1001;
-- 查询过程：
-- 1. 使用idx_order_date找到order_date='2024-01-15'的记录
-- 2. 获取对应的order_id（主键）
-- 3. 用order_id回表查询完整数据
-- 4. 在内存中过滤customer_id=1001
四、覆盖索引（Covering Index）
4.1 覆盖索引概念
定义： 查询需要的所有列都包含在索引中，无需回表查询。
优势：
-
避免回表：直接从索引获取数据
-
减少IO：索引通常比数据行小
-
提升性能：特别是COUNT、SUM等聚合查询
-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(customer_id, order_date, amount);
-- 覆盖索引查询示例1：所有字段都在索引中
EXPLAIN SELECT customer_id, order_date, amount
FROM orders
WHERE customer_id = 1001;
-- Extra: Using index（使用了覆盖索引）
-- 覆盖索引查询示例2：包含主键
SELECT order_id, customer_id, order_date
FROM orders
WHERE customer_id = 1001;
-- 虽然order_id不在idx_covering中，但所有二级索引都包含主键
-- 所以这也是覆盖索引查询
-- 非覆盖索引查询
EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;
-- Extra: NULL（需要回表）
五、索引下推（Index Condition Pushdown, ICP）
5.1 ICP原理
MySQL 5.6引入，将WHERE条件中索引列的过滤下推到存储引擎层执行，减少回表次数。
-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);
-- 无ICP的查询（MySQL 5.6前）
SELECT * FROM users
WHERE name LIKE '张%'
AND age > 20;
-- 存储引擎：查找所有name以'张'开头的记录
-- Server层：过滤age>20的记录
-- 回表次数：所有name以'张'开头的记录
-- 有ICP的查询（MySQL 5.6+）
SELECT * FROM users
WHERE name LIKE '张%'
AND age > 20;
-- 存储引擎：查找name以'张'开头且age>20的记录
-- 回表次数：name以'张'开头且age>20的记录
-- 显著减少回表次数！
-- 查看ICP使用
EXPLAIN SELECT * FROM users
WHERE name LIKE '张%'
AND age > 20;
-- Extra: Using index condition
六、单列索引（Single Column Index）
6.1 单列索引使用
定义： 只包含单个列的索引。
适用场景：
-
WHERE条件中频繁使用的单列
-
ORDER BY单列排序
-
具有高选择性的列（区分度高）
-- 创建单列索引
CREATE INDEX idx_customer ON orders(customer_id);
CREATE INDEX idx_order_date ON orders(order_date);
-- 单列索引使用示例
-- 场景1：等值查询
SELECT * FROM orders WHERE customer_id = 1001;
-- 使用索引：idx_customer
-- 场景2：范围查询
SELECT * FROM orders WHERE order_date >= '2024-01-01';
-- 使用索引：idx_order_date
-- 场景3：排序
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;
-- 使用索引：idx_order_date
-- 多个单列索引的局限性
SELECT * FROM orders
WHERE customer_id = 1001
AND order_date = '2024-01-15';
-- 只能使用其中一个索引（idx_customer或idx_order_date）
-- 另一个条件在Server层过滤
七、联合索引（Composite Index）
7.1 联合索引原理
定义： 包含多个列的索引，列顺序非常重要。
最左前缀原则： 查询必须从索引的最左列开始，不能跳过中间的列。
-- 创建联合索引
CREATE INDEX idx_customer_date ON orders(customer_id, order_date, status);
-- 有效使用联合索引的查询
-- 1. 使用最左列
SELECT * FROM orders WHERE customer_id = 1001; -- √
-- 2. 使用前两列
SELECT * FROM orders WHERE customer_id = 1001 AND order_date = '2024-01-15'; -- √
-- 3. 使用全部三列
SELECT * FROM orders WHERE customer_id = 1001 AND order_date = '2024-01-15' AND status = 'PAID'; -- √
-- 4. 范围查询后，后面的列无法使用索引
SELECT * FROM orders WHERE customer_id = 1001 AND order_date > '2024-01-01' AND status = 'PAID'; -- △
-- customer_id和order_date使用索引，status无法使用索引
-- 无效使用联合索引的查询
-- 1. 跳过最左列
SELECT * FROM orders WHERE order_date = '2024-01-15'; -- ×
-- 2. 跳过中间列
SELECT * FROM orders WHERE customer_id = 1001 AND status = 'PAID'; -- ×
graph LR
subgraph A[联合索引 idx_a_b_c<br>a,b,c]
A1[按a排序]
A2[相同a时按b排序]
A3[相同a,b时按c排序]
A1 --> A2 --> A3
end
subgraph B[查询条件]
B1[WHERE a=1 AND b=2 AND c=3<br>√ 完全使用索引]
B2[WHERE a=1 AND b=2<br>√ 使用前两列]
B3[WHERE a=1<br>√ 使用最左列]
B4[WHERE b=2 AND c=3<br>× 跳过最左列]
B5[WHERE a=1 AND c=3<br>× 跳过中间列]
end
7.2 联合索引设计原则
-- 原则1：区分度高的列在前
CREATE INDEX idx_good ON users(gender, name); -- 不好，gender区分度低
CREATE INDEX idx_better ON users(name, gender); -- 更好，name区分度高
-- 原则2：等值查询列在前，范围查询列在后
CREATE INDEX idx_bad ON users(age, city); -- 不好，age常范围查询
CREATE INDEX idx_good ON users(city, age); -- 更好，city等值查询
-- 原则3：经常查询的列放在前面
CREATE INDEX idx_frequent ON users(department, hire_date, salary);
-- 如果经常按department查询，放最前面
-- 原则4：考虑覆盖索引
CREATE INDEX idx_covering ON orders(customer_id, order_date, amount);
-- 如果经常查询这三个字段，创建覆盖索引避免回表
八、索引使用最佳实践
8.1 索引选择策略
-- 1. 选择性高的列建索引
SELECT
COUNT(DISTINCT gender) / COUNT(*) as gender_selectivity,
COUNT(DISTINCT email) / COUNT(*) as email_selectivity
FROM users;
-- 选择性越高，索引效果越好
-- 2. 避免过多索引
-- 每个索引增加写操作开销
-- 建议：大表不超过5-7个索引
-- 3. 使用前缀索引
CREATE INDEX idx_email_prefix ON users(email(10)); -- 对长字符串使用前缀
-- 适用于TEXT/VARCHAR等长字段
-- 4. 定期分析索引使用
-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;
-- 5. 监控索引效率
-- 查看索引使用统计
SELECT * FROM sys.schema_index_statistics;
8.2 常见索引优化场景
-- 场景1：分页查询优化
-- 原始：深度分页慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 优化：使用覆盖索引+子查询
SELECT * FROM orders o
JOIN (
SELECT id FROM orders
ORDER BY id
LIMIT 1000000, 20
) AS tmp ON o.id = tmp.id;
-- 场景2：IN和OR优化
-- 多个单列索引，使用UNION代替OR
SELECT * FROM users WHERE age = 20 OR city = '北京';
-- 优化为：
SELECT * FROM users WHERE age = 20
UNION
SELECT * FROM users WHERE city = '北京';
-- 场景3：ORDER BY优化
-- 确保ORDER BY使用索引
CREATE INDEX idx_date_status ON orders(order_date DESC, status);
SELECT * FROM orders
WHERE customer_id = 1001
ORDER BY order_date DESC, status;
通过合理使用各种索引类型，可以显著提升MySQL查询性能。实际应用中应根据查询模式、数据分布和业务需求综合设计索引策略。
九、索引的本质：空间换时间
索引（Index） 是数据库中的一种排好序的快速查找数据结构，类似于书籍的目录。它的核心原理是通过减少磁盘 I/O 次数来加速查询。
1. 核心优缺点对比
|
维度 |
优点（为什么用） |
缺点（代价与风险） |
|---|---|---|
|
性能 |
大幅提升查询速度： |
降低写入速度： |
|
资源 |
降低 CPU 消耗：减少数据排序和比较的计算量。 |
占用磁盘空间：索引本身也是文件，会占用额外存储（通常为表的 1.2-1.5 倍）。 |
|
约束 |
保证数据唯一性：唯一索引（Unique）可防止重复数据插入。 |
维护成本高：索引碎片、统计信息更新、冗余索引管理需要 DBA 介入。 |
2. 索引类型速查（MySQL）
|
索引类型 |
关键字 |
特点 |
适用场景 |
|---|---|---|---|
|
主键索引 |
|
唯一、非空，一张表只能有一个 |
自增 ID、业务主键 |
|
唯一索引 |
|
列值唯一，允许多个 NULL |
手机号、邮箱、身份证号 |
|
普通索引 |
|
最基本的索引，无约束 |
姓名、状态码等高频查询字段 |
|
复合索引 |
|
多列组合，遵循最左前缀原则 |
多条件联合查询 |
|
全文索引 |
|
对文本分词检索 |
文章内容、商品描述（现多改用 ES） |
十、实战：SQL 索引创建与使用语法
1. 创建索引的 3 种方式
方式一：建表时直接创建（推荐）
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`phone` varchar(11) DEFAULT NULL,
`age` int DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`), -- 主键索引
UNIQUE KEY `uk_phone` (`phone`), -- 唯一索引
KEY `idx_username` (`username`), -- 单列索引
KEY `idx_age_created` (`age`, `created_at`) -- 复合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
方式二：使用 CREATE INDEX（已有表）
-- 创建普通索引
CREATE INDEX idx_username ON user(username);
-- 创建复合索引（常用于多条件查询）
CREATE INDEX idx_age_created ON user(age, created_at);
方式三：使用 ALTER TABLE（功能更强）
-- 添加唯一索引
ALTER TABLE user ADD UNIQUE INDEX uk_phone(phone);
-- 添加主键索引（如果表没有主键）
ALTER TABLE user ADD PRIMARY KEY (id);
2. 查看与删除索引
-- 查看表的所有索引
SHOW INDEX FROM user;
-- 删除索引
DROP INDEX idx_username ON user;
十一、什么时候该用索引？（最佳实践）
原则：读多写少，且数据量大于 1 万行。
|
场景 |
说明 |
示例 SQL（建索引后） |
|---|---|---|
|
高频查询条件 |
|
|
|
外键关联字段 |
多表 |
|
|
排序与分组 |
|
|
|
高选择性列 |
字段值几乎不重复（如手机号） |
|
|
覆盖索引 |
查询列全在索引中，避免回表 |
|
四、什么时候不该用索引？（避坑指南）
原则：小表、写多读少、低区分度字段。
|
场景 |
原因 |
建议 |
|---|---|---|
|
小表（< 1万行） |
全表扫描比索引查找更快（无 I/O 瓶颈） |
数据量小无需优化，等表变大后再加 |
|
频繁更新的列 |
每次更新都要重构索引树，性能开销大 |
日志表、流水表慎用索引 |
|
低区分度字段 |
如性别、状态（只有几个值），索引无效 |
|
|
很少使用的列 |
只为偶尔一次的报表查询建索引是浪费 |
使用 |
|
长文本字段 |
如 |
改用前缀索引或全文检索引擎（如 ES） |
十一、高级技巧：复合索引与最左前缀原则
复合索引（联合索引） 是面试高频考点，必须掌握最左前缀原则。
1. 创建复合索引示例
-- 创建一个 (country, city, age) 的复合索引
CREATE INDEX idx_country_city_age ON user(country, city, age);
2. 最左前缀原则：哪些 SQL 会走索引？
|
查询条件 |
是否走索引 |
说明 |
|---|---|---|
|
|
✅ 全索引生效 |
从左到右连续匹配 |
|
|
✅ 部分生效 |
只用到了第一列 |
|
|
⚠️ 部分生效 |
跳过了 |
|
|
❌ 失效 |
缺少最左列 |
|
|
❌ 失效 |
缺少最左列，全表扫描 |
结论：建立复合索引时，将最常用、区分度最高的列放在最左边。
十二、常见索引失效场景与优化（附 SQL 对比）
即使建了索引，写法不当也会导致索引失效，退化为全表扫描。
|
失效场景 |
错误写法（❌ 失效） |
正确写法（✅ 优化） |
|---|---|---|
|
在索引列上使用函数 |
|
|
|
隐式类型转换 |
|
|
|
使用 |
|
无法优化，尽量避免对索引列使用否定条件 |
|
|
|
改为 |
|
|
|
|
十三、总结：索引设计黄金法则
-
不要过度索引：索引不是越多越好，一般单表建议 3-5 个。
-
优先考虑复合索引：将多个单列索引合并为覆盖查询需求的复合索引。
-
使用
EXPLAIN验证：执行EXPLAIN SELECT ...查看type是否为ref/range，确认索引生效。 -
定期维护：使用
ANALYZE TABLE更新统计信息，定期清理未使用的索引。
版权声明：本文为 优快云 博主原创，转载请注明出处。如有疑问，欢迎评论区交流。
1591
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
