一介白衣ing

Appearance

MySQL索引 ​

在MySQL数据库中，索引是提升查询性能的核心机制。本文将从基本分类、数据结构、使用场景到优化策略，全面解析MySQL的七种索引类型及其技术细节。

一、索引类型概览 ​

MySQL支持以下主要索引类型：

1. 普通索引（Normal Index） ​

  • 定义：最基本的索引类型，无唯一性限制，允许重复值和空值。
  • 适用场景：频繁查询但无需保证唯一性的列。
  • 创建方式：
sql
CREATE INDEX idx_name ON table(column); -- 直接创建
ALTER TABLE table ADD INDEX idx_name(column); -- 修改表结构

2. 唯一索引（Unique Index） ​

  • 定义：索引列值必须唯一，允许存在多个NULL值。
  • 与普通索引区别：
特性普通索引唯一索引
值唯一性允许重复必须唯一
空值处理允许多个允许多个
数据约束无有
  • 创建方式：
sql
sql
CREATE UNIQUE INDEX idx_name ON table(column);
ALTER TABLE table ADD UNIQUE INDEX idx_name(column); -- 修改表结构

3. 主键索引（Primary Key） ​

  • 定义：特殊的唯一索引，不允许NULL值，每个表仅能存在一个。
  • 特点：
  • 自动创建聚簇索引（InnoDB引擎）
  • 物理存储顺序与索引顺序一致

4. 组合索引（Composite Index） ​

  • 定义：在多个字段上建立的联合索引，遵循最左前缀原则。
  • 示例：索引(A,B,C)生效场景：
  • WHERE A=1 AND B=2 ✅
  • WHERE B=2 AND C=3 ❌
  • WHERE A=1 AND C=3 ✅（仅A生效）
  • 优化策略：
  • 高频查询字段置左
  • 使用覆盖索引减少回表
  • 定期使用EXPLAIN分析索引命中

5. 全文索引（Full-text Index） ​

  • 定义：基于倒排索引实现的文本搜索索引，支持自然语言和布尔搜索。
  • 演进：
  • 5.6+：InnoDB支持英文全文索引
  • 5.7.6+：内置ngram解析器支持中文分词
  • 创建示例：
sql
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (content) WITH PARSER ngram;
  • 查询示例：
sql
SELECT * FROM articles
WHERE MATCH(content) AGAINST('+数据库 -Oracle' IN BOOLEAN MODE);

6. 空间索引（Spatial Index） ​

  • 定义：基于R-tree结构的地理空间索引，支持GIS数据类型（POINT/LINESTRING等）。
  • 应用场景：
  • 地图应用中查询某半径内的POI
  • 物流系统追踪车辆位置
  • 城市规划中的区域覆盖分析
  • 创建限制：
  • 仅MyISAM（5.7前）和InnoDB（5.7+）支持
  • 字段必须为GEOMETRY类型

7. 哈希索引（Hash Index） ​

  • 原理：通过哈希函数计算键值的哈希码，实现O(1)时间复杂度查询。
  • 特点：
  • 仅支持等值查询
  • Memory引擎显式支持，InnoDB自适应哈希索引
  • 冲突处理：链地址法
  • 缺陷：
  • 不支持范围查询
  • 哈希冲突影响性能

二、索引数据结构剖析 ​

B+Tree索引（默认结构） ​

  • 结构特性：
  • 非叶节点仅存储键值（索引分离）
  • 叶节点形成双向链表，支持范围查询
  • 树高通常3-4层（可支持千万级数据）
  • 优势：
  • 范围查询效率高
  • 排序优化
  • 页存储适配磁盘IO

B-Tree vs B+Tree ​

特性B-TreeB+Tree
数据存储位置所有节点仅叶节点
叶节点链接无双向链表
范围查询效率低高
磁盘页利用率较低更高

三、索引优化实践指南 ​

1. 组合索引优化 ​

  • 字段顺序策略：
  • 区分度高的字段置左（cardinality大的列）
  • 等值查询字段优先范围查询字段
  • 示例优化：
sql
-- 原始查询
SELECT * FROM orders
WHERE region='华东' AND order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- 更优索引：(region, order_date)

2. 索引使用准则 ​

  • 较频繁查询的字段适合使用索引
  • 唯一性太差的字段不适合单独创建索引
  • 更新非常频繁的字段不适合创建索引

3. 索引失效场景 ​

  • 隐式类型转换（如字符串列用数字查询）
  • 对索引列使用函数（WHERE YEAR(create_time)=2024）
  • 前导模糊查询（LIKE '%关键字%'）
  • 索引列参与运算（WHERE price+10>100）

4. 监控与维护 ​

  • 使用SHOW INDEX FROM table分析索引基数
  • 结合EXPLAIN执行计划持续优化
  • 定期执行ANALYZE TABLE更新统计信息
  • 通过慢查询日志定位低效索引

四、索引选择矩阵 ​

查询需求推荐索引类型
等值查询哈希/唯一索引
范围查询B+Tree
文本搜索全文索引
空间查询R-Tree
多条件组合查询组合索引

五、总结 ​

合理使用索引可使查询性能提升数倍，但需注意：

  • 索引不是越多越好，维护成本需权衡
  • 新版本特性（如MySQL 8.0的倒序索引、函数索引）需关注