线上业务使用到了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级。
[mysql慢查询日志]mysql慢查询之同一个字段做两次排序的思考
http://m.bbyears.com/shujuku/113601.html
推荐访问:mysql慢查询优化 mysql慢查询日志分析