MySQL索引

在谈论优化MySQL的方案时，索引常常被提起。索引是一个能够显著提升MySQL查询性能的一个重要技术，掌握好索引十分的重要！

索引是什么？

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。--百度百科

从百度百科可以了解到，索引就一个为了加快数据检索速度所设计的数据结构！

实现索引的数据结构

实现索引的数据结构分别有哈希索引、二叉树、B树、B+树

哈希索引

哈希索引是一个将值通过哈希函数转化为哈希码，然后再通过哈希码来查找对应的数据行所在位置的指针，最后再根据指针来找到具体的数据。需要注意的是，在MySQL的存储引擎中只有**Memory，NDB**两种引擎支持，并且只对查询所有列的SQL语句生效

上图就是一个哈希索引的流程图，对于出现哈希冲突的问题，采用的解决办法为链地址法。

不难发现，对于进行精确查询的SQL语句，哈希索引非常之快，查询的速度仅为O(1)。

但是哈希索引的限制非常多，例如不能够进行范围的查询、不能够进行排序、如果频繁出现哈希冲突的话性能也会比较低而且维护操作的代价也会很高。

总得来说，如果场景非常适合哈希索引，那么就可以使用，否则不建议使用

二叉树

二叉树一个最为常见的数据结构，它允许一个根节点只能由两个子节点，它的一个查找效率为log以2为底的n次方。二叉树有许多的变形，例如二叉查找树、二叉平衡树等。

二叉查找树是在二叉树的基础上增加了左节点的值一定小于根节点的值，右节点的值一定大于根节点的值的特性。

那么使用二叉查找树可以吗？答案是不行！由于二叉查找树的定义，那么就有可能发生所有的值都大于或者都小于根节点的值，就会导致二叉树退化成链表，严重的影响性能！

二叉平衡树则是为了解决该问题而出现的一种二叉树，二叉平衡树是在二叉查找树的基础上增加了左右旋功能，该功能能够保证二叉树不会退化成链表！那么既然问题解决了，是否就能够使用二叉平衡树当作MySQL索引的数据结构了呢？答案也是否定的！因为在实际中，MySQL需要存储大量的数据，如果使用二叉树这种数据结构，由于每一个节点上只能够存储一个数据，那么数据量一旦大起来，就会导致高度急剧增高即使你的查找效率为log以2为底的n次方，查询速度也会慢。所以无论是哪种二叉树都不适用于当作MySQL索引的数据结构。

B树

B树是一种多叉平衡树，它有如下几个特点：

1、所有的键值分布在整棵树中

2、搜索有可能会在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找

3、每个节点最多有m个子节点

4、根节点至少两个子节点

5、分支节点至少拥有m/2个子节点

6、所有的叶子节点在同一层，每个节点最多有m-1个key，并且以升序排列

B树这种数据结构相比较于二叉树来讲，能够存储更多的数据，有效的降低数据查询的时间。显然使用B树作为MySQL索引的数据结构比二叉树更好，但是B树这种数据结构没有什么缺点嘛？答案是有的！我们知道MySQL存储数据的最小单位是页，页所存储的数据量也是有限的，在MySQL中一页可以存放16KB的数据量。我们知道B树是将data直接放到所有的节点上，所以一旦节点上的data过大，那么B树key就会变少，key变少，树的深度就会变深，一旦变深磁盘的IO次数增多，速度照样慢下来，所以B树这种数据结构在面对data过大的情况下，也不容乐观！

B+树

B+树一个在B树的基础上扩展的数据结构，它在B树的基础上修改和扩展了以下几个地方：

1、B+树将数据全放置到叶子节点中进行存储，非叶节点用只用于存储key

2、叶子节点两两相连，顺序读取的速度更快

B+树有着如下几个特点：

1、由于数据现在全部存放在叶子节点中，那么非叶子节点就能够存放更多的key，key存放的多了，那么树的高度就会降低，磁盘IO的次数就减少了，提高了查询的性能。

2、B+树的叶子节点构成一个有序链表，提高了范围查询的性能并且天然具备排序的功能。

3、查询更加稳定，数据存放在叶子节点，每次查询的次数有一样，比B树更加稳定

总得来看，B+树更加适用于作为MySQL索引的数据结构，它具有着更加良好的性能优势。

索引的分类

在MySQL中，根据逻辑分类索引总共分为五类：

