8.优化

 

8.1 优化概述

数据库性能依赖于多个因素，在数据库级别，如表，查询，配置。硬件级别如cpu，IO。

一般的用户可以使用现有工具来获得最好的性能，高级用户可以通过修改代码来提高性能。

Ÿ   数据库级别的优化

s   使用合适表结构，定义合适的字段类型，如果定义的太大，当数据量大的时候会出明显的空间浪费。

s   使用合适的索引，来提高查询性能

s   保证每个表使用了合适的引擎，要注意非事务引擎和事务引擎的区别

s   每个表是否有合适的行格式，如，压缩表读写的时候比普通表耗的IO要小。

s   应用程序是否使用合适的锁策略，innodb存储引擎的锁机制提供了很好的并发性。

s   所有用来缓存的内存，大小是否合适。主要是innodb的buffer pool和myisam的key cache和mysql的query cache。

Ÿ   硬件级别优化

任何db上的问题都是硬件造成的问题，而dba是调整应用程序，或者重新配置服务来避免。

s   磁盘查找（Disk seeks），现在的磁盘，平均时间是10ms，也就是说1s理论上能运行100次。如果出现瓶颈为了优化可以把数据放到不同的磁盘。

s   磁盘读写（Disk reading and writing），现在的磁盘吞吐量能够达到10-20MB/s

s   CPU

s   内存带宽，当cpu需要的数据超过cpu cache，那么主存的带宽就会变成瓶颈，虽然对大多数系统来说不可能成为瓶颈，但是还是要注意。

Ÿ   可移植性和性能之间平衡

为了性能还可以使用/*! */对优化器进行提示。

8.2 优化SQL语句

8.2.1 优化SELECT语句

8.2.1.1 SELECT语句的速度

主要考虑一下几点来优化：

Ÿ   为了让一个select….where…语句速度变快，首先检查是否可以增加一个索引。按where子句来创建索引可以加快评估，过滤和结果获取。为了避免索引而导致空间浪费，尽量使用小索引。

可以使用explain语句确定索引是否用户查询

Ÿ   隔离和跳转查询的任意部分，比如函数，在查询中调用函数可能一个查询调用一次，也可能每行调用一次。

Ÿ   最少化表扫描，特别是大表

Ÿ   定期使用ANALYZE TABLE语句，保持统计信息最新。

Ÿ   调整基数，索引技术和参数配置。

Ÿ   避免变化语句，而导致语句难以理解

Ÿ   如果性能问题不能通过简单的调整解决，那么就查看查询内部，研究EXPLAIN语句返回并调整索引，where子句，join子句，等等。

Ÿ   调整mysql用来缓存的内存区的大小。主要是innodb的buffer pool和myisam的key cache。

Ÿ   尽管使用内存语句执行很快，但是也要调整减少内存使用，提高可扩展性。

Ÿ   处理锁问题。

8.2.1.2 WHERE子句优化

Where子句出现在delete，insert，update，select语句中，本节以select为例。

注意：Mysql的优化器是不断在更新的，所以不是所有的优化被写入文档

你可能获取优化语句，但是mysql优化器也会自动去做相同的工作，所以应该去做让语句更加易懂的工作。一些优化方法：

Ÿ   删除没必要的括号

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))

-> (a AND b AND c) OR (a AND b AND c AND d)

Ÿ   常亮合并

  (a<b AND b=c) AND a=5

-> b>5 AND b=c AND a=5

Ÿ   删除常量条件

(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)

-> B=5 OR B=6

Ÿ   索引评估常量表达式只使用一次

Ÿ   Count(*)一个简单的表，没有where直接来至于myisam和memory表信息。对于not null使用在单表上也是如此。

Ÿ   快速发现不可用的表达式，mysql会快速诊断出一些select语句永远没有返回行。

Ÿ   在没有group by或者聚合函数情况下，having和where合并。

Ÿ   对于每个join的表，简单的where可以快速的被评估，并且会尽快的跳过行。

Ÿ   所有的常量表都会被先读入，常量表定义：

s   空的表或者只有一行的表

s   使用了primary key或者unique索引，的所有key进行对常量表达式的比较。

SELECT * FROM t WHERE primary_key=1;

SELECT * FROM t1,t2

  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

Ÿ   最好的join组合是通过尝试所有join的可能找到的，如果所有的order by，group by列都来自同一个表，那么这个表会被优先读入。

Ÿ   如果启动了SQL_SMALL_RESULT选项，那么MySQL会使用in-memory临时表

Ÿ   如果表的索引被查询，最好的索引将会被使用，除非优化器坚信表扫描性能更好。

Ÿ   当索引覆盖的时候，mysql可以从索引直接读入数据，不需要去读原始记录。

Ÿ   在行输入之前，哪些不满足having子句的会被跳过。

8.2.1.3 Range优化

range访问是使用索引获取表记录的子集。

8.1.2.3.1 简单索引(Single-Part Index)的Range访问

