By索引优化,如何优化
分类:数据库

 

简介

本文主要介绍当在MySQL中执行order by时,MySQL使用的排序算法。当在select语句中使用order by语句时,MySQL首先会使用索引来避免执行排序算法;在不能使用索引的情况下,可能使用 快速排序归并排序堆排序进行排序。
本文中很多地方都是翻译的MySQL官网,英语好的同学可直接查看原文

MySQL Order By keyword是用来给记录中的数据进行分类的。MySQL Order By Keyword根据关键词分类ORDER BY keyword是用来给记录中的数据进行分类的。

这几天在研究mysql数据库优化,在网上查了查资料,留在这里,后面继续跟新自己的研究心得。下面是网上的一些资料

 

译者注:
MySQL 8.0之前,不管是否指定索引建的排序方式,都会忽略创建索引时候指定的排序方式(语法上不会报错),最终都会创建为ASC方式的索引,
在执行查询的时候,只存在forwarded(正向)方式对索引进行扫描。
关于正向索引和反向索引,逻辑上很容易理解,这里有两个相关的概念:
正向索引或者反向(倒序)索引,两者都是在构建B树索引时候的相关字段排序方式,是B索引树的逻辑存储方式
正向扫描(forward)和反向扫描( Backward index scan;)是执行查询的过程中对B树索引的扫描方式,是数据执行计划时候的一种索引扫描方式
关于正向扫描或者反向扫描不是随意的,受sql语句中(正/反向)排序方式以及(正/反向)索引的影响
之前在sqlserver中简单写过一点类似的东西,

索引排序

在某些情况下,MySQL可以使用索引来满足ORDER BY子句,从而无需进行额外的排序。
即使ORDER BY与索引不完全匹配,索引也可以使用,只要索引的所有未使用部分和所有额外的ORDER BY列都是WHERE子句中的常量。 以下查询使用索引来解析ORDER 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不能使用索引来解析ORDER BY,尽管它仍然可以使用索引来查找与WHERE子句匹配的行。 例如:

  • 针对查询对不同索引使用ORDER BY(注意:此处的key1和key2是两个完全不同的索引,区别对待上文的第一个例子):

SELECT * FROM t1 ORDER BY key1, key2;

  • 查询在索引的非连续部分使用ORDER 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;

  • 用于获取行的索引与ORDER BY中使用的索引不同(where查询已经打破超出key1所能做的):

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

  • 查询使用ORDER BY索引列名称以外的术语的表达式(比如sum等):

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

  • 查询连接了许多表,ORDER BY中的列不是全部来自用于检索行的第一个非常数表。 (这是EXPLAIN输出中没有const连接类型的第一个表。)
  • 查询使用了不同的ORDER BY和GROUP BY表达式。
  • 索引不按顺序存储行。 例如,对于MEMORY表中的HASH索引。

排序索引的可用性可能会受到列别名的影响。 假设列t1.a被索引。 在此语句中,选择列表中列的名称为a。 它指的是t1.a,与ORDER BY中的a的引用一样,因此可以使用t1.a上的索引:

SELECT a FROM t1 ORDER BY a;

在此语句中,选择列表中列的名称也是a,但它是别名。 它指的是ABS(a),和在ORDER BY中引用a一样,所以t1.a上的索引不能使用:

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

在以下语句中,ORDER BY引用的名称不是选择列表中列的名称。 但是t1中有一个列命名为a,所以ORDER BY指的是t1.a,可以使用t1.a上的索引。 (当然,结果的排序顺序可能与ABS(a)的顺序完全不同。)

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

默认情况下,如果在查询中指定了ORDER BY col1,col2,...,MySQL会排序所有GROUP BY col1,col2,...查询。
如果查询包含GROUP BY,但是您希望避免排序结果的开销,则可以通过指定ORDER BY NULL来禁止排序。例如:

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

