Mysql索引原理

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

–直接创建索引
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.主键索引

4.组合索引

5.全文索引

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

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

      真实数据库中的B+树应该是非常扁平的，可以通过向表中顺序插入足够数据的方式来验证InnoDB中的B+树到底有多扁平。我这里从网友博客看到的数据，如下：

• 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树一样。
• 索引查询是很快，但是更新和插入的速度多少都会受到拖累，所以索引设置有讲究。