第5章 索引与算法

第5章 索引与算法

5.1 InnoDB存储引擎索引概述

InnoDB存储引擎支持两种常见的索引，一种是B+树索引，另一种是哈希索引。
InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。
B+树索引就是传统意义上的索引，这是目前关系型数据库系统中最常用、最有效的索引。B+树索引的构造类似于二叉树，根据键值(key value)快速找到数据。需要注意的是，B+树中的B不是代表二叉(binary)，而是代表平衡(balance)，因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。
B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入内存，再在内存中进行 查找，最后得到查找的数据。

5.2 二分查找法

二分查找法(binary search)也称为折半查找法。用来查找一组有序的记录数组中的某一记录。

5.3 平衡二叉树

B+树是通过二叉查找树，再由平衡二叉树，B+树演化而来。
二叉树的特点是：任何一个节点的值都大于它的左子节点，小于它的右子节点。
平衡二叉树：符合二叉查找树的定义，其次必须满足任何节点的左右两个子树的高度最大差为1.
平衡二叉树对于查找的性能是比较高的，但不是最高的，只是接近最高性能。要达到最好的性能，需要建立一棵最优二叉树，但是最优二叉树的建立和维护需要大量的操作，因此建立平衡二叉树就可以了。

5.4 B+树

B+树由B树和索引顺序访问方法演化而来，但是在实际使用过程中几乎已经没有使用B树的情况了。
B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层叶节点中，各叶节点指针进行连接。

5.7 B+树索引

B+树索引其本质就是B+树在数据库中的实现，但是B+树在数据库中有一个特点就是其高扇出性，因此在数据库中，B+树的高度一般都在2~3层，也就是对于查找某一键值的行记录，最多只需要2~3次IO。
数据库中的B+树索引可以分为聚集索引(clustered index)和辅助聚集索引(secondary index)。但是不管是聚集还是非聚集索引，其内部都是B+树的，即高度平衡的，叶节点存放着所有的数据，聚集索引与非聚集索引不同的是，叶节点存放的是否是一整行的信息。

聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存储。而聚集索引就是按照每张表的主键构造一棵B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。
数据页上存放的是完整的行记录，而在非数据页中，存放的仅仅是键值以及 指向数据页的偏移量，而不是一个完整的行记录。
聚集索引的存储并不是物理上的连续，而是逻辑上连续的。需要注意两点：一是数据页是通过双向链表链接，页按照主键的顺序排列。二是每个页中的记录也是通过双向链表进行维护，物理存储上可以同样不按照主键存储。

mysql> explain select *from t order by a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

rows代表的是一个预估值，不是确切的值。如果想要确切的值，可以使用：

select count(*) from t order by a\G

辅助索引

对于辅助索引(非聚集索引)，叶级别不包含行的全部数据。叶节点除了包含键值以外，每个叶级别中的索引行中还包含了一个书签(bookmark)，该书签用来告诉InnoDB存储引擎，哪里可以找到与索引相对应的行数据。因为InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后在通过主键索引来找到一个完整的行记录。

B+树索引的管理

索引的创建和删除可以通过两种方法，一种是ALTER TABLE，另一种是CREATE/DROP INDEX。

mysql> show index from t\G
*************************** 1. row ***************************
        Table: t
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: t
   Non_unique: 1
     Key_name: b
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.01 sec)

表t上有2个索引，一个主键索引，一个辅助索引。
Non_unique：非唯一的索引，可以看到pk是0，因为必须是唯一的。
Seq_in_index：索引中该列的位置，如果看联合索引idx_a_b就比较直观了。
Key_name：索引的名称，我们可以通过这个名称来drop index。
Column_name：索引的列。
Collation：列以什么方式存储在索引中。可以是‘A’或者NULL。B+树的索引总是A，即排序的。如果使用Heap存储引擎，并且建立了Hash索引，这里会显示NUll。因为Hash根据Hash桶来存放索引数据，而不是对数据进行排序。
Cardinality：索引中唯一值的数目估计值。 Cardinality/行数应尽可能接近1，如果非常小，那么需要考虑是否还需要建立这个索引。
Sub_part：是否是列的部分被索引。有时候可以只索引一个列的开头部分数据。如b列为varchar(8000)，可以只索引前100个字段。
Packed：关键字如何被压缩，如果没有被压缩，则为NULL。
Null：是否索引的列含有NULL值，如果允许有NULL值，则为YES。
Index_type：索引的类型。
Comment：注释

Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值不是实时更新的，并非每次索引的更新都会更新该值，它只是一个大概值，可以使用ANALYZE TABLE命令，来更新这个值。如果得到的值不是预想中的，可以再次使用上次命令。

