Mysql（三）索引

欢迎大家来到IT世界,在知识的湖畔探索吧!

一、索引数据结构

1. B+Tree（默认结构）

• 特点：非叶子节点仅存储索引，叶子节点包含完整数据并通过双向指针连接，支持高效范围查询和排序。
• 优势：树高度低（如16KB页大小可存储约1170个索引元素），减少I/O操作，适合大数据量场景。
• 示例：查询WHERE age > 20时，B+Tree通过叶子节点指针快速定位范围数据。

2. Hash索引

• 特点：通过哈希算法直接定位数据，等值查询效率高。
• 局限：不支持范围查询、排序及部分函数操作，实际使用较少。

3. 其他结构

• 红黑树：平衡二叉树，解决单边增长问题，但高度仍较大。
• 空间索引（R树）：用于地理空间数据查询。

二、索引类型

从三个不同维度对索引分类：

欢迎大家来到IT世界,在知识的湖畔探索吧!

下面从基本使用的维度说明常用的索引分类：

1. 单列索引/普通索引

• 场景：适用于WHERE、JOIN、ORDER BY子句中的单个列。
• 示例：为email列创建索引：

 CREATE INDEX idx_email ON users(email);

欢迎大家来到IT世界,在知识的湖畔探索吧!

2. 复合索引

• 特点：多列组合，遵循最左前缀原则。
• 示例：索引(name, age)可支持以下查询：

欢迎大家来到IT世界,在知识的湖畔探索吧! WHERE name = 'Alice' AND age > 25; WHERE name = 'Alice'; -- 仅使用name列

• 失效场景：跳过左侧列或范围查询右侧列：

 WHERE age > 25; -- 无法使用复合索引 WHERE name = 'Alice' OR age > 25; -- OR条件导致失效

3. 唯一索引

• 场景：确保列值唯一，适用于主键或唯一约束字段。
• 示例：

欢迎大家来到IT世界,在知识的湖畔探索吧! CREATE UNIQUE INDEX idx_unique_username ON users(username);

4.主键索引

• 场景：主键索引是一种特殊的唯一索引，不允许有空值
• 示例：

 `id` bigint PRIMARY KEY,

三、索引创建与最佳实践

1. 创建语法

• 方式1：CREATE INDEX

欢迎大家来到IT世界,在知识的湖畔探索吧!CREATE INDEX idx_name ON table_name(column);

• 方式2：ALTER TABLE

ALTER TABLE table_name ADD INDEX idx_name(column);

• 方式3：建表时定义

欢迎大家来到IT世界,在知识的湖畔探索吧!CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, INDEX idx_user_order_date(user_id, order_date) );

2. 最佳实践

• 高频查询字段：优先为WHERE、JOIN、ORDER BY子句中的列创建索引。
• 区分度高的列：如UUID、邮箱，避免低区分度字段（如性别）。
• 复合索引顺序：将高频使用字段置于左侧，如(user_id, order_date)。
• 小表慎用索引：数据量小或频繁更新的字段可能因索引写入开销而降低性能。

四、索引失效场景与解决方案

1. 常见失效原因

• 函数或计算：

SELECT * FROM users WHERE DATE(create_time) = '2023-01-01'; -- 失效

优化：改用范围查询：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';

• 隐式转换：

SELECT * FROM users WHERE phone = ; -- phone为字符串类型，导致失效

优化：显式转换：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM users WHERE phone = '';

• 复合索引失效：

 CREATE INDEX idx_age_classid ON students(age, classid); SELECT * FROM students WHERE classid = 101; -- 失效 SELECT * FROM students WHERE age = 18 AND classid > 10; -- 有效 

• 通配符开头：

欢迎大家来到IT世界,在知识的湖畔探索吧! SELECT * FROM products WHERE name LIKE '%apple'; -- 失效 SELECT * FROM products WHERE name LIKE 'apple%'; -- 有效

• OR条件：

SELECT * FROM users WHERE name = 'Alice' OR email = ''; -- 若email无索引，则失效

优化：使用UNION ALL替代：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM users WHERE name = 'Alice' UNION ALL SELECT * FROM users WHERE email = '';

2. 其他注意事项

• 字符集不一致：确保表、字段字符集统一（如UTF8MB4）。
• NULL值处理：IS NULL可使用索引，IS NOT NULL可能失效。

五、索引正确使用与优化

1. 覆盖索引

• 定义：仅通过索引即可获取所需数据，避免回表。
• 示例：

CREATE INDEX idx_user_order ON orders(user_id, order_date, amount); SELECT user_id, order_date, amount FROM orders WHERE user_id = 1001;

2. 索引下推（ICP）

• 支持版本：MySQL 5.6+。
• 优势：将WHERE条件过滤下推至存储引擎层，减少回表次数。
• 示例：

欢迎大家来到IT世界,在知识的湖畔探索吧!-- users表已有复合索引（age，name） SELECT * FROM users WHERE age > 25 AND name LIKE 'A%';

3. 执行计划分析

• 工具：使用EXPLAIN查看索引使用情况：

EXPLAIN SELECT * FROM users WHERE age > 25;

• 关键字段：

• type：访问类型（如ALL为全表扫描，ref为索引查找）。
• key：实际使用的索引。
• rows：预估扫描行数。

4. 避免过度索引

• 影响：增加写入开销，降低并发性能。
• 建议：仅创建必要索引，定期清理无用索引。

六、总结

通过合理设计索引，可显著提升查询性能，尤其在大数据量场景下效果显著。建议结合EXPLAIN和性能监控工具（如Percona Monitoring）持续优化。