INNODB索引与算法

在之前的博文中简单提到了索引的分类与索引的可选择性查看：Click HERE

这片博客主要包含内容：索引组织表，索引算法B+树简单介绍

索引组织表

在innodb存储引擎中，表都是根据主键顺序组织存放的，使用这种存储方式的表就叫做索引组织表（index organized table 简称IOT表）。

在innodb存储引擎中，每张表都有个主键（primary key），如果创建表是没有显式的定义主键，则INNODB存储引擎会按如下方式选择或创建主键。

首先判断表中是否有非空唯一索引，如果有，则该列即主键。
如果不符合上述条件，INNODB存储引擎会自动创建一个6字节大小的指针。

当表中有多个非空的唯一索引时，INNODB存储引擎将选择建表时第一个定义的非空唯一索引为主键。这里需要说明的是，主键的选择根据的是定义索引的顺序而不是建表时列的顺序。

CREATE TABLE t1 (

    a INT NOT NULL,

    b INT NULL,

    c INT NOT NULL,

    d INT NOT NULL,

    UNIQUE KEY (b),

    UNIQUE KEY (d),

    UNIQUE KEY (c)

);

insert into t1 select 1,2,3,4;

insert into t1 select 5,6,7,8;

insert into t1 select 9,10,11,12;

#上面创建了表， 并且创建了索引，注意索引的顺序，然后插入了数据。
#使用字段_rowid可以查看表中的主键，_rowid只可以用来查看单列索引的主键。

#注意主键非空的唯一索引

mysql> select a, b, c, d, _rowid from t1;

+---+------+----+----+--------+

| a | b    | c  | d  | _rowid |

+---+------+----+----+--------+

| 1 |    2 |  3 |  4 |      4 |

| 5 |    6 |  7 |  8 |      8 |

| 9 |   10 | 11 | 12 |     12 |

+---+------+----+----+--------+

3 rows in set (0.00 sec)

mysql>

这里提到INNODB存储引擎是索引组织表？那么在INNODB的内部是如何使用主键将表组织起来的呢？

INNODB存储引擎概述

存储引擎的索引分类（安装索引的内部实现不同）：

B+树索引
哈希索引（INNODB是自适应哈希索引）
全文索引

B+树索引就是传统意义上的索引，也就是上面提到过那种类型的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引。B+树索引的构造类似于二叉树。

哈希索引，INNODB存储引擎是自适应的，INNODB存储引擎会根据表的使用情况自动为表生成哈希索引，不能认为干预是否在一张表中生成哈希索引。

全文索引，会在后面讲述。

B+树索引

B+树索引并不能找到一个给定键值的具体的行，只能找到这个记录所在的数据页。然后把这个数据页读到内存（innodb_buffer_pool_size）中,然后找到其中的记录。

一个问题：把对应的页读到内存中，那么是如何在这个页中找到对应的记录呢？

上面提到到INNODB时索引组织表，是按照主键的顺序排放的，这里按照顺序是按照逻辑顺序存放的，每个页之间通过双向链表链接。但是数据在一个物理页上却是物理有序的。也就是说，读到内存中的这个页是物理有序，这时候我们要在这个有序的队列中找到想要的记录，就会很方便了。但是这里具体是怎么查找的？INNODB使用的是二分查找法。

二分查找法：

二分查找法也称折半查找法，用来查找一组有序的记录数组中某一记录，其基本思想是：将记录按有序化排列，在查找过程中采用跳跃式方式查找，即先以有序数列的中点位置为比较对象，如果要找的元素值小于该元素中点元素，则将待查序列缩小为左半部分，否则为右半部分。通过一次比较，将查找区间缩小一半。

通过一个例子说明二分查找法：

一个有序数列如下：

5  10  19  21  23   25    27  31   33

寻找31这个数字：

第一次找到中间数23,

31>23, 所以第二次要在后半段查找也就是23 25 27  31  33中查找。

然后重复上面的步骤。