定义简单索引Range条件如下:

Ÿ   对于BTREE和HASH索引，使用常量来比较key，使用=，<=>,in(),is null或者is not null。

Ÿ   对于BTREE还可以使用>,<,>=,<=,BETWEEN,!=(<>)或者LIKE操作。

Ÿ   对于对个range条件，通过使用or或者and组合成一个range条件

常量值：1.来自查询，2.来自const或者system表，3.不相关子查询的结果，4.前面几种情况的表达式。

 

range条件的例子：

SELECT * FROM t1

  WHERE key_col > 1

  AND key_col < 10;

SELECT * FROM t1

  WHERE key_col = 1

  OR key_col IN (15,18,20);

SELECT * FROM t1

  WHERE key_col LIKE ‘ab%‘

  OR key_col BETWEEN ‘bar‘ AND ‘foo‘;

在常量生成阶段，某些非常量值可以转化成常量值。

 

mysql会视图每个可能的索引获取range条件，在获取range条件时，不满足的条件会被删除(设为true)，重叠的range条件会被合并，空的条件会被删除。

如:key1是索引列，nonkey不是索引

SELECT * FROM t1 WHERE

  (key1 < ‘abc‘ AND (key1 LIKE ‘abcde%‘ OR key1 LIKE ‘%b‘)) OR

  (key1 < ‘bar‘ AND nonkey = 4) OR

  (key1 < ‘uux‘ AND key1 > ‘z‘);

1.获取原始where子句

(key1 < ‘abc‘ AND (key1 LIKE ‘abcde%‘ OR key1 LIKE ‘%b‘)) OR

(key1 < ‘bar‘ AND nonkey = 4) OR

(key1 < ‘uux‘ AND key1 > ‘z‘)

2.删除nonkey=4和key1 like ‘%b’因为不能用于range scan，删除的做法是把这2个条件设为true，这样条件就不会被减少。

(key1 < ‘abc‘ AND (key1 LIKE ‘abcde%‘ OR TRUE)) OR

(key1 < ‘bar‘ AND TRUE) OR

(key1 < ‘uux‘ AND key1 > ‘z‘)

3.折叠总是为true或者false的条件

(key1 LIKE ‘abcde%‘ OR TRUE) 总是为true

(key1 < ‘uux‘ AND key1 > ‘z‘) 总为false

(key1 < ‘abc‘ AND TRUE) OR (key1 < ‘bar‘ AND TRUE) OR (FALSE)

删除没必要的true和flase条件

(key1 < ‘abc‘) OR (key1 < ‘bar‘)

4.合并条件

(key1 < ‘bar‘)

 

通常，用于range scan的条件限制比较少，然后mysql执行检查过滤出满足range条件的记录。而不是过滤整个where子句。

 

range条件提取算法可以处理任意深度的and/or嵌套，并且输出和range条件在where子句中的位置无关。

 

目前，mysql还不支持在空间索引上进行range条件合并。因为这个限制，可以使用union2个独立select语句，然后把空间每个空间谓词放到各自的select中。

8.1.2.3.2 复合索引(Multiple-Part Index)Range访问

复合索引上的Range条件是简单索引上的Range条件的扩展。在复合索引下，Range条件是使用key组合。

如：

key_part1  key_part2  key_part3

  NULL       1          ‘abc‘

  NULL       1          ‘xyz‘

  NULL       2          ‘foo‘

   1         1          ‘abc‘

   1         1          ‘xyz‘

   1         2          ‘abc‘

   2         1          ‘aaa‘

如：条件key_part1=1定义的区间为：

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

以下描述说明了在复合索引情况下range条件如何工作：

Ÿ   对于hash索引来说，每个区间表示相同的值，比较运算符只能是 =，<=>，is null,is not null。

Ÿ   对于btree，条件可以使用and连接，每个条件都可以使用>，<,>=,<=,!=,<>,between，like。

如条件：

key_part1 = ‘foo‘ AND key_part2 >= 10 AND key_part3 > 10

转化为区间后：

(‘foo‘,10,-inf) < (key_part1,key_part2,key_part3) < (‘foo‘,+inf,+inf)

Ÿ   如果条件覆盖的行是多个使用or连接的区间，那么形成一个条件覆盖的行是多个区间的并集。如果是用and连接的，那么会形成一个条件覆盖的行是多个区间的交集。

如：

(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)

区间是：

(1,-inf) < (key_part1,key_part2) < (1,2)

(5,-inf) < (key_part1,key_part2)

在explain中key_len是最大key前缀。key_len可以说吗key使用的情况，但是可能和预期的不一样。

8.1.2.3.3 多值等式Range优化

多值等式的情况：

col_name IN(val1, ..., valN)

col_name = val1 OR ... OR col_name = valN

如果col_name等于其中的某一些值，serveral就为true。等式range读取符合条件的行花费评估如下：

