SELECT * 测试

描述

大家通常禁止在生产环境直接使用select * 已成常识了，也常常在开发规范中就会规定不允许直接使用select *，那么我们为什么不允许使用select * ，在一些什么场景下select * 会出问题？能否控制不能直接使用select *?出于这些疑问，我们特别测试记录一下。

测试环境

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

Feb 10 2012 19:39:15

Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

select * 可能会造成业务功能出错

假设我们有一个视图 V_test 代码如下：

1 CREATE VIEW v_test
2 AS
3     SELECT  *
4     FROM    test

你在web前端调用直接使用如下脚本并对应好显示的字段，当时功能是正常的。

   SELECT  *
    FROM    v_test

test 表结构如下：

后来因为业务调整，有人调整了test表，并加一个字段F ，那么接下来的后果就会前端相应的功能因新增的字段F直接报错。

select * 可能会造成性能方面的问题

测试数据准备

 1 --新建测试表
 2 IF OBJECT_ID('test', 'U') IS NOT NULL
 3     DROP TABLE test;
 4 CREATE TABLE test
 5     (
 6       id int IDENTITY(1, 1) ,
 7       a nvarchar(20) ,
 8       b nvarchar(15) ,
 9       c nvarchar(20) ,
10       d int ,
11       e float
12     );

 1 --造测试数据
 2 DECLARE @i INT; 
 3 SET @i = 10000;
 4 WHILE ( @i > 0 )
 5     BEGIN 
 6         INSERT  INTO test
 7                 ( a ,
 8                   b ,
 9                   c ,
10                   d ,
11                   e
12                 )
13                 SELECT  RAND() * 1000 ,
14                         RAND() * 1000 ,
15                         REPLICATE('a', 3) ,
16                         @i ,
17                         @i - 1;
18         SELECT  @i = @i - 1;
19     END;

1 --新建索引（主键、非聚集索引）
2 ALTER TABLE dbo.test ADD CONSTRAINT PK_test_id PRIMARY KEY(ID);
3 CREATE INDEX IX_test_1 ON dbo.test(a);

查询条件使用主键字段的执行计划，主键是默认覆盖全部字段。

如直接使用字段a做查询条件

还是聚集索引扫描，全表扫，无法使用我们已新建好的非聚集索引IX_test_1，在上述语句中因该索引未覆盖到非a 的字段。假设实际场景我们只需取a字段，那么执行计划又是怎么样的？

如上图，就会走我们期望的索引，同时也可以减少因select * 而多读的字段（id、b、c、d、e）的网络传输，所以，尽量指定自己所需的字段名,可以避免一些无谓的性能开销。

如何控制不让 select *

假设我们不允许在上述测试表test上执行select * 可以如何处理

 1 --新建测试表
 2 IF OBJECT_ID('test', 'U') IS NOT NULL
 3     DROP TABLE test;
 4 CREATE TABLE test
 5     (
 6       id int IDENTITY(1, 1) ,
 7       a nvarchar(20) ,
 8       b nvarchar(15) ,
 9       c nvarchar(20) ,
10       d int ,
11       e float,
12       abort_select_all  AS  (1/0)        ---新增控制字段
13     );
14 --造测试数据
15 DECLARE @i INT; 
16 SET @i = 10000;
17 WHILE ( @i > 0 )
18     BEGIN 
19         INSERT  INTO test
20                 ( a ,
21                   b ,
22                   c ,
23                   d ,
24                   e
25                 )
26                 SELECT  RAND() * 1000 ,
27                         RAND() * 1000 ,
28                         REPLICATE('a', 3) ,
29                         @i ,
30                         @i - 1;
31         SELECT  @i = @i - 1;
32     END; 
33 --新建索引 （主键、非聚集索引）
34 ALTER TABLE dbo.test ADD CONSTRAINT PK_test_id PRIMARY KEY(ID);
35 CREATE INDEX IX_test_1 ON dbo.test(a);

如下图，如果select * 因读取了abort_select_all字段（1/0）就会直接报错,从而达到不能直接select *的效果；

直接按字段名查询没有问题（非abort_select_all字段）。

直接按字段名查询没有问题。