细说一下Mysql的索引的作用和使用？

　　运用mysql最多的便是查询，咱们火燎的期望mysql能查询的更快一些，咱们常常用到的查询有：

　　依照id查询唯一一条记载

　　依照某些个字段查询对应的记载

　　查找某个规划的悉数记载（between and）

　　对查询出来的作用排序

　　mysql的索引的意图是使上面的各种查询能够更快。

　　预备常识

　　什么是索引？

　　上一篇中有详细的介绍，能够以前看一下：什么是索引？

　　索引的本质：经过不断地缩小想要获取数据的规划来筛选出终究想要的作用，一同把随机的作业变成次第的作业，也便是说，有了这种索引机制，咱们能够总是用同一种查找办法来确认数据。

　　磁盘中数据的存取

　　以机械硬盘来说，先了解几个概念。

　　扇区：磁盘存储的最小单位，扇区一般大小为512Byte。

　　磁盘块：文件系统与磁盘交互的的最小单位（计算机系统读写磁盘的最小单位），一个磁盘块由接连几个（2^n）扇区组成，块一般大小一般为4KB。

　　磁盘读取数据：磁盘读取数据靠的是机械运动，每次读取数据花费的时刻能够分为寻道时刻、旋转推迟、传输时刻三个部分，寻道时刻指的是磁臂移动到指定磁道所需求的时刻，干流磁盘一般在5ms以下；旋转推迟便是咱们常常风闻的磁盘转速，比方一个磁盘7200转，标明每分钟能转7200次，也便是说1秒钟能转120次，旋转推迟便是1/120/2 = 4.17ms；传输时刻指的是从磁盘读出或将数据写入磁盘的时刻，一般在零点几毫秒，相关于前两个时刻能够忽略不计。那么拜访一次磁盘的时刻，即一次磁盘IO的时刻约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒能够实施5亿条指令，由于指令依托的是电的性质，换句话说实施一次IO的时刻能够实施40万条指令，数据库动辄十万百万甚至千万级数据，每次9毫秒的时刻，明显是个灾祸。

　　mysql中的页

　　mysql中和磁盘交互的最小单位称为页，页是mysql内部界说的一种数据结构，默许为16kb，恰当于4个磁盘块，也便是说mysql每次从磁盘中读取一次数据是16KB，要么不读取，要读取便是16KB，此值能够修改的。

　　数据检索进程

　　咱们对数据存储办法不做任何优化，直接将数据库中表的记载存储在磁盘中，假定某个表只需一个字段，为int类型，int占用4个byte，每个磁盘块能够存储1000条记载，100万的记载需求1000个磁盘块，假定咱们需求从这100万记载中检索所需求的记载，需求读取1000个磁盘块的数据（需求1000次io），每次io需求9ms，那么1000次需求9000ms=9s，100条数据随意一个查询便是9秒，这种状况咱们是无法承受的，明显是不可的。

　　咱们火燎的需求是什么？

　　咱们火燎需求这样的数据结构和算法：

　　需求一种数据存储结构：当从磁盘中检索数据的时分能，够减少磁盘的io次数，最好能够降低到一个安稳的常量值

　　需求一种检索算法：当从磁盘中读取磁盘块的数据之后，这些块中或许包括多条记载，这些记载被加载到内存中，那么需求一种算法能够快速从内存多条记载中快速检索出方针数据

　　咱们来找找，看是否能够找到这样的算法和数据结构。

　　咱们看一下常见的检索算法和数据结构。

　　循环遍历查找

　　从一组无序的数据中查找方针数据，常见的办法是遍历查询，n条数据，时刻复杂度为O(n)，最快需求1次，最坏的状况需求n次，查询功率不安稳。

　　二分法查找

　　二分法查找也称为减半查找，用于在一个有序数组中快速界说某一个需求查找的数据。

　　原理是：

　　先将一组无序的数据排序（升序或许降序）之后放在数组中，此处用升序来举例说明：用数组中心方位的数据A和需求查找的数据F比照，假定A=F，则完毕查找；假定AF，则将查找规划缩小至数组中A数据左边的部分，持续依照上面的办法直到找到F中止。