Ÿ   如果在col_name上有唯一索引，行评估为1，因为一个给定值只能有一行。

Ÿ   否则使用索引的统计信息。

使用index dive，优化器为每个range dive，使用range内的行数作为评估。

如col_name in (10,20,30)，三个等式range，然后为每个range做2个dive，生成一个行评估。每对dive为给定值生成评估。

index dive提供了准确的行评估，当比较值越多，评估时间也就越大，使用统计信息评估准确率稍微低一点，但是速度比index dive评估快。

eq_range_index_dive_limit变量，说明可以等式数在变量值内那么可以使用。如果都要使用index dive那么把值设为0。如果N个等式一下都使用index dive那么设置为N+1。

 

使用ANALYZE TABLE来优化索引统计信息。

8.2.1.4 索引合并(Index Merge)优化

索引合并是通过合并多个range扫描为一个。合并可以产生交集，并集或者合并底层扫描的交集。这些合并方法只能合并单个表，不能合并多个表的扫描。

 

在EXPLAIN输出，索引合并是在type列中值为index_merge，key列包含了使用的索引，key_len指使用的索引最长key。

如：

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

 

SELECT * FROM tbl_name

  WHERE (key1 = 10 OR key2 = 20) AND non_key=30;

 

SELECT * FROM t1, t2

  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE ‘value%‘)

  AND t2.key1=t1.some_col;

 

SELECT * FROM t1, t2

  WHERE t1.key1=1

  AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

索引合并有以下几个算法：

Ÿ   Using intersect()

Ÿ   Using union()

Ÿ   Using sort_union()

注意：索引合并优化算法有以下已知的不足：

Ÿ   如果你的查询有复杂的where，and/or嵌套并且mysql没有使用最有的计划，尝试使用以下的样式：

    (x AND y) OR z = (x OR z) AND (y OR z)

(x OR y) AND z = (x AND z) OR (y AND z)

Ÿ   索引合并不能使用在全文索引。

Ÿ   在5.6.6前，如果range scan在一些key，优化器不会考虑使用索引合并

SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

这个查询计划2种可能：

1.在(goodkey1 < 10 OR goodkey2 < 20)  上使用索引合并

2.在badkey < 30上使用range scan

 

8.2.1.4.1 索引交集合并访问算法(The Index Merge Intersection Access Algorithm)

当where子句转为成了多个Range条件，用不同的key使用and连接，那么这个算法可用。每个条件是以下：

Ÿ   这个方式，索引有N个部分(索引所有部分都被覆盖)

key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

Ÿ   任何range条件在innodb表的primary key

如：

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

SELECT * FROM tbl_name

  WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

 

索引交集合并访问算法是同时scan多个索引按scan的接收顺序为交叉行顺序处理。

如果查询中使用的列覆盖了索引，那么不需要去表上读其他数据。EXPLAIN输出中extra为using index。如：

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

当所有没有覆盖所有列，那么就要去表上获取其他需要的列。

若其中一个合并条件是primary key不会另外取表上取行，而是从条件中获取。

8.2.1.4.2 索引并集合并访问算法(The Index Merge Union Acess Algorithm)

当where子句转化为多个range条件，并且条件之间使用or连接，那么算法可用，每个条件以下：

Ÿ   这个方式，索引有N个部分(索引所有部分都被覆盖)

key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

Ÿ   任何range条件在innodb表的primary key

Ÿ   索引交集合并算法可用的条件

如：

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

 

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR

  (key3=‘foo‘ AND key4=‘bar‘) AND key5=5;

8.2.1.4.3 索引排序合并访问算法(The Index Merge Sort-Union Access Algorithm)

当where子句转化为多个range条件，并且条件之间使用or连接，但是不适用于索引并集合并访问算法。

如：

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

 

SELECT * FROM tbl_name

  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

sort-union算法和union算法不同的是要在返回航之前先对行id进行排序。

8.2.1.5 引擎Pushdown条件优化

引擎pushdown条件，是条件被pushed down到了存储引擎来评估。这个优化只能在NDB引擎上有用。

对于mysql cluster来说，这个优化可以评估需要在cluster节点和mysql服务之间发送多少不必要的行，并且可以加快查询速度。

条件pushdown可以用在非索引列和常量比较。

CREATE TABLE t1 (

    a INT,

    b INT,

    KEY(a)

) ENGINE=NDB;

SELECT a, b FROM t1 WHERE b = 10;

mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 10

        Extra: Using where with pushed condition

但是不能用于以下两种情况：

SELECT a,b FROM t1 WHERE a = 10;

SELECT a,b FROM t1 WHERE b + 1 = 10;

第一个语句因为a上有索引，所以不能用pushdown，因为用索引访问更加有效。

第二个语句因为b并不是直接比较，所以也不能使用pushdown

其他可以pushdown的条件：

·         column [NOT] LIKE pattern

·         column IS [NOT] NULL

·         column IN (value_list)

