MySQL索引
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 查询流程：
- 从根节点开始，根据键值采用二分查找。
- 通过比较确定下一步要查找的子节点。
- 最终在叶子节点找到所需数据或数据的主键（对于二级索引，用于回表查询）。
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、执行计划
5.1 使用 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.2 SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
5.2.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.2.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.2.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 分析索引使用情况，持续优化索引设计。
本文系转载，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文系转载，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
