mysql order by 造成语句 执行计划中Using filesort,Using temporary相关语句的优化解决
mysql> explain select permission.* from t_rbac_permission permission inner JOIN t_rbac_acl acl on acl.PERMISSION_ID=permission.ID
where permission.menu=1 and acl.PRINCIPAL_TYPE=0 order by permission.create_date desc;
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+----------------------------------------------+
| 1 | SIMPLE | acl | ALL | FKE43AF088F9936F96 | NULL | NULL | NULL | 94 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | permission | eq_ref | PRIMARY | PRIMARY | 98 | bs_common.acl.PERMISSION_ID | 1 | Using where |
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)
explain select permission.* from t_rbac_permission permission inner JOIN t_rbac_acl acl
on acl.PERMISSION_ID=permission.ID where permission.menu=1 and acl.PRINCIPAL_TYPE=0 ;
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+-------------+
| 1 | SIMPLE | acl | ALL | FKE43AF088F9936F96 | NULL | NULL | NULL | 94 | Using where |
| 1 | SIMPLE | permission | eq_ref | PRIMARY | PRIMARY | 98 | bs_common.acl.PERMISSION_ID | 1 | Using where |
+----+-------------+------------+--------+--------------------+---------+---------+-----------------------------+------+-------------+
????? why 加上 order by permission.create_date desc 的影响???
首先查看相关字段是否加上索引如 order by的字段是否加上索引。加上索引之后单表查询看如何??
![](https://img2020.cnblogs.com/blog/1468187/202101/1468187-20210104182417101-1957318290.png)
ok 单表查询还是Using filesort. 查询相关资料进行组合索引试试看
ok,现在终结了,那么试试连接查询。各种测试结果如下:
例如:
![](https://img2020.cnblogs.com/blog/1468187/202101/1468187-20210104182754133-776882933.png)
增加索引后
![](https://img2020.cnblogs.com/blog/1468187/202101/1468187-20210104182848807-486685918.png)
效果如下
本文来自博客园,作者:飞龙在生,转载请注明原文链接:https://www.cnblogs.com/flzs/p/14231425.html