【MySQL】索引分类、聚簇与非聚簇索引，索引优化，常见explain分析索引案例，type字段

索引基本概念

索引是数据库中用于加速数据检索的数据结构，类似于书籍的目录。它通过建立额外的数据结构来存储部分数据，从而加快查询速度。

索引的优缺点

优点	缺点
加快数据检索速度	占用额外存储空间
保证数据唯一性（唯一索引）	插入、更新、删除时需要维护索引，增加性能开销
加速表与表之间的连接操作
减少分组和排序的时间

MySQL索引类型分类

按存储结构划分：

• B+Tree索引：最常用的索引类型，支持范围查找。
• Hash索引：仅支持等值匹配，不支持范围查找。
• FULLTEXT全文索引：用于全文搜索（如搜索文章内容）。
• R-Tree索引：主要用于GIS空间数据。

R-Tree 是一种用于索引空间对象（如矩形、坐标点、多边形）的数据结构，能高效支持“交叉”“包含”等复杂空间查询。典型应用如：查找一个区域内的所有地理坐标点。

按应用层次划分：

• 普通索引：最基本的索引类型，无唯一性要求。
• 唯一索引：值必须唯一，允许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 个字符建索引。

索引使用建议

1. 为WHERE、JOIN、ORDER BY中的字段建立索引：这些操作频繁，索引能极大提升性能。
2. 使用自增主键减少页分裂

   页分裂是指新数据插入B+树中间节点导致节点分裂，影响性能；自增主键是顺序插入，避免频繁分裂，提升插入性能。

3. 索引字段应尽量 NOT NULL

   NULL值不能参与部分索引运算，且对查询优化器判断不利。

4. 选择区分度高的列建索引（索引选择性）

   索引选择性 = count(distinct col)/count(*)，越接近1越好。
   索引选择性使用详细可看我的另一篇文章【MySQL】前缀索引、索引下推、访问方法，自适应哈希索引

5. 尽量扩展已有索引，而不是新建多个冗余索引

   减少索引数量，降低维护成本。

6. 索引列不能参与计算、函数、类型转换

   否则索引会失效，变成全表扫描。

索引失效场景

场景	示例	说明
使用 != 或 <>	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', '[e-mail]', 25, '2023-01-01'),
('user2', '[e-mail]', 30, '2023-02-15'),
('user3', '[e-mail]', 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 = '[e-mail]';

输出结果：

+----+-------------+-------+------------+-------------+-------------------------+---------+---------+-------+------+----------+-------------+
| 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 结果，可以：

1. 验证索引是否按预期工作
2. 发现全表扫描等高成本操作
3. 识别冗余索引优化存储空间
4. 检查联合索引的有效性

---

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 写法不当。

---

https://github.com/0voice