MySQL索引底层原理

最新推荐文章于 2023-03-27 14:23:49 发布

断橋殘雪

最新推荐文章于 2023-03-27 14:23:49 发布

阅读量1.1k

点赞数 4

分类专栏： MySQL 文章标签： MySQL 索引 MySQL索引 MySQL索引底层 MySQL索引原理

本文链接：https://blog.csdn.net/qq_35620501/article/details/103224121

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章目录

什么是索引

索引时帮助我们高校获取数据的排好序的数据结构
索引它是一个文件
走索引的本质是在索引B+树中从上到下根据索引指向的叶子节点进行扫描

索引的类型

从逻辑角度进行分类

主键索引

ALTER TABLE table_name ADD PRIMARY KEY ( column )

顾名思义，它是一张表中主键的一种唯一性索引

唯一索引

ALTER TABLE table_name ADD UNIQUE ( column )

索引中的值只能够是唯一的，如果新增/修改的索引数据不是唯一的，那么将会报错

普通索引

ALTER TABLE table_name ADD INDEX index_name ( column )

或者联合索引

ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

基本的索引类型，不对唯一性约束

全文索引

ALTER TABLE table_name ADD FULLTEXT ( column)

从物理角度进行分类

数据存储在磁盘中

InnoDB引擎：
使用InnoDB作为表的存储引擎时，会生成两个文件：
.frm结尾的文件：用来存储表的结构定义；
.ibd结尾的文件：用来存储索引结构数据和元素数据

MyISAM引擎：
使用MyISAM作为表的存储引擎时，会生成三个文件：
.frm结尾的文件：用来存储表的结构定义；
.MYD结尾的文件：用来存储表中一行一行的数据；
.MYI结尾的文件：用来存储表中定义的索引；
它不支持事务，每次查找数据都要经过两个数据文件的交互

聚集索引

主键索引的叶子节点包含完整的数据记录
叶子节点data区域存储的是元素数据

非聚集索引

索引的叶子节点不包含完整的数据记录，每次查找数据都要经过两个数据文件的交互
叶子节点data区域存储的不是元素数据，而是该元素在磁盘文件中的磁盘指针

从数据结构角度进行分类

B+树索引

1、元素数据都存在叶子节点中，非叶子节点只存储索引

2、关键的索引数据在非叶子节点上做了冗余，完整的数据存储在叶子节点上

3、前一个叶子节点指向后一个叶子节点

4、一个节点存储更多的数据，能够很好的控制数的高度

hash索引

对主键进行求hash值，求出的hash值对数据的地址值有映射关系，就能够找到该数据，仅支持精准查找。但是范围查找需要全表扫描效率很慢，范围查找很少的情况下可以考虑使用hash表

为什么InnoDB默认选用B+树作为数据的存储结构

hash：

二叉查找树(AVL树)：

1、左子树的元素小于父元素，右子树的元素大于父元素

2、随着数据的增加，树的高度不可控，当元素越多时，树的高度会很大，这样相对于磁盘的IO次数也会很大

3、当主键id为自增的时候，二叉树表现为一个链表(链表是特殊的平衡二叉树)，走索引查找数据时相当于全表扫描

4、AVL(平衡二叉树)增加一个元素，树的深度有可能会+1，相对于磁盘IO +1

红黑树：

相比于AVL树，在增加数据时，会通过左旋右旋优化树的高度，在树的高度上有了优化，但是红黑树的高度还是不可控的

B树：

1、相比于AVL树，B树的一个节点能存储多个元素，一个节点中的元素从左到右还是递增的

2、每个节点都有指针指向子节点，叶子节点的指针为空

3、B树每个节点都能存储元素

B+树：

1、degree：定义每个节点中数据的个数

2、元素数据都存在叶子节点中，非叶子节点只存储索引

3、关键的索引数据在非叶子节点上做了冗余，完整的数据存储在叶子节点上

4、前一个叶子节点指向后一个叶子节点

5、一个节点存储更多的数据，能够很好的控制数的高度

主键索引的数据结构

create table if not exists innodb_test(
	id int not null auto_increment,
	name varchar(10),
	age int,
	sex varchar(10),
	primary key(id)
)engine=innodb default charset=utf8mb4;	

