MySQL的索引实现原理

MySQL,在开发中，是我们经常使用的数据库，除了 Oracle，那么就是 MySQL ,至于 SQLServer ,市场占比已经非常的小了，今天阿粉就来说说关于 MySQL 索引的那些事。

MySQL 索引类型

我们在开发中，都知道 MySQL 中的索引可以提升查询速度，会影响where查询，以及order by排序。

接下来我们就来看看索引类型都有哪些。

• 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
• 从应用层次划分：普通索引、唯一索引、主键索引、复合索引
• 从索引键值类型划分：主键索引、辅助索引（二级索引）
• 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

普通索引

普通索引这事最基本的索引类型，是基于普通字段建立的索引，没有任何的限制，创建普通索引的方法其实也很简单。

​​CREATE INDEX <索引的名字> ON tablename (字段名);​​

​​ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);​​

​​CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );​​

这些都是创建索引的语句，而且是普通索引的创建方式，也是我们最常用的创建索引的方式，一般都是选择使用这种方式来进行创建索引，对于一些普通索引来说，直接创建还是比较舒服的。

唯一索引

其实“唯一索引”与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。

创建唯一索引的语句也很简单：

​​CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);​​

​​ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);​​

​​CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;​​

主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主 键。

这个主键索引，阿粉甚至都不需要说，因为你的表百分之百都是有主键ID的，而这个主键ID，都是一个主键索引。

关键字就是 ​​PRIMARY KEY​​

复合索引(组合索引)

其实复合索引也叫做组合索引，叫法有区别，但是内容上就是一个东西。

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索 引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合 索引所需的开销更小。

索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超 过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有 效

创建组合索引的语句：

​​CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);​​

​​ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);​​

​​CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );​​

当我们使用组合索引的时候，需要注意一些内容，来增加我们的效率，而不是为了创建索引而创建影响我们的效率。

比如： 要根据where条件建索引，注意不要过多使用索引，如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

如果你想看自己写的一些 SQL 是否命中了你增加的索引，那么可以使用 explan 关键字来进行查看即可。

全文索引

全文索引，这个其实并不是很多见，因为如果你的 MySQL的版本是5.6之前的版本，那么全文索引几乎不支持，为什么呢？

因为在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果 使用全文索引，查询速度会比like快很多倍。

创建全文索引的SQL：

​​CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);​​

​​ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);​​

​​CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;​​

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字.

我们看看 SQL 是怎么说的：

​​select * from user where match(name) against('aaa');​​

而且使用全文索引的话，限制也是非常多的。

• 全文索引必须在字符串、文本字段上建立。
• 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4- 84）
• 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
• 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布 尔模式下搜索a*

索引的实现原理

其实阿粉觉得，索引的实现原理，对于开发来说，并不陌生，因为在面试的时候，很多面试官都会问到这个，底层是怎么实现的，有没有自己对他的了解呢？

首先我们得先知道 MySQL官方是怎么对他定义的:

是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

MyISAM索引实现：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址

这里假设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示。同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB索引实现：

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。

从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

下图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。

这种索引叫做聚集索引（聚簇索引是根据主键创建的一棵B+树，聚簇索引的叶子节点存放了表中的所有记录）。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

不管是InnorDB还是MyISAM，除了细节上有偏差，但都是用的B+树，那为什么MySQL主流的数据库引擎的索引都是用B+树呢？

索引实现为什么都是靠着B+树来实现呢？

B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接。

B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。

因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

所以关于索引的实现，你了解了么？

文章参考

《MySQL海量数据优化》 

《MySQL 索引的实现原理》

文章转载自公众号：  Java极客技术