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

核心提示： So when do you really need Descending indexes ? Most typical case is when you want to order by two colums in different directions: … ORDER

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.

那么什么时候才真的需要倒序索引呢？很多典型的情况是当你想要对两个字段作不同方向的排序时：… ORDER BY price ASC, date DESC LIMIT 10。如果已经有了对 (price,date) 的正序索引，则不能较好地优化这个查询 -- 需要用到外部排序(“filesort”)。如果能建立 price ASC, date DESC 的索引，那么这个查询就能按照已经排好的顺序取出数据了。

This is however something you can workaround by having something like “reverse_date” column and using it for sort. With MySQL 5.0 you even can use triggers to update it as real date updates so it becomes less ugly. In fact this is for example why you would see “reverse_timestamp” field in Wikipedia table structure.

然而，常见的变通办法是创建一个“倒序数据”字段，并且利用它来排序。在 MySQL 5.0 中你甚至可以使用触发器来更新真实的数据使得更合适。这就是为什在 Wikipedia 的表结构中有一个 “reverse_timestamp” 字段的缘故。

Loose index scan - Number of years ago when I just started using MySQL I thought it would have any optimization which could come to my mind. For example if I would have (A>0 and B>6) clause and index (A,B) I expected it would start looking at all values where A>0 instantly jumping to onces have B>6 by using index. It is possibe. So I was shocked and upset to find out it did not. And this optimization is still not implemented. This is very important item to remember when you designing your new applications or porting ones from other databases. Designing the indexes for MySQL you should only make sure queries use “=” for all keyparts in the last of index. Only last one is allowed to have “range” clauses, such as >, IN etc. All clauses which follow the range in the index will not use index for their operation.