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

时间:2022-06-27 20:56:27

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

谈谈MySQL中的降序索引 order by id DESC / ASC的更多相关文章

  1. MySQL 8 新特性之降序索引

    MySQL 8.0终于支持降序索引了.其实,从语法上,MySQL 4就支持了,但正如官方文档所言,"they are parsed but ignored",实际创建的还是升序索引 ...

  2. [20181124]关于降序索引问题3.txt

    [20181124]关于降序索引问题3.txt --//链接:blog.itpub.net/267265/viewspace-2221425/,探讨降序索引中索引的键值.--//实际上使用函数sys_ ...

  3. [20181124]关于降序索引问题2.txt

    [20181124]关于降序索引问题2.txt --//链接:blog.itpub.net/267265/viewspace-2221425/,探讨降序索引中索引的键值.--//实际上使用函数sys_ ...

  4. [20191218]降序索引疑问4.txt

    [20191218]降序索引疑问4.txt --//前几天优化一个项目,我发现许多表里面有有隐含字段,一般开发很少建立函数索引.我自己检查发现里面存在大量的降序索引.--//我感觉有点奇怪,为什么开发 ...

  5. [20190910]关于降序索引问题5.txt

    [20190910]关于降序索引问题5.txt --//测试了索引TERM使用0xfe表示,回想到以前遇到降序索引的特殊字符编码问题,现在可是忘得一干二净.--//现在想想当时自己怎么猜测出来的,^_ ...

  6. 谈谈MySQL中的锁

    谈谈MySQL中的锁 锁的定义 ​ 在生活中锁的例子就非常多了,所以应该很容易理解锁的含义.在计算机领域,可以这样来概述,锁是计算机协调多个进行进程并发访问某一资源的机制. ​ 在数据库中,锁也是一个 ...

  7. Mysql中主键与索引

    摘自: https://www.cnblogs.com/wicub/p/5898286.html 一.什么是索引?索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没 ...

  8. mysql 利用延迟关联优化查询(select * from your_table order by id desc limit 2000000,20)

    其实在我们的工作中类似,select * from your_table order by id desc limit 2000000,20会经常遇见,比如在分页中就很常见. 如果我们的sql中出现这 ...

  9. ROW_NUMBER() over(order by ID desc ) as RowNumber 返回类型问题

    因为ID是int类型,所以ROW_NUMBER() over(order by ID desc ) as RowNumber 想当然的认为是Int类型 实际ROW_NUMBER() over(orde ...

随机推荐

  1. C++ std::array

    std::array template < class T, size_t N > class array; Code Example #include <iostream> ...

  2. python对XML的解析

    原文:http://blog.csdn.net/yueguanghaidao/article/details/7265246 python有三种方法解析XML,SAX,DOM,以及ElementTre ...

  3. python学习道路&lpar;day2note&rpar;&lpar;数据类型,运算符,字符串,列表&rpar;

    一,数据类型 1.1数字 数字分为int(整型),long(长整型),float(浮点型) 1.1.1 int整型的取值范围为 在32位机器上,整数的位数为32位,取值范围为-2**31-2**31- ...

  4. Spring 配置文件详解 http&colon;&sol;&sol;www&period;blogjava&period;net&sol;hellxoul&sol;archive&sol;2011&sol;11&sol;19&sol;364324&period;html

    1.基本配置: <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http: ...

  5. 理解java中的ThreadLocal(转)

    一.对ThreadLocal概述 JDK API 写道: 该类提供了线程局部 (thread-local) 变量.这些变量不同于它们的普通对应物,因为访问某个变量(通过其 get 或 set 方法)的 ...

  6. 关于CopyU!的常见问题解答

    拷优(CopyU!)常见问题解答 本常见问题解答列举了一些常见的疑问及其解释,如果您对CopyU!有任何问题,请您首先查看本解答! 本解答将会保持随时更新! 一.使用篇:     1.问:我的杀毒软件 ...

  7. 二、VueJs 填坑日记之基础项目构建

    在上一篇文章中,大致介绍了一下本系列博文以及学习vuejs我们需要了解的一些概念,希望大家认真阅读,所谓知己知彼,百战百胜,学习也一样,工欲善其事,必先利其器,要想学好vuejs,那前提的概念一定要熟 ...

  8. redis特性 存储 API 集群 等

    公司组内技术分享,刚好最近工作用redis构建缓存,所以想同事们分享关于redis的一些知识, 这些知识不仅仅是包括一些API层,而是一些关于redis功能功能特性的 目前为什么使用redis构建缓存 ...

  9. HTML基础教程-标题

    HTML 标题 在 HTML 文档中,标题很重要. HTML 标题 标题(Heading)是通过 <h1> - <h6> 等标签进行定义的. <h1> 定义最大的标 ...

  10. css学习の第六弹—样式设置小技巧

    一.css样式设置小技巧>>1.行内元素水平居中是通过给父元素设置 text-align:center 来实现的.html代码:<body> <div class=&qu ...