1、普通索引：它是MySQL中最基本的索引类型，它没有任何的限制，允许数据可以有重复值和空值，唯一的目的就是加快数据的检索速度。

2、唯一索引：在某个字段上建立该索引，不允许数据存在重复值，可以存在空值。

3、主键索引：该索引在表中只能够设置一个，它不允许数据存在重复值和空值。

4、组合索引：由两个及以上的字段建立的索引，称之为组合索引，该索引使用的时候需要十分注意。

5、全文索引：该索引用于查找文本中的关键字，只能在char、varchar、text的类型上创建。但是该索引不常用，因为文本内容过于庞大就不适合放到数据库中进行检索查询，这样会影响性能。

从数据存储方式分类索引分为两类：一类是聚簇索引，另一个是非聚簇索引

聚簇索引

聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。--百度百科

由于聚簇索引的特性，一般指定主键索引为聚簇索引，如果没有设置主键索引，就会选取第一个设置了非空且唯一的约束的字段作为聚簇索引，如果也没有，就会使用隐藏字段row_id作为聚簇索引

聚簇索引的优点：

1、查询速度更加快速，由于数据和索引的保存在同一个B+树中，获取数据的速度就会非常的快

2、范围查询和排序更加适用

聚簇索引的缺点：

1、频繁的更新会导致B+树频繁的变动，严重的影响性能，所以一般将主键设置为不可更新

2、聚簇索引被插入的顺序所影响，如果插入是顺序插入，那么就会很快，如果是非顺序插入就会导致也分裂，也会严重的影响性能，所以一般将主键设置为整型自增。

3、如果不是直接通过主键索引进行查询，而是通过其它索引进行查询那么就需要进行两次查询，第一次查询主键的索引值，第二次按照主键的索引值查询对应的行数据

非聚簇索引

非聚簇索引也叫做辅助索引，除了聚簇索引之外其他的都称之为非聚簇索引。非聚簇索引就是行数据与索引值是分开的，叶子节点上存放的不再是行数据，而是主键值或者行数据的存储地址信息

聚簇索引和非聚簇是索引是数据存储方式来进行区分的，并不是说主键索引一定是聚簇索引，只能说是聚簇索引的前提需要时主键索引。

存储引擎和聚簇索引、非聚簇索引

在MySQL中的最常用的存储引擎就是Innodb和MyIsam这两种，这两种存储引擎只有Innodb引擎具有聚簇索引，而MyIsam引擎并不具有聚簇引擎。产生这种差异的原因是由于两种引擎的文件设计不同。

在使用Innodb创建表的时候会产生两个文件，一个是frm格式的文件，该文件用于描述表结构字段长度等；另一个是ibd，用于存放索引和数据信息。也就是说使用Innodb存储引擎就将索引和数据放置在同一个文件下面，显而易见的就是为了聚簇索引而设计出来的。

而在使用MyIsam创建表的时候会产生三个文件，一个是frm格式的文件；一个是myd格式的文件，该文件是用于存放表中的数据信息；最后一个就是myi格式的文件，该文件是用于存放表中的索引信息。既然索引信息和数据信息都是单独存放的，那么就明显不符合聚簇索引的定义，也就是说MyIsam这个引擎并不存在聚簇索引这种存储方式，换句话说使用MyIsam引擎建立的索引都是非聚簇索引。

回表

回表是一个在学习MySQL的时候时常遇到的一个词汇，那么什么是回表呢？所谓的回表其实就是通过辅助索引查找出聚簇索引的索引值，然后再通过聚簇索引的值来查找出最终的行数据。需要经历两次搜索B+树。回表操作只会出现在Innodb引擎中，因为只有Innodb存在聚簇索引。MyIsam索引并不存在回表的操作，它只需要扫描一遍B+树得到数据的地址，然后通过地址得到数据即可

覆盖索引

所谓的覆盖索引并不是一种新的索引，覆盖索引是一个不需要回表就能够查询出所需要的数据，这种操作就叫做覆盖索引。

select id,name from student where name='lisi';（name字段建立了索引）

上面的查询语句就会使用到覆盖索引。如果需要使用覆盖索引可以建立组合索引，通过组合索引的方式来避免出现回表的操作

总结

MySQL索引是优化方式中最重要的一个，了解MySQL索引的定义已经底层十分的有必要。合理的利用MySQL索引能够更加有效的提高MySQL的性能，但是错误的使用索引也会导致MySQL性能变差，毕竟没有什么东西一定是完美的，总会有缺点。