I have a table with 10 columns, It contains about 8 million rows. I'm doing a statistically job with this table. The problem is when I run more time, when id grows, the select query slow more.
Here is the query:
我有一个包含10列的表，它包含大约800万行。我正在用这张桌子做统计工作。问题是当我运行更多时间时，当id增长时，选择查询的速度会更慢。这是查询：
select * from transaction
where id > :pointer
AND col_a = :col_a
AND col_b >= :from
order by id ASC limit 5000
Both of 3 fields in the query have been created index.
After each loop, I will run query again with new pointer value, the value of pointer is id of last row of previous result set, I don't use OFFSET. Finally, I took half of day to run the script with this query, too long.
So how can I fix this performance problem.
查询中的3个字段都已创建索引。在每次循环之后，我将使用新的指针值再次运行查询，指针的值是前一个结果集的最后一行的id，我不使用OFFSET。最后，我花了半天时间用这个查询运行脚本太久了。那么我该如何解决这个性能问题呢。
1 个解决方案
#1
1
Both of 3 fields in the query have been created index
查询中的3个字段都已创建索引
Mysql can use only one index per table in a query. If you created separate indexes for each field, then MySQL can use only one of them to speed up your query, not all 3.
Mysql在查询中每个表只能使用一个索引。如果为每个字段创建了单独的索引，那么MySQL只能使用其中一个来加速查询，而不是全部3。
I would create a multi-column index on id, col_a, col_b fields (in this order). This way a single index can be used to satisfy all 3 conditions in the where criteria and the order by as well.
我会在id，col_a，col_b字段上创建一个多列索引（按此顺序）。这样，单个索引可用于满足where条件和顺序中的所有3个条件。
After each loop, I will run query again with new pointer value
在每个循环之后，我将使用新的指针值再次运行查询
Your code suggests that you use some kind of a parametrised query, but we cannot determine if it is a proper MySQL prepared statement. If it is not, then consider using MySQL prepared statement for this process.
您的代码建议您使用某种参数化查询，但我们无法确定它是否是正确的MySQL预处理语句。如果不是，那么请考虑使用MySQL预处理语句进行此过程。
Prepare the query before the loop and then in the loop just adjust the parameters and then execute the prepared statement again. This way MySQL will parse the query only once, not each time the code loops.
在循环之前准备查询，然后在循环中调整参数然后再次执行准备好的语句。这样MySQL只会解析一次查询，而不是每次代码循环。
#1
1
Both of 3 fields in the query have been created index
查询中的3个字段都已创建索引
Mysql can use only one index per table in a query. If you created separate indexes for each field, then MySQL can use only one of them to speed up your query, not all 3.
Mysql在查询中每个表只能使用一个索引。如果为每个字段创建了单独的索引，那么MySQL只能使用其中一个来加速查询，而不是全部3。
I would create a multi-column index on id, col_a, col_b fields (in this order). This way a single index can be used to satisfy all 3 conditions in the where criteria and the order by as well.
我会在id，col_a，col_b字段上创建一个多列索引（按此顺序）。这样，单个索引可用于满足where条件和顺序中的所有3个条件。
After each loop, I will run query again with new pointer value
在每个循环之后，我将使用新的指针值再次运行查询
Your code suggests that you use some kind of a parametrised query, but we cannot determine if it is a proper MySQL prepared statement. If it is not, then consider using MySQL prepared statement for this process.
您的代码建议您使用某种参数化查询，但我们无法确定它是否是正确的MySQL预处理语句。如果不是，那么请考虑使用MySQL预处理语句进行此过程。
Prepare the query before the loop and then in the loop just adjust the parameters and then execute the prepared statement again. This way MySQL will parse the query only once, not each time the code loops.
在循环之前准备查询，然后在循环中调整参数然后再次执行准备好的语句。这样MySQL只会解析一次查询，而不是每次代码循环。