MySQL索引

知知之之

已于 2024-05-15 15:43:54 修改

阅读量491

点赞数

分类专栏： MySQL 文章标签： mysql

于 2022-04-12 15:28:11 首次发布

本文链接：https://blog.csdn.net/weixin_35973945/article/details/124124116

版权

MySQL 专栏收录该内容

32 篇文章 1 订阅

订阅专栏

索引

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行。

索引类型

Hash索引

Hash索引是一种基于哈希表的数据结构，它用于快速数据检索。在哈希索引中，表中的列值被哈希函数转换成一个哈希值，该哈希值作为索引的键，而对应的记录位置信息作为键值。这种类型的索引对于等值查询（即“=”或“IN”条件）非常高效，因为它们可以直接计算出键的哈希值并迅速定位到数据。

Hash索引的特点包括：

速度：具有极快的查找速度，理论上可以提供接近O(1)的时间复杂度进行数据访问。
键的唯一性：由于哈希碰撞的存在（不同的键值映射到同一哈希值），理论上哈希索引不保证键的唯一性。不过，在实际应用中，哈希冲突可以通过各种策略来解决，例如使用链表等结构来存储相同哈希值的多个键。
范围查询的性能不佳：由于哈希索引仅对等值比较有效，对于范围查询（例如“<”、“>”、“BETWEEN”、“LIKE”）通常需要全表扫描，性能较差。
只能用于单列：虽然可以对多列分别建立哈希索引，但如果查询涉及多列的比较，则各个列的哈希索引无法组合使用来加速查询。
内存使用：哈希索引通常存储在内存中，它的性能很大程度上依赖于内存大小。

Hash索引适用于哈希表能够完整存储在内存中，并且主要执行等值查询的场景。

B+Tree索引

B Tree

B树（B-tree）和B+树（B+-tree）都是用于数据库索引和文件系统中的树形数据结构，它们对提高大规模数据存取的效率具有重要意义。尽管两者非常相似，它们之间还是有一些关键的区别：

结点存储：
- B树：在它的内部及叶子结点中都存储键和数据。
- B+树：所有的数据和指向数据记录的指针都存储在叶子结点中，内部结点只存储键。
叶子结点链接：
- B树的叶子结点通常是独立的，没有相互链接。
- B+树的叶子结点通过指针链接在一起，形成一个有序链表。这种结构对于范围查询特别有用，因为它可以通过遍历叶子结点的链表来顺序访问数据。
空间利用率：
- B+树因为内部结点不存储数据，只存储键，所以每个内部结点可以存储更多的键，从而降低树的高度，提高空间利用率。
查询效率：
- B树进行查找时，可能需要在内部结点和叶子结点中都进行数据的检索。
- B+树的查询效率更加稳定，因为所有的查询都要到叶子结点才能完成，每次查询的路径长度相同。
数据插入和删除：
- B+树由于所有数据都在叶子结点中，插入和删除操作可能会更简单些，因为它只会影响到叶子结点和从叶子结点到根结点的路径。而且，由于叶子结点是相互链接的，范围删除变得更为高效。
全扫描能力：
- B+树因为叶子结点是相互链接的，所以对数据库的全表扫描更加高效，只需要遍历叶子结点的链表即可。而B树则需要通过遍历整个树来进行全扫描。

综上，B+树由于其数据存储方式和叶子结点的链接特性，更适合于数据库索引的实现，其中对于范围查询有着更好的性能表现。而B树的应用更多地体现在需要频繁插入、删除和访问特定单个数据记录的场景中。

前缀索引

有时候需要索引很长的字符列，这会让索引变得大且慢，解决方法可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样会降低索引的选择性。

ALTER table 表名 add index title_pre(列名(100))

复合索引

复合索引的最左前缀匹配原则（又称最左匹配原则或最左前缀法则）是指在使用复合索引执行查询操作时，数据库从索引的最左边开始对条件进行匹配。如果查询条件不是从复合索引的最左列开始，索引将无法被有效使用。以下是一些该原则的关键点：

