SELECT * 测试

描述

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

 

测试环境

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

Feb 10 2012 19:39:15 

Copyright (c) Microsoft Corporation

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

 

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

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

CREATE VIEW v_test
AS
    SELECT  *
    FROM    test

 

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

   SELECT  *
    FROM    v_test

 

test 表结构如下：

 

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

 

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

测试数据准备

--新建测试表
IF OBJECT_ID('test', 'U') IS NOT NULL
    DROP TABLE test;
CREATE TABLE test
    (
      id int IDENTITY(1, 1) ,
      a nvarchar(20) ,
      b nvarchar(15) ,
      c nvarchar(20) ,
      d int ,
      e float
    );

--造测试数据
DECLARE @i INT; 
SET @i = 10000;
WHILE ( @i > 0 )
    BEGIN 
        INSERT  INTO test
                ( a ,
                  b ,
                  c ,
                  d ,
                  e
                )
                SELECT  RAND() * 1000 ,
                        RAND() * 1000 ,
                        REPLICATE('a', 3) ,
                        @i ,
                        @i - 1;
        SELECT  @i = @i - 1;
    END; 

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

 

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

 

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

 

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

 

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

 

如何控制不让 select *

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

--新建测试表
IF OBJECT_ID('test', 'U') IS NOT NULL
    DROP TABLE test;
CREATE TABLE test
    (
      id int IDENTITY(1, 1) ,
      a nvarchar(20) ,
      b nvarchar(15) ,
      c nvarchar(20) ,
      d int ,
      e float,
      abort_select_all  AS  (1/0)        ---新增控制字段
    );
--造测试数据
DECLARE @i INT; 
SET @i = 10000;
WHILE ( @i > 0 )
    BEGIN 
        INSERT  INTO test
                ( a ,
                  b ,
                  c ,
                  d ,
                  e
                )
                SELECT  RAND() * 1000 ,
                        RAND() * 1000 ,
                        REPLICATE('a', 3) ,
                        @i ,
                        @i - 1;
        SELECT  @i = @i - 1;
    END; 
--新建索引 （主键、非聚集索引）
ALTER TABLE dbo.test ADD CONSTRAINT PK_test_id PRIMARY KEY(ID);
CREATE INDEX IX_test_1 ON dbo.test(a);

 

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

 

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

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