8.优化

8.优化... 1

8.1 优化概述... 1

8.2 优化SQL语句... 1

8.2.1 优化SELECT语句... 1

8.2.1.1 SELECT语句的速度... 1

8.2.1.2 WHERE子句优化... 1

8.2.1.3 Range优化... 1

8.2.1.4 索引合并(Index Merge)优化... 1

8.2.1.5 引擎Pushdown条件优化... 1

8.2.1.6 索引条件Pushdown优化... 1

8.2.1.7 使用索引扩展... 1

8.2.1.8 IS NULL优化... 1

8.2.1.9 LEFT JOIN和RIGHT JOIN优化... 1

8.2.1.10 嵌套循环join算法... 1

8.2.1.11 嵌套连接优化... 1

8.2.1.12 外连的例子... 1

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

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

8.2.1.15 ORDER BY优化... 1

8.2.1.16 GROUP BY优化... 1

8.2.1.17 DISTINCT 优化... 1

8.2.1.18 子查询优化... 1

8.2.1.19 优化limit语句... 1

8.2.1.20 如何避免表扫描... 1

8.2.2 优化DML语句... 1

8.2.2.1 加快insert语句... 1

8.2.2.2 加快update语句... 1

8.2.2.3 加快delete语句... 1

8.2.3 优化数据库权限... 1

8.2.4 优化INFORMATION_SCHEMA查询... 1

8.2.5 其他优化提示... 1

8.3 优化和索引... 1

8.3.1 MySQL如何使用索引... 1

8.3.2 使用Primary Keys1

8.3.3 使用外键(Foreign Key)1

8.3.4 列索引(Column Indexes)1

8.3.4.1 Prefix Indexes1

8.3.4.2 全文索引... 1

8.3.4.3 空间索引... 1

8.3.4.4 MEMORY存储引擎中的索引... 1

8.3.5 复合索引(Mutiple-column Indexes)1

8.3.6 验证索引使用... 1

8.3.7 Innodb和MyISAM索引统计信息收集... 1

8.3.8 比较btree和hash索引... 1

8.3.8.1 btree索引特点... 1

8.3.8.2 Hash索引特点... 1

8.4 优化数据库结构... 1

8.4.1 优化数据大小... 1

8.4.1.1 表列... 1

8.4.1.2 行格式(Row Format)1

8.4.1.3 索引... 1

8.4.1.4 Joins1

8.4.1.5 常规(Normalization)1

8.4.2 优化MySQL数据类型... 1

8.4.2.1 数值类型优化... 1

8.4.2.2 优化字符和字符串类型... 1

8.4.2.3 优化blob数据类型... 1

8.4.2.4 使用PROCEDURE ANALYSE. 1

8.4.3 表的优化... 1

8.4.3.1 mysql如何打开和关闭表... 1

8.4.3.2 不要再同一个数据库中创建多个表... 1

8.4.4 MySQL如何使用内部临时表... 1

8.5 优化innodb表... 1

8.5.1 优化innodb表存储引擎的layout1

8.5.2 优化innodb事务管理... 1

8.5.3 优化innodb logging. 1

8.5.4 innodb表的批量数据导入... 1

8.5.5 innodb查询优化... 1

8.5.6 优化innodb DDL操作... 1

8.5.7 优化innodb磁盘I/O.. 1

8.5.8 优化innodb配置变量... 1

8.5.9 优化innodb多表情况... 1

8.6 优化MyISAM表... 1

8.6.1 优化MyISAM查询... 1

8.6.2 myisam表批量导入... 1

8.6.3 加快REPAIR TABLE. 1

8.7 MEMORY表的优化... 1

8.8 理解查询执行计划... 1

8.8.1 使用explain来优化查询... 1

8.8.2 EXPLAIN输出格式... 1

8.8.2.1 EXPLAIN输出的列... 1

8.8.2.2 EXPLAIN join类型... 1

8.8.2.3 EXPLAIN 扩展信息... 1

8.8.2.4 EXPLAIN 输出说明... 1

8.8.3 EXPLAIN EXTENDED输出格式... 1

8.8.4 评估查询性能... 1

8.8.5 控制查询优化器... 1

8.8.5.1 控制执行计划评估... 1

8.8.5.2 控制开关优化(Controlling Switchable Optimizations)1

8.9 Buffering and Caching. 1

8.9.1 innodb的buffer pool1

8.9.1.1 指导... 1

8.9.1.2 内部细节... 1

8.9.1.3 配置选项... 1

8.9.1.4 监控buffer pool1

8.9.2 MyISAM的Key Cache. 1

8.9.2.1 共享key cache访问... 1

8.9.2.2 多个key cache. 1

8.9.2.3 Midpoint插入策略... 1

8.9.2.4 索引预载入... 1

8.9.2.5 key cache块大小... 1

8.9.2.6 重新构建key cache. 1

8.9.3 MySQL查询Cache. 1

8.9.3.1 Query Cache如何工作... 1

8.9.3.2 Query Cache的SELECT选项... 1

8.9.3.3 Query Cache配置... 1

8.9.3.4 Query Cache状态和维护... 1

8.9.4 缓存prepared语句和存储程序... 1

8.10 优化锁操作... 1

8.10.1 内部锁方法... 1

8.10.1.1 行级别锁定... 1

8.10.1.2 表级别锁定... 1

8.10.2 发生表锁(Table Locking Issues)1

8.10.2.1 使用innodb的性能考虑... 1

8.10.2.2 锁定的性能问题... 1

8.10.3 并发插入... 1

8.10.4 元数据锁定... 1

8.10.5 外部锁定... 1

8.11 优化MySQL服务... 1

8.11.1 系统因素和启动参数调整... 1

8.11.2 优化服务参数... 1

8.11.3 优化磁盘I/O.. 1

8.11.3.1 使用符号链接(Using Symbolic Links)1

8.11.4 优化内存使用... 1

8.11.4.1 MySQL如何使用内存... 1

8.11.4.2 Large Page的支持... 1

8.11.5 优化网络使用... 1

8.11.5.1 MySQL如何为客户端连接使用线程... 1

8.11.5.2 Host Cache和DNS查找优化... 1

8.11.6 线程池插件... 1

8.11.6.1 线程池组件和安装... 1

8.11.6.2 线程池操作... 1

8.11.6.3 调整线程池... 1

8.12 性能测试... 1

参考... 1

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优化器也会自动去做相同的工作，所以应该去做让语句更加易懂的工作。一些优化方法：