·         column BETWEEN constant1 AND constant2

pattern必须是包含了模式匹配的字符串

value_list是常量或者字符常量

constant1和constant2必须是常量或者字符

引擎条件pushdown默认是启动的，要disable掉，需要设置optimizer_switch系统变量

[mysqld]

optimizer_switch=engine_condition_pushdown=off

或者在启动的时候

SET optimizer_switch=‘engine_condition_pushdown=off‘;

限制：引擎条件pushdown限制：

Ÿ   条件pushdown只支持NDB

Ÿ   列只能和常量比较或者常量表达式比较

Ÿ   BLOB，TEXT类型不能用于列比较

Ÿ   如果是比较字符串必须使用相同的排序规则

Ÿ   join不能直接支持，有可能的时候涉及到多个表会被分开push。

8.2.1.6 索引条件Pushdown优化

索引条件pushdown（icp）是mysql通过索引从表上获取数据的另一种优化。

如果没有使用icp存储引擎比那里索引来定位表中的行并且返回给mysql。

如果使用icp，若where的部分条件可以使用索引评估，mysql服务把这部分条件push到存储引擎，然后存储引擎通过索引项来评估这个push下来的条件。并且只有满足的行才能被读取。

icp可以减少存储过程必须访问基表的次数，和访问存储引擎的次数。

 

当索引条件pushdown不可用，索引扫描是如何处理的：

1.获取下一行，先读取索引组，然后使用索引组定位到表中的行。

2.然后测试where中的行，根据test结果，接受或者拒绝。

当索引条件pushdown可用，过程：

1.获取下一行索引组。

2.test应用到这个表的部分where条件，通过索引给的列进行test，如果条件不满足就获取一行的索引组。

3.如果条件满足使用索引组定位并且读取表行。

4.测试where中剩下的部分。根据测试结果决定是接受或者拒绝。

 

当使用索引条件pushdown的时候，在explain输出的时候extra列显示Using index condition，不会显示index only因为还会去表上读记录。

 

假设有个联系方式的表，里面有索引index(zipcode,lastname,firstname)。当你执行一下语句的时候：

SELECT * FROM people

  WHERE zipcode=‘95054‘

  AND lastname LIKE ‘%etrunia%‘

  AND address LIKE ‘%Main Street%‘;

MySQL会通过zipcode扫描people表，lastname并不能够使用来查找索引，所以没有索引条件pushdown，查询会获取说有zipcode为95054的所有记录。

使用了索引条件pushdown，mysql会先检查lastname，避免获取不能匹配lastname的记录。

 

索引条件pushdown默认启动，可以通过optimizer_switch系统变量设置index_condition_pushdown标记，来设置是否启动。

 

参考阅读：

 

8.2.1.7 使用索引扩展

innodb自动会为每个secondary索引扩展，为secondary key后面加上primary key。

CREATE TABLE t1 (

  i1 INT NOT NULL DEFAULT 0,

  i2 INT NOT NULL DEFAULT 0,

  d DATE DEFAULT NULL,

  PRIMARY KEY (i1, i2),

  INDEX k_d (d)

) ENGINE = InnoDB;

primary key在i1,i2。也定义了索引k_d在列d上，innodb内部扩展之后实际索引列为d,i1,i2。

 

在mysql 5.6.9之前，优化器选择索引的时候是不会去关心secondary上primary key列。

在mysql 5.6.9之后，被扩展的部分也会被考虑到，并且会产生很有效的查询计划和性能。

使用ref,range，index_merge索引访问都可以使用扩展的secondary索引。

INSERT INTO t1 VALUES

(1, 1, ‘1998-01-01‘), (1, 2, ‘1999-01-01‘),

(1, 3, ‘2000-01-01‘), (1, 4, ‘2001-01-01‘),

(1, 5, ‘2002-01-01‘), (2, 1, ‘1998-01-01‘),

(2, 2, ‘1999-01-01‘), (2, 3, ‘2000-01-01‘),

(2, 4, ‘2001-01-01‘), (2, 5, ‘2002-01-01‘),

(3, 1, ‘1998-01-01‘), (3, 2, ‘1999-01-01‘),

(3, 3, ‘2000-01-01‘), (3, 4, ‘2001-01-01‘),

(3, 5, ‘2002-01-01‘), (4, 1, ‘1998-01-01‘),

(4, 2, ‘1999-01-01‘), (4, 3, ‘2000-01-01‘),

(4, 4, ‘2001-01-01‘), (4, 5, ‘2002-01-01‘),

(5, 1, ‘1998-01-01‘), (5, 2, ‘1999-01-01‘),

(5, 3, ‘2000-01-01‘), (5, 4, ‘2001-01-01‘),

(5, 5, ‘2002-01-01‘);

在不考虑扩展索引的情况下，因为k_d只被当做是d：

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01‘\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: ref

possible_keys: PRIMARY,k_d

          key: k_d

      key_len: 4

          ref: const

         rows: 5

        Extra: Using where; Using index

 

