SQL 查询性能优化 解决书签查找
程序员文章站
2023-12-13 15:34:34
先来看看什么是书签查找： 当优化器所选择的非聚簇索引只包含查询请求的一部分字段时，就需要一个查找（lookup）来检索其他字段来满足请求。对一个有聚簇索引的表来说是...
先来看看什么是书签查找：
当优化器所选择的非聚簇索引只包含查询请求的一部分字段时，就需要一个查找（lookup）来检索其他字段来满足请求。对一个有聚簇索引的表来说是一个键查找（key lookup），对一个堆表来说是一个rid查找（rid lookup）。这种查找即是——书签查找。
书签查找根据索引的行定位器从表中读取数据。因此，除了索引页面的逻辑读取外，还需要数据页面的逻辑读取。
从索引的行定位器到从表中读取数据这之间会产生一些额外的开销，本文就来解决这个开销。
先看下我的测试表结构：
其中可以看出 有一个 聚簇索引 pk_userid 和一个 非聚簇索引ix_username。
看看产生书签 查找的效果：
select username,gender from dbo.userinfo where username='usern600'
按上面的 sql 产生执行计划 可以看出， 会产生一个书签查找（key lookup），如下图
如果把上面的 sql 改写成
select username from dbo.userinfo where username='usern600'
可以看出 书签查找 没有了。
本sql 产生书签查找的 主要原因是 本sql 优化器会选择 非聚簇索引ix_username，来执生sql 。ix_username 索引不包含 gender 这个字段 于是产生个从索引到 数据表的 一个 查找 即 书签查找。
解决书签查找：
方法一、使用一个 聚簇索引
对于聚簇索引， 索引的叶子页面和表的数据页面相同，因此，当读取聚簇索引 键列的值时，数据引擎可以读取其它列的值而不需要任何行定位，这样就解决了书签查找。
对于这句sql （ select username,gender from dbo.userinfo where username='usern600'）解决了书签查找的办法就是在username 上 建聚簇索引 ，因为一个表只有一个聚簇索引 ，这就意味着删除现有聚簇索引（pk_userid），将会造成其它从表 中的外键约束 要发生更改，这需要考一些相关的工作，可能严重影响依赖于现有聚簇索引的其它查询。
方法二、使用一个 覆盖索引
覆盖索引 是在所有为满足sql 查询不用到达基本表所需的列 建立的非聚簇索引。如果查询遇到一个索引并且完全不需要引用底层数据表，那么 该索引可以被认为是 覆盖索引。
对于这句sql （ select username,gender from dbo.userinfo where username='usern600'） 解决书签查找的办法就是 在非聚簇索引ix_username 里包含 gender 字段。
也就是在 建索引时 用include 语句，具体操作如下
用include 最好在 以下情况下使用：
1、不希望增加索引键的大小，但是仍然可以建一个 覆盖索引；
2、打算索引一种不能被索引的数据类型（除了文本、ntext和图像）；
3、已经超过了一个索引的关键字列的最大数量
方法三、使用 索引连接
索引连接 是使用多个索引之间一个索引交叉来完全覆盖一个查询。如果覆盖索引变的非常宽，那么就可以考虑索引连接。
对于这句sql （ select username,gender from dbo.userinfo where username='usern600' and gender=1）可以在 gender 上 建一个非聚簇索引就行了。
对于这个例 子，可能 sql 优化器并没有同时 选 用非聚簇索引ix_username 和 我们新建立在gender 上的索引，这时我们可以告知 sql 优化器 同时使用 这个两上索引，操作如下
select gender,username from userinfo with(index (ix_gender,ix_username)) where username='jins' and gender=0
好了就写这么多吧.
当优化器所选择的非聚簇索引只包含查询请求的一部分字段时，就需要一个查找（lookup）来检索其他字段来满足请求。对一个有聚簇索引的表来说是一个键查找（key lookup），对一个堆表来说是一个rid查找（rid lookup）。这种查找即是——书签查找。
书签查找根据索引的行定位器从表中读取数据。因此，除了索引页面的逻辑读取外，还需要数据页面的逻辑读取。
从索引的行定位器到从表中读取数据这之间会产生一些额外的开销，本文就来解决这个开销。
先看下我的测试表结构：
其中可以看出 有一个 聚簇索引 pk_userid 和一个 非聚簇索引ix_username。
看看产生书签 查找的效果：
select username,gender from dbo.userinfo where username='usern600'
按上面的 sql 产生执行计划 可以看出， 会产生一个书签查找（key lookup），如下图
如果把上面的 sql 改写成
select username from dbo.userinfo where username='usern600'
可以看出 书签查找 没有了。
本sql 产生书签查找的 主要原因是 本sql 优化器会选择 非聚簇索引ix_username，来执生sql 。ix_username 索引不包含 gender 这个字段 于是产生个从索引到 数据表的 一个 查找 即 书签查找。
解决书签查找：
方法一、使用一个 聚簇索引
对于聚簇索引， 索引的叶子页面和表的数据页面相同，因此，当读取聚簇索引 键列的值时，数据引擎可以读取其它列的值而不需要任何行定位，这样就解决了书签查找。
对于这句sql （ select username,gender from dbo.userinfo where username='usern600'）解决了书签查找的办法就是在username 上 建聚簇索引 ，因为一个表只有一个聚簇索引 ，这就意味着删除现有聚簇索引（pk_userid），将会造成其它从表 中的外键约束 要发生更改，这需要考一些相关的工作，可能严重影响依赖于现有聚簇索引的其它查询。
方法二、使用一个 覆盖索引
覆盖索引 是在所有为满足sql 查询不用到达基本表所需的列 建立的非聚簇索引。如果查询遇到一个索引并且完全不需要引用底层数据表，那么 该索引可以被认为是 覆盖索引。
对于这句sql （ select username,gender from dbo.userinfo where username='usern600'） 解决书签查找的办法就是 在非聚簇索引ix_username 里包含 gender 字段。
也就是在 建索引时 用include 语句，具体操作如下
用include 最好在 以下情况下使用：
1、不希望增加索引键的大小，但是仍然可以建一个 覆盖索引；
2、打算索引一种不能被索引的数据类型（除了文本、ntext和图像）；
3、已经超过了一个索引的关键字列的最大数量
方法三、使用 索引连接
索引连接 是使用多个索引之间一个索引交叉来完全覆盖一个查询。如果覆盖索引变的非常宽，那么就可以考虑索引连接。
对于这句sql （ select username,gender from dbo.userinfo where username='usern600' and gender=1）可以在 gender 上 建一个非聚簇索引就行了。
对于这个例 子，可能 sql 优化器并没有同时 选 用非聚簇索引ix_username 和 我们新建立在gender 上的索引，这时我们可以告知 sql 优化器 同时使用 这个两上索引，操作如下
select gender,username from userinfo with(index (ix_gender,ix_username)) where username='jins' and gender=0
好了就写这么多吧.