精华内容
下载资源
问答
  • 什么是覆盖索引优点有哪些?

    千次阅读 2020-02-13 11:19:42
    覆盖索引:一个索引中包含所有需要查询字段的值 优点:无需回表 1.索引条目通常远小于数据行大小,只需要读取索引,则mysql会极大地减少数据访问量。 2.因为索引是按照列值顺序存储的,所以对于IO密集的范围查找会...

    覆盖索引:一个索引中包含所有需要查询字段的值

    优点:无需回表

    1.索引条目通常远小于数据行大小,只需要读取索引,则mysql会极大地减少数据访问量。
    2.因为索引是按照列值顺序存储的,所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
    3.一些存储引擎如myisam在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用
    4.innodb的聚簇索引,覆盖索引对innodb表特别有用

    展开全文
  • MySQL覆盖索引

    2018-05-01 15:56:50
    正确地创建和使用索引是实现高性能查询的基础。本文简要介绍MySQL覆盖索引覆盖索引 根据查询的WHERE条件创建合适的索引,不过只是索引优化的一个方面。...覆盖索引优点 被索引覆盖的查询即为索引覆盖...

    正确地创建和使用索引是实现高性能查询的基础。本文简要介绍MySQL覆盖索引。

    覆盖索引

    根据查询的WHERE条件创建合适的索引,不过只是索引优化的一个方面。设计优秀的索引还应该考虑到整个查询,MySQL也可以使用索引来直接获取列的数据,这样就不需要读取数据行了。
    如果一个索引包含或覆盖所有需要查询的字段的值,我们就成之为“覆盖索引”。

    覆盖索引的优点

    被索引覆盖的查询即为索引覆盖查询。索引覆盖查询只需要扫描索引而无需回表,不仅能够极大地提高性能,而且也有很大其他好处:

    • 索引条目通常远小于数据行大小,所以如果只需要读取索引,MySQL就会极大地减少数据访问量。因为索引比数据更小,就更容易把全部索引放入内存中。
    • 因为索引是按照列值顺序存储的,所以对于I/O密集型的范围查询会比随机从磁盘读取数据的I/O要少得多。
    • 一些存储引擎如MyISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用。这可能导致严重的性能问题,尤其是那些系统调用占了数据访问中的最大开销的场景。
    • 由于InnoDB的聚簇索引,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值,所以如果二级索引能够覆盖查询,则可以避免对主键索引的二次查询。

    覆盖索引的限制

    • 不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引等都不存储索引列的值,索引MySQL只能使用B-Tree索引做覆盖索引。

    举例

    新建author表如下:

    CREATE TABLE `author` (
       `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
       `name` varchar(32) NOT NULL COMMENT '姓名',
       `gender` tinyint(1) NOT NULL COMMENT '性别,0-男,1-女',
       `age` tinyint(3) NOT NULL DEFAULT '0' COMMENT '年龄',
       `email` varchar(32) NOT NULL DEFAULT '' COMMENT '邮箱',
       `homepage` varchar(128) NOT NULL DEFAULT '' COMMENT '主页',
       `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
       `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
       PRIMARY KEY (`id`),
       KEY `idx_author_union_1` (`gender`,`email`)
     ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    
    // 插入5条数据
    insert into `author` (`name`, `gender`, `age`, `email`) values('xx','0','20','xx@126.com');
    insert into `author` (`name`, `gender`, `age`, `email`) values('yy','1','18','yy@126.com');
    insert into `author` (`name`, `gender`, `age`, `email`) values('zz','0','25','zz@126.com');
    insert into `author` (`name`, `gender`, `age`, `email`) values('xyz123','0','30','xyz123@126.com');
    insert into `author` (`name`, `gender`, `age`, `email`) values('xyz123','0','120','xxx@163.com');

    执行相关查询及其执行计划如下:
    这里写图片描述
    从执行计划看,Extra列都是“Using index”,由此可知这两个查询都是索引覆盖查询。另外,二级索引idx_author_union_1只有gender和email两个索引列,但查询id字段仍然是索引覆盖查询,这也印证了二级索引在叶子节点中保存了行的主键值,且可以对主键值覆盖查询。

    第一个查询的执行计划type=index,说明MySQL使用了索引扫描来做排序,下面的查询结果也印证的这一点。
    这里写图片描述

    需要注意的是,只有当索引的列顺序和ORDER BY 子句的顺序一致,并且所有列的排序方向都一样时,MySQL才能使用索引来对结果进行排序。如果查询需要关联多张表,则只有当ORDER BY 子句引用的字段全部为第一个表时,才能使用索引做排序。

    展开全文
  • 3、覆盖索引 优点 应用覆盖索引 1、聚簇索引 一篇聚簇索引数据结构的文章:聚簇索引数据结构 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB存储引擎的聚簇索引的背后数据结构就是 B-Tree...

    目录

    1、聚簇索引

    优点

    缺点

    聚簇索引在 InnoDB 和 MyISAM 中的区别

    2、非聚簇索引

    3、覆盖索引

    优点

    应用覆盖索引


    1、聚簇索引

    一篇聚簇索引数据结构的文章:聚簇索引数据结构

    聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB存储引擎的聚簇索引的背后数据结构就是 B-Tree或者B-Tree的变种B+Tree。

    当表有聚簇索引时,它的数据行实际上存放在索引的叶子页中,也就是 B+Tree的叶子节点上。因为数据行不能存在两个地方,所以一个表只能有一个聚簇索引。

    如下图所示,叶子页包含了行的全部数据,但是节点页只包含了索引列(可以理解为只是更好索引数据的指针)。

    InnoDB 通过主键聚集数据,上图被索引的列就是主键列。

    如果没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键来作为聚簇索引。

    优点

    1. 可以把相关数据保存在一起。例如查找用户名时,可以根据用户id来聚集数据,这样只需要从磁盘读取少数的数据页就能获取某个用户的名称。如果没有使用聚簇索引,则每条记录都可能导致一次磁盘 I/O。

    2. 数据访问更快。聚簇索引将索引和数据保存在同一个 B+Tree中,因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。

    3. 使用 覆盖索引扫描的查询可以直接使用叶节点中的主键值。

    缺点

    1. 聚簇数据最大限度地提高了 I/O 密集型应用的性能,但如果数据全部都放在内存中,则访问的熟悉怒就没那么重要了,聚簇索引也就没有什么优势了。

    2. 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式。

    3. 更新聚簇索引列的代价很高,因为会强制 InnoDB 将每个被更新的行移动到新的位置。

    4. 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。

    5. 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于也分裂导致数据存储不连续的时候。

    6. 二级索引(非聚簇索引)可能比想象的要更大,因为在非聚簇索引的叶子节点包含了引用行的主键列。

    7. 非聚簇索引访问需要两次索引查找,而不是一次。

    聚簇索引在 InnoDB 和 MyISAM 中的区别

    https://blog.csdn.net/ruanhao1203/article/details/97954949

    2、非聚簇索引

    非聚簇索引也是B-Tree数据结构,它的叶子节点包含了引用行的主键列。这意味着通过非聚簇索引查找行,存储引擎需要找到非聚簇索引的叶子节点获得对应的主键,然后根据这个主键值去聚簇索引中查找到对应的行。这里做了至少两次的 BTree 查找。

    为什么不止两次查找呢?

    当行更新的时候可能无法存储在原来的位置,这会导致表中出现行的碎片化或者移动行并在原位置保存“向前指针”,这两种情况都会导致查找行时需要更多的工作。

    如下图:非聚簇索引包含当前列和主键列,如果主键列比较大,会造成非聚簇索引比较大。

    3、覆盖索引

    一个索引包含了所有需要查询的字段的,就称为覆盖索引。

    覆盖索引可以直接获取列的数据,而不用在读取数据行,所以效率比较高。

    优点

    1. 索引条目通常远小于数据行大小,所以如果只需要读取索引,那MySQL 就会极大地减少数据访问量。

    2. 因为索引是按照列值顺序存储的(至少在单个页内是如此),所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。

    3. 由于 InnoDB 的聚簇索引,覆盖索引对 InnoDB 表特别有用。InnoDB 的非聚簇索引在叶子节点中保存了行的主键值,所以如果运用覆盖索引覆盖需要索引的列,则可以避免对主键索引(聚簇索引)的二次查询。

    应用覆盖索引

    例如创建表 fgsy 字段如下

    覆盖索引如下

    如下图,只有select里面选择的字段在覆盖索引内,才可以使用覆盖索引。

    下面这种方式就不能运用覆盖索引,因为无法覆盖 所有列。

    加上where条件的查询

    这三种查询的type分别是:index,all,ref,这里单开一篇讲解 执行计划type

    展开全文
  • 覆盖索引

    2017-03-16 01:23:03
    1、覆盖索引是联合索引的一个延伸与优化 2、覆盖索引的定义就是查询的列都包含在索引中。 3**优点** 3.1、索引条目通常远小于数据行大小,这样一次IO可以加载更多数据,减少IO次数 3.2、对于IO密集型的范围...

    1、覆盖索引是联合索引的一个延伸与优化
    2、覆盖索引的定义就是查询的列都包含在索引中。
    3优点
    3.1、索引条目通常远小于数据行大小,这样一次IO可以加载更多数据,减少IO次数
    3.2、对于IO密集型的范围查询,因为索引是顺序存储的,所以会比随机从磁盘读取每一行数据的IO要少的多
    3.3、对于InnoDB存储引擎,如果能从二级索引中获取要查询到的列,就可以避免对主键的二次查询
    4、索引顺序的探讨
    4.1、mysql能在索引中做最左前缀匹配的like比较,该操作可以转化为简单的比较操作,但是如果是通过通配符开头的like查询(%like%),则存储引擎无法做比较匹配。
    4.2、对于sql排序来讲,扫描索引本身是很快的,但是如果索引无法覆盖查询所需要的全部列,则就需要每扫描一条就需要回表查询一次对应的行。
    4.3、设计索引的时候应该尽量满足,索引既能排序,也能用于查找行数据。只有当索引中的列顺序与order by字句中的顺序完全一致,并且所有列的排序方向(倒序或者正序)都一样,mysql才能使用索引来对结果做排序。对于关联查询,只有当order by中引用的字段全部为第一个表,才能使用索引做排序。
    4.4、当前导列是常量的时候,order by在不满足索引最左前缀要求的情况下,也可以使用索引做排序,例如(a,b,c)三列为联合索引,……WHERE a=5 order by b,c
    4.5、 Index Key
    用于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件,被称之为Index Key。由于一个范围,至少包含一个起始与一个终止,因此Index Key也被拆分为Index First Key和Index Last Key,分别用于定位索引查找的起始,以及索引查询的终止条件。
    4.5.1、Index First Key
    用于确定索引查询的起始范围。提取规则:从索引的第一个键值开始,检查其在where条件中是否存在,若存在并且条件是=、>=,则将对应的条件加入Index First Key之中,继续读取索引的下一个键值,使用同样的提取规则;若存在并且条件是>,则将对应的条件加入Index First Key中,同时终止Index First Key的提取;若不存在,同样终止Index First Key的提取。
    select * from t1 where b >= 2 and b < 8 and c > 1 and d != 4 and e != ‘a’;
    联合索引为b、c、d
    针对上面的SQL,应用这个提取规则,提取出来的Index First Key为(b >= 2, c > 1)。由于c的条件为 >,提取结束,不包括d。
    4.5.2、Index Last Key
    Index Last Key的功能与Index First Key正好相反,用于确定索引查询的终止范围。提取规则:从索引的第一个键值开始,检查其在where条件中是否存在,若存在并且条件是=、<=,则将对应条件加入到Index Last Key中,继续提取索引的下一个键值,使用同样的提取规则;若存在并且条件是 < ,则将条件加入到Index Last Key中,同时终止提取;若不存在,同样终止Index Last Key的提取。
    针对上面的SQL,应用这个提取规则,提取出来的Index Last Key为(b < 8),由于是 < 符号,因此提取b之后结束。
    4.6、Index Filter
    Index Filter的提取规则:同样从索引列的第一列开始,检查其在where条件中是否存在:若存在并且where条件仅为 =,则跳过第一列继续检查索引下一列,下一索引列采取与索引第一列同样的提取规则;若where条件为 >=、>、<、<= 其中的几种,则跳过索引第一列,将其余where条件中索引相关列全部加入到Index Filter之中;若索引第一列的where条件包含 =、>=、>、<、<= 之外的条件,则将此条件以及其余where条件中索引相关列全部加入到Index Filter之中;若第一列不包含查询条件,则将所有索引相关条件均加入到Index Filter之中。
    针对上面的用例SQL,索引第一列只包含 >=、< 两个条件,因此第一列可跳过,将余下的c、d两列加入到Index Filter中。因此获得的Index Filter为 c > 1 and d != 4 。
    4.7、Table Filter
    Table Filter是最简单,最易懂,也是提取最为方便的。提取规则:所有不属于索引列的查询条件,均归为Table Filter之中。
    同样,针对上面的用例SQL,Table Filter就为 e != ‘a’
    mysql5.5以及之前版本,会将index Filter放在服务器层进行过滤操作,但是在mysql5.6中则采用了Index Condition Pushdown做法,将index Filter下沉到存储引擎层来做过滤,这样返回服务器层的数据减少,则减少了服务器层进行table Filter的压力。

    展开全文
  • 详解覆盖索引

    2014-09-09 16:28:35
    通常大家都会根据查询的where条件来创建合适的索引,不过这只是索引优化的一个方面。索引确实是一种查找数据的高效方式,但是mysql也可以使用索引来直接获取列的数据,这样...一、覆盖索引优点  覆盖索引是非常...
  • MySQL 覆盖索引

    千次阅读 2017-01-09 23:02:15
    本文主要概述mysql的覆盖索引,以及几种常见的优化场景 内容概要  聚集索引和辅助索引  什么是覆盖索引  几种优化场景   总体建议 聚集索引和辅助...
  • mysql覆盖索引详解

    2018-09-06 14:57:49
    如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。 只扫描索引而无需回表的优点: 1.索引条目通常远小于数据行大小,只需要读取索引,则mysql会极大地减少数据访问...
  • mysql高效索引之覆盖索引

    千次阅读 2017-01-09 22:58:38
    如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index),也就是平时所说的不需要回表操作 判断标准 使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL...
  • 因为无法同时把数据行存在两个不同的地方,所以一个表只能有一个聚簇索引(覆盖索引可以模拟多个聚簇索引的情况) 索引的建立 一个表有且只有一个聚簇索引; InnoDB一般是通过主键建立聚簇索引; 如果没有定义主键...
  • Mysql 覆盖索引及其使用注意事项

    千次阅读 2019-08-04 03:23:33
    一,什么叫覆盖索引 网上对覆盖索引的定义有如下三种: 解释一: 就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。 解释二: 索引是高效找到行的一个方法,...
  • Mysql 之覆盖索引

    2018-09-10 19:30:39
    如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。 只扫描索引而无需回表的优点:  1.索引条目通常远小于数据行大小,只需要读取索引,则mysql会极大地减少数据访问...
  • MySQL覆盖索引(Covering Index) mysql高效索引之覆盖索引 ...
  • 聚簇索引、二级索引、覆盖索引

    千次阅读 2017-11-21 22:34:18
    覆盖索引不是一种单独的索引类型(索引类型大致分为B-Tree索引、Hash索引、全文索引、空间数据索引、其他索引我们不是特别见到的这几大类),是一种数据存储方式,在索引存储结构结构上保存数据。InnoDB的聚簇索引...
  • 一、覆盖索引 1.1 概念 索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。 1.2...
  • 如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。 只扫描索引而无需回表的优点: 1.索引条目通常远小于数据行大小,只需要读取索引,则mysql会极大地减少数据访问...
  • 覆盖索引 一个索引如果包含了所有查询字段,就称为覆盖索引
  • 覆盖索引有何用?

    2016-11-29 15:04:00
    覆盖索引有何用? 通常开发人员会根据查询的where条件来创建合适的索引,但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。如果索引的叶子节点包含了要查询的数据,那么就不用回表...
  • 覆盖索引是什么?一篇文章告诉你

    千次阅读 2020-06-06 22:37:47
    覆盖索引 覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。 理解方式一:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它...
  • 聚簇索引和覆盖索引

    千次阅读 2018-08-14 16:30:10
    聚簇索引:聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。 聚簇索引是将数据行的内容放在B--tree的叶子节点中,节点列存放数据列,由于不能把数据行放在两个不同的地方,所以每个表只能有一个聚簇索引...
  • mysql主键索引、非主键索引、联合索引、覆盖索引、最左匹配、索引下推
  • 覆盖索引—MyISAM 与 InnoDB、
  • 一个包含查询所需的字段的索引称为 covering index 覆盖索引。MySQL只需要通过索引就可以返回查询所需要的数据,而不必在查到索引之后进行回表操作,减少IO,提供效率。  当你对一个sql 使用explain statement ...
  • 一、聚簇索引定义: 并不是一种单独索引类型,而是一种数据存储方式。 聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引索引顺序与数据物理排列顺序无关。举例来说,你翻到新华字典的汉字“爬”那一页就是P...
  • 索引优点中,最重要的就是加快查询速度。 索引有如下3个特点,这3点虽然看上去,句句都是废话,但仔细想想就发现,这3个特点包含的东西,远远超过了3句话所涵盖的。 (1)索引有序 创建索引后,之所以查询速度会...
  • 索引原理索引的本质就是数据结构,采用B树或其变种B+树作为索引结构,以空间换时间。 索引的原理大致概括为以空间换时间,数据库在未添加索引的时候进行查询默认的是进行全量搜索,也就是进行全局扫描,有多少条数据...
  • 索引失效like 以%开头,索引失效组合索引,不是使用第一列索引,索引失效数据类型出现隐式转化,索引失效其它情况不推荐使用索引的情况覆盖索引组合索引最左匹配原则注意组合索引数据结构索引下推谓
  • 定义 优点 无法使用覆盖索引的情况 演示 explain的几个参数的说明 搞定MySQL 生猛干货 带你搞定MySQL实战,轻松对应海量业务处理及高并发需求,从容应对大场面试 官方文档 https://dev.mysql.com/doc/ 如果英文不好...
  • 索引的一个优点是可以 降低分组、排序的时间。 先说对排序的影响。 (1)排序 说对排序的影响,就不得不说一下索引本身的特点,就是索引是对字段进行排序的,语句如下: select ... from A order by ID ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 50,433
精华内容 20,173
关键字:

覆盖索引优点