DBMS/数据库（四）索引

xzh
164 阅读16分钟

索引

索引简述

什么是索引

索引是一种优化DBMS查询的技术，本质是数据结构，与 DBMS 的存储没有任何关系（独立于存储之外） ，只需查询有关系，没有索引，DBMS 也可以正常工作。

为什么需要索引

索引的目的是为了优化查询性能，DBMS 在查找某一条记录时，如果没有索引，那么需要遍历所有的Page（根据Page Directiry或双线链表），再遍历每一个Page中的记录（堆组织文件由于是无序必须扫描、索引组织文件可以二分查找），直到找到对应的记录。这种方式可以实现记录的查询，但问题在于性能太低，如果记录很多，那么等同于全表扫描，非常消耗时间。

索引为什么能提升查询性能

索引本质上是一个数据结构，是一个存储了所有记录的 目录 结构，查询记录时根据目录进行查询肯定优于遍历每一条记录。

优缺点

优点：性能高

缺点：额外的空间以及索引页数据与真实记录的一致性保证

索引结构

顺序索引

全表扫描，找到目标索引记录。性能非常差。

Hash 索引

使用Hash结构查询记录。

  • 优点
    • 包含等值的查询性能非常高（ <=> ，聚簇索引下，有序的索引记录hash仍然可以快速的找到范围值，但前提是包含‘=’）；
    • Hash索引只需要 很少的磁盘IO；因为Hash结构可以快速的定位一个查询，因此不需要多次磁盘IO；
  • 缺点
    • 纯粹的范围查询性能很差（ <> ，无法定位到某一个具体的值，意味着只能全表扫描）；
    • Hash 结构会面临key的冲突问题（如果索引Key不唯一，那么将会出现Hash冲突问题）；
    • Hash 结构不支持前缀索引（hash的key必须是准确的，如果是使用通配符的前缀，那么不能实现）；
    • Hash 结构不支持联合索引的最左匹配原则（实际上，和前缀索引类似，联合索引使用Hash实现，那么就必须key中存储了所有列值，但当条件只包含某一列时，此时无法根据某一个列进行索引查找）；

hash 索引的优缺点实际上就是Hash数据结构的优缺点，查询性能高，但仅适合于等值条件下，当查询条件与key不相等时（前缀索引导致的不相等；联合索引时仅使用某列导致的不相等；纯粹的范围查找导致不相等），那么就没办法了。

为什么很少用 Hash 索引结构
  • 非唯一列的Hash冲突问题；
  • 不支持纯粹的范围查找；
  • 不支持前缀索引；
  • 不支持联合索引时的最左前缀原则；
Tree 索引

使用Tree结构查询；

  • 优点
    • 支持对索引记录的排序（排序的好处）；
    • 查询性能很高；
    • 可以支持更加灵活的索引实现（如前缀索引、联合索引、纯粹的范围查找等）；
  • 缺点
    • 一次查询，可能需要多次磁盘IO（B+Tree更是叶子节点才存储数据；B-Tree的深度可能很大）；
    • 性能适中，等值查询优于顺序查找但不如Hash查找；
二叉搜索树

性能很好，但由于其是二叉，因此树深度太大，并且二叉搜索树如果根节点设定不合适，那么可能会退化为一个链表。没有DBMS使用此结构。时间复杂度为：最好是O(logN)，最差是O(N)。

平衡二叉树

由于二叉查找树会退化为O(N)；一般的二叉查找树不是平衡的，因此数据可能一直落在一侧。

所以为了避免二叉搜索树的退化，因此使用平衡二叉树可以避免此问题。时间复杂度为：O(logN)。

B - Tree（平衡多路搜索树）

由于二叉的树一层存储的数据太少，导致树深度太大，那么查找节点的时候，每次向下查找都会有一次磁盘IO，非常耗费性能。

所以使用B-Tree，使用搜索树保证了性能，多叉保证了深度不会太大，平衡保证了树的稳定性，性能有所保障。

特点：

  • 每一个树节点都存储了索引key和索引记录或索引记录的地址偏移量；
  • 平衡多叉搜索树；
  • 数据量很大时，树深度依然很大（节点存储的是记录，每一个节点大小有限的，通常等于一页，因此很可能一个节点才存储了几个索引记录）；
B + Tree

由于B-Tree每一个节点都存储索引记录，会导致分叉太多，一个数据页存储的数据太少，而仅仅叶子节点存储的话，普通节点存储的key就会更多。

所以B+Tree在B-Tree基础上又做了一些优化：

  • 非叶子节点不存储索引记录，叶子节点存储索引记录或索引记录的偏移量；
  • 有序的索引记录是通过记录之间的双向指针关联，这样可以实现快速的进行范围查找；

