MySQL索引

引言：

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。接下来就来了解一下索引的使用吧！

一、索引的原理

索引用来快速地寻找那些具有特定值的记录，如果没有索引，执行查询时MySQL必须从第一
个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个
操作的代价就越高。
索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似:通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。
如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

索引中的列被称为索引字段或索引项，该列的各个值被称为索引值。索引访问首先会搜索索引值，然后会通过指针直接找到数据表中对应的记录，从而实现快速地查找到数据。

而学号在没有创建索引的情况如下，如果要找位于第10000条的学号”20070201”的记录，计算机要在表中查找10000次 

计算机先在索引文件中学号为”20070201”的记录，找到相应的记录号，再到学生表中直接读取相关记录

索引往往以索引文件的形式存储在磁盘上。因此索引查找过程中就要产生磁盘I/O消耗，为了减少查找索引文件产生的磁盘I/O，索引的组织效率就非常重要。   
BTree是为了充分利用磁盘预读功能而创建的一种数据结构。BTree相对于平衡二叉树 缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用,从而提高了查询效率 ,B+Tree是在BTree基础上的一种优化，其更适合实现外存储索引结构。
MySQL InnoDB存储引擎使用B+Tree实现其索引结构。B+Tree基于BTree提高了磁盘型I/O性能的同时，解决元素遍历的效率低下的问题。B+Tree也是一种多路搜索树，只要遍历叶子节点就可以实现整棵树的遍历，B+Tree的结构也特别适合带有范围的查找。  

与BTree相比，B+Tree有以下不同点:非叶子节点不存储data，只存储索引key，只有叶子节点才存储data。B+Tree的结构如图：

理解B+Tree 时，最重要要理解其两个特征: 
1、所有的关键字(可以理解为数据)都存储在叶子节点(LeafPage)，非叶子节点(Index Page)并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。 
2、所有的叶子节点由指针连接。

MySQL B+Tree在经典B+Tree的基础上进一步做了优化，增加了顺序访问指针。在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。如果要查询key为从15-49的所有数据记录，当找到15后，只需顺节点和指针顺序谝历就可以一次性访问到所有数据节点，这样就提高了区间访问性能(无须返回 MySQL结构简述上层父节点重复遍历查找减少I/O操作)。

 

 二、聚簇索引与非聚簇索引

聚簇索引：

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

非聚簇索引：

将数据存储与索引分开结构，索引结构的叶子节点存储了数据在磁盘的地址，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时，在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

InnoDB存储引擎索引过程
​ InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，如上图，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
​ 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MyISM存储引擎索引过程
​ MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引的优势：

​ 1、由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。 
2、辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。 
3、聚簇索引适合用在排序的场合，非聚簇索引不适合 
4、取出一定范围数据的时候，使用用聚簇索引 
5、二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据 
6、可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。 

聚簇索引的劣势
1、维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片。
2、表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以一般用自增id。

三、索引的分类

普通索引(INDEX)

概念：这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。

唯一性索引(UNIQUE)

概念：这种索引和前面的普通索引基本相同，但有一个区别:索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE 。

唯一索引与普通索引基本是一样的，但是有一个区别，索引列上的值只能出现一次，即必须是唯一的。

主键(PRIMARYKEY)

概念：主键是一种唯一性索引，它必须指定为“PRIMARY KEY”，主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。

对于InnoDB存储引擎，主键毫无疑问是个聚集索引。如果一个主键被定义了，那么这个主键就作为聚集索引。如果没有主键被定义，那么InnoDB取第一个唯一索引(unique)且只含非空列
(NOTNULL)作为主键，lnnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生
一个ID值，它有6个字节，而且是隐藏的，使其作为聚集索引。

如果表使用自增列(INT/BIGINT类型)做主键，这时写入顺序是自增的，和B+树叶子节点分裂顺序一致，避免了插入过程中的聚集索引排序问题。如果使用非自增主键(如身份证号或学号等)，由于每次插入主键的值近似于随机，因此每次新记录都要被插到现有索引页的中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，而频繁的移动、分页操作会造成大量的碎片，增加很多开销。

全文索引(FULLTEXT) 

概念：MySQL支持全文检索和全文索引。全文索引的索引类型FULLTEXT。全文索引只能在 CHAR、VARCHAR或TEXT类型的列上创建。

MySQL支持全文检索和全文索引。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引只 
能在CHAR，VARCHARETEXT类型的列上创建。全文索引是一种特殊类型的索引，他查找的是文本中的关键词，是基干相似度的查询，而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事，而不是简单的where条件匹配。虽然搜索引擎的索引对象是超大量的数据且通常其背后都不是关系型数据库，但全文索引的原理是一样的。

MyISAM的全文索引是一类特殊的B-Tree索引，共有两层。第一层是所有的关键字，然后碎语每
型个关键字的第二层，包含的是一组相关的“文档指针”。全文索引并不会存储关键字具体匹配在哪一列，如果需要根据不同的列来进行组合查询，那么不需要针对每一列来建立多个全文索引。

四、索引的使用

