sql server根据返回列决定是否使用索引的例子

àì夳堔傛蜴生んèń 2022-08-29 06:18 95阅读 0赞

### sql server根据返回列 决定 是否使用索引的例子 ###

*  前提
 *  试验

# 前提 #

表定义、数据插入、索引定义

-- 表定义，及数据插入
    create table op_param_sniffing (
    	id bigint IDENTITY(1,1) not null,
    	product_id varchar(100) not null,
    	product_name varchar(100) not null
    )
    
    declare @id int;
    set @id = 1;
    while @id < 14 
    begin
    	insert into op_param_sniffing(product_id, product_name) values('100', '商品100')
    	set @id = @id + 1
    end
    while @id < 100 
    begin
    	insert into op_param_sniffing(product_id, product_name) values('200', '商品200')
    	set @id = @id + 1
    end
    while @id < 1000
    begin
    	insert into op_param_sniffing(product_id, product_name) values('300', '商品300')
    	set @id = @id + 1
    end
    -- 索引
    create index idx_op_param_sniffing_product_id on op_param_sniffing(product_id)

# 试验 #

1）  
select \* from op\_param\_sniffing where product\_id = ‘100’  
select product\_id from op\_param\_sniffing where product\_id = ‘100’  
这两个sql的执行计划分别是：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70]  
可以看到select\*时并不会选择索引，因为索引里只有product\_id, 其它返回列还需要回表查询，优化器应该是认为还不如全表扫描吧。

2）我们加大数据量到10w

declare @id int;
    set @id = 1000;
    while @id < 100001
    begin
    	insert into op_param_sniffing(product_id, product_name) values('300', '商品300')
    	set @id = @id + 1
    end

再来看实际的执行计划：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 1]

我们可以看到select\*开始的sql也走索引了！这是因为在10w行记录里只有十几行目标数据，既然回表查询也会比全表扫描快！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70]: /images/20220829/5c417961be7c4cd7b7addce50b5d7707.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 1]: /images/20220829/0ed1834e8257462b8f3fa1fa049ec3ae.png