(译)MySQL 8.0实验室—MySQL中的倒序索引(Descending Indexes)

 

简介

正文首要介绍当在MySQL中实施order
by时,MySQL使用的排序算法。当在select语句中央银行使order
by语句时,MySQL首先会利用索引来制止实行排序算法;在不可能使用索引的景况下,大概选拔
迅猛排序归并排序堆排序扩充排序。
本文中许多地方都以翻译的MySQL官网,罗马尼亚语好的同校可径直翻开原来的小说

方今在钻探mysql数据库优化,在英特网查了查资料,留在这里,前边继续跟新自个儿的切磋心得。下边是英特网的部分素材

译者注:
MySQL
8.0事先,不管是或不是钦赐索引建的排序格局,都会忽略创立索引时候钦赐的排序格局(语法上不会报错),最后都会创制为ASC情势的目录,
在施行查询的时候,只设有forwarded(正向)情势对索引进行围观。
有关正向索引和反向索引,逻辑上很轻松了然,这里有八个有关的定义:
正向索引只怕反向(倒序)索引,两个都是在营造B树索引时候的相关字段排序格局,是B索引树的逻辑存款和储蓄方式
正向扫描(forward)和反向扫描( Backward index
scan;)是进行查询的进程中对B树索引的扫描格局,是数额推行安顿时候的一种索引围观方式
有关正向扫描或许反向扫描不是随机的,受sql语句中(正/反向)排序格局以及(正/反向)索引的震慑
事先在sqlserver中简易写过好几好像的事物,

目录排序

在有些情形下,MySQL可以使用索引来满意O哈弗DER
BY子句,从而无需实行额外的排序。
就算OLacrosseDER
BY与索引不完全同盟,索引也得以选择,只要索引的享有未利用一些和全体额外的OPAJERODER
BY列都以WHERE子句中的常量。 以下查询利用索引来解析OEscortDEENCORE BY部分:

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = 1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

在某个景况下,MySQL不能够应用索引来解析OPAJERODER
BY,尽管它依然能够使用索引来查找与WHERE子句相配的行。 举个例子:

  • 针对查询对分化索引使用OEnclaveDER
    BY(注意:此处的key1和key2是八个完全差异的目录,差距对待上文的率先个例子):

SELECT * FROM t1 ORDER BY key1, key2;

  • 查询在目录的非三翻五次部分行使OSportageDELAND BY:

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part1, key_part3;

  • 查询混合使用ASC和DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

  • 用来获取行的目录与O奥迪Q5DER
    BY中动用的目录分歧(where查询已经打破凌驾key1所能做的):

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

  • 查询利用OENCOREDEGL450 BY索引列名称以外的术语的表明式(举例sum等):

SELECT * FROM t1 ORDER BY ABS(key);
澳门金沙vip,SELECT * FROM t1 ORDER BY -key;

  • 查询连接了过多表,OWranglerDER
    BY中的列不是漫天源于用于检索行的第一个特别数表。
    (那是EXPLAIN输出中从不const连接类型的率先个表。)
  • 询问利用了分裂的ORAV4DECRUISER BY和GROUP BY表达式。
  • 目录不按顺序存款和储蓄行。 举例,对于MEMOENCOREY表中的HASH索引。

排序索引的可用性或然会惨被列小名的熏陶。 纵然列t1.a被索引。
在此语句中,选择列表中列的名目为a。 它指的是t1.a,与OQashqaiDER
BY中的a的援引同样,由此得以使用t1.a上的目录:

SELECT a FROM t1 ORDER BY a;

在此语句中,选择列表中列的名号也是a,但它是小名。
它指的是ABS(a),和在OQX56DEPRADO BY中引用a同样,所以t1.a上的目录无法使用:

SELECT ABS(a) AS a FROM t1 ORDER BY a;

在偏下语句中,ORAV4DEOdyssey BY援引的称呼不是选拔列表中列的称谓。
可是t第11中学有三个列命名称为a,所以OENCOREDEMurano BY指的是t1.a,能够使用t1.a上的目录。
(当然,结果的排序依次只怕与ABS(a)的依次完全两样。)

SELECT ABS(a) AS b FROM t1 ORDER BY a;

暗中认可情形下,假使在查询中钦点了OENCOREDE揽胜极光 BY
col1,col2,…,MySQL会排序全数GROUP BY col1,col2,…查询。
假诺查询包含GROUP BY,可是你希望制止排序结果的花费,则能够通过点名OENVISIONDER
BY NULL来禁止排序。举个例子:

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

