索引

方便存储引擎快速查找，索引可以包含一列或多列。

索引的类型

B-Tree 索引

即是 BTree，在不同的存储引擎底层可会有不同的实现，NDB 存储引擎内部使用 T-Tree 结构存储；InnoDB 使用的是 B+Tree。 BTree对索引列是顺序存储的， BTree可以加快访问数据的速度，使存储引擎不在需要全表扫描，从索引的根节点开始搜索。

可以使用 BTree 索引的查询类型
1
2
3
4
5
6
7
8
9
create table index_test(
    aa varchar(10) not null,
    bb varchar(10) not null,
    cc varchar(10) not null,
    dd varchar(10) not null,
    key(aa, bb, cc)
);
insert into index_test(aa, bb, cc, dd) 
values("a1", "b1", "c1", "d1"),("a2", "b2", "c2", "d2"),("a3", "b3", "c3", "d3");
  • 全值匹配：和索引中的所有列进行匹配；上述索引可以用于查找 aa=a1,bb=b1,cc=c1 的行
  • 匹配最左前缀：即组合索引中使用索引的第一列；可以查找 aa=a1 的行
  • 匹配列前缀：匹配某一列值的开头部分；可以查找 aa 列以 a 开头的行
  • 匹配范围值：查找 aa 在 a1 和 a3 之间的行
  • 精确匹配某一列并范围匹配另外一列：可以查找 aa=a1 AND bb='%b' 的行
  • 只访问索引的查询：
BTree索引限制
  • 如果不是按照索引的最左列开始查找则无法使用索引；无法使用索引查找 cc=c3 的行
  • 不能跳过索引的列；例如 aa=a1 AND cc=c1 则只使用了第一列索引，第三列索引未使用
  • 查询中有某个列的范围查询则其右边的列都无法使用索引查询，例如 WHERE aa="a1" AND BB = "%1" AND cc="c1" 中 CC 这里只能使用索引的前两列

在实际的业务项目中给那些列加索引需要重点分析，在编码过程中 WHERE 列的匹配顺序也很重要。

哈希索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，存储引擎会给每一行的索引列计算一个哈希码，不同键值的行计算出的哈希码不一样，哈希索引将所有的哈希吗存储在索引中并在哈希表中保存了索引指向每一行数据的指针。如果哈希码计算产生了哈希冲突，哈希索引会以链表的方式存储在一个哈希条目中。

在具体的查询中，先计算查询条件的哈希值，然后在哈希索引中查找对应索引然后根据数据指针找到对应的数据行比对数据是否是要查询的数据

哈希索引的限制
  • 哈希索引中只存索引值和行数据指针，所以不能根据索引直接比对数据，需要定位到对应的行
  • 哈希索引并不是按照索引值排序存储的，也就无法用于排序
  • 哈希索引不支持部分索引列匹配查找，哈希索引是根据索引列的全部内容来计算索引值的，在hash(aa, bb)上建立哈希索引，如果只是用 aa列查找则无法使用索引
  • 由于哈希索引的计算和存储格式，只能支持等值(= IN <=>)查找不支持范围查找
  • 哈希冲突较少时，哈希索引的访问速度非常快；哈希冲突多的时候会造成索引维护成本变高，也会造成查询效率变低

InnoDB 的自适应性哈希索引会在某个索引被频繁使用时，在 B-Tree 索引之上在建立一个哈希索引。

空间数据索引(R-Tree)

MyISAM 支持空间索引，可以做地理数据存储。可以从所有维度来索引数据，可以从任意维度组合索引查询。但是必须使用 MySQL 的 GIS 相关函数 MBRCONTAINS() 来维护数据，在这个方面 PostgreSQL 支持的较好。

全文索引

适合查找文本中的关键字，而不是比较索引中的值。全文索引更适合做搜索引擎。

聚簇索引

将数据与索引放在一起存储，避免数据冗余存储所以一个表只有一个聚簇索引。聚簇索引默认使用主键作为 key，没有主键使用唯一键，还没有的话使用 6 字节的 rowId。

索引的优点

  • 大大减少服务需要扫描的数据量
  • 索引可以帮助服务器避免排序和建立临时表
  • 将随机 IO 变为顺序 IO

索引为查询提供了很好的性能，但是当表的数据量特别大时，维护索引的代价也会增加。

索引策略

独立的列

查询条件中的列需要是独立的，列不能是表达式的一部分，也不能是函数的参数

前缀索引和索引选择性

索引的选择性是指不重复的索引值和数据表的记录总数(T)的比值，范围是从 1/T ~ 1 唯一索引的选择性是最好的性能也是最好的。

对于一些数据类型必须要使用前缀索引(BLOB、TEXT、过长的VARCHAR)，索引这些列的完整长度，资源耗费太大。前缀索引的长度也是需要根据具体的数据分析。前缀索引是的索引更小更快但是 MySQL 无法使用前缀索引做 GROUP BY、ORDER BY 和覆盖扫描。

组合索引

创建索引的时候并不是给每个列创建索引，可以结合具体的业务查询，可以创建多列索引。尽可能是的索引的选择性更高。

  • 当服务器对多个索引做相交操作(多个 AND 条件)时，这时建立包含相关列的组合索引效果会更好
  • 当服务器对多个索引做相交操作(多个 OR 条件)时，通常会耗费大量 CPU、内存资源在缓存、排序和合并操作上

索引顺序

在一个多列索引中，索引列顺序按照最左匹配开始，所以索引列的顺序很重要。一般情况下将选择性最高的索引列放在最左边，这样可以降低后面索引列的查找范围。这个在 B-Tree 索引中有介绍。

