深入了解-数据库建索引

提到数据库，少不了索引。 我们先看看索引的定义：[在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容]

我们经常在实际的生产活动中遇到这种情况：where条件没有索引，加一个就好了。实际上真的这么简单吗？

数据最主要或者最频繁的操作就是查询。各种缓存的出现，无非是为了解决数据库查询的瓶颈。举个例子：

select * from t_orangefin_customer  t where t.id = 11888； 这个sql语句是在客户表t_orangefin_customer 获取id为11888的客户记录。常规方法，想获取这个记录，那就遍历这个表，找到id=11888的记录。查找复杂度是O(n),如果表数据量很大，性能是很糟糕的。假如这个表的ID是有序的，那就可以二分查找，那查找的复杂度是0（log2 n）。要满足二分查找，那ID必须是有序的，似乎只要维护这个表的ID 有序，那么查询就快了。但是如果有一天，我需要select * from t_orangefin_customer  t where t.no = 11888,  需要通过另一个编号去查询用户信息，这个时候就没法保证id和no 同时有序了。 怎么办呢 ？

如果是我来设计数据库，那么我会对要查询的条件做一个额外的数据结构，例如，做2个线性表分别对Id,no 进行排序，如果要通过ID来定位数据，则在ID这个有序的线性表进行二分查找，找到了，再通过关联关系直接定位到表数据，返回记录信息。

其实，数据库索引，也是这个原理。只不过，索引的数据结构不是有序线性表那么简单。索引是数据库表数据之外的额外的数据结构，并且用指针关联表的物理数据，需要占用额外的空间，索引的作用是为了提高查询速度。

实际上，大部分关系型数据库索引的数据结构并不是有序线性表这么简单，而是B树，或者B+树。为什么用B+树，我大概说下原因：如果用有序线性表，那么修改，删除记录，性能太低；如果用有序二叉树，这棵树很不稳定（不平衡），最坏的情况会导致查询复杂度O(n)。这里简单介绍下B+树：

B+ 树是一种树数据结构，是一个n叉排序树，每个节点通常有多个孩子，一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。

B+ 树通常用于数据库和操作系统的文件系统中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系统都在使用B+树作为元数据索引。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。

B+树的查询、插入、删除都是O(㏒2 N)级别。 

总结一下： B+树稳定（出现，修改，插入），并且时间复杂度0（log2 N）

同样是log2N的复杂度，为什么B+树比有序线性表要快呢？

当前计算机的瓶颈在IO上，所以一个sql的查询，主要的时间也花在IO上。所以IO的次数决定了sql查询的快慢。

计算机文件系统读取文件，都是按块（block）为单位读取的，block是文件存取的最小单位。目前主流的操作系统一块的大小为

4K，例如win10:

ubuntu 16:

 

假如一个表有1000W数据， 一行记录1K大小，一个块有4条记录，整个表占用1000W/4=250W块空间。

1 如果这个表没有建任何索引（包括主键），表记录总共250W块。则查询一条记录，需要全表扫描（为了简单说明，我们假设一次IO读取一个块，实际上一次IO可以读取多个块。）

找一条记录需要的IO次数为：250W/1 = 250w  次

2 如果这个表建了索引，并且索引是有序链表或者有序二叉树，假如每个索引大小是4B，一个块有4K/4 =1024个索引，

索引空间总共需要1000W/1024=9766块。

找一条记录需要的IO次数为：log2 9766= 13.25  ,14次IO就能定位到索引块，然后块加载到内存，在内存中对块进行二分查找，最终根据索引指针定位到表的记录上，14+1+1=16次（说明：14次定位索引块，1次块加载，一次索引指针加载实际记录）。

3  如果索引用B+树来做，先大概说下B+树结构 ：

B+数所有的关键字都在叶子节点上，且叶子结点本身依关键字的大小自小而大顺序链接。我们要查找关键字，必须到达叶子节点。

如果B+树的度是1000，

到达叶子节点 最快情况需要次数log1000 1000W = 2.33 次；最差情况：log1000/2 1000W = 2.59 。

也就是3次 就可以定位到叶子节点。 叶子节点1000个索引关键字，每个索引关键字大小4B，一个块大小是4K，一个叶子节点1000个索引关键字都在一个块内；一次IO 就能加载到内存，然后在内存对1000个值进行二分查找，最终根据索引指针定位到表的记录上

总共的IO次数：3+1+1=5次。（说明：3次叶子节点定位，1次叶子节点加载，一次索引指针加载实际记录）

 

从上面可以看出，采用B+树的索引数据结构，IO次数大大减少。为什么要用B+树作为索引数据结构？

1 如果树的度足够大，那么树的树的高度（深度）会特别小:H(max)=log ceil(m/2) N, H(min)=log ceil(m) N (H树的高度，m 树的度，N是总的关键字，ceil 向上取整，如 ceil 3/2 = =2)，遍历的节点数就少。非叶子节点数特别少，并且非叶子节点不包含真正的关键字信息，非叶子节点占用空间极小，非叶子节点可以一次性完全加载到内存，减少IO次数。

也就是之前1000W数据 前面3次IO完全可以用内存替代。也就是前面1000W数据只需要2次IO就行了。

2 所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。也就是范围查询可以被连续加载。

3 归根到底还是磁盘结构决定的，通过合理设计树的度，充分利用好block大小、连续IO特点减少IO次数，提高查询效率。

 

如果是组合索引，在B+树中是什么结构呢？

举个例子：

CREATE TABLE `t_orangefin_test` (
  `id` int(11) NOT NULL auto_increment,
  `no` int(11) default NULL,
  `age` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `CompIndex_test` (`no`,`age`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

测试表 有三个字段，id,no,age。

no,age建了复合索引。如果表数据如下

那么复合索引（no,age）在2阶B+树结构如下：

所有的关键字都在叶子节点，如果我们查询：

SELECT * FROM  t_orangefin_test  t  WHERE t.no=X  |  t.no > X  | t.no<X  这个时候 是可以走索引的，B+树是从左边开始排序。

反过来，如果 SELECT * FROM  t_orangefin_test  t  WHERE t.age=X  ,这时候 是没法走索引的。

如果建立复合索引，最好就是 SELECT * FROM  t_orangefin_test  t  WHERE t.no=X and t.age=Y;

 

通过符合索引，我能可以类比到字段类型为字符串的索引。

select  * from t_test  t where t.name like '%abc';  如果name建 了索引，这种情况是没法走索引的；t.name like'abc%' 是可以走索引的。

 

通过上面的分析得出如下

总结：

1  索引建立是需要占用额外的空间，索引数据结构是需要维护的，保证数据结构的正确性。建立索引会增加磁盘消耗，降低

插入，删除，修改的性能。因为增删改会破坏索引数据结构，需要额外维护。

2  表数据量特别大，才适合建立索引，如果表记录很少，建立索引反而减低性能。

3  索引建立要在选择性比较好的字段，如果有个字段是学历，总共就几种类型，这种情况建立索引会降低性能。

4  在业务设计上，尽可能的减少查询维度，也就是索引尽量要少，索引列字段类型尽量短。建立索引的字段，尽可能减少增删改操作。

5 索引列不能参与计算，我们知道，索引是通过B+树节点值对比进行查找的。如：select  * from t_test  t where   CONCAT(name,'1111')='XXX1111' ; 如果name有索引，也不会走索引，因为CONCAT(name,'1111') 在B+树没法进行比较。

 

参考文章：

https://baike.baidu.com/item/B+%E6%A0%91/7845683

点击打开链接