select 统计数量_到底为什么不能SELECT *?

最新推荐文章于 2023-06-14 13:58:38 发布

weixin_39890102

最新推荐文章于 2023-06-14 13:58:38 发布

阅读量483

点赞数

文章标签： select 统计数量

本文通过实验验证了SQL开发规范中关于'避免SELECT*'的建议是否合理。实验证明，在普通数据库中，SELECT*与指定列名在返回少量数据时几乎没有性能差异，但当涉及大对象（LOB）字段时，性能差距显著。此外，索引的存在对SELECT*的影响较大，可能显著降低查询速度。

摘要由CSDN通过智能技术生成

很多企业的SQL开发规范中都有这么一条：不能SELECT *，而应该在SELECT后面指定具体的列名。常见的解释是SELECT * 会返回所有的列，增加应用和数据库之间传输的数据，导致性能问题。真的是这样吗？实践出真知，接下来本文通过具体的实验场景来验证这个问题。

**场景一、SELECT * 和SELECT NAME，ADDRESS的区别**

我们为场景一准备了一张STUDENT表，表结构如下：

图1 STUDENT表结构

进一步查看表的详情，从统计信息中可以看到该表中有1280行数据：

图2 STUDENT表详情

先来执行如下第一条SQL语句，使用的是SELECT * ：

SELECT  *  FROM STUDENT WHERE ID = 512；

通过监控工具抓获的SQL历史曲线，可以发现该语句的平均执行时间为0.2ms，其中平均CPU时间才0.11ms，SQL的执行效率非常高。

图3 场景一下SELECT * 的SQL历史曲线

接下来我们还可以查看SQL语句的时间消耗分布图，看到该SQL执行的时候几乎没有任何等待时间，说明这条SQL执行的时候没有任何瓶颈。

图4 场景一下SELECT * 语句时间消耗分布图

进一步查看该SQL语句的瓶颈分析，可以看到该语句所有的指标都是绿色，其中有效读比例100%，平均操作行数为1，等待时间占比0%，指标都非常棒：

图5 场景一下SELECT * 语句瓶颈分析

接下来将语句改为如下指定列名的SQL语句再次进行实验：

SELECT  NAME, ADDRESS  FROM STUDENT WHERE ID = 512；

执行完成之后，同样通过监控SQL的平均执行时间以及瓶颈分析，发现该SQL语句的平均执行时间为0.20ms，CPU时间为0.1ms，同样各项指标都非常棒，没有任何瓶颈。

图6 场景一SELECT NAME, ADDRESS执行历史曲线

图7 场景一下SELECT NAME, ADDRESS语句瓶颈分析

到这里我们第一个场景的实验结束了，两条语句在执行效率上几乎没有任何区别，是不是有点儿大跌眼镜? 再对比一下两条语句的执行计划，发现两条语句的执行计划是一模一样的，根据前面的执行结果，这也在意料之中。

图8 场景一中SELECT NAME, ADDRESS与SELECT *语句执行计划对比

有人会指出，因为这个查询只返回一条记录，SELECT * 中返回数据的差异太小，所以SQL执行起来没有什么差异。我们接下来为场景一补充一个用例：将查询条件从ID = 512改为ID > 1000。

执行完成之后，再次对比两条SQL语句的平均执行时间和瓶颈分析。先来看SELECT * FROM STUDENTE WHERE ID > 1000的SQL历史曲线，平均执行时间为0.4ms：

图9 SELECT * FROM STUDENTE WHERE ID &gt; 1000 的SQL历史曲线

再来看语句SELECT NAME,ADDRESS FROM STUDENTE WHERE ID > 1000的历史曲线，平均执行时间也是0.4ms：

图10 SELECT NAME,ADDRESS FROM STUDENTE WHERE ID &gt; 1000的历史曲线

再来看瓶颈分析，现在两条SQL的瓶颈分析都显示平均操作行数为280行，这主要是因为我们设定正常的OLTP系统中高频执行的SQL语句不应该返回这么多行数据，所以显示成红色。但两个SQL语句的所有指标几乎一模一样，所以从这里看SELECT * 在返回多条记录的情况下和SELECT NAME, ADDRESS也没有什么区别：

图11 SELECT * FROM STUDENTE WHERE ID &gt; 1000瓶颈分析

图12 SELECT NAME, ADDRESS FROM STUDENTE WHERE ID &gt; 1000瓶颈分析

从场景一的实验看，无论是在返回单行还是多行的情况下，SELECT * 和 SELECT NAME，ADDRESS语句的执行效率并没有什么差异。这其实也很容易理解，因为主流的关系型数据库数据都是按行存储的，SQL语句执行的效率通常只与需要扫描的数据行数相关，同一行数据的所有列的值都是连续存储的，因此返回的结果中多一列或少一列的影响其实非常有限，场景一中两条SQL语句执行的各项具体指标也能印证这个结论。

场景一小结