insert into innodb_test(id,name,age,sex) values(21,'d',19,'男');
insert into innodb_test(id,name,age,sex) values(25,'h',25,'男');
insert into innodb_test(id,name,age,sex) values(30,'b',25,'女');
insert into innodb_test(id,name,age,sex) values(34,'c',24,'女');
insert into innodb_test(id,name,age,sex) values(15,'a',20,'女');
insert into innodb_test(id,name,age,sex) values(18,'e',20,'女');
insert into innodb_test(id,name,age,sex) values(20,'f',21,'男');
insert into innodb_test(id,name,age,sex) values(35,'x',30,'男');
insert into innodb_test(id,name,age,sex) values(49,'y',27,'女');

这个表它只有id的主键索引

在这里插入图片描述

联合索引的数据结构

create table if not exists innodb_test(
	id int not null auto_increment,
	name varchar(10),
	age int,
	sex varchar(10),
	primary key(id),
	KEY `idx_innodb_test_name_age` (name,age)
)engine=innodb default charset=utf8mb4;	

insert into innodb_test(id,name,age,sex) values(21,'d',19,'男');
insert into innodb_test(id,name,age,sex) values(25,'h',25,'男');
insert into innodb_test(id,name,age,sex) values(30,'b',25,'女');
insert into innodb_test(id,name,age,sex) values(34,'c',24,'女');
insert into innodb_test(id,name,age,sex) values(15,'a',20,'女');
insert into innodb_test(id,name,age,sex) values(18,'e',20,'女');
insert into innodb_test(id,name,age,sex) values(20,'f',21,'男');
insert into innodb_test(id,name,age,sex) values(35,'x',30,'男');
insert into innodb_test(id,name,age,sex) values(49,'y',27,'女');

这个表它有id的主键索引，还有name和age字段组成的联合索引

在这里插入图片描述

主键索引的data数据为什么全在叶子节点上？

因为一个节点大小固定的情况下，如果不存data数据，那么将能存储更多的索引元素，那么该节点的子节点也会更多，从而整个B+树的深度也会更少，最后与磁盘的IO次数也会更少。

MySQL默认设置1个节点为1页的大小为16kb

1、当选用bigint类型作为主键时，占用8个字节即8B，指针的大小为6个字节即6B。那么一个节点能大约储存(16*1024-8)/(8+6)=1169个元素

2、树的第二层也是存储索引元素时，第三层当一个data元素的大小约为1K时，最终一个三层的B+树能储存11691169(16/1)=2186万个数据

3、也就是说3次磁盘IO的情况下就能够储存2186万个叶子节点元素

什么是聚集索引和非聚集索引

数据存储在磁盘中

InnoDB：使用MyISAM作为表的存储引擎时，会生成两个文件：
.frm结尾的文件：用来存储表的结构定义
.ibd结尾的文件：用来存储索引结构数据和元素数据
MyISAM：使用MyISAM作为表的存储引擎时，会生成三个文件：
.frm结尾的文件：用来存储表的结构定义
.MYD结尾的文件：用来存储表中一行一行的数据
.MYI结尾的文件：用来存储表中定义的索引
它不支持事务，每次查找数据都要经过两个数据文件的交互

非聚集索引：
索引的叶子节点不包含完整的数据记录，每次查找数据都要经过两个数据文件的交互
叶子节点data区域存储的不是元素数据，而是该元素在磁盘文件中的磁盘指针

聚集索引：
主键索引的叶子节点包含完整的数据记录
叶子节点data区域存储的是元素数据

为什么建议使用InnoDB引擎的表使用整型的自增主键而不是UUID？

1、使用InnoDB引擎的表的数据都存储在主键索引中的B+树中。如果建表时没有指定主键，那么MySQL会为我们生成一个隐藏的主键用来维护表的数据并存储元素(因为元素数据都存储在主键索引B+树中的叶子节点中)，但是在查询的时候并不能查询出来，同时隐藏的主键也占用了磁盘空间

2、使用InnoDB引擎的表的数据在插入的时候，会使用主键对元素进行排序，最终插入到主键索引B+树中的叶子节点根据主键递增。如果主键不是整型的，那么MySQL需要花费时间在对主键元素进行字符转换；如果主键不是自增的，MySQL在插入元素时需要对插入的逐渐进行插入排序，数据在插入时有可能还需要对节点进行分裂，然后再平衡，从而浪费性能；如果主键时整型的自增的，那么MySQL直接在最后面添加元素，从而提高了插入元素的效率。自增的主键索引它会尽可能的避免数据插入到叶子节点中时对叶子节点产生分裂然后再平衡

