[数据库整理]——索引

(5) 全文索引：主要用来查找文本中的关键字，不是直接与索引中的值相比较，像是一个搜索引擎，配合match against使用，现在只有CHAR、VARCHAR、TEXT上可以创建全文索引。在数据量较大时，先将数据放在一张没有全文索引的表里，然后再利用create index创建全文索引，比先生成全文索引再插入数据快很多。

1.4 索引优点

1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

3.可以加速表和表之间的连接。

4.为用来排序或者是分组的字段添加索引可以加快分组和排序顺序。

5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

1.5 索引缺点

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

1.6 何时使用索引

1.主键，unique字段；

2.和其他表做连接的字段需要加索引；

3.在where里使用＞，≥，＝，＜，≤，is null和between等字段；

4.使用不以通配符开始的like，where A like 'China%'；

5.聚集函数MIN()，MAX()中的字段；

6.order by和group by字段；

1.7 何时不使用索引

1.表记录太少；

2.数据重复且分布平均的字段（只有很少数据值的列）；

3.经常插入、删除、修改的表要减少索引；

4.text，image等类型不应该建立索引，这些列的数据量大（假如text前10个字符唯一，也可以对text前10个字符建立索引）；

5.MySQL能估计出全表扫描比使用索引更快时，不使用索引；

1.8 索引何时失效

1.组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引；

2.like未使用最左前缀，where A like '%China'；

3.搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引；

4.or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）

5.如果列类型是字符串，要使用引号。例如where A='China'，否则索引失效（会进行类型转换）；

6.在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

1.9 性别为什么不适应用建立索引

因为访问索引需要付出额外的IO开销，从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO。假如要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了。但如果是从100 万行数据中取50万行数据，就比如性别字段，那你相对需要访问50万次索引，再访问50万次表，加起来的开销并不会比直接对表进行一次完整扫描小。

2 索引实现原理（B树、B+树）

索引的实现通常采用B树或其变种B+树作为索引结构。

2.1 B树(B-树)定义

1.定义任意非叶子结点最多只有M个儿子；且M>2；

2.根结点的儿子数为[2, M]；

3.除根结点以外的非叶子结点的儿子数为[M/2, M]；

4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）

5.非叶子结点的关键字个数=指向儿子的指针个数-1；

6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的

子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；

8.所有叶子结点位于同一层；

如：（M=3）

B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

2.2 B树的特性

1.关键字集合分布在整颗树中；

2.任何一个关键字出现且只出现在一个结点中；

3.搜索有可能在非叶子结点结束；

4.其搜索性能等价于在关键字全集内做一次二分查找；

由于限制了除根结点以外的非叶子结点，至少含有M/2个儿子，确保了结点的至少利用率，其最低搜索性能为：O(logN)。所以B树的性能总是等价于二分查找（与非叶子结点最多子树个数M值无关）。

2.3 B+树定义

B+树是B树的变体，也是一种多路搜索树：

1.其定义基本与B树同，除了：

2.非叶子结点的子树指针与关键字个数相同；

3.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树

（B树是开区间）；

5.为所有叶子结点增加一个链指针，相邻的叶子结点顺序链接（相当于顺序链表）；

6.所有关键字都在叶子结点出现；

如：（M=3）

B+的搜索与B树也基本相同，区别是B+树只有达到叶子结点才命中（B树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；

2.4 B+树的特性（与B树相比的不同点）

1.内部节点中，关键字的个数与其子树的个数相同，不像B树中，子树的个数总比关键字个数多1个；

2.所有指向文件的关键字及其指针都在叶子节点中，而在B树中，有的指向文件的关键字是在内部节点中，所以B树的搜索有可能在非叶子结点结束。即在B+树中，内部节点仅仅起到索引的作用，在搜索过程中，如果查询和内部节点的关键字一致，那么搜索过程不停止，而是继续向下搜索这个分支，直到叶子结点；

3.叶子节点之间，增加了链表，扫描获取所有关键字信息，不再像B树那样需要中序遍历，而只需遍历叶子结点即可，范围查找效率更高。

2.5 为什么使用B树（B+树）做为索引

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。索引查找过程中就要产生磁盘I/O消耗，由于存储介质的特性，磁盘本身存取就比主存慢很多，因此为了提高效率，要尽量减少磁盘I/O。

为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理。

局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。

根据B树的定义，可知检索一次最多需要访问h个结点，h为B树高度：

$h\leq log_{d}\frac{n+1}{2}$

其中，d为最小度数\出度（d >=2），n为关键字个数（n >=1）。

利用磁盘预读原理，将一个结点的大小设为等于一个页面，这样每个结点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B树还需要使用如下技巧：每次新建结点时，直接申请一个页面的空间，这样可以保证一个结点的大小等于一个页面，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。B树中一次检索最多需要h次I/O（根结点常驻内存），渐进复杂度为O(h) = O(logdn)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小。

综上所述，用B树作为索引结构效率是非常高的。

而红黑树结构，h明显要深得多。由于逻辑上很近的结点（父子结点）物理上可能离得很远，无法利用局部性原理。所以即使红黑树的I/O渐进复杂度也为O(h)，但是查找效率明显比B树差得多。

B+Tree更适合外存索引，是和内结点出度d有关。从上面分析可以看到，d越大索引的性能，度的上限取决于结点内key和data的大小。由于B+Tree内结点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

2.6 相比B树，索引采用B+树的优势

1.B+树更有利于对数据库的扫描B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题，而 B+ 树只需要遍历叶子节点就可以解决对全部关键字信息的扫描，所以对于数据库中频繁使用的range query，B+树有着更高的性能。

2.B+树的磁盘读写代价更低 B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 I/O 读写次数也就降低了。

3.B+树的查询效率更加稳定由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3 MySQL索引实现

在MySQL中，不同存储引擎对索引的实现方式是不同的，下面讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

3.1 MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶结点的data域存放的是数据记录的地址。下面是MyISAM索引的原理图：

这里设表一共有三列，若以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。若在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做非聚集索引。

3.2 InnoDB索引实现

InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶结点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引：