　　示例：

　　从下列有序数字中查找数字9，进程如下

　　[1,2,3,4,5,6,7,8,9]

　　第1次查找：[1,2,3,4,5,6,7,8,9]中心方位值为5，9>5，将查找规划缩小至5右边的部分：[6、7、8、9]

　　第2次查找：[6、7、8、9]中心值为8，9>8 ，将规划缩小至8右边部分：[9]

　　第3次查找：在[9]中查找9，找到了。

　　能够看到查找速度是恰当快的，每次查找都会使规划减半，假定咱们选用次第查找，上面数据最快需求1次，最多需求9次，而二分法查找最多只需求3次，耗时时刻也比较安稳。

　　二分法查找时刻复杂度是:O(logN)(N为数据量)，100万数据查找最多只需求20次（2^20=1048576‬）

　　二分法查找数据的利益：定位数据十分快，条件是：方针数组是有序的。

　　有序数组

　　假定咱们将mysql中表的数据以有序数组的办法存储在磁盘中，那么咱们定位数据进程是：

　　取出方针表的悉数数据，存放在一个有序数组中

　　假定方针表的数据量十分大，从磁盘中加载到内存中需求的内存也十分大

　　进程取出悉数数据耗费的io次数太多，进程2耗费的内存空间太大，还有新增数据的时分，为了保证数组有序，刺进数据会涉及到数组内部数据的移动（365myworLd），也是比较耗时的，明显用这种办法存储数据是不可取的。

　　链表

　　链表恰当于在每个节点上添加一些指针，能够和前面或许后边的节点联接起来，就像一列火车相同，每节车厢恰当于一个节点，车厢内部能够存储数据，每个车厢和下一节车厢相连。

　　链表分为单链表和双向链表。

　　单链表

　　每个节点中有持有指向下一个节点的指针，只能依照一个方向遍历链表，结构如下：

　　//单项链表

　　class Node1{

　　private Object data;//存储数据

　　private Node1 nextNode;//指向下一个节点

　　}

　　双向链表

　　每个节点中两个指针，别离指向其时节点的上一个节点和下一个节点，结构如下：

　　//双向链表

　　class Node2{

　　private Object data;//存储数据

　　private Node1 prevNode;//指向上一个节点

　　private Node1 nextNode;//指向下一个节点

　　}

　　链表的利益：

　　能够快速定位到上一个或许下一个节点

　　能够快速删去数据，只需改动指针的指向即可，这点比数组好

　　链表的缺点：

　　无法向数组那样，经过下标随机拜访数据

　　查找数据需从第一个节点开端遍历，不利于数据的查找，查找时刻和无需数据类似，需求全遍历，最差时刻是O(N)

　　二叉查找树

　　二叉树是每个结点最多有两个子树的树结构，一般子树被称作“左子树”（left subtree）和“右子树”（right subtree）。二叉树常被用于完毕二叉查找树和二叉堆。二叉树有如下特性：

　　1、每个结点都包括一个元素以及n个子树，这儿0≤n≤2。2、左子树和右子树是有次第的，次第不能任意倒置，左子树的值要小于父结点，右子树的值要大于父结点。

　　数组[20,10,5,15,30,25,35]运用二叉查找树存储如下：

　　Mysql索引的详解常识送给咱们

　　每个节点上面有两个指针（left,rigth），能够经过这2个指针快速拜访左右子节点，检索任何一个数据最多只需求拜访3个节点，恰当于拜访了3次数据，时刻为O(logN)，和二分法查找功率相同，查询数据仍是比较快的。

　　可是假定咱们刺进数据是有序的，如[5,10,15,20,30,25,35]，那么结构就变成下面这样：

　　Mysql索引的详解常识送给咱们

　　二叉树退化为了一个链表结构，查询数据最差就变为了O(N)。

　　二叉树的优缺点：

　　查询数据的功率不安稳，若树左右比较平衡的时，最差状况为O(logN)，假定刺进数据是有序的，退化为了链表，查询时刻变成了O(N)

