mysql利用覆盖索引避免回表优化查询

前言

说到覆盖索引之前，先要了解它的数据结构：B+树。

先建个表演示(为了简单,id按顺序建)：

id name
1 aa
3 kl
5 op
8　 aa
10 kk
11 kl
14 jk
16 ml
17 mn
18 kl
19 kl
22 hj
24 io
25 vg
29 jk
31 jk
33 rt
34 ty
35 yu
37 rt
39 rt
41 ty
45 qt
47 ty
53 qi
57 gh
61 dh

以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

B+树

B+树和B树是mysql索引的常用数据结构，B+树是B树的进一步优化，将上面的表转成图分析一下：

B+树的特点：

1.B+ 树非叶子节点上是不存储数据的，仅存储键值

2.叶子节点的数据是按照顺序排列的

3. B+ 树中各个页之间是通过双向链表连接

聚簇索引和非聚簇索引

B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。

聚簇索引：

以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。

这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。

这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。

非聚簇索引：

以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

如何用覆盖索引避免回表

为什么明明用了非主键索引还会回表，简单说就是非主键索引是非聚簇索引，在B+树叶子节点中只保存主键和该非主键索引，一次查询只能查到这两个字段，如果想查三个字段，就必须再查一次聚簇索引，这就是回表。

举个例子，表中新增一个字段age，我们用name建一个索引（非聚簇索引）

id name age
10　 zs 23
7 ls 54
13 ww 12
5 zl 76
8 xw 23
12 xm 43
17 dy 21 
select id,name from user where name = 'zs';

能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

select id,name,age from user where name = 'zs';

能够命中name索引，索引叶子节点存储了主键id，但age字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取age字段，效率会降低。　　

结论:那怎么做才能避免回表呢?很简单,将单列索引(name)升级为联合索引(name,age).

总结

