MySQL性能，内部联接，如何避免使用临时和文件排序 [英] MySQL performance, inner join, how to avoid Using temporary and filesort

问题描述

我有一个表1和表2.

表1 PARTNUM-ID_BRAND partnum是主键 id_brand被编入索引"

Table 1 PARTNUM - ID_BRAND partnum is the primary key id_brand is "indexed"

表2 ID_BRAND-BRAND_NAME id_brand是主键 brand_name被编入索引"

Table 2 ID_BRAND - BRAND_NAME id_brand is the primary key brand_name is "indexed"

表1包含一百万条记录，表2包含1.000条记录.

The table 1 contains 1 million of records and the table 2 contains 1.000 records.

我正在尝试使用EXPLAIN优化某些查询，经过多次尝试，我已经走到了尽头.

I'm trying to optimize some query using EXPLAIN and after a lot of try I have reached a dead end.

EXPLAIN 
SELECT pm.partnum, pb.brand_name
FROM products_main AS pm 
LEFT JOIN products_brands AS pb ON pm.id_brand=pb.id_brand
ORDER BY pb.brand ASC 
LIMIT 0, 10

查询返回以下执行计划:

The query returns this execution plan:

ID, SELECT_TYPE, TABLE, TYPE, POSSIBLE_KEYS, KEY, KEY_LEN , REF, ROWS, EXTRA
1, SIMPLE, pm, range, PRIMARY, PRIMARY, 1, , 1000000, Using where; Using temporary; Using filesort
1, SIMPLE, pb, ref, PRIMARY, PRIMARY, 4, demo.pm.id_pbrand, 1,

MySQL查询优化器在执行计划中显示一个临时+文件排序. 如何避免这种情况?

The MySQL query optimizer shows a temporary + filesort in the execution plan. How can I avoid this?

"EVIL"位于 ORDER BY pb.brand ASC 中.通过该外部字段进行排序似乎是瓶颈..

The "EVIL" is in the ORDER BY pb.brand ASC. Ordering by that external field seems to be the bottleneck..

推荐答案

尝试用子查询替换联接. MySQL的优化器很烂；子查询通常比联接具有更好的性能.

Try replacing the join with a subquery. MySQL's optimizer kind of sucks; subqueries often give better performance than joins.

这篇关于MySQL性能，内部联接，如何避免使用临时和文件排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！