注意：二分查找法通过降低比较的次数，也就是降低的是cpu的使用。

#!/usr/bin/env python

#*-* coding:utf -8 *-*

#二分法查找数值

import sys

import random

def UnsortList():         ###如果没有指定参数，随机生成一个序列

    list = []

    long = random.randint(0,100)

    for i in range(long):

        list.append(random.randint(0,10000))

    return list

def BinarySearch(list, mark, low=0,uplow=None):   #二分法查找

    if not uplow:

        uplow = len(list) -1

    if low == uplow:

        assert mark == list[uplow]

        return uplow

    else:

        mid = (low + uplow) // 2

        if mark > list[mid]:

            return BinarySearch(list, mark,mid+1,uplow)

        else:

            return  BinarySearch(list,mark,low,uplow=mid)    

def SuijiMark(list):            ###在列表中随机挑选一个要查找的数据

    l = len(list)

    mark = list[random.randint(0,l) - 1]

    return mark

def main():           ####主函数

    Ulist = []

    print "1:随机产生列表，验证二分法"

    print "2:用户自己输入数值生成列表，验证二分法"

    answer = input("请输入对应的数字: ")

    if answer == 1:

        Ulist = UnsortList()

        mark = SuijiMark(Ulist)

        print "The list is %s" % Ulist

        print "The mark is %s" % mark

        print "The len of the list is %s " % len(Ulist)

    elif answer == 2:

        lang = input("请输入列表长度: ")      ##根据输入的数值，组成列表

        for i in range(lang):

            Ulist.append(input("请输入列表第%d个值：" % (i + 1)))

        mark = SuijiMark(Ulist)

        print "the list is %s" % Ulist

        print "the mark is %s" % mark

    else:

        print "请输入合法的数字"

    Ulist.sort()

    index = BinarySearch(Ulist, mark)

    print "The index %s is %s" % (index, mark)

if __name__ == "__main__":

    main()

用python写的二分查找法，python2.6版本

数据页读到内存中之后，INNODB就是根据二分查找法在指定的数据页中，查找对应的记录。

二叉树

在介绍B+树前，需要先了解一下二叉树。B+树是通过二叉树，再由平衡二叉树，B树演化而来的。

一个简单的二叉树如下图：

在一个二叉树中，每个节点最多只能有两个分支，每个节点与其子节点又构成一棵二叉树。二叉树中的左子节点总是小于根节点，右子节点总是大于根节点。因此二叉树中，左子节点<根节点<右子节点。

二叉树有三种遍历方法，中序遍历，前序遍历，后序遍历。

上面二叉树中序遍历的结果为： 2 3 5 6 7 8.

在二叉树中查找一个数据时，先与根节点比较，若是大于根节点，则在右子树继续比较，若是小于根节点，则选择左子树继续比较。

对上图中这棵二叉树进行查找，如查找键值为5的记录，先找到根，其键值是6，6大于5，因此查找6的左子树，找到3；而5大于3，再找到其右子树；一共找了3次。如果按2、3、5、6、7、8的顺序来找同样需要3次。用同样的方法再查找键值为8的这个记录，这次用了3次查找，而顺序查找需要6次。

顺序查找需要的次数平均为：（1+2+3+4+5+6）/6=3.3次。

二叉查找法需要的平均次数： (1+2+2+3+3+3)/6=2.3次。

当数据量比较大时，这个差值就会比较高了。

二叉查找树可以任意构造，同样是2、3、5、6、7、8这六个数字，也可以按照下图的方式建立二叉树查找树。

这样的二叉树的平均查找为：（1+2+3+4+5+5）/6=3.16，这个查找法和顺序查找就差不多，显然效率比较低。

因此若想最大性能地构造一棵二叉查找树，需要这棵二叉查找树是平衡的，从而引入了新的定义–平衡二叉树，或称为AVL树。