Ÿ   删除没必要的括号

   ((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()

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标记，来设置是否启动。

参考阅读：浅析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也类似。

具体看：http://dev.mysql.com/doc/refman/5.6/en/nested-join-optimization.html

8.2.1.12 外连的例子

具体看：http://dev.mysql.com/doc/refman/5.6/en/outer-join-simplification.html

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可以避免额外的排序操作直接可以从索引中顺序获取。

假设index(c1,c2,c3)在表table(c1,c2,c3,c4)，下面的查询不支持松散索引扫描，但是支持紧凑索引扫描

Ÿ   虽然有空隙但是已经被where c2=’a’填补

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

Ÿ   虽然group by不是和索引第一位匹配，但是where中提供了和常量的比较

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

8.2.1.17 DISTINCT 优化

distinct配合order by 很多时候都需要创建临时表。distinct是gourp的特例。所以在group上的优化也可以应用到distinct上。

如果使用了limit配合distinct，mysql会在到达limit的时候马上停止。

如果查询并没有使用全部的列，Mysql就会在找到一个匹配之后停止扫描。

8.2.1.18 子查询优化

mysql查询优化，有不同的策略来评估子查询：

对于in或者any：

Ÿ   semi-join

Ÿ   物化

Ÿ   exists策略

对于not in子查询：

Ÿ   物化

Ÿ   exists策略

8.2.1.18.1 使用semi-join方式优化子查询

mysql 5.6.5之后优化器使用semi-join策略提高子查询性能。

对于inner join 2张表，join返回和一行匹配的所有行。但是对于一些查询，只需要知道是不是有匹配，不需要关心能匹配多少。

假设有2个表，class和roster，为了显示有学生报名的class：

SELECT class.class_num, class.class_name

FROM class INNER JOIN roster

WHERE class.class_num = roster.class_num;

这样出现很多重复的class，可以用distinct去重复，但是效率不怎么样。

也可以使用以下获取：

SELECT class_num, class_name

FROM class

WHERE class_num IN (SELECT class_num FROM roster);

这样的语句，查询可以用semi-join执行，这个操作只为每个class中的行返回一个roster的实例。

在mysql中，子查询只有满足这些条件才能使用semi-join：

Ÿ   必须是in或者any子查询在where或on子句中。

Ÿ   必须是一个select，没有union

Ÿ   不能包含group by，having或者聚合函数

Ÿ   不能有使用order by配合limit

Ÿ   join表个数不能多于join限制

子查询可以是关联的也可以是非关联的。distinct和limit都是允许的，limit和order by一起用是不运行的。

若子查询满足上面那段条件，mysql会把它转化成semi-join，根据cost有一下策略：

Ÿ   把子查询传化为join，或者使用table pullout，然后在子查询表和外表之间使用inner join。

Ÿ   去重复，把semi join当做是join，并使用临时表去除重复记录

Ÿ   firstmatch，当扫描内表，对给定值进行分组，然后在分组结果中选一个返回

Ÿ   loosescan，使用索引扫描子查询表，然后从子查询执行结果的分组中选出一个值

Ÿ   物化，把子查询物化到临时表，然后使用索引和临时表做join，索引可以用来去重复，或者join时用于lookup，如果没有那么就可能要表扫描了。

除了去除重复，其他的都可以通过设置optimizer_switch的semijoin标记来控制。如果semijoin为on，可以单独通过firstmatch，loosescan，materialization。

控制firstmatch，loosescan，物化。这些标记默认为on。

semi-join策略会在explain中体现：

Ÿ   explain extended，show warnings显示了重写的语句。如果子查询被转化为semi-join，会发现子查询的谓词合并到了外查询的where中。

Ÿ   对于去重复，在extra劣种会出现start temporary，end temporary。

Ÿ   firstmatch(tbl_name)在extra列中说明join shortcutting。

Ÿ   loosescan(m..n)在extra列中说明LooseScan策略，m..n是key部分。

Ÿ   如果select_type为MATERIALIZED并且table为<subqueryN>说明。说明临时表用于子查询物化。

8.2.1.18.2 使用子查询物化优化子查询

mysql 5.6.5，优化器使用子查询物化策略，可以让子查询更加有效的被处理。

如果物化没有被使用，优化器有时候会重写非关联子查询变成关联子查询。如：

SELECT * FROM t1

WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

变成

SELECT * FROM t1

WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

子查询物化使用临时表，避免子查询执行多次。物化可以提高查询执行，临时表生产一搬都会在内存中。

物化产生的临时表一般在内存中，如果太大会被放到磁盘。

是否使用子查询物化，由optimizer_switch中的materialization标记决定，当谓词满足任一条件都可以使用：

Ÿ   谓词是以下形式，并且oe_i或者ie_i没有null

(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)

Ÿ   在这个方式下oe,ie可以为null

oe [NOT] IN (SELECT ie ...)

Ÿ   谓词是in或者not in结果为null和false一样

通过查看explain可以说明子查询是否物化。select_type会从DEPENDENT SUBQUERY变成SUBQUERY，这个说明会为外表每一行都执行一次。

物化可以让子查询只运行一次。另外explain extended，show warnings会包含，materialize materialize和materialize-subquery。

8.2.1.18.3 优化FROM子句中的子查询(Derived Tables)

mysql 5.6.3优化器处理derived table更加有效：

Ÿ   物化from中的子查询，会被延迟直到需要使用的时候

s   之前，from子句会被explain select的时候被物化，因为尽管是获取执行计划，但是还是有部分会被运行。这种情况下物化就不会发生，explain也更加快。

s   对于不explain的语句，延迟物化，有时候就不再需要物化。

Ÿ   在查询执行的时候，优化器可能会对derived table加索引加快行的获取

以下explain子句：

EXPLAIN SELECT * FROM (SELECT * FROM t1);

优化器延迟子句物化，知道需要使用的时候再物化。，这个情况下查询时不会被执行的，所以不需要物化。

在查询执行的情况下，延迟子查询物化可以让优化器不去物化它

SELECT * FROM t1

  JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1

  WHERE t1.f1 > 0;

如果优化器先处理t1,并且where生成的结果为空，join生成的一定是空，所以没必要物化。

当子查询需要物化的时候，优化器可能会增加索引来加快访问速度。如果索引可以ref访问表，那么可以在查询执行的时候会大大的减少数据读取，如下：

SELECT * FROM t1

  JOIN (SELECT * FROM t2) AS derived_t2 ON t1.f1=derived_t2.f1;

优化器如果在derived_t2的f1上增加了索引，如果允许ref访问。增加索引之后优化器可以把derived表当成有索引的用户表。

创建索引会带来消耗，但是和没有索引执行相比，创建索引的符合可以忽略。如果ref访问可能比其他访问开销更大，那么优化器就不会创建索引。

8.2.1.18.4 优化带exists的子查询

某些优化对于in或者any子查询是适用的。这里要讨论不适用的情况。

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

mysql评估是从外到内的，先获取外部表达式的值，然后执行子查询获取行。

一个很用的优化是转化子查询，把条件pushdown到了子查询的where中

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

转化之后，mysql可以把pushdown等式，来限制要检查的行数。

(oe_1, ..., oe_N) IN

  (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

转化为

EXISTS (SELECT 1 FROM ... WHERE subquery_where

                          AND oe_1 = ie_1

                          AND ...

                          AND oe_N = ie_N)

转化其实是有限制的，只有在忽略null的情况下才能使用，只有一下2个都成立才能使用pushdown：

Ÿ   outer_expr和inner_expr不能为null

Ÿ   不需要区分null和false

如果任意一个条件不满足，优化就变得很复杂。

当outer_expr不为null但是子查询可能不会生成outer_expr=inner_expr记录，outer_expr in(select…)评估：

Ÿ   null，如果生成的任何记录，inner_expr为null

Ÿ   false，如果生成了一个非null值或没有产生

在这个情况下，查找outer_expr=inner_expr就不可用了，在吗，没有找打行的情况下，还需要找inner_expr is null。

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND

        (outer_expr=inner_expr OR inner_expr IS NULL))

这时需要访问is null条件，这也就是为什么会有ref_or_null的访问方式。（这样转化的话是否有语义上的问题）

如果type为index_subquery或者index_subquery也有or null但是explain没有输出，要使用explain extended，show warnings的输出注意checking null。

or…is null让查询执行，稍微更加复杂一点。

当outer_expr可能为null那么就会更加复杂，null in(select…)评估：

Ÿ   null，如果select不产生任何行

Ÿ   flase，如果select不产生行

所以为了正确的评估需要检查，子查询是否产生行。要先判断select会不会产生行，所以outer_expr=inner_expr不能被pushdown到子查询。

其实是要更具outer_expr的不同来不同处理。

如果outer_expr为null，那么就要判断select是否产生数据，这个时候就要执行select语句，不带pushdown条件。

NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)

如果outer_expr不为null，那么就有必要做转化pushdown条件。

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

转化为

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

如果不转化那么速度就会很慢，为了解决这2难的问题，由一个触发函数来决定是否触发pushdown条件

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

转化为

EXISTS (SELECT 1 FROM ... WHERE subquery_where

                          AND trigcond(outer_expr=inner_expr))

如果为：

(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

那么转化为

EXISTS (SELECT 1 FROM ... WHERE subquery_where

                          AND trigcond(oe_1=ie_1)

                          AND ...

                          AND trigcond(oe_N=ie_N)

       )

每个trigcond是一个特殊的函数，评估如下：如果oe_i不为null，则为X，如果为null则为true。

对trigcond(x)，等式的触发有一下优化：

Ÿ   trigcond(x=y[or y is null])可以使用ref，eq_ref,ref_or_null。

Ÿ   trigcond(x=y)可以访问unique_subquery或者index_subquery。

Ÿ   如果子查询是join多个表，触发条件会尽快的检查。

当触发条件被调成off，必须要有个fallback策略，执行一个全表扫描。

null in select()可能会导致全表扫描。这个就是获取正确结果需要的花费。

对于多个表的子查询，执行null in select()可能会很慢，因为优化器不对外面为null的进行优化。对于外部表达式，如果null不出现，那么就不会影响性能。

为了优化器更好的执行，有几个tips：

Ÿ   如果可以尽量吧列定义成not null

Ÿ   如果不会去取分false和null的区别，可以使用not null 来避免

outer_expr IN (SELECT inner_expr FROM ...)

转化为

(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))

subquery_materialization_cost_based会控制是否对in语句使用exists策略。

8.2.1.19 优化limit语句

如果要指定查询结果的行数，使用limit子句来限制结果集行数。

mysql有些时候会对有limit，没有having子句优化：

Ÿ   如果limit值限制一些行，mysql在这个时候可能会用索引来代替表扫描。

Ÿ   如果使用order by配合limit，mysql会在sort直到结果出现limit限制的行数。

Ÿ   如果order by是根据索引顺序，那么会很快，如果要用filesort，那么会选择所有的行，然后排序，直到找到前几行。之后排序就不会再运行。

有个现象加不加limit可能会导致order by顺序不同。有一下几个方面：

Ÿ   如果limit带distinct，会在到达limit行数之后马上停止。

Ÿ   某些情况，group by可以通过读取key的顺序，这个时候，limit可以限制不用计算不必要的group by值。

Ÿ   当客户端收到指定行数的时候，会中断查询，除非使用了SQL_CALC_FOUND_ROWS。

Ÿ   limit 0直接返回空集，可以用来检查查询是否合法。

Ÿ   当服务使用临时表，会使用limit子句来计算需要多少空间。

在有相同排序值的时候，顺序是不确定的。

mysql> SELECT * FROM ratings ORDER BY category;

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

| id | category | rating |

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

|  1 |        1 |    4.5 |

|  5 |        1 |    3.2 |

|  3 |        2 |    3.7 |

|  4 |        2 |    3.5 |

|  6 |        2 |    3.5 |

|  2 |        3 |    5.0 |

|  7 |        3 |    2.7 |

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

使用了limit

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;

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

| id | category | rating |

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

|  1 |        1 |    4.5 |

|  5 |        1 |    3.2 |

|  4 |        2 |    3.5 |

|  3 |        2 |    3.7 |

|  6 |        2 |    3.5 |

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

若要确定顺序，最好加个唯一列：

mysql> SELECT * FROM ratings ORDER BY category, id;

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

| id | category | rating |

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

|  1 |        1 |    4.5 |

|  5 |        1 |    3.2 |

|  3 |        2 |    3.7 |

|  4 |        2 |    3.5 |

|  6 |        2 |    3.5 |

|  2 |        3 |    5.0 |

|  7 |        3 |    2.7 |

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

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;

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

| id | category | rating |

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

|  1 |        1 |    4.5 |

|  5 |        1 |    3.2 |

|  3 |        2 |    3.7 |

|  4 |        2 |    3.5 |

|  6 |        2 |    3.5 |

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

如果排序的个数N刚好能在sort buffer里面，那么服务就可以避免执行文件合并和并且把sort buffer当做一个优先级队列来处理：

Ÿ   扫描表，把选中的行都插入队列中，如果队列满了把最后一个剔除掉。

Ÿ   然后返回前N行，如果有跳过M，那么先条过M行，然后返回之后的N行记录。

之前使用的处理方法：

Ÿ   扫描表，重复下面的步骤直到结束

s   输入select row直到sort buffer满。

s   写入前N行到buffer，然后把前N行合并到文件。

Ÿ   排序合并文件并返回前N行。

扫描表的花费和队列和文件合并一样，所以优化器在选择的时候是根据其他花费的：

Ÿ   队列的方法会使用很多cpu来插入到队列。

Ÿ   合并文件会使用IO来读写文件，cpu来排序。

优化器在行数和不同值N之间平衡。

8.2.1.20 如何避免表扫描

explain输出type列为all，就说明mysql使用表扫描，如果发生表扫描考虑以下几点：

Ÿ   表很小，表扫描比key lookup快。

Ÿ   没有索引可用

Ÿ   有索引，和一个常量比较，但是常量覆盖了很多行，可能造成比使用扫描慢。

Ÿ   key的选择度很低，也就是不同的值很少。这种情况下会导致一个key对于很多行，可能会造成比表扫描慢。

对于大的表，以下技术可能可以避免优化器错误的选择表扫描：

Ÿ   使用ANALYZE TABLE tbl_name来更新统计信息

Ÿ   使用提示FORCE INDEX

Ÿ   使用参数--max-seek-for-key=1000，key seek不能多于这个系统变量。

8.2.2 优化DML语句

8.2.2.1 加快insert语句

优化insert的速度是，组合多个小的操作到一个大的操作。理想状态是一个连接每次发对多个行的修改，并延迟索引更新和一致性检查。

insert一行插入的时间由以下几个因素决定：

Ÿ   连接(3)

Ÿ   把查询发送的服务(2)

Ÿ   解析查询(2)

Ÿ   插入行(1*行大小)

Ÿ   插入索引(1*索引个数)

Ÿ   关闭(1)

如果是Btree，那么insert插入会showdown大概logN（N表的大小）。

你可以使用以下方法来提高insert速度：

Ÿ   如果在同一个客户端插入多个行，可以使用insert values(),()，values列表同一时间插入多个行。

Ÿ   使用LOAD DATA INFILE，导入文本文件。这个比insert语句块20倍。

Ÿ   对于有默认值的列，只指定和默认值不同的值。

Ÿ   关于innodb的批量导入：Section 8.5.4, “Bulk Data Loading for InnoDB Tables”

Ÿ   关于myisam的批量导入：Section 8.6.2, “Bulk Data Loading for MyISAM Tables” 

8.2.2.2 加快update语句

update语句的优化和select语句类似，就是有额外的写入，写入的时间依赖于更新的数据和要更新的索引个数。

另外一个快速更新的方法是延迟更新，然后一次性对多个行做多次更新。

8.2.2.3 加快delete语句

delete语句的时间主要是删除个别行和index中的行成正比。为了更快的删除，可以通过key_buffer_size系统变量增加key cache。

如果要删除myisam表的所有行，truncate table tbl_name比delete from tbl_name快的多。

8.2.3 优化数据库权限

越多的权限，会造成越多的sql语句负荷。简单的权限可以让mysql在运行客户端请求时，减少权限的检查。

8.2.4 优化INFORMATION_SCHEMA查询

应用程序使用information_schema监控，数据库流畅运行。目的是为了最小化文件操作来收集信息填充到动态表。

1.尝试使用在where子句中使用常量来查找表或者数据库。

2.最小化必须要打开的文件个数。

3.使用explain决定服务是否可以使用INFORMATION_SCHEMA优化。

8.2.5 其他优化提示

Ÿ   为了避免创建连接时的负荷，使用长连接。

Ÿ   检查查询是否使用了索引。

Ÿ   尽量避免在更新比较频繁的myisam表上使用复杂的select语句。

Ÿ   当没有空闲中间空间时，myisam可以插入和select同时进行。这个行为主要由concurrent_insert变量控制。

Ÿ   使用optimize table来修复任何archive表的压缩问题。

Ÿ   若你经常使用expr1,expr2…获取数据，那么使用alter table…order by expr1,expr2…

Ÿ   如果列窄，并且是唯一的，那么使用hash作为索引比使用宽的索引更加高效。

Ÿ   对于更新频繁的myisam表来说，尽量避免使用变长字段，如果有一个变成字段那么就为动态行模式(因为动态行模式比较容易产生碎片)。

Ÿ   当行变多，把表分为多个表不是很管用。因为最大的性能消耗是寻找需要行的前几个字节，而接下来读取整行是很快的。

Ÿ   如果要是要通过很多行来计算的，如count，可以考虑创建一个中间表，然后实时更新。

UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;

Ÿ   如果你收集大日志表的统计信息，使用summary表来代替整个日志表。维护一个summaries比计算统计信息快的多。

Ÿ   通常，report分为live，statistic，当数据需要静态report的时候，report从summary表被创建，而summary从live数据定期更新。

Ÿ   有些时候，封装和存储数据到blob列很方便，特别是要存不是行列模式的数据时。

Ÿ   通常，数据时不冗余的，但是看具体情况而定。

Ÿ   存储过程和用户函数有时候会增加性能。

Ÿ   可以通过缓冲查询或者结果到应用程序来增加性能。

Ÿ   使用一个insert插入多个sql语句。

Ÿ   使用load data infile导入大量数据，比insert快。

Ÿ   使用auto_increment让每行有个唯一标示

Ÿ   使用optimize table来避免myisam动态行模式的碎片问题。

Ÿ   如果有可能使用memory存储引擎。

Ÿ   如果使用web server，图片和其他二进制资源应该以文件方式存储。

Ÿ   列在别的表定义时，用相同数据类型，这样join的时候速度才快。

Ÿ   尽量让列名简单，考虑让列名少于18字节。

Ÿ   如果要你要加快速度，应该先看不同的sql服务对低级别的数据存储的支持。

Ÿ   如果使用数值类型，在很多情况下，会比文字形式访问就要快。

Ÿ   复制在某些操作下提供性能的有点，比如读写分离

Ÿ   mysql定义表使用delay_key_write=1会让索引更新更快，因为在表关闭之前不会被刷新磁盘。

当表在打开的时候被kill，必须确保服务启动使用了选项--myisam-recover-options,或者在启动前使用myisamchk。

Ÿ   对于非事务表，给select高优先，那么使用insert low_priority来降低insert优先级

Ÿ   select high_priority可以让在非事务表上访问时直接跳过队列。

low_priority和high_priority只适用于非事务表。

8.3 优化和索引

select语句性能最好的提升是在一个或者多个列上创建索引。

8.3.1 MySQL如何使用索引

索引使用指定的列的行值速度会很快，如果没有索引mysql会扫描整个表。mysql中的索引可以让mysql快速的定位查询数据文件，不需要扫描所有数据。

大多数mysql index保存在btree中，空间引擎是rtree，memory表也支持hash索引。

mysql在以下几个途径使用索引：

Ÿ   选择符合where子句的索引。

Ÿ   减少预估的影响行数，选择选择度高的索引

Ÿ   当执行join的时候，mysql会选择在列上长度和类型都一样的。在大小一样的情况下，varchar和char可以认为是同一个类型。

在非二进制字符串上比较，列的字符集必须一样。在不同类型上做比较可能会倒是无法使用索引。

Ÿ   在索引的列上使用min和max。

Ÿ   在order by，group by下和索引左前缀匹配。

Ÿ   查询获取数据可以不获取原始数据行。

索引在小的表，或者查询需要很多行的大表上是没啥用的。

8.3.2 使用Primary Keys

primary key一般是表中最重要的查询使用最常用的列。使用innodb存储引擎，表是有primary key索引组织。加快基于primary key的lookup和sort的速度。

如果表很大，并没有明显的primary key，可以使用自增列充当primary key。

8.3.3 使用外键(Foreign Key)

如果一个表有很多列，而查询使用了不同列的组合。把不太使用的列放到另外一个独立的表可能会很有效。表之间使用冗余id来关键2个表。

8.3.4 列索引(Column Indexes)

最常用的索引时简单索引，是元数据行中的值的备份。btree数据结构可以查找指定的值，值的集合，值的区间更快，涉及的运算符，<,>,>=,<=,=,!=,<=>。

每个表的最大索引个数，和索引内最大的列数由每个存储引擎定义。

8.3.4.1 Prefix Indexes

对于列col_name，在创建的索引的时候可以指定前N个字符。对于blob和text，必须指定长度。

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

N的最大个数是1000字节。（innodb为767，除非你设置了innodb_large_prefix）。

8.3.4.2 全文索引

你可以创建全文索引，只有innodb，myisam存储引擎支持全文索引，并且全文索引只能用于列text，char，varchar。

查询是有以下特性会特别高效：

Ÿ   全文索引查询只返回文档id或者文档id和查询rank

Ÿ   全文查询以desc排序匹配的行并且使用limit来获取前n行

Ÿ   全文查询只使用count(1)返回匹配的行。

8.3.4.3 空间索引

现在只有myisam支持rtree， Other storage engines use B-trees for indexing spatial types 。

8.3.4.4 MEMORY存储引擎中的索引

memory存储引擎默认使用hash索引，同时也支持btree索引。

8.3.5 复合索引(Mutiple-column Indexes)

mysql可以创建复合索引，一个索引可以由16个组成。对于比较大的列可以hash并最为索引，这样可能会更快。

具体看: http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html

8.3.6 验证索引使用

使用explain语句可以查看是否使用了索引，具体看： Section 8.8.1, “Optimizing Queries with EXPLAIN”.

8.3.7 Innodb和MyISAM索引统计信息收集

存储引擎收集的统计信息让优化器使用，表的统计信息是基于值分组的，值分组(value group)是多个行使用相同key前缀的值。为了优化的目的，一个重要的统计信息是值分组的平均大小。

mysql使用值分组的平均大小有以下几个用途：

Ÿ   评估每个ref中有多少行要被读取。

Ÿ   评估有多少行在join中产生。

值分组的平均值增加，会导致索引效率变差。（因为选择度变低）。

innodb，myisam有一些控制统计信息收集的系统变量，innodb_stats_emthod,myisam_stats_method，这2个变量有3个可选值：

Ÿ   nulls_equal，所有的null值都相等，默认。

Ÿ   nulls_unequal，所有的null都不相等

Ÿ   nulls_ignored，null值会被忽略

可以使用一下方法来更新统计信息：

使用myisamchk --stats_method=method_name --analyze

设置表让统计信息过期，然后设置myisam_stats_method并且执行analyze table语句。

有一些注意点：

Ÿ   可以手动的更新统计信息，但是mysql也会自动收集统计信息。

Ÿ   没有办法提示，使用了什么方法生成的统计信息

Ÿ   这些变量只能应用于innodb和myisam表

8.3.8 比较btree和hash索引

8.3.8.1 btree索引特点

btree索引可以使用不等式比较。<,>,>=,<=,=,!=,<=>，between和开头不带通配符的like。

col_name is null 可以使用索引。如果and是符合索引前缀的就可以使用这个索引。

如：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

    /* index = 1 OR index = 2 */

... WHERE index=1 OR A=10 AND index=2

    /* optimized like "index_part1=‘hello‘" */

... WHERE index_part1=‘hello‘ AND index_part3=5

    /* Can use index on index1 but not on index2 or index3 */

... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

不能使用索引的情况：

    /* index_part1 is not used */

... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */

... WHERE index=1 OR A=10

    /* No index spans all rows  */

... WHERE index_part1=1 OR index_part2=10

有时候可以使用索引但是优化器没有使用，那是优化器认为使用索引会更慢。

8.3.8.2 Hash索引特点

hash索引有一下几个特点：

Ÿ   只能使用=,<=>

Ÿ   hash索引不能用于优化order by

Ÿ   mysql无法通过hash评估2个值之间有多少行

Ÿ   只能搜索整个key来获取行

8.4 优化数据库结构

8.4.1 优化数据大小

设计表是为了最小化磁盘空间，减少数据读写的量。小的表需要的内存更小，空间减少表示表中的数据也减少也可以减少索引长度加快执行速度。

8.4.1.1 表列

Ÿ   尽可能使用最小的数据类型。

Ÿ   尽可能给列写上not null。

8.4.1.2 行格式(Row Format)

innodb现有文件格式再有行格式，目前innodb的文件格式antelope和barracuda。

antelope支持compact和redundant，barracuda支持compressed和dynamic。

具体看：http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html

Ÿ   innodb中可以使用compact存储格式，来缩小存储空间

Ÿ   为了最小化空间，也可以使用compressed格式

Ÿ   对于myisam如果没有固定长度，执行会很快但是会有点空间浪费。如果使用了varchar但是想使用固定长度，可以在创建表的时候使用row_format=fixed

8.4.1.3 索引

Ÿ   primary key应该尽量的短，对于innodb表，primary key会被复制到其他secondary索引，所以短的primary key会减少一些secondary的空间。

Ÿ   索引虽然可以加快查询的速度，但是会降低insert和update的数据库，如果多数查询时以组合的方式访问，那么创建一个复合索引，而不是2个单独的索引。

Ÿ   如果有一个大字符串，头几个字符是唯一的前缀，那么在该前缀上创建索引比较好。

8.4.1.4 Joins

Ÿ   在某些场景下，如果表总是扫描的把表分为2个表可能会提升性能。

Ÿ   join字段使用相同的数据类型会提升性能。

Ÿ   尽量缩小列的名称长度。

8.4.1.5 常规(Normalization)

Ÿ   通常不会让数据冗余。

Ÿ   如果速度远远比磁盘空间和维护副本的重要，可以放松一些规则，可以保留一些副本信息来加快数据访问。

8.4.2 优化MySQL数据类型

8.4.2.1 数值类型优化

Ÿ   对于唯一的id，或者其他值都可以用数值或者字符串来代替，首选数值。因为数值类型比较节省空间，占的内存少。

Ÿ   如果使用数值类型，在很多情况下都比访问文本的好，因为数值类型比文本类型窄。

8.4.2.2 优化字符和字符串类型

Ÿ   使用binary排序规则，会让排序和比较加快

Ÿ   在比较的列上使用相同的字符集和排序规则，避免字符串转化

Ÿ   如果列的值小于8k考虑使用varchar来代替blob，如不包含blob，order by或者group by生成的临时表可以使用mempry存储引擎。

Ÿ   若表包含字符串，但是不经常使用可以考虑分表处理

Ÿ   当使用随机生成的数据作为primary key，在前面加上一个唯一的列，这样可以加快插入的速度(避免插入导致分页)

Ÿ   如果是数值最好选择数值类型

8.4.2.3 优化blob数据类型

Ÿ   当使用blob保存数据，考虑压缩blob

Ÿ   对于有很多列的表，考虑不适用blob或者把blob访问到另外一个表

Ÿ   因为blob在显示和获取上的性能要求，和其他数据类型不一样。可以考虑放到单独的存储设备上甚至另外一个数据库实例上。

Ÿ   如果长度没有超过varchar限制，最好使用varchar。

Ÿ   于其比较长的字符串，还不如比较字符串的hash值。

8.4.2.4 使用PROCEDURE ANALYSE

analyse()可以分析查询结果给出建议的数据类型。

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])

