MySQL 5.6中的升序、降序混合排序的理解是否正确
需求对一张表2个字段排序取50行记录，其中money字段是降序，mid字段是升序。
环境
mysql 5.6
表结构
root@localhost>show create table info;
CREATE TABLE `info` (
`mid` int(11) unsigned NOT NULL,
`sitemid` varchar(50) NOT NULL,
`sid` int(10) unsigned NOT NULL DEFAULT '0' ,
`exp` int(11) NOT NULL DEFAULT '0' ,
`money` int(10) NOT NULL DEFAULT '0' ,
`level` smallint(5) NOT NULL DEFAULT '1',
`wintimes` int(11) NOT NULL DEFAULT '0',
`losetimes` int(11) NOT NULL DEFAULT '0',
`matchstatus` smallint(3) unsigned NOT NULL DEFAULT '0',
`diamond` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`mid`),
KEY `idx_money` (`money`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 原始SQL
root@localhost> explain SELECT mid,money FROM info ORDER BY money DESC,mid ASC LIMIT 50;
+----+-------------+------+-------+---------------+-----------+---------+------+---------+-----------------------------+
| id | select_type | table| type| possible_keys | key | key_len | ref| rows | Extra |
+----+-------------+------+-------+---------------+-----------+---------+------+---------+-----------------------------+
|1 | SIMPLE | info | index | NULL | idx_money | 4 | NULL | 1348933 | Using index; Using filesort |
+----+-------------+------+-------+---------------+-----------+---------+------+---------+-----------------------------+
root@localhost> SELECT mid,money FROM info ORDER BY money desc,mid ASC LIMIT 50;
+-----------+-----------+
| mid | money |
+-----------+-----------+
| 127781800 | 243206203 |
| 159971400 | 212759136 |
..........略.............
|11413400 | 7997950 |
| 129845800 | 7897523 |
+-----------+-----------+
50 rows in set (0.34 sec)子查询
root@localhost> explain select mid,money from (SELECT mid,money FROM info ORDER BY money DESC LIMIT 50) AS t ORDER BY midASC;
+----+-------------+------------+-------+---------------+-----------+---------+------+---------+----------------+
| id | select_type | table | type| possible_keys | key | key_len | ref| rows | Extra |
+----+-------------+------------+-------+---------------+-----------+---------+------+---------+----------------+
|1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 50 | Using filesort |
|2 | DERIVED | info | index | NULL | idx_money | 4 | NULL | 1348933 | Using index |
+----+-------------+------------+-------+---------------+-----------+---------+------+---------+----------------+
root@localhost> SELECT * from (SELECT mid,money FROM info ORDER BY money desc LIMIT 50) AS t ORDER BY mid ASC;
+-----------+-----------+
| mid | money |
+-----------+-----------+
| 151100 |11159367 |
| 1406900 | 9697046 |
..........略.............
| 161220000 |32384430 |
| 161364600 |29029832 |
+-----------+-----------+
50 rows in set (0.00 sec)总结
[*]原始SQL虽然使用了money索引，但是升序排列时对全表所有记录进行了filesort，最终取50行。
[*]子查询SQL首先利用money索引取50行，升序排列时仅仅对50行记录进行filesort，IO操作更少，效率更优。
是的，这种情况下，改用子查询方式做优化的思路很好
页:
[1]