聚簇索引

聚簇索引并不是一种索引类型，而是一种数据存储方式。索引依赖与具体的存储引擎，并不是所有的存储引擎都实现了聚簇索引，InnoDB 的聚簇索引叶子节点保存了 key 和数据行，非叶子只保存 key。因为保存了数据行，所以一个表只能有一个聚簇索引。InnoDB 将主键作为聚簇索引的 key，如果没有主键使用唯一键，还没有的话会定义一个隐式的主键来作为聚簇索引。

优点：
  • 索引和数据保存在一起，找到索引即找到数据，数据访问更快
  • 使用覆盖索引扫描的查询可以直接使用叶节点的主键值
缺点：
  • 对于 I/O 密集的应用聚簇索引可以提高查询的性能，但是如果数据都放在内存中时这种优势就下降了
  • 聚簇索引的叶子节点是有序的，当插入新的数据时，插入速度依赖于主键顺序。
  • 当要更新聚簇索引的索引列，这样可能引起底层叶子节点的数据迁移，这样会影响插入性能
  • 当插入数据和更新索引列，有可能会引起“页分裂”(新插入的行和索引变化列需要放到某个已满的页)，这个时候需要将已满页的数据调整，叶子节点要调整上层非叶子节点也可能要调整。
  • 聚簇索引可能会导致全表扫描变慢。尤其是行比较稀疏的情况，这是因为数据行分布在不同的页上，需要将数据加载到内存，此时 I/O 的损耗较大。
  • 建立了聚簇索引，会导致二级索引(非聚簇索引)访问需要两次查找，先在二级索引查到对应的主键列然后在聚簇索引中查找数据。前面说的 InnoDB 引擎自适应性哈希可以减少这样的工作。二级索引存储主键列不是行指针，这样可以减少页分裂和行移动时的二级索引维护工作

可以看到建立聚簇索引主键的性质对于聚簇索引很重要，如果主键是有序自增长的则在插入数据时会更方便一点。如果是随机的例如 UUID，则会存在几个缺点：

  • 插入的数据不一定是在最后可能是已有数据的中间，这样可能导致 InnoDB 不得不频繁的做页分裂，以便为新的行数据分配空间
  • 数据航要写入的页可能已经刷到磁盘上并从缓存中移除，或者还没有被加载到缓存中，此时要插入数据时需要先从磁盘读取页数据，增大了 I/O 开销
  • 频繁的页分裂，页会变的稀疏并被不规则的填充最终有数据碎片产生

当然顺序的主键因为要控制自增，在并发情况下可能会导致间隙锁竞争和 AUTO_INCREMENT锁竞争

覆盖索引

索引的叶子节点已经包含(覆盖)了要查找的字段，不需要在回表查询。所以索引在设计上要尽可能多考虑到后续的查询场景。覆盖索引对于性能的提升很有帮助。

覆盖索引必须要存储索引列的值，MySQL 只能使用 B-Tree 做覆盖索引

  • 索引条目远小于数据行，所以如果只读取索引，可以减少缓存的负载、I/O 的消耗
  • 索引按照列值顺序存储，对于 I/O 密集型的范围查询比随机读取每一行数据的 I/O 要少
  • MyISAM 在内存中缓存索引，访问数据时需要访问磁盘进行一次系统调用。如果在内存中可以直接获取性能会更好
  • 二级索引存在查询聚簇索引的情况，如果二级索引能够覆盖查询则可以避免对主键索引的二次查询

使用索引做排序

扫描索引本身是很快的，但是索引要是没有完全包含要查询的列，则需要在查询的时候回表查询对应的数据行，这样的话反而比顺序全表扫描慢了。

当索引的列顺序和 ORDER BY 的列顺序完全一致，并且所有列的排序方向 都一样时才能使用索引排序；如果查询关联多张表只有当 ORDER BY 子句引用的字段全部是第一个表的才能使用索引排序，ORDER BY 也要求满足索引的最左前缀匹配原则。当前导列为常量时， ORDER BY 不满足最左前缀要求也可以用索引排序。

压缩(前缀压缩)索引

MyISAM 使用前缀压缩来减少索引的大小，让内存可以存放更多的索引。MyISAM 压缩每个索引块的方法是：保存索引块中的第一个值，然后把后面的索引对比得到相同的前缀部分和不同的后缀部分，把这部分存储起来即可。例如：第一个索引是 abcd，第二个是 abcdefg，第二个所以压缩存储后是 4,efg。MyISAM 对行指针也采取类似存储做法。

压缩块减少了存储空间但是在查询时每个索引值计算依赖前面的值，使得查找时无法使用二分查找只能从头开始扫描。

冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建相同类型的索引，例如给主键再加索引

冗余索引是指要创建的索引已经可以被包含在其他的索引中，例如已经有 KEY(a, b)，再创建 KEY(a)，此时 (a) 是 (a, b) 的前缀索引。所以创建索引的时候应该尽量在之前的基础上进行扩展而不是一味新建索引。但是扩展时也得考虑扩展后会不会导致索引的维护成本变得更高。

删除未使用的索引

在前期架构设计时可能存在缺陷，存在一些无用的索引，可以利用 INFORMATION_SCHEMA.INDEX_STATISTICS 查看使用频率低的索引，将其删除。

索引和锁

索引可以在查询的时候锁定更少的行，减少 InnoDB 访问的行数，InnoDB 在二级索引上使用共享(读)锁，但访问主键索引需要排他(写)锁。