在对索引列进行排序时，如何避免使用大型表的MySQL中糟糕的执行计划选择？
在对索引列进行排序时，如何避免使用大型表的MySQL中糟糕的执行计划选择？
提问于 2015-01-15 19:59:10
我在InnoDB 5.5上有三个MySQL表：
user_company_assignment (1.7 million rows)
- id (int)
- company_id (int) FKEY (company.id)
- user_id (int) FKEY (user.id)
company (200k rows)
- id (int)
- name (varchar(255)) - Indexed
user (2 million rows)
- id (int)
- name (varchar(255)) - Indexed连接所有表并按一个索引varchar字段进行排序具有良好的执行计划，且执行速度快(~10 is )：
mysql> explain select user_company_assignment.id, company.name
from user_company_assignment
inner join company
on user_company_assignment.company_id = company.id
inner join user
on user_company_assignment.user_id = user.id
order by company.name asc limit 5;
+----+-------------+------------+--------+-----------------------------+-------------+---------+------------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-----------------------------+-------------+---------+------------------------------------------+------+-------------+
| 1 | SIMPLE | company | index | PRIMARY | company_name | 767 | NULL | 1 | Using index |
| 1 | SIMPLE | user_company_assignment | ref | fk_company_id,fk_user_id | fk_company_id | 4 | company.id | 4 | |
| 1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 4 | user_company_assignment.user_id | 1 | Using index |
+----+-------------+------------+--------+-----------------------------+-------------+---------+------------------------------------------+------+-------------+
3 rows in set (0.09 sec)但是，当我加入两个表并按它们的索引名称进行排序时，它会选择一个非常慢的执行计划，并且查询需要超过一分钟才能完成：
mysql> explain select user_company_assignment.id, company.name
from user_company_assignment
inner join company
on user_company_assignment.company_id = company.id
inner join user
on user_company_assignment.user_id = user.id
order by company.name asc, user.name asc limit 5;
+----+-------------+------------+--------+-----------------------------+-------------+---------+------------------------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-----------------------------+-------------+---------+------------------------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | company | index | PRIMARY | company_name | 767 | NULL | 180792 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | user_company_assignment | ref | fk_company_id,fk_user_id | fk_company_id | 4 | company.id | 4 | |
| 1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 4 | user_company_assignment.user_id | 1 | |
+----+-------------+------------+--------+-----------------------------+-------------+---------+------------------------------------------+--------+----------------------------------------------+
3 rows in set (0.11 sec)是否没有办法获得将在这两个表列上使用索引的执行计划？我不认为它会像按一列排序时那样快，但我觉得应该稍微慢一点。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-01-17 00:06:25
你可以用迂回的方式做你想做的事。
不幸的是，在InnoDB中无法创建包含来自不同表的列的键。因此，如果您按照不同表中的列进行排序，然后使用ORDER BY ... LIMIT来切断它们，那么ORDER BY ... LIMIT将不得不选择每个匹配的行。这样做之后，它就可以对磁盘上的大结果集进行排序，最后从您想要的顶部选择几个结果集。
解决方案是将匹配的结果集限制在可管理的范围内，从而减少MySQL必须操作的行数。
由于您知道最多将有5个结果总数，所以您可以选择前5个公司ID：
SELECT company.id
FROM company
ORDER BY company.name ASC
LIMIT 5然后只使用这些进行连接，最后按公司和用户名进行排序：
SELECT user_company_assignment.id, top_companies.name
FROM user_company_assignment
INNER JOIN (
SELECT company.name, company.id
FROM company
ORDER BY company.name ASC
LIMIT 5
) top_companies
ON user_company_assignment.id = top_companies.id
INNER JOIN user
ON user_company_assignment.user_id = user.id
ORDER BY top_companies.name ASC, user.name ASC
LIMIT 5下面是一个SQL小提琴，演示了运行的查询。我投入了少量的数据来测试。
这仍然会吸引相当数量的行来进行文件处理，因为您将加入5家公司的所有员工，但这将使您的查询的运行时间减少几个数量级。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27971955
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号