优化器照旧能够选择使用排序来贯彻分组操作。 O猎豹CS6DE中华V BY
NULL禁止对结果进行排序,并非由此对操作实行分组来规定结果。

注意:
暗许情况下,GROUP
BY隐式排序(即在未曾ASC或DESC提示符的情事下),可是依附隐式GROUP
BY排序已被弃用。 要发生给定的排序依次,请对GROUP
BY列使用显式ASC或DESC提醒符,或提供O翼虎DE科雷傲 BY子句。 GROUP
BY排序是四个MySQL增添,大概会在今后的本子中退换;
例如,为了使优化器以其以为最平价的法子对分组举行排序并防止排序费用。

在好几情形下,MySQL能够从来采取索引来满足一个 OENVISIONDEKoleos BY 或 GROUP BY
子句而没有要求做额外的排序。

全部上看,抛开正向索引和倒序索引,在围观扫描的长河中,正向索引围观的在性质上,稍微优于反向索引围观。
不过,即正是反向索引围观,也是优化器依据具体查询实行优化的结果,并不是一个倒霉的选料。

排序算法

当order by不能够采取索引实行排序时,将运用排序算法举行排序:

  1. 若排序内容能一切归入内存,则仅在内部存款和储蓄器中选拔敏捷排序
  2. 若排序内容不可能一体放入内存,则分批次将排好序的内容放入文件,然后将七个文本举办归并排序
    3.若排序中隐含limit语句,则利用堆排序优化排序进程

注意:
MySQL是在5.6后引进堆排序来优化limit子句,但堆排序是非牢固的(对于同样的key值,无法担保排序后与排序前的地点一致),所以导致分页重复的气象。为了防止这一个主题素材,我们得以在排序中丰硕独一值,比方主键id,那样由于id是天下无双的,确定保证到场排序的key值不等同。
例:SELECT * FROM ratings ORDER BY category, id LIMIT 5;

就算 O君越DE奥迪Q7 BY
不是和目录的一一精确相配,索引还能被用到,只要永不的目录部分和具备的附加的
O途胜DEEvoque BY 字段在 WHERE 子句中都被归纳了。

 

原本文本排序算法

  1. 依据键或表扫描读取全部行。跳过不相符WHERE子句的行。
  2. 对此每一行,在排序缓冲区中蕴藏由一对值(排序键值和行ID)组成的元组。
  3. 如果具备对都契合排序缓冲区,则不会创设临时文件。否则,当排序缓冲区变满时,在内部存款和储蓄器中运转敏捷排序并将其写入有时文件。保存指向排序块的指针。
  4. 双重上述手续,直到读取全数行。
  5. 在多少个MELacrosseGEBUFF(7)区域中进行频仍统一到另三个不经常文件中的贰个块。重复,直到第三个文件的有着块都在第一个文本中。
  6. 双重以下操作,直到剩余零星ME科雷傲GEBUFF2(15)个块。
  7. 在最后三遍多合併时,只将行ID(值对的最终一片段)写入结果文件。
  8. 接纳结果文件中的行ID按排序依次读取行。要优化此操作,请读取大块行ID,对它们举办排序,并采纳它们以排序依次将行读入行缓冲区。行缓冲区大小是read_rnd_buffer_size系统变量值。

这种艺术的一个标题是它读取行一次:贰遍在WHERE子句评估期间,并在排序值对之后再度。
即便第三次一而再地拜见了行(举例,假诺表扫描完结),则第三遍被随便访问。
(排序键是稳步的,但行职责不是。)

下列的多少个查询都会动用索引来缓和 O普拉多DELacrosse BY 或 GROUP BY 部分:

 

革新的文本排序算法

精雕细刻的文件排序算法富含二个优化,以幸免读取行四遍:它记录排序键值,但不记住行ID,它记录查询列的援引。
修改的filesort算法的办事规律如下:

  1. 读取与WHERE子句相称的行。
  2. 对此每一行,在排序缓冲区中积累由排序键值和询问援引的列组成的元组。
  3. 当排序缓冲区变满时,通过内存中的排序键值对元组进行排序,并将其写入临时文件。
  4. 在集合排序有的时候文件之后,以排序依次检索行,可是从排序的元组中一贯读取查询所需的列,而不是再次访问该表。