在考虑扩展扩展索引的情况下，k_d被当做是d,i1,i2所以查询可以使用，d,i1

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01‘\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: ref

possible_keys: PRIMARY,k_d

          key: k_d

      key_len: 8

          ref: const,const

         rows: 1

        Extra: Using index

2个语句都是使用了k_d索引，但是explain语句输出切实不同：

Ÿ   key_len从4字节变成8字节，说明lookup使用了d,i1,而不是d。

Ÿ   ref从const变成了const，const，索引是2个key而不是1个。

Ÿ   rows从5变成了1，说明读取的行更加少。

Ÿ   extra值从using where;using index变成了using indexs说明只需要从index上获取数据就可以满足

是否考虑扩展索引，也可以冲show status上看出不同

FLUSH TABLE t1;

FLUSH STATUS;

SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01‘;

SHOW STATUS LIKE ‘handler_read%‘

flush table，flush status刷新表cache和清理status计数。

 

没有索引扩展，show status输出

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| Handler_read_first    | 0     |

| Handler_read_key      | 1     |

| Handler_read_last     | 0     |

| Handler_read_next     | 5     |

| Handler_read_prev     | 0     |

| Handler_read_rnd      | 0     |

| Handler_read_rnd_next | 0     |

+-----------------------+-------+

使用索引扩展，show status过程产生结果。Handler_read_next值从5到1，说明了索引扩展更加有效。

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| Handler_read_first    | 0     |

| Handler_read_key      | 1     |

| Handler_read_last     | 0     |

| Handler_read_next     | 1     |

| Handler_read_prev     | 0     |

| Handler_read_rnd      | 0     |

| Handler_read_rnd_next | 0     |

+-----------------------+-------+

optimizer_switch系统变量use_index_extensions标记。默认是启动的，可以通过禁用功能来检查性能是否有提升。

SET optimizer_switch = ‘use_index_extensions=off‘;

索引扩展搜到key个数的限制（16个）和可以长度的限制（3072字节）。

8.2.1.8 IS NULL优化

在MySQL中col_name is null 和col_name=constant_value使用相同的优化，mysql中可以使用is null来查找null值。

如果where子句中，对一个not null列使用了is null那么就会被优化掉。这个优化就不可能会在有可能为null的列中出现，比如left join的右侧表。

 

MySQL也能优化col_name=exp or col_name is null，当优化器使用的时候explain会显示ref_or_null。

ref_or_null，先按给的值读，然后读值为null的。

注意，优化器只能处理一个is null，如下面情况，只能处理t1.a=t2.a and t2.a is null不能处理b上面的

SELECT * FROM t1, t2

  WHERE (t1.a=t2.a AND t2.a IS NULL)

  OR (t1.b=t2.b AND t2.b IS NULL);

8.2.1.9 LEFT JOIN和RIGHT JOIN优化

Mysql实现A LEIFT JOIN B join_condition如下：

Ÿ   表B依赖于表A和表A依赖的表。

Ÿ   表A依赖于除了表B之外使用在LEFT JOIN上的表

Ÿ   LEFT JOIN条件用来决定如何从表B上获取数据

Ÿ   所有标准的join优化都会被执行，除了表一定要读完依赖的表之后才读。如果出现循环的依赖关系，mysql就会报错。

Ÿ   所有的标准where都会被执行

Ÿ   如果A中有一行满足where条件，但是没有对应的B中的记录，那么就生产一个全是null的B的记录

Ÿ   如果使用left join是为了发现哪些行不再另外一些表中，可以做以下测试，在where中使用col_name is null，col_name被定义为not null的。

 

RIGHT JOIN和LEFT JOIN类似，只是角色换了一下。

优化器会计算join的顺序，但是如果使用left join或者straight join会帮助优化器更快的工作，因为已经强制了join顺序，如果使用一下类型，mysql会全扫描b，因为left join强制要求在d之前读取

SELECT *

  FROM a JOIN b LEFT JOIN c ON (c.key=a.key)

  LEFT JOIN d ON (d.key=a.key)

  WHERE b.key=d.key;

可以通过调换 a和b的顺序来调整

SELECT *

  FROM b JOIN a LEFT JOIN c ON (c.key=a.key)

  LEFT JOIN d ON (d.key=a.key)

  WHERE b.key=d.key;

对于生产null来说where 条件总是错误的，那么left join会转化为 inner join

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

理论上来说转化为inner join是没问题的

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

8.2.1.10 嵌套循环join算法

8.2.1.10.1 嵌套循环join算法

一个简单的嵌套循环连接算法，是从第一个表读入然后一行一行的传入嵌套内表的。

假设有表t1,t2,t3

Table   Join Type

t1      range

t2      ref

t3      ALL

join执行类似于：

for each row in t1 matching range {

  for each row in t2 matching reference key {

    for each row in t3 {

      if row satisfies join conditions,

      send to client

    }

  }

}

