问题描述
我有一个表1和表2.
表1PARTNUM-ID_BRANDpartnum是主键id_brand被编入索引"
Table 1PARTNUM - ID_BRANDpartnum is the primary keyid_brand is "indexed"
表2ID_BRAND-BRAND_NAMEid_brand是主键brand_name被编入索引"
Table 2ID_BRAND - BRAND_NAMEid_brand is the primary keybrand_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性能,内部联接,如何避免使用临时和文件排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!