mysql降序索引和减轻索引扫描

　　降序索引和减轻索引扫描

　　Comments to my previous posts, especially this one by Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None of these are actially related to Innodb tables in general - these are features MySQL should get for all storage engines at some point.

　　在我以前文章的评论中，尤其是 Gokhan 在这个中提到的，激发了我想写点关于降序索引和减轻索引扫描的东西，或者是 Gokhan 所谓的“改善的范围”支持。通常这些特性跟 Innodb 表都没有内在联系，它们将来在某些时候都能支持各种存储引擎。

　　Descending indexes - This is something MySQL does not have at this point, but it was not where for so long at large extent because it is less problem than many people think. First - if index is ascending it does not mean it can’t be scanned in reverse order and it will well be. This is how MySQL will optimize indexed ORDER BY col DESC queries for example. Reverse scan could be as fast as forward scan - this is however where storage engines and operating systems come in play. For example certain operation systems might not do backward read-ahead which may slow it down a bit. Or some storage engines, such as MyISAM (for packed indexes) may have reverse scan being much slower than forward scan.

　　降序索引 -- 现在 MySQL 还不支持这个功能，不过这比很多人想的那样，问题少多了。首先，如果索引是顺序的并不意味着它不能倒序扫描，实际上它表现得挺好的。这就是为什么 MySQL 能优化例如 ORDER BY col DESC 查询的索引。倒序扫描能和正序扫描一样快，不过这些是由存储引擎和操作系统来处理的。例如有些操作系统无法倒序读，这对速度有所降低。或者某些存储引擎，如 MyISAM (它压缩了索引) 倒序扫描时比正序扫描来的慢。

　　So when do you really need Descending indexes ? Most typical case is when you want to order by two colums in different directions: … ORDER BY price ASC, date DESC LIMIT 10 If you have indexed on (price,date) in ascending order you will not be able to optimize this query well - external sort (”filesort”) will be needed. If you would be able to build index on price ASC, date DESC the same query could retrive data in aready sorted order.