MySQL索引

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

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

1、分类

1.1 按功能逻辑划分

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

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

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

复制代码

1.3 按数据结构划分

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

2、索引管理

2.1 创建表时创建索引

1. -- 示例：创建用户表并定义多种索引
2. CREATE TABLE `user` (
3.   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
4.   `username` VARCHAR(50) NOT NULL COMMENT '用户名',
5.   `email` VARCHAR(100) NOT NULL COMMENT '邮箱',
6.   `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
7.   `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
8.   
9.   -- 主键索引（自动创建）
10.   PRIMARY KEY (`id`),
11.   
12.   -- 唯一索引（邮箱唯一）
13.   UNIQUE KEY `uk_email` (`email`),
14.   
15.   -- 普通索引（用户名查询）
16.   KEY `idx_username` (`username`),
17.   
18.   -- 复合索引（多字段组合查询）
19.   KEY `idx_phone_created` (`phone`, `created_at`)
20. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复制代码

2.2 表创建后添加索引

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

复制代码

2.3 删除索引

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

复制代码

2.4 查看索引

1. -- 方法1：查看表的索引信息
2. SHOW INDEX FROM `user`;
4. -- 方法2：通过 INFORMATION_SCHEMA 查询
5. SELECT
6.   index_name,
7.   column_name,
8.   non_unique  -- 0=唯一索引，1=非唯一索引
9. FROM information_schema.statistics
10. 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 可以直接使用索引返回结果，而不需要访问数据行，也就是不需要回表查询。

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

复制代码

4.2 选择正确的索引列

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

复制代码

4.3 使用前缀索引

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

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

复制代码

4.4 使用组合索引

1. -- 创建组合索引，注意列的顺序
2. CREATE INDEX idx_composite ON users(last_name, first_name, age);
4. -- 最左前缀原则：索引可用于以下查询
5. SELECT * FROM users WHERE last_name = 'Smith';
6. SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';
7. SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John' AND age = 30;
9. -- 但不能用于以下查询
10. SELECT * FROM users WHERE first_name = 'John';
11. 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 语句的执行计划，判断是否使用了索引

1. -- 分析查询执行计划
2. EXPLAIN SELECT * FROM users WHERE name = 'John' AND age > 25;
4. -- 详细分析
5. 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内部还会再次进行评估）。

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

复制代码

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEth_userNULLrefidx_user_proidx_user_pro47const4100.00NULL5.2.2 ignore index

忽略指定的索引。

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

复制代码

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEtb_userNULLrefidx_user_pro_age_staidx_user_pro_age_sta47const4100.00NULL5.2.3 force index

强制使用索引。

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

复制代码

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEtb_userNULLrefidx_user_pro_age_staidx_user_pro_age_sta47const4100.00NULL6、总结

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

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

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！