5.6 B+树索引的使用

什么时候使用B+树索引

访问表中很少一部分行的时候，使用B+树索引才有意义。

顺序读、随机读与预读取

顺序读(Sequntial Read)是指顺序地读取磁盘上的块(Block)，随机读(Random Read)是指访问的块不是连续的，需要磁盘的磁头不断移动。
在数据库中，顺序读是指根据索引的叶节点数据就能顺序地读取所有的行数据。这个顺序只是逻辑地顺序读，在物理磁盘上可能还是随机读取。
随机读，一般是指访问辅助索引叶节点不能完全得到结果，需要根据辅助索引叶节点中的主键去找实际行数据。因为一般来说，辅助索引和主键所在的数据段不同，因此访问是随机的方式。随机读的性能一般远低于顺序读。
为了提高读取的性能，InnoDB存储引擎引入了预读取技术(read ahead 或prefetch)。预读取是指通过一次IO请求将多个页预读取到缓冲池中，并且估计预读取的多个页马上会被访问。
InnoDB存储引擎有两个预读方法，称为随机预读取(random read ahead)和线性预读取(linear read ahead)。随机预读取是指当一个区(64个连续页)中13个页在缓冲区中，并在LRU列表的前端(即页是被频繁地访问)，则InnoDB存储引擎会将这个区中剩余的所有野预读到缓冲区。线性预读取基于缓冲池中页的访问模式，而不是数量。如果一个区中的24个页被顺序地访问了，则InnoDB存储引擎会读取下一个区的所有页。
但InnoDB存储引擎的预读取技术在实际测试下去非常糟糕，随机访问的预读取已经被取消，而线性的预读取还是保留了，并且加入了innodb_read_ahead_threshold参数，该参数表示一个区中的多少页被顺序访问时，InnoDB存储引擎才启用预读取，预读下一个区的所有页。

辅助索引的优化使用

辅助索引的叶并不包含完整的行信息。因此，InnoDB存储引擎总是会先从辅助索引的叶节点判断是否能得到所需的数据。

#强制使用主键索引
select *from t force index(primary);

联合索引

联合索引是指对表上的多个列做索引。

alter table t add key idx_a_b(a,b);

覆盖索引

InnoDB存储引擎覆盖索引(covering index)，或称索引覆盖(index coverage)，即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。使用覆盖索引的好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
使用覆盖索引的时候，extra会显示using index

优化器选择不使用索引的情况

在某些情况下，当执行explain时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据，这种情况多发生于范围查找，join操作。
如果确定使用索引可以带来更好的效果可以使用force index来强制使用某个索引。

INDEX HINT

MySQL数据库支持INDEX HINT(索引提示)，显示地告诉优化器使用哪个索引。以下两种情况可能需要用到INDEX HINT。

• MySQL数据库的优化器错误地选择了某个索引，导致SQL语句运行得很慢。这种情况很少见，因为优化器一般都非常有效和正确。
• 某个SQL语句可以选择的所有非常多，这时优化器选择执行计划时间开销可能会大于SQL语句本身。

INDEX HINT语法

tbl_name [[AS] alias][index_hint_list]
index_hint_list:
index_hint[,index_hint]...
index_hint:
USE{INDEX|KEY}
FOR ...
index_list:
index_name[,index_name]...

#例子：
select *from t use index(a) where a=1 and b=2;