索引顺序：
- 一个复合索引包含了多个列，如索引(A, B, C)，则索引的最左列是A，其次是B，然后是C。
查询匹配：
- 如果一个查询条件是以索引的最左列开始的，则这个复合索引是有效的，比如WHERE A=...或WHERE A=... AND B=...。
- 如果查询条件跳过了最左列，如只有WHERE B=...，那么这个复合索引就不会被使用。
部分使用：
- 即使查询条件没有包含所有的索引列，只要它们是从最左列连续开始的，索引依然部分有效。例如，对于索引(A, B, C)，查询WHERE A=...能利用索引，查询WHERE A=... AND B=...也能利用索引，但WHERE A=... AND C=...则只有A部分能利用索引。
范围查询和顺序：
- 当涉及到范围查询（如>, <, BETWEEN, LIKE）时，匹配会在范围条件的列停止。比如，索引(A, B, C)对于查询WHERE A=... AND B>...只能对A和部分B利用索引，列C不会被用到索引中。
- 范围查询应该放在其它等值查询条件之后，如WHERE A=... AND B=... AND C>...，以充分利用索引。
非等值查询：
- 对于非等值查询（如LIKE '%...%'），如果不以索引的最左列开始，那么复合索引可能不会被使用。
列的选择性：
- 当设计复合索引时，通常应该将选择性最高的列（即具有唯一值最多的列）放在索引的最左边。

这个原则对于优化数据库查询和设计索引策略非常关键，因为它影响到数据库如何利用索引来加速查询。理解和应用这个原则有助于减少不必要的全表扫描，提高查询效率。

Primary Key（聚簇索引）

将索引与数据存储放到一起，找到索引就能找到数据，一个表仅有一个聚簇索引，MySQL默认主键是聚簇索引，如果表中没有主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引

数据排序：聚簇索引使得数据按照索引键的顺序存储在磁盘上，因此范围查询可以非常快速地执行。
唯一性：每个表只能有一个聚簇索引，因为表中的数据只能按照一种顺序存放。
快速访问：对于基于聚簇索引键进行的查询，可以更快地访问数据，因为索引结构直接指向含有数据的页，并且数据行是按顺序排列的。
插入速度：由于聚簇索引决定了数据的物理存储顺序，所以新增数据可能需要移动现存数据以保持数据顺序，可能会导致插入操作变慢。
更新开销：如果更新操作涉及到索引键的变更，可能需要移动数据行以保持整个表的顺序，导致更新操作的开销较大。
空间使用：由于聚簇索引直接包含表中的数据行，所以一般不需要额外的指针去定位数据行，可能会节省存储空间。

覆盖索引

覆盖索引是指一个索引包含（覆盖）了查询所需的所有数据。在这种情况下，数据库引擎可以仅通过索引来满足查询，而不需要访问表中的数据行。这通常可以显著提高查询性能，特别是对于大表的查询操作，因为:

减少I/O：当索引覆盖了所有需要的数据时，数据库就无需对表进行额外的读取操作，这减少了磁盘I/O压力。
高效的索引结构：索引通常比数据表占用更少的空间，并且因为它们通常是有序的，因此能更快地被搜索。
更少的数据读取：由于数据已经在索引中了，数据库无需加载整个数据行。

覆盖索引的一个典型应用场景是只包含几个列的查询，而这些列刚好都被索引所覆盖:

SELECT column1, column2 FROM table WHERE column1 = 'some_value'

如果存在一个包含column1和column2的索引，那么该查询就可以使用这个覆盖索引。

需要注意的是，索引尽管加快了查询速率，但也有其成本：

维护：当表中的数据被插入、更新或删除时，所有的索引都需要被更新。
空间消耗：每一个额外的索引都会消耗磁盘空间。

因此，设计索引时需要在查询性能和存储维护成本之间找到平衡点。覆盖索引是一个强大的工具，但也应该仔细考虑和测试，确保它适合特定查询的需求。

MyISAM和InnoDB存储引擎实现B+树索引方式的区别

MyISAM引擎中B+Tree叶子节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜素索引，如果指定的key存在，则取其data域的值，然后以data域的值为地址读取相应的数据记录