平衡二叉树的定义如下：首先符合二叉查找树的定义，其次必须满足任何节点的两个字数的高度最大差为1.显然，上图不满足平衡二叉树的定义。平衡二叉树的查找性能是比较高的，但不是最高的，只是接近最高性能。最好的性能需要建立一棵最优二叉树，但是最优二叉树的建立和维护需要大量的操作，因此，用户一般只需要建立一棵平衡二叉树即可。

平衡二叉树的查询速度的确很快，但是维护一棵平衡二叉树的代价是非常大的。通常来说，需要1次或多次左旋和右旋来得到插入活更新后树的平衡性。如下图中需要多次旋转的平衡二叉树示例。（这个旋转不太理解，但inside君的书中是这样说的，先写这里）

上图列举了向一棵平衡二叉树插入一个新的节点后，平衡二叉树需要做的旋转操作。除了插入操作，还有更新和删除操作，不过这和插入没有本质的区别，都是通过左旋或右旋来完成的。因此对一棵平衡树的维护是有一定开销的，不过平衡二叉树多用于内存结构对象中，因此维护的开销相对较小。然后对于MySQL这种依靠磁盘来完成数据的存储跟查询工作的，无法使用这种平衡二叉树。

随着数据库中数据的增加，索引本身大小随之增加，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。可以想象一下一棵几百万节点的二叉树的深度是多少？如果将这么大深度的一颗二叉树放磁盘上，每读取一个节点，需要一次磁盘的I/O读取，整个查找的耗时显然是不能够接受的。那么如何减少查找过程中的I/O存取次数？

一种行之有效的解决方法是减少树的深度，将二叉树变为m叉树（多路搜索树），而B+Tree就是一种多路搜索树。

二叉树的是一个数据结构中一个很重要的概念，这里只是一个简单引用。

B+树

B+树和二叉树、平衡二叉树一样，都是经典的数据结构。B+树由B树（就是B-树）和索引顺序访问方法演化而来，但是在现实使用过程中几乎已经没有使用B树的情况了。其实也可以理解为B+树就是结合了平衡二叉树+二分查找法的精华。

B+树的定义在任何一本数据结构书中都能找到，其定义十分复杂，这里，精简地对B+树做个介绍：B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。

其实理解B+Tree时，只需要理解其最重要的两个特征即可【摘自博客：http://www.ywnds.com/?p=5300】：

第一，所有的关键字（可以理解为数据）都存储在叶子节点（Leaf Page），非叶子节点（Index Page）并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。

第二，所有的叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。InnoDB B+Tree结构是在经典B+Tree的基础上进行了优化，增加了顺序访问指针，做这个优化的目的是为了提高区间访问的性能。

如下图，是一颗高度为2，每页可存放4条记录，扇出（fanout）为4，简化版的B+Tree。扇出就是第一层指向第二层的四个箭头。

所有记录都在叶子节点上，并且是顺序存放的，如果用户从最左边的叶子节点开始顺序遍历，可以得到所有键值的顺序排序：5、10、15、20、25、30、50、55、60、65、75、80、85、90。另外需要注意的是，B+树是无法根据某一个查找Key直接定位到value的。比如我们查找“30”这条记录，其只能通过B+树定位到“30”这条记录在这个页中，然后再通过二分查找法定位到具体的记录，只不过二分查找法的速度很快，基本忽略这个查询，但这个开销本身是存在的。

那么怎么理解B+Tree这两个重要特征呢？MySQL将每个节点的大小设置为一个页的整数倍（原因下文会介绍），也就是在节点空间大小一定的情况下，每个节点可以存储更多的内结点，这样每个节点能索引的范围更大更精确。所有的叶子节点使用指针链接的好处是可以进行区间访问，比如上图中，如果查找大于20而小于30的记录，只需要找到节点20，就可以遍历指针依次找到25、30；只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。如果没有链接指针的话，就无法进行区间查找。这也是MySQL使用B+Tree作为索引存储结构的重要原因。

局部性原理与磁盘预读