　　数据量大的状况下，会导致树的高度变高，假定每个节点对应磁盘的一个块来存储一条数据，需io次数大幅添加，明显用此结构来存储数据是不可取的

　　平衡二叉树（AVL树）

　　平衡二叉树是一种特别的二叉树，所以他也满意前面说到的二叉查找树的两个特性，一同还有一个特性：

　　它的左右两个子树的高度差的绝对值不跨越1，并且左右两个子树都是一棵平衡二叉树。

　　平衡二叉树相关于二叉树来说，树的左右比较平衡，不会呈现二叉树那样退化成链表的状况，不管怎样刺进数据，终究经过一些调整，都能够保证树左右高度相差不大于1。

　　这样能够让查询速度比较安稳，查询中遍历节点控制在O(logN)规划内

　　假定数据都存储在内存中，选用AVL树来存储，仍是能够的，查询功率十分高。不过咱们的数据是存在磁盘中，用过选用这种结构，每个节点对应一个磁盘块，数据量大的时分，也会和二叉树相同，会导致树的高度变高，添加了io次数，明显用这种结构存储数据也是不可取的。

　　B-树

　　B杠树，千万不要读作B减树了，B-树在是平衡二叉树上进化来的，前面介绍的几种树，每个节点上面只需一个元素，而B-树节点中能够放多个元素，首要是为了降低树的高度。

　　一棵m阶的B-Tree有如下特性【特征描绘的有点绕，看不懂的能够越过，看后边的图】：

　　每个节点最多有m个孩子，m称为b树的阶

　　除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子

　　若根节点不是叶子节点，则至少有2个孩子

　　悉数叶子节点都在同一层，且不包括其它关键字信息

　　每个非终端节点包括n个关键字（健值）信息

　　关键字的个数n满意：ceil(m/2)-1 <= n <= m-1

　　ki(i=1,…n)为关键字，且关键字升序排序

　　Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的悉数节点关键字均小于ki，但都大于k(i-1)

　　B-Tree结构的数据能够让系统高效的找到数据地址的磁盘块。为了描绘B-Tree，首要界说一条记载为一个二元组[key, data] ，key为记载的键值，对应表中的主键值，data为一行记载中除主键外的数据。关于不同的记载，key值互不相同。

　　B-Tree中的每个节点根据实践状况能够包括许多的关键字信息和分支，如下图所示为一个3阶的B-Tree：

　　Mysql索引的详解常识送给咱们

　　每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点地址磁盘块的地址。两个键将数据划分红的三个规划域，对应三个指针指向的子树的数据的规划域。以根节点为例，关键字为17和35，P1指针指向的子树的数据规划为小于17，P2指针指向的子树的数据规划为17~35，P3指针指向的子树的数据规划为大于35。

　　仿照查找关键字29的进程：

　　根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】

　　比较关键字29在区间（17,35），找到磁盘块1的指针P2

　　根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】

　　比较关键字29在区间（26,30），找到磁盘块3的指针P2

　　根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】

　　在磁盘块8中的关键字列表中找到关键字29

　　分析上面进程，发现需求3次磁盘I/O操作，和3次内存查找操作，由于内存中的关键字是一个有序表结构，能够运用二分法快速定位到方针数据，而3次磁盘I/O操作是影响整个B-Tree查找功率的决定因素。

　　B-树相关于avl树，经过在节点中添加节点内部数据的个数来减少磁盘的io操作。

　　上面咱们说过mysql是选用页办法来读写数据，每页是16KB，咱们用B-树来存储mysql的记载，每个节点对应mysql中的一页（16KB），假定每行记载加上树节点中的1个指针占160Byte，那么每个节点能够存储1000（16KB/160byte）条数据，树的高度为3的节点大约能够存储（第一层1000+第二层1000^2+第三层1000^3）10亿条记载，是不是十分惊讶，一个高度为3个B-树大约能够存储10亿条记载，咱们从10亿记载中查找数据只需求3次io操作能够定位到方针数据地址的页，而页内部的数据又是有序的，然后将其加载到内存顶用二分法查找，是十分快的。