因为嵌套循环连接每次传递一行到内表中，通常要在内表处理好多次。

8.2.1.10.2 块嵌套循环Join算法(Block Nested-Loop Join Algorithm)

块嵌套循环连接算法，会对外表的行进行缓存来减少在内表的读取时间。以数量级方式，减少了内表表读取次数。

MySQL使用连接缓存(join buffer)有一下几个条件：

Ÿ   join_buffer_size系统变量决定了每个join buffer的大小

Ÿ   连接缓存可以用于ALL,index,range连接类型

Ÿ   每个join都有一个缓存，所以一个查询可能有多个缓存。

Ÿ   连接缓存不会为第一个非常量表，甚至是类型为ALL或者index。

Ÿ   连接缓存在执行连接的时候被分配，然后在查询完成后被释放。

Ÿ   只有join感兴趣的列会被保存在连接缓存，并不会保存所有的行

 

for each row in t1 matching range {

  for each row in t2 matching reference key {

    store used columns from t1, t2 in join buffer

    if buffer is full {

      for each row in t3 {

        for each t1, t2 combination in join buffer {

          if row satisfies join conditions,

          send to client

        }

      }

      empty buffer

    }

  }

}

if buffer is not empty {

  for each row in t3 {

    for each t1, t2 combination in join buffer {

      if row satisfies join conditions,

      send to client

    }

  }

}

如果t1,t2保存在连接缓存的组合大小为S，组合个数为C，那么t3扫描次数是：

(S * C)/join_buffer_size + 1

t3的扫描次数会随着join_buffer_si增加而减少，当连接缓存增加到了可以包含所有记录，在大也不能减少扫描次数。

8.2.1.11 嵌套连接优化

table_factor在标准sql上被扩展，不接受括号里面都是表的方式。

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)

                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

可以转化为

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)

                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

在mysql中 inner join和cross join是相等的，但是在标准的SQL是不相等的。

通常只有inner join操作括号可以忽略，对于out join括号不能去掉或者变化，都有可能带来结果的不同。

准确的说是在left join的右边不能忽略括号，在right join的左边不能忽略括号。

 

外连接的算法和内连接的算法是相似的

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)

                 INNER JOIN T3 ON P2(T2,T3)

  WHERE P(T1,T2,T3).

如果P(T1,T2,T3)可以做一下拆分：

P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).

算法会变成：

(T2 LEFT JOIN T3 ON P2(T2,T3))

FOR each row t1 in T1 such that C1(t1) {

  FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2)  {

    FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {

      IF P(t1,t2,t3) {

         t:=t1||t2||t3; OUTPUT t;

      }

    }

  }

}

会发现C1(T1) ， C2(T2) ， C3(T3)被push出了循环，如果C1(T1)是很好的过滤条件，那么会减少很多行到内表。

out join也类似。

具体看：

8.2.1.12 外连的例子

具体看：

8.2.1.13 多区间读优化(Multi-Range Read Optimization)

在secondary索引上使用range扫描会导致很多磁盘随机访问。Mysql通过MRR优化来减少磁盘随机读写：

1.先扫描所有手机相关的行

2.对key进行排序，然后通过primary key顺序从表上获取数据。

MRR算法是把随机读写转成顺序读写，来降低随机访问次数。

MRR优化提供了以下好处：

Ÿ   MRR让数据根据索引组，顺序访问而不是随机访问。服务获取一组符合查询条件的索引组，根据ROWID排序(默认primary key)，然后根据这个顺序获取数据。

Ÿ   MMR可以批量处理key访问请求，通过索引组访问数据行。如区间索引扫描，等式join以索引为join属性。

 

以下场景演示了使用MRR优化的好处：

场景1：MRR被用来innodb和myisam表的索引区间扫描和等式join

1.部分索引组被存到buffer

2.这些组以rowid被排序

3.数据组根据排序好的索引组顺序访问。

场景2：MRR在NDB表上进行索引区间扫描和等式join

1.部分range被存在提交查询节点的buffer中。

2.range被发送到需要执行的节点上

3.被访问的行被打包并发送到查询提交的节点

4.接受的包内的数据行被写入到buffer

5.数据行从buffer中被读取

 

当使用MMR，explain中的extra列为using MMR。

 

如果innodb和myisam表不需要访问表上的行，那么就不会使用MRR。因为数据可以从索引组上获取，没必要在访问表。

使用MRR的例子，index(key_part1,key_part2):

SELECT * FROM t

  WHERE key_part1 >= 1000 AND key_part1 < 2000

  AND key_part2 = 10000;

 

若不是用MRR，会扫描1000到2000的所有记录不管key_part2的值

若使用MRR，扫描会被分为多个range(1000,1001,…,1999)，每个单独的值都会去找key_part2=10000,若有很多组都不是10000，那么MRR只需要读取很少的行。

 

