问题：同一个sql执行倒序和正序性能差别很大，测试如下：
CREATE TABLE `tb_temp` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`col1` varchar(32) NOT NULL,
`col2` varchar(255) NOT NULL,
`col3` varchar(64) NOT NULL DEFAULT '0',
`col4` varchar(255) DEFAULT NULL,
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_col1_col2` (`col1`(20),`col2`(100)),
KEY `idx_time` (`gmt_modified`)
) ENGINE=InnoDB AUTO_INCREMENT=31853378 DEFAULT CHARSET=utf8mb4;
性能测试
SELECT id, col1, col2, col4, gmt_create FROM tb_temp WHERE col1 = 'fg789f70' AND col3 = '0' ORDER BY id desc limit 10;
10 rows in set (0.00 sec)
SELECT id, col1, col2, col4, gmt_create FROM tb_temp WHERE col1 = 'fg789f70' AND col3 = '0' ORDER BY id asc limit 10;
10 rows in set (5.03 sec)
SELECT id, col1, col2, col4, gmt_create FROM tb_temp WHERE col1 = 'fg789f70' AND col3 = '0' ORDER BY id desc;
168023 rows in set (0.33 sec)
SELECT id, col1, col2, col4, gmt_create FROM tb_temp WHERE col1 = 'fg789f70' AND col3 = '0' ORDER BY id asc;
168023 rows in set (0.36 sec)
desc SELECT id, col1, col2, col4, gmt_create FROM tb_temp WHERE col1 = 'fg789f70' AND col3 = '0' ORDER BY id desc;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | tb_temp | NULL | ref | idx_col1_col2 | PRIMARY,idx_col1_col2 | 62 | const | 337324 | 10.00 | Using where; Using filesort |
+
1 row in set, 1 warning (0.00 sec)
desc SELECT id, col1, col2, col4, gmt_create FROM tb_temp WHERE col1 = 'fg789f70' AND col3 = '0' ORDER BY id asc;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | tb_temp | NULL | ref | idx_col1_col2 | PRIMARY,idx_col1_col2 | 62 | const | 337324 | 10.00 | Using where; Using filesort |
+
1 row in set, 1 warning (0.00 sec)
desc SELECT id, col1, col2, col4, gmt_create FROM tb_temp WHERE col1 = 'fg789f70' AND col3 = '0' ORDER BY id desc limit 10;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | tb_temp | NULL | index | idx_col1_col2 | PRIMARY | 8 | NULL | 392 | 2.65 | Using where; Backward index scan |
+
desc SELECT id, col1, col2, col4, gmt_create FROM tb_temp WHERE col1 = 'fg789f70' AND col3 = '0' ORDER BY id asc limit 10;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | tb_temp | NULL | index | idx_col1_col2 | PRIMARY | 8 | NULL | 392 | 2.65 | Using where |
+
#倒序：
-> Limit: 10 row(s) (actual time=0.050..0.072 rows=10 loops=1)
-> Filter: ((tb_temp.col1 = 'fg789f70') and (tb_temp.col3 = '0')) (cost=17.88 rows=10) (actual time=0.047..0.061 rows=10 loops=1)
-> Index scan on tb_temp using PRIMARY (reverse) (cost=17.88 rows=392) (actual time=0.045..0.050 rows=10 loops=1)
#正序：
-> Limit: 10 row(s) (actual time=21865.328..21865.360 rows=10 loops=1)
-> Filter: ((tb_temp.col1 = 'fg789f70') and (tb_temp.col3 = '0')) (cost=17.88 rows=10) (actual time=21865.326..21865.349 rows=10 loops=1)
-> Index scan on tb_temp using PRIMARY (cost=17.88 rows=392) (actual time=0.761..14687.031 rows=13815686 loops=1
解释
从执行计划可以看出有limit限制的sql选择了主键扫描，基于不同排序规则差别在于：
这个测试表的单行数据大小约100B，总1000W数据两层索引树就能存下，而select字句的查询列超过了max_length_for_sort_data的长度，所以mysql选择了代价较小的扫主键的方式避免filesort，
而 col1 = 'fg789f70'的数据col3列全部为0并且id集中分布在索引树右侧，desc下从右开始扫描，根节点和叶节点扫面的数据页相对较少，并且8.0还使用了Backward index scan(索引倒序)做了进一步优化，
所以在主键扫描是desc较快。
有些应用框架默认会设置SET SQL_SELECT_LIMIT从而限制返回的行数，导致实际执行sql时没有选择合适的索引而直接扫主键；
对于上述filesort的消除，只需要建立一个col1列的独立索引即可，这时where条件检索col1时本身主键id就是asc有序的，所以不需要再内存排序.
