复杂的查询需要太多时间转移 [英] complex query takes too much time transferring

问题描述

下面的查询很慢，我不明白为什么.我有所有的 id 作为索引(一些主要的).

the following query is very slow, I don't understand why. I have all id as indexes (some primary).

SELECT r.name as tool, r.url url ,r.id_tool recId, count(*) as count, r.source as source, 
     group_concat(t.name) as instrument
FROM tools r 
INNER JOIN
 instruments_tools ifr
ON ifr.id_tool = r.id_tool
INNER JOIN
 instrument t
ON t.id= ifr.id_instrument
WHERE t.id IN (433,37,362) AND t.source IN (1,2,3)
GROUP BY r.id_tool
ORDER BY count desc,rand() limit 10;

在本地安装 Wampserver 时，我在传输数据时遇到严重问题.与海蒂一起，我看到两个 2 和 6 秒的发送数据".在共享服务器上，这是我看到的重要部分:

Locally on a Wampserver installation I have serious issues with transferring data. With Heidi I see two "Sending Data" of 2 resp 6 seconds. On a shared server, this is the important part I see:

| statistics                     | 0.079963 |
| preparing                      | 0.000028 |
| Creating tmp table             | 0.000037 |
| executing                      | 0.000005 |
| Copying to tmp table           | 7.963576 |
| converting HEAP to MyISAM      | 0.015790 |
| Copying to tmp table on disk   | 5.383739 |
| Creating sort index            | 0.015143 |
| Copying to group table         | 0.023708 |
| converting HEAP to MyISAM      | 0.014513 |
| Copying to group table         | 0.099595 |
| Sorting result                 | 0.034256 |

考虑到我想改进查询(参见 LIMIT)或删除 rand() 并添加权重，我有点担心我做错了什么.

Considering that I'd like to improve the query (see LIMIT) or remove rand() and add weights, I'm a bit afraid I'm doing something very wrong.

附加信息:工具表有 500.000 行，而仪器大约有 6000 行.instruments_tools 大约有 300 行.查询是为了找到我可以用我拥有的工具制作哪个工具(通过检查 t.id IN(工具 ID).Group_concat(t.name) 是一种知道选择了哪个工具的方法.

Additional info: The tools table is 500.000 rows big, while the instruments around 6000. instruments_tools is around 3M rows. The query is to find which tool I can make with the instruments I have (by checking t.id IN(id of instruments). Group_concat(t.name) is a way to know which instrument is selected.

查询说明:

+----+-------------+-------+--------+-------------------------+---------------+-------- -+----------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys           | key           | key_len     | ref                        | rows | Extra                                        |
+----+-------------+-------+--------+-------------------------+---------------+---------+----------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | t     | range  | PRIMARY                 | PRIMARY       | 4       | NULL                       |    3 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ifr   | ref    | id_tool,id_instrument | id_instrument | 5       | mydb2.t.id          |  374 | Using where                                  |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY                 | PRIMARY       | 4       | mydb2.ifr.id_tool |    1 |                                              |
+----+-------------+-------+--------+-------------------------+---------------+---------+----------------------------+------+----------------------------------------------+

推荐答案

您需要在交集表上建立复合索引:

You need a compound index on the intersection table:

ALTER TABLE instruments_tools ADD KEY (id_instrument, id_tool);

该索引中列的顺序很重要！

The order of columns in that index is important!

您希望连接从仪器表开始，然后根据 id_instrument 在复合索引中查找匹配的索引条目.然后一旦找到该索引条目，它就会免费获得相关的 id_tool.所以它根本不需要读取instrument_tools表，它只需要读取索引条目.这应该会在您的instruments_tools 表的EXPLAIN 中给出使用索引"注释.

What you're hoping for is that the joins will start with the instrument table, then look up the matching index entry in the compound index based on id_instrument. Then once it finds that index entry, it has the related id_tool for free. So it doesn't have to read the instrument_tools table at all, it only need to read the index entry. That should give the "Using index" comment in your EXPLAIN for the instruments_tools table.

这应该会有所帮助，但您无法避免临时表和文件排序，因为您分组和排序的列不能使用索引.

That should help, but you can't avoid the temp table and filesort, because of the columns you're grouping by and sorting by cannot make use of an index.

您可以尝试通过增加可用于临时表的内存大小来使 MySQL 避免将临时表写入磁盘:

You can try to make MySQL avoid writing the temp table to disk by increasing the size of memory it can use for temporary tables:

mysql> SET GLOBAL tmp_table_size = 256*1024*1024;      -- 256MB
mysql> SET GLOBAL max_heap_table_size = 256*1024*1024; -- 256MB

那个数字只是一个例子.我不知道在您的情况下临时表必须有多大.

That figure is just an example. I have no idea how large it would have to be for the temp table in your case.

这篇关于复杂的查询需要太多时间转移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！