optimizer_switch系统变量中的mrr标记控制MRR优化是否启动。mrr_cost_based标记控制是否根据cost决定是否使用MRR，默认2个都是on。

 

对于MRR，read_rnd_buffer_size控制buffer的大小，read_rnd_buffer_size决定了一趟能够处理多少range。

 

8.2.1.14 块嵌套循环和批量Key访问连接(Block Nested-Loop and Batched Key Access Joins)

8.2.1.14.1 块嵌套循环和批量Key访问连接算法的连接buffer管理

在MySQL5.6，join buffer可以用来执行 inner join，out join和semi join。

join buffer的管理是很简洁有效的：对于NULL值buffer并不会为它分配跟多的字节，最小化分配给varchar类型的字节数。

 

代码支持2个类型的buffer，常规的和增量的，假设buffer B1用于t1和t2的join，join结果和t3 join使用B2:

Ÿ   如果是常规join buffer，B2是常规join buffer，那么每一行都包含B1的r1和t2感兴趣列的r2的组合。

Ÿ   如果是增量join buffer，只包含下一个join生产的。如果B2是增量buffer，B2只包含了t2的r2和指向r1的指针。

 

增量join buffer总是以增量的方式对应到之间的join buffer，所以第一个join操作总是常规的。如例子中，B1总是常规join buffer。

增量buffer中用来join操作的每一行都只包含来自被join表的被感兴趣的列。通过引用的方式。只要这些行可以匹配r，那么一些增量buffer中的行可以指向同一个行r。

增量buffer可以减少复制之前join产生的列，这样可以减少空间。因为减少了复制，也可以为处理节省时间。

 

optimizer_switch中的block_nested_loop和batched_key_access标记控制优化器使用这2个算法。默认block_nested_loop是启动的，batched_key_access是关闭的。

变量optimizer_join_cache_level控制了join buffer管理。

8.2.1.14.2 外连和半连的块嵌套循环算法(Block Nested-Loop Algorithm For Outer Joins and Semi Join)

MySQL 5.6之后，块嵌套循环算法才支持外连接和半连接。

如果使用外连接，每一个有第二个操作产生的行和join buffer中的行匹配，如果能匹配到一个扩展行就产生，通过下一个join进一步扩展。剩下的没有被扩展的行，都以null来扩展。

如果使用BNL，explain输出中的extra包含using join buffer并且type职位all，index，range。

8.2.1.14.3 批量key访问join(Batched Key Access Joins)

批量key访问(BKA)被应用在，在访问另外一个表时有索引可用。

1.BKA使用join buffer保存由join的第一个操作产生的感兴趣的数据。

2.然后BKA算法，生产key来访问被连接的表，并批量提交到数据库index查找，使用MRR接口提交keys。

3.提交keys之后，MRR使用最佳的方式来获取行。

MRR接口有2个应用场景：

场景1：应用于传统的基于磁盘的存储引擎(innodb,myisam)，对于这些引擎join buffer中keys是一次性提交到MRR，MRR通过key找到rowid，通过rowid来获取数据

场景2：用于远程存储引擎(NDB)，来自join buffer上的部分key，从SQL NODE发送到DATA NODE，然后SQL NODE会收到通过相关关系匹配的行组合。然后使用这些行组合匹配出新行。

              然后在发送新key，知道发完为止。

如果使用了BKA，explain的extra会显示using join buffer(batch key access)并且type为ref或者eq_ref。

8.2.1.15 ORDER BY优化

当MySQL使用索引能够满足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;

因为索引本来就是排序的按 key_part1, key_part2, key_part3…key_partn如果违反了都需要另外排序。

 

可以使用explain语句查看mysql是否使用索引解决了排序问题，如果extra列有using filesort索引没有解决这个问题。

mysql的filesort有2个算法，一个算法只是对需要order by的字段进行排序，另外一个修改后的方法是对所有字段进行排序。

通常优化器使用修改后的方法，除非有BLOB或者TEXT字段，不管什么算法，排序缓存都是sort_buffer_size。

原始的filesort算法如下：

1.读取匹配where条件的数据

2.对于每行，存排序的key和rowid到sort buffer

3.如果key和rowid都放在sort buffer中，就不需要创建临时文件。当sort buffer满了，运行快速排序，然后写入到临时文件。保留sorted block指针。

4.重复处理直到所有的行都被读取

5.执行merge buffer到另外一个文件，直到所有的在第一个文件中的block在了第二个文件中。

6.一直merge buffer直到剩下2个block

7.最后一次merge，只写入rowid到结果表

8.根据排序结果中的rowid顺序读取数据。（手册中还提到了一个优化方案，但是我不认为能起到优化作用）。

 

这个方法的一个问题是需要读2次数据，一次是读排序列，一次是排好之后读取数据输出，而且都是随机访问。

 

修改后的filesort算法避免两次读取，会记录排序规则，而不是rowid，也会记录被查询应用的列。算法如下：

