I'm working with a table in MySql that has an int indexed column called "serial". This table has around 2 million rows.
我正在使用MySql中的一个表，它有一个名为“serial”的int索引列。这个表大约有200万行。
If I apply a select statement with this column in this way:
如果我以这种方式对本列应用select语句:
SELECT serial FROM Table WHERE Serial=12345
this returns the data in around < 1 sec.
这将返回小于1秒的数据。
However, if I use a SELECT * query in this same table, this query takes around 78 seconds...
但是，如果我在同一个表中使用SELECT *查询，这个查询大约需要78秒……
I know it is not useful to apply indexes to all the columns in the table, how can I optimize/minimize the query response time if I need to get all the columns from a specific serial?
我知道将索引应用到表中的所有列是没有用的，如果我需要从一个特定的串行中获得所有列，那么如何优化/最小化查询响应时间呢?
SELECT * FROM Table WHERE serial= 12345
The results from EXPLAIN :
解释结果:
SELECT serial:
选择系列:
1 SIMPLE tableName index idx_tablename_serial 5 6686620 Using index
一个简单的表名索引idx_tablename_serial 5 6686620使用索引
SELECT * :
SELECT *:
1 SIMPLE agsensepivotreadings ALL (null values) 6686620
1简单的agsensepivotreading ALL (null values) 6686620
Please, any sugggestion or guide will be very appreciated.
如有任何建议或指导，我们将不胜感激。
4 个解决方案
#1
1
There is part answer for your question.
你的问题有部分答案。
https://*.com/a/3211164/2957840
https://*.com/a/3211164/2957840
But also, maybe you should consider partitioning your table: https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
但是，也许您应该考虑对表进行分区:https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
#2
1
Even limiting how many columns you need to read by a few will help. Just limit it even more and IF indexing a few more columns helps then go ahead but they'd need to be used in the WHERE clause.
甚至限制你需要阅读的专栏的数量也会有所帮助。只需要对它进行更多的限制，如果索引更多的列会有帮助，那么继续，但是它们需要在WHERE子句中使用。
#3
1
This is too long for a comment.
这对评论来说太长了。
It is rather unlikely that the columns are causing the problem. This could happen if one (or more) of the columns are really, really large objects. To get to 78 seconds, you need to be thinking in terms of many megabytes or gigabytes, although even 1 Gbyte might not take that long in many environments.
这些列不太可能引起问题。如果其中一个(或多个)列是非常非常非常大的对象，就会发生这种情况。要达到78秒，您需要考虑以兆字节或千兆字节为单位，尽管在许多环境中，即使是1兆字节也不会花那么长时间。
The use of the index versus the non-index is easy to explain. The first query is covered by the index, so the original data pages are not needed. The second query is not covered by the index. Because so many rows are being selected, all the data may need to be read, in order to find a matching row. This is an optimization to prevent thrashing. It might explain what is going on, although 78 seconds for loading a table into memory seems like a long time -- unless the rows are very wide.
索引与非索引的使用很容易解释。第一个查询由索引覆盖，因此不需要原始数据页。第二个查询不在索引中。因为选择了这么多行，所以可能需要读取所有数据，以便找到匹配的行。这是一种防止抖动的优化。它可能会解释发生了什么，尽管将表加载到内存中的78秒看起来很长——除非行非常宽。
Another possibility is that other operations are locking the table. In many environments, this would be the most likely culprit.
另一种可能是其他操作正在锁定表。在许多环境中，这可能是罪魁祸首。
Finally, if the queries were subtly different (such as one having an order by or the constant being enclosed in single quotes), then that might account for some difference.
最后，如果查询有细微的不同(比如一个查询的order by或常量被括在单引号中)，那么这可能会导致一些差异。
I would check the explain to see what is happening. Even searching through a table with a few million rows should not take 78 seconds.
我会检查一下解释，看看发生了什么。即使搜索一个包含数百万行的表，也不应该花费78秒。
#4
0
If Serial is VARCHAR, you have a typing problem. Put quotes around "123456".
如果串口是VARCHAR，你有一个打字问题。把引号“123456”。
PARTITION will not help that query.
分区不会帮助查询。
Please provide SHOW CREATE TABLE if you need to discuss this further.
如果需要进一步讨论，请提供SHOW CREATE TABLE。
#1
1
There is part answer for your question.
你的问题有部分答案。
https://*.com/a/3211164/2957840
https://*.com/a/3211164/2957840
But also, maybe you should consider partitioning your table: https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
但是，也许您应该考虑对表进行分区:https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
#2
1
Even limiting how many columns you need to read by a few will help. Just limit it even more and IF indexing a few more columns helps then go ahead but they'd need to be used in the WHERE clause.
甚至限制你需要阅读的专栏的数量也会有所帮助。只需要对它进行更多的限制，如果索引更多的列会有帮助，那么继续，但是它们需要在WHERE子句中使用。
#3
1
This is too long for a comment.
这对评论来说太长了。
It is rather unlikely that the columns are causing the problem. This could happen if one (or more) of the columns are really, really large objects. To get to 78 seconds, you need to be thinking in terms of many megabytes or gigabytes, although even 1 Gbyte might not take that long in many environments.
这些列不太可能引起问题。如果其中一个(或多个)列是非常非常非常大的对象，就会发生这种情况。要达到78秒，您需要考虑以兆字节或千兆字节为单位，尽管在许多环境中，即使是1兆字节也不会花那么长时间。
The use of the index versus the non-index is easy to explain. The first query is covered by the index, so the original data pages are not needed. The second query is not covered by the index. Because so many rows are being selected, all the data may need to be read, in order to find a matching row. This is an optimization to prevent thrashing. It might explain what is going on, although 78 seconds for loading a table into memory seems like a long time -- unless the rows are very wide.
索引与非索引的使用很容易解释。第一个查询由索引覆盖，因此不需要原始数据页。第二个查询不在索引中。因为选择了这么多行，所以可能需要读取所有数据，以便找到匹配的行。这是一种防止抖动的优化。它可能会解释发生了什么，尽管将表加载到内存中的78秒看起来很长——除非行非常宽。
Another possibility is that other operations are locking the table. In many environments, this would be the most likely culprit.
另一种可能是其他操作正在锁定表。在许多环境中，这可能是罪魁祸首。
Finally, if the queries were subtly different (such as one having an order by or the constant being enclosed in single quotes), then that might account for some difference.
最后，如果查询有细微的不同(比如一个查询的order by或常量被括在单引号中)，那么这可能会导致一些差异。
I would check the explain to see what is happening. Even searching through a table with a few million rows should not take 78 seconds.
我会检查一下解释，看看发生了什么。即使搜索一个包含数百万行的表，也不应该花费78秒。
#4
0
If Serial is VARCHAR, you have a typing problem. Put quotes around "123456".
如果串口是VARCHAR，你有一个打字问题。把引号“123456”。
PARTITION will not help that query.
分区不会帮助查询。
Please provide SHOW CREATE TABLE if you need to discuss this further.
如果需要进一步讨论，请提供SHOW CREATE TABLE。