普通数据库其实是用的行式存储，在大部分情况下，SELECT * 和 SELECT C1, C2没有太大差距，返回的数据量差距也有限。

那么，这条SQL开发规范是假的了？是不是可以放飞自我，SELECT * 随便用了呢？接下来看看我们场景二的实验。

场景二、为STUDENT表增加一个CLOB字段

在场景二中，我们新建了一张表STUDENT_CLOB。从下图的表结构中我们可以看到，该表的表结构和主键与场景一中的STUDENT表几乎一摸一样，只是多了一个类型为CLOB的字段DESCRIPTION：

图13 STUDENT_CLOB表结构

这次先来试一试SELECT NAME,ADDRESS FROM STUDENT _CLOB WHERE ID = 512，该语句执行的结果如下图所示：

图14 SELECT指定列名语句的历史曲线

可以看到，该SQL语句的执行结果为0.2毫秒，和场景一中不加CLOB字段时的执行时间一摸一样，看来增加的CLOB字段对该SQL语句的执行效率没有任何影响。

接下来再试试SELECT * FROM STUDENT_CLOB WHERE ID = 512，从SQL执行历史曲线中查看该语句的执行时间：

图15 SELECT * 语句的历史曲线

这次SELECT * 的平均执行时间飙涨，变成了358.8ms，相差了整整1794倍！再来对比一下两条SQL的执行计划：

图16 执行计划对比

两条SQL的执行计划竟然是一样的，都有通过索引查询，那么SQL变慢和执行计划没有任何关系，那么问题出哪儿了？我们来看看SQL语句瓶颈分析：

从SQL时间消耗分布图和SQL瓶颈分析中，可以看到该SQL语句等待时间占比占到了99.88%，而等待时间中占比最大的部分是直接读时间，达到34.81%。

图17 SQL瓶颈分析

SQL等待时间详细信息里其实已经直接给出了原因：SQL语句花费在读取大对象的时间消耗较高。

为什么一个大对象字段会造成这么大的差异呢？原因是数据库在读取数据的时候，都是先访问缓冲池(bufferpool)，如果缓冲池里没有所需要的数据，才会从磁盘读取数据。如果SELECT请求的数据已经在缓冲池中的时候，查询会非常快，因为不需要访问磁盘。

但是，有一个例外，那就是大对象(LOB，XML等)字段不能进缓冲池。大对象字段通常比较大，数据库会把它单独存在一个地方，而不是和其它普通列的数据存在一起。数据库访问大对象字段的时候，不会通过缓冲池，而是直接访问磁盘，例如在Db2里面叫做直接读(Direct Read)，其它种类数据库关于大对象的处理和Db2是一致的，只是这种行为的命名可能不叫直接读。

上面的例子每次只访问一条数据，仅仅是一次大对象的访问，就将SQL的平均执行时间从0.2ms增长到了358.8ms，如果这个SELECT * 是访问多行数据呢？

答案是会万分糟糕，因为每一条返回结果集中的记录都需要数据库执行一次直接读(DIRECT READ)操作。

下图是语句SELECT * FROM STUDENT_CLOB WHERE ID > 1000的SQL执行历史曲线，可以看到这条语句的平均执行时间达到了28,382.00ms，而在场景一中，同样的语句针对不含CLOB字段的STUDENT表，平均执行时间只有0.4ms，相差72万倍。

图18 返回多条记录SELECT *语句历史曲线

场景二小结

如果表上有LOB字段，那么SELECT的返回列里有没有LOB字段对SQL性能的影响非常大。因为LOB字段不能进入缓冲池，需要额外的IO操作，随着查询结果集数量的增长，对数据库服务器的压力会更加明显，因此在查询中要尽量规避返回字段中带有CLOB字段。

Oracle和Db2等数据库中可以使用lob inline特性帮助规避这个问题，大家可以自行查阅相关资料进一步了解。

场景三索引带来的影响

场景二里，我们看到了LOB字段对于SELECT *的影响，这个场景里，将验证索引对SELECT *的影响。

先执行以下建表语句创建一张测试表test3:

create table test3 as
      (
            select
                  TABSCHEMA
                 , TABNAME
                 , OWNER
                 , OWNERTYPE
                 , TYPE
                 , STATUS
                 , BASE_TABSCHEMA
                 , BASE_TABNAME
                 , ROWTYPESCHEMA
                 , ROWTYPENAME
            FROM
                  SYSCAT.TABLES
      ) DEFINITION ONLY;

然后，为这张表创建一个索引：

CREATE INDEX test3_idx ON test3(TABSCHEMA, TABNAME)

接下来为表test3插入数据：

INSERT INTO test3
SELECT
        TABSCHEMA
       , TABNAME
       , OWNER
       , OWNERTYPE
       , TYPE
       , STATUS
       , BASE_TABSCHEMA
       , BASE_TABNAME
       , ROWTYPESCHEMA
       , ROWTYPENAME
FROM
        SYSCAT.TABLES；

使用下面语句验证一下表的记录数量，返回的数量是230912：