索引物理存储方式

索引结构中关于索引记录的存储方式有两种：聚簇索引和非聚簇索引。

索引记录和索引结构保持相同的顺序，那么就是聚簇索引；索引记录和索引结构顺序不一致，那么就是非聚簇索引；两者区别在于聚簇索引结构中的value存储的是索引记录（最后一个索引节点在索引记录中），而非聚簇索引结构的value中存储的是索引记录的地址偏移量（非聚簇索引通过二级索引实现） ；

Cluster Indexing （聚簇索引）

聚簇索引实际上是一种索引结构中存储索引记录的方式，聚簇索引下索引结构中存储的是索引记录，索引结构与索引记录的顺序是一致的（索引记录在页上的存储通常使用索引文件组织）。

  • 表中所有索引记录的存储顺序和索引结构一致；
  • 一个表只能有一个聚簇索引（每个表都有聚簇索引，而聚簇索引要求索引记录的排列必须和索引结构一致，因此一个表也仅能只有一个聚簇索引）；
  • 聚簇索引使用主键或第一个唯一索引或默认生成的列；
优缺点

优点：

  • 索引结构与索引记录数据页融合，查找到索引结构value时，不需要额外的多加载一次数据页到Buffer Poll中；
  • 辅助索引的value存储的是主键值，因此记录的迁移不需要辅助索引同步更新；
  • 聚簇索引下的索引记录是有序的，使用索引组织文件方式，因此有序的查询性能很高；

缺点：

  • 索引记录需要有序，因此会发生频繁的索引记录迁移；
Non-Cluster Indexing（非聚簇索引）

非聚簇索引实际上也是一种索引结构中存储索引记录的方式，非聚簇索引下索引结构中存储的是索引记录的地址偏移量，索引结构与索引记录的顺序不是一致的（索引记录在页上的存储通常使用堆文件组织或其他），索引记录甚至是无序的。通常会将非聚簇索引结构的文件与索引记录文件分开；

优缺点

优点：

  • 索引记录和索引结构不耦合，灵活；
  • 辅助索引存储的地址偏移量，直接加载索引记录所在页即可，不需要通过主键二次查询；

缺点：

  • 索引结构查询到索引记录地址后，需要二次加载数据页，导致额外的磁盘IO开销（DBMS 最重要的一点就是尽可能减少磁盘IO），所以主流实现都是聚簇索引；
聚簇索引与非聚簇索引
  • 非聚簇索引虽然更加灵活，索引结构和索引记录分开。但性能相交于两者融合的聚簇索引来说较低（索引页和数据页不在一起，因此需要多一次磁盘页的加载） 。因此大多数DBMS都是用聚簇索引。
  • 非聚簇索引在非主键查询下 仅需要一次查找，而聚簇索引需要通过辅助索引定位到主键，再使用主键通过聚簇索引结构查询真实记录（聚簇索引多一次查询，但通过索引覆盖可以优化）。
Secondary Index（二级索引）

二级索引只是一个技术，即索引结构中不存储索引记录，而是存储引用；

非聚簇索引和辅助索引都是通过二级索引实现的。非聚簇索引存储的引用是索引记录地址偏移量；而辅助索引存储的引用是其所对应的索引记录的主键；

值得注意的是，每创建一个二级索引，DBMS都会重新创建一个索引结构。

辅助索引

辅助索引用于解决非主键查询情况下的索引查询。

聚簇索引下的辅助索引

聚簇索引下，使用二级索引实现的辅助索引，建立一个二级索引（辅助索引），辅助索引结构中的value存储的是主键列的值。

当执行非主键索引查询时：

  1. 先根据辅助索引结构查找到 value（主键列的值） ；
  2. 再根据主键值通过聚簇索引结构查询到真正的索引记录（ 二次查询 ） ；
非聚簇索引下的辅助索引

非聚簇索引下， 使用二级索引实现的辅助索引，建立一个二级索引（辅助索引），辅助索引结构的value 存储的不是主键，而是索引记录地址偏移量。

当执行非主键索引查询时：

  1. 先根据辅助索引结构查询到 value（索引记录的地址偏移量）；
  2. 再根据索引记录的地址偏移量获取到索引记录（一次查询，记录所在页需要二次加载）；
为什么聚簇索引时辅助索引value存储的是主键而不是地址偏移量

聚簇索引下会产生频繁的索引记录迁移（聚簇索引下索引记录是有序的，因此插入记录很容易导致记录迁移），迁移会导致索引记录地址偏移量发生变化。如果辅助索引结构 value 存储了地址偏移量，那么所有的辅助索引结构都需要同步更新，而存储主键值，就只是聚簇索引结构同步更新，而其他的辅助索引都不需要更新。

