索引介绍、原理与分类

介绍

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。 而要提高查询效率，创建索引就是其中一个非常重要的手段。

那什么是索引呢？

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。数据库除了数据本身之外,还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。

通俗点理解索引就如同生活中的字典、列车时刻表、图书目录一样，原理都是通过不断缩小想要获得的数据范围来筛选出最终想要的结果，把本来是随机查询的事件变成有序的事件。

由于索引记录着表记录的某种顺序，所以索引本身一般也很大，不可能全部存储在内存中，最终索引往往以文件形式存储在硬盘上。

综合以上，可以简单的把索引理解为一种排好序的快速查找数据的数据结构。

优势

  • 类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

劣势

  • 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。
    因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，
    都会调整因为更新所带来的键值变化后的索引信息
  • 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

原理

任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来）。

如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树叶子节点为什么要有序？

从上图可以看出来， 所有的数据都在叶子节点，且每一个叶子节点都带有指向相邻节点的指针，形成了一个有序的链表。 这么做的原因也很简单， 是为了范围查询。 比如说select * from Table where id > 1 and id < 100; 当找到1后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

b+树的性质

  1. 索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
  2. 索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
  3. 查询效率稳定: 数据全都存在叶子节点上，在数据库中，b+树的高度又一般都在24层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，因为当前一般的机械硬盘每秒至少可以做100次IO，24次的IO意味着查询时间只需要0.02~0.04秒, 所以随机访问时，io此时都是一样的，时间也都差不多。
  4. 排序能力强: b+tree的叶子节点形成了一个有序双向链表, 在范围查询的时候，会沿着链表扫描数据，不会再返回上一层节点。

分类

数据库中的B+树索引可以分为clustered index(聚集索引）和secondary index（辅助索引, 次要索引,非聚集索引），

聚集索引:

聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在很多情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

辅助索引:

表中除了聚集索引外其他索引都是辅助索引，辅助索引的叶子节点不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。根据这个原理,"覆盖索引"就是一种优化查询的很重要的手段之一.

综上所述聚集索引与辅助索引的关系如下所示.

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息,即聚集索引存储的数据页, 辅助索引存储的是主键索引键

mysql中的常用索引

普通索引: 最基本的索引，它没有任何限制。

唯一索引：

  • 唯一索引: 与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  • 主键索引： 特殊的唯一索引，一个表只能有一个主键，不允许有空值, 一般是在建表的时候同时创建主键索引。

联合索引： 多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，使用组合索引时遵循最左前缀集合。上述三种索引都支持使用联合索引，及联合主键索引，联合唯一索引，联合普通索引。

创建与删除索引

# 方法一：创建表时
CREATE TABLE 表名 (
     字段名1  数据类型 [完整性约束条件…],
     字段名2  数据类型 [完整性约束条件…],
     [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
     [索引名]  (字段名[(长度)]  [ASC |DESC]) 
);

# 方法二：CREATE在已存在的表上创建索引
CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
      ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

# 方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
     索引名 (字段名[(长度)]  [ASC |DESC]) ;
                             
# 删除索引：
DROP INDEX [索引名] ON 表名字;

# 查看索引：
SHOW INDEX FROM table_nameG

使用索引的情况

  • 主键自动建立唯一索引
  • 频繁作为查询的条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 单间/组合索引的选择问题（在高并发下倾向创建组合索引）
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
  • 查询中统计或者分组字段

不使用索引的情况：

  • 表记录太少
  • 经常增删改的表,频繁更新的字段不适合创建索引(因为每次更新不单单是更新了记录还会更新索引，加重IO负担)
  • Where条件里用不到的字段不创建索引
  • 数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。
    注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yscl/p/12044365.html