Mysql索引原理

     Mysql索引类型及其特性

1.普通索引

     最基本的索引，它没有任何限制，也是我们大多数情况下用到的索引。

–直接创建索引
CREATE INDEX index_name ON table(column(length))
–修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
–创建表的时候同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`description` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`birthday` date NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (name)
);
–删除索引
DROP INDEX index_name ON table

2. 唯一索引

     与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

–创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))
–修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
–创建表的时候直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`description` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`birthday` date NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE indexName (name)
);

3.主键索引

     是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。mysql的InnoDB引擎一般建表的时候都会要求你设置主键，就是为了建立一个主键索引，同时主键索引也是聚簇索引。

4.组合索引

    多个列上创建索引，例如 ALTER TABLE article ADD INDEX index_name_birthday (name,time)。这里使用的时候会遵循最左匹配原则。

5.全文索引

     在mysql5.6版本 以前 FULLTEXT索引仅可用于 MyISAM 表，在5.6之后innodb引擎也支持FULLTEXT索引；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

–创建表的适合添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`description` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`birthday` date NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (description)
);
–修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_description(description)
–直接创建索引
CREATE FULLTEXT INDEX index_description ON article(description)

PS：索引也是有优先级的，mysql按照索引的限制最严格的索引排优先，越是限制严格的索引的越先用。

mysql的索引原理

• 概述

    Mysql的两种引擎，即MyISAM和InnoDB的索引都是使用b树的变种b+树存储的（b树的结构就不多说了，简单的说就是多叉树），之所以会选择b+树，是因为mysql存储是外部存储，需要从磁盘读入内存，这是很耗时的操作，使用b+树可以有效的减少这种io读取，大幅增加速度。虽然这两种引擎都是使用的b+树存储，但是还是有一些区别的，MyISAM是使用的非聚簇索引，索引树和数据存储分开，索引树只存储索引key值和数据的地址，而且所有的索引都是存储的这个地址；InnoDB就不一样了，它规定一张表必须有主键，因为它需要通过主键来构建一个聚簇索引，将主键列key和数据都放到这棵树上，而其他辅助索引都是非聚簇索引，存的都是索引key和主键key值，它的查询机制就是先由辅助索引找到主键key，再去主键key找到数据，两次查找。此外b+树的叶子节点还添加了到顺序访问的指针，可以大大提高范围查询的效率。

• MyISAM索引

    上面提到，MyISAM索引也是b+树，但是数据和索引树分开，这样非主键查询就很快，不需要像InnoDB这样两次查找，所以比起InnoDB，MyISAM的查询速度会更快，同样的也是由于这个原因，MyISAM的数据在增加或原来的数据地址移动的话，就需要更新所有的索引树，这就稍稍嫌慢。

• InnoDB索引

    InnoDB的索引大体上和MyISAM一样，不一样就在于它的数据存储方式不同，它采用主键的聚簇索引，将数据存在主键索引树上，辅键索引则是存储的主键key，除此之外，InnoDB，如下图所示：

• mysql的b+树及查找

      真实数据库中的B+树应该是非常扁平的，可以通过向表中顺序插入足够数据的方式来验证InnoDB中的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操作就检索出所需的数据，效率是非常之高的。
   

   

    可见InnoDB引擎下的聚簇索引树是可以非常扁平的，一张表的数据量通常也就3层高度，由于key一般不会很大，所以一般情况下非叶子结点直接加载到内存，不需要多次磁盘IO，这里就是b+树比b树好的地方，b树由于有数据，所以它的非叶子结点的大小就会大大增加，从而造成寻找一行数据的时候可能需要多次磁盘IO，就会大大降低效率。大体过程如下：

• mysql的更深层的存储

    mysql在存储实现基于一个叫做Page的结构， Page是整个InnoDB存储的最基本构件，也是InnoDB磁盘管理的最小单位，与数据库相关的所有内容都存储在这种Page结构里。Page分为几种类型，常见的页类型有数据页（B-tree Node）,Undo页（Undo Log Page）,系统页（System Page）,事务数据页（Transaction System Page）等。单个Page的大小是16K（编译宏UNIV_PAGE_SIZE控制），每个Page使用一个32位的int值来唯一标识，这也正好对应InnoDB最大64TB的存储容量（16Kib * 2^32 = 64Tib）。一个Page的基本结构如下图所示：

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

     我们重点关注和数据组织结构相关的字段：Page的头部保存了两个指针，分别指向前一个Page和后一个Page，头部还有Page的类型信息和用来唯一标识Page的编号。根据这两个指针我们很容易想象出Page链接起来就是一个双向链表的结构。

    再看看Page的主体内容，我们主要关注行数据和索引的存储就是存储在这的，以单向链表的方式存储，每个Page主体中都会有一个开始点Infimum和结束点supremum，达到结束点就会通过Page头部的next Page跳到下一页Page继续查找，当然Page主体的内容是分类存储的，根据索引的不同类型和节点不同类型分类成4种，即主索引树非叶子节点,主索引树叶子节点，辅助索引树非叶子节点，辅助索引树叶子节点四种，每个record存放的内容都是不一样的，如下图所示：

    以上所说的page为基础的存储都是放在相应表的表空间里的，ok，介绍到这，应该知道mysql的一次查询是如何在底部运行的了，例如通过辅键作为条件的一次查询，流程大体如下：

1. 首先根据表名，知道这是哪个表，获取表空间信息，再次通过索引列取得索引树在磁盘的位置，将根节点读入内存，根据二分查找找到它的page编号。
2. 从磁盘读入page编号继续二分查找，直到找到辅键的叶子节点所在的page编号，然后读入该块到内存中继续查找匹配key，直到匹配到，去主键key值。
3. 主键key值取到之后，重复1-2步骤，找到主键的叶子节点匹配的位置得到该行数据值。

以上发生的磁盘IO不一定都会产生，mysql会有局部预读到内存，所以实际上发生的磁盘IO会比较少。至此 本文结束。

    小结

    对于Mysql的索引，大体上的实现就是如此了，思路很明确，通过b+树这种数据结构，大大提高查询效率，不再是线性的查询。而通过上述原理，我们可以得出一个结论，一下一些用法结论：

• 索引列存数据量要尽量的小，原理不多说，和不用b树一样。
• 索引查询是很快，但是更新和插入的速度多少都会受到拖累，所以索引设置有讲究。

参考文章

1. https://www.cnblogs.com/shijingxiang/articles/4743324.html