MySQL之数据存储索引篇(一)

索引的功能

       建立索引的目的是加快对表中记录的查找或排序。付出的代价：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

常见的索引

类别 名称 数据结构 按照数据的连续性 聚簇索引 b+tree 非聚簇索引 b-tree 按照密度分类 稀疏索引 b-tree 稠密索引 b+tree 按照功能分类 主键索引 b-tree、b+tree 唯一索引 b-tree、b+tree 辅助索引 b-tree、b+tree 二级索引 b-tree、b+tree 覆盖索引 b-tree、b+tree 按照列数分类 单列索引 b-tree、b+tree 组合索引（多列索引、复合索引） b-tree、b+tree 按照顺序分类 正向索引 b+tree 反向索引（倒排索引） b+tree 按照数据结构分类 位图索引 bitMap 哈希索引 hash B树索引 b-tree、b+tree、b*tree R树索引 R-tree LSM树索引 LSM-tree 红黑树索引 Red Black Tree

不同的索引介绍

聚簇索引

     特点

• 聚簇索引也称为聚集索引，聚类索引，簇集索引，聚簇索引确定表中数据的物理顺序。
• 由于聚簇索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚簇索引。
• 并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。
• 聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。

     示意图

       

非聚簇索引

     特点

• 非聚簇索引，叶级页指向表中的记录，记录的物理顺序与逻辑顺序没有必然的联系。非聚簇索引则更像书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。
• 表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。
• 每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。但是，一个表可以有不止一个非聚簇索引。实际上，对每个表你最多可以建立249个非聚簇索引。非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。
• 非聚集索引，则是稠密索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。

      示意图

     

聚簇索引与非聚簇索引异同

• 聚簇索引是物理有序的，非聚集索引表数据存储顺序与索引顺序无关。
• 聚簇索引叶子节点包含data，而非聚集索引叶子节点包含指向data的指针。
• 聚集索引是一种稀疏索引，非聚集索引，则是稠密索引。
• 两者都是B+tree索引。

稠密索引

     特点

• 文件中每个搜索码值都有一个索引记录，比如非聚簇索。

      示意图

       

稀疏索引

     特点

• 相对于稠密索引，稀疏索引只为某些搜索码值建立索引记录；在搜索时，找到其最大的搜索码值小于或等于所查找记录的搜索码值的索引项，然后从该记录开始向后顺序查询直到找到为止。

      示意图       

稠密索引与稀疏索引引区别

• 稠密索引比稀疏索引更快地定位一条记录。
• 稀疏索引所占空间小，并且插入和删除时所需的维护开销也小。

主键索引

     特点

• 主键索引是唯一索引的特殊类型。
• 数据库表通常有一列或列组合，其值用来唯一标识表中的每一行。该列称为表的主键。
• 在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。
• 在MYSQL中，当你建立主键时，索引同时也已建立起来了。

唯一索引

     特点

• 唯一索引不允许两行具有相同的索引值。
• 如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。

辅助索引

     特点

• 辅助索引，其叶子节点并不包含行记录的全部数据，叶子结点除了包含键值以外，每个叶子结点中的索引行还包含了一个书签，该书签用来告诉存储引擎可以在哪找到相应的数据行，由于innodb引擎表是索引组织表，因此innodb存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

二级索引

     特点

• 一个表中的所有索引除了聚集索引，其他的都是二级索引（secondary index）

覆盖索引

     特点

• 是非聚集复合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段，也即，索引包含了查询正在查找的数据）。
• 如果你想要通过索引覆盖select多列，那么需要给需要的列建立一个多列索引，当然如果带查询条件，where条件要求满足最左前缀原则。
• Innodb的辅助索引叶子节点包含的是主键列，所以主键一定是被索引覆盖的。

单列索引

     特点

• 在某一个字段上建立的索引。
• 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知。

多列索引

     特点

• 基于多个字段而创建的索引就称为组合索引。
• 在适当的情况下，可以将多个索引合并成一个组合索引。

正向索引

     特点

• 正排表是以文档的ID为关键字，表中记录文档中每个字的位置信息，查找时扫描表中每个文档中字的信息直到找出所有包含查询关键字的文档。
• 这种组织方法在建立索引的时候结构比较简单，建立比较方便且易于维护;因为索引是基于文档建立的，若是有新的文档加入，直接为该文档建立一个新的索引块，挂接在原来索引文件的后面。

      示意图

     

反向索引

     特点

• 反转索引时为了解决热块冲突的问题。比如有ID 为1，2，3,4,5，都在索引块的BLOCK A中。当用id =1 访问时，访问的是BLOCK A。当用id =2 访问时，访问的是BLOCK A。这样就会导致索引出现热块（关于热快请参考http://zhuyuehua.iteye.com/blog/1874708）。如果反转后存放，就可能1在BLOCK A，2在BLOCK B很多事务访问同一个块，对同一个块并发操作产生的I/0竞争
• 反向索引能作为避免热点块的一个方法
• 当索引是从序列中取的时候，如果是一般的b-tree 索引，在大量的插入后会导致块的分裂以及树的倾斜，使用reverse key index可以使索引段条目被更均匀的分布
• 如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。
• 不可以将反转键索引与位图索引或索引组织表结合使用。

      示意图

     

