如何理解index skip scan

最新推荐文章于 2023-10-07 11:10:01 发布

weixin_33690963

最新推荐文章于 2023-10-07 11:10:01 发布

阅读量281

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/huayd/blog/133788

版权

为什么80%的码农都做不了架构师？>>>

问别人什么是index skip scan的时候，很大回答都局限于，这个执行计划不常见，并且出现在有组合索引，但是前导列不再谓语条件中的查询。仅此而已。

这样的回答，确实基本回答了index skip scan的使用场景。

Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.

The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate.

但没有解释，index skip scan是如何实现，在没有前导列的情况下，进行"skip" scan的。

Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

In a skip scan, the number of logical subindexes is determined by the number of distinct values in the leading column.

看到这里，其实就比较好理解了。逻辑上，通过前导列，把组合索引分成几个更小子索引，然后对这些子所以进行扫描，并合并结果。子索引的数目，由前导列的不同值的数目决定。下面是一个例子：

-- For example, assume that you run the following query for a customer in the sh.customers table:

SELECT * FROM sh.customers WHERE cust_email = 'Abbey@company.com';

-- The customers table has a column cust_gender whose values are either M or F. Assume that a composite index exists on the columns (cust_gender, cust_email) that was created as follows:

CREATE INDEX customers_gender_email ON sh.customers (cust_gender, cust_email);

-- portion of the index entries.
F,Wolf@company.com,rowid
F,Wolsey@company.com,rowid
F,Wood@company.com,rowid
F,Woodman@company.com,rowid
F,Yang@company.com,rowid
F,Zimmerman@company.com,rowid
M,Abbassi@company.com,rowid
M,Abbey@company.com,rowid

-- The database can use a skip scan of this index even though cust_gender is not specified in the WHERE clause.

-- When searching for the record for the customer whose email is  bbey@company.com, the database searches the subindex with the value First and then searches the subindex with the value M. Conceptually, the database processes the query as follows:

SELECT * FROM sh.customers WHERE cust_gender = 'F' 
  AND cust_email = 'Abbey@company.com'
UNION ALL
SELECT * FROM sh.customers WHERE cust_gender = 'M'
  AND cust_email = 'Abbey@company.com';

转载于:https://my.oschina.net/huayd/blog/133788