带你撸出一手好代码
覆盖索引字段顺序不同造成的差别

覆盖索引的效果受到索引字段先后顺序的影响。

 

比如说


select column1,column2 from table where column1 = value1

 

对于此查询, 索引


create index index_a(column1, column2)




create index index_a(column2, column1)


的效果是截然不同的

 

以我的某个项目为例,有表结构如下

 

create table like_info(
    id int primary key
    from_user_id int NOT NULL,
    to_user_id int NOT NULL,
    add_time datetime NULL
)

 

表上有一个覆盖索引,索引字段为 from_user_id  和  to_user_id

 

create nonclustered index index_fuser_touser ON like_info
(
    from_user_id,
    to_user_id
)

 

当执行如下查询时,也就是筛选的字段是覆盖索引的首列


select id, from_user_id,to_user_id from like_info where from_user_id = 1671890

 

执行计划如下

 

未命名图片.png

 

针对查询, 数据库为执行一个非聚集索引查找操作

 

再根据 to_user_id 字段进行筛选


select from_user_id, to_user_id  from like_info where to_user_id = 52186246;

 

此查询同样会利用到表上的覆盖索引,但是利用索引的方式却有所改变

 

未命名图片1.png

 

此查询利用索引的方式变为非聚集索引扫描

 

查找和扫描, 从字面意义上理解似乎差别不大。 然而,数据库中,非聚集索引查找和非聚集索引扫描却是完全不同的两种工作模式。

 

我假定此文章读者掌握索引的基础工作原理,如有疑问请参阅此文

 

http://www.cnblogs.com/aspwebchh/p/6652855.html

 

非聚集索引查找就是从平衡树的根节点定位到叶节点,并进行有序扫描的过程。索引能使查询变快的最根本原因就是这中查找方式。 换句话说,这就是索引的看家本领。

 

而非聚集索引扫描只是借助了索引的一部分能力,索引的平衡树数据结构的威力并未得到发挥。我们知道覆盖索引的数据集中在索引的叶节点, 非聚集索引查找是通过索引第一个键的值从根节点定位到叶节点。非聚集索引扫描是比较粗暴的直接在叶节点查找数据, 这有点像我们在现实世界中在某个范围内寻找一个具体的地点,用的方法是一家一家的敲门询问,显然这不如通过手机导航软件定位来的迅捷。 索引中叶子节点以外的那些节点就是导航软件,显然非聚集索引查找利用了导航软件,而非聚集索引扫描却没有。

 

所以当通过索引优化查询时,要优先利用非聚集索引查找,换言之,当按照某个字段的值进行筛选时, 如要利用某个索引,此字段是索引字段的第一列,则效果最佳。


作者:陈大侠
日期:2018-02-19

留言(0条)

我要发表留言

您的大名 选填
电子邮箱 选填

欢迎关注微信公众号 「带你撸出一手好代码」

首页    GitHub 知乎 豆瓣 博客园