InnoDB引擎数据文件本身就是索引文件，树的叶子节点data域保存了完整的数据记录

回表查询

表中有四条记录：

　　1, shenjian, m, A

　　3, zhangsan, m, A

　　5, lisi, m, A

　　9, wangwu, f, B

两个B+树索引分别如上图：

　　（1）id为PK，聚簇索引，叶子节点存储行记录；

　　（2）name为KEY，普通索引，叶子节点存储PK值，即id；

select * from t where name='lisi';　是如何执行的呢？

如粉红色路径，需要扫码两遍索引树：

（1）先通过普通索引定位到主键值id=5；

（2）在通过聚簇索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

在MySQL中，尤其是使用了InnoDB存储引擎的情况下，可以通过以下几种策略来避免或减少回表查询：

使用覆盖索引：创建一个包含所有查询需要的列的索引。这样做可以直接从索引中获取所有所需数据，而无需回到数据行本身。
使用聚簇索引：尽量设计你的数据模型，使得最常用的查询能够利用聚簇索引（在InnoDB中，默认的聚簇索引是表的主键）。如果查询经常涉及主键，那么查询性能通常会更好。

索引下推

索引下推，也称为“Index Condition Pushdown”（ICP），是MySQL数据库在查询优化方面的一个重要策略。在MySQL的早期版本中，如果利用索引进行查询，MySQL会首先从索引中检索到符合条件的记录，然后根据这些记录的主键来回表查找整行的数据，然后再在服务器层面执行WHERE条件过滤。这种操作在某些情况下可能会导致大量不必要的回表操作，从而降低查询效率。

为了解决这个问题，从MySQL 5.6版本开始，引入了索引下推优化。通过此优化，MySQL可以将WHERE子句中的部分过滤条件直接应用到索引上，从而减少不必要的数据读取。这样，如果这部分条件过滤后的结果集小于原始结果集，那么回表读取的数据也会相应减少，可以明显提升查询性能。

简而言之，索引下推是一种查询优化技术，在提取主表数据前将更多筛选条件下推到索引层过滤，提升查询速度，减少CPU的消耗。但是并不是在所有场景下都能带来性能提升，是否使用索引下推取决于实际情况。

假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

select * from tuser where name like '张 %' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

下面图1、图2分别展示这两种情况。

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

五分钟搞懂MySQL索引下推https://www.cnblogs.com/three-fighter/p/15246577.html

索引的创建原则

选择查询频繁的列：为哪些经常出现在WHERE子句、JOIN条件或者是ORDER BY、GROUP BY中的列创建索引。这些列通常是查询优化的最佳候选者。
避免对经常变化的列创建索引：如果某个列的值经常变化，每次变化都会导致索引也要变化。这种变化会导致额外的写操作，拖慢数据的更新、删除和插入操作。
索引应尽量小：索引占用的磁盘空间和索引创建的速度与索引的列宽度有直接关系。较小的索引意味着磁盘I/O需求低，可以缓存更多的索引页，提高查询效率。
考虑使用前缀索引来减小索引大小：对于较长的字符串列，可以通过索引列值的前缀来减小索引大小以减少存储空间的占用以及提高查询效率（在不影响查询效果的前提下）。
利用复合索引避免多列的频繁查询：如果查询经常涉及多个列，则可以创建一个包含这些列的复合索引，但需要注意索引列的顺序。一般来说，选择性（区分度）更高的列应该放在复合索引的前面。
避免为表中的每一列都创建索引：虽然索引可以提高查询速度，但过多的索引会消耗更多的磁盘空间，并且在插入、更新、删除数据时增加额外的负担。
理解和使用覆盖索引：覆盖索引指的是一个索引包含了查询中需要的所有字段的情况。如果能通过索引直接获取查询所需数据，将会大大提高查询效率，因为避免了访问表数据的开销。
定期审查和优化索引：随着业务的演进和数据的增长，原有的索引可能不再适用，可能需要调整或删除。定期审查索引的使用和性能，确保索引策略与业务需求和数据状态保持一致。