什么是“SELECT *”的速度命中率使用MySql，而不是更窄的数据库调用？ [英] what is the speed hit of "SELECT *" with MySql, as opposed to narrower database calls?
问题描述
是否有任何额外的，不需要的VARCHAR，CHAR，
INT，BIGINT，TEXT或MEDIUMTEXT使用MySql减慢数据库调用的基准？
PostGre信息也很有用。
我想向一些朋友解释制作数据库的实用程序
来电返回只需要的数据。
作为我正在谈论的一个例子，假设我们有一个数据库
表格如下：
表博客（
int id，
varchar 255标题，
text mainContent，
int dateCreated，
varchar 255作者，
varchar 255 navigationText，
char 1 isPrivate
）;
假设我正在做一个PHP命令，它获得了头条新闻，并提供了一个
链接到实际页面。什么是速度差异
SELECT * FROM weblogs
而不是
SELECT id，标题来自weblogs
那里有信息，可以大致了解每个额外的，不需要的数据库字段可能会减慢脚本的速度？
lk *** ***@geocities.com 写道：
是否有额外的，不需要的VARCHAR，CHAR，
INT的基准， BIGINT，TEXT或MEDIUMTEXT使用MySql减慢数据库调用吗？
PostGre信息也很有用。
我正在尝试向一些朋友解释制作数据库的实用程序
调用仅返回所需的数据。
作为我正在谈论的一个例子，假设我们有一个像这样的数据库表：
表博客（
int id，
varchar 255标题，
text mainContent，
int dateCreated，
varchar 255作者，
varchar 255 navigationText，
char 1 isPrivate
）;
假设我正在做一个PHP命令，它成为头条新闻，并提供了一个指向实际页面的链接。
SELECT / FROM
SELECT id，标题来自weblogs
之间的速度差异是什么？ >那里有信息可以大致了解每个额外的，不需要的数据库字段可能会减慢脚本的速度吗？
我''我没有得到任何关于性能的数据，虽然取决于所涉及的b / b
列的数量和它们的大小可能相当多。我可以告诉你
一个实例我遇到了另一个使用ODBC的DBMS，它只是喜欢
来读取系统目录以获取有关所有内容的信息。 >
列级别，用于从70列表中返回5000行用户数据
占用了200多万页I / O到系统目录。不太好
表现。
在我看来还有其他更重要的理由不使用SELECT
*。这些主要与应用程序发生的情况有关，如果您更改数据库结构。例如，在你给出的表格中，我们决定在作者列的b / b
之后立即添加列发布者varchar（255）。我们还假设您按位置访问
结果集中的列，而不是按名称访问。现在，如果您使用select *
，则在您显示发布者之前显示navigationText。
插入语句出现类似问题列列表不是指定
，而只是值列表。在您添加
列之前，一切正常。如果最后一列是NOT NULL，那么声明破裂了。更糟糕
虽然你最终可能会把数据放到错误的地方。
HTH
Phil
lk******@geocities.com 写道：< blockquote class =post_quotes>是否有任何额外的，不需要的VARCHAR，CHAR，
INT，BIGINT，TEXT或MEDIUMTEXT使用MySql减慢数据库调用的基准？
PostGre信息也将是有用。
我正在向一些朋友解释使数据库调用只返回所需数据的实用程序。
作为我的一个例子我在谈论，假设我们有一个像这样的数据库表：
表weblogs（
int id，
varchar 255标题，
text mainContent，
int dateCreated，
varchar 255 author，
varchar 255 navigationText，
char 1 isPrivate
）;
假设我'我正在做一个PHP命令，它成为头条新闻并提供了一个
链接到实际的页面。
SELECT / FROM
SELECT id，标题来自weblogs
之间的速度差异是什么？ >那里有信息可以大致了解每个额外的，不需要的数据库字段可能会减慢脚本的速度吗？
我想你会发现它不会产生很大的差别
除非你要检索数百万行。
读取的数据量由物理量决定阅读
磁盘子系统的特性。数据库将磁盘子系统交给操作系统取出的内容，找到所需的
表行，然后解析该行以取出每个
列或指定的列。
大部分时间用于等待磁盘i / o完成
和时间的差异高性能数据库如
MySQL花费解析数据并提取和呈现
每列或列的子集都不重要。
顺便说一句，我同意之前作出回应的菲利普·尼尔森，并且认为应该避免使用SELECT *，因为可能会出现问题，因为未来的数据库维护更改会产生
。
HTH
Jerry
" ;菲利普尼尔森 < TE ***** @ scotdb.com>在消息中写道
新闻：我们*********************** @ news.easynews.com ..。lk******@geocities.com 写道：
是否有任何额外的，不需要的VARCHAR，CHAR，
INT，BIGINT，TEXT或MEDIUMTEXT使用MySql减慢数据库调用的基准？
PostGre信息也将是有用。
我正在向一些朋友解释使数据库调用只返回所需数据的实用程序。
作为我的一个例子我在谈论，假设我们有一个像这样的数据库表：
表weblogs（
int id，
varchar 255标题，
text mainContent，
int dateCreated，
varchar 255 author，
varchar 255 navigationText，
char 1 isPrivate
）;
假设我'正在做一个PHP命令，它获得了头条新闻，并提供了一个指向实际页面的链接。
SELECT / FROM
SELECT id，标题来自weblogs
之间的速度差异是什么？ >那里有信息可以大致了解每个额外的，不需要的数据库字段可能会减慢脚本的速度吗？
我没有得到任何关于性能的数据，尽管取决于所涉及的列数和它们的大小可能相当多。我可以告诉你
我使用ODBC遇到另一个DBMS的一个实例，它只是喜欢阅读系统目录以获取有关一切的信息，直到列级别，从70列表中返回5000行用户数据
将200多页页面I / O带到系统目录中。对于表现来说并不是很好。
在我看来，还有其他更重要的理由不使用
SELECT
*。这些主要涉及如果您更改数据库结构将会发生什么。例如，在您提供的表中
允许
假设我们决定在作者列之后立即添加列发布者varchar（255）。我们还假设您按位置访问
结果集中的列，而不是按名称访问。现在，如果您使用select *
，您将在显示publisherText之前显示发布者。
这是主要原因。返回关联列表，这样你就可以按名称而不是他们的位置来参考
。这种方式不会以什么顺序检索列。
类似的问题出现在列表列表未指定的插入语句中，但只是值列表。在您添加
列之前一切正常。如果最后一列是NOT NULL，那么声明破裂了。更糟糕
虽然你最终可能会把数据放到错误的位置。
如果你做我做的事情并构造所有INSERT，UPDATE和DELETE语句
以编程方式然后这个问题永远不会出现。
-
Tony Marston
http://www.tonymarston.net
Are there any benchmarks on how much an extra, unneeded VARCHAR, CHAR,
INT, BIGINT, TEXT or MEDIUMTEXT slows down a database call with MySql?
PostGre info would also be useful.
I''m trying to explain to some friends the utility of making database
calls return only needed data.
As an example of what I''m talking about, suppose we had a database
table sort of like this:
table weblogs (
int id,
varchar 255 headline,
text mainContent,
int dateCreated,
varchar 255 author,
varchar 255 navigationText,
char 1 isPrivate
);
Suppose I''m doing a PHP command that get''s the headlines and offers a
link to the actual page. What is the speed difference between
SELECT * FROM weblogs
as opposed to
SELECT id, headline FROM weblogs
And is there info out there that gives a general sense of how much each
extra, unneeded database field might slow down a script?
lk******@geocities.com wrote:
Are there any benchmarks on how much an extra, unneeded VARCHAR, CHAR,
INT, BIGINT, TEXT or MEDIUMTEXT slows down a database call with MySql?
PostGre info would also be useful.
I''m trying to explain to some friends the utility of making database
calls return only needed data.
As an example of what I''m talking about, suppose we had a database
table sort of like this:
table weblogs (
int id,
varchar 255 headline,
text mainContent,
int dateCreated,
varchar 255 author,
varchar 255 navigationText,
char 1 isPrivate
);
Suppose I''m doing a PHP command that get''s the headlines and offers a
link to the actual page. What is the speed difference between
SELECT * FROM weblogs
as opposed to
SELECT id, headline FROM weblogs
And is there info out there that gives a general sense of how much each
extra, unneeded database field might slow down a script?
I''ve not got any figures on performance, although depending on the number of
columns involved and their size it could be quite a bit. I can tell you of
one instance I came across with another DBMS using ODBC, which just loves
to read the system catalog for information about everything right down to
column level, that to return 5000 rows of user data from a 70 column table
took over 2 million page I/Os to the system catalog. Not exactly great
for performance.
In my opinion there are other, more important, reasons for not using "SELECT
*". These principally relate to what will happen to the application if
you change the database structure. For example, in the table you gave lets
assume we decide to add a column publisher varchar(255) immediately after
the author column. Let''s also assume that you access the columns in a
resultset by position, rather than by name. Now if you use a "select *"
where you displayed navigationText before you will now display publisher.
Similar problems occur with insert statements where the column list isn''t
specified, but just the values list. Everything is OK until you add a
column. If the last column is "NOT NULL" the statement breaks. Worse
though you can end up putting data into the wrong places.
HTH
Phil
lk******@geocities.com wrote:Are there any benchmarks on how much an extra, unneeded VARCHAR, CHAR,
INT, BIGINT, TEXT or MEDIUMTEXT slows down a database call with MySql?
PostGre info would also be useful.
I''m trying to explain to some friends the utility of making database
calls return only needed data.
As an example of what I''m talking about, suppose we had a database
table sort of like this:
table weblogs (
int id,
varchar 255 headline,
text mainContent,
int dateCreated,
varchar 255 author,
varchar 255 navigationText,
char 1 isPrivate
);
Suppose I''m doing a PHP command that get''s the headlines and offers a
link to the actual page. What is the speed difference between
SELECT * FROM weblogs
as opposed to
SELECT id, headline FROM weblogs
And is there info out there that gives a general sense of how much each
extra, unneeded database field might slow down a script?
I think you will find it isn''t going to make much difference
unless you are retrieving several million rows.
The amount of data read is dictated by the physical read
characteristics of the disk subsystem. The database takes what
the disk subsystem hands off to the OS, locates the desired
table row, and then parses the row to pull out either each
column or the specified columns.
The bulk of the time is spent waiting for disk i/o completion
and the difference in time a high performance database like
MySQL spends parsing the data and extracting and presenting
either every column or a subset of the columns isn''t significant.
BTW, I agree with Philip Nelson, who responded earlier, and felt
that SELECT * should be avoided due to possible problems arising
from future maintenance changes to the database.
HTH
Jerry
"Philip Nelson" <te*****@scotdb.com> wrote in message
news:we***********************@news.easynews.com.. .lk******@geocities.com wrote:
Are there any benchmarks on how much an extra, unneeded VARCHAR, CHAR,
INT, BIGINT, TEXT or MEDIUMTEXT slows down a database call with MySql?
PostGre info would also be useful.
I''m trying to explain to some friends the utility of making database
calls return only needed data.
As an example of what I''m talking about, suppose we had a database
table sort of like this:
table weblogs (
int id,
varchar 255 headline,
text mainContent,
int dateCreated,
varchar 255 author,
varchar 255 navigationText,
char 1 isPrivate
);
Suppose I''m doing a PHP command that get''s the headlines and offers a
link to the actual page. What is the speed difference between
SELECT * FROM weblogs
as opposed to
SELECT id, headline FROM weblogs
And is there info out there that gives a general sense of how much each
extra, unneeded database field might slow down a script?
I''ve not got any figures on performance, although depending on the number
of
columns involved and their size it could be quite a bit. I can tell you
of
one instance I came across with another DBMS using ODBC, which just loves
to read the system catalog for information about everything right down to
column level, that to return 5000 rows of user data from a 70 column table
took over 2 million page I/Os to the system catalog. Not exactly great
for performance.
In my opinion there are other, more important, reasons for not using
"SELECT
*". These principally relate to what will happen to the application if
you change the database structure. For example, in the table you gave
lets
assume we decide to add a column publisher varchar(255) immediately after
the author column. Let''s also assume that you access the columns in a
resultset by position, rather than by name. Now if you use a "select *"
where you displayed navigationText before you will now display publisher.
That''s the primary reason for returning associative lists, so that you can
reference items by name instead of their position. That way is does not
matter in what order the columns are retrieved.
Similar problems occur with insert statements where the column list isn''t
specified, but just the values list. Everything is OK until you add a
column. If the last column is "NOT NULL" the statement breaks. Worse
though you can end up putting data into the wrong places.
If you do what I do and construct all INSERT, UPDATE and DELETE statements
programmatically then this problem will never appear.
--
Tony Marston
http://www.tonymarston.net
这篇关于什么是“SELECT *”的速度命中率使用MySql，而不是更窄的数据库调用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
