迅闻网
让更多人看到你

被查询的列,为啥要放到索引里?

多查询了一个特点,为何检索过程完全不同?
什么是回表查询?
什么是索引掩盖?
怎么实现索引掩盖?
哪些场景,能够使用索引掩盖来优化SQL?
这些,这是今日要分享的内容。
画外音:本文实验基于MySQL5.6-InnoDB。
一、什么是回表查询?
这先要从InnoDB的索引实现说起,InnoDB有两大类索引:
(1)集合索引(clusteredindex);
(2)一般索引(secondaryindex);
InnoDB集合索引和一般索引有什么差异?
InnoDB集合索引的叶子节点存储行记载,因而,InnoDB有必要要有,且只有一个集合索引:
(1)假如表界说了PK,则PK便是集合索引;
(2)假如表没有界说PK,则第一个notNULLunique列是集合索引;
(3)不然,InnoDB会创建一个躲藏的row-id作为集合索引;
画外音:所以PK查询非常快,直接定位行记载。
InnoDB一般索引的叶子节点存储主键值。
画外音:注意,不是存储行记载头指针,MyISAM的索引叶子节点存储记载指针。
举个栗子,无妨设有表:
t(idPK,nameKEY,sex,flag);
画外音:id是集合索引,name是一般索引。
表中有四条记载:
1,shenjian,m,A
3,zhangsan,m,A
5,lisi,m,A
9,wangwu,f,B
两个B+树索引分别如上图:
(1)id为PK,集合索引,叶子节点存储行记载;
(2)name为KEY,一般索引,叶子节点存储PK值,即id;
既然从一般索引无法直接定位行记载,那一般索引的查询过程是怎么样的呢?
通常情况下,需求扫码两遍索引树。MySQL
例如:
select*fromtwherename=’lisi’;
是怎么履行的呢?
如粉红色途径,需求扫码两遍索引树:
(1)先通过一般索引定位到主键值id=5;
(2)在通过集合索引定位到行记载;
这便是所谓的回表查询,先定位主键值,再定位行记载,它的功能较扫一遍索引树更低。
二、什么是索引掩盖(Coveringindex)?
额,楼主并没有在MySQL的官网找到这个概念。
借用一下SQL-Server官网的说法。
MySQL官网,相似的说法出现在explain查询计划优化章节,即explain的输出结果Extra字段为Usingindex时,能够触发索引掩盖。
不管是SQL-Server官网,还是MySQL官网,都表达了:只需求在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。
三、怎么实现索引掩盖?
常见的办法是:将被查询的字段,树立到联合索引里去。
仍是《MySQL功能调优,这个工具最有用》中的比如:
createtableuser(
idintprimarykey,
namevarchar(20),
sexvarchar(5),
index(name)
)engine=innodb;
第一个SQL语句:
selectid,namefromuserwherename=’shenjian’;
能够射中name索引,索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,契合索引掩盖,功率较高。
画外音,Extra:Usingindex。
第二个SQL语句:
selectid,name,sexfromuserwherename=’shenjian’;
能够射中name索引,索引叶子节点存储了主键id,但sex字段有必要回表查询才能获取到,不契合索引掩盖,需求再次通过id值扫码集合索引获取sex字段,功率会降低。
画外音,Extra:Usingindexcondition。
假如把(name)单列索引升级为联合索引(name,sex)就不同了。
createtableuser(
idintprimarykey,
namevarchar(20),
sexvarchar(5),
index(name,sex)
)engine=innodb;
能够看到:
selectid,name…wherename=’shenjian’;
selectid,name,sex…wherename=’shenjian’;
都能够射中索引掩盖,无需回表。
画外音,Extra:Usingindex。
四、哪些场景能够使用索引掩盖来优化SQL?
场景1:全表count查询优化
原表为:
user(PKid,name,sex);
直接:
selectcount(name)fromuser;
不能使用索引掩盖。
添加索引:
altertableuseraddkey(name);
就能够使用索引掩盖提效。
场景2:列查询回表优化
selectid,name,sex…wherename=’shenjian’;
这个比如不再赘述,将单列索引(name)升级为联合索引(name,sex),即可防止回表。
场景3:分页查询
selectid,name,sex…orderbynamelimit500,100;
将单列索引(name)升级为联合索引(name,sex),也能够防止回表。
InnoDB集合索引一般索引,回表,索引掩盖,期望这1分钟大家有收获。

未经允许不得转载:迅闻网 » 被查询的列,为啥要放到索引里?
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

迅闻网-让更多人看到你

登录/注册返回首页