优化器仍然可以选择使用排序来实现分组操作。 ORDER BY NULL禁止对结果进行排序,而不是通过对操作进行分组来确定结果。

注意:
默认情况下,GROUP BY隐式排序(即在没有ASC或DESC指示符的情况下),但是依赖隐式GROUP BY排序已被弃用。 要产生给定的排序顺序,请对GROUP BY列使用显式ASC或DESC指示符,或提供ORDER BY子句。 GROUP BY排序是一个MySQL扩展,可能会在将来的版本中更改; 例如,为了使优化器以其认为最有效的方式对分组进行排序并避免排序开销。

复制代码 代码如下:SELECT column_name FROM table_name ORDER BY column_name

在某些情况下,MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序。

在一些情况下,MySQL可以直接使用索引来满足一个ORDER BY 或GROUP BY 子句而无需做额外的排序。尽管ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的ORDER BY 字段在WHERE 子句中都被包括了。

整体上看,抛开正向索引和倒序索引,在扫描扫描的过程中,正向索引扫描的在性能上,稍微优于反向索引扫描。
不过,即便是反向索引扫描,也是优化器根据具体查询进行优化的结果,并非一个不好的选择。

排序算法

当order by不能使用索引进行排序时,将使用排序算法进行排序:

  1. 若排序内容能全部放入内存,则仅在内存中使用快速排序
  2. 若排序内容不能全部放入内存,则分批次将排好序的内容放入文件,然后将多个文件进行归并排序
    3.若排序中包含limit语句,则使用堆排序优化排序过程

注意:
MySQL是在5.6后引入堆排序来优化limit子句,但堆排序是非稳定的(对于相同的key值,无法保证排序后与排序前的位置一致),所以导致分页重复的现象。为了避免这个问题,我们可以在排序中加上唯一值,比如主键id,这样由于id是唯一的,确保参与排序的key值不相同。
例:SELECT * FROM ratings ORDER BY category, id LIMIT 5;

例子

尽管 ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子句中都被包括了。

 

 

原始文件排序算法

  1. 根据键或表扫描读取所有行。跳过不符合WHERE子句的行。
  2. 对于每一行,在排序缓冲区中存储由一对值(排序键值和行ID)组成的元组。
  3. 如果所有对都适合排序缓冲区,则不会创建临时文件。否则,当排序缓冲区变满时,在内存中运行快速排序并将其写入临时文件。保存指向排序块的指针。
  4. 重复上述步骤,直到读取所有行。
  5. 在多个MERGEBUFF(7)区域中进行多次合并到另一个临时文件中的一个块。重复,直到第一个文件的所有块都在第二个文件中。
  6. 重复以下操作,直到剩下少于MERGEBUFF2(15)个块。
  7. 在最后一次多合并时,只将行ID(值对的最后一部分)写入结果文件。
  8. 使用结果文件中的行ID按排序顺序读取行。要优化此操作,请读取大块行ID,对它们进行排序,并使用它们以排序顺序将行读入行缓冲区。行缓冲区大小是read_rnd_buffer_size系统变量值。

这种方法的一个问题是它读取行两次:一次在WHERE子句评估期间,并在排序值对之后再次。 即使第一次连续地访问了行(例如,如果表扫描完成),则第二次被随机访问。 (排序键是有序的,但行位置不是。)

SQL创建代码:

下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分:

使用索引的MySQL Order By

 

改进的文件排序算法

改进的文件排序算法包含一个优化,以避免读取行两次:它记录排序键值,但不记住行ID,它记录查询列的引用。 修改的filesort算法的工作原理如下:

  1. 读取与WHERE子句匹配的行。
  2. 对于每一行,在排序缓冲区中存储由排序键值和查询引用的列组成的元组。
  3. 当排序缓冲区变满时,通过内存中的排序键值对元组进行排序,并将其写入临时文件。
  4. 在合并排序临时文件之后,以排序顺序检索行,但是从排序的元组中直接读取查询所需的列,而不是再次访问该表。

