首页 > 数据库 >MySQL- 覆盖索引

MySQL- 覆盖索引

时间:2024-08-13 22:27:35浏览次数:16  
标签:salary 覆盖 查询 索引 MySQL id

覆盖索引(Covering Index)是 MySQL 中的一种优化技术,它能够显著提高查询性能。在使用覆盖索引的情况下,查询操作只需要访问索引即可获取所需的数据,而不必再访问表的实际数据行(即不需要回表)。这种优化可以减少 I/O 操作,提升查询效率。

1. 什么是覆盖索引?

覆盖索引是指一个索引包含了查询所需要的所有列的数据。换句话说,查询可以完全从索引中获取所需的数据,而不需要访问表中的实际行数据。这意味着查询只需要读取索引就可以返回结果,而不必进行额外的磁盘 I/O 来读取表数据。

覆盖索引的典型特征是:

  • 索引包含了 SELECT 子句中的所有列。
  • 索引包含了 WHERE 子句中的所有列。
  • 索引包含了 ORDER BY 子句中的所有列(如果有)。

2. 覆盖索引的工作原理

在没有覆盖索引的情况下,查询执行的过程通常如下:

  1. MySQL 使用索引查找满足查询条件的记录的主键值(或聚簇索引)。
  2. MySQL 使用主键值回表(即访问表数据)来读取查询所需的列。

在有覆盖索引的情况下,查询执行的过程可以简化为:

  1. MySQL 使用索引查找满足查询条件的记录,并直接从索引中获取所有查询所需的列。
  2. 由于索引已经覆盖了查询所需的所有数据,MySQL 不需要回表读取数据。

3. 覆盖索引的示例

假设我们有一个表 employees,结构如下:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    INDEX idx_dept_salary(department_id, salary)
);

现在我们执行以下查询:

SELECT department_id, salary FROM employees WHERE department_id = 5;

在这个查询中,SELECT 子句只涉及 department_idsalary 列,而这些列都包含在 idx_dept_salary 索引中。因此,MySQL 可以利用这个覆盖索引来优化查询。

覆盖索引的工作原理:

  • MySQL 可以直接从 idx_dept_salary 索引中获取 department_idsalary 的值,而不必再去访问 employees 表的数据行。
  • 因为查询所需的所有数据都可以从索引中获得,所以减少了不必要的磁盘 I/O 操作,显著提高了查询性能。

4. 覆盖索引的优点

  • 减少 I/O 操作:覆盖索引允许查询只读取索引,而不必回表读取实际数据行。这减少了磁盘 I/O 操作,从而提高了查询性能。
  • 提高查询速度:由于查询的数据可以直接从索引中获取,覆盖索引可以显著减少查询的响应时间,特别是在数据量较大的情况下。
  • 减少锁竞争:由于减少了回表操作,覆盖索引也可以减少表上的行级锁定,降低锁竞争的概率。

5. 覆盖索引的局限性

  • 索引大小的限制:为了让索引覆盖查询,索引必须包含查询所需的所有列。这可能导致索引变得非常大,从而增加了维护索引的开销(如插入、更新、删除操作的成本)。
  • 冗余数据:在索引中包含所有查询列可能会导致数据冗余,特别是当表中有许多列且查询涉及的列较多时,创建覆盖索引可能会导致索引的存储空间显著增加。
  • 适用场景有限:覆盖索引对那些查询列较少且频繁执行的查询最有效。如果查询涉及的列较多,或者查询模式变化频繁,覆盖索引的作用可能会减弱。

6. 何时使用覆盖索引?

覆盖索引特别适用于以下场景:

  • 频繁查询特定列:如果应用程序经常查询某些列,而这些列可以通过索引覆盖,可以考虑创建覆盖索引。
  • 优化读性能:在只读或读操作远多于写操作的场景中,覆盖索引可以显著提高查询性能。
  • 减少回表操作:对于那些数据量大、需要频繁读取的表,覆盖索引可以减少回表操作,降低 I/O 开销。

7. 查看是否使用了覆盖索引

我们可以通过 EXPLAIN 关键字来查看 MySQL 是否使用了覆盖索引来执行查询。在 EXPLAIN 输出中,如果 Extra 列包含 Using index,则表示查询使用了覆盖索引。

EXPLAIN SELECT department_id, salary FROM employees WHERE department_id = 5;

如果 Extra 列中显示 Using index,说明 MySQL 只使用索引就完成了查询,无需回表操作,这就是覆盖索引在发挥作用。