由创新的公文排序算法使用的元组比原始算法使用的指标长,并且在排序缓冲区中有更加少的万分。
由此,额外的I/O或者使修改后的办法变得更加慢,实际不是更加快。
为幸免减速,优化器仅在排序元组中的额外列的总大小不超越max_length_for_sort_data系统变量的值时才使用革新算法。(将此变量的值设置得太高的多个症状是高磁盘活动和CPU活动低的重组。)
精耕细作的公文排序算法包含额外的优化,意在使越来越多的元组适合排序缓冲区:对于项目为CHA酷路泽或VARCHAMurano的任何列或其余可空固定大小的数据类型,那些值将被打包。
比方,不包装,独有3个字符的VARCHA中华V(255)列值在排序缓冲区中需求2五十个字符。
打包时,该值只需3个字符,加上多个字节的长度提示符。
NULL值只必要三个位掩码。

SELECT * FROM t1 ORDER BY key_part1,key_part2,… ;
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,
key_part2 DESC;


参谋文献

  1. ORDER BY
    Optimization
  2. LIMIT Query
    Optimization

 在另一对场所下,MySQL不可能利用索引来满意 OLANDDER
BY,就算它会使用索引来找到记录来协作 WHERE 子句。这一个情况如下:

原稿链接:http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/ 

1>对两样的索引键做 O卡宴DEKuga BY :
SELECT * FROM t1 ORDER BY key1, key2;

 

2>在非两次三番的索引键部分上做 O奇骏DE奥迪Q5 BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

以下为译文:

3>同临时候利用了 ASC 和 DESC:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

从8.0优化器实验室公布开端,MySQL初阶辅助倒序索引。
正如我将要本文中详细介绍的,那些新特色能够用来清除对结果排序的要求,并在数不完查询中拉动质量创新。

4>用于寻找记录的索引键和做 O奥迪Q7DE福特Explorer BY 的不是同四个:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

 

5>表索引中的记录不是按序存款和储蓄。比方,HASH 和 HEAP 表正是这么。

简介

透过试行 EXPLAIN SELECT … O大切诺基DER
BY,就驾驭MySQL是或不是在查询中央银行使了目录。若是 Extra 字段的值是 Using
filesort,则表达MySQL不能使用索引。

在此版本从前,全部索引都是按升序创设的。当语法本人被深入分析时,元数据不会被封存。举例在MySQL
5.7中:

翻看索引

mysql 5.7> CREATE TABLE t1 (a INT, b INT, INDEX a_desc_b_asc (a DESC, b ASC));
Query OK, 0 rows affected (0.47 sec)

mysql 5.7> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a_desc_b_asc` (`a`,`b`) <-- 创建索引时候的元数据没有被保留
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

基数是数据列所包涵的分裂值的数目。比如,某些数据列包括值1、3、7、4、7、3,那么它的基数正是4。索引的基数相对于数据表行数较高(也正是说,列
中含有众多不一的值,重复的值非常少)的时候,它的办事效果最佳。假使某数码列含有相当多差异的年华,索引会非常快地分辨数据行。假若某些数据列用于记录性别
(独有”M”和”F”三种值),那么索引的用途就相当小。假诺值现身的可能率几乎相当于,那么不论寻觅哪个值都恐怕赢得二分之一的数据行。在这一个情况下,最佳根本不
要使用索引,因为查询优化器开掘某些值现身在表的数量行中的百分比异常高的时候,它一般会忽视索引,举行全表扫描。惯用的百分比界线是”33.33%”。

相应小心的是,MySQL 5.7
optimizer可以反向扫描四个升序索引(依照降序排列),其资金财产较高

在好几景况下,MyS…

(译者注:以上是原来的作品中写道的,MySQL
5.7中不领会怎么去看清在对索引围观的时候,毕竟是正向扫描照旧反向扫描)。
一般来讲能够更上一层楼测量试验,大家得以见见正向索引围观比反向索引围观好~15%。
无法支撑倒叙索引的显要限制是,优化器必需对混合顺序(如DESC、b
ASC的顺序)使用文件排序。

MySQL 8.0中的创新

引进反向索引后,InnoDB以往得以坚守降序顺序存储数据行,优化器就要查询中呼吁降序时行使它。
再也上面的事例,大家得以见到在开立表时目录顺序消息被正确地保留了: