mysql慢查詢之同一個字段做兩次排序的思考

mysql慢查詢這個東西我們可以分析出有問題的sql語句了,今天我們來看一篇關於mysql慢查詢之同一個字段做兩次排序的思考吧

線上業務使用到了ORM框架，發現了一個慢查詢sql，同一個字段做了兩次排序，導致產生了filesort。
ORM框架自帶的表結構如下：

CREATE TABLE `unopen` (
  `corp` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `invtype` tinyint(3) NOT NULL DEFAULT '1',
  `uid` int(10) NOT NULL DEFAULT '0',
  `username` char(32) NOT NULL DEFAULT '0',
  `fatid` int(10) NOT NULL DEFAULT '0',
  `fatname` char(32) NOT NULL DEFAULT '0',
  `invtitle` char(100) NOT NULL DEFAULT '',
  `invest` decimal(12,2) NOT NULL DEFAULT '0.00',
  `opened` decimal(12,2) unsigned NOT NULL DEFAULT '0.00',
  `unopened` decimal(12,2) NOT NULL DEFAULT '0.00',
  `doflag` tinyint(3) NOT NULL DEFAULT '0',
  `manual_opened` decimal(12,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`uid`,`corp`,`doflag`,`invtype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
看到這個表結構就會想到第一件事就想增加自增主鍵（有點強迫症的感覺），其實表不一定要一個自增整型的字段來做主鍵，只要是能保證唯一的字段都可以用來做主鍵，最好是整型。但主要還得看應用場景，只有等值查詢的列，或多列來創建單列主鍵或聯合主鍵，可能會更好，因為如果作為二級索引存在時，可能還會造成回表查詢，直接是主鍵可直接通過主鍵拿回數據。 回到正題,原sql是如下這樣的：

SELECT `i`.*, `v`.`type`, `v`.`status` FROM `V_STAT`.`unopen` AS `i`
 LEFT JOIN `V_USER`.`info` AS `v` ON `i`.`uid` = `v`.`uid` WHERE (`i`.`uid` > 0) AND (`i`.`corp` = '31') AND (`i`.`doflag` = '2') ORDER BY `uid` ASC, `uid` ASC, `corp` ASC LIMIT 50;
這個sql執行需要2.5s多,explain一看進行了filesort

+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+-----------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref           | rows    | Extra                       |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+-----------------------------+
|  1 | SIMPLE      | i     | range | PRIMARY       | PRIMARY | 4       | NULL          | 1610297 | Using where; Using filesort |
|  1 | SIMPLE      | v     | ref   | PRIMARY       | PRIMARY | 4       | V_STAT.i.uid |       1 |                             |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+-----------------------------+
根據表的結構及sql，都使用了索引字段查詢排序，且排序字段都是基表字段，應該不需要再排序了，對如何讓group by不產生sort可以參考下【mysql】創建索引時如何考慮order by查詢。在調整單引號時，發現這個sql出現了兩次uid的排序，而且沒有明確標明是哪個表的。意思應該是使用i.uid和v.uid進行排序，於是我就加上。

mysql> explain SELECT  `i`.*, `v`.`type`, `v`.`status` FROM `V_STAT`.`unopen` AS `i`  LEFT JOIN `V_USER`.`info` AS `v` ON `i`.`uid` = `v`.`uid` WHERE (`i`.`uid` > 0) AND (`i`.`corp` = 3) AND (`i`.`doflag` = 2) ORDER BY i.`uid` ASC, v.`uid` ASC, i.`corp` ASC LIMIT 50;
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref           | rows    | Extra                                        |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+----------------------------------------------+
|  1 | SIMPLE      | i     | range | PRIMARY       | PRIMARY | 4       | NULL          | 1610297 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | v     | ref   | PRIMARY       | PRIMARY | 4       | V_STAT.i.uid |       1 |                                              |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+----------------------------------------------+
發現了using temporary，當沒有指定排序表時，默認走i表的uid了。其實這裡已經很明顯，這個uid是不需要做兩次排序的，因為兩個表的uid是相同的，取出來時順序肯定是一樣的。果斷地刪除後面的uid 排序，並明確排序表為i，這裡只為增加可讀性。

mysql> explain SELECT  `i`.*, `v`.`type`, `v`.`status` FROM `V_STAT`.`unopen` AS `i`  LEFT JOIN `V_USER`.`info` AS `v` ON `i`.`uid` = `v`.`uid` WHERE (`i`.`uid` > 0) AND (`i`.`corp` = 3) AND (`i`.`doflag` = 2) ORDER BY i.`uid` ASC, i.`corp` ASC LIMIT 50;
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref           | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+-------------+
|  1 | SIMPLE      | i     | range | PRIMARY       | PRIMARY | 4       | NULL          | 1610297 | Using where |
|  1 | SIMPLE      | v     | ref   | PRIMARY       | PRIMARY | 4       | V_STAT.i.uid |       1 |             |
+----+-------------+-------+-------+---------------+---------+---------+---------------+---------+-------------+
filesort消失，查詢直接變為ms級。