2022/12/17 mysql 索引基本原理解读

@Autowire

已于 2022-12-26 15:53:16 修改

阅读量440

点赞数

分类专栏：数据库-MySQL 文章标签： mysql 数据库 java

于 2022-12-17 16:27:10 首次发布

本文链接：https://blog.csdn.net/zs18753479279/article/details/128351993

版权

数据库-MySQL 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

1什么是索引

索引是帮助MySQL 高效获取数据的数据结构，通过使用索引可以在查询的过程中，使用优化隐藏器，提高系统的性能。
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
在这里插入图片描述
优势

类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势
1)实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2)虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE，因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

2索引的分类

类别	特点
普通索引	由关键字KEY或INDEX定义的索引加快对数据的访问速度普通索引允许被索引的数据列包含重复的值
唯一索引	某个数据列将只包含彼此各不相同的值 UNIQUE 唯一索引可以保证数据记录的唯一性，可以允许空值但是只能有一个
主键索引	一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY 来创建，不可以为空值
联合索引	索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引

3索引的基本操作

3.1建表时就设计好索引

CREATE TABLE `tb_user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `user_name` varchar(255) NOT NULL DEFAULT '',
  `user_age` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_name_age` (`user_name`,`user_age`),
  KEY `index_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

3.2ALTER 命令添加和删除索引

ALTER TABLE tb_user_info ADD INDEX index_id_name_age(user_id, user_name, user_age);

在这里插入图片描述

ALTER TABLE tb_user_info DROP INDEX index_id_name_age;

在这里插入图片描述

4 复合索引-最左前缀原理

where子句中使用最频繁的一列放在最左边；我们在（a,b,c）字段上建了一个联合索引，所以这个索引是先按a 再按b 再按c进行排列的，所以：以下的查询方式都可以用到索引

select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；

上面三个查询按照 **（a ）, （a，b ）,（a，b，c ）**的顺序都可以利用到索引，这就是最左前缀匹配。

如果查询语句是：
select * from table where a=1 and c=3； 那么只会用到索引a。
如果查询语句是：
select * from table where b=2 and c=3； 因为没有用到最左前缀a，所以这个查询是没有用到索引的。

5索引的底层原理 B+树

CREATE TABLE `t_emp` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test-center`.`t_emp` (`id`, `name`, `age`) VALUES (1, 'a', 23);
INSERT INTO `test-center`.`t_emp` (`id`, `name`, `age`) VALUES (8, 'f', 53);
INSERT INTO `test-center`.`t_emp` (`id`, `name`, `age`) VALUES (2, 'b', 26);
INSERT INTO `test-center`.`t_emp` (`id`, `name`, `age`) VALUES (9, 'v', 13);
INSERT INTO `test-center`.`t_emp` (`id`, `name`, `age`) VALUES (3, 'c', 27);
INSERT INTO `test-center`.`t_emp` (`id`, `name`, `age`) VALUES (4, 'a', 32);
INSERT INTO `test-center`.`t_emp` (`id`, `name`, `age`) VALUES (5, 'd', 22);
INSERT INTO `test-center`.`t_emp` (`id`, `name`, `age`) VALUES (6, 'd', 22);
INSERT INTO `test-center`.`t_emp` (`id`, `name`, `age`) VALUES (7, 'e', 21);

在这里插入图片描述
查询出来的时候，id-是有序的。

这是为什么呢？主键索引进行了排序。

B+树结构：

B树结构：

B+Tree是在B-Tree(B树)基础上的一种优化，使其更活合实现外存储索结构，lnnoDB存储引警就是用B+Tree实现其索引结构，B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值，而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小，当存的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/0次数，进而影响查询效率；
在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

顶层目录页是常驻内存的，是不用I/O的。

6 为什么MySQL选择B+树作为索引结构？

磁盘预读：

内存跟磁盘在进行交互的时候有一个最小的逻辑单位称之为页，一般是4k或者8k，由操作系统决定，我们在进行数据读取的时候，一般会读取页的整数倍，也就是4k，8k， 16k。innodb存储引擎在进行数据加载的时候读取的是16kb的数据。

对于数据库查询时的优化主要考虑的就是：1减少IO的次数；2减小一次IO的量。

一般使用的索引结构主要就是3种：Hash、B-树、B+树。

不采用Hash主要是因为：对于Hash这种数据结构而言，在进行数据存储的时候往往会出现Hash冲突，造成数据的散列不均；无序的排列方式->不适合作为范围查询的数据结构，当然Innodb引擎采用的也有自适应Hash这种数据结构。

不采用B-树主要是因为：没有重复的数据；树的一个节点=索引+data+指针，这样一个节点存储的索引数据量很少，当需要向数据库中插入更多的数据的时，树的层数不断地变深，造成IO的次数增多，影响性能。