如：

SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);

结果会给出建议的最优的数据类型。

analyse 有2个参数：

Ÿ   max_elements:默认256是analyse能够注意到的最大的distinct值个数。

Ÿ   max_memory:默认8192是analyse用来查找distinct值是可用的最大内存。

8.4.3 表的优化

8.4.3.1 mysql如何打开和关闭表

执行mysqladmin status会发现

Uptime: 426 Running threads: 1 Questions: 11082

Reloads: 1 Open tables: 12

当你只有6个表的时候会很疑惑为什么open tables为12。

mysql是多线程的，所以很有可能很多查询同时访问一个表，为了最小化多个客户端使用不同的状态来访问表，表为被每个独立的会话打开。虽然会增加内存的时候，但是也提高了性能。

table_open_cache和max_connections系统变量，会影响文件的最大打开数。如果你增加一个或者2个变量，可能会到达每个处理可以打开文件描述符的个数。

table_open_cache和max_connections相关，如果有200个并发运行的连接，那么指定的table cache至少要是200*N，N是每个join最大的执行个数。

当然也需要为临时表和文件包里文件描述符。

要保证你的操作徐彤可以打开文件描述符的个数。如果table_open_cache太大，会导致mysql超出闲置，查询执行会失败，并且会导致mysql可靠性降低。

