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

目录
  • 聚集索引(Clustered Index)
  • 辅助索引(Secondary Index)
  • 覆盖索引(Covering index)
  • 联合索引

《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聚集索引、辅助索引、覆盖索引、联合索引的使用的文章就介绍到这了,更多相关聚集索引、辅助索引、覆盖索引、联合索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

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

    联合索引 本文中联合索引的定义为(MySQL): ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`); 联合索引的优点 若多个一条SQL,需要多个用到两个条件 SELECT * FROM `user_info` WHERE username='XX',password='XXXXXX'; 当索引在检索 password字段的时候,数据量大大缩小,索引的命中率减小,增大了索引的效率. 符合索引的索引体积比单独索引的体积要小,而且只是一

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

    通常会对多列索引缺乏理解,常见的错误是将很多列设置独立索引,或者是索引列使用错误的次序.我们在下一篇讨论索引列次序的问题,首先看一下多列独立索引的情况,以下面的表结构为例: CREATE TABLE test ( c1 INT, c2 INT, c3 INT, KEY(c1), KEY(c2), KEY(c3), ); 使用这种索引策略通常是一些权威的建议(例如在WHERE条件中用到的条件列增加索引)的结果.事实上,这是大错特错的,要评分的话顶多给1颗星.这种方式的索引与真正优化的索引相比,要慢

  • MySQL 覆盖索引的优点

    一个通常的建议是为WHERE条件创建索引,但这其实是片面的.索引应当为全部查询设计,而不仅仅是WHERE条件.索引确实能有效地查找数据行,但MySQL也能够使用索引获取列数据,这样根本不需要去读取一行数据.毕竟,索引的叶子节点包含了索引对应的值.当年能够读取索引就能够拿到想要的数据时为什么还去读数据行呢?当索引包含了所有查询的数据时,这个索引就称之为覆盖索引. 覆盖索引能够成为一个非常有力的工具并且能够显著改善性能.考虑一下不读数据只需要读取索引的情况: 索引值通常会比整个行存储空间小很多,因此

  • Mysql覆盖索引详解

    概念 如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index),也就是平时所说的不需要回表操作 判断标准 使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询 注意 1.覆盖索引也并不适用于任意的索引类型,索引必须存储列的值 2.Hash 和full-text索引不存储值,因此MySQL只能使用B-TREE 3.并且不同的存储引擎实现覆盖索引都是不同的 4.并

  • MySQL 的覆盖索引与回表的使用方法

    两大类索引 使用的存储引擎:MySQL5.7 InnoDB 聚簇索引 * 如果表设置了主键,则主键就是聚簇索引 * 如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引 * 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引 InnoDB的聚簇索引的叶子节点存储的是行记录(其实是页结构,一个页包含多行数据),InnoDB必须要有至少一个聚簇索引. 由此可见,使用聚簇索引查询会很快,因为可以直接定位到行记录. 普通索引 普通索引也叫二级索引,除聚簇索引外的

  • SQL Server 聚集索引和非聚集索引的区别分析

    聚集索引:物理存储按照索引排序非聚集索引:物理存储不按照索引排序优势与缺点聚集索引:插入数据时速度要慢(时间花费在"物理存储的排序"上,也就是首先要找到位置然后插入)查询数据比非聚集数据的速度快 汉语字典的正文本身就是一个聚集索引.比如,我们要查"安"字,就会很自然地翻开字典的前几页,因为"安"的拼音是"an",而按照拼音排序汉字的字典是以英文字母"a"开头并以"z"结尾的,那么&quo

  • mysql中关于覆盖索引的知识点总结

    如果一个索引包含(或覆盖)所有需要查询的字段的值,称为'覆盖索引'. 覆盖索引是一种非常强大的工具,能大大提高查询性能,只需要读取索引而不需要读取数据,有以下优点: 1.索引项通常比记录要小,所以MySQL访问更少的数据. 2.索引都按值得大小存储,相对于随机访问记录,需要更少的I/O. 3.数据引擎能更好的缓存索引,比如MyISAM只缓存索引. 4.覆盖索引对InnoDB尤其有用,因为InnoDB使用聚集索引组织数据,如果二级索引包含查询所需的数据,就不再需要在聚集索引中查找了. 限制: 1.

  • MySQL全文索引、联合索引、like查询、json查询速度哪个快

    查询背景 有一个表tmp_test_course大概有10万条记录,然后有个json字段叫outline,存了一对多关系(保存了多个编码,例如jy1577683381775) 我们需要在这10万条数据中检索特定类型的数据,目标总数据量:2931条 SELECT COUNT(*) FROM tmp_test_course WHERE `type`=5 AND del=2 AND is_leaf=1 我们在限定为上面类型的同时,还得包含下面任意一个编码(也就是OR查询) jy157768338177

  • Sql Server中的非聚集索引详细介

    非聚集索引,这个是大家都非常熟悉的一个东西,有时候我们由于业务原因,sql写的非常复杂,需要join很多张表,然后就泪流满面了...这时候就有DBA或者资深的开发给你看这个猥琐的sql,通过执行计划一分析...或许就看出了不该有的表扫描...万恶之源...然后给你在关键的字段加上非聚集索引后...才发现提速比阿斯顿马丁还要快...那么一个问题来了,为什么非聚集索引能提速这么快...怎么做到的???是不是非常的好奇??? 这篇我们来解开神秘面纱.  一:现象 先让我们一睹非聚集索引的真容,看看到底

  • 理解Sql Server中的聚集索引

    说到聚集索引,我想每个码农都明白,但是也有很多像我这样的猥程序员,只能用死记硬背来解决这个问题,什么表中只能建一个聚集索引,然后又扯到了目录查找来帮助读者记忆....问题就在这里,我们不是学文科,,,不需要去死记硬背,,,我们需要的就是能看到在眼里面的真实东西.....我们都喜欢聚集索引,因为它能够把无序的堆表记录变成有序,还玩起了B树...这样就把复杂度从N降低到了LogMN... 这样的话逻辑读,物理读就下来了.  一:现象 1:无索引的情况 还是老规矩,看个例子感受下,首先我有一个Prod

  • MySQL索引之聚集索引介绍

    在MySQL里,聚集索引和非聚集索引分别是什么意思,有什么区别? 在MySQL中,InnoDB引擎表是(聚集)索引组织表(clustered index organize table),而MyISAM引擎表则是堆组织表(heap organize table). 也有人把聚集索引称为聚簇索引. 当然了,聚集索引的概念不是MySQL里特有的,其他数据库系统也同样有. 简言之,聚集索引是一种索引组织形式,索引的键值逻辑顺序决定了表数据行的物理存储顺序,而非聚集索引则就是普通索引了,仅仅只是对数据列创

  • 浅析SQL Server 聚焦索引对非聚集索引的影响

    前言 在学习SQL 2012基础教程过程中会时不时穿插其他内容来进行讲解,相信看过SQL Server 2012 T-SQL基础教程的童鞋知道前面写的所有内容并非都是摘抄书上内容,如若是这样那将没有任何意义,学习的过程必须同时也是一个思考的过程,无论是独立思考也好还是查资料也罢都是思考而非走马观花,要不然过一段时间又会健忘.简短的内容,深入的理解. 话题 非聚集索引定义:非聚集索引也是一个B树结构,与聚集索引不同的是,B树的叶子节点存的是指向堆或聚集索引的指针.你真的理解了吗??你能举出例子吗?

随机推荐