【索引】索引的介绍与优化三生石1314

1. 索引基础探究

索引，乃是存储引擎用以快速定位记录的数据架构。

1.1 索引的多样类型

1.1.1 B + 树剖析

B + 树属于多路搜索树类别。

B + 树能够显著提升数据访问速率，无需对全表进行扫描来查询数据，其搜索起始于根节点，根节点存有子节点的指针，借此精准选定正确的子节点展开查找，进而获取对应数值。

可借助 B - Tree 实施的查询类型如下：

以如下表为例阐释查询类型

Create Table People {

f_name varchar(50) not null,

l_name varchar(50) not null,

birth date not null,

gender enum('m', 'f') not null,

key(f_name, l_name, birth)

}

全值匹配：与索引中的全部列相契合，例如：查找 f_name 为‘Allen’，l_name 为‘Cuba’且生日是 '1990 - 01 - 01' 的记录。

匹配最左前缀：匹配最左侧的前几列，如查找 f_name 为‘Allen’且 l_name 为‘Cuba’的记录。

匹配列前缀：匹配某一列值的起始部分，例如查找 f_name 为‘Allen’且 l_name 中首字为‘C’的记录。

匹配范围值：用于查找 f_name 在 'Allen' 与 'Barrymore' 之间的记录。

索引的局限性：

若并非从索引的最左列起始则无法运用索引

不能跨越索引中的列进行查询

若查询中某列存在范围查询，则其右侧的索引均无法启用

哈希索引解读

哈希索引借助 Hash 表予以实现，仅在精确匹配时有效，存储引擎为每一列额外添加一个依据索引列计算得出的索引值，并且不同的值会生成各异的索引，hash 存储于索引之中，同时在 hash 表内留存指向每行数据的指针。

限制条件

索引自身仅需存储 hash 值，故而无法凭借索引中的值来规避读取行数据的操作。

无法进行排序

仅支持等值查找，不支持匹配查找。

存在 hash 冲突现象。

全文索引简述

这是一种特殊的索引形式，主要用于查找文中的关键词，而非对比索引的值，类似于搜索引擎的工作原理，需留意：停用词、词干和复数、布尔搜索等方面。

1.2 索引的优劣之处

有效削减了服务器所需扫描的数据规模。

规避了排序与临时表的生成。

将随机 I/O 转换为顺序 I/O。

索引的类别划分

唯一索引：既能加速查询进程，又能确保列值的唯一性。

主键索引：可加速查询且列值具有唯一性。

组合索引：由多列值共同构建而成的索引。

覆盖索引：索引涵盖了查询所需的所有列，查询时仅需执行一次查询操作即可获取结果数据。

聚簇索引

其叶子结点存储的是数据而非数据指针。

优点：

将数据集中存储。

数据访问速度较快。

运用覆盖索引能够直接获取主键值。

缺点：

插入速度受插入数据量的影响。

更新操作的代价颇高。

在表中插入新行或者主键被更新且需要移动数据时，可能引发页分裂现象。

全表扫描的效率降低。

二级索引规模较大，由于二级索引的叶子结点包含主键的值，所以可能需要额外的一次 I/O 操作。

非聚簇索引

其叶子结点为数据指针而非数据本身。

InnoDB 与 myisam 的对比分析

数据分布情况

InnoDB

myisam