聚簇索引下不会发生频繁的索引记录迁移（非聚簇索引大多使用堆文件组织，无序的索引记录在插入、删除时都不需要迁移），因此直接存储索引地址偏移量即可，避免了二次查询。

回表

聚簇索引下，执行辅助索引结构获取到的value是主键，需要再次执行聚簇索引结构，使用主键查询到最终的索引记录，这个过程就是回表。

联合索引

多个列组成的索引结构，不同于单列实现的索引结构，构建联合索引即对每一列进行排序构建。多列时查询时，需要根据每一个列进行匹配；索引结构中key存储的是所有列的值；

如在A、B、C三列组成的索引中：

当构建联合索引结构时：

  1. 联合索引结构中的key存储的是 多列的值（单列存储的是单列的值）；
  2. 对A列值排序并调整索引结构顺序；
  3. 当A列值相等时， 再对B列值排序 并调整索引结构顺序；
  4. 当B列值相等时，再对C列值排序并调整索引结构顺序；

当执行多列查询时：

  1. 先根据 A 列（第一列）值进行查询；
  2. 如果查询条件包含B列值，那么继续在查询出的A列中继续根据B列值查询；
  3. 如果查询条件还包含C列，那么继续根据C列值查询；
  4. 找到最终索引记录；
什么是最左列问题

联合索引查询时，没有最左列，此时索引失效。

由于联合索引结构中key使用的是多列值，且构造结构时，以最左列先排序，其他次之。因此在查询时，就需要先根据最左列进行第一轮筛选。如果没有最左列，那么没办法根据第二列在联合索引结构上查询。

为什么联合索引会失效

联合索引（sid, name）

  • 条件带有 OR（多列值组成了key，查询时必须匹配每一列值，因此多列是‘与’的关系）；
    • explain SELECT * FROM xzh_test.tb_index where sid = 'sid-000-10000' or name = 'name-10000' limit 0, 10000000; （失效：联合索引列之间使用 OR ）
  • 条件中缺少第一索引列（最左匹配原则，联合索引的多列是根据创建的字段顺序优先排序的，如先根据A列排序，后B、C等，查询时会先使用A列进行第一次筛选，而没有最左列A的话，那么无法实现索引）；
    • explain SELECT * FROM xzh_test.tb_index where name = 'name-10000' limit 0, 10000000; （失效：缺少最左列）
    • explain SELECT * FROM xzh_test.tb_index where name = 'name-10000' order by sid limit 0, 10000000; （失效：缺少最左列，即使 order by 中使用了最左列）
    • explain SELECT * FROM xzh_test.tb_index where name = 'name-10000' and sid = 'sid-000-10000' limit 0, 10000000;（成功：虽然最左列不是第一个条件，但DBMS会进行顺序调整优化）
    • explain SELECT * FROM xzh_test.tb_index where sid = 'sid-000-10000' limit 0, 10000000;（成功：虽然只有最左列）
    • explain SELECT * FROM xzh_test.tb_index where sid = 'sid-000-10000' order by name limit 0, 10000000;（成功：只要有最左列，即使使用了 order by 且不是对最左列排序）
    • explain SELECT * FROM xzh_test.tb_index where sid = 'sid-000-10000' order by sid limit 0, 10000000;（成功：只要有最左列，即使使用了 order by）
  • 条件中索引列使用 like （如果前缀是通配符，那么失效，如果后缀是通配符，那么成功）；
    • explain SELECT * FROM xzh_test.tb_index where sid like 'sid-000-10000' and name = 'name-10000' limit 0, 10000000 （成功：Like + 前缀没有使用通配符）;
    • explain SELECT * FROM xzh_test.tb_index where sid like '$sid-000-10000' and name = 'name-10000' limit 0, 10000000; （失效：Like + 前缀使用了通配符）
  • 条件中索引列使用了 ><条件符，如果是区间，那么后续的不会生效，但当前会生效；如果不是区间，那么都不生效；
    • explain SELECT * FROM xzh_test.tb_index where sid > 'sid-000-10000' and name = 'name-10000' limit 0, 10000000; （失效：最左列使用了范围查询，但不是区间型的）
    • explain SELECT * FROM xzh_test.tb_index where sid > 'sid-000-100000' limit 0, 10000000; （失效：最左列使用了范围查询，但不是区间型的）
    • explain SELECT * FROM xzh_test.tb_index where sid > 'sid-000-10000' and sid < 'sid-000-100000' and name = 'name-10000' limit 0, 10000000; （成功：最左列使用了范围查询，但是是区间型的）
    • explain SELECT * FROM xzh_test.tb_index where sid > 'sid-000-100000' and sid < 'sid-000-1000000' limit 0, 10000000;（成功：最左列使用了范围查询，但是是区间型的）
    • explain SELECT * FROM xzh_test.tb_index where sid between 'sid-000-10000' and 'sid-000-100000' and name = 'name-10000' limit 0, 10000000;（成功：最左列使用了范围查询，但是是区间型的）
    • explain SELECT * FROM xzh_test.tb_index where sid = 'sid-000-10000' and name > 'name-10000' limit 0, 10000000; （成功：最左列不是范围型的，虽然第二列是范围型）
    • explain SELECT * FROM xzh_test.tb_index where sid in ('sid-000-10000','sid-000-10001') and name = 'name-10000' limit 0, 10000000; （成功：IN 是明确的等值）