可以通过--open-files-limit来增加mysql可以使用的文件描述符个数。

服务在启动的时候会自动设置table_open_cache,也可以在服务启动时候显示设置。

以下情况mysql会关闭不适用的表，并从cache中删除：

Ÿ   当cache满了，但是一个线程有要打开一个不再cache上的表。

Ÿ   当cache包含的比table_open_cache更多的项，而且i zengjia mysql

Ÿ   iang l 立的会话代考。有不再被使用的表。

Ÿ   当表发生flush。如flush tables语句。

服务使用一下过程来存放cache：

Ÿ   如果有没有使用的表，以最少被使用的顺序进行释放

Ÿ   若打开一个表，但是cache满了，没有表可以被释放，cache会被临时性的扩展，当表被close不被使用的时候，就会被释放。

MyISAM表会为每个并发访问打开一次表，比如一个表被2个线程同时访问那么会被打开两次，或者一个线程在查询中访问2次表。

打开一次myisam表需要2个描述符，一个是数据文件，一个是索引文件。索引文件的描述符是公用的。

如果一个表是通过HANDLE tbl_name OPEN打开，表对象不会和其他线程共享，只有调用了HANDLE tbl_name CLOSE才能关闭，或者中断线程。

使用状态变量opened_tables显示了打开的表个数。

