MySQL 的覆盖索引与回表的使用方法-6ren

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

转载作者：qq735679552 更新时间：2022-09-28 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章MySQL 的覆盖索引与回表的使用方法由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

两大类索引。

使用的存储引擎：MySQL5.7 InnoDB 。

聚簇索引。

* 如果表设置了主键，则主键就是聚簇索引
* 如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
* 以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引

InnoDB的聚簇索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据），InnoDB必须要有至少一个聚簇索引.

由此可见，使用聚簇索引查询会很快，因为可以直接定位到行记录.

普通索引。

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引.

InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针.

示例。

建表。

 
    ? 
   
         mysql>  
         create 
         table 
         user 
         ( 
        
         -> id  
         int 
         (10) auto_increment, 
        
         ->  
         name 
         varchar 
         (30), 
        
         -> age tinyint(4), 
        
         ->  
         primary 
         key 
         (id), 
        
         ->  
         index 
         idx_age (age) 
        
         -> )engine=innodb charset=utf8mb4;

id 字段是聚簇索引，age 字段是普通索引（二级索引）。

填充数据。

 
    ? 
   
 
     
       
       
         insert 
         into 
         user 
         ( 
         name 
         ,age)  
         values 
         ( 
         '张三' 
         ,30); 
        
 
         insert 
         into 
         user 
         ( 
         name 
         ,age)  
         values 
         ( 
         '李四' 
         ,20); 
        
 
         insert 
         into 
         user 
         ( 
         name 
         ,age)  
         values 
         ( 
         '王五' 
         ,40); 
        
 
         insert 
         into 
         user 
         ( 
         name 
         ,age)  
         values 
         ( 
         '刘八' 
         ,10); 
        

            
        
 
         mysql>  
         select 
         *  
         from 
         user 
         ; 
        
 
         + 
         ----+--------+------+ 
        
 
         | id |  
         name 
         | age | 
        
 
         + 
         ----+--------+------+ 
        
 
         | 1 | 张三 | 30 | 
        
 
         | 2 | 李四 | 20 | 
        
 
         | 3 | 王五 | 40 | 
        
 
         | 4 | 刘八 | 10 | 
        
 
         + 
         ----+--------+------+ 
        
 
     
 
   

索引存储结构。

id 是主键，所以是聚簇索引，其叶子节点存储的是对应行记录的数据。

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

聚簇索引（ClusteredIndex）。

age 是普通索引（二级索引），非聚簇索引，其叶子节点存储的是聚簇索引的的值。

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

普通索引（secondaryIndex）。

如果查询条件为主键（聚簇索引），则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据.

如：select * from user where id = 1,

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

聚簇索引查找过程。

如果查询条件为普通索引（非聚簇索引），需要扫描两次B+树，第一次扫描通过普通索引定位到聚簇索引的值，然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据。如：select * from user where age = 30,

1. 先通过普通索引 age=30 定位到主键值 id=1 。

2. 再通过聚集索引 id=1 定位到行记录数据。

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

普通索引查找过程第一步。

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

普通索引查找过程第二步。

回表查询。

先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低.

索引覆盖。

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快.

例如：select id,age from user where age = 10,

如何实现覆盖索引。

常见的方法是：将被查询的字段，建立到联合索引里去.

1、如实现：select id,age from user where age = 10,

explain分析：因为age是普通索引，使用到了age索引，通过一次扫描B+树即可查询到相应的结果，这样就实现了覆盖索引。

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

2、实现：select id,age,name from user where age = 10,

explain分析：age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询。

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

为了实现索引覆盖，需要建组合索引idx_age_name(age,name) 。

 
    ? 
   
         drop 
         index 
         idx_age  
         on 
         user 
         ; 
        
         create 
         index 
         idx_age_name  
         on 
         user 
         (`age`,` 
         name 
         `);

explain分析：此时字段age和name是组合索引idx_age_name，查询的字段id、age、name的值刚刚都在索引树上，只需扫描一次组合索引B+树即可，这就是实现了索引覆盖，此时的Extra字段为Using index表示使用了索引覆盖.

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

哪些场景适合使用索引覆盖来优化SQL 。

全表count查询优化。

 
    ? 
   
         mysql>  
         create 
         table 
         user 
         ( 
        
         -> id  
         int 
         (10) auto_increment, 
        
         ->  
         name 
         varchar 
         (30), 
        
         -> age tinyint(4), 
        
         ->  
         primary 
         key 
         (id), 
        
         -> )engine=innodb charset=utf8mb4;

例如：select count(age) from user,

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

使用索引覆盖优化：创建age字段索引。

 
    ? 
   
         create 
         index 
         idx_age  
         on 
         user 
         (age);

列查询回表优化。

前文在描述索引覆盖使用的例子就是。

例如：select id,age,name from user where age = 10,

使用索引覆盖：建组合索引idx_age_name(age,name)即可。

