需求
对一张表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
- 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 mid ASC;
- +----+-------------+------------+-------+---------------+-----------+---------+------+---------+----------------+
- | 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操作更少，效率更优。
|
