MySQL获取时间优化 [英] MySQL fetch time optimization

问题描述

我有一个带有200万个寄存器的表，但是它将很快增长.基本上，该表包含具有各个描述符的图像的兴趣点.当我尝试执行选择在空间上靠近查询点的点的查询时，总执行时间会花费很长时间.更准确地说，持续时间/提取= 0.484秒/27.441秒.而且查询非常简单，仅返回〜17000行.

我的查询是:

SELECT fp.fingerprint_id, fp.coord_x, fp.coord_y, fp.angle,
fp.desc1, fp.desc2, fp.desc3, fp.desc4, fp.desc5, fp.desc6, fp.desc7, fp.desc8, fp.desc9, fp.desc10,
fp.desc11, fp.desc12, fp.desc13, fp.desc14, fp.desc15, fp.desc16, fp.desc17, fp.desc18, fp.desc19,
fp.desc20, fp.desc21, fp.desc22, fp.desc23, fp.desc24, fp.desc25, fp.desc26, fp.desc27, fp.desc28,
fp.desc29, fp.desc30, fp.desc31, fp.desc32
FROM fingerprint fp 
WHERE 
fp.is_strong_point = 1 AND 
(coord_x BETWEEN 193-40 AND 193+40) AND (coord_y BETWEEN 49-15 AND 49+15 ) 
LIMIT 1,1000000;

这就是我所做的.

1. 我尝试在my.ini中更改key_buffer_size，但是没有看到太多更改.
2. 此外，我尝试将coord_x和coord_y设置为索引，但是查询时间变慢了.
3. 该表按coord_x字段的范围划分，这给了我更好的结果.

如何减少提取时间?是否可以将其减少到毫秒?

解决方案

如果我是对的，查询的速度确实很快，但是很慢的是从数据库中获取数据.从您的存储中加载170000个结果需要27秒.

您似乎使用了错误的数据库类型.尝试将表从一个数据库引擎切换到另一个.

要获得最大速度，可以使用MEMORY引擎.唯一的缺点是，如果必须对其进行动态更改，则必须将该表的副本存储在另一个引擎中，并且在进行任何更改之后，必须重新加载差异或整个表.

此外，您还必须制作一个脚本，以便在重新启动服务器时触发该脚本，以便在启动mysql服务器时加载内存表

在此处查看文档

oI have a table with 2 millions of registers, but it will grow much more soon. Basically this table contains points of interest of an image with respective descriptors. When I'm trying to execute query that selects points that are spatially near to the query points, total execution time takes too long. More precisely Duration / Fetch = 0.484 sec / 27.441 sec. And the query is quite simple, which returns only ~17000 rows.

My query is:

SELECT fp.fingerprint_id, fp.coord_x, fp.coord_y, fp.angle,
fp.desc1, fp.desc2, fp.desc3, fp.desc4, fp.desc5, fp.desc6, fp.desc7, fp.desc8, fp.desc9, fp.desc10,
fp.desc11, fp.desc12, fp.desc13, fp.desc14, fp.desc15, fp.desc16, fp.desc17, fp.desc18, fp.desc19,
fp.desc20, fp.desc21, fp.desc22, fp.desc23, fp.desc24, fp.desc25, fp.desc26, fp.desc27, fp.desc28,
fp.desc29, fp.desc30, fp.desc31, fp.desc32
FROM fingerprint fp 
WHERE 
fp.is_strong_point = 1 AND 
(coord_x BETWEEN 193-40 AND 193+40) AND (coord_y BETWEEN 49-15 AND 49+15 ) 
LIMIT 1,1000000;

That is what I've done.

1. I've tried to change key_buffer_size in my.ini, but didn't see much changes.
2. In addition I've tried to set coord_x and coord_y as indexes, but query time became slower.
3. The table is partitioned by range of coord_x field, which gave me better results.

How I can reduce the Fetch time? Is it possible to reduce it to milliseconds?

解决方案

If i am right the query is really fast but what is slow is the fetching of the data from your database. It takes 27 seconds to load the 170000 results from your storage.

It looks like you use the wrong database type. Try switching the table from one database engine to another.

For maximum speed you can use the MEMORY engine. The only drawback would be that you would have to store a copy of that table in another engine if you have to do dynamic changes to it and after any change you would have to reload the differences or the entire table.

Also you would have to make a script that fires when you restart your server so that your memory table would be loaded on startup of your mysql server

See here for the doc

这篇关于MySQL获取时间优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！