8.4.3.2 不要再同一个数据库中创建多个表

如果很多myisam表，在同一个数据库目录，打开，关闭和创建操作时很慢的，如果当cache满了，select多个报表可能会造成一点负荷。因为每个表要打开都需要关闭表。

8.4.4 MySQL如何使用内部临时表

内部临时表在处理查询的时候被创建，这样的临时表可以以memory存储引擎保存，或者以myisam保存。若临时表太大，mysql服务会自动把内存中的表转到磁盘中。

以下几个条件下临时表会被创建：

Ÿ   union语句

Ÿ   某一些视图会使用临时表，如使用了temptable算法或者使用了union语句或者使用了聚合

Ÿ   如果order by和group by子句的key不同，或者order by， group by的来自于不同的表，那么临时表就会被创建。

Ÿ   查询中出现order by和distinct子句，临时表可能会被创建

Ÿ   如果使用了SQL_SMALL_RESULT选项，mysql会把临时表存放到内存中，除非包含了元素只能存放到硬盘表上。

Ÿ   from子句中的子查询，即衍生表(derived table)。

Ÿ   为子查询或者semi-join物化生成的表。

通过explain中的extra如果包含 using tempprary。

若一个临时表变很大，mysql会自动传化为硬盘表。内存表最大是tmp_table_size,max_heap_table_size的最小值。

对于create table 创建的memory表，是有max_heap_table_size来决定memory允许增长的最大值。

临时表创建之后，状态值created_tmp_tables会增长，若是硬盘表created_tmp_disk_tables会增长。

有一些因素无法让表在内存临时表中:

Ÿ   有blob，text列在表中

Ÿ   group by，order by的长度超过512个字节

Ÿ   当使用union或者union all的时候字符串列最大值超过512个字节

8.5 优化innodb表

8.5.1 优化innodb表存储引擎的layout

Ÿ   当表的数据大小达到一定程度，可以考虑使用optimize table语句来重新组织优化表，收缩浪费的空间。optimize table复制表的数据部分然后重建索引，减少了表空间中的碎片。

Ÿ   innodb有长的primary key，primary key会被复制到secondary key。使用小的primary key 可以减少secondary index 大小

Ÿ   使用varchar来代替char和会有null的列。

Ÿ   对于很大的表，而且包含很多重复的文本或者数值，可以考虑使用compressed压缩格式。

8.5.2 优化innodb事务管理

有了优化innodb事务处理，找到到性能和负荷之间的平衡点。

Ÿ   默认使用autocommit=1会在繁忙的db服务器上强化性能的限制。innodb在每次commit之后必须刷新日志，当每个修改都跟了个commit，就很容易出现io瓶颈。

Ÿ   如果事务都是select，那么设置成autocommit，innodb会自动识别只读事务并优化他们。

Ÿ   避免在插入修改删除大量数据之后去回滚，如果一个事务是的性能变差，那么如果去回滚会变得更差。

kill掉服务也没用，服务启动的时候还是会去回滚。唯一的优化是增加buffer pool这样修改全在buffer pool，不需要直接写入硬盘。

设置innodb_change_buffering=all来缓存所有的写入，这样可以让修改尽量顺序的写入

到磁盘。当处理大的dml要定期的运行checkpoint，把多个dml拆成多个小的。

Ÿ   如果可以容忍一些数据都是，那么可以设置innodb_flush_log_at_trx_commit=0,这样innodb会每秒一次刷新日志。

为了同步磁盘数据和binary log的次数可以使用innodb_support_xa=0。

Ÿ   当行被删除或者更新的时候，相关的undo log不会被马上物理删除，甚至commit之后也不会马上删除。

老的数据会被一直保留，直到更早的事务，或者并发事务完成，这样这些事务才能访问到老的数据。

因此，长时间运行的事务在运行的时候，可以阻止innodb数据被清空。

Ÿ   当长运行事务中被修改删除，其他事务使用读提交，或者可持续读，为了访问到老的数据需要花很多功夫来重建

Ÿ   当长运行事务中修改了表，在其他事务中的查询需要去读原表，而不是用索引覆盖。

当使用secondary索引查找的时候page_max_trx_id太新或者secondary索引中行被标记为了delete，innodb可能需要找聚集索引中的记录。

8.5.3 优化innodb logging

Ÿ   保证日志文件大一点，甚至和buffer pool一样大。当发生checkpoint必须把在buffer pool中的修改内容写入到磁盘。小的日志文件会导致很多不必要的磁盘写入。

Ÿ   log buffer 越大越好。（8MB的倍数）

8.5.4 innodb表的批量数据导入

Ÿ   当插入数据到innodb，先关闭自动提交模式，因为自动提交会为每次插入都提交。反而会影响插入速度。

Ÿ   如果有唯一性约束，那么为了提高性能可以先临时的关闭唯一性约束，对于大的表可以节省一些io，因为innodb可以使用insert buffer，批量写入到secondary索引

Ÿ   如果有foreign key约束，可以先关闭外键检查。

Ÿ   使用一个insert多行数据来减少客户端和server之间交互的成本。

Ÿ   对于有自动增长的列，可以先把innodb_autoinc_lock_mode设置为2.

Ÿ   当导入innodb全文索引的时候，为了优化性能可以使用一下步骤：

s   在创建表时候创建一个fts_doc_id，类型为bigint unsigned not null并在上面常见一个索引 如：