MySQL为何将节点大小设置为页的整数倍，这就需要理解磁盘的存储原理。磁盘本身存取就比主存慢很多，在加上机械运动损耗（特别是普通的机械硬盘），磁盘的存取速度往往是主存的几百万分之一，为了尽量减少磁盘I/O，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存，预读的长度一般为页的整数倍。

这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用；程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数，页是计算机管理存储器的逻辑块，硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多OS中，
页的大小通常为4K）。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始
位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

MySQL巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了读取一个节点只需一次I/O。假设B+Tree的高度为h，一次检索最多需要h-1I/O（根节点常驻内存），复杂度$O(h) = O(log_{M}N)$。实际应用场景中，M通常较大，常常超过100，因此树的高度一般都比较小，通常不超过2-4层。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

总结一下B+树，B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接，也就可以利用二分查找法进行数据查找了，对于范围查询特别有优势。另外，B+Tree属于高扇区（fanout）性（大于100），简单说就是上层节点指向下层节点的指针数多，每一个指针可以称之为一个扇区。所以高扇区特性意味这棵树不会太高（一般2-4层最多），对应IO操作次数的减少。

B+树的操作（插入和删除）

【站位】

B+树索引

上面说的基本都是B+树的数据结构及其一般操作，B+树索引的本质是B+树再数据库中的实现。但是B+树索引在数据库中有一个特点就是高扇出，树的高度一般是2~4层，这也就是说查找某一个记录最多只需要2到4次IO。

数据库中B+树索引可以分为聚集索引和辅助索引。但是不管聚集索引还是辅助索引，其内部都是B+树，即高度平衡的。不同的是，聚集索引叶子节点存放着所有的数据，辅助索引叶子节点存放的只是一个指向聚集索引的指针。

聚集索引

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

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

聚集索引并不是物理上连续的，而是逻辑上连续的。数据页都是通过双向链表链接，页按照主键的顺序排序；另一个是每个页中的记录也是通过双向链表进行维护的，
物理存储上可以不按照主键存储。

上面提到聚集索引对于主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所需要的数据。

mysql> select * from employees ignore index(pri) order by  emp_no  desc limit 10;          #禁用索引

+--------+------------+------------+--------------+--------+------------+

| emp_no | birth_date | first_name | last_name    | gender | hire_date  |

+--------+------------+------------+--------------+--------+------------+

| 499999 | 1958-05-01 | Sachin     | Tsukuda      | M      | 1997-11-30 |

| 499998 | 1956-09-05 | Patricia   | Breugel      | M      | 1993-10-13 |

| 499997 | 1961-08-03 | Berhard    | Lenart       | M      | 1986-04-21 |

| 499996 | 1953-03-07 | Zito       | Baaz         | M      | 1990-09-27 |

| 499995 | 1958-09-24 | Dekang     | Lichtner     | F      | 1993-01-12 |

| 499994 | 1952-02-26 | Navin      | Argence      | F      | 1990-04-24 |

| 499993 | 1963-06-04 | DeForest   | Mullainathan | M      | 1997-04-07 |

| 499992 | 1960-10-12 | Siamak     | Salverda     | F      | 1987-05-10 |

| 499991 | 1962-02-26 | Pohua      | Sichman      | F      | 1989-01-12 |

| 499990 | 1963-11-03 | Khaled     | Kohling      | M      | 1985-10-10 |

+--------+------------+------------+--------------+--------+------------+

10 rows in set (0.32 sec)

mysql> select * from employees  order by  emp_no  desc limit 10;                      #使用主键查询

+--------+------------+------------+--------------+--------+------------+

| emp_no | birth_date | first_name | last_name    | gender | hire_date  |

+--------+------------+------------+--------------+--------+------------+

| 499999 | 1958-05-01 | Sachin     | Tsukuda      | M      | 1997-11-30 |

| 499998 | 1956-09-05 | Patricia   | Breugel      | M      | 1993-10-13 |

| 499997 | 1961-08-03 | Berhard    | Lenart       | M      | 1986-04-21 |

