MySql JOIN性能随分组而下降 [英] MySql JOIN performance go down with group by
问题描述
我有这些表:
table "f" (26000 record)
+------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| idFascicolo | int(11) | NO | PRI | | |
| oggetto | varchar | NO |index| | |
+------------------+------------------+------+-----+---------+-------+
table "r" (22000 record)
+------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| idRichiedente | int(11) | NO | PRI | | |
| name | varchar | NO |index| | |
+------------------+------------------+------+-----+---------+-------+
table "fr" (32000 record)
+------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | | |
| idFascicolo | int(11) | NO |index| | FK |
| idRichiedente | int(11) | NO |index| | FK |
+------------------+------------------+------+-----+---------+-------+
这是我的选择
SELECT
f.idFascicolo,
f.oggetto,
r.richiedente
FROM fr
JOIN f ON (f.idFascicolo=fr.idFascicolo)
JOIN r ON (r.idRichiedente=fr.idRichiedente)
WHERE r.name LIKE '%string%'
在结果中,我希望每个f.idFascicolo仅看到1行(对于同一f.idFascicolo,我应该有"Rossi Mario"和"Rossi Marco"),我的新选择是:
in the result, I would like to see only 1 row per f.idFascicolo (I should have "Rossi Mario" and "Rossi Marco" for the same f.idFascicolo) , the my new select is:
SELECT
f.idFascicolo,
f.oggetto,
r.richiedente
FROM fr
JOIN f ON (f.idFascicolo=fr.idFascicolo)
JOIN r ON (r.idRichiedente=fr.idRichiedente)
WHERE r.name LIKE '%string%'
GROUP BY f.idFascicolo
此处,性能是从PhpMyAdmin中读取的:
here, the performance read from PhpMyAdmin:
0.0057 seconds: .. WHERE r.name LIKE '%string%'
0.0527 seconds: .. WHERE r.name LIKE '%string%' GROUP BY f.idFascicolo
0.0036 seconds: .. WHERE r.name LIKE 'string%' GROUP BY f.idFascicolo
我不明白慢查询的问题是GROUP BY还是LIKE'%string%'(我需要'%string%'..我找不到全文索引和MATCH的等效解决方案.反对)
I don't understand if the problem of the slow query is GROUP BY or LIKE '%string%'(i need '%string%' .. I can't find an equivalent solution with fulltext index and MATCH .. AGAINST)
这是解释:
+------+-------------+-------+------+-------------------------+---------------+---------+----------------------+-----------+---------------------------------------------+
| id | select type | table | type | possible keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+-------------------------+---------------+---------+----------------------+-----------+---------------------------------------------+
| 1 | simple | r | ALL | PRIMARY | NULL | NULL | NULL | 20925 |Using where; Using temporary; Using filesort |
+------+-------------+-------+------+-------------------------+---------------+---------+----------------------+-----------+---------------------------------------------+
| 1 | simple | fr | ref |idFascicolo,idRichiedente| idRichiedente | 4 | db.r.idRichiedente | 1 | |
+------+-------------+-------+------+-------------------------+---------------+---------+----------------------+-----------+---------------------------------------------+
| 1 | simple | f |eq_ref|PRIMARY | PRIMARY | 4 | db.fr.idFascicolo | 1 | |
+------+-------------+-------+------+-------------------------+---------------+---------+----------------------+-----------+---------------------------------------------+
推荐答案
您有两个潜在的性能问题.首先是GROUP BY
.这需要对数据进行排序,因此它必须读取所有数据并做很多工作.
You have two potential performance issues. First is the GROUP BY
. This requires sorting the data, so it has to read all the data and do a lot of work.
第二个是LIKE
.两者之间有根本的区别:
The second is the LIKE
. There is a fundamental difference between:
WHERE r.name LIKE '%string%'
和
WHERE r.name LIKE 'string%'
第二个可以使用r(name)
上的索引,因为like
模式不是不是以模式开头.
The second can use an index on r(name)
, because the like
pattern does not start with a pattern.
我不确定您的实际问题是什么.我不建议您按自己的方式使用GROUP BY
-因为您在SELECT
中具有未聚合的列.
I am not sure what your actual question is. I don't recommend doing using GROUP BY
the way you are using it -- because you have unaggregated columns in the SELECT
.
这篇关于MySql JOIN性能随分组而下降的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!