适当使用索引的优点
在MySQL中合理地创建索引不仅可以极大地提高在数据库中获取所需信息的速度。 而且能提高服务器处理相关搜索请求的效率。建立索引的优点如下:  
1、可以加快数据的检索速度。 
2、可以保证数据库表中每一行数据的唯一性。 
3、加速表和表之间的连接。 
4、在使用分组或者排序子句时，可以减少查询中分组和排序的时间。

过度使用索引的缺点
虽然索引在检索效率上具有诸多的积极作用，但过多的索引也会引起一些不必要的问题。
1、在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
2、索引本身的存储也需要占用物理空间，因此过多的索引会挤占宝贵的磁盘空间。 
3、当对数据进行增、删、改操作时，用户也需要对索引进行相应的维护，降低了数据的维护速度。 

索引使用的原则 
1、一定要有主键，显式定义主键且采用与业务无关的列以避免修改。InnoDB表在有主键时会自动将主键设为聚集索引，建议采用自增列来使数据顺序插入。
2、合理添加索引的一个通用法则，即对于经常被查询的列、经常用于表连接的列、经常排序分组的列，需要创建索引。
3、创建索引之前，还要查看索引的选择性(不重复的索引值和表的记录总数的比值)来判断这个字段是否合适创建索引。索引的选择性越接近于1，说明选择性越高，非常适合创建索引。例如学生信息表中的“性别”列就不适合创建索引。 
4、组合索引(表中两个或两个以上的列上创建的索引)，一般把选择性高的列放在前面。组合索引字段数不建议超过5个，如果5个字段还不能极大地缩小row范围，那么肯定是设计有问题。
5、合理利用覆盖索引(只需通过索引就可以返回查询所需要的数据，不必在查到索引之后再回表查询数据)。禁止使用select*只获取必要字段，指定字段能有效利用索引覆盖。
6、使用explain判断SQL语句是否合理使用了索引，尽量避免Extra列出现Using File Sort，Using Temporary。
7、单张表的索引数量建议控制在5个以内，索引太多也会浪费空间且降低修改数据的速度，影响性能。
8、不建议在频繁更新的字段上建立索引。 
9、Where条件中的索引列不能是表达式的一部分，避免在Where条件中在索引列上进行计算或使用函数，因为这将导致索引不被使用而进行全表扫描。
10、如果要进行join查询，那么被join的字段必须类型相同并建立索引，因为join段类型不一致会导致全表扫描。
11、隐式类型转换会使索引失效，导致全表扫描。 当我们对不同类型的值进行比较时，MySQL 为了让这些不同类型的值可比较，就会对它们进行类型转换，这样就会导致索引失效。

索引失效的原因

1、Where条件中的索引列不能是表达式的一部分，避免在Where条件中在索引列上进行计算或使用函数，因为这将导致索引不被使用而进行全表扫描。

2、隐式类型转换会使索引失效，导致全表扫描。 当我们对不同类型的值进行比较时，MySQL 为了让这些不同类型的值可比较，就会对它们进行类型转换，这样就会导致索引失效。

索引失效案例分析：

通过explain查看的执行计发现，type列是ALL，这条语句进行了一个全表扫描。虽然 MysQL给字段log_time加了索引，但是没有用到索引，因为违背避免在Where条件中在索引列上进行计算或使用函数。可以修改为SELECT*FROMtIWHERElogtime>=2015-04 09 00:00:00 AND logtime<=2015-04-1000:00:00，通过explain查看查询执行计划时使用到了索引。

使用索引避免排序

MySQL排序（Order By）为什么要使用索引？

1、WHERE条件句中使用索引

以查询一条程咬金 记录为例：

 创建索引，先创建一个二级索引

不选择索引字段做条件查询

key=null 执行计划中没有用到索引rows=69 全表扫描，使用索引字段作为条件查询

key=name_index 使用到索引name_index，rows=1

结论：

（1）在选条件语句中不加索引字段，索引会失效，包括聚簇索引。

（2）使用的索引避免了全表扫描。

2、Order By 使用索引

众所周知，为了避免全表扫描，条件句中增加了索引，上面性能对比一目了然。可是为什么要在Order By排序中也要使用索引字段呢？

先创建一个二级索引hp_start_index

不使用索引字段进行排序

结果如上图，type=all，key=null 没有使用到索引，rows=69 全表扫描，using filessort。

接下来使用索引字段进行排序

结果为key=hp_start_index 使用到了索引，rows=3非全表扫描，Using index condition; Backward index scan；

其实这个SQL是分三步来执行的：

第一步、where得到数据。

第二步、Order By处理数据（排序）看第一部执行计划是不是用到索引，如果用到了就可以直接获得索引的顺序，从而避免再次排序。如果没用到就做排序（using filessort）。

第三步、返回数据。

其中Order By有两种排序方法：

（1）Backward index scan：使用索引扫描。索引本身就是有序的，所以不需要再次进行排序

（2）using filessort：在内存中排序，占用CPU资源。如果查询结果太大还会产生临时文件，到磁盘中进行排序，这时候会进行大量IO操作性能较差。

结论：

（1）Order By语句跟WHERE语句中都用了索引字段，Order By中的索引才会生效。

（2）Order By中使用索引可避免重新排序导致CPU资源浪费。