| 499996 | 1953-03-07 | Zito       | Baaz         | M      | 1990-09-27 |

| 499995 | 1958-09-24 | Dekang     | Lichtner     | F      | 1993-01-12 |

| 499994 | 1952-02-26 | Navin      | Argence      | F      | 1990-04-24 |

| 499993 | 1963-06-04 | DeForest   | Mullainathan | M      | 1997-04-07 |

| 499992 | 1960-10-12 | Siamak     | Salverda     | F      | 1987-05-10 |

| 499991 | 1962-02-26 | Pohua      | Sichman      | F      | 1989-01-12 |

| 499990 | 1963-11-03 | Khaled     | Kohling      | M      | 1985-10-10 |

+--------+------------+------------+--------------+--------+------------+

10 rows in set (0.00 sec)

#数据时间差可以看出还是有差别的，看一下这两条查询的计划任务。

mysql> explain select * from employees order by  emp_no  desc limit 10\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: employees

   partitions: NULL

         type: index

possible_keys: NULL

          key: PRIMARY

      key_len: 4

          ref: NULL

         rows: 10

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

#使用order by时对记录排序，但是在实际过程中并没有进行所谓的filesort操作。

mysql> explain select * from employees ignore index(pri) order by  emp_no  desc limit 10\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: employees

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 299246

     filtered: 100.00

        Extra: Using filesort

1 row in set, 1 warning (0.02 sec)

#禁用主键（也就是聚集索引）看到这里使用了filesort排序操作

使用聚集索引排序查询

mysql> explain select * from employees where emp_no > 125389 and emp_no < 314534;

+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |

+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

|  1 | SIMPLE      | employees | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 149623 |   100.00 | Using where |

+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from employees ignore index(pri) where emp_no > 125389 and emp_no < 314534;

+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |

+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299246 |    11.11 | Using where |

+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

#返回的行数只是一个大概的数字，并不是一个确切的数字。这里看到第二个查询执行了全表扫描。过滤率只有11.11%；

使用聚集索引进行范围查找

辅助索引

对于辅助索引（非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除列包含键值以外，每个叶子节点中的索引行中还包含了一个书签。该书签用来告诉INNODB存储引擎哪里可以找到与索引相对应的行数据。由于INNODB存储引擎是索引组织表，因此INNODB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

【上面这段话，可以发现如果用辅助索引来查找数据的话，需要两个步骤：首先找到辅助索引的位置，然后辅助索引给出目标数据的聚集索引的位置，最后再根据这个聚集索引来找到对应数据行。这个第二阶段的过程叫做回表】

这里需要说明的从辅助索引的指针得道的聚集索引的位置是无序的，也就是回表这个过程是随机读取的，随机的过程一般很慢的？为了优化这个回表的过程，从mysql5.6版本开始加入了mrr优化！

MRR优化

从MySQL5.6开始支持对Multi-Range Read（MRR）优化。MRR优化的目的就是为了减少磁盘的随机访问，对于MySQL的二级索引（非聚集索引）而言，过于随机的回表会造成随机读取过于严重，范围扫描（range access）中MySQL将扫描到的数据存入read_rnd_buffer_size，然后对其按照Primary Key（RowID）排序，然后使用排序好的数据进行顺序回表，因为我们知道InnoDB中叶子节点数据是按照PRIMARY KEY（ROWID）进行排列的，那么这样就转换随机读取为顺序读取了。这对于IO-bound类型的SQL查询语句带来性能极大的提升。MRR优化可用于range，ref，eq_ref类型的查询。

MRR优化有以下几个好处：

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

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

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

此外，若InnoDB存储引擎缓冲池不是足够大，即不能存放下一张表中的所有数据，此时频繁的离散读取操作还会导致将缓存中的页替换出缓冲池，然后又不断地读入缓冲池。若按照主键顺序进行访问，则可以将重复行为的次数降为最低。

执行简易过程如图：