到此这篇关于mysql利用覆盖索引避免回表优化查询的文章就介绍到这了,更多相关mysql覆盖索引避免回表优化查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

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

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

  • mysql索引覆盖实例分析

    本文实例讲述了mysql索引覆盖.分享给大家供大家参考,具体如下: 索引覆盖 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.这种查询速度非常快,称为"索引覆盖". 假设有一张t15表,在表中建立了一个联合索引:cp(cat_id,price) 当我们使用下面的sql语句,会出现索引覆盖的情况.不信我们可以来查看一下,这里的Extra中显示了Using index,表示这条sql语句刚好用到了索引覆盖. select price from t1

  • Mysql性能优化案例 - 覆盖索引分享

    Mysql性能优化案例 - 覆盖索引分享

    场景 产品中有一张图片表,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录 现在有一个根据user_id建立的索引:uid 查询语句也很简单:取得某用户的图片集合 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化前 执行查

  • Mysql覆盖索引详解

    Mysql覆盖索引详解

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

  • MySQL覆盖索引的使用示例

    什么是覆盖索引 创建一个索引,该索引包含查询中用到的所有字段,称为"覆盖索引". 使用覆盖索引,MySQL 只需要通过索引就可以查找和返回查询所需要的数据,而不必在使用索引处理数据之后再进行回表操作. 覆盖索引可以一次性完成查询工作,有效减少IO,提高查询效率. 使用示例 查询语句: SELECT col2, col3 FROM test_table WHERE col1 = xxx ORDER BY col2; 创建一个单列索引: ALTER TABLE `test_table` A

  • Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

    场景 产品中有一张图片表pics,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: 复制代码 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有多条图片记录,现在有一个根据user_id建立的索引:uid,查询语句也很简单:取得某用户的图片集合: 复制代码 代码如下: select picname, smallimg from pics where user_id = xxx; 优化

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

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

  • mysql利用覆盖索引避免回表优化查询

    mysql利用覆盖索引避免回表优化查询

    前言 说到覆盖索引之前,先要了解它的数据结构:B+树. 先建个表演示(为了简单,id按顺序建): id name 1 aa 3 kl 5 op 8 aa 10 kk 11 kl 14 jk 16 ml 17 mn 18 kl 19 kl 22 hj 24 io 25 vg 29 jk 31 jk 33 rt 34 ty 35 yu 37 rt 39 rt 41 ty 45 qt 47 ty 53 qi 57 gh 61 dh 以主键以外的列值作为键值构建的 B+ 树索引,我们称之为非聚集索引.

  • MySQL千万级数据的大表优化解决方案

    目录 1.数据库设计和表创建时就要考虑性能 设计表时要注意: 索引 简言之就是使用合适的数据类型,选择合适的索引 引擎 2.sql的编写需要注意优化 3.分区 分区的好处是: 分区的限制和缺点: 分区的类型: 4.分表 5.分库 mysql数据库中的表数据量几千万后,查询速度会很慢,日常各种卡慢,严重影响使用体验.在考虑升级数据库或者换用大数据解决方案前,必须优化现有mysql数据库表设计和sql语句. 1.数据库设计和表创建时就要考虑性能 mysql数据库本身高度灵活,造成性能不足,严重依赖开

  • MySQL使用MRG_MyISAM(MERGE)实现分表后查询的示例

    数据库大数据量优化是一门很大的学问,也是做为一名开发者需要掌握的专业技能. MySQL分表方式分为垂直分表和水平分表,这两种分表形式都比较简单,简单理解垂直分表指的是:表的记录并不多,但是字段却很长,表占用空间很大,检索表的时候需要执行大量的IO,严重降低了性能.这时需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系.而水平分表则是在同一个数据库内,把同一个表的数据按一定规则拆到多个表中,目的是优化单一表数据量过大而产生的性能问题,避免IO争抢并减少锁表的几率. 实现分表很简单,复杂的是

  • MySQL回表的性能伤害程度有多大

    目录 1回表的性能消耗 2覆盖索引 1 回表的性能消耗 无论单列索引 还是 联合索引,一个索引就对应一个独立的B+索引树,索引树节点仅包含: 索引里的字段值 主键值 即使根据索引树按条件找到所需数据,也仅是索引里的几个字段的值和主键值,万一你搞个select *,那就还得其他字段,就需回表,根据主键到聚簇索引里找,聚簇索引的叶节点是数据页,找到数据页才能把一行数据所有字段值读出来.所以类似 select * from table order by xx1,xx2,xx3 得从联合索引的索引树里按

  • MySQL中的回表和索引覆盖示例详解

    MySQL中的回表和索引覆盖示例详解

    目录 索引类型 索引结构 非聚簇索引查询 索引覆盖 总结 索引类型 聚簇索引: 叶子节点存储的是行记录,每个表必须要有至少一个聚簇索引.使用聚簇索引查询会很快,因为可以直接定位到行记录 普通索引:二级索引,除聚簇索引外的索引,即非聚簇索引.普通索引叶子节点存储的是主键(聚簇索引)的值. 聚簇索引递推规则: 如果表设置了主键,则主键就是聚簇索引 如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引 索引结

  • MySQL 回表,覆盖索引,索引下推

    MySQL 回表,覆盖索引,索引下推

    目录 回表 覆盖索引 索引下推 无索引下推: 查看索引下推的状态 有索引下推: 开启索引下推 回表 在研究mysql二级索引的时候,发现Mysql回表这个操作,往下研究了一下 字面意思,找到索引,回到表中找数据 解释一下就是: 先通过索引扫描出数据所在的行,再通过行主键ID 取出数据. 举个例子说明: SELECT * FROM INNODB_USER WHERE AGE = 18 AND USER_NAME LIKE '模糊查%'; 假如age和user_name两个字段是个联合索引,我们通过

  • mysql回表致索引失效案例讲解

    mysql回表致索引失效案例讲解

    简介 mysql的innodb引擎查询记录时在无法使用索引覆盖的场景下,需要做回表操作获取记录的所需字段. mysql执行sql前会执行sql优化.索引选择等操作,mysql会预估各个索引所需要的查询代价以及不走索引所需要的查询代价,从中选择一个mysql认为代价最小的方式进行sql查询操作.而在回表数据量比较大时,经常会出现mysql对回表操作查询代价预估代价过大而导致索引使用错误的情况. 案例 示例如下,在5.6版本的mysql.1CPU2G内存的Linux环境下,新建一个测试表,并创建将近

  • MySQL 覆盖索引的优点

    MySQL 覆盖索引的优点

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

随机推荐