MySQL索引

索引是数据库中的排序数据结构，类似于书籍的目录，用于快速定位数据，避免全表扫描（Full Table Scan）

• 优点：提高查询速度、加速排序和分组操作、确保数据唯一性
• 缺点：占用额外存储空间、降低数据写入速度（因为需要维护索引）

1、分类

1.1 按功能逻辑划分

索引类型	说明	适用场景
主键索引	针对主键字段创建,自动建立,唯一且非空(PRIMARY KEY)	基于主键的查询(如 WHERE id =100
唯一索引	确保字段值唯一(允许 NULL),通过 UNIQUE 定义	需唯一约束的字段(如邮箱、手机号
普通索引	最基本的索引,无唯一性约束,通过 KEY 或 INDEX 定义	频繁查询的非唯一字段 (如用户名、分类)
全文索引	用于全文搜索,支持大文本字段的关键词匹配,通过 FULLTEXT	文章内容、评论等大文本的关键词搜索
空间索引	用于地理空间数据类型(如 GEOMETRY ),通过 SPATIAL 定	地理位置相关查询(如附近的店铺

1.2 按列数划分

• 单列索引：基于单个列创建的索引。
• 组合索引（复合索引）：基于多个列创建的索引，遵循最左前缀原则（查询条件必须从索引的最左列开始才能生效）

CREATE INDEX idx_surname_age ON people(surname, age);
-- 能使用索引: WHERE surname = 'Wang', WHERE surname = 'Wang' AND age = 30
-- 不能使用索引: WHERE age = 30 (跳过了最左列 surname)

1.3 按数据结构划分

• B+Tree 索引：最常用的索引类型，支持范围查询和排序
• 哈希索引：基于哈希表，仅支持精确匹配（=， IN），Memory 引擎默认
• R-Tree 索引：用于空间索引

2、索引管理

2.1 创建表时创建索引

-- 示例：创建用户表并定义多种索引
CREATE TABLE `user` (`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',`username` VARCHAR(50) NOT NULL COMMENT '用户名',`email` VARCHAR(100) NOT NULL COMMENT '邮箱',`phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',-- 主键索引（自动创建）PRIMARY KEY (`id`),-- 唯一索引（邮箱唯一）UNIQUE KEY `uk_email` (`email`),-- 普通索引（用户名查询）KEY `idx_username` (`username`),-- 复合索引（多字段组合查询）KEY `idx_phone_created` (`phone`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 表创建后添加索引

-- 添加普通索引
ALTER TABLE `user` ADD INDEX `idx_created_at` (`created_at`);-- 添加唯一索引
ALTER TABLE `user` ADD UNIQUE INDEX `uk_phone` (`phone`);-- 添加复合索引（先按 phone 排序，再按 created_at 排序）
ALTER TABLE `user` ADD INDEX `idx_phone_created` (`phone`, `created_at`);-- 添加全文索引（适合大文本字段）
ALTER TABLE `article` ADD FULLTEXT INDEX `idx_content` (`content`);

2.3 删除索引

-- 方法1：ALTER TABLE
ALTER TABLE `user` DROP INDEX `idx_username`;-- 方法2：DROP INDEX（需指定表名）
DROP INDEX `uk_email` ON `user`;-- 注意：删除主键索引需先取消自增
ALTER TABLE `user` MODIFY COLUMN `id` INT UNSIGNED;
ALTER TABLE `user` DROP PRIMARY KEY;

2.4 查看索引

-- 方法1：查看表的索引信息
SHOW INDEX FROM `user`;-- 方法2：通过 INFORMATION_SCHEMA 查询
SELECT index_name, column_name, non_unique  -- 0=唯一索引，1=非唯一索引
FROM information_schema.statistics 
WHERE table_schema = '数据库名' AND table_name = 'user';

3、索引的底层原理（B+Tree）

MySQL 的 InnoDB 引擎默认使用 B+Tree 结构存储索引

B+Tree 特点：

• 非叶子节点 只存储键值（索引列的值）和指向子节点的指针。
• 所有数据 都存储在叶子节点，并且叶子节点之间通过指针连接形成链表。
• 查询效率高，通常只需 3-4 次磁盘 I/O 就能在上亿数据中定位记录。
• 非常适合范围查询，例如 WHERE id > 1000。

B+Tree 查询流程：

1. 从根节点开始，根据键值采用二分查找。
2. 通过比较确定下一步要查找的子节点。
3. 最终在叶子节点找到所需数据或数据的主键（对于二级索引，用于回表查询）。

4、索引优化

4.1 覆盖索引

当查询的所有字段都包含在索引中时，MySQL 可以直接使用索引返回结果，而不需要访问数据行，也就是不需要回表查询。

-- 创建覆盖索引
CREATE INDEX idx_covering ON users(name, age, email);-- 查询可以使用覆盖索引
SELECT name, age, email FROM users WHERE name = 'John' AND age > 25;

4.2 选择正确的索引列

-- 为经常查询的列创建索引
CREATE INDEX idx_frequently_queried ON orders(user_id, status);-- 为经常用于连接的列创建索引
CREATE INDEX idx_join_column ON orders(user_id);-- 为经常用于排序和分组的列创建索引
CREATE INDEX idx_sort_group ON orders(created_at);

4.3 使用前缀索引

对于文本列，可以只索引前几个字符以减少索引大小

-- 创建前缀索引
CREATE INDEX idx_name_prefix ON users(name(10));-- 确定合适的前缀长度
SELECT COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) AS selectivity_5,COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) AS selectivity_10,COUNT(DISTINCT LEFT(name, 15)) / COUNT(*) AS selectivity_15
FROM users;

4.4 使用组合索引

-- 创建组合索引，注意列的顺序
CREATE INDEX idx_composite ON users(last_name, first_name, age);-- 最左前缀原则：索引可用于以下查询
SELECT * FROM users WHERE last_name = 'Smith';
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John' AND age = 30;-- 但不能用于以下查询
SELECT * FROM users WHERE first_name = 'John';
SELECT * FROM users WHERE age = 30;

4.5 避免索引失效的情况

• 函数或表达式操作索引字段：WHERE YEAR(created_at) = 2024（改用 created_at BETWEEN '2024-01-01' AND '2024-12-31'）。
• 隐式类型转换：字段是字符串，查询用数字（如 WHERE phone = 13800138000，应改为 WHERE phone = '13800138000'）。
• 使用 NOT IN、!=、<>：可能导致索引失效（视数据分布而定）。
• LIKE '%后缀' 或 LIKE '%中间%'：模糊查询以 % 开头，索引失效。
• 复合索引不满足最左匹配：如复合索引 (a, b, c)，查询 WHERE b = 1 AND c = 2 不生效。
• OR 连接非索引字段：WHERE 索引字段 = 1 OR 非索引字段 = 2 可能导致索引失效。

5、使用 EXPLAIN 分析查询

使用 EXPLAIN命令可以查看 SQL 语句的执行计划，判断是否使用了索引

-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE name = 'John' AND age > 25;-- 详细分析
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'John' AND age > 25;

关键字段：

• id: 查询标识符
• select_type: 查询类型（SIMPLE, PRIMARY, SUBQUERY等）
• table: 访问的表
• type: 访问类型（从好到坏：system > const > eq_ref > ref > range > index > ALL）
• possible_keys: 可能使用的索引
• key: 实际使用的索引
• key_len: 使用的索引长度
• ref: 索引与哪一列比较
• rows: 估计要检查的行数
• Extra: 额外信息（Using where, Using index, Using temporary等）

5.3 SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

5.3.1 use index

建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

explain select * from tb_user use index (idx_user_pro) where profession = '软件工程';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	th_user	NULL	ref	idx_user_pro	idx_user_pro	47	const	4	100.00	NULL

5.3.2 ignore index

忽略指定的索引。

explain select * from tb_user ignore index (idx_user_pro) where profession = '软件工程';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tb_user	NULL	ref	idx_user_pro_age_sta	idx_user_pro_age_sta	47	const	4	100.00	NULL

5.3.3 force index

强制使用索引。

explain select * from tb_user force index (idx_user_pro) where profession = '软件工程';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tb_user	NULL	ref	idx_user_pro_age_sta	idx_user_pro_age_sta	47	const	4	100.00	NULL

6、总结

MySQL 索引是提升查询性能的核心工具，关键要点：

• 类型选择：主键索引用于唯一标识，唯一索引保证字段不重复，普通索引加速高频查询，复合索引优化多字段查询。
• 使用原则：遵循最左前缀原则，避免索引失效场景（如函数操作、隐式转换）。
• 设计平衡：索引并非越多越好，需在查询性能和写性能之间平衡，优先为高频查询字段创建索引。
• 性能分析：通过 EXPLAIN 分析索引使用情况，持续优化索引设计。