【MySQL】索引分类、聚簇与非聚簇索引，索引优化，常见explain分析索引案例，type字段
字段重要值说明优化关注点typeALL（全表扫描）、index（全索引扫描）出现ALL需重点关注可能使用的索引检查是否存在冗余索引key实际使用的索引确认是否使用最优索引rows预估扫描行数数值越大查询效率越低Extra出现这些值说明需要优化通过定期分析EXPLAIN验证索引是否按预期工作发现全表扫描等高成本操作识别冗余索引优化存储空间检查联合索引的有效性type。
·
索引基本概念
索引是数据库中用于加速数据检索的数据结构，类似于书籍的目录。它通过建立额外的数据结构来存储部分数据，从而加快查询速度。
索引的优缺点
| 优点 | 缺点 |
|---|---|
| 加快数据检索速度 | 占用额外存储空间 |
| 保证数据唯一性（唯一索引） | 插入、更新、删除时需要维护索引，增加性能开销 |
| 加速表与表之间的连接操作 | |
| 减少分组和排序的时间 |
MySQL索引类型分类
按存储结构划分：
- B+Tree索引：最常用的索引类型，支持范围查找。
- Hash索引：仅支持等值匹配，不支持范围查找。
- FULLTEXT全文索引：用于全文搜索（如搜索文章内容）。
- R-Tree索引：主要用于GIS空间数据。
R-Tree 是一种用于索引空间对象（如矩形、坐标点、多边形）的数据结构，能高效支持“交叉”“包含”等复杂空间查询。典型应用如：查找一个区域内的所有地理坐标点。
按应用层次划分：
- 普通索引：最基本的索引类型，无唯一性要求。
- 唯一索引：值必须唯一，允许NULL。但是查询时无法对null进行索引
- 联合索引：组合多个列建立的索引。
- 聚簇索引（Clustered Index）：索引和数据存储在一起（InnoDB主键）。
- 非聚簇索引：索引与数据分离，需回表。
索引底层实现
MySQL主要使用 B+树，原因如下：
| 比较对象 | 特点 | 缺点 |
|---|---|---|
| B+Tree | 非叶子节点仅存储键值，叶子节点通过链表连接，支持范围查询，I/O次数少 | 无法快速退化成链结构（对磁盘友好） |
| B树 | 所有节点存储数据，内存利用率低 | 树高更高，磁盘访问次数更多 |
| AVL/红黑树/BST | 平衡性好，适合内存结构 | 数据量大时树高过大，不适合磁盘访问 |
聚簇索引 vs 非聚簇索引
| 类型 | 数据与索引关系 | 特点 |
|---|---|---|
| 聚簇索引 | 索引与数据在一起 | InnoDB的主键索引；查询快，但插入顺序影响性能 |
| 非聚簇索引 | 索引存储的是主键指针 | 查询需要回表（通过非聚簇查到主键，再通过主键再查一次） |
一个表只能有一个聚簇索引，一般是主键。
联合索引与最左前缀原则
联合索引：多个列组成的索引。
最左前缀原则：只有从最左开始连续使用索引字段时，索引才生效。
示例：索引为 (a, b, c)
| 查询条件 | 是否使用索引 |
|---|---|
| WHERE a=1 AND b=2 AND c=3 | ✅ |
| WHERE a=1 AND b=2 | ✅ |
| WHERE a=1 | ✅ |
| WHERE b=2 | ❌ |
| WHERE a=1 AND c=3 | ✅（但只使用a列） |
索引优化相关概念
- 覆盖索引：查询的字段都包含在索引中，无需回表。
- 索引下推（ICP）：MySQL 5.6 起支持，把过滤条件尽量交给存储引擎执行，减少回表。
- 前缀索引：只对字符串列的前 N 个字符建索引。
索引使用建议
- 为WHERE、JOIN、ORDER BY中的字段建立索引：这些操作频繁，索引能极大提升性能。
- 使用自增主键减少页分裂
页分裂是指新数据插入B+树中间节点导致节点分裂，影响性能；自增主键是顺序插入，避免频繁分裂，提升插入性能。
- 索引字段应尽量 NOT NULL
NULL值不能参与部分索引运算，且对查询优化器判断不利。
- 选择区分度高的列建索引（索引选择性）
索引选择性 =
count(distinct col)/count(*)，越接近1越好。
索引选择性使用详细可看我的另一篇文章【MySQL】前缀索引、索引下推、访问方法，自适应哈希索引 - 尽量扩展已有索引，而不是新建多个冗余索引
减少索引数量，降低维护成本。
- 索引列不能参与计算、函数、类型转换
否则索引会失效，变成全表扫描。
索引失效场景
| 场景 | 示例 | 说明 |
|---|---|---|
使用 != 或 <> |
WHERE a != 1 | 不走索引 |
| 类型不一致 | 字符串列和数字比 | 索引失效（隐式转换） |
| 索引列上使用函数 | WHERE DATE(dt) = ‘2023-01-01’ | 不走索引 |
| 索引列上使用运算 | WHERE a + 1 = 5 | 不走索引 |
OR连接不同字段 |
WHERE a=1 OR b=2 | 若a、b无联合索引则失效 |
模糊查询 %开头 |
WHERE name LIKE ‘%abc’ | 无法利用索引（违背最左前缀原则） |
| NOT IN、NOT EXISTS | WHERE a NOT IN (…) | 不走索引 |
| 违反最左前缀原则 | WHERE b=1 (索引a,b) | 无法命中索引 |
演示如何使用 EXPLAIN 分析索引使用情况：
1. 准备测试表
-- 创建测试表
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
age INT,
created_at DATETIME,
INDEX idx_username (username),
UNIQUE INDEX uniq_email (email),
INDEX idx_age_created (age, created_at)
);
-- 插入测试数据
INSERT INTO user (username, email, age, created_at) VALUES
('user1', 'user1@example.com', 25, '2023-01-01'),
('user2', 'user2@example.com', 30, '2023-02-15'),
('user3', 'user3@example.com', 28, '2023-03-20');
2. 基础使用示例
示例1：索引生效的查询
EXPLAIN SELECT * FROM user WHERE username = 'user1';
输出结果：
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_username | idx_username| 202 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
关键字段解析：
possible_keys: 显示可能使用的索引（idx_username）key: 实际使用的索引（idx_username）type:ref表示使用了非唯一索引rows: 扫描行数（1行）
示例2：索引失效的查询
EXPLAIN SELECT * FROM user WHERE LOWER(username) = 'user1';
输出结果：
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
关键字段解析：
possible_keys:NULL表示没有可用索引key:NULL表示未使用索引type:ALL表示全表扫描Extra:Using where表示在存储引擎层后过滤数据
3. 联合索引示例
示例3：符合最左前缀原则
EXPLAIN SELECT * FROM user WHERE age = 25 AND created_at > '2023-01-01';
输出结果：
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | user | NULL | range | idx_age_created | idx_age_created | 9 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+------+----------+-----------------------+
关键字段解析：
key_len: 9（4字节int + 5字节datetime）type:range表示范围扫描
示例4：违反最左前缀原则
EXPLAIN SELECT * FROM user WHERE created_at > '2023-01-01';
输出结果：
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
关键解析：
- 联合索引
idx_age_created未生效 - 因为查询条件没有包含最左列
age
4. 冗余索引检测
示例5：查看可能使用的索引
EXPLAIN SELECT * FROM user
WHERE username = 'user1' AND email = 'user1@example.com';
输出结果：
+----+-------------+-------+------------+-------------+-------------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+-------------------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | const | idx_username,uniq_email | uniq_email | 402 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------------+-------------------------+---------+---------+-------+------+----------+-------------+
优化建议：
possible_keys显示两个索引都可用- 实际使用
uniq_email（唯一索引效率更高） - 如果
username索引很少单独使用，可以考虑删除idx_username避免冗余
5. 索引失效的经典场景
示例6：使用OR导致失效
EXPLAIN SELECT * FROM user WHERE username = 'user1' OR age = 25;
输出结果：
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | idx_username,idx_age_created | NULL | NULL | NULL | 3 | 55.55 | Using where |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
优化建议：
- 使用
UNION改写SQL：SELECT * FROM user WHERE username = 'user1' UNION SELECT * FROM user WHERE age = 25;
执行计划分析要点总结
| 字段 | 重要值说明 | 优化关注点 |
|---|---|---|
| type | ALL（全表扫描）、index（全索引扫描） | 出现ALL需重点关注 |
| possible_keys | 可能使用的索引 | 检查是否存在冗余索引 |
| key | 实际使用的索引 | 确认是否使用最优索引 |
| rows | 预估扫描行数 | 数值越大查询效率越低 |
| Extra | Using filesort/Using temporary | 出现这些值说明需要优化 |
通过定期分析 EXPLAIN 结果，可以：
- 验证索引是否按预期工作
- 发现全表扫描等高成本操作
- 识别冗余索引优化存储空间
- 检查联合索引的有效性
type 字段详解（访问类型）
type 是判断 SQL 性能非常重要的一列，它表示 MySQL 查询在表上的访问方式。访问方式越精确，效率越高。
下面是 MySQL 官方对访问方式的性能由好到差的排序：
| 排名 | 访问方式（type） | 说明 |
|---|---|---|
| 1️⃣ | system |
表只有一行，属于常量表（极快） |
| 2️⃣ | const |
表中最多只有一条匹配行（通过主键或唯一索引等值查询） |
| 3️⃣ | eq_ref |
联接时通过主键或唯一索引等值匹配，最多一条匹配（性能非常高） |
| 4️⃣ | ref |
通过普通索引等值匹配，返回多行结果（常见但高效） |
| 5️⃣ | range |
使用索引范围查询（例如 BETWEEN, <, >, LIKE 'xxx%'） |
| 6️⃣ | index |
全索引扫描，但不回表（比全表扫描略快） |
| 7️⃣ | ALL |
全表扫描（最慢） |
类型示例及使用场景
| type | 示例 SQL | 说明 |
|---|---|---|
system |
SELECT * FROM config LIMIT 1;（只有一行） |
极少见，性能最好 |
const |
SELECT * FROM users WHERE id = 5;（主键等值） |
主键或唯一索引等值查询 |
eq_ref |
多表联接：SELECT * FROM a JOIN b ON a.id = b.a_id;（a_id 为唯一键） |
每次 join 只返回一条记录 |
ref |
SELECT * FROM users WHERE name = 'Tom';（name 上有索引） |
索引等值匹配多行结果 |
range |
SELECT * FROM users WHERE age BETWEEN 20 AND 30; |
范围匹配，适用范围查询 |
index |
SELECT name FROM users;（走索引覆盖，不回表） |
只使用索引中的字段，无需回表 |
ALL |
SELECT * FROM users;（没有条件） |
全表扫描，性能最差 |
实战建议
- 目标：尽量让
type处于ref或更优（即：ref、eq_ref、const、system）。 - 如果看到
ALL，要警惕是否没有加合适的索引，或 SQL 写法不当。
欢迎加入DeepSeek 技术社区。在这里，你可以找到志同道合的朋友，共同探索AI技术的奥秘。
更多推荐
12
0- 0
已为社区贡献2条内容
所有评论(0)