虽然指定了使用a索引，但是优化器实际可能采用的是表扫的方法，因此use index只是告诉优化器可以选择该索引，而实际上优化器还是会根据自己的判断进行选择。如果要强制使用某个索引，可以使用force index。

Multi-Range Read

MySQL数据库5.6版本开始支持Multi-Range Read(MRR)优化，MRR优化的目的就是减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，可为IO-bound类型的SQL查询语句带来性能的极大提升。MRR优化适用于range、ref和eq_ref类型的查询。
MRR优化有以下几个好处：

• 使得数据访问变得较为顺序，在查询辅助索引时，先对得到的查询结果按照主键进行排序，并按照主键排列的顺序进行书签查找。
• 减少缓冲池中被替换的次数。
• 批量处理对键值的查询操作。

对于InnoDB和MyISAM存储引擎的范围查询和联接查询，MRR的工作方式如下：

• 将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
• 将缓存中的键值根据rowID进行排序。
• 根据rowID的排序顺序来访问实际的数据文件。

若启用MRR特性，则除了会在Extra列看到Using index condition外，还会看见Using MRR。
是否启用MRR优化可以通过参数optimizer_switch中的标记(flag)来控制。当启用mrr为on时，表示启用MRR优化。mrr_cost_based标记表示是否通过cost based的方式来选择是否启用mrr。若将mrr设为on，mrr_cost_based设为off，则总是启用MRR优化。下面的语句可以将MRR优化总是设为开启状态：

set @@optimizer_switch = 'mrr=on,mrr_cost_based=off';

Index Condition Pushdown

和MRR一样，Index Condition Pushdown(ICP)同样是MySQL5.6开始支持的一种根据索引进行查询的优化方式。之前的MySQL版本不支持ICP，当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录。在支持ICP后，MySQL数据库会在取出索引的同时，判断是否可以进行where条件的过滤，即将where的部分过滤操作放在了存储引擎层。在某些查询中，ICP会大大减少上层SQL层对于记录的索取(fetch)，从而提高数据库的整体性能。
当优化器选择ICP优化时，可在执行计划的Extra列看到using index condition 提示。

5.7 哈希算法

InnoDB存储引擎中自适应哈希索引使用的是散列表(Hash Table)的数据结构。但是散列表不只存在于自适应哈希中，在每个数据库中都存在。

哈希表

哈希表也称为散列表，有直接寻址表改进而来。
在哈希函数中，可能将两个关键字映射到同一个槽上，一般称这种情况为发生了碰撞(collision)，数据库中一般采用最简单的碰撞解决技术–链接法(chaining)。
在链接法中，把散列到同一槽中的所有元素都放在一个链表中，每个槽都有一个指针，指向由所有散列到该槽的元素构成的链表头。如果不存在这样的元素，则该指针为NULL。
数据库中一般采用除法散列的方法。在用来设计哈希函数的除法散列法中，通过取k除以m的余数，来将关键字k映射到m个槽的某一个中，即哈希函数为：
h(k) = k mod m

InnoDB存储引擎中的哈希算法

InnoDB存储引擎使用哈希算法对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。对于缓冲池页的哈希表来说，在缓冲池中的page页都有一个chain指针，它指向相同哈希函数值的页。而对于除法散列，m的取值为略大于2倍的缓冲池页数量的质数。
InnoDB存储引擎对于页是怎么进行查找的呢？
InnoDB存储引擎的表空间都有一个space号，而我们要查的应该是某个表空间的某个连续的16KB的页，即偏移量offset。InnoDB存储引擎将space左移20位，然后加上这个space的offset，即关键字K=space<<20+space+offset，然后通过除法散列到各个槽中。

自适应哈希索引

可以通过参数innodb_adaptive_hash_index来禁用或启动此特性。
show engine innodb statusl中可以看到自适应哈希的相关信息。
其中哈希索引只能用来搜索等值的查询，对于其他类型的查找则不能使用哈希索引，因此会有non-hash searches/s。
hash searches：non-hash searches可以大概知道使用哈希索引后的效率。