由改进的文件排序算法使用的元组比原始算法使用的对象长,并且在排序缓冲区中有更少的匹配。 因此,额外的I/O可能使修改后的方法变得更慢,而不是更快。 为避免减速,优化器仅在排序元组中的额外列的总大小不超过max_length_for_sort_data系统变量的值时才使用改进算法。(将此变量的值设置得太高的一个症状是高磁盘活动和CPU活动低的组合。)
改进的文件排序算法包括额外的优化,旨在使更多的元组适合排序缓冲区:对于类型为CHAR或VARCHAR的其他列或任何可空固定大小的数据类型,这些值将被打包。 例如,不包装,只有3个字符的VARCHAR(255)列值在排序缓冲区中需要255个字符。 打包时,该值只需3个字符,加上两个字节的长度指示符。 NULL值只需要一个位掩码。

复制代码 代码如下:CREATE TABLE IF NOT EXISTS mysql_order_by_test NOT NULL AUTO_INCREMENT, name char NOT NULL, sex tinyint NOT NULL, KEY uid ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;INSERT INTO mysql_order_by_test VALUES;INSERT INTO mysql_order_by_test VALUES;INSERT INTO mysql_order_by_test VALUES;

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 *By索引优化,如何优化。 FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

下列的几个查询都会使用索引来解决ORDER BY 或GROUP BY 部分:


参考文献

  1. ORDER BY Optimization
  2. LIMIT Query Optimization

通过索引优化来实现MySQL的ORDER BY语句优化:

 在另一些情况下,MySQL无法使用索引来满足 ORDER BY,尽管它会使用索引来找到记录来匹配 WHERE 子句。这些情况如下:

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

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

1、ORDER BY的索引优化。如果一个SQL语句形如:

1>对不同的索引键做 ORDER BY :
SELECT * FROM t1 ORDER BY key1, key2;

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

 

复制代码 代码如下:SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];

2>在非连续的索引键部分上做 ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

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

以下为译文:

在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

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

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

从8.0优化器实验室发布开始,MySQL开始支持倒序索引。
正如我将在本文中详细介绍的,这个新特性可以用来消除对结果排序的需求,并在许多查询中带来性能改进。

2、WHERE ORDER BY的索引优化,形如:

4>用于搜索记录的索引键和做 ORDER BY 的不是同一个:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

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

 

复制代码 代码如下:SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];

5>表索引中的记录不是按序存储。例如,HASH 和 HEAP 表就是这样。

 

简介

建立一个联合索引来实现order by 优化。

通过执行 EXPLAIN SELECT ... ORDER BY,就知道MySQL是否在查询中使用了索引。如果 Extra 字段的值是 Using filesort,则说明MySQL无法使用索引。

不使用索引的MySQL Order By

在此版本之前,所有索引都是按升序创建的。当语法本身被解析时,元数据不会被保留。例如在MySQL 5.7中:

注意:如果columnX对应多个值,如下面语句就无法利用索引来实现order by的优化

查看索引

在另一些情况下,MySQL无法使用索引来满足ORDER BY,尽管它会使用索引来找到记录来匹配WHERE 子句。这些情况如下:

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 t1G
*************************** 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)

复制代码 代码如下:SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ORDER BY[sort];

基数是数据列所包含的不同值的数量。例如,某个数据列包含值1、3、7、4、7、3,那么它的基数就是4。索引的基数相对于数据表行数较高(也就是说,列 中包含很多不同的值,重复的值很少)的时候,它的工作效果最好。如果某数据列含有很多不同的年龄,索引会很快地分辨数据行。如果某个数据列用于记录性别 (只有"M"和"F"两种值),那么索引的用处就不大。如果值出现的几率几乎相等,那么无论搜索哪个值都可能得到一半的数据行。在这些情况下,最好根本不 要使用索引,因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。惯用的百分比界线是"30%"。

