首页
学习
活动
专区
圈层
工具
发布

MySQL 覆盖索引与延迟关联

如果一个索引包含(覆盖)我们所需要查询的所有字段值,我们就称之为「覆盖索引」。...MyISAM 当使用 MyISAM 存储引擎时,由于我们在 product_id 建立了索引,所以 SELECT product_id FROM orders 将使用覆盖索引: mysql> EXPLAIN...也就是说,二级索引不仅能覆盖其本身,也能覆盖到该行的主键值。 ?...在查询的第一阶段 MySQL 使用覆盖索引,再通过该覆盖索引查询到的结果到外层查询匹配需要的所有列值。 这样说有些抽象,我们来看看下面的例子。...总结 如果使用覆盖索引,MySQL 只需扫描索引,无须回表,这极大地减少了数据访问量,能让查询更快、更高效。 延迟关联(deferred join)是覆盖索引的实际应用,可用于优化分页或其他场景。

2.4K10

MySQL覆盖索引的使用案例

在使用覆盖索引时,执行计划的Extra字段中有Using index的信息,下面是一个SQL语句的执行计划: mysql>explain select customer_id,inventory_id,...1row in set, 1 warning (0.01 sec) 这个SQL语句没有where条件,但仍然访问了rental_date索引,而且没有访问表,查询rental_date索引的构成: mysql...由于二级索引实质上都包含主键,因此如果再加上主键,一样可以使用覆盖索引,下面是在输出字段中加上主键字段的SQL语句的执行计划: mysql>explain select rental_id,customer_id...但如果要访问的字段不在这个索引中,则还需要访问表,如果在上面的查询字段中再增加任意一个其他字段就不能使用覆盖索引了,例如下面的SQL语句将无法使用覆盖索引: mysql>explain select *...但可以对这个SQL语句进行改写,先用一个可以使用覆盖索引的子查询查询出主键,再通过主键查找相应的记录,这种方法称之为延迟关联(deferred join),改写后的SQL语句执行计划如下: mysql>

