mysql的索引

索引

(以下信息，凡是涉及到存储引擎的，均为innoDB）

在计算机上运行一个任务，一般有三部分涉及性能，分别是内存大小，CPU及磁盘的速度，而索引是一种存储方式，与它相关的最重要部分就是磁盘，所以磁盘性能的高低，直接影响了在数据库中查找数据的效率。

要理解MySQL索引是如何工作的，最简单的方式就是去看看一本书的“索引”部分：如果想在一本书中找个某个特定的主题，一般会先看书的“索引”，然后通过索引找到对应的页码。在MySQL中，存储引擎也是使用类似的方法使用索引，在查询时，先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

索引有很多种，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层实现而不是在服务层实现的。不同的存储引擎有不同的索引实现方式。 

MyISAM使用前缀压缩技术使得索引更小，但InnDB则按照原数据格式进行压缩。

MyISAM通过数据的物理位置引用被索引的行，InnoDB则根据主键引用被索引的行。

B-Tree

适合的场景（了解BTree，对于聚簇索引的排序和范围查找都无需任何操作）

全值匹配:和索引中的所有列进行匹配
匹配最左前缀:仅仅使用索引中最左边列进行查询。比如在col1+col2+col3字段上的联合索引能够被包含col1、（col1+col2）、（col1+col2+col3）的等值查询利用到，不能够被col2、（col2+col3）的等值查询利用。
匹配列前缀:仅仅使用索引的第一列，并且只包含索引第一列的开头一部分进行查询。例如：查询出title是以“最美丽的”开头的所有数据，也就是SQL中的“%最美丽的”。
匹配范围值:适用于查找存在第一列中一个范围数据。

精确匹配某一列并范围匹配另外一列:匹配一列精确查找，匹配后面一列的范围查找。
仅仅对索引进行查询:只许访问索引，无需访问数据行，（也许查询的列都在索引的字段中）

不适用的场景

**不是按照索引的最左列开始查找
**不能跳过索引中的列

**如果查找中包含范围查找，则后面的key无法使用索引优化查找

哈希索引

哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行。不过访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
哈希索引数据并不是按照索引值顺序存储的，索引也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
哈希索引只支持等值比较查询。包括=，IN()等。也不支持任何范围查询，例如where price >100。

访问哈希索引的速度非常快，除非有很多哈希冲突（不同索引列值有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低的列上建立哈希索引，那么从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

innodb引擎有一个特殊的功能叫做自适应哈希索引，当innodb注意到某些索引值被使用的非常频繁时，它会在内存中基于btree索引之上再创建一个哈希索引，这样就让btree索引也具有哈希索引的一些优点，比如：快速的哈希查找，这是一个全自动的，内部的行为，用户无法控制或者配置，不过如果有必要，可以选择关闭这个功能（innodb_adaptive_hash_index=OFF，默认为ON）。

例如在查询的时候>

 select id from url where url="http://www.mysql.com" and url_crc=CRC32("http://www.mysql.com");

可以使用触发器来维护哈希索引,如下：

delimiter |    create trigger pseudohash_crc_ins before insert on pseudohash  for each row begin      set new.url_crc=crc32(new.url);  end;  |    create trigger pseudohash_crc_upd before update on pseudohash  for each row begin      set new.url_crc=crc32(new.url);  end;  |    delimiter ;

倒排索引

倒排索引源于实际应用中需要根据属性的值来查找记录。这种索引表中的每一项都包括一个属性值和具有该属性值的各记录的地址。由于不是由记录来确定属性值，而是由属性值来确定记录的位置，因而称为倒排索引(inverted index)。带有倒排索引的文件我们称为倒排索引文件，简称倒排文件(inverted file)。

首先，我们来解释正常的索引，我们要通过id去查找content，我们会将ID做成索引，加快我们的查找，下图来解释正常的索引应该是：

可是现在的问题是我们要去查到那些content中有b这两个词，首先想到的是依次去尝试content中是否有b,显然在时间复杂度上是很大的，尤其是数据库中数量很多时，那么如何去解决这个问题？现在都需要引入倒排索引的思想

我们已content做成索引，这样查找ID的时间就快很多，跟正向的索引比较，也就是做了一个倒置，这就是倒排索引的思想。

两种表现形式

**inverted file index,其表现形式为：{单词，单词所在的文档ID}

**full inverted index,其表现形式为：{单词，（单词所在的文档ID，在具体文档中的位置）}

值得一提的是，mysql在版本号5.6（2013）为InnoDB加入全文检索。 并且Elasticsearch也是基于倒排索引实现的。

InnoDB的索引

B-Tree

        一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足：m/2 - 1 <= j <= m - 1；一个节点的子节点数量会比关键字个数多1，这样关键字就变成了子节点的分割标志。一般会在图示中把关键字画到子节点中间，非常形象，也容易和后面的B+树区分。由于数据同时存在于叶子节点和非叶子结点中，无法简单完成按顺序遍历B树中的关键字，必须用中序遍历的方法。

B+Tree

B+树是在B树上的一种改进，它把所有的data信息放在也所有叶子节点，从B+树的理解上，其内节点中的key值个数可以等于子节点的个数，也可以和B树一样key值比子节点少一个。就目前参考Mysql运维内参一书中，Mysql的innoDB中采用的是子节点等于key值的个数。在高性能Mysql、和Mysql技术内幕中，则说的是子节点多于key值个数，但是首先，源于书Mysql运维内参的作者为周彦伟先生，ACMUG主席编写，并且本书的宗旨就是以源码为依靠所写，其次，另外俩书中介绍的索引均为B+树，并未指名为InnoDB索引，所以以下的例子讲解使用子节点个数=key值个数的图。

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

B+Tree的分裂

InnoDB传统的分裂模式：

按照原页面中50%的数据量进行分裂，针对当前这个分裂操作，3，4记录保留在原有页面，5，6记录，移动到新的页面。最后将新纪录7插入到新的页面中；

50%分裂策略的优势：分裂之后，两个页面的空间利用率是一样的；如果新的插入是随机在两个页面中挑选进行，那么下一次分裂的操作就会更晚触发；

50%分裂策略的劣势：空间利用率不高：

按照传统50%的页面分裂策略，索引页面的空间利用率在50%左右；分裂频率较大：针对如上所示的递增插入(递减插入)，每新插入两条记录，就会导致最右的叶页面再次发生分裂；

插入分裂的具体过程如下，如果假定一页只能存下4个key值，先有一颗B+树中有1，2，3，4，5如下图：

当插入6时，结构变为：

当继续插入7时，结构变为：

那么有什么修改或者优化策略呢？

考虑以下的一个B+树，已有的用户数据是1，2，3，4，5，6，100，并且在插入记录100之后，引起索引页面分裂，记录100在分裂后被插入到新的页面：

这样会不会有别的问题呢？如果继续插入9，会发生什么？

因此继续优化，如果当前插入的数据的上一个页面已满，而小于下一个链表中的最大值且还有位置，则继续插入到下一个页面，且及时更新父节点的最小值，如下图：

B+Tree和B-Tree的区别

1、B树中的同一键值不会出现多次，它有可能出现在叶子节点上，也可能出现在内节点上；而B+树上的键一定会出现在叶子节点上，同时也有可能在非叶子节点中重复出现。简单来说，B+树的内节点存储的都是键值，键值对应的具体数据都存储在叶子节点上。

2、由于B树的每一个节点都存储了真实的数据，会导致每一个节点存储的数据量变小（换句话就是B-Tree的存储空间整体比B+Tree小），所以整个B树的层数就会相对变高，当数据量变大后，维护代价是比较大的，而且层数越高，搜索、插入或修改的性能就会越低，相关的IO操作却会增加；而B+树的内节点中，只存储键值，相对而言，一个内节点存储的记录个数比B树多很多。由于B+树是横向扩展的，所以随着其中数据量的增长，最终会成长为一个矮胖子，不像B树一样是纵向扩展，最终会变成一个瘦高个子。这样总体而言，B+树在搜索时，从上到下直到叶子节点只需要遍历层数个节点而已，因此性能会比较高，且整体比较稳定。

3、B树的查询效率与键在B树中的位置有关，（在叶子节点的时候）最大时间复杂度与B+数相同，最小时间复杂度为1（在根节点的时候）；而B+树的复杂度对某个建成的树是固定的。

4、由于B+Tree底部的叶子结点是链表形式, 因此也可以实现更方便的顺序遍历。

Mysql为什么使用B树作为索引？

很多其他的索引也适合做索引，但是为什么文件系统及数据库系统普遍采用B-/+Tree作为索引结构？
这需要结合计算机组成原理相关知识来解答一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理，然后再结合这些原理分析B-/+Tree作为索引的效率。

主存存取原理

目前计算机使用的主存基本都是随机读写存储器（RAM），现代RAM的结构和存取原理比较复杂，这里本文抛却具体差别，抽象出一个十分简单的存取模型来说明RAM的工作原理。

从抽象角度看，主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据。每个存储单元有唯一的地址，现代主存的编址规则比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元。图5展示了一个4 x 4的主存模型。

主存的存取过程如下：

当系统需要读取主存时，则将地址信号放到地址总线上传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其它部件读取。
写主存的过程类似，系统将要写入单元地址和数据分别放在地址总线和数据总线上，主存读取两个总线的内容，做相应的写操作。
这里可以看出，主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响，例如，先取A0再取A1和先取A0再取D3的时间消耗是一样的。

磁盘存取原理

上文说过，索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。
下图是磁盘的整体结构示意图。

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）。
下图是磁盘结构的示意图。

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头。
当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：
当一个数据被用到时，其附近的数据也通常会马上被使用。
程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

磁盘的读写过程

磁盘上数据必须用一个三维地址唯一标示：柱面号、盘面号、块号(磁道上的盘块)。
读/写磁盘上某一指定数据需要下面3个步骤：
(1)  首先移动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为定位或查找。
(2)  如上图6盘组示意图中，所有磁头都定位到了10个盘面的10条磁道上(磁头都是双向的)。这时根据盘面号来确定指定盘面上的磁道。
(3) 盘面确定以后，盘片开始旋转，将指定块号的磁道段移动至磁头下。
经过上面三个步骤，指定数据的存储位置就被找到。这时就可以开始读/写操作了。
访问某一具体信息，由3部分时间组成：
● 查找时间(seek time) Ts: 完成上述步骤(1)所需要的时间。这部分时间代价最高，最大可达到0.1s左右。
● 等待时间(latency time) Tl: 完成上述步骤(3)所需要的时间。由于盘片绕主轴旋转速度很快，一般为7200转/分(电脑硬盘的性能指标之一, 家用的普通硬盘的转速一般有5400rpm(笔记本)、7200rpm几种)。因此一般旋转一圈大约0.0083s。
● 传输时间(transmission time) Tt: 数据通过系统总线传送到内存的时间，一般传输一个字节(byte)大概0.02us=2*10^(-8)s
磁盘读取数据是以盘块(block)为基本单位的。位于同一盘块中的所有数据都能被一次性全部读取出来。而磁盘IO代价主要花费在查找时间Ts上。因此我们应该尽量将相关信息存放在同一盘块，同一磁道中。或者至少放在同一柱面或相邻柱面上，以求在读/写信息时尽量减少磁头来回移动的次数，避免过多的查找时间Ts。

B+树能做到什么？

真实数据库中的B+树应该是非常扁平的，可以通过向表中顺序插入足够数据的方式来验证InnoDB中的B+树到底有多扁平。我们通过如下图的CREATE语句建立一个只有简单字段的测试表，然后不断添加数据来填充这个表。通过下图的统计数据（来源见参考文献1）可以分析出几个直观的结论，这几个结论宏观的展现了数据库里B+树的尺度.

1 每个叶子节点存储了468行数据，每个非叶子节点存储了大约1200个键值，这是一棵平衡的1200路搜索树！

2 对于一个22.1G容量的表，也只需要高度为3的B+树就能存储了，这个容量大概能满足很多应用的需要了。如果把高度增大到4，则B+树的存储容量立刻增大到25.9T之巨！
3 对于一个22.1G容量的表，B+树的高度是3，如果要把非叶节点全部加载到内存也只需要少于18.8M的内存（如何得出的这个结论？因为对于高度为2的树，1203个叶子节点也只需要18.8M空间，而22.1G从良表的高度是3，非叶节点1204个。同时我们假设叶子节点的尺寸是大于非叶节点的，因为叶子节点存储了行数据而非叶节点只有键和少量数据。），只使用如此少的内存就可以保证只需要一次磁盘IO操作就检索出所需的数据，效率是非常之高的。

聚簇索引

聚集索引的叶子节点存储的是数据，而且是按照物理顺序存储的，所以通常来说物理顺序结构只有一种，那么一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引。

因为存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。一些数据库服务器允许选择哪个索引作为聚簇索引，但直MySQL内建的存储引擎没有一个支持这一点。InnoDb将通过主键聚集数据。如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

最好避免随机的（不连续，且值的分布范围非常大的）聚簇索引，特别是对于io密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
　　因为主键的值时顺序的，索引InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的15/16 ,留出部分空间用于以后修改），下一条记录就会写入到新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果（然而二级索引页可能不一样）。
　　使用UUID聚簇索引的表插入数据，因为新的行的主键值不一定比之前插入的大，所以InnoDB 无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找到合适的位置--通常是已有数据的中间位置--并且分配空间。这会增加很多的额外操作。并导致数据分布不够优化。下面是总结的一些缺点：
　　写入的目标页可能已经数到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的磁盘io。
　　因为写入是乱序的，InnoDB 不得不频繁的做分页操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页面，而不是一个页。
　　由于频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。
　　总结：使用InnoDB 时应该尽可能地按照主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

联合索引

联合索引是指对表的多列进行索引关联，并且联合索引遵循从左到右的匹配规则，如果跳跃，则无法匹配。如果中间使用范围查询，则后续的键值将不会起到作用，具体原因是因为联合索引的实现方式是一个多维数组的B+Tree，比较大小排序的时候是从左到右比较得到的,存储结构如下：

覆盖索引

覆盖索引（covering index）指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。也可以称之为实现了索引覆盖。
那么，优点显而易见。辅助索引不包含一整行的记录，因此可以大大减少IO操作。覆盖索引是mysql dba常用的一种SQL优化手段

前缀索引

前缀索引对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快，但是不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

ADD INDEX `test` (`b`(3) ASC);

这里有一个选择性的概念，就是使用某个字段，截取几位作为前缀索引合适，具体的算法为：

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;

查询执行路径

_______________参考文献

http://www.cnblogs.com/tgycoder/p/5410057.html （主要讲的索引实现的原理）

高性能的mysql

innodb技术内幕

mysql运维内参

http://www.admin10000.com/document/5372.html