SELECT COUNT(*) FROM test3;

这个场景里，同样实验两条语句，一条是SELECT *：

SELECT * FROM test3 WHERE TABSCHEMA = ’ SYSIBM’

另外一条是在SELECT后面指定列名：

SELECT TABSCHEMA, TABNAME FROM test3   WHERE TABSCHEMA=’SYSIBM’

这个表里面并没有LOB字段，这两条语句执行的情况是不是应该和场景一一样呢？我们来验证一下。

先看看SELECT * 的执行历史曲线：

图19 SELECT *语句历史曲线

可以看到，SELECT * 的平均执行时间是0.93ms，同时请留意一下，上图中有一个关键指标，平均数据逻辑读次数503.61次。

接下来我们再来看看指定列名SELECT的执行情况：

图20 SELECT指定列名语句历史曲线

从上图中可以看到，指定列名SELECT语句的平均执行时间只有0.12ms。

两条SQL语句的执行速度相差了大概8倍，也是一个比较大的差距了。造成这种差异的原因是什么呢？我们来对比一下SQL瓶颈分析：

图21 SELECT *语句瓶颈分析

图22 SELECT指定列名语句瓶颈分析

通过SQL瓶颈分析，我们发现两条SQL的主要差异是SELECT *的平均数据逻辑读页数达到了512次，而这条SQL语句的平均返回数据行数是512条，也就是说返回的每一条数据都需要一次数据逻辑读。而SELECT指定列名的平均数据逻辑读页数是0次，也就是没有。

为什么会这样呢？因为在数据库里，索引和数据表是单独存放的，如果需要返回的列，全部已经在索引里面了，那么查询的时候就不用访问数据页，这就是通常说的回表。

最后，对比一下两个SQL语句的执行计划，验证一下从SQL瓶颈分析中得到的结论：

图23 SELECT指定列名的执行计划

图24 SELECT *语句瓶颈分析

通过对比执行计划，可以清楚的看到SELECT* 语句的执行计划中多了访问TABLE的操作，而SELECT指定列名的语句则根本没有访问TABLE。

场景三小结

如果需要返回的字段都在某个索引里面，那么SELECT后面指定列的语句可以从索引中获得所有的字段，无需访问数据页，性能更好。在Db2和SQL Server等数据库中，可以用Index Include Columns帮助提升性能。

结论

我们通过三个场景下SELECT * 和SELECT指定列名的对比，证明了SELECT * 确实可能会带来较大的性能问题：

如果表字段中存在LOB字段的话，由于LOB字段不能进BUFFERPOOL，这种情况下SELECT * 访问所有的字段，包括LOB字段，与SELECT指定列名(不含LOB字段)相比，性能差异巨大；
如果SELECT指定列名中所有的字段都在某个索引中，则可以通过索引获取所有数据，不需要访问数据页，性能会有明显提升。

关于SELECT *的分析，本期就到这里，希望大家能有收获。

本文中所有SQL性能数据和分析截图，均可通过ShinSight Lite^[1]和ShinData DMP产品^[2]取得。新数科技^[3]本着开放、共享的理念，推出了免费的企业级ShinSight Lite数据库监控与性能分析系统，致力于通过数据库运维分析自动化，为提升数据库系统的高性能、高稳定性和数据安全助力。如有希望了解更多的信息，可访问新数科技官网http://www.shindata.com。

关于新数

ShinData新数科技成立于2014年，致力于为广大用户提供企业级数据库智能生态软件产品和数据云服务，产品形态兼有私有化部署和公有云SaaS模式，主要涵盖dbPaaS数据库云管理平台、分布式数据库和数据迁移传输平台等多个系列自主知识产权软件产品系列，应用于多家大型银行、证券、央企和能源制造行业企业，为广大企业在新时期云计算、大数据、人工智能等环境下的数据库基础软件转型变革提供持久源动力！

参考

^产品信息 http://www.shindata.com/shinsight.html
^产品信息 http://www.shindata.com/product.html
^官网 http://www.shindata.com

weixin_39890102

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
select 统计数量_到底为什么不能SELECT *?

很多企业的SQL开发规范中都有这么一条：不能SELECT *，而应该在SELECT后面指定具体的列名。常见的解释是SELECT * 会返回所有的列，增加应用和数据库之间传输的数据，导致性能问题。真的是这样吗？实践出真知，接下来本文通过具体的实验场景来验证这个问题。场景一、SELECT * 和SELECT NAME，ADDRESS的区别我们为场景一准备了一张STUDENT表，表结构如下：图1 STU...
复制链接

扫一扫

select 统计数量_到底为什么不能SELECT *?

场景一、SELECT * 和SELECT NAME，ADDRESS的区别

场景二、 为STUDENT表增加一个CLOB字段

场景三 索引带来的影响

结论

参考

**场景一、SELECT * 和SELECT NAME，ADDRESS的区别**

场景二、为STUDENT表增加一个CLOB字段

场景三索引带来的影响