当同时发送另一个查询时，MySQL查询速度变慢 [英] MySQL query slows down when another query is concurently sent

问题描述

我正在使用一个InnoDB表，该表当前有16m条记录.当我使用MySQL Workbench运行以下查询时，它将在大约14秒后返回:

I am using an InnoDB table that currently has 16m of records. When I run the following query by using MySQL Workbench, it returns in about 14 seconds:

SELECT countryCode AS label, SUM(count) AS value 
FROM AnalyticsPieCharts 
GROUP BY label 
ORDER BY value DESC;   

当我在另一台计算机上运行同一查询时，第一个查询正在运行时，两者都将在约23-24秒内返回.

When I run the same query from a different computer, while the first one is running, both return in about 23-24 seconds.

我没有创建任何索引(主键除外)或放置事务规则. 我正在使用Amazon RDS.

I haven't created any indexes (except of the primary key) or put transaction rules. I am using Amazon RDS.

您对可能是什么问题有任何线索吗?

Do you have any clue on what could be the problem?

推荐答案

我设法自己解决了这个问题.我在数据中找到了一种模式，该模式使我可以将数据拆分为唯一的块，并在以后需要时对块进行计数.这样，我将数据库的大小减少了300倍.现在查询将在1秒内返回，并且非分块表(大表)的总数现在为1.2亿条记录.

I managed to solve it by myself. I found a pattern in the data, that allowed me to split the data into unique chunks and count the chunks later, when needed. This way I reduced the size of the database by 300 times. Now the queries return in 1 second and the total amount of the non-chunked table (the big table) is now 120mln records.

这篇关于当同时发送另一个查询时，MySQL查询速度变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！