不采用红黑树：1、红黑树必须存在内存里的，数据库表太大了，存不进去；2、在大规模数据存储的时候，红黑树（二叉查找树）往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。根据磁盘查找存取的次数往往由树的高度所决定，红黑树查找一个节点最多要查logN层，每一层都是一个内存页。虽然你只是想找一个节点，但硬盘必须一次读一个页，那么一共logN次IO，消耗太大。

采用B+树主要是因为：有重复数据；数据data值存储在叶子节点中，非叶节点=索引（key）+ 指针；这样一个节点就存储更多的索引，三层的B+树，就达到千万级别的数据存储。

6索引结构

索引是在MYSQL的存储引擎层中实现的，而不是在服务层实现的。所以每种存储擎的索引都不一定完全相同，也不是所有的存储引擎整都支持所有的索引类型的。MySQL目前提供了以下2种索引:
BTREE 索 :最常见的索引类型，大部分索引都支持 B 树索引。
HASH 索引: 只有Memory引擎支持，使用场景简单。
在这里插入图片描述
我们平常所说的索引，如果没有特别指明，都是指B+树。其中聚簇索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

7聚簇索引 & 非聚簇索引

mysql的索引类型跟存储引擎是相关的，innodb存储引擎数据文件跟索引文件全部放在ibd文件中，而myisam的数据文件放在myd文件中，索引放在myi文件中，其实区分聚簇索引和非聚簇索引非常简单：只要判断数据跟索引是否存储在一起就可以了。
聚簇索引：数据 && 索引存储在一起，没有存储在一起的叫做非聚簇索引。
innodb存储引擎在进行数据插入的时候，数据必须要跟某一个索引列存储在一起，这个索引列可以是主键，如果没有主键，选择唯一键，如果没有唯一键，选择6字节的row_id来进行存储，数据必定是跟某一个索引绑定在一起的，绑定数据的索引叫做聚簇索引，非聚簇索引的叶子节点中存储的数据不再是整行的记录，而是聚簇索引的索引值，innodb中既有聚簇索引也有非聚簇索引。

如果查询条件为非聚簇索引，需要扫描两次B+树：第一次扫描通过普通索引定位到聚簇索引的值，然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据。
在这里插入图片描述
innoDB使用的是聚族索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where d =14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键，第二步使用主键在聚簇索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

这就引出了回表查询：
先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。我们应该避免这种情况，所以使用索引覆盖来解决这个问题。

8回表查询

常见的方法是：将被查询的字段，建立到联合索引里去。

 create table user(
		id int(10) auto_increment,
		name varchar(30),
		age tinyint(4),
		primary key (id),
		index idx_age (age)
)engine=innodb;

insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);

1、实现：

select id,age from user where age = 10;

explain分析：因为age是普通索引，使用到了age索引，通过一次扫描B+树即可查询到相应的结果，这样就实现了覆盖索引
在这里插入图片描述
2、实现：

select id,age,name from user where age = 10;

explain分析：age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询。
在这里插入图片描述
为了实现索引覆盖，需要建组合索引idx_age_name(age,name)：

drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);

explain分析：此时字段age和name是组合索引idx_age_name，查询的字段id、age、name的值刚刚都在索引树上，只需扫描一次组合索引B+树即可，这就是实现了索引覆盖，此时的Extra字段为Using index表示使用了索引覆盖。
在这里插入图片描述

9使用聚簇索引的优势

MYISAM使用的是非聚簇索引，非聚簇索引的两个B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助索引B+树存储了辅助键，表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
在这里插入图片描述
问题: 每次使用辅助索引检索都要经过两次B+树查找，看上去聚索引的效率明显要低于非非聚簇索引，这不是多此一举吗? 聚族索引的优势在哪?

1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中缓存器)，再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化，或者是我们需要查找的数据，在上一次I/O读写的存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因附注索引存放的是主键值，减少了辅助索引占用的存储空间大小。

10主键最好不要使用uuid

当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。

建议使用int类型的自增，方便排序并且默认会在索引树的未尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到I/O操作读取到的数据量。

11为什么主键通常建议使用自增id

聚簇索引的教据的物理存放顺序与索引顺序是一致的，只要索引是相邻的，那么对应的据一定也是相邻的存放在磁盘上的。如果主键不是自增id，那么它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

12索引无法使用到的情况

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
2、当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。
3、对于联合索引而言，违反最左匹配原则。

@Autowire

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2022/12/17 mysql 索引基本原理解读

索引是帮助MySQL 高效获取数据的数据结构，通过使用索引可以在查询的过程中，使用优化隐藏器，提高系统的性能。优势类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。劣势1)实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
复制链接

扫一扫