mysql> EXPLAIN SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM E_role_info INNER JOIN E_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY E_info.info_time DESC LIMIT 10;
+----+-------------+-------------+------+-------------------------------------------+-------------+---------+-------------------------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+-------------------------------------------+-------------+---------+-------------------------------+---------+---------------------------------+
| 1 | SIMPLE | E_role_info | ref | PRIMARY,idx_E_role_info_roleID,idx_roleID | idx_roleID | 3 | const | 2508851 | Using temporary; Using filesort |
| 1 | SIMPLE | E_info | ref | PRIMARY,idx_id_time | idx_id_time | 4 | news_data2.E_role_info.infoID | 1 | Using index |
+----+-------------+-------------+------+-------------------------------------------+-------------+---------+-------------------------------+---------+---------------------------------+
我所不清楚的是,该语句ORDER BY E_info.info_time 是按照E_info表中的info_time来排序,并没有要求按照E_role_info表中的任何字段排序,但为什么会在该表中的extra字段出现Using temporary; Using filesort ?
能否通过优化,消除这个filesort呢?
mysql> SET PROFILING = 1;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM E_role_info INNER JOIN E_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY E_info.info_time DESC LIMIT 10;
+----------+---------------------+--------+---------+
| infoID | info_time | roleID | related |
+----------+---------------------+--------+---------+
| 42880323 | 2009-04-18 08:58:56 | 413382 | 0 |
| 42880311 | 2009-04-18 08:58:24 | 413382 | 0 |
| 42880353 | 2009-04-18 08:58:21 | 413382 | 0 |
| 42880347 | 2009-04-18 08:58:21 | 413382 | 0 |
| 42880348 | 2009-04-18 08:58:21 | 413382 | 0 |
| 42880351 | 2009-04-18 08:58:20 | 413382 | 0 |
| 42880340 | 2009-04-18 08:57:59 | 413382 | 0 |
| 42880288 | 2009-04-18 08:57:45 | 413382 | 0 |
| 42880314 | 2009-04-18 08:57:31 | 413382 | 0 |
| 42880306 | 2009-04-18 08:57:31 | 413382 | 0 |
+----------+---------------------+--------+---------+
10 rows in set (24.82 sec)mysql> SHOW PROFILE;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| (initialization) | 0.000002 |
| checking query cache for query | 0.000079 |
| Opening tables | 0.000012 |
| System lock | 0.000006 |
| Table lock | 0.000007 |
| init | 0.000023 |
| optimizing | 0.000014 |
| statistics | 0.0000900 |
| preparing | 0.000018 |
| Creating tmp table | 0.000026 |
| executing | 0.000003 |
| Copying to tmp table | 6.206605 |
| converting HEAP to MyISAM | 0.101729 |
| Copying to tmp table on disk | 16.788693 |
| Sorting result | 1.722166 |
| Sending data | 0.000234 |
| end | 0.000003 |
| removing tmp table | 0.012298 |
| end | 0.000007 |
| query end | 0.000002 |
| freeing items | 0.00001 |
| closing tables | 0.000006 |
| logging slow query | 0.000002 |
+--------------------------------+-----------+
23 rows in set (0.00 sec)
mysql> SET PROFILING = 1;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT SQL_NO_CACHE STRAIGHT_JOIN E_info.infoID, E_info.info_time, E_role_info.roleID, E_role_info.related FROM E_role_info INNER JOIN E_info ON E_info.infoID = E_role_info.infoID WHERE E_role_info.roleID = 413382 ORDER BY E_info.info_time DESC LIMIT 10;
+----------+---------------------+--------+---------+
| infoID | info_time | roleID | related |
+----------+---------------------+--------+---------+
| 42880323 | 2009-04-18 08:58:56 | 413382 | 0 |
| 42880311 | 2009-04-18 08:58:24 | 413382 | 0 |
| 42880353 | 2009-04-18 08:58:21 | 413382 | 0 |
| 42880347 | 2009-04-18 08:58:21 | 413382 | 0 |
| 42880348 | 2009-04-18 08:58:21 | 413382 | 0 |
| 42880351 | 2009-04-18 08:58:20 | 413382 | 0 |
| 42880340 | 2009-04-18 08:57:59 | 413382 | 0 |
| 42880288 | 2009-04-18 08:57:45 | 413382 | 0 |
| 42880314 | 2009-04-18 08:57:31 | 413382 | 0 |
| 42880306 | 2009-04-18 08:57:31 | 413382 | 0 |
+----------+---------------------+--------+---------+
10 rows in set (24.82 sec)mysql> SHOW PROFILE;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| (initialization) | 0.000002 |
| checking query cache for query | 0.000079 |
| Opening tables | 0.000012 |
| System lock | 0.000006 |
| Table lock | 0.000007 |
| init | 0.000023 |
| optimizing | 0.000014 |
| statistics | 0.0000900 |
| preparing | 0.000018 |
| Creating tmp table | 0.000026 |
| executing | 0.000003 |
| Copying to tmp table | 6.206605 |
| converting HEAP to MyISAM | 0.101729 |
| Copying to tmp table on disk | 16.788693 |
| Sorting result | 1.722166 |
| Sending data | 0.000234 |
| end | 0.000003 |
| removing tmp table | 0.012298 |
| end | 0.000007 |
| query end | 0.000002 |
| freeing items | 0.00001 |
| closing tables | 0.000006 |
| logging slow query | 0.000002 |
+--------------------------------+-----------+
23 rows in set (0.00 sec)
ORDER BY E_info.info_time 这种情况下,在 where 之后,做 join ,JOIN完后的数据是没有索引可利用。这时MySQL只能进行全部记录集中的排序。这么多记录(2508851), 不可以在内存中完成,因此会用到临时表进行排序。