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的字段是否加上索引。加上索引之后单表查询看如何？？

ok 单表查询还是Using filesort. 查询相关资料进行组合索引试试看

ok，现在终结了，那么试试连接查询。各种测试结果如下：