【MySQL进阶】索引

努力干饭的小鱼

已于 2024-08-02 16:02:06 修改

阅读量838

点赞数 20

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-08-01 11:20:29 首次发布

本文链接：https://blog.csdn.net/2401_83045332/article/details/140837305

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

索引基本概念

什么是索引

索引是数据库管理系统（DBMS）中一种用于快速查找数据的方法。它是数据库表中一列或多列的有序数据结构，通过对这些列进行排序并生成索引，数据库可以更快地执行查询操作。

MySQL中两种数据访问方式

顺序访问

顺序访问是从表的第一条记录开始访问，直到访问到最后一条。

性能：当表中数据量较大时，顺序访问的性能较差，因为需要逐行读取所有记录。
适用场景：通常在没有索引或者索引不可用的情况下使用，比如对于小表或返回大量数据的查询

索引访问

索引访问就是我们在查询数据是在索引数据结构上执行的（如B树，哈希表等），索引数据首先是排好序的，其次它没有保存完整的数据列（聚簇索引除外，它的数据记录在叶子节点上的）。如果创建了索引之后，当我们对数据进行更新操作时，需要同时更新和维护索引资源。

性能：索引访问通常比顺序访问更快，尤其是当表中数据量很大时。索引能够显著减少需要读取的记录数量。
适用场景：适用于查询条件中包含索引列的场景，可以大幅提高查询效率。

什么时候需要创建索引

1.经常用于查询条件的列

如果某列经常出现在 WHERE 子句中，可以考虑为该列创建索引。例如：

如果查询条件中频繁使用 age 列，那么可以为 age 列创建索引：

SELECT * FROM employees WHERE age > 30;

CREATE INDEX idx_age ON employees(age);

2. 经常用于排序的列

如果某列经常出现在 ORDER BY 子句中，可以考虑为该列创建索引。例如：

如果排序操作频繁使用 name 列，那么可以为 name 列创建索引：

SELECT * FROM employees ORDER BY name;

CREATE INDEX idx_name ON employees(name);

3. 经常用于连接操作的列

如果某列经常出现在表连接的 JOIN 子句中，可以考虑为该列创建索引。例如：

如果连接操作频繁使用 dept_id 列，那么可以为 dept_id 列创建索引：

SELECT * FROM employees e JOIN departments d ON e.dept_id = d.id;

CREATE INDEX idx_dept_id ON employees(dept_id);

4. 唯一性约束的列

对于具有唯一性约束的列，创建唯一索引可以确保数据的唯一性。例如：

CREATE UNIQUE INDEX idx_unique_email ON employees(email);

5. 经常用于分组的列

如果某列经常出现在 GROUP BY 子句中，可以考虑为该列创建索引。例如：

如果分组操作频繁使用 dept_id 列，那么可以为 dept_id 列创建索引：

SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;

CREATE INDEX idx_group_dept_id ON employees(dept_id);

6. 覆盖索引

当索引包含所有查询所需的列时，查询可以直接从索引中获取数据，而无需访问表。例如：

如果为 dept_id、name 和 age 列创建复合索引：

SELECT name, age FROM employees WHERE dept_id = 1;

CREATE INDEX idx_covering ON employees(dept_id, name, age);

注意事项

不必要的索引：不要为每一列都创建索引，索引过多会增加插入、删除和更新操作的开销，并占用更多的存储空间。
选择性：选择性高的列（即具有大量不同值的列）适合创建索引，选择性低的列（如性别）不适合单独创建索引。
复合索引：对于多列查询，可以考虑创建复合索引（多列索引），但要注意列的顺序和查询的使用情况。
维护成本：索引需要维护，因此在频繁进行写操作（插入、删除、更新）的表上，需要慎重考虑是否创建索引。

总结

创建索引可以大幅提高查询性能，但需要根据具体应用场景和查询模式来合理创建索引。避免不必要的索引，选择性高的列和常用的查询条件、排序、连接、分组列是创建索引的主要考虑因素。

索引的数据结构

二叉树

二叉树是一种树形数据结构，其中每个节点最多有两个子节点，通常称为左子节点和右子节点。

二叉树的特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

二叉树的实现主要为了每次查找都可以折半而减少IO次数，但还有种极端情况下很容易出现树不分叉，这时候查询时间复杂度为O(n)如下图：

平衡二叉树

平衡二叉树除了具备二叉树的特点之外，最主要的特征是它的左右两个子树的高度差的绝对值不超过1。这个方案很好的解决了二叉查找树退化成链表的问题，在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。但是频繁旋转会使插入和删除牺牲掉O(logN)左右的时间。

可以看到当我们顺序插入1，2，3，4，5时候，平衡二叉树会通过旋转的方式使得数据分布较为均匀。

存在问题：

实现复杂：
- 平衡二叉树的实现比普通二叉搜索树要复杂得多。需要额外的逻辑来维护树的平衡，特别是插入和删除操作后，需要执行旋转操作来恢复平衡。
插入和删除操作的开销：
- 虽然插入和删除操作的时间复杂度在最坏情况下仍然是O(log n)，但平衡二叉树在这些操作过程中需要进行额外的旋转和调整，导致常数因子较大，实际开销较高。

Hash

哈希表通常由以下两个主要部分组成：

数组：
- 一个固定大小的数组，用于存储数据项或指向数据项的指针。
哈希函数：
- 一个用于将键映射到数组索引的函数。理想的哈希函数应尽可能均匀地分布键，以减少冲突。
冲突处理机制：
- 当两个不同的键映射到同一个索引时，称为冲突。常见的冲突处理方法包括链地址法（链表法）和开放地址法（如线性探测、二次探测和双重哈希）。

优点

快速查找：
- 在平均情况下，哈希表可以在O(1)时间复杂度内完成查找操作。
快速插入和删除：
- 插入和删除操作的平均时间复杂度也是O(1)，这使得哈希表在需要频繁插入和删除操作的应用中非常高效。
实现简单：
- 基本的哈希表实现相对简单，适用于需要快速存取的数据集合。

缺点

哈希冲突：
- 哈希冲突是无法完全避免的，处理冲突需要额外的开销。选择合适的哈希函数和冲突处理机制是关键。
内存浪费：
- 为了减少冲突，哈希表通常需要分配比实际存储数据更多的内存空间，这可能导致内存浪费。

B-Tree

MySQL的数据是存储在磁盘文件中的，查询数据时需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以我们优化的重点就是尽量减少磁盘IO操作，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。当数据极大的时候，二叉树的节点也是海量的，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！为了解决这个问题，B树应运而生，B树是一种多叉平衡查找树。

B树的特点

自平衡：
- B树始终保持平衡，所有叶子节点都在同一层上。因此，从根节点到任何叶子节点的路径长度相同，这保证了查找、插入和删除操作的时间复杂度为O(log n)。
多叉性：
- B树的每个节点可以有多个子节点，而不仅仅是两个（如在二叉树中）。具体来说，B树的每个节点最多可以有m-1个键和m个子节点，其中m是B树的阶数（degree）。
节点包含多个元素：
- 每个节点包含多个元素（键）和子节点指针，而不仅仅是单个值。节点中的元素按顺序存储，以便进行高效的范围查询。