我的编程空间,编程开发者的网络收藏夹
学习永远不晚

mysql聚集索引、辅助索引、覆盖索引、联合索引的使用

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

mysql聚集索引、辅助索引、覆盖索引、联合索引的使用

《MySQL技术内幕 InnoDB存储引擎》学习笔记

聚集索引(Clustered Index)

聚集索引就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据。

举个例子,直观感受下聚集索引。

创建表t,并以人为的方式让每个页只能存放两个行记录(不清楚怎么人为控制每页只存放两个行记录):

这里写图片描述

最后《MySQL技术内幕》的作者通过分析工具得到这棵聚集索引树的大致构造如下:

这里写图片描述

聚集索引的叶子节点称为数据页,每个数据页通过一个双向链表来进行链接,而且数据页按照主键的顺序进行排列。

如图所示,每个数据页上存放的是完整的行记录,而在非数据页的索引页中,存放的仅仅是键值及指向数据页的偏移量,而不是一个完整的行记录。

如果定义了主键,InnoDB会自动使用主键来创建聚集索引。如果没有定义主键,InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引,InnoDB会隐式定义一个主键来作为聚集索引。

辅助索引(Secondary Index)

辅助索引,也叫非聚集索引。和聚集索引相比,叶子节点中并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点的索引行还包含了一个书签(bookmark),该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。

还是以《MySQL技术内幕》中的例子,来直观感受下辅助索引的模样。

还是以上面的表t为例,在列c上创建非聚集索引:

这里写图片描述

然后作者通过分析工作得到辅助索引和聚集索引的关系图:

这里写图片描述

可以看到辅助索引idx_c的叶子节点中包含了列c的值和主键的值。

以Key为7fffffff为例,7是0111,0代表负数,真实的值应该取反加1,是-1,这是列c的值。Pointer是80000001,8是1000,1代表正数,所以80000001代表1,是主键的值。

覆盖索引(Covering index)

InnoDB存储引擎支持覆盖索引,即从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。

使用覆盖索引有啥好处?

  • 可以减少大量的IO操作

上图中我们知道,如果要查询辅助索引中不含有的字段,得先遍历辅助索引,再遍历聚集索引,而如果要查询的字段值在辅助索引上就有,就不用再查聚集索引了,这显然会减少IO操作。

比如上图中,以下sql可以直接使用辅助索引,

select a from where c = -2;
  • 有助于统计