38410
  • 您找到你想要的搜索结果了吗?
    是的
    没有找到

    mysql优化:覆盖索引(延迟关联)

    覆盖索引必须要存储索引的列,而哈希索引、空间索引和全文索引等都不存储索引列的值,所以MySQL只能使用B-Tree索引做覆盖索引 当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时,在EXPLAIN的Extra...也就是说,在这个查询里面,索引k已经“覆盖了”我们的查询需求,我们称为覆盖索引。 由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。...需要注意的是,在引擎内部使用覆盖索引在索引k上其实读了三个记录,R3~R5(对应的索引k上的记录项),但是对于MySQL的Server层来说,它就是找引擎拿到了两条记录,因此MySQL认为扫描行数是2。...当偏移量很大时,如limit 100000,10 取第100001-100010条记录,mysql会取出100010条记录然后将前100000条记录丢弃,这无疑是一种巨大的性能浪费。...最后以《高性能Mysql》中的一段话结束: ?

    2.6K20

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

    场景 产品中有一张图片表,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化 表结构很简单,主要字段: user_id 用户ID picname 图片名称 smallimg...看到使用的索引变成了刚刚建立的联合索引,并且Extra部分显示使用了'Using Index' 总结 'Using Index'的意思是“覆盖索引”,它是使上面sql性能提升的关键 一个包含查询所需字段的索引称为...“覆盖索引” MySQL只需要通过索引就可以返回查询所需要的数据,而不必在查到索引之后进行回表操作,减少IO,提高了效率 例如上面的sql,查询条件是user_id,可以使用联合索引,要查询的字段是...picname smallimg,这两个字段也在联合索引中,这就实现了“覆盖索引”,可以根据这个联合索引一次性完成查询工作,所以提升了性能

    1.6K50

    MySQL高效索引之覆盖索引

    概念 如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index),也就是平时所说的不需要回表操作 判断标准 使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询...,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询 注意 1、覆盖索引也并不适用于任意的索引类型,索引必须存储列的值 2、Hash 和full-text索引不存储值...,因此MySQL只能使用B-TREE 3、并且不同的存储引擎实现覆盖索引都是不同的 4、并不是所有的存储引擎都支持它们 5、如果要使用覆盖索引,一定要注意SELECT 列表值取出需要的列,不可以是SELECT...*,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降,不能为了利用覆盖索引而这么做 InnoDB 1、覆盖索引查询时除了除了索引本身的包含的列,还可以使用其默认的聚集索引列 2、这跟...因此,sakila.actor表使用InnoDB,而且对于是last_name上有索引,所以,索引能覆盖那些访问actor_id的查 询,如下 mysql> EXPLAIN SELECT actor_id

    1.6K10

    「Mysql索引原理(七)」覆盖索引

    覆盖索引是非常有用的工具,能够极大地提高性能: 索引条目通常远小于数据行大小,所以如果只需要读取索引,那MySQL就会极大地减少数据访问量。...不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引都不存储索引列的值,所以MySQL只能使用B+Tree索引所覆盖索引。...索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了wehre条件中的字段,但不是整个查询涉及的字段。...不过理论上mysql有一个捷径可以利用:where条件中的列是由索引可以覆盖的,因此Mysql可以使用该索引找到对应的last_name并检查是否first_name是否匹配,过滤之后再读取所需要的数据行...在查询第一个阶段MySQL可以使用覆盖索引,因为索引包含了主键id的值,不需要做二次查找。

    2.7K12

    一条更新SQL在MySQL数据库中是如何执行的

    点击关注"故里学Java" 右上角"设为星标"好文章不错过 前边的在《一条SQL查询在MySQL中是怎么执行的》中我们已经介绍了执行过程中涉及的处理模块,包括连接器、分析器、优化器、执行器、存储引擎等。...接下来,分析器会经过语法分析和词法分析,知道了这是一条更新语句后,优化器决定要使用哪一个索引,然后执行器负责具体的执行,先找到这一行,然后做更新。...我们先创建一张表,这个表有主键ID和一个整型字段c: mysql> create table demo T (ID int primarty ,c int); 然后将ID=2的这一行的值加1 mysql...假设redolog写完,binlog还没写完,MySQL进程异常重启了。我们知道,redolog写完以后,系统即使崩溃了,也可以将数据恢复,所以在MySQL重启后,这一行会被恢复成1。...我们可以看到如果不使用“两阶段提交",那么数据库的状态就会和用日志恢复出来的库不一致。

    6.3K30

    MySQL插入重复后进行覆盖更新

    MySQL插入重复后进行覆盖更新 一、介绍 在MySQL中,常常会有唯一键的约束,当使用Java插入重复的值后,会报异常我们需要进行捕获处理。...上面的解决思路,确实是一种办法,但我将介绍一下MySQL的一种插入写法,可以解决这种插入重复数据的问题。...二、使用 首先,我们先建立一张有唯一键的表,并初始化插入一条数据 CREATE TABLE `tb_user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT...update_user_id`) VALUES (1, '半月无霜', NULL, '50001', '2023-10-25 15:47:31', 1, '2023-10-25 15:54:17', 1); 接下来就将插入一条重复的数据...三、最后 以上,就是在MySQL中,插入时发生唯一键约束后的简单处理。 当然了,这种处理比较简单,只适合单表。 如果异常有涉及多表的处理的话,那还是老老实实捕获异常吧。 我是半月,你我一同共勉!!!

    1.9K30

    详解MySQL覆盖索引、索引下推

    1.覆盖索引 1.1.概述 覆盖索引,是为了避免“回表查询”,从而降低查询耗时的一种使用索引的方法,所以要聊覆盖索引首先我们要知道什么是"回表查询,“回表查询”是因为MySQL的索引结构决定的,是因为非聚集索引要找聚集索引拿数据而出现的现象...1.2.聚集索引、非聚集索引 众所周知mysql的索引有两种: 聚集索引 非聚集索引 聚集索引: 聚集索引的叶子节点上挂着的是一条分支上的所有索引对应的数据。...MySQL会默认为每张表维护一个聚集索引,如果表有主键,那么这个聚集索引就是主键索引,如果没有主键,MySQL仍然会为每个表维护一个隐藏的聚集索引。...sys_user表,里面随即插入了100万条数据: 然后在username上建立一个索引: create index index_username on sys_user(username); 未使用覆盖索引查询其中一条数据...: explain select * from sys_user where username='test_852107'; 使用覆盖索引查询其中一条数据: explain select username

    68510

    【MySQL数据库基础】(一)保姆级 MySQL 环境配置教程!CentOS 7+Ubuntu 双系统全覆盖

    前言 作为后端开发、数据库学习的入门必备,MySQL 的环境配置是很多小伙伴的第一道 “小关卡”。...MySQL 被 Oracle 收购后,很多 Linux 发行版(比如 CentOS 7、Ubuntu)会默认预装MariaDB—— 它是 MySQL 的分支,语法兼容但底层有差异,会和官方 MySQL...3.3 验证 MySQL 服务状态 # 查看MySQL服务状态 sudo systemctl status mysql # 设置开机自启 sudo systemctl enable mysql...3.5 Ubuntu MySQL 编码与核心配置优化 Ubuntu 的 MySQL 主配置文件为/etc/mysql/mysql.conf.d/mysqld.cnf,编辑该文件优化编码和核心参数...后续可以继续学习 MySQL 的用户管理、数据库设计、SQL 语句优化等内容,打好后端开发的数据库基础。 如果配置过程中遇到其他报错,欢迎在评论区留言,我会第一时间解答!

    1.1K30

    MySQL面试常问:一条语句提交后,数据库都做了什么?

    今天大部分程序需要处理的数据,都来自数据库,尤其是关系型数据库,那么一条 SQL 提交到数据库之后,数据库都做了些什么?如果不懂这些问题,就无法更好的使用数据库,更无法回答好面试官的问题。...现在流行的开源数据库,非 MySQL 莫属,面试中 MySQL 也是必问,于是我就学习了专栏《MySQL实战45讲》,今天的文章试着回答以下两个问题: 1、一条 SQL 语句提交到数据库之后,数据库都会执行哪些动作...先来看看一条读操作 SQL 的查询过程: 连接器 客户端在提交 SQL 语句之前,你需要先连接上数据库,也就是说要提供用户名密码登陆,这便是连接器发挥作用的时候。...“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。 redo log 用于回滚,binlog 用于恢复。...如果将 MySQL 恢复到某一天的某一秒 要做到这一点有个前提,就是要对 MySQL 数据库定期做整库备份。这里的定期取决于系统的重要性,可以是一天一备,也可以是一周一备。

    1.4K20

    云开发数据库里规避写覆盖

    一个业务偶然遇到了并发写操作相互覆盖的问题。直觉的想法是能不能加锁?看了一下云开发数据库没有提供锁的接口(当然数据库自己写操作的时候肯定实现了自己的锁)。...还是得考虑下怎么利用数据库自己的锁机制来避免覆盖。...一个解决办法是把数据放到数组里面,更新数据的时候用push或者unshift来插入数据,这样即使并发写也不会相互写覆盖,但是如果是相同的数据重复写入的话可能数组里面出现重复元素的问题,可能需要处理额外的去重逻辑...,然后在用update语句的字段set能力来实现并发更新记录的时候相互不覆盖: function test(i){ var answer={}; answer["answer"+i] = _

    1K40

    一条MySQL报警的分析思路

    这是学习笔记的第 1903 篇文章 今天带着同事一起分析了一个常见的MySQL慢日志报警,从分析的过程希望带给大家一些启示和反思。...报警信息类似: PROBLEM P5 Endpoint:xxxx Metric:mysql.slow_queries Tags:idc=IDC1,port=4306,service=test diff(...看到这条报警信息,可以明确一个任务,此时数据库中存在大量的慢日志,条数为335,超出了阈值设置的300 当然打开日志来分析的时候,会发现比想象的要复杂一些,因为慢日志文件可能有几百兆甚至更大,要分析整个文件显然是不可行的...# ll *cccd* -rw-r----- 1 mysql mysql 70494 Nov 20 09:37 dic_fsm_cccd_info.frm -rw-r----- 1 mysql mysql...# ll *cccd* -rw-r----- 1 mysql mysql 70494 Feb 27 23:47 dic_fsm_cccd_info.frm -rw-r----- 1 mysql mysql

    1.4K20

    MySQL 覆盖索引成立的条件说明

    在 MySQL 中,覆盖索引(Covering Index)指的是: 查询所需的数据可以完全从索引中获取,而无需回表读取数据行。覆盖索引是否成立,取决于查询字段、索引结构以及执行计划。...下面从原理和条件角度对覆盖索引的成立规则进行说明。一、什么是覆盖索引当一个查询满足以下条件时,即可称为覆盖索引: 查询所需要的所有列,都包含在同一个索引中。...不需要额外回表 五、覆盖索引与 WHERE 条件的关系覆盖索引不仅与 SELECT 列有关,也与 WHERE 条件有关。...七、覆盖索引的本质原因覆盖索引之所以能够避免回表,原因在于: InnoDB 的索引本身是有序结构(B+Tree) 索引叶子节点已包含查询所需数据 无需通过主键再次定位数据行 这是一种结构层面的优化结果...八、小结覆盖索引是否成立,可以归纳为以下几点: 查询字段必须全部包含在索引中 SELECT * 会直接破坏覆盖索引 InnoDB 二级索引天然包含主键 EXPLAIN 中的 Using index

    42410
    领券