索引与 order by 关系
  • Query和条件中没有索引列或最左索引列时，Order By 不使用索引；
    • explain SELECT * FROM xzh_test.tb_index where name = 'name-10000' order by sid limit 0, 10000000;（失效：条件中没有最左索引列）
    • explain SELECT * FROM xzh_test.tb_index order by sid, name limit 0, 10000000;（失效：条件缺少索引列）
    • explain SELECT sid FROM xzh_test.tb_index order by name limit 0, 10000000;（失效：条件缺少索引列）
    • explain SELECT * FROM xzh_test.tb_index order by name limit 0, 10000000;（失效：条件缺少索引列）
  • 只要Query或条件中使用了索引或最左索引列时，Order By 无论是否使用索引都会生效；
    • explain SELECT * FROM xzh_test.tb_index where sid = 'sid-000-10000' order by name limit 0, 10000000;
    • explain SELECT * FROM xzh_test.tb_index where sid = 'sid-000-10000' order by sid limit 0, 10000000;
    • explain SELECT * FROM xzh_test.tb_index where sid in ('sid-000-10000') order by sid limit 0, 10000000;
    • explain SELECT sid FROM xzh_test.tb_index order by sid, name limit 0, 10000000;（成功：虽然条件中没有索引列，但Query中有索引列）
    • explain SELECT * FROM xzh_test.tb_index where sid = 'sid-000-10000' order by name limit 0, 10000000;（成功：虽然Order By中没有索引列，但条件中使用了）

索引类型

Primary Index （主键索引）

主动创建的主键或默认生成的主键。

事实上在物理结构中，只有Cluster index和Secondary Index两种方式，Primary index只是Cluster Indexing 使用了 Primary Key 作为了索引结构的key；而没有Primary，那么就是默认的ID作为索引结构的key；

Dense Index（稠密索引）

搜索键值与索引记录一一对应。即每一个记录都在索引结构中有一个索引键值对应。

如每一个索引记录在B-Tree中都有一个节点与之对应。

优缺点：

  • 优点：性能较高，直接通过映射可以获取到记录。
  • 缺点：占用空间，每一个记录都需要映射。
Sparse Index（稀疏索引）

搜索键值与索引记录并非是一一对应的，而是分区对应。即每一组记录在索引结构中有一个索引键值与之对应。

如索引记录分为多组，每一组的起始在索引结构有对应，而组内余下的记录并没有索引键值对应。MySQL的InnoDB中使用的是稀疏索引，将记录分为多组，每一组在索引结构上映射，检索时，根据二分查找到组起始记录，然后再遍历组内其他记录。

优缺点：

  • 优点：空间占用小，每一页中存储的记录量增加。
  • 缺点：性能稍微影响，但不大。
唯一索引

唯一列组成的索引。使用辅助索引实现查询。

普通索引

普通列组成的索引。使用辅助索引实现查询。

索引是否可以重复

普通索引可以。

唯一索引和普通索引的区别

唯一索引使用的是值唯一的列；普通索引的值可能是重复的；

  • 在索引结构上，使用Hash结构会使得普通索引发生Hash冲突，而Tree结构上不存在；
  • 在查询时，唯一索引结构上查询到一个索引记录时直接返回；而普通索引由于可能重复，因此需要按照范围查找方式寻找到链表的下一个非匹配的记录。（唯一索引性能更高；普通索引会遍历链表，在遍历过程中，如果当前目标记录较多，那么可能会继续加载多个数据页，发生多次磁盘IO）
前缀索引

针对字符串等类型的列，为其前几个字符建立一个索引。目的是前缀索引使得索引结构占用空间变小了，降低空间占用。如果不使用前缀索引，那么一个很大的字符串会使得索引结构很大。

因此通常也会要求name等一些信息有长度限制。

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length)) #核心是长度限制，即为字符的前length长度建立索引。
);
全文索引

很少DBMS支持，大多是关系型数据库，提升检索速度的索引。

NoSQL中有更为复杂的支持逻辑。

avatar
文章
阅读
粉丝