分页查询。

例如：select id,age,name from user order by age limit 100,2,

因为name字段不是索引，所以在分页查询需要进行回表查询，此时Extra为Using filesort文件排序，查询性能低下.

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

使用索引覆盖：建组合索引idx_age_name(age,name) 。

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

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我.

原文链接：https://segmentfault.com/a/1190000021718016 。

最后此篇关于MySQL 的覆盖索引与回表的使用方法的文章就讲到这里了,如果你想了解更多关于MySQL 的覆盖索引与回表的使用方法的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐： Workbench连接不上阿里云服务器Ubuntu的Mysql解决方法(已测)

文章推荐： PHP生成自适应大小的缩略图类及使用方法分享

文章推荐：详解Mysql查询条件中字符串尾部有空格也能匹配上的问题

Ruby 方法() 方法
我想了解 Ruby 方法 methods() 是如何工作的。我尝试使用“ruby 方法”在 Google 上搜索，但这不是我需要的。我也看过 ruby-doc.org，但我没有找到这种方法。
VBS教程：方法-Test 方法
Test 方法对指定的字符串执行一个正则表达式搜索，并返回一个 Boolean 值指示是否找到匹配的模式。 object.Test(string) 参数 object 必选项。总是一个
VBS教程：方法-Replace 方法
Replace 方法替换在正则表达式查找中找到的文本。 object.Replace(string1, string2) 参数 object 必选项。总是一个 RegExp 对象的名称。
VBS教程：方法-Raise 方法
Raise 方法生成运行时错误 object.Raise(number, source, description, helpfile, helpcontext) 参数 object 应为
VBS教程：方法-Execute 方法
Execute 方法对指定的字符串执行正则表达式搜索。 object.Execute(string) 参数 object 必选项。总是一个 RegExp 对象的名称。 string
VBS教程：方法-Clear 方法
Clear 方法清除 Err 对象的所有属性设置。 object.Clear object 应为 Err 对象的名称。说明在错误处理后，使用 Clear 显式地清除 Err 对象。此
VBS教程：方法-CopyFile 方法
CopyFile 方法将一个或多个文件从某位置复制到另一位置。 object.CopyFile source, destination[, overwrite] 参数 object 必选
VBS教程：方法-Copy 方法
Copy 方法将指定的文件或文件夹从某位置复制到另一位置。 object.Copy destination[, overwrite] 参数 object 必选项。应为 File 或 F
VBS教程：方法-Close 方法
Close 方法关闭打开的 TextStream 文件。 object.Close object 应为 TextStream 对象的名称。说明下面例子举例说明如何使用 Close 方
VBS教程：方法-BuildPath 方法
BuildPath 方法向现有路径后添加名称。 object.BuildPath(path, name) 参数 object 必选项。应为 FileSystemObject 对象的名称
VBS教程：方法-GetFolder 方法
GetFolder 方法返回与指定的路径中某文件夹相应的 Folder 对象。 object.GetFolder(folderspec) 参数 object 必选项。应为 FileSy
VBS教程：方法-GetFileName 方法
GetFileName 方法返回指定路径（不是指定驱动器路径部分）的最后一个文件或文件夹。 object.GetFileName(pathspec) 参数 object 必选项。应为
VBS教程：方法-GetFile 方法
GetFile 方法返回与指定路径中某文件相应的 File 对象。 object.GetFile(filespec) 参数 object 必选项。应为 FileSystemObject
VBS教程：方法-GetExtensionName 方法
GetExtensionName 方法返回字符串，该字符串包含路径最后一个组成部分的扩展名。 object.GetExtensionName(path) 参数 object 必选项。应
VBS教程：方法-GetDriveName 方法
GetDriveName 方法返回包含指定路径中驱动器名的字符串。 object.GetDriveName(path) 参数 object 必选项。应为 FileSystemObjec
VBS教程：方法-GetDrive 方法
GetDrive 方法返回与指定的路径中驱动器相对应的 Drive 对象。 object.GetDrive drivespec 参数 object 必选项。应为 FileSystemO
VBS教程：方法-GetBaseName 方法
GetBaseName 方法返回字符串，其中包含文件的基本名 (不带扩展名), 或者提供的路径说明中的文件夹。 object.GetBaseName(path) 参数 object 必
VBS教程：方法-GetAbsolutePathName 方法
GetAbsolutePathName 方法从提供的指定路径中返回完整且含义明确的路径。 object.GetAbsolutePathName(pathspec) 参数 object
VBS教程：方法-FolderExists 方法
FolderExists 方法如果指定的文件夹存在，则返回 True；否则返回 False。 object.FolderExists(folderspec) 参数 object 必选项
VBS教程：方法-FileExists 方法
FileExists 方法如果指定的文件存在返回 True；否则返回 False。 object.FileExists(filespec) 参数 object 必选项。应为 FileS

qq735679552

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

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