CREATE TABLE t1 (

FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,

title varchar(255) NOT NULL DEFAULT ”,

text mediumtext NOT NULL,

PRIMARY KEY (`FTS_DOC_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);

s   把数据导入到表。

s   创建全文索引。

8.5.5 innodb查询优化

调整innodb查询，在表上创建一个合适的索引，以下是innodb索引的一些原则：

Ÿ   因为每个innodb有primary key，使用在很重要的并且时间敏感的查询的列上。

Ÿ   不要再primary key上指定太多或者太长的列，因为primary key会被保存在secondary

Ÿ   不要为每个列创建secondary索引，因为每个查询只能使用一个索引。尽量少的创建索引，而不是大量的简单索引。

Ÿ   如果索引列不包含null，那么在创建表的时候定义为not null。

Ÿ   你可以优化单个查询的事务，使用Section 14.13.14, “Optimizing InnoDB Read-Only Transactions”.

Ÿ   如果表读多写少，那么就启动query cache

[mysqld]

query_cache_type = 1

query_cache_size = 10M

8.5.6 优化innodb DDL操作

Ÿ   对于ddl操作对于表和索引的操作，最重要的方面是innodb表创建和删除索引。在mysql 5.5比mysql5.1要快的多，因为innodb引入了快速索引创建。

Ÿ   快速索引创建，可以让删除索引，导入数据后再创建索引更快

Ÿ   使用过truncate table清空表，不是delete from tbl_name

Ÿ   因为表是由primary key组织的，修改primary key的定义会重新组织整个表，primary key在create table语句中定义，不需要先alter或者drop primary key。

8.5.7 优化innodb磁盘I/O

经过应用最佳实践，io还是很高，那么就查看cpu的使用，如果cpu使用低于70%，但是io使用很低，那么服务的负荷就是磁盘密集型。

对IO的优化有一下几种：

Ÿ   当表数据被缓存在innodb buffer pool，那么查询就不需要去读取物理io，buffer pool的大小可以通过 innodb_buffer_pool_size来指定，一般为80%的物理内存。

Ÿ   在一些linux或者unix版本中，使用fsync函数来刷新文件到磁盘，相似的方法是很慢的。如果数据库写入性能有问题，可以尝试使用innodb_flush_method=o_dsync进行基准测试看看性能。

Ÿ   如果innodb存储引擎使用在Solaris 10 X86_64结构体系上(AMD处理器)。对innodb相关的文件使用直接io，来避免innodb性能降级。

为了让整个存储了innodb相关文件的ufs文件系统使用直接io，那么在mount的时候加上选项forcedirectio。

如果直接io只针对innodb相关文件而不是整个文件系统，那么使用innodb_flush_method=O_DIRECT，这个时候innodb会使用directio()来代替fcntl()。

Ÿ   当innodb使用了很大的innodb_buffer_pool_size，应该在Solaris 2.6以及以上平台，对innodb数据文件和日志文件所在的原设备，

或者独立的直接IO UFS文件系统(使用forcedirectio)进行基准测试。

Ÿ   如果有额外的存储设备使用RAID设置，或者symbolic连接到了别的磁盘，参考Section 8.11.3, “Optimizing Disk I/O” 

Ÿ   如果在checkpoint期间，io吞吐量变小，那么考虑把innodb_io_capacity设高，值越高说明flush越频繁，值高可以避免工作积压而导致的吞吐量问题。

Ÿ   如果system不会被innodb flush操作落下，那么可以考虑使用低一点的innodb_io_capacity，通常尽量保持低的配置，

但是不能低到影响吞吐量，可以通过SHOW ENGINE INNODB STATUS查看：

s   History list length low, below a few thousand.

s   insert buffer合并和row inserted相近

s   buffer pool中修改的也会低于buffer pool的innodb_max_dirty_pages_pct。

s   Log sequence number-Last checkpoint小于7/8，或者理想的6/8的innodb日志文件大小。

Ÿ   其他的用来调整IO的选：

innodb_adaptive_flushing， innodb_change_buffer_max_size, innodb_change_buffering,innodb_flush_neighbors, innodb_log_buffer_size, 

innodb_log_file_size, innodb_lru_scan_depth,innodb_max_dirty_pages_pct, innodb_max_purge_lag, innodb_open_files, 

innodb_page_size,innodb_random_read_ahead, innodb_read_ahead_threshold, innodb_read_io_threads,innodb_rollback_segments, 

innodb_write_io_threads, 和 sync_binlog.

8.5.8 优化innodb配置变量

不同的负荷有不同的设置。

innodb会自动执行优化，性能调整要配合性能监控保证数据库有良好的性能。并在性能出问题的时候调整配置选项。

主要的配置步骤：

Ÿ   启用innodb使用高性能内存分配。 Section 14.13.3, “Configuring the Memory Allocator for InnoDB”.

Ÿ   控制不同类型的DML操作对应的buffer，来避免频繁的小磁盘写入。Section 14.13.4, “Configuring InnoDB Change Buffering”

Ÿ   通过innodb_adaptive_hash_index来调整自适应hash index。Section 14.2.13.6, “Adaptive Hash Indexes” 

Ÿ   如果上下文切换到达瓶颈，限制并发线程数。 Section 14.13.5, “Configuring Thread Concurrency for InnoDB”.

Ÿ   控制innodb预读的读取量。 Section 14.13.1.1, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.

Ÿ   若有高端的IO子系统，没有被完全利用，可以增加后台的IO读写线程。Section 14.13.6, “Configuring the Number of Background InnoDB I/O Threads”.

Ÿ   控制后台I/O innodb执行的量。 Section 14.13.8, “Configuring the InnoDB Master Thread I/O Rate”.

Ÿ   控制，当innodb执行确定类型的后台写入时用的算法。Section 14.13.1.2, “Configuring the Rate of InnoDB Buffer Pool Flushing”.

Ÿ   利用多核cpu和它们的cache配置。 Section 14.13.10, “Configuring Spin Lock Polling”.

Ÿ   防止一次表扫描而导致，而干扰buffer cache中的热点数据。Section 14.13.1.3, “Making the Buffer Pool Scan Resistant”.

Ÿ   调整合适的log文件大小，保证crash恢复的可靠性。

Ÿ   配置buffer pool的个数和大小 Section 14.13.1.4, “Using Multiple Buffer Pool Instances”.

Ÿ   增加最大允许并发事务数量，可以大大增加繁忙事务的可扩展性。Section 14.13.12, “Better Scalability with Multiple Rollback Segments”

Ÿ   把清空操作移到后台线程。为了有效的评估设置的结果，先调整其他io相关，线程相关的配置。Section 14.13.13, “Configuring InnoDB Purge Scheduling”.

Ÿ   减少线程之间的上下文切换，对于强力的现代系统大概把innodb_thread_concurrency选项设置为32。把innodb_concurrency_tickets设置为5000，这个选项设置了线程个数和一次跳读后可以执行的时间。

8.5.9 优化innodb多表情况

innodb在启动后第一次访问时，就会被表计算索引基数，而不是被存在表里面。

这个操作会花很多时间，因此被转移到了初始化表打开操作中，先对这个表预热之后再使用。

预热过程：在启动之后会马上通过select 1 from tbl_name limit 1的方式来运行。

8.6 优化MyISAM表

MyISAM存储引擎最好执行在大量读数据或者地并发操作。因为表锁会限制update并发。

8.6.1 优化MyISAM查询

Ÿ   为了帮助mysql更好的优秀的查询，使用analyze table或者mysiamchk --analyze。更新一个相同值对应的平均行数。

在2个表基于非常量表达式的join，mysql使用这个来确定使用什么索引。可以通过show index from tbl_name的cardinality来确定列的密度。

Ÿ   根据一个索引对索引或者数据进行排序。使用myisamchk --sort-index --sort-records=1

Ÿ   避免在更新频繁的表上使用复杂的select语句。

Ÿ   如果一个表没有空闲的block，myisam支持并发插入。通过修改concurrent_insert来让插入的数据总是是追加的方式

Ÿ   对于myisam表如果修改很频繁，试图避免变长列。包含了一个可变长列，myisam就以动态行模式。

Ÿ   对于表很大的时候拆分表是没有用的，在访问一样的时候，主要的性能花费是查找行的前几行。

Ÿ   使用alter table…order by expr1,expr2,…让表以这个顺序组织。

Ÿ   如果要计算表的结果比如count，那么可以生成一个中间表，实时更新。

Ÿ   当不需要知道什么时候更新myisam表的时候，可以使用insert delayed。

Ÿ   定期使用optimize table可以避免myisam动态行产生的碎片。

Ÿ   定义一个myisam表使用delay_key_write=1，索引更新就会很快，因为修改知道表被close的时候会被更新到磁盘，如果服务崩溃必须使用myisam-recover-options选项修复，或者在启动服务之前使用myisamchk。

Ÿ   在myisam的索引中字符串会被自动的把前缀和后缀的空间压缩。

Ÿ   可以通过cache查询和查询结果在应用程序上，来提高性能。并且让insert和update能够批量执行

8.6.2 myisam表批量导入

Ÿ   可以使用insert delayed语句来加快多客户端数据插入

Ÿ   对于myisam表，如果没有空闲空间，可以并发插入数据。

Ÿ   当有很多插入的时候load data infile会比insert快，步骤如下：

1.执行flush table或者mysqladmin flush-tables命令

2.使用myisamchk --keys-userd=0， -rq /path/to/db/tbl_name删除所有的索引

       3.使用load data infile导入数据

       4.如果只有读数据的请求，那么使用myisampack压缩

       5.使用myisam -rq /path/to/db/tbl_name来重建索引

       6.再执行flush table或者执行mysqladmin flush-table

当执行load data infile为一个空的表导入数据的时候，会被自动应用前面的优化。自动优化和显示过程的区别是在执行创建索引的时候可以比重建索引分配的内存多。

       也可以直接通过以下语句禁用和启用索引，如果使用以下语句就可以跳过flush table：

ALTER TABLE tbl_name DISABLE KEYS;

ALTER TABLE tbl_name ENABLE KEYS;

Ÿ   可以使用如下方式加快非事务表的插入速度

LOCK TABLES a WRITE;

INSERT INTO a VALUES (1,23),(2,34),(4,33);

INSERT INTO a VALUES (8,26),(6,29);

...

UNLOCK TABLES;

好处是，在insert执行完成之后，索引buffer只被刷新一次。如果所有的行在一个insert语句上执行那么不需要显示的锁定语句。

通过增加锁可以让数据库写入性能比较好。

Ÿ   通过增加key_buffer_size可以加快，myisam表的load data infile和insert的性能。

8.6.3 加快REPAIR TABLE

REPAIR TABLE和myisamchk的修复选项相同。有一些相同的性能优化点：

Ÿ   myisamchk可以通过变量来分配内存。通过这些变量获得更多内存。

Ÿ   repair table可以有相同的配置，但是是通过系统变量，而不是myisamchk的选项。

假设myisamchk有以下一些选项：

--key_buffer_size=128M --myisam_sort_buffer_size=256M

--read_buffer_size=64M --write_buffer_size=64M

那么对应到系统变量

myisam_sort_buffer_size和read_buffer_size有session级别和global级别的，session级别不会影响其他session，但是global级别会影响其他session，key_buffer_size是所有的session共享这块内存。

repair table可以和myisamchk类似的使用：

SET SESSION myisam_sort_buffer_size = 256*1024*1024;

SET SESSION read_buffer_size = 64*1024*1024;

SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;

SET GLOBAL repair_cache.key_buffer_size = 128*1024*1024;

CACHE INDEX tbl_name IN repair_cache;

LOAD INDEX INTO CACHE tbl_name;

REPAIR TABLE tbl_name ;

SET GLOBAL repair_cache.key_buffer_size = 0;

如果变量只想在repair table起作用：

SET @old_myisam_sort_buffer_size = @@global.myisam_max_sort_file_size;

SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;

REPAIR TABLE tbl_name ;

SET GLOBAL myisam_max_sort_file_size = @old_myisam_max_sort_file_size;

这些变量可以在服务启动的时候就配置，配置在my.cnf中

[mysqld]

myisam_sort_buffer_size=256M

key_buffer_size=1G

myisam_max_sort_file_size=100G

这里没有配置read_buffer_size，因为这个配置了之后每个session都会分配read_buffer_size的大小，会导致性能问题。

8.7 MEMORY表的优化

考虑把经常访问，并且是读多写少，不是很重要的数据放到memory表。

在innodb或者myisam下进行模拟真实环境的基准测试，确定额外的性能增加是否可以允许内存丢失，和应用启动的时候从磁盘中复制的负荷。

为了获得更好的memory性能，检查各种查询对应的表，并且制定每个使用索引的索引类型，btree索引或者hash索引。

在create index的时候使用using btree，using hash来指定btree树或者hash树。btree在>,<,between比较快，hash在=，<=>比较快。

8.8 理解查询执行计划

根据表的详细信息，列，索引和where子句中的条件，mysql优化器会考虑很多技术来有效的执行sql查询。

大表上的查询可以不必读取所有数据，join了多个表，没必要比较所有的行组合。

优化器选择的一些操作的集合被叫做查询执行计划，也被称为explain plain。

我们的工作是通过plan来识别sql是否是足够优秀的。

8.8.1 使用explain来优化查询

explain语句可以用来获取mysql如何执行语句的执行信息：

Ÿ   MySQL 5.6.3之后explain允许查看select,delete,insert,replace和update，在5.6.3之前只能查看select语句。

Ÿ   当explain用来解释一个语句，mysql会显示语句是如何执行的执行计划。

Ÿ   explain extended可以用来获取额外的执行计划信息。

Ÿ   explain partitions用来检查查询设置分区表。

Ÿ   MySQL 5.6.3之后，可以制动FORMAT选项来指定输出格式。

根据explain的帮助，你可以看到那你你需要增加索引，这样语句执行通过索引会更加快。

如果认为索引会被使用，但是有问题索引没有被使用，可以使用analyze table来更新表的统计信息。

8.8.2 EXPLAIN输出格式

explain语句提供了语句的执行计划信息。

explain会为每个表都返回一行记录。输出来的表的顺序就是MySQL读取处理语句的顺序。MySQL通过nested-loop来解决所有的join问题。

当使用extended，explain产生额外信息，使用show warnings可以显示。

8.8.2.1 EXPLAIN输出的列

Ÿ   id

select标识符，是一个是查询中select的位置。如果是一个union的结果那么可以为null，这样table列会显示<unionM,N>，M和N是select标识符。

Ÿ   select_type

select类型，可以有以下一些值

dependent通常表示使用相关子查询。dependent subquery和uncacheable subquery评估方式不同。

对于dependent subquery，只查询只会被不同的值进行评估。对于uncacheable subquery，子查询会为外表每一行进行评估。

Ÿ   table

行来至于一个表的表名，也可以是以下值：

<unionM,N>：行来至于select标识符为M和N的union

<derivedN>：行来至于标识符为N的衍生表

<subqueryN>：行来自于标识符为N的子查询

Ÿ   partitions

表示来自于某个的分区来匹配查询，只有在partition关键字被指定的时候才会显示。对于非分区表则为null。

Ÿ   type

join类型

Ÿ   possible_key

这个列表示，查询可以使用的一些索引，如果为null表示没有相关的索引可用。

Ÿ   key

表示mysql要使用的索引。

当命名索引覆盖了列，但是扫描笔索引查询性能更好，就有可能key的名字不再possible_key中。

       如果为null索引mysql没有发现合适的索引应用到查询。

       对于使用force index，use index或者ignore index会忽略possible_keys的列表。

Ÿ   key_len

key_len表示mysql使用的key的长度

Ÿ   ref

表示那个列或者常量被用来比较索引的键值。

Ÿ   rows

rows表示预估会响应的行数

Ÿ   filtered

预估有百分之多少的数据会被过滤掉。

Ÿ   extra

说明mysql解决查询问题额外的信息。

8.8.2.2 EXPLAIN join类型

join类型有一下几个：

Ÿ   system

表和只有一行，是const的特例

Ÿ   const

表只能匹配到一行，在查询开始就被读取，const只会被读取一次。当比较primary key和unique所有的部分；

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name

  WHERE primary_key_part1=1 AND primary_key_part2=2;

Ÿ   eq_ref

为前表的每个行组合读取一行数据，这个join类型是除了system，const之外最好的。

当索引的所有部分都被使用时，会使用eq_ref并且索引要是primary key或者unique not null索引。

eq_ref的比较值使用等号比较，比较值可以是常量或者使用了之前读入表的列的表达式。以下情况会使用eq_ref

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column_part1=other_table.column

  AND ref_table.key_column_part2=1;

Ÿ   ref

为之前表的每个行组合，读出所有匹配的索引行。ref只有在join负荷左前缀的时候使用，并且key不是primary key或者unique。如果key只匹配到了一些列，那么这个join type是好的join type。

ref可以使用=或者<=>比较符

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column_part1=other_table.column

  AND ref_table.key_column_part2=1;

Ÿ   fulltext

join使用fulltext执行

Ÿ   ref_or_null

这个类型和ref的区别是，会另外找包含null值的。下面的情况会被应用：

SELECT * FROM ref_table

       WHERE key_column=expr OR key_column IS NULL;

Ÿ   index_merge

这个join类型索引使用了索引合并优化，这种情况下key中包含2个以上索引，key_len是使用的索引中最长的部分。

Ÿ   unique_subquery

这个类型会在有in子句的情况下代替ref。

value IN (SELECT primary_key FROM single_table WHERE some_expr)

Ÿ   index_subquery

和unique_subquery类似，只是使用的是secondary索引

value IN (SELECT key_column FROM single_table WHERE some_expr)

Ÿ   range

只有在给定范围内的行会被获取，使用索引来获取选中的行。key_len是使用的最长key长度。ref列为null。

range 可以在以下任意的表达式下使用：=,<=>,>,<,<>,>=,<=,is null,between,in()。

SELECT * FROM tbl_name

  WHERE key_column = 10;

SELECT * FROM tbl_name

  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name

  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name

  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

Ÿ   index

index和ALL一样是指获取所有的索引扫描，只有2中情况会发生：

s   只有当索引是覆盖索引并且可以通过索引扫描来完成，这种情况下extra有using index。index通常比all要快，因为index的数据比all要少。

s   全表被扫描是通过索引按索引的顺序读取，using index不会出现在extra列中。

Ÿ   all

为上一个表的每个组合做一次全表扫描，如果第一个表不是const这个情况并不是很好。通常可以通过创建索引，并且通过常量或者之前表的列值来获取。

8.8.2.3 EXPLAIN 扩展信息

extra列包含了explain输出的额外信息。

Ÿ   Child of ‘table’ pushed join@1

在join中这个表被认为是table的子表，可以通过push down条件到ndb内核。只能应用在mysql集群

Ÿ   const row not found

执行select…from tbl_name，这个表是空的。

Ÿ   deleting all row

对于某些存储引擎，可以简单的删除所有的行，这个值表示存储引擎是否有这种优化。

Ÿ   distinct

mysql查找distinct值，找到一个匹配之后就会立刻停止。

Ÿ   FirstMatch(tbl_name)

对tbl_name进行了semi-join firstmatch优化。

Ÿ   Full scan on NULL key

子查询优化当优化器不能使用索引查找访问的时候，采用回退策略。

Ÿ   impossible having

having 条件都为flase，不会返回行

Ÿ   impossible where

where子句为flase

Ÿ   impossible where noticed after reading const tables

到读取玩所有const和system表的时候才发现where条件为flase。

Ÿ   loosescan(m…n)

semi-join loosescan策略，m和n是key的一部分

Ÿ   materialize，scan

在mysql 5.6.7之前，这个只能用来表示物化临时表。如果有scan表示在表读取的时候没有索引临时表的索引。

在mysql 5.6.7之后，物化由select_type使用materialized说明，并且table的值为<subqueryN>。

Ÿ   no matching min/max row

没有满足的查询的条件，select min() from …where condition

Ÿ   no matching row in const table

一个查询使用了join，有空表，或者没有匹配上的行。

Ÿ   no matching rows after partition pruning

发生分区清理之后发现没有东西能够被delete或者update，和impossible where意思一样。

Ÿ   no tbales userd

查询没有from子句或者from dual。

Ÿ   not exists

mysql可以使用left join优化，之前的行组合不在这个表中的数据。

Ÿ   range checked for each reord(index map: N)

mysql发现没有好的索引可以用，但是发现如果获取了之前表的值可以一些索引就可以被使用了。尽管不是很快，比没有索引的情况下还快。

Ÿ   scanned N databases

表示当处理在information_schema上的查询的时候有多少目录扫描。

Ÿ   select tables optimized away

当查询只包含max，min聚合函数的时候，通过索引来解决。

Ÿ   skip_open_table,open_frm_only,open_trigger_only,open_full_table

skip_open_table:表不需要被打开

open_frm_only：只需要打开frm文件

open_trigger_only：只有trg文件需要被打开

open_full_table：所有的文件都要被打开。

Ÿ   start materialize，end materialize，scan

在mysql 5.6.7之前，这个只能用来表示物化临时表。如果有scan表示在表读取的时候没有索引临时表的索引。

在mysql 5.6.7之后，物化由select_type使用materialized说明，并且table的值为<subqueryN>。

Ÿ   start temporary，end temporary

表示临时表被用来semi-join去重复策略

Ÿ   unique row not found

没有满足在primary key和unique上的行。

Ÿ   using filesort

MySQL必须做一些事，让数据以排序的顺序被读取。

Ÿ   using index

只从index上读取数据，不从表上读取数据。如果extra还有using where，意味着用使用索引来做索引查找。如果没有using where优化器会读索引但是不表示是索引查找。

Ÿ   using index condition

表通过上访问索引和过滤索引的方式来确定是否要读取所有的表数据。这种方式下，索引用来推迟访问表数据，除非有需要。

Ÿ   using index for group-by

和using index 类似，这个表名mysql发现有索引可以用来group by或者distinct

Ÿ   using join buffer(block nested loop),using join buffer(batched key access)

block nested loop表名block nested-loop算法，batched key access表示使用batched key access算法

Ÿ   using MRR

表示使用了multi-reange read优化策略

Ÿ   using sort_union(),using union(),using intersect()

表示索引如何被合并

Ÿ   using temporary

为了处理查询，mysql需要创建一个临时表来保存中间结果

Ÿ   using where

where子句用来限制传到下一个表或者输出的记录。

Ÿ   using where with pushed condition

这个只能应用在ndb表上。吧where条件push到数据节点。

8.8.2.4 EXPLAIN 输出说明

略，具体看：http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

8.8.3 EXPLAIN EXTENDED输出格式

当使用了extended，输出会增加filtered列，否则就不会输出。这个列评估了有多少列会被过滤掉。使用extended然后show warnings可以输出一下信息：

<auto_key>:自动为临时表生成的key

<cache>(expr):表达式只会被执行一次，然后结果存放在内存中

<exists>(query fragment):子查询被转化为了exists谓词

<in_optimizer>(query fragment):优化器内部对象

<index_lookup>(query fragment):query fragment通过索引查找来获取记录

<if>(condition,expr1,expr2):如果条件为true，返回expr1的结果，否则返回expr2的结果

<is _not_null_test>(expr):验证表达式是不是为null

<materialize>(query fragment):子表达式被物化

`materialized-subquery`.col_name,`materialized subselect`.col_name:引用物化临时表的列。

<primary_index_lookup>(query fragment):使用primary key来查找行。

<ref_null_helper>(expr):优化器内部对象

/* select#N*/ select_stmt:表示一个sql语句N是语句的id

outer_tables semi join(inner join):semi join操作。

<temporary table>:内部临时表，用来保存中间结果。

8.8.4 评估查询性能

很多时候，可以使用磁盘seek来评估查询性能。对于小的表，访问一行只需要一次查询。

但是对于大表来说使用btree，查找一行的开销是：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1.

在mysql，索引块是通常是1024个字节并且数据指针为4个字节，对于50万行的表，key值的长度为3个字节，那么花费就是：log(500,000)/log(1024/3*2/(3+4)) + 1 = 4

索引会占用的空间时500000*(3+4)*3/2=5.2MB，通常索引buffer的填充率是2/3，所以有可能很多索引在内存中，只需要1,2个读取就可以找到行。

对于写入，需要4个seek来查找定位索引的值，并且2个seek来更新索引并且写入行。

当数据变大，不能够放到内存，开始变慢知道全部依赖于硬盘为止。

8.8.5 控制查询优化器

mysql 提供了可以根据系统便来来控制和影响执行计划。

8.8.5.1 控制执行计划评估

优化器的任务时找到一个最优化执行计划执行。当大的查询被提交，可能会有很多时间需要花费在寻找查询优化上。

有2个参数可以控制优化器评估计划个数：

Ÿ   optimizer_prune_level

这个变量通知优化器基于访问表行数跳过计划，而获得要执行的计划，这个参数为1会通过表的访问行数来跳过计划，如果参数为0那么会全面的查找执行计划，会导致编译时间过长。

Ÿ   optimizer_seach_depth

变量决定优化器的深度，值越小，说明编译时间越短。如果不知道如何设置可以设置为0由mysql自己决定。

8.8.5.2 控制开关优化(Controlling Switchable Optimizations)

optimizer_switch系统变量可以控制优化器行为。查看当前的optimizer_switch设置：

mysql> SELECT @@optimizer_switch\G

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

@@optimizer_switch: index_merge=on,index_merge_union=on,

                    index_merge_sort_union=on,

                    index_merge_intersection=on,

                    engine_condition_pushdown=on,

                    index_condition_pushdown=on,

                    mrr=on,mrr_cost_based=on,

                    block_nested_loop=on,batched_key_access=off,

                    materialization=on,semijoin=on,loosescan=on,

                    firstmatch=on,

                    subquery_materialization_cost_based=on,

                    use_index_extensions=on

设置optimizer_switch

SET [GLOBAL|SESSION] optimizer_switch=‘command[,command]...‘;

command为：

可用的opt_name：