今天这篇主要讲order by 语句中的多个字段asc desc的问题。mysql5中，索引存储的排序方式是ASC的，没有DESC的索引。现在能够理解为啥order by 默认是按照ASC来排序的了吧?虽然索引是ASC的，但是也可以反向进行检索，就相当于DESC了。如果您在ORDER BY 语句中使用了 DESC排序，mysql确实会反向进行检索。在理论上，反向检索与正向检索的速度一样的快。但是在某些操作系统上面，并不支持反向的read-ahead预读，所以反向检索会略慢。由于设计的原因，在myisam引擎中，反向的检索速度比正向检索要慢得多。如果ORDER BY 子句中同时出现ASC和DESC，会是怎样的情况呢?
OEDER BY price ASC, date DESC LIMIT 0,10;
而且在 (price,date)上有一个组合索引。
explain之后可以发现，虽然用到了这个索引，但是仍然会用到filesort，说明只是使用到了索引中price的ASC排序。
看一个实际的例子吧：
discuz 7.2 gbk版，主题列表：cdb_threads。
mysql> SHOW CREATE TABLE cdb_threads;
| cdb_threads | CREATE TABLE `cdb_threads` (
`tid` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`fid` smallint(6) UNSIGNED NOT NULL DEFAULT ‘0’,
`iconid` smallint(6) UNSIGNED NOT NULL DEFAULT ‘0’,
`typeid` smallint(6) UNSIGNED NOT NULL DEFAULT ‘0’,
`sortid` smallint(6) UNSIGNED NOT NULL DEFAULT ‘0’,
`readperm` tinyint(3) UNSIGNED NOT NULL DEFAULT ‘0’,
`price` smallint(6) NOT NULL DEFAULT ‘0’,
`author` char(15) NOT NULL,
`authorid` mediumint(8) UNSIGNED NOT NULL DEFAULT ‘0’,
`subject` char(80) NOT NULL,
`dateline` int(10) UNSIGNED NOT NULL DEFAULT ‘0’,
`lastpost` int(10) UNSIGNED NOT NULL DEFAULT ‘0’,
`lastposter` char(15) NOT NULL,
`views` int(10) UNSIGNED NOT NULL DEFAULT ‘0’,
`replies` mediumint(8) UNSIGNED NOT NULL DEFAULT ‘0’,
`displayorder` tinyint(1) NOT NULL DEFAULT ‘0’,
`highlight` tinyint(1) NOT NULL DEFAULT ‘0’,
`digest` tinyint(1) NOT NULL DEFAULT ‘0’,
`rate` tinyint(1) NOT NULL DEFAULT ‘0’,
`special` tinyint(1) NOT NULL DEFAULT ‘0’,
`attachment` tinyint(1) NOT NULL DEFAULT ‘0’,
`moderated` tinyint(1) NOT NULL DEFAULT ‘0’,
`closed` mediumint(8) UNSIGNED NOT NULL DEFAULT ‘0’,
`itemid` mediumint(8) UNSIGNED NOT NULL DEFAULT ‘0’,
`supe_pushstatus` tinyint(1) NOT NULL DEFAULT ‘0’,
`sgid` mediumint(8) UNSIGNED NOT NULL DEFAULT ‘0’,
`recommends` smallint(6) NOT NULL,
`recommend_add` smallint(6) NOT NULL,
`recommend_sub` smallint(6) NOT NULL,
`heats` int(10) UNSIGNED NOT NULL DEFAULT ‘0’,
`status` smallint(6) UNSIGNED NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`tid`),
KEY `digest` (`digest`),
KEY `displayorder` (`fid`,`displayorder`,`lastpost`),
KEY `typeid` (`fid`,`typeid`,`displayorder`,`lastpost`),
KEY `sgid` (`fid`,`sgid`),
KEY `sortid` (`sortid`),
KEY `recommends` (`recommends`),
KEY `heats` (`heats`),
KEY `authorid` (`authorid`)
) ENGINE=InnoDB AUTO_INCREMENT=330109 DEFAULT CHARSET=gbk |
fid开头的组合索引有三个：
KEY `displayorder` (`fid`,`displayorder`,`lastpost`),
KEY `typeid` (`fid`,`typeid`,`displayorder`,`lastpost`),
KEY `sgid` (`fid`,`sgid`),
我们用fid和displayorder字段来做排序。
先看order by fid ASC,displayorder ASC的情况：
mysql> EXPLAIN SELECT * FROM cdb_threads WHERE fid IN(1,3,5) ORDER BY fid ASC,displayorder ASC;
+—-+————-+————-+——-+————————–+————–+———+——+——-+————-+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
+—-+————-+————-+——-+————————–+————–+———+——+——-+————-+
| 1 | SIMPLE | cdb_threads | range | displayorder,typeid,sgid | displayorder | 2 | NULL | 12728 | USING WHERE |
+—-+————-+————-+——-+————————–+————–+———+——+——-+————-+
1 row IN SET (0.00 sec)
再看ORDER BY fid DESC, displayorder DESC的情况：
mysql> EXPLAIN SELECT * FROM cdb_threads WHERE fid IN(1,3,5) ORDER BY fid DESC,displayorder DESC;
+—-+————-+————-+——-+————————–+————–+———+——+——-+————-+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
+—-+————-+————-+——-+————————–+————–+———+——+——-+————-+
| 1 | SIMPLE | cdb_threads | range | displayorder,typeid,sgid | displayorder | 2 | NULL | 12728 | USING WHERE |
+—-+————-+————-+——-+————————–+————–+———+——+——-+————-+
1 row IN SET (0.00 sec)
这两种情况下，使用到的KEY都是 KEY `displayorder` (`fid`,`displayorder`,`lastpost`), 没有进行filesort,很完美。
再来看一个DESC,另外一个ASC的情况：
mysql> EXPLAIN SELECT * FROM cdb_threads WHERE fid IN(1,3,5) ORDER BY fid DESC,displayorder ASC;
+—-+————-+————-+——-+————————–+——+———+——+——+—————————–+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
+—-+————-+————-+——-+————————–+——+———+——+——+—————————–+
| 1 | SIMPLE | cdb_threads | range | displayorder,typeid,sgid | sgid | 2 | NULL | 6512 | USING WHERE; USING filesort |
+—-+————-+————-+——-+————————–+——+———+——+——+—————————–+
1 row IN SET (0.00 sec)
mysql> EXPLAIN SELECT * FROM cdb_threads WHERE fid IN(1,3,5) ORDER BY fid ASC,displayorder DESC;
+—-+————-+————-+——-+————————–+——+———+——+——+—————————–+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
+—-+————-+————-+——-+————————–+——+———+——+——+—————————–+
| 1 | SIMPLE | cdb_threads | range | displayorder,typeid,sgid | sgid | 2 | NULL | 6512 | USING WHERE; USING filesort |
+—-+————-+————-+——-+————————–+——+———+——+——+—————————–+
1 row IN SET (0.00 sec)
这两次使用到的key是 KEY `sgid` (`fid`,`sgid`), 由于我们并没有涉及到sgid，所以只用到了fid的索引。。。 至于displayorder字段怎样排序，用的是filesort。肯定比直接使用索引要慢多了。
如果可以搞一个fid ASC, displayorder DESC的组合索引，那就方便多了。事实上mysql不支持这么做啦。
既然mysql不支持这种方式，那我们只好用其它方法解决这个问题。
创建一个新的字段，叫做reverse_displayorder。 此字段中保存的值为 displayorder字段的值乘以-1。
于是 order by fid ASC, displayorder DESC 就可以转化成 order by fid ASC, reverse_displayorder ASC了。
如果是mysql 5.0或之后的版本，只要创建一个触发器(trigger)来自动更新reverse_displayorder的值就可以了，程序都不用大改。
虽然discuz没有这样做，但是MediaWiki确实是这样设计的。
我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。
我原创，你原创，我们的内容世界才会更加精彩！
【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】
微信公众号
TechTarget
官方微博
TechTarget中国
作者
相关推荐
-
加速SQL查询效能的七大秘诀
任何平台的SQL开发者都有自身的困惑，似乎他们一直纠缠在DO WHILE循环里，这个循环让他们不断地重复同样的错误。这是因为数据库的发展依然不够成熟。
-
巧建MySQL sum索引以提升效率
在MySQL中使用恰当的索引，可以使SQL的效率倍增，类似sum的函数还有min(),max(),这些都需要在字段上建索引。
-
MySQL的B-Tree索引和Hash索引的区别
Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问。