正向索引与反向索引的区别

• 100篇文章，你将所有文章都提取出来一定量的tags标签，这就是正向索引。
  你选择其中一个tags标签，然后所有包含这个标签的文章都出来，这就是倒序索引。
• 正向索引就是，每一个页面都包含哪些关键词。
• 倒序索引就是，某一个关键词，都有哪些页面包含。

位图索引

     特点

• 位图索引指的是位图索引(bitmap index)技术，是一类特殊的数据库索引技术，其索引使用bit数组（或称bitmap、bit set、bit string、bit vector）进行存储与计算操作。
• 主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等),
  索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码,
  位置编码中的每一位表示键值对应的数据行的有无.一个块可能指向的是几十甚至成百上千行数据的位置.
• 这种方式存储数据,相对于B*Tree索引,占用的空间非常小,创建和使用非常快.
• 当根据键值查询时,可以根据起始Rowid和位图状态,快速定位数据.
• 当根据键值做and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算,快速得出结果行数据.
• 当select count(XX) 时,可以直接访问索引就快速得出统计数据.

      示意图

     

哈希索引

     特点

• hash索引根据根据一定的hash算法建立索引，比如集合框架中的HashMap。

      示意图

     

B树索引

     特点

• 以二叉树的形式建立的索引结构。
• 其查询效率近似于二分查找。

      示意图

     

R树索引

     特点

• R树是B树在高维空间的扩展，是一棵平衡树。每个R树的叶子结点包含了多个指向不同数据的指针，这些数据可以是存放在硬盘中的，也可以是存在内存中。
• 根据R树的这种数据结构，当我们需要进行一个高维空间查询时，我们只需要遍历少数几个叶子结点所包含的指针，查看这些指针指向的数据是否满足要求即可。这种方式使我们不必遍历所有数据即可获得答案，效率显著提高。

      示意图

     

LSM树索引

     特点

• 在 B+树 基础上， 将读写分离、读操作先内存后磁盘、数据写操作（包括插入、修改、删除也是写）都在内存中进行。到达一定阈值的时候才会刷新到磁盘上。（HBase 刷新到 memStore me） 在大规模情况下，寻道明显比传输低效。
• LSM树原理把一棵大树拆分成N棵小树，它首先写入内存中，随着小树越来越大，内存中的小树会flush到磁盘中，磁盘中的树定期可以做merge操作，合并成一棵大树，以优化读性能。
• LSM树（Log-Structured Merge Tree）存储引擎和B树存储引擎一样，同样支持增、删、读、改、顺序扫描操作。而且通过批量存储技术规避磁盘随机写入问题。当然凡事有利有弊，LSM树和B+树相比，LSM树牺牲了部分读性能，用来大幅提高写性能。

      示意图

     

红黑树索引

     特点

• 红黑树（Red-Black Tree）是二叉搜索树（Binary Search Tree）的一种改进。
• 红黑树的每个节点上的属性除了有一个key、3个指针：parent、lchild、rchild以外，还多了一个属性：color。它只能是两种颜色：红或黑。
• 在JDK源码中, 有treeMap和JDK8的HashMap都用到了红黑树去存储。

      示意图

     

参考

• 红黑树
• LSM树由来、设计思想以及应用到HBase的索引
• R树空间索引
• B树索引和位图索引
• 深入研究B树索引（一）
• B树索引和位图索引的结构介绍
• MySQL B+树索引和哈希索引的区别
• 数据库常见索引解析（B树，B-树，B+树，B*树，位图索引，Hash索引）
• Oracle索引梳理系列（四）- Oracle索引种类之位图索引
• 正排索引和倒排索引
• MySQL优化笔记（三）–索引的使用、原理和设计优化
• JAVA中的数据结构 - 真正的去理解红黑树
• 数据结构 之 索引技术（线性、倒排、动态检索B+、位索引、红黑树）
• 查找（一）史上最简单清晰的红黑树讲解
• 索引基础——B-Tree、B+Tree、红黑树、B*Tree数据结构
• 日志结构的合并树 The Log-Structured Merge-Tree
• LSM和B+树区别
• 二叉树、B树、B+树、B*树、LSM树
• 详解SSTable结构和LSMTree索引
• 复合非聚集索引——列顺序重要么？
• 聚集、非聚集索引、唯一索引、复合索引、系统自建索引
• MYSQL-B+TREE索引原理 （写得很好）
• B+/-Tree原理
• 深入 聚集索引与非聚集索引(一)（写得很好）
• BTree和B+Tree详解（写得很好）
• 聚簇索引和聚簇索引介绍
• 地图索引 R-tree
• 下面的表总结了何时使用聚集索引或非聚集索引（什么时候用什么索引）