返回的列数是否会影响查询速度? [英] Does the number of columns returned affect the speed of a query?
问题描述
如果我有两个查询
SELECT Id, Forename, Surname
FROM Person
WHERE PersonName Like(‘%frank%’)
和
SELECT *
FROM Person
WHERE PersonName Like(‘%frank%’)
哪个查询会运行得更快?是 where 子句/表加入最大的因素，还是返回的列数?
Which query will run faster? Is the where clause / table joining the biggest factor, or the number of columns returned?
我之所以这么问是因为我正在构建一系列映射到数据库表的对象.通常每个对象至少会有这三个方法:
I’m asking because I’m building a series of objects that map to database tables. Typically each object will have at least these three methods:
Select – 选择所有内容
List – 选择足够多的可用于填充下拉列表的内容
List – Selects enough that is can be used to populate a dropdown list
Search – 选择结果中可见的所有内容，通常大约 6 列.
Search – Selects everything that is viewable in the results, typically about 6 or so columns.
如果每个查询都返回完全相同的一组列，则代码的维护和测试应该更简单.数据库不太可能在任何给定表中超过 50,000 行，因此如果性能差异很小，那么我将节省开发时间.如果性能要下降，那么我会从不同的角度工作.
If each query returned exactly the same set of columns the maintenance and testing of the code should be more trivial. The database is unlikely to get above 50,000 rows in any given table, so if the difference in performance is small then I’ll take the saving in development time. If the performance is going to drop through the floor, then I’ll work at in from a different angle.
那么，为了便于开发，SELECT *是明智的，还是幼稚的?
So, for ease of development, is SELECT * sensible, or is it naive?
推荐答案
你最好避免 SELECT *
- 更改表格布局时会导致混淆.
- 它会选择不需要的列，并且您的数据包会变大.
- 列可以有重名，这对某些应用程序也不利
- 如果你需要的所有列都被一个索引覆盖了，
SELECT columns只会使用这个索引，而SELECT *需要访问表记录来获取您不需要的值.对性能也不利.
- It leads to confusion when you change the table layout.
- It selects unneeded columns, and your data packets get larger.
- The columns can get duplicate names, which is also not good for some applications
- If all the columns you need are covered by an index,
SELECT columnswill only use this index, whileSELECT *will need to visit the table records to get the values you don't need. Also bad for performance.
这篇关于返回的列数是否会影响查询速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
