索引与 B+Tree

一、MySQL 中索引的语法

创建索引

在创建表的时候添加索引

CREATE TABLE mytable(      ID INT NOT NULL,       username VARCHAR(16) NOT NULL,      INDEX [indexName] (username(length))  ); 

在创建表以后添加索引

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);-- 或者CREATE INDEX index_name ON my_table(column_name);

注意：

1、索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够

2、创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行

删除索引

DROP INDEX my_index ON tablename；-- 或者ALTER TABLE table_name DROP INDEX index_name;

查看表中的索引

SHOW INDEX FROM tablename;

二、索引的优缺点

**优势：**可以快速检索，减少 I/O 次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

**劣势：**索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的 1.5 倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表;

三、索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

1、主键索引：即主索引，根据主键 pk_clolum（length）建立索引，不允许重复，不允许空值；

-- 直接修改时添加主键和自增alter table users modify uid int primary key AUTO_INCREMENT;
-- 删除主键索引 注意需要先取消 自增,再删除主键-- 先取消自增,修改字段alter table users modify uid int;-- 删除主键alter table users drop primary key;
-- 添加主键索引-- alter table users add  primary key(uid)-- alter table users modify uid int AUTO_INCREMENT;

2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值

-- 添加唯一索引 UNIQUE 当前列要求唯一,但允许为空alter table users add unique u_name(uname);
-- 删除唯一索引 根据当前索引名去进行删除alter table users drop index u_name;

3、普通索引：用表中的普通列构建的索引，没有任何限制

-- 添加索引alter table users add index in_name(email);
-- 删除索引drop index in_name on users;

4、全文索引：用大文本对象的列构建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；-- 5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。--- 5.6版本之后InnoDB存储引擎开始支持全文索引-- 在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

-- 添加索引alter table users add index in_x(email,phone,uname);
-- 删除索引alter table users drop index in_x;

*遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了 col1,col1col2,col1col2col3 三个索引，而 col2 或者 col3 是不能使用索引的。

*在使用组合索引的时候可能因为列名长度过长而导致索引的 key 太大，导致效率降低，在允许的情况下，可以只取 col1 和 col2 的前几个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；--表示使用col1的前4个字符和col2的前3个字符作为索引

四、索引的实现原理

1、哈希索引：

只有 memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的 hashCode，然后在 hashCode 相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个 hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能

2、B+Tree 索引

正常情况下，如果不指定索引的类型，那么一般是指 B+Tree 索引（或者 B+Tree 索引）。

存储引擎以不同的方式使用 B+Tree 索引。性能也各有不同，但是 InnoDB 按照原数据格式进行存储。

B+Tree 索引能够加快数据的读取速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，相反是从索引的根节点开始进行搜索，通过相应的指针移动，最终存储引擎要么找到了对应的值，要么该记录不存在。树的深度与表的大小直接相关。

B+Tree 索引是按照顺序组织存储的，所以适合范围查找数据

B+Tree 索引使用与全键值、键值范围或者键前缀查找，其中键前缀进适用于根据最左前缀的查找。

• B-Tree 40 22 56 15 35 45 55 75 5 8 48 58

• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-X587wPmE-1627555103000)(./imgs/B-Tree.索引.png)]

• B+Tree

为什么使用 B+树而不是 B 树

1.磁盘读写代价更低

在计算机中,所有与空间相关的东西都是按照块(block)进行存取和操作的.每次读取都意味着一次 I/O

假设计算机中每个块的大小为 4K,行的大小为 1k,索引的大小为 0.06K,就可以计算并得出结果

• B 树的数据和索引都在同一个节点上,那么意味着每一个块(block)中包含的索引是少量的,如果想要取出比较深层的数据就意味着要读取很多的快,才能得到想要的索引和数据,那就是 I/O 的次数会多

• 而 B+树中每一个块能够存储的索引数量是 B 树的很多倍,那么获取比较深层的数据只需要读取少量的快(block)就可以做到.那就是 I/O 的次数会少很多

2.随机 I/O 的次数更少

随机 I/O 是指读写操作时间连续，但访问地址不连续,时长约为 10ms

顺序 I/O 是指读取和写入操作基于逻辑块逐个连续访问来自相邻地址的数据,时长约为 0.1ms

• 在相同情况下,B 树要进行更多的随机 IO,而 B+树需要更多的顺序 IO,因此 B+树,效率也更快

3.查询速度更稳定

由于 B+Tree 非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

3,聚簇索引和非聚簇索引

在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

MyISAM——非聚簇索引

MyISAM 存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的 key 都存储指向键值对应的数据的物理地址。

非聚簇索引的主索引和辅助索引的叶子节点的 data 都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。

InnoDB——聚簇索引

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。

聚簇索引的辅助索引的叶子节点的 data 存储的是主键的值，主索引的叶子节点的 data 存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；

总结 Innodb Myisam 的区别

• 事务的支持: innodb 支持事务,myisam 不支持事务

• 存储方式: innodb 由两个文件组成,一个存表结构,另一个存 数据和索引.myisam 由三个文件组成,一个存结构,一个数据,一个索引

• 数据与索引的聚簇: innodb 中主索引是聚簇类型,辅助索引是非聚簇.myisam 由于数据和索引不在同一个文件中,主索引和辅助索引都只存数据物理地址,因此是非聚簇索引