8. 结合 InnoDB 的覆盖索引

在 InnoDB 存储引擎中,聚簇索引(主键索引)会包含表的所有列。因此,InnoDB 的二级索引自动包含主键列,这在某些情况下会对覆盖索引的设计产生影响。

假设有如下表结构:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    INDEX idx_lastname_salary(last_name, salary)
);

查询:

SELECT last_name, salary FROM employees WHERE last_name = 'Smith';

idx_lastname_salary 索引已经覆盖了 last_namesalary 列,因此这个查询可以完全由索引覆盖。

总结

覆盖索引是一种强大的 MySQL 查询优化技术,可以显著减少查询的 I/O 操作并提高性能。通过将查询所需的列全部包含在索引中,MySQL 可以避免回表操作,直接从索引中获取数据。然而,在使用覆盖索引时需要平衡索引的大小和性能收益,以确保索引能够有效地服务于实际的查询需求。

标签:salary,覆盖,查询,索引,MySQL,id
From: https://blog.csdn.net/weixin_43844521/article/details/141175454

相关文章

  • MySQL- 索引下推
    索引下推(IndexConditionPushdown,简称ICP)是MySQL5.6引入的一项优化技术,它通过将部分查询条件“下推”到索引扫描阶段,从而减少不必要的行访问和回表操作,提高查询性能。1.索引下推的概念在传统的索引扫描过程中,MySQL会首先通过索引找到符合索引条件的记录,然后回表(即......
  • MySQL使用教程笔记(远程访问)
    远程连接MySQL数据库_mysql远程访问数据库-CSDN博客如何远程连接mysql数据库服务器_mysql如何连接远程数据库服务器_如何连接远程数据库mysql-腾讯云开发者社区-腾讯云(tencent.com)如何使用C#连接远程MySQL服务器?_webrtc_weixin_0010034-MySQL数据库(csdn.net)c#中mysq......
  • 最全MySQL面试题和答案(四)
    1.数据库结构优化一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。优化设计需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的因素。将字段很多的表分解成多个表概述:对于字段较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来......
  • MySQL学习[4] ——MySQL锁
    四、MySQL锁4.1MySQL有哪些锁?4.1.1全局锁全局锁就是**对整个数据库实例加锁,主要用于全库逻辑备份**等场景。flushtableswithreadlock#加全局锁unlocktables#解锁加上全局(读)锁后,整个数据库都是只读状态。若数据库的数据较多,导致整个处理流程较慢,数据库......
  • MySQL8.0 Clone Plugin 实现解析浅析
    MySQL8.0ClonePlugin实现解析浅析从8.0.17版本开始官方实现了clone的功能,允许用户通过简单的SQL命令把远端/本地的数据库实例拷贝到其他实例后快速拉起一个新的实例。该功能由一些列的WL组成:Clonelocalreplica(WL#9209):实现了数据本地Clone。Cloneremotereplica......
  • MySQL数据库——数据库的数据类型(一)
    四、数据类型1.数据类型分类分类数据类型说明数值类型BIT(M)位类型。指定位数,默认值1,范围1-64TINYINT[UNSIGNED]带符号的范围-128127,无符号范围0255.默认有符号BOOL使用0和1表示真和假SMALLINT[UNSIGNED]带符号是-2^15次方到2^15-1,无符号是2^16-1IN......
  • 【JAVA_Mysql】JAVA连接Mysql基本步骤
    ......
  • MySQL:复杂查询(二)——联合查询02
    本篇博客接上篇,上篇已讲联合查询部分知识:MySQL:复杂查询(一)——聚合函数&分组查询&联合查询01-CSDN博客目录1、联合查询1.1外连接1.1.1右外连接RIGHTJOIN1.1.2左外连接LEFTJOIN1.2自连接1.3子查询1.3.1单行子查询1.3.2多行子查询[NOT]IN1.3.3 多列......
  • 尚硅谷MYSQL(第七章)
    从函数定义的角度出发,我们可以将函数分成内置函数和自定义函数。在SQL语言中,同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数,而自定义函数是我们根据自己的需要编写的里面有因子的话因子相同 随机数相同四舍五入中......
  • 数据库系统 第13节 索引
    索引(Indexing)是一种数据结构技术,广泛应用于数据库和文件系统中,用于提高数据检索的速度。在没有索引的情况下,数据库必须从头到尾扫描整个表来找到所需的数据行,这被称为全表扫描(FullTableScan),对于大型数据库来说效率极低。索引的基本原理索引类似于书籍的目录。当你需要查......