1.读取匹配where的数据

2.记录排序列和查询用到的列

3.当buffer满，对排序列排序，放到临时文件。

4.当合并完临时文件之后，直接输出。

 

修改后的方法，列长于原来的方法。很有可能会产生大量IO，让排序变得很慢。为了避免这个问题，优化器会所有读取列的长度小于max_length_for_sort_data系统变量，才会选择修改后的算法。

当filesort完成，explain输出中extra会有using filesort，优化器跟踪输出中filesort_summary块：

"filesort_summary": {

  "rows": 100,

  "examined_rows": 100,

  "number_of_tmp_files": 0,

  "sort_buffer_size": 25192,

  "sort_mode": "<sort_key, additional_fields>"

}

其中sort mode就说了算法：

<sort_key,rowid>表示原始的算法

<sort_key,addtitional_filed>表示是修改后的算法

 

为了提高排序速度，可以检查是否可以使用索引，如果不能使用：

1.增加sort_buffer_size的大小

2.增加read_rnd_buffer_size的大小

3.通过表设计减少空间占用

4.修改tmpdir目录指向专用文件系统

如果order by没有使用索引，但是有limit子句，那么优化器可能可以避免合并临时文件，直接在内存中排序。

8.2.1.16 GROUP BY优化

最简单的方法，是GROUP BY子句然后扫描整个表，创建临时表，临时表中的数据是按分组顺序的(按分组字段顺序的)。

然后使用临时表发现各个分组，然后应用聚合函数。如果可以使用索引那么就可以避免创建临时表。

GROUP BY使用索引的先决条件是，group by列必须来自于同一个索引。而且group by顺序一定要和索引key顺序一样。

临时表的处理方法是否可以被索引访问替代取决于查询中使用了哪部分索引。

通过索引访问有2种方法：1.group和range谓词一起使用，2.先做range扫描，然后做group。

8.2.1.16.1 松散索引扫描(Loose Index Scan)

最有效的方法处理是通过索引直接获取group列。这样可以直接在索引上查找分组不需要考虑key是否满足where子句。

这样的访问只会考虑索引的一部分，所以称为松散的索引扫描(loose index scan)。

如果没有where子句，那么松散扫描直接读取需要的key，比读全部key要小。

如果where子句包含range谓词，松散扫描查找每个分组的第一个key，然后读取最好有可能的key数量。需要满足一下几点：

Ÿ   在单个表上查询

Ÿ   group by中的列，一定要和索引前缀匹配，如index(c1,c2,c3)，那么group by c1,c2可以适用，group by c2就不适用。

Ÿ   在select列表中可以使用的聚合函数是min，max而且只能用于同一列。这个列必须是在索引里面，并且在group by字段之后的，如 group by c1,c2, max(c3)。

Ÿ   所有的其他部分，除了group by的之外，必须是常量。也就是说必须等式常量来引用他们。除了max，min之外。

Ÿ   对于索引列，所有列值必须被索引，不能前缀。比如c1 varchar(20),index(c1(10))这样就不能使用松散索引扫描。

可以使用松散索引扫描的例子：

index(c1,c2,c3)，表t1(t1,t2,t3,t4)

SELECT c1, c2 FROM t1 GROUP BY c1, c2;

SELECT DISTINCT c1, c2 FROM t1;

SELECT c1, MIN(c2) FROM t1 GROUP BY c1;

SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;

SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

 

不能使用的例子：

Ÿ   聚合函数不是max,min

SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

Ÿ   gourp by中的列不是索引前缀

SELECT c1, c2 FROM t1 GROUP BY c2, c3;

Ÿ   查询应用了部分key，是来自group by字段的后面，但是没有使用等式比较常量，如果使用了where c3=const那么就可以适用

SELECT c1, c3 FROM t1 GROUP BY c1, c2;

松散索引扫描也可以使用在其他形式的聚合函数：

Ÿ   AVG(DISTINCT),SUM(DISTINCT),COUNT(DISTINCT)支持。

Ÿ   没有group by或者 distinct子句在查询中

Ÿ   前面提到的限制依然适用

 

可以适用的情况：

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

不能适用的情况：

SELECT DISTINCT COUNT(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;

8.2.1.16.2 紧凑索引扫描(Tight Index Scan)

紧凑索引扫描要不是全索引扫描要不是区间索引扫描。

当松散索引扫描不能使用的时候，依然可以避免创建临时表。如果where子句中有range条件，那么只读取满足条件的key。

否则执行全索引扫描。因为算法读取所有满足range条件的key，或者如果没有条件扫描整个索引，我们成为紧凑索引扫描。

使用紧凑索引扫描，分组操作只有在所有key照完之后执行。

算法在使用等式比较所有查询引用的列生效，只有等式常量能够填上查询key的间隙，才有可能形成索引的前缀，使用索引前缀来进行索引查找。

这样mysql可以避免额外的排序操作直接可以从索引中顺序获取。