3、UUID字符串长，并没有实际的意义，占用的内存空间大，字符无序。在新增数据时，由于需要对主键进行排序，当主键为UUID时，需要进行很复杂的排序，浪费性能，而且当数据需要的空间超出了节点所能存储的大小时，数据在插入时有还需要对节点进行分裂，然后再B+树平衡。使用自增的主键作为主键时，直接走索引确定新增的数据在叶子节点最右侧新增即可

为什么非主键索引的叶子节点中data数据存储的是主键值？

1、如果在非主键索引的叶子节点中存储完整的数据，那么叶子节点内存储的元素数量更少了，可能会造成需要更多的磁盘IO，需占用更多的内存

2、在修改表数据时，需要同时修改主键索引和非主键索引中的数据，那么就存在数据一致性的问题，在高并发的情况下，数据会出现数据不一致的情况

什么是最左前缀原则

使用联合索引时需要遵守最左前缀原则

最左前缀原则：
拿KEY idx_innodb_test_name_age_sex (name,age，sex)为例，当where条件中为name时，或者为name和age，或者为name、age、sex才能使用索引，并且从左至右查找时，遇到范围、模糊查询(>、<、between、like)就会停止继续向右匹配索引中的数据

因为索引在一个节点上储存是按照索引字段的顺序进行储存的，只能先比较前一个字段才能比较后一个字段

为什么like模糊查询时，%在前不能使用索引而是走全表扫描

%在前，不能匹配到索引，因为匹配索引需要从左至右匹配，最左边为%，不能确定具体字符，不能匹配索引。like ‘name%’，就能使用索引，因为，‘name%最左边为准确的字符，可以与索引进行匹配’

为什么不建议在where条件中进行运算

举例：select * from t1 where age + 1 = 21将不能使用索引需走全表扫描，因为在where条件中进行运算，MySQL将是需要重新建一颗age + 1的索引B+树，这样的效率更低，可能SQL执行优化器会觉得使用全表扫描是一条最短路径

MySQL索引的一些总结

数据库中的数据是存储在磁盘中的，每一次从磁盘中取数据到内存，叫做一次磁盘IO
建立索引就是对数据进行排序，排序就是比较大小，B+树其实就是帮助我们排序的一种数据结构
insert在插入数据的时候就会对主键ID进行升序排序
B+树一个节点可以储存多个元素
非叶子节点都会冗余一份在叶子节点，叶子节点存储了所有的元素；叶子节点之间用双向指针进行连接
当新建的表中没有设置主键，那么会先看是否有唯一索引，如果没有，会生成一个隐藏的自增的id，select不能够查询出来，所以说使用InnoDB引擎的表一定会有一个主键id
where条件能否利用索引本质上就是看此条件能不能和某个B+树索引进行比较大小从而查询数据
最左前缀原则：本质就是对多个字段联合组成的复合字段进行比较大小，类似字符串比较大小，只有给定了最左侧的字段，才能去比较后续的字段
全表扫描：在InnoDB中，表中所用行数据都在主键索引的叶子节点中，所以全表扫描就是直接扫描主键索引的叶子节点
覆盖索引：对于某一个SQL在执行时，如果发现所要查询的数据在某一个索引上也存在时(除开主键索引)，那么就可以直接利用这个索引进行查询获取数据，而不需要回表
在MySQL中，数字与字符串进行运算时，统一会将字符串转换成数字，非数字字符串会统一转化为0
InnoDB引擎中：innodb_page_size页，1页=16kb=16384字节，这个值可以进行修改，但是一般不进行修改，查询1页的大小命令：
show global status like 'innodb_page_size'或show variables like ‘innodb_page_size’`
并不是满足了走索引的条件就一定会走索引

当a为主键，b、c、d的联合索引
select * from t1 where b > 1可能就不会走索引，因为一条SQL在执行的过程中需要经过MySQL的SQL执行优化器的选择优化，它可能认为在数据量少的情况下直接就走全表扫描是一条最短路径，而使用(b,c,d)的联合索引还需要进行回表操作，更浪费性能和时间