本文详细介绍了MySQL中索引的存储类型，包括B型树索引和哈希索引，以及它们在InnoDB和MyISAM存储引擎中的应用。讨论了索引的优缺点、分类（如普通索引、唯一性索引、主键索引等）以及设计原则。此外，还阐述了如何创建和删除索引，重点分析了B-树和B+树的底层原理，特别是它们在InnoDB和MyISAM存储引擎中的区别。最后，文章探讨了为何MySQL选择使用B+树而非B树，并提到了哈希索引的特性。
索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询速度。
一、索引存储类型：
B型树(btree)索引和哈希(hash)索引。其中Innodb和myisam存储引擎支持B型索引，memory存储引擎支持hash索引和B型索引；
二、索引的优缺点：
优点：提高检索数据的速度
缺点：创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低。
三、索引的分类：
普通索引：不附加任何限制条件，可以给任何类型的字段创建普通索引。
唯一性索引：使用unique修饰的字段，值不会重复，主键索引隶属于唯一性索引。
主键索引：使用primary key修饰的字段会自动创建索引。
单列索引：在一个字段上创建的索引
多列索引：在表的多个字段上创建索引。
全文索引：使用fulltext参数可以设置全文索引，只支持char，varchar和text类型的字段，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。MyISAM存储引擎支持全文检索。
空间索引：使用spatial参数设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。mysql中的空间数据类型包括geometry,point,linestring,polygon
四、索引的设计原则：
1.选择唯一性索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段建立索引
4.限制索引的数目
5.尽量使用数据量少的索引
6.尽量使用前缀来索引
7.删除不再使用或者很少使用的索引
五、创建索引
1.创建表的时候创建索引
create table 表名 （属性名 数据类型 【完整性约束条件】，
属性名 数据类型 【完整性约束条件】，
【unique| fulltext|spatial】 index| key
[别名] （属性名1【（长度）】【ASC|DESC】）
）;
1.1.创建普通索引
create table 表名(id int,
name varchar(20),
sex boolean,
index(id));
1.2.创建唯一性索引
create table 表名（id int unique,
name varchar(20),
unique index 索引别名 （id asc））;
1.3.创建全文索引
create table 表名(id int,
info varchar(20),
fulltext index 别名(info)
)engine=myisam;
全文索引只能对于存储引擎为myisam的表设置；
1.4.创建单列索引
create table index4(id int,
subject varchar(30),
index 别名 (subject(10)))
);
subject字段长度为20，而创建的索引长度只有10，这样做的目的是为了提高查询速度。对于字符型数据，可以不用查询全部信息，而只查询其前面的若干字符信息。
1.5.创建多列索引
create table 表名（id int,
name varchar(20),
sex char(4),
index 别名 （name,sex））;
注意：只有使用了索引的第一个字段时才会触发索引。如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用
1.6.创建空间索引
create table 表名 （id int,
space geometry not null,
spatial index 别名（space））engine=myisam;
创建空间索引时必须使用spatial参数设置，表的存储引擎必须是muisam类型，索引字段必须有非空约束。数据类型必须是空间数据类型，geometry,point,linestring,polygon;
2.在已经存在的表上创建索引
create [unique|fulltext|spatial] index 索引名
on 表名 (属性名【（长度）】【asc|desc】);
2.1.创建普通索引
create index 别名 on 表名 （属性名）；
2.2.创建唯一性索引
create unique index 别名 on 表名 （属性名）；
2.3.创建全文索引
create fulltxt index 别名 on 表名 （属性名）；属性名必须为char,varchar,text类型；
2.4.创建单列索引
create index 别名 on 表名 （属性名）；
2.5.创建多列索引
create index 别名 on 表名 （属性名1，属性名2）；
2.6.创建空间索引
create spatial index 别名 on 表名 （属性名）； 属性名数据类型为空间数据类型，而且是非空
3.用alter table 语句来创建索引
alter table 表名 add 【unique|fulltext |spatial】 index 索引名
（属性名 【（长度）】【asc|desc】）
六.删除索引
删除索引是指将表中已经存在的索引删除掉，一些不在使用的索引会降低表的更新速度，影响数据库的性能。
drop index 索引名 on 表名；
show create table 表名\G 用来查看索引信息以及表的结构；
explain select * from 索引名 where condition; 查看索引是否被使用；
七、索引的底层原理
1.B-树是一种多路自平衡的搜索树，类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。那么m阶B-tree满足下列条件数据结构：
1.所有的键值分布在整棵树中
2.搜索有可能在非叶子结点结束，在关键字全集内做一次查找，性能逼近二分查找
3.每个节点最多拥有m个子树
4.根节点至少有2个子树
5.分支节点至少拥有m/2棵子树（除根节点和叶子节点外都是分支节点）
6.所有叶子节点都在同一层，每个节点最多可以有m-1个key，并且以升序排列
每个节点占用一个磁盘块，一个节点有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词分成的三个范围域对应三个指向的子树的数据范围域。以根节点为例，关键字为17和35，p1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。
模拟查找关键字38的过程：
1.根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2.比较关键字38在区间（35，无穷），找到磁盘块1的指针P3.
3.根据P3指针找到磁盘块4，读入内存。【磁盘I/O操作第2次】
4.比较关键字38在区间（35,65），找到磁盘块4的指针P1。
5.根据P1指针找到磁盘块9，读入内存【磁盘I/O操作第3次】
6.在磁盘块9中的关键字列表中找到关键字38.
分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分查找提高效率。而3次磁盘I/O操作是影响整个B-tree查找效率的决定因素。
存在问题：
每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时，将会导致每个节点（即一个页）能存储的key的数量很小。
当存储的数据量很大时同样会导致B-tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。
2.B+tree
B+tree是在B-tree基础上的一种优化，Innodb存储引擎就是用B+tree实现其索引结构。
优点：
B+树每个节点可以包含更多的节点，这样做有两个原因，一个是降低树的高度。另外一个是将数据范围变为多个区间，区间越多，数据检索越快
非叶子节点存储key，叶子节点存储key和数据
叶子节点两两指针相互链接，顺序查询性能更高
注：Mysql的Innodb存储引擎在设计时是将根节点常驻内存的，因此力求达到树的深度不超过3，也就是说I/O不需要超过3次。
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
3. B-树和B+树的区别
B+树内节点不存储数据，所有数据存储在叶节点导致查询时间复杂度固定为 log n
B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)
B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等
B-树每个节点 key 和 data 在一起，则无法区间查找
B+树更适合外部存储(存储磁盘数据)。由于内节点无 data 域，每个节点能索引的范围更大更精确。
4.Mysql索引是如何实现的
4.1 InnoDB 中的 B+Tree
InnoDB 是通过 B+Tree 结构对 ID 建索引，然后在叶子节点中存储记录。采用 InnoDB 引擎的数据存储文件有两个，一个定义文件，一个是数据文件。
若建索引的字段不是主键 ID，则对该字段建索引，然后在叶子节点中存储的是该记录的主键，然后通过主键索引找到对应的记录。
INNODB存储引擎-主键索引：
INNODB存储引擎的主键索引，叶子节点中，索引关键字和数据是在一起存放的，索引关键字和数据一起存储在叶子节点上
INNODB存储引擎-辅助索引
INNODB的辅助索引，叶子节点上存放的是索引关键字和对应的主键，辅助索引的B+树，先根据关键字找到对应的主键，再去主键索引树上找到对应的行记录数据。
从索引树上可以看到，INNODB的索引关键字和数据都是在一起存放的，体现在磁盘存储上。例如创建一个user表，在磁盘上只存储两种结构，user.frm(存储表的结构),user.idb（存储索引和数据）
INNODB的索引树叶子节点包含了完整的数据记录，这种索引叫做聚集索引。因为INNODB的数据文件本身要按照主键聚集，
所以INNODB要求表必须有主键（MYISAM可以没有），如果没有显示指定，则MYISAM系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MYSQL自动为INNODB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型
4.2 Myisam 中的 B+Tree
Myisam 引擎也是采用的 B+Tree 结构来作为索引结构。由于 Myisam 中的索引和数据分别存放在不同的文件，所以在索引树中的叶子节点中存的数据是该索引对应的数据记录的地址，由于数据与索引不在一起，所以 Myisam 是非聚簇索引。
MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复，如果给其他字段创建辅助索引，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
可以看出：MYISAM存储引擎，索引结构叶子节点存储关键字和数据地址，也就是说索引关键字和数据没有在一起存放，体现在磁盘上，就是索引在一个文件存储，数据在另一个文件存储。例如一个user表，会在磁盘上存储三个文件：user.frm(表结构文件) user.MYD(表的数据文件) user.MYI(表的索引文件)
MYISAM的索引方式也叫做非聚集索引，之所以这么称呼为了与InNoDB的聚集索引区分
为什么Mysql最终使用B+tree而不是B-tree，他们在存储结构上有什么不同？
1.B-树的每一个节点，存储的是关键字和对应数据地址，而B+树的非叶子结点存储关键字，因此B+树的每个非叶子节点存储的关键字数量远远多于B-树，B+树叶子结点存储关键字和对应数据地址，因此：从树的高度来说，B+树的高度要小于B-树，使用磁盘的次数少。
2.B-树由于每一个节点存储关键字和数据，因此离根结点近的数据查询比较快，离根结点比较远的数据，查询就比较慢；B+树上所有的数据都存储在叶子结点上，因此B+树查询关键字，找到对应数据的时间上是比较均匀的，没有快慢之分。总体上说，B+的查询时间比较小。
3.在B-树如果做区间查找，遍历的节点比较多，B+树所有的叶子结点被连接成有序链表结构，因此做整表遍历和区间查找是非常容易的。
哈希索引是由哈希表实现，哈希表无法对数据做到排序，因此无法做区间查找，效率非常低，需要查询整个哈希结构
索引分类：主键索引、辅助索引、聚集索引、非聚集索引
参考书籍：
1.《Mysql入门很简单》
2.《深入浅出Mysql全文》
3.很用心的博文
扫一扫
3361
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