* 对不同的索引键做ORDER BY :

应该注意的是,MySQL 5.7 optimizer能够反向扫描一个升序索引(按照降序排列),其成本较高

3、WHERE 多个字段ORDER BY

在某些情况下,MyS...

SELECT * FROM t1 ORDER BY key1, key2;

By索引优化,如何优化。(译者注:以上是原文中写道的,MySQL 5.7中不知道怎么去判断在对索引扫描的时候,究竟是正向扫描还是反向扫描)。
如下可以进一步测试,我们可以看到正向索引扫描比反向索引扫描好~15%。
不能支持倒叙索引的主要限制是,优化器必须对混合顺序(如DESC、b ASC的顺序)使用文件排序。

复制代码 代码如下:SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

* 在非连续的索引键部分上做ORDER BY:

MySQL 8.0中的改进

建立索引实现order by的优化,比建立索引效果要好得多

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

引入反向索引后,InnoDB现在可以按照降序顺序存储数据行,优化器将在查询中请求降序时利用它。
重复上面的例子,我们可以看到在创建表时索引顺序信息被正确地保留了:

在某些情况中,MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序。where条件和order by使用相同的索引,并且order by的顺序和索引顺序相同,并且order by的字段都是升序或者都是降序。

* 同时使用了ASC 和DESC:

mysql 8.0> 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 8.0> show create table t1;
 ------- -------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Table | Create Table |
 ------- -------------------------------------------------------------------------------------------------------------------------------------------------------- 
