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

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引'。

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不需要读取数据，有以下优点：

1、索引项通常比记录要小，所以MySQL访问更少的数据。

2、索引都按值得大小存储，相对于随机访问记录，需要更少的I/O。

3、数据引擎能更好的缓存索引，比如MyISAM只缓存索引。

4、覆盖索引对InnoDB尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引包含查询所需的数据，就不再需要在聚集索引中查找了。

限制：

1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。

2、Hash和full-text索引不存储值，因此MySQL只能使用BTree。

3、不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引。

4、如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

知识点扩充：

1、两遍扫描算法(Two passes)

实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns

注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小

2、 一次扫描算法(single pass)

该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出
注： 从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要 的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出 “Using temporary;Using filesort”

以上就是mysql中关于覆盖索引的知识点总结的详细内容，更多关于mysql中什么是覆盖索引的资料请关注我们其它相关文章！

赞 (0)

相关推荐

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

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

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

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

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

  • 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 回表,覆盖索引,索引下推

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

  • MySQL中的唯一索引的简单学习教程

    mysql 唯一索引UNIQUE一般用于不重复数据字段了我们经常会在数据表中的id设置为唯一索引UNIQUE,下面我来介绍如何在mysql中使用唯一索引UNIQUE吧. 创建唯一索引的目的不是为了提高访问速度,而只是为了避免数据出现重复.唯一索引可以有多个但索引列的值必须唯一,索引列的值允许有空值.如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该使用关键字UNIQUE. 把它定义为一个唯一索引. 创建表时直接设置: DROP TABLE IF EXISTS `st

  • MySQL 中这么多索引该怎么选择

    目录 前言 MySQL 单字段索引问题 组合索引 唯一索引和普通索引 总结 前言 索引的本质是存储引擎用于快速查询记录的一种数据结构.特别是数据表中数据特别多的时候,索引对于数据库的性能就愈发重要. 在数据量比较大的时候,不恰当的索引对于数据库的性能的影响是非常大的.在实际的应用中常常会遇见使用错误的索引而导致一系列问题,所以,选择正确的索引对于 MySQL 数据库来说相当重要. 下面我们就来一起聊聊在 MySQL 数据库中该怎么选择正确的索引. 在了解怎么选择索引之前,我先给你举一个例子.如果

  • Python如何识别 MySQL 中的冗余索引

    目录 前言 脚本介绍 表结构 MySQL 元数据 DEMO 演示 SQL 查询冗余索引 后记 前言 最近在搞标准化巡检平台,通过 MySQL 的元数据分析一些潜在的问题.冗余索引也是一个非常重要的巡检目,表中索引过多,会导致表空间占用较大,索引的数量与表的写入速度与索引数成线性关系(微秒级),如果发现有冗余索引,建议立即审核删除. PS:之前见过一个客户的数据库上面竟然创建 300 多个索引!?当时的想法是 “他们在玩排列组合呢” 表写入非常慢,严重影响性能和表维护的复杂度. 脚本介绍 表结构

  • MySQL中主键索引与聚焦索引之概念的学习教程

    MySQL中主键索引与聚焦索引之概念的学习教程

    主键索引 主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录.一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL. 在MySQL中,InnoDB数据表的主键设计我们通常遵循几个原则: 采用一个没有业务用途的自增属性列作为主键: 主键字段值总是不更新,只有新增或者删除两种操作: 不选择会动态更新的类型,比如当前时间戳等. 这么做的好处有几点: 新增数据时,由于主键值是顺序增长的,innodb page发生分裂的概率降低了:可以

  • MySQL中的联合索引学习教程

    联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 两个或更多个列上的索引被称作复合索引. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用两个单独的索引.复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏

  • MySQL中NULL对索引的影响深入讲解

    MySQL中NULL对索引的影响深入讲解

    前言 看了很多博客,也听过很多人说,包括我们公司的DBA,说MySql中如果某一列中含有null,那么包含该列的索引就无效了. 翻了下<高性能MySQL第二版>和<MySQL技术内幕--InnoDB存储引擎第2版>,并没有看到关于这个的介绍.但在本地试了下,null列是可以用到索引的,不管是单列索引还是联合索引,但仅限于is null,is not null是不走索引的. 后来在官方文档中找到了说明,如果某列字段中包含null,确实是可以使用索引的,地址:https://dev.m

  • mysql中数据库覆盖导入的几种方式总结

    mysql中数据库覆盖导入的几种方式总结

    目录 部分覆盖 业务场景 应用方案 完全覆盖 业务场景 应用方案 总结 众所周知,数据库中INSERT INTO语法是append方式的插入,而最近在处理一些客户数据导入场景时,经常遇到需要覆盖式导入的情况 常见的覆盖式导入主要有下面两种: 1.部分覆盖:新老数据根据关键列值匹配,能匹配上则使用新数据覆盖,匹配不上则直接插入. 2.完全覆盖:直接删除所有老数据,插入新数据. 本文主要介绍如何在数据库中完成覆盖式数据导入的方法. 部分覆盖 业务场景 某业务每天给业务表中导入大数据进行分析,业务表中

  • 正确理解Mysql中的列索引和多列索引

    Mysql数据库提供两种类型的索引,如果没正确设置,索引的利用效率会大打折扣却完全不知问题出在这. 复制代码 代码如下: CREATE TABLE test (    id         INT NOT NULL,    last_name  CHAR(30) NOT NULL,    first_name CHAR(30) NOT NULL,    PRIMARY KEY (id),    INDEX name (last_name,first_name)); 以上创建的其实是一个多列索引,

随机推荐