欢迎光临!请记住我的域名:http://wish123.cnblogs.com
代码改变世界

谈谈MySQL中的降序索引 order by id DESC / ASC

2019-03-26 17:55  wish123  阅读(10686)  评论(0编辑  收藏  举报

今天这篇主要讲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确实是这样设计的。