假设存在如下表:

  CREATE TABLE `student` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` varchar(255) NOT NULL,
  `school` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_school_age` (`school`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果在该表上执行:

select count(*) from student

优化器会怎么处理?

遍历聚集索引和辅助索引都可以统计出结果,但辅助索引要远小于聚集索引,所以优化器会选择辅助索引来统计。执行explain命令:

这里写图片描述

key和Extra显示使用了idx_name这个辅助索引。

还有,假设执行以下sql:

select * from student where age > 10 and age < 15

因为联合索引idx_school_age的字段顺序是先school再age,按照age做条件查询,通常不走索引:

这里写图片描述

但是,如果保持条件不变,查询所有字段改为查询条目数:

select count(*) from student where age > 10 and age < 15

优化器会选择这个联合索引:

这里写图片描述

联合索引

联合索引是指对表上的多个列进行索引。

以下为创建联合索引idx_a_b的示例:

这里写图片描述

联合索引的内部结构:

这里写图片描述

联合索引也是一棵B+树,其键值数量大于等于2。键值都是排序的,通过叶子节点可以逻辑上顺序的读出所有数据。数据(1,1)(1,2)(2,1)(2,4)(3,1)(3,2)是按照(a,b)先比较a再比较b的顺序排列。

基于上面的结构,对于以下查询显然是可以使用(a,b)这个联合索引的:

select * from table where a=xxx and b=xxx ;

select * from table where a=xxx;

但是对于下面的sql是不能使用这个联合索引的,因为叶子节点的b值,1,2,1,4,1,2显然不是排序的。

select * from table where b=xxx

联合索引的第二个好处是对第二个键值已经做了排序。举个例子:

create table buy_log(
    userid int not null,
    buy_date DATE
)ENGINE=InnoDB;

insert into buy_log values(1, '2009-01-01');
insert into buy_log values(2, '2009-02-01');

alter table buy_log add key(userid);
alter table buy_log add key(userid, buy_date);

当执行

select * from buy_log where user_id = 2;

时,优化器会选择key(userid);但是当执行以下sql:

select * from buy_log where user_id = 2 order by buy_date desc;

时,优化器会选择key(userid, buy_date),因为buy_date是在userid排序的基础上做的排序。

如果把key(userid,buy_date)删除掉,再执行:

select * from buy_log where user_id = 2 order by buy_date desc;

优化器会选择key(userid),但是对查询出来的结果会进行一次filesort,即按照buy_date重新排下序。所以联合索引的好处在于可以避免filesort排序。

到此这篇关于mysql聚集索引、辅助索引、覆盖索引、联合索引的使用的文章就介绍到这了,更多相关聚集索引、辅助索引、覆盖索引、联合索引内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

mysql聚集索引、辅助索引、覆盖索引、联合索引的使用

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

mysql中聚集索引、辅助索引、覆盖索引、联合索引怎么用

这篇文章主要介绍了mysql中聚集索引、辅助索引、覆盖索引、联合索引怎么用,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让小编带着大家一起了解一下。聚集索引(Clustered Index)聚集索引就是
2023-06-29

5. 索引与算法—B+树的操作、辅助索引与聚集索引、Cardinality、联合索引、覆盖索引、MRR/ICP、哈希算法、全文索引

5.3 B+ 树B+ 树是为磁盘或其他直接存储辅助设备设计的一种平衡查找树。在B+树中,所有记录都是按照键值大小顺序存放在同一层的叶子节点上,由叶子节点指针进行连接,双向链表连接。5.3.1 B+ 树的插入操作考虑一下三种情况:Leaf Page满Index
5. 索引与算法—B+树的操作、辅助索引与聚集索引、Cardinality、联合索引、覆盖索引、MRR/ICP、哈希算法、全文索引
2015-09-03

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 - G

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 What"s Index ?索引就是帮助RDBMS高效获取数据的数据结构。索引可以让我们避免一行一行进行全表扫描。它的价值就是可以帮助你对数据进行快速定位。 索引分类按照功能逻辑来分普通
MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序 - G
2018-09-14

MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引是什么

今天小编给大家分享一下MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引是什么的相关知识点,内容详细,逻辑清晰,相信大部分人都还太了解这方面的知识,所以分享这篇文章给大家参考一下,希望大家阅读完这篇文章后有所收获,下面我们一起来了解一下
2023-04-21

MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引详细介绍

目录一、索引类型二、聚簇索引和非聚簇索引三、覆盖索引四、联合索引最左前缀匹配原则索引下推五、唯一索引一、索引类型索引根据底层实现可分为B-Tree索引和哈希索引,大部分时候我们使用的都是B-Tree索引,因为它良好的性能和特性更适合于构建
2023-04-19

如何通过索引提升PHP与MySQL的联合索引和覆盖索引查询效率?

随着互联网的发展和数据量的增加,数据库的性能优化变得越来越重要。索引是提升数据库查询性能的一种重要手段。在PHP与MySQL的应用中,通过合理使用联合索引和覆盖索引,可以大幅度提升查询效率。本文将介绍如何使用联合索引和覆盖索引进行查询优化,
2023-10-21

MySQL中复合索引和覆盖索引的区别详解

目录前言准备复合索引覆盖索引总结前言准备我们先准备一张表和几个字段,方便介绍覆盖索引和复合索引。创建一个user表,表中有id、name、school、age字段。字段名字段类型idintnamevarcharschoolvarcha
MySQL中复合索引和覆盖索引的区别详解
2023-11-23

Mysql覆盖索引如何使用

在MySQL中,覆盖索引是指索引包含了查询需要的所有字段,这样MySQL可以直接使用索引来返回查询结果,而不需要再去访问数据表。这可以提高查询性能,减少IO操作。要使用覆盖索引,需要创建一个包含查询需要的所有字段的索引。然后在查询时,只选
Mysql覆盖索引如何使用
2024-04-09

MySQL 覆盖索引的优点

一个通常的建议是为WHERE条件创建索引,但这其实是片面的。索引应当为全部查询设计,而不仅仅是WHERE条件。索引确实能有效地查找数据行,但MySQL也能够使用索引获取列数据,这样根本不需要去读取一行数据。毕竟,索引的叶子节点包含了索引对应
2022-05-30

Mysql索引覆盖的实现

目录1.什么是覆盖索引2.覆盖索引为什么快3.SQL优化场景(1)无where条件(2)where条件区分度低(3)查询仅选择主键4.总结与建议1.什么是覆盖索引通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个
2023-03-03

mysql的联合索引(复合索引)的实现

联合索引 本文中联合索引的定义为(MySQL):ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`);联合索引的优点 若多个一条SQL,需要多个用到两个条件SELECT * FR
2022-05-29

MySQL 独立索引和联合索引的选择

通常会对多列索引缺乏理解,常见的错误是将很多列设置独立索引,或者是索引列使用错误的次序。我们在下一篇讨论索引列次序的问题,首先看一下多列独立索引的情况,以下面的表结构为例:CREATE TABLE test (c1 INT,c2 INT,c
2022-06-01

[MySQL] mysql索引的长度计算和联合索引

1.所有的索引字段,如果没有设置not null,则需要加一个字节。2.定长字段,int占4个字节、date占3个字节、char(n)占n个字符。3.变长字段,varchar(n),则有n个字符+两个字节。4.不同的字符集,一个字符占用的字节数不同。latin
[MySQL] mysql索引的长度计算和联合索引
2021-12-22

编程热搜

目录