浅谈索引（一）

什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。

索引的常见模型

1.哈希表

哈希表是一种以键-值（key-value）存储数据的结构。哈希的思路就是，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置上。不可避免的，多个key值经过哈希函数的换算，会出现在同一个值得情况。处理这种情况的一种方式就是拉出一个链表。

假设，现在维护一个身份证信息和姓名表，需要根据身份证号查找对应的名字，这时对应的哈希索引的示意图如下：

图中，user2和user4根据身份证号算出来的哈希值是一样的都是N，但是没关系，后面会跟上一个链表。假设，这时候要查user2身份证号对应的名字，处理步骤是：首先，将id_card_n2通过哈希函数计算哈希值是N，然后顺序遍历N所对应的链表，找到user2。

需要注意的是，图中四个id_card_n的值不是顺序递增的，这样在增加新的user时会很快速的在后追加，但是缺点是，因为不是有序，所以做区间查询速度会很慢。查询某个区间的值，就需要全部遍历一边。所以哈希表这种结构只有适用于等值查询的场景，比如Memcached及其他一些Nosql引擎。

2.有序数组

有序数组在等值查询和范围查询场景中都非常优秀。还是上面的例子，如果用有序数组实现的话，如下图：

 

这里假设身份账号没有重复，且按照顺序递增的方式保存的。这时候如果需要找到id_card_user2对应的名字，用二分法就可以快速的找到了。

同时很显然，这个索引结构支撑范围查询。你要查询身份证号在[id_card_x,id_card_y]区间的user,可以先用二分法找到id_cara_x(如果不存在id_card_x,就找到第一个大于id_card_x的值)，然后向有遍历，直到找到第一个大于id_card_y的退出循环。

如果仅仅看查询效率，有序数组是最好的数据结构了。但是在需要更新的时候就麻烦了，往中间插入一个记录时就必须得挪动后面的所有记录，成本太高。所以，有序数组索引只适用于静态存储引擎，比如保存2017年某个城市人口信息，这类不会再修改的数据。

3.二叉搜索树

特点：每个节点的左儿子小于父节点，父节点小于右儿子。这样查询复杂度和更新复杂度都是O(log(N))。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树的搜索效率最高，但是实际上大多数数据在数据库存储却并不适用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

可以想象一下一颗100万节点的平衡二叉树，树高20.一次查询可能需要访问20个数据块，每次访问数据块需要从磁盘寻址，在机械硬盘时代，这个动作大约需要10ms左右的时间。也就是说对于一个100万行的表，如果使用二叉树来存储，单独访问一行可能需要20个10ms的时间，这个查询可真够慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用"N叉树"。这里的“N”取决于数据块的大小。

一InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的的3次方的值，这已经差不多17亿了。考虑到树根的数据块总是在内存中的，一个10亿行的表的一个整数字段索引，查找一个值最多需要访问3次磁盘。其实，树的第二层也有大概率在内存中，那么访问磁盘的平均次数就更少了。

“N叉树”由于在读写上的性能优点，以及适应磁盘的访问模式，已经广泛应用在数据库引擎中了。

平衡二叉树动态模拟：https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

InnoDB索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。每一个索引在InnoDB中都对应了一棵B+树。

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上建有索引。建表语句是：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中R1~R5的（ID,k）值分别是（100,1）、（200,2）、（300,3）、（500,5）和（600,6），两个索引对应的树示意图如下。

从图中不难看出，根据叶子节点的内容，索引分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB中，主键索引也被称为聚簇索引。

非主键索引的叶子节点存的是主键的值。在InooDB中，非主键索引也被称为二级索引。

那么，基于主键索引查询和非主键索引查询有什么区别呢？

（1）如果语句是“select * from T where ID = 500”,即主键索引查询，则只需要搜索ID这课B+树；

（2）如歌语句是"select * from T where k  = 5",即普通索引查询，则需要先搜索k索引这个树找到ID=500，再在ID索引树上搜索一次拿到整行数据。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一颗索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护

B+树为了维护索引有序性，在插入新值时候需要做必要的维护。以上面这个图为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。更糟糕的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下性能自然会受到影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个也的数据，现在分到两页中了，空间整理利用率下降了约50%。当然有分裂就有合并。当相邻两个页数据由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂的逆过程。

基于索引维护说明一下建表规范：建表一定要有自增主键，当然事物绝对，什么场景下不需要自增主键？

自增主键是指在自增列上定义主键，在建表语句中一般这样定义的：NOT NULL  PRIMARY KEY AUTO_INCREMENT.。插入新纪录的时候可以不知道ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。

也就是说，自增主键的插入模式，正符合了我们前面提到的递增插入的场景。每次插入一条新纪录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而又业务逻辑的字段做主键，则往往不能保证有序插入，这样写数据成本相对较高。除了考虑性能外，我们还可以从存储空间的角度看。假设你的表中有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点都是主键的值。如果使用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节。

显然，主键长度越小，普通索引的叶子节点越小，普通索引占用的空间也就越小。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

有没有什么场景适合业务字段直接做主键的呢？还是有的，比如，有些业务场景需求是这样的：

1.只有一个索引；

2.该索引必须是主键索引。

这就是典型的K-V场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

这时候就要优先考虑“尽量使用主键索引查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

重建索引

重建普通索引

alter table T drop index k;
alter table T add index(k);

重建普通索引这种做法是合理的。

重建主键索引

alter table T drop primary key;
alter table T add primary key(id);

重建主键索引这个过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以这两个语句，第一个语句白做了。可以用语句“alter table T engine=InnoDB”代替。