| t1 | CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `a_desc_b_asc` (`a` DESC,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 ------- -------------------------------------------------------------------------------------------------------------------------------------------------------- 
1 row in set (0.00 sec)

例如:下列sql可以使用索引。

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

为了区分向后和向前索引扫描,还改进了EXPLAIN的输出。
对于MySQL-5.7,除了查询2和查询6之外,我们对所有查询都使用反向索引扫描或文件排序,因为这两个查询只需要升序。

复制代码 代码如下: SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

* 用于搜索记录的索引键和做ORDER BY 的不是同一个:

 

但是以下情况不使用索引:

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

Query 1: SELECT * FROM t1 ORDER BY a DESC;

复制代码 代码如下:①SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;--order by的字段混合ASC和DESC②SELECT * FROM t1 WHERE key2=constant ORDER BY key1;--用于查询行的关键字与ORDER BY中所使用的不相同③SELECT * FROM t1 ORDER BY key1, key2;--对不同的关键字使用ORDER BY:

* 有很多表一起做连接,而且读取的记录中在ORDER BY 中的字段都不全是来自第一个非常数的表中(也就是说,在EXPLAIN 分析的结果中的第一个表的连接类型不是const)。

mysql 8.0> explain SELECT * FROM t1 ORDER BY a DESC;
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ------------- 
| 1  | SIMPLE    | t1    | NULL    | index  | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Using index |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ------------- 
1 row in set, 1 warning (0.00 sec)

* 使用了不同的ORDER BY 和GROUP BY 表达式。

Query 2: SELECT * FROM t1 ORDER BY a ASC;

* 表索引中的记录不是按序存储。例如,HASH 和HEAP 表就是这样。

mysql 8.0> explain SELECT * FROM t1 ORDER BY a ASC;
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ---------------------------------- 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ---------------------------------- 
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Backward index scan; Using index |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ---------------------------------- 
1 row in set, 1 warning (0.00 sec)

 

Query 3: SELECT * FROM t1 ORDER BY a DESC, b ASC;

通过执行EXPLAIN SELECT ... ORDER BY,就知道MySQL是否在查询中使用了索引。如果Extra 字段的值是Using filesort,则说明MySQL无法使用索引。详情请看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。当必须对结果进行排序时,MySQL 4.1以前 它使用了以下filesort 算法:

mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a DESC, b ASC;
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ------------- 
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Using index |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ------------- 
1 row in set, 1 warning (0.00 sec)
  1. 根据索引键读取记录,或者扫描数据表。那些无法匹配WHERE 分句的记录都会被略过。

Query 4: SELECT * FROM t1 ORDER BY a ASC, b DESC;

2. 在缓冲中每条记录都用一个‘对’存储了2个值(索引键及记录指针)。缓冲的大小依据系统变量sort_buffer_size 的值而定。

mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a ASC, b DESC;
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ---------------------------------- 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ---------------------------------- 
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Backward index scan; Using index |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ---------------------------------- 
1 row in set, 1 warning (0.00 sec)

3. 当缓冲慢了时,就运行qsort(快速排序)并将结果存储在临时文件中。将存储的块指针保存起来(如果所有的‘对’值都能保存在缓冲中,就无需创建临时文件了)。

Query 5: SELECT * FROM t1 ORDER BY a DESC, b DESC;

  1. 执行上面的操作,直到所有的记录都读取出来了。
mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a DESC, b DESC;
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ----------------------------- 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ----------------------------- 
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Using index; Using filesort |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ----------------------------- 
1 row in set, 1 warning (0.01 sec)

5. 做一次多重合并,将多达MERGEBUFF(7)个区域的块保存在另一个临时文件中。重复这个操作,直到所有在第一个文件的块都放到第二个文件了。

Query 5: SELECT * FROM t1 ORDER BY a ASC, b ASC;

  1. 重复以上操作,直到剩余的块数量小于MERGEBUFF2 (15)。
mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a ASC, b ASC;
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ----------------------------- 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ----------------------------- 
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Using index; Using filesort |
 ---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------ ---------- ----------------------------- 
1 row in set, 1 warning (0.00 sec)

7. 在最后一次多重合并时,只有记录的指针(排序索引键的最后部分)写到结果文件中去。

当表中有一个索引a_desc_b_asc (a DESC, b ASC)时,以下是上述6个查询的性能指标。

8. 通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作,MySQL将记录指针读取放到一个大的块里,并且使用它来按序读取记录,将记录放到缓冲中。缓冲的大小由系统变量read_rnd_buffer_size 的值而定。这个步骤的代码在源文件`sql/records.cc' 中。

数据大小为1000万行。在MySQL-5.7中,它是a_asc_b_asc(a ASC, b ASC),因为不支持倒叙索引。

这个逼近算法的一个问题是,数据库读取了2次记录:一次是估算WHERE 分句时,第二次是排序时。尽管第一次都成功读取记录了(例如,做了一次全表扫描),第二次是随机的读取(索引键已经排好序了,但是记录并没有)。在MySQL 4.1 及更新版本中,filesort 优化算法用于记录中不只包括索引键值和记录的位置,还包括查询中要求的字段。这么做避免了需要2次读取记录。改进的filesort 算法做法大致如下:

图片 1

  1. 跟以前一样,读取匹配WHERE 分句的记录。

性能指标的解释:

2. 相对于每个记录,都记录了一个对应的;‘元组’信息信息,包括索引键值、记录位置、以及查询中所需要的所有字段。

1, 对于查询1,也即ORDER BY a DESC;:
我们看到查询1中性能的提升,因为请求的语句排序是“a”列的DESC
译者注:因为MySQL8.0中可以建立倒叙索引,查询1按照a字段desc排序,直接走正向(forwarded)索引扫描即可完成查询,
避免了在MySQL5.7中查询出来数据之后再进行排序操作的步骤

  1. 根据索引键对‘元组’信息进行排序。

2,对于查询2:
由于查询2的排序为正序(译者注:与索引的顺序相反,因此需要反向扫描),由于反向索引扫描,
在MySQL-8.0中(相对于查询1)执行向反向索引扫描需要更多的时间
(注意,从图中可以看出,MySQL-8.0总体上表现更好。MySQL 5.7中正向索引扫描,与MySQL 8.0中反向索引扫描花费的时间(几乎)相同)

4. 按序读取记录,不过是从已经排序过的‘元组’列表中读取记录,而非从数据表中再读取一次。

3,对于查询3 也即ORDER BY a DESC, b ASC;:
查询3的排序方式与查询1类似,然而在MySQL-5.7中,对于任何请求混合顺序的查询,会对查询结果重新排序,因此性能差别是巨大的。

 

4,对于查询4 也即 ORDER BY a ASC, b DESC;
可以看到,在MySQL 8.0中,查询4执行的是反向索引扫描,因此比查询3花费了更多的时间,
尽管如此,在查询5和查询6中,排序的方式是(a DESC, b DESC)/(a ASC, b ASC),不管是正向扫描还是反向扫描,都无法满足排序需求,因此会用到filesort
但是,在这种情况下,由于在MySQL-5.7中ASC/DESC索引标志被忽略(译者注:MySQL 5.7中没有正向和反向索引的概念),因此MySQL-5.7可以使用(正向/反向)索引扫描来给出请求的顺序。

使用改进后的filesort 算法相比原来的,‘元组’比‘对’需要占用更长的空间,它们很少正好适合放在排序缓冲中(缓冲的大小是由sort_buffer_size 的值决定的)。因此,这就可能需要有更多的I/O操作,导致改进的算法更慢。为了避免使之变慢,这种优化方法只用于排序‘元组’中额外的字段的大小总和超过系统变量max_length_for_sort_data 的情况(这个变量的值设置太高的一个表象就是高磁盘负载低CPU负载)。想要提高ORDER BY 的速度,首先要看MySQL能否使用索引而非额外的排序过程。如果不能使用索引,可以试着遵循以下策略:

5,如果用户想要避免查询5和查询6的filesorts,可以修改表以添加一个键(a ASC, b ASC)。
此外,如果用户也想避免反向索引扫描,可以同时添加(a ASC, b DESC)和(a DESC, b DESC)。

* 增加sort_buffer_size 的值。

下面是添加了第5点下的额外索引后的MySQL-5.7.14和MySQL-8.0-labs的最后对比:

* 增加read_rnd_buffer_size 的值。

图片 2

* 修改tmpdir,让它指向一个有很多剩余空间的专用文件系统。

注意,在MySQL-5.7中,我们不能添加额外的索引来提高上述查询的性能。
而且,有了这个特性,在某些情况下可以避免物化,比如在连接中的第一个表上请求混合顺序。
在一些用例中,反向索引提高了性能。区间扫描访问方法也使用反向索引。
虽然并不是所有的范围扫描访问方法都使用反向索引,但我们将在未来尝试消除这些限制。

如果使用MySQL 4.1或更新,这个选项允许有多个路径用循环的格式。各个路径之间在Unix 上用冒号(':')分隔开来,在Windows,NetWare以及OS/2 上用分号(';')。可以利用这个特性将负载平均分摊给几个目录。注意:这些路径必须是分布在不同物理磁盘上的目录,而非在同一个物理磁盘上的不同目录

改进

  

随着倒序索引(反向索引)的引入,我们已经删除了对隐式排序的支持,结果是作为GROUP BY的一部分提到的列的升序。
除了上述改进外,我们还看到在一些情况下性能得到了改善,这些情况下的顺序是隐含的,但可能不是必需的。

BY 或GROUP BY 子句而无需做额外的排序。尽管ORDER BY 不是和索引的顺序准确匹配,索引...

 

总结

我们很高兴能够解决MySQL社区长期存在的功能请求之一。请了解倒叙索引的特性,让我们知道你的想法!

 

 

本文由澳门新萄京发布于数据库,转载请注明出处:By索引优化,如何优化

上一篇:澳门新萄京:MD5注意事项,SQL将原始数据开展M 下一篇:没有了
猜你喜欢
热门排行
精彩图文