　　能够看出运用B-树定位某个值仍是很快的(10亿数据中3次io操作+内存中二分法)，可是也是有缺点的：B-不利于规划查找，比方上图中咱们需求查找[15,36]区间的数据，需求拜访7个磁盘块（1/2/7/3/8/4/9），io次数又上去了，规划查找也是咱们常常用到的，所以b-树也不太适合在磁盘中存储需求检索的数据。

　　b+树

　　先看个b+树结构图：

　　Mysql索引的详解常识送给咱们

　　b+树的特征

　　每个结点至多有m个子女

　　除根结点外,每个结点至少有[m/2]个子女，根结点至少有两个子女

　　有k个子女的结点必有k个关键字

　　父节点中持有拜访子节点的指针

　　父节点的关键字在子节点中都存在（如上面的1/20/35在每层都存在），要么是最小值，要么是最大值，假定节点中关键字是升序的办法，父节点的关键字是子节点的最小值

　　最底层的节点是叶子节点

　　除叶子节点之外，其他节点不保存数据，只保存关键字和指针

　　叶子节点包括了悉数数据的关键字以及data，叶子节点之间用链表联接起来，能够十分便利的支撑规划查找

　　b+树与b-树的几点不同

　　b+树中一个节点假定有k个关键字，最多能够包括k个子节点（k个关键字对应k个指针）；而b-树对应k+1个子节点（多了一个指向子节点的指针）

　　b+树除叶子节点之外其他节点值存储关键字和指向子节点的指针，而b-树还存储了数据，这样相同大小状况下，b+树能够存储更多的关键字

　　b+树叶子节点中存储了悉数关键字及data，并且多个节点用链表联接，从上图中看子节点中数据从左向右是有序的，这样快速能够支撑规划查找（先定位规划的最大值和最小值，然后子节点中依托链表遍历规划数据）

　　B-Tree和B+Tree该怎样挑选？

　　B-Tree由于非叶子结点也保存详细数据，所以在查找某个关键字的时分找到即可回来。而B+Tree悉数的数据都在叶子结点，每次查找都得到叶子结点。所以在相同高度的B-Tree和B+Tree中，B-Tree查找某个关键字的功率更高。

　　由于B+Tree悉数的数据都在叶子结点，并且结点之间有指针联接，在找大于某个关键字或许小于某个关键字的数据的时分，B+Tree只需求找到该关键字然后沿着链表遍历就能够了，而B-Tree还需求遍历该关键字结点的根结点去查找。

　　由于B-Tree的每个结点（这儿的结点能够理解为一个数据页）都存储主键+实践数据，而B+Tree非叶子结点只存储关键字信息，而每个页的大小有限是有限的，所以同一页能存储的B-Tree的数据会比B+Tree存储的更少。这样相同总量的数据，B-Tree的深度会更大，增大查询时的磁盘I/O次数，然后影响查询功率。

　　Mysql的存储引擎和索引

　　mysql内部索引是由不同的引擎完毕的，首要说一下InnoDB和MyISAM这两种引擎中的索引，这两种引擎中的索引都是运用b+树的结构来存储的。

　　InnoDB中的索引

　　Innodb中有2种索引：主键索引（集结索引）、辅佐索引（非集结索引）。

　　主键索引：每个表只需一个主键索引，叶子节点一同保存了主键的值也数据记载。

　　辅佐索引：叶子节点保存了索引字段的值以及主键的值。

　　MyISAM引擎中的索引

　　不管是主键索引仍是辅佐索引结构都是相同的，叶子节点保存了索引字段的值以及数据记载的地址。

　　如下图：

　　有一张表，Id作为主索引，Name作为辅佐索引。

　　Mysql索引的详解常识送给咱们

　　InnoDB数据检索进程

　　假定需求查询id=14的数据，只需求在左边的主键索引中检索就能够了。

　　假定需求查找name='Ellison'的数据，需求2步：

　　先在辅佐索引中检索到name='Ellison'的数据，获取id为14

　　再到主键索引中检索id为14的记载

　　辅佐索引这个查询进程在mysql中叫做回表。

　　MyISAM数据检索进程

