首页 > 代码库 > 一次mysql order by 优化案例
一次mysql order by 优化案例
desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM wikiuseractionlog Where CreateDate>‘0001-01-01 00:00:00 ‘ and ActionType in(10,9,19,20) ORDER BY CreateDate DESC limit 93060,20;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+------------------------+------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | wikiuseractionlog | ALL | idx_date_type,idx_date | NULL | NULL | NULL | 91859 | Using where; Using filesort
看执行计划，没有用上索引，虽然我建了一个复合索引idx_date_type(createdate,actiontype)和一个列索引idx_date(createdate)，要使上面的语句走索引只要有下面几点：
1，需要将日期‘0001-01-01 00：00：00’改成2014-01-01 00:00:00 (2013以下都不行，不知道为什么)
2，需将createdate和actiontype做复合索引！一般情况下order by的字段如果要走索引优化，就需要将where条件下条件字段与order by的字段做联合索引！
3，Limit limit 93060,20 之前一直以后这句Limit会影响扫描的行数！不管有没索引都必须扫描93060行数据！实际不然！只要用上了索引，便只是取数据了！
mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM wikiuseractionlog ORDER BY CreateDate;
+----+-------------+-------------------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | wikiuseractionlog | ALL | NULL | NULL | NULL | NULL | 95220 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)
mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM wikiuseractionlog Where CreateDate>‘2014-01-01 00:00:00 ‘ ORDER BY CreateDate DESC limit 93060,20;
+----+-------------+-------------------+-------+------------------------+---------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+-------+------------------------+---------------+---------+------+------+-------------+
| 1 | SIMPLE | wikiuseractionlog | range | idx_date_type,idx_date | idx_date_type | 8 | NULL | 3737 | Using where
此种情况下虽然有单独的索引idx_date(createdate)，但发现依旧用不上索引，原因是order by的字段也必须出现在where 条件里，此处的原因大概也就是，排序操作是在最后的，首先提取数据的时候，如果where条件里的字段有索引，在提取出来的时候就已经做了排序操作了，此后再order by的时候就不用filesort了，不然则，在最后提取出来的数据里order by的时候，自然就要做filesort了！！
mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM wikiuseractionlog Where ActionType in(10,9,19,20) ORDER BY CreateDate;
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | wikiuseractionlog | ALL | idx_type_date | NULL | NULL | NULL | 90747 | Using where; Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)
上面的例子用不上索引！下面的则可以！将ActionType in(10,9,19,20) 改成or 也不行！
mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM wikiuseractionlog Where ActionType =‘10‘ ORDER BY CreateDate;
+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+-------------+
| 1 | SIMPLE | wikiuseractionlog | ref | idx_type_date | idx_type_date | 3 | const | 45373 | Using where |
+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+-------------+
1 row in set (0.00 sec)
本文出自 “夫臣” 博客，请务必保留此出处http://fucheng.blog.51cto.com/2404495/1441534
一次mysql order by 优化案例