#不起用mrr的时候，这条sql语句大概执行了3.17秒钟.
mysql> explain select * from salaries where salary > 10000 and salary < 40000;

+----+-------------+----------+------------+-------+---------------+-----------+---------+------+-------+----------+-----------------------+

| id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                 |

+----+-------------+----------+------------+-------+---------------+-----------+---------+------+-------+----------+-----------------------+

|  1 | SIMPLE      | salaries | NULL       | range | idx_index     | idx_index | 4       | NULL | 21450 |   100.00 | Using index condition |

+----+-------------+----------+------------+-------+---------------+-----------+---------+------+-------+----------+-----------------------+

1 row in set, 1 warning (0.00 sec)
#启用mrr之后，这条sql语句大概执行了0.06秒。
mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from salaries where salary > 10000 and salary < 40000;
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+-------+----------+----------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | salaries | NULL       | range | idx_index     | idx_index | 4       | NULL | 21450 |   100.00 | Using index condition; Using MRR |
+----+-------------+----------+------------+-------+---------------+-----------+---------+------+-------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

#会在extra字段标识使用了MRR
#salary上有一个辅助索引idx_salary，因此除了通过辅助索引查找键值外，还需要通过书签查找来进行对整行数据的查找。这里使用了ICP（pushed-down conditions）和MRR，
#因为MRR的代价评估一般较高，所以这里使用mrr_cost_based=off。

此外，MRR还可以将某些范围查询拆分为键值对，以此来完成批量的数据查询。这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据，例如：

SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 <= 2000 AND key_part2 = 10000;

表t中有(key_part1, key_part2)的联合索引，因此索引根据key_part1、key_part2的位置关系进行排序。若没有MRR，此时查询类型为Range，SQL优化器会先将key_part1大于1000小于2000的数据都取出，就是使key_part2不等于10000，待取出行数据后再根据key_part2的条件进行过滤，这会导致无用数据被取出。如果存在大量的数据并且其key_part2不等于10000，则启用MRR优化性能会有巨大的提升。

倘若启用了MRR优化，那么优化器会先将查询条件进行拆分，然后再进行数据的查询。就上述查询语句而言，优化器会将查询条件拆分为(1000, 10000)，(1001, 10000)，(1002, 10000)，…，(1999, 10000)，最后再根据这些拆分出的条件进行数据查询。

mysql> show variables like "read_rnd_buffer_size";

+----------------------+--------+

| Variable_name        | Value  |

+----------------------+--------+

| read_rnd_buffer_size | 262144 |

+----------------------+--------+

1 row in set (0.00 sec)

默认值是256KB。

ICP优化

和MRR一样，ICP（index condition pushdown）同样是MySQL5.6开始支持的一种根据索引进行查询的优化方式。之前MySQL数据库不支持ICP，当进行索引查询时，首先根据索引来查找记录了，然后再根据where条件来过滤记录。在支持ICP之后，MySQL数据库会在取出索引的同时，判断是否可以进行where条件过滤，也就是将where的部分过滤操作放在了存储引擎层。在一些查询下，可以大大减少上层sql对记录的索取，从而提高数据库的整体性能。

ICP优化支持对range， ref，eq_ref, ref_or_null类型的查询，当前仅支持myisam和INNODB存储引擎。当优化器选择ICP时，可在执行计划列EXTRA看到Using index condition提示。

哈希算法

哈希算法是一种常见算法，时间复杂度为o(1)，且不只存在于索引中，每个数据库应用中都存在该数据库结构。我们知道bp中缓存的有许多数据页，那么我们如何在内存的众多数据页中，查找到我们需要的数据页？虽然内存中查询速度很快，但是也不可能每次都要遍历所有的内存来进行查找，这时对于字典的操作只需要o(1)的哈希算法就由了很好的用武之地。

哈希表

全文索引

【暂缺】

巴特西

INNODB索引与算法

索引组织表

INNODB存储引擎概述

最新文章

热门文章