Mysql索引相关知识
索引是什么
MySQL索引是数据库性能优化的核心组件，其本质是通过特定数据结构（如B+树）加速数据检索。以下从基础原理、类型、使用场景到优化技巧进行系统讲解，结合实际案例和最佳实践：
一、索引基础原理
- 数据结构 B+树：InnoDB默认索引结构，非叶子节点仅存键值，叶子节点存数据指针，支持高效范围查询和顺序扫描。
- 哈希索引：Memory引擎支持，基于哈希表实现，仅支持等值查询（如
=），不支持范围查询。 - 聚簇索引：数据与索引存储在一起（如InnoDB主键索引），范围查询效率高。
- 非聚簇索引：索引与数据分离（如二级索引），查询时需回表获取主键数据。
二、索引类型与适用场景
| 类型 | 特点 | 适用场景 | 示例 |
|---|---|---|---|
| 主键索引 | 唯一、非空，自动创建聚簇索引 | 唯一标识记录（如用户ID） | PRIMARY KEY (id) |
| 唯一索引 | 值唯一，允许NULL | 防重复数据（如邮箱、手机号） | UNIQUE INDEX (email) |
| 普通索引 | 基础索引，无唯一性约束 | 加速单列查询 | INDEX (name) |
| 复合索引 | 多列联合索引，遵循最左前缀原则 | 多条件查询（如WHERE a=1 AND b=2） | INDEX (name, age) |
| 全文索引 | 支持自然语言搜索，分词处理 | 文本内容检索（如文章内容） | FULLTEXT INDEX (content) |
| 空间索引 | 基于R树，支持地理数据查询 | GIS数据（如地图坐标） | SPATIAL INDEX (location) |
三、索引失效分析
索引失效场景
- 函数或运算：
WHERE YEAR(create_time)=2023→ 改为范围查询WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'。 - 隐式类型转换：
WHERE varchar_col=123→ 显式类型匹配WHERE varchar_col='123'。 - OR条件：
WHERE a=1 OR b=2（若b无索引则失效）→ 改用UNION。 - 模糊查询通配符在前：
LIKE '%abc'→ 改用全文索引或反转字段存储。
索引覆盖
MySQL 的覆盖索引(Covering lndex)是指二级索引中包含了查询所需的所有字段，从而使查询可以仅通过访问二级索引|而不需要访问实际的表数据(主键索引)。
索引下推
索引下推(Index Condition Pushdown,ICP)是一种减少回表査询，提高査询效率的技术。它允许 MVSQL 在使用索引查找数据时，将部分査询条件下推到存储引擎层过滤，从而减少需要从表中读取的数据行，减少了 10(本该由 Server 层做操作，交由存储引擎层因此叫做“下推”)。 注意:索引下推是应用在联合索引上的
最左前缀匹配原则
MySQL 索引的最左前缀匹配原则指的是在使用联合索引时，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，然后是第二个列，以此类推。
底层原理:因为联合索引在 B+ 树中的排列方式遵循“从左到右”的顺序，例如联合索引(first_name，last_name，age)会按照(first_name，last_name，age)的顺序在 B+ 树中进行排序,MySQL在查找时会优先使用 first_name作为匹配依据，然后依次使用 1astname和 age 。
因此，组合索引能够从左到右依次高效匹配，跳过最左侧字段会导致无法利用该索引。按照(first_name，last_name，age)的顺序在 B+ 树中的排列方式(大致的示意图)如下
为什么使用B+数
B+树是一种自平衡树,每个叶子节点到根节点的路径长度相同,B+树在插入和删除节点时会进行分裂和合并操作以保持树的平衡,但它又会有一定的冗余节点,使得删除的时候树结构的变化小,更高效。査找、插入、删除等操作的时间复杂度为O(log n),能够保证在大数据量情况下也能有较快的响应时间。
B+树不像红黑树,数据越多树的高度增长就越快。它是多叉树,非叶子节点仅保存主键或索引值和页面指针,使得每-页能容纳更多的记录,因此内存中就能存放更多索引,容易命中缓存,使得査询磁盘的!/O次数减少。
B+树特别适合范围査询。因为叶子节点通过链表链接,从根节点定位到叶子节点査找到范围的起点之后,只需要顺序扫描链表即可遍历后续的数据,非常高效。
