数据库篇(4)--索引

一、索引是什么

---

• index是帮助MYSQL高效获取数据的数据结构。索引是数据结构。相当于书的目录。

二、索引的优缺点

---

索引的优势：

1. 提高查询效率（降低IO的使用率）

2. 降低CPU使用率 （因为B树索引本身就是一个排好序的结构，因此在排序时可以直接使用）

索引的缺点：

1. 索引本身很大，可以存放在内存/硬盘（通常为硬盘）

2. 索引不是所有情况均适用：（比如：数据量少、频繁更新的字段、很少使用的字段等情况）

3. 所有索引会降低增删改的效率

三、索引的分类

---

主键索引：

• 主键索引：根据主键来组织数据的，不能重复 不能为Null

辅助索引/二级索引：

• 单值索引：单列；一个表可以有多个单值索引
• 唯一索引：不能重复 可以是Null
• 复合索引：多个列构成的索引

四、索引与数据的存储关联性

---

聚簇索引（也可以理解为主键索引）：

Innodb中的主键索引（B+树索引）结构中，非叶子节点存储的是索引指针，叶子节点存储的是既有索引也有整行数据。索引和数据是存储在一起的，是典型的聚簇索引。

非聚簇索引（也可以理解为辅助索引/二级索引）

innodb中的辅助索引结构中，叶子节点存储的是主键索引值，并没有完整数据，所以为非聚簇索引。

MyISAM中索引和数据文件分开存储，B+Tree的叶子节点存储的是数据存放的地址，而不是具体的数据，是典型的非聚簇索引；换言之，数据可以在磁盘上随便找地方存，索引也可以在磁盘上随便找地方存，只要叶子节点记录对了数据存放地址就行。因此，索引存储顺序和数据存储关系毫无关联，是典型的非聚簇索引。

五、索引的操作

---

创建索引：

（1）创建方式一：create 索引类型 索引名 on 表（字段）

• 单值：create index 索引名 on 表(字段)
• 唯一：create unique index 索引名 on 表(字段)
• 复合：create index 索引名 on 表(字段1，字段2)

（2）创建方式二：alert table 表名 add index 索引名(字段)

• 单值：alert table 表名 add index 索引名(字段)
• 唯一：alert table 表名 add unique index 索引名(字段)
• 复合：alert table 表名 add index 索引名(字段1，字段2)

注意：如果一个字段是primary key,则该字段默认是主键索引

删除索引：drop index 索引名 on 表名

查询索引：show index from 表名

六、创建索引的时候需要考虑哪些问题

---

创建索引需围绕 查询需求、数据特征 和 维护成本 展开，核心原则：

        1）必要性：只为高频查询和性能瓶颈字段建索引。

        2）精准性：优先选择高区分度字段，合理设计联合索引顺序。

        3）可持续性：监控索引使用情况，定期清理冗余索引。

七、如果查询优化器选错了索引怎么办

---

1）如何识别查询优化器选错了索引

        1-1）使用EXPLAIN分析执行计划

        1-2）SHOW PROFILE分析查询性能

        1-3）通过慢查询日志分析

2）常见导致查询优化器选错索引的原因

        2-1）不合理的索引设计

        不合理的索引设计会误导查询优化器。例如，如果索引的选择性差，优化器可能会高估其性能，选择错误的索引执行计划。过多的索引还会增加写操作的开销，导致查询优化器难以做出正确的决策。

        解决方案：优化索引设计，选择性差的列可以与其他高选择性的列组成联合索引。

示例：低选择性索引

CREATE INDEX idx_status ON orders (status);

        在这个例子中，status 列可能有大量重复值，这导致索引的选择性较差。优化器可能会选择该索引，但查询性能并不理想。

        2-2）多表连接中的索引问题

        在多表连接查询中，优化器可能错误地选择连接顺序或使用不合适的索引，导致查询性能下降。

        解决方案：可以通过重写查询、调整连接顺序，或为 users 表创建合理的索引来优化查询。

        2-3）查询中使用了函数或表达式

        当查询条件中使用了函数或表达式，查询优化器可能无法正确使用索引，导致全表扫描或索引失效。

        解决方案：避免在索引列上使用函数或表达式，将查询条件调整为能够直接使用索引的形式

八、使用数据库的时候有哪些情况会导致索引失效呢

---

        1）对索引列使用函数或表达式：索引存储的是列的原始值，对列进行运算后，数据库无法直接匹配索引。

        2）隐式类型转换：查询条件与索引列类型不匹配，触发隐式转换，导致索引失效。(字段是VARCHAR 类型，但传入数字)

        3）前导通配符的 LIKE 查询：B+Tree 索引按前缀匹配，前导 % 无法定位起始点。

        4）联合索引未遵循最左前缀原则：联合索引 (a, b, c) 的结构依赖最左列顺序。

        5）使用 OR 连接非索引列：OR 条件中只要有一个列无索引，优化器可能放弃使用索引。

        6）数据分布不均导致全表扫描：优化器认为全表扫描比索引更快（如某值占比超过 30%）。

        7）使用 != 或 <> 运算符：非等值查询难以利用索引快速定位。

        8）使用 NOT IN 或 NOT EXISTS：否定查询难以利用索引快速过滤。

        9）分页查询深度偏移：LIMIT 100000, 10 需扫描前 100000 行，索引无法优化。

 更多内容：

数据库篇(1)--数据库事务的四大特性（ACID）

数据库篇(2)--数据库隔离级别及脏读、不可重复读、幻读

数据库篇(3)--MySQL存储引擎InnoDB和MyISAM区别及使用场景

数据库篇(4)--索引

数据库篇(5)-- sql 优化