MySQL 系列教程之（十）索引原理：B+ 树与索引丨【绽放吧！数据库】

索引与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树

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

【绽放吧！数据库】有奖征文火热进行中：https://bbs.huaweicloud.com/blogs/285617

MySQL 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。