　　在索引中找到对应的关键字，获取关键字对应的记载的地址

　　经过记载的地址查找到对应的数据记载

　　咱们用的最多的是innodb存储引擎，所以此处首要说一下innodb索引的状况，innodb中最好是选用主键查询，这样只需求一次索引，假定运用辅佐索引检索，涉及到回表操作，比主键查询要耗时一些。

　　innodb中辅佐索引为什么不像myisam那样存储记载的地址？

　　表中的数据发生改动的时分，会影响其他记载地址的改动，假定辅佐索引中记载数据的地址，此时会受影响，而主键的值一般是很少更新的，当页中的记载发生地址改动的时分，对辅佐索引是没有影响的。

　　咱们来看一下mysql中页的结构，页是实在存储记载的当地，对应B+树中的一个节点，也是mysql中读写数据的最小单位，页的结构设计也是恰当有水平的，能够加速数据的查询。

　　页结构

　　mysql中页是innodb中存储数据的基本单位，也是mysql中处理数据的最小单位，和磁盘交互的时分都是以页来进行的，默许是16kb，mysql中选用b+树存储数据（cameL2005），页恰当于b+树中的一个节点。

　　页的结构如下图：

　　Mysql索引的详解常识送给咱们

　　每个Page都有通用的头和尾，可是中部的内容根据Page的类型不同而发生改动。Page的头部里有咱们关心的一些数据，下图把Page的头部详细信息显示出来：

　　Mysql索引的详解常识送给咱们

　　咱们要点注重和数据组织结构相关的字段：Page的头部保存了两个指针，别离指向前一个Page和后一个Page，根据这两个指针咱们很简单幻想出Page链接起来便是一个双向链表的结构，如下图：

　　Mysql索引的详解常识送给咱们

　　再看看Page的主体内容，咱们首要注重行数据和索引的存储，他们都位于Page的User Records部分，User Records占有Page的大部分空间，User Records由一条一条的Record组成。在一个Page内部，单链表的头尾由固定内容的两条记载来标明，字符串办法的"Infimum"代表开端，"Supremum"代表完毕，这两个用来代表开端完毕的Record存储在System Records的，Infinum、Supremum和User Records组成了一个单向链表结构。开端数据是依照刺进的先后次第摆放的，可是跟着新数据的刺进和旧数据的删去，数据物理次第会变得紊乱，但他们依然经过链表的办法保持着逻辑上的先后次第，如下图：

　　Mysql索引的详解常识送给咱们

　　把User Record的组织办法和若干Page组合起来，就看到了稍微完好的办法。

　　Mysql索引的详解常识送给咱们

　　innodb为了快速查找记载，在页中界说了一个称之为page directory的目录槽（slots）,每个槽位占用两个字节（用于保存指向记载的地址），page directory中的多个slot组成了一个有序数组（可用于二分法快速定位记载，向下看），行记载被Page Directory逻辑的分红了多个块，块与块之间是有序的，能够加速记载的查找，如下图：

　　Mysql索引的详解常识送给咱们

　　看上图，每个行记载的都有一个n_owned的区域（图中粉色区域），n_owned标识所属的slot这个这个块有多少条数据，伪记载Infimum的n_owned值总是1，记载Supremum的n_owned的取值规划为[1,8]，其他用户记载n_owned的取值规划[4,8]，并且只需每个块中最大的那条记载的n_owned才会有值，其他的用户记载的n_owned为0。

　　数据检索进程

　　在page中查询数据的时分，先经过b+树中查询办法定位到数据地址的页，然后将页内整体加载到内存中，经过二分法在page directory中检索数据，缩小规划，比方需求检索7，经过二分法查找到7位于slot2和slot3所指向的记载中心，然后从slot3指向的记载5开端向后向后一个个找，能够找到记载7，假定里边没有7，走到slot2向的记载8完毕。

　　n_owned规划控制在[4,8]内，能保证每个slot统辖的规划内数据量控制在[4,8]个，能够加速方针数据的查找，当有数据刺进的时分，page directory为了控制每个slot对应块中记载的个数（[4,8]），此时page directory中会对slot的数量进行调整。