SQL语句的执行顺序：

(5)select 列a ， 聚合函数 （1） from 表 （2） where 过滤条件 （3） group by 列a （4） having 聚合函数过滤条件 （6） order by

数据库优化的意义： 更高的吞吐量，更快的响应时间

数据库查询优化：

广义的数据库查询优化： 1. 查询重用技术； 2.查询重写规则； 3.查询算法优化技术； 4.并行查询优化技术

5. 分布式查询优化技术。 6.其他方面（如框架结构）的优化技术；

狭义的数据库查询优化： 1. 查询重写规则； 2. 查询算法优化。

从优化的内容角度 分为逻辑优化和物理优化：

逻辑优化主要根据关系代数的等价变换做一些逻辑变换，基于关系代数和启发式规则。

物理优化主要根据数据读取，表连接方式，表连接顺序，排序对查询进行优化，基于代价估算模型，查询算法优化属于这一类。

逻辑优化：

1 子查询优化

子查询出现的位置： 1. 目标列：

2. From子句：

3. Where子句：

4. Join/On子句：

5. Group by子句：

6. Order by子句：

从对象间的关系看：

相关子查询：

子查询的执行依赖于外层父查询的一些属性值。子查询因依赖于父查询的参数，当父 查询的参数改变时，子查询需要根据新参数值重新执行（查询优化器对相关子查询进行优化有一定意义）

非相关子查询：

子查询的执行，不依赖于外层父查询的任何属性值。这样子查询具有独立性，可独自求解，形成一个子查询计划先于外层的查询求解

从特定谓词看：

1 [NOT] IN/ALL/ANY/SOME子查询。

语义相近，表示“[取反] 存在/所有/任何/任何”，左面是操作数，右面是子查询，是最常见的子查询类型之一。

2[NOT] EXISTS子查询。

半连接语义，表示“[取反] 存在”，没有左操作数，右面是子查询，也是最常见的子查询类型之一。

3 其他子查询。

除了上述两种外的所有子查询。

从语句的构成复杂程度看：

1 SPJ子查询。

由选择、连接、投影操作组成的查询。

2 GROUPBY子查询。

SPJ子查询加上分组、聚集操作组成的查询。

3 其他子查询。

GROUPBY子查询中加上其他子句如Top-N 、LIMIT/OFFSET、集合、排序等操作。

后两种子查询有时合称非SPJ子查询。

从结果的角度看：

1 标量子查询。

子查询返回的结果集类型是一个简单值（return a scalar，a single value）。

2 单行单列子查询。

子查询返回的结果集类型是零条或一条单元组（return a zero or single row，but only a column）。相似于标量子查询,但可能返回零条元组。

3 多行单列子查询。

子查询返回的结果集类型是多条元组但只有一个简单列（return multiple rows，but only a column）。

4 表子查询。

子查询返回的结果集类型是一个表（多行多列）（returna table，one or more rows of one or more columns）。

子查询优化的意义：

在数据库实现早期，查询优化器对子查询一般采用嵌套执行的方式，即对父查询中的每一行，都执行一次子查询，这样子查询会执行很多次。这种执行方式效率很低。

而对子查询进行优化，可能带来几个数量级的查询效率的提高。

子查询转变成为连接操作之后，会得到如下好处：

1 子查询不用执行很多次。

2 优化器可以根据统计信息来选择不同的连接方法和不同的连接顺序。

子查询中的连接条件、过滤条件分别变成了父查询的连接条件、过滤条件，优化器可以对这些条件进行下推，以提高执行效率。

子查询优化的方式：

1. 子查询合并：

在某些条件下（语义等价：两个查询块产生同样的结果集），多个子查询能够合并成一个子查询（合并后还是子查询，以后可以通过其他技术消除掉子查询）。这样可以把多次表扫描、多次连接减少为单次表扫描和单次连接，如：

SELECT * FROM t1 WHERE a1<10 AND (

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2)

);

可优化为：

SELECT * FROM t1 WHERE a1<10 AND (

EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND (t2.b2=1 OR t2.b2=2)

/*两个ESISTS子句合并为一个，条件也进行了合并 */

);

2. 子查询展开（子查询上拉）：

把一些子查询置于外层的父查询中，作为连接关系与外层父查询并列，其实质是把某些子查询重写为等价的多表连接操作（展开后，子查询不存在了，外部查询变成了多表连接）。

带来的好处是，有关的访问路径、连接方法和连接顺序可能被有效使用，使得查询语句的层次尽可能的减少常见的IN/ANY/SOME/ALL/EXISTS依据情况转换为半连接（SEMI JOIN）、普通类型的子查询消除等情况属于此类，如：

SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 >10) v_t2

WHERE t1.a1<10 AND v_t2.a2<20;

可优化为：

SELECT * FROM t1, t2 WHERE t1.a1<10 AND t2.a2<20 AND t2.a2 >10;

/* 子查询变为了t1、t2表的连接操作，相当于把t2表从子查询中上拉了一层 */

使用子查询展开的条件：

1 如果子查询中出现了聚集、GROUPBY、DISTINCT子句，则子查询只能单独求解，不可以上拉到外层

2 如果子查询只是一个简单格式的（SPJ格式）查询语句，则可以上拉子查询到外层，这样往往能提高查询效率。子查询上拉，讨论的就是这种格式，这也是子查询展开技术处理的范围。

把子查询上拉到上层查询：

前提是上拉（展开）后的结果不能带来多余的元组，所以子查询展开需要遵循如下规则：

1 如果上层查询的结果没有重复（即SELECT子句中包含主码），则可以展开其子查询。并且展开后的查询的SELECT子句前应加上DISTINCT标志。

2 如果上层查询的SELECT语句中有DISTINCT标志，可以直接进行子查询展开。

如果内层查询结果没有重复元组，则可以展开。

子查询展开的具体步骤：

1) 将子查询和外层查询的FROM子句连接为同一个FROM子句，并且修改相应的运行参数。

2) 将子查询的谓词符号进行相应修改(如：“IN”修改为“=”)。

3) 将子查询的WHERE条件作为一个整体与外层查询的WHERE条件合并，并用AND条件连接词连接，从而保证新生成的谓词与原旧谓词的上下文意思相同，且成为一个整体

3. 聚集子查询消除：

MySQL支持的子查询优化技术：

支持的：

1. MySQL支持对简单SELECT查询中的子查询优化，包括：

简单SELECT查询中的子查询。

带有DISTINCT、ORDERBY、LIMIT操作的简单SELECT查询中的子查询。

2. MySQL只有在针对主键列进行类似的子查询时，才把子查询上拉为内连接。所以，MySQL还是支持子查询展开技术的

不支持的：

1. MySQL不支持对如下情况的子查询进行优化：

带有UNION操作。

带有GROUPBY、HAVING、聚集函数。

使用ORDERBY中带有LIMIT。

内表、外表的个数超过MySQL支持的最大表的连接数。

2. MySQL不支持子查询合并技术

3. 子查询展开（子查询反嵌套）技术,支持得不够好

4. 聚集子查询消除、技术,不支持

2 视图重写

什么是视图：

视图是数据库中基于表的一种对象, 把对表的查询固化,这种固化就是视图。

注意区分:

视图<——>物化视图<——>物化(技术)

视图的类型：

1 用SPJ格式构造的视图，称为简单视图。

CREATE VIEW v1 AS SELECT x, y, z FROM t;

2 用非SPJ格式构造的视图（带有GROUPBY等操作），称为复杂视图。

CREATE VIEW v2 AS SELECT x, y, z FROM t ORDER BY x;

什么是视图重写：

1 查询语句中出现视图对象

2 查询优化后,视图对象消失

3 消失的视图对象的查询语句, 融合到初始查询语句中

视图重写示例

CREATE TABLE t_a(a INT, b INT);

CREATE VIEW v_a AS SELECT * FROM t_a;

基于视图的查询命令如下：

SELECT col_a FROM v_a WHERE col_b>100;

经过视图重写后可变换为如下形式：

SELECT col_a FROM

(

SELECT col_a, col_b FROM t_a

)

WHERE col_b>100;

未来经过优化，可以变换为如下等价形式：

SELECT col_a FROM t_a WHERE col_b>100;

MySQL视图重写准则：

1 MySQL支持对视图进行优化。

2 优化方法是把视图转为对基表的查询，然后进行类似子查询的优化。

3 MySQL通常只能重写简单视图，复杂视图不能重写

3 等价谓词重写

把逻辑表达式重写成等价的且效率更高的形式

优点：

能有效提高查询执行效率

常见的等价谓词重写示例1：

LIKE规则

LIKE谓词，是SQL标准支持的一种模式匹配比较操作；

LIKE规则，是对LIKE谓词的等价重写，即改写LIKE谓词为其他等价的谓词，以更好地利用索引进行优化。

LIKE规则

示例如：

name LIKE ‘Abc%’

重写为

name >=’Abc’ AND name <‘Abd’

应用LIKE规则的好处：

转换前针对LIKE谓词，只能进行全表扫描，如果name列上存在索引，则转换后可以进行索引扫描。

示例再如，LIKE其他形式还可以转换：

LIKE匹配的表达式中，没有通配符（%或_），则与“=”等价，

如：

name LIKE ‘Abc’

重写为：

name =’Abc’

如果name列上存在索引，则可以利用索引提高查询效率

BETWEEN-AND规则

BETWEEN-AND谓词，是SQL标准支持的一种范围比较操作；

BETWEEN-AND规则，是BETWEEN-AND谓词的等价重写，即改写BETWEEN-AND谓词为其他等价的谓词，以更好地利用索引进行优化。

如：

sno BETWEEN 10 AND 20

重写为：

sno>=10 AND sno <=20

应用BETWEEN-AND规则的好处是：

如果sno上建立了索引，则可以用索引扫描代替原来BETWEEN-AND谓词限定的全表扫描，从而提高了查询的效率。

IN转换OR规则：

IN是只IN操作符操作，不是IN子查询。

IN转换OR规则，就是IN谓词的OR等价重写，即改写IN谓词为等价的OR谓词，以更好地利用索引进行优化。

将IN谓词等价重写为若干个OR谓词，可能会提高执行效率。

如：

age IN (8，12，21)

重写为：

age=8 OR age=12 OR age=21

应用IN转换OR规则后效率是否能够提高，需要看数据库对IN谓词是否只支持全表扫描。

如果数据库对IN谓词只支持全表扫描且OR谓词中表的age列上存在索引，则转换后查询效率会提高。

IN转换ANY规则

IN转换ANY规则，就是IN谓词的ANY等价重写，即改写IN谓词为等价的ANY谓词。

IN可以转换为OR，OR可以转为ANY，所以可以直接把IN转换为ANY。

将IN谓词等价重写为ANY谓词，可能会提高执行效率。

如：

age IN (8，12，21)

重写为：

age ANY(8, 12, 21)

应用IN转换ANY规则后效率是否能够提高，依赖于数据库对于ANY操作的支持情况。

OR转换ANY规则：

OR转换ANY规则，就是OR谓词的ANY等价重写，即改写OR谓词为等价的ANY谓词，以更好地利用MIN/MAX操作进行优化。

ALL/ANY转换集函数规则

ALL/ANY转换集函数规则，就是ALL/ANY谓词改写为等价的聚集函数MIN/MAX谓词操作，以更好地利用MIN/MAX操作进行优化。

如：

sno>ANY(10, 2*5+3,sqrt(9))

重写为：

sno>sqrt(9)

上面这个ALL/ANY转换集函数规则的示例，有两点需要注意：

1 示例中存在“>”和“ANY”，其意是在找出“(10, 2*5+3,sqrt(9))”中的最小值，所以可以重写为“sno>sqrt(9)”。

通常，聚集函数MAX()、MIN()等的执行效率一般都比ANY、ALL谓词的执行效率高，因此在这种情况下对其进行重写，可以起到比较好的效果。

2 如果有索引存在，求解MAX/MIN的效率更高。

NOT规则

NOT谓词的等价重写，如下：

NOT (col_1 !=2) 重写为 col_1=2

NOT (col_1 !=col_2)重写为 col_1=col_2

NOT (col_1 =col_2) 重写为 col_1!=col_2

NOT (col_1 <col_2) 重写为 col_1>=col_2

NOT (col_1 >col_2) 重写为 col_1<=col_2

NOT规则重写的好处：

如果col_1上建立了索引，则可以用索引扫描代替原来的全表扫描，从而提高查询的效率。

OR重写并集规则

OR条件重写为并集操作，形如下SQL示例：

SELECT *

FROM student

WHERE(sex=’f’ AND age>15) OR age>18；

假设所有条件表达式的列上都有索引（即sex列和age列上都存在索引），数据库可能对于示例中的WHERE语句强迫查询优化器使用顺序扫描，因为这个语句要检索的是OR操作的集合。

为了能利用索引处理上面的查询，可以将语句改成如下形式：

SELECT *

FROM student

WHERE sex=’f’ and age>15

UNION

SELECT *

FROM student

WHERE age>18;

改写后的形式，可以分别利用列sex和age上的索引，进行索引扫描，然后再提供执行UNION操作获得最终结果

4 条件化简

什么是条件?

SQL查询语句中，对元组进行过滤和连接的表达式

形式上是出现在WHERE/JOIN-ON/HAVING的子句中的表达式。

条件优化技术：

1. 条件下推

把与单个表相关的条件，放到对单表进行扫描的过程中执行

说明：

数据库系统都支持条件下推，且无论条件对应的列对象有无索引

系统自动进行优化，不用人工介入

2. 条件化简

1 WHERE、HAVING和JOIN-ON条件由许多表达式组成，而这些表达式在某些时候彼此之间存在一定的联系。

2 利用等式和不等式的性质，可以将WHERE、HAVING和ON条件化简

3 但不同数据库的实现可能不完全相同。

条件化简技术归总：

（一）把HAVING条件并入WHERE条件：

优点：

便于统一、集中化解条件子句，节约多次化解时间。

注意：

不是任何情况下HAVING条件都可以并入WHERE条件，只有在SQL语句中不存在GROUPBY条件或聚集函数的情况下，才能将HAVING条件与WHERE条件的进行合并。

（二）去除表达式中冗余的括号：

优点：

可以减少语法分析时产生的AND和OR树的层次。—减少CPU的消耗

示例：

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

化简为

a AND b AND c AND d

（三）常量传递：

优点：

对不同关系可以使得条件分离后有效实施“选择下推”，从而可以极大减小中间关系的规模。

示例：

col_1 = col_2 AND col_2 = 3

化简为

col_1=3 AND col_2=3。

示例：

col_1 = col_2 AND col_2 = 3

转化为

[col_1, col_2, 3],

所以可以推知：

col_1=3 AND col_2=3

注意：

操作符“=、<、>、<=、>=、<>、<=>、LIKE”中的任何一个，在“col_1 <操作符> col_2”条件中都可能会发生常量传递

（四）消除死码：

化简条件，将不必要的条件去除。

示例：

WHERE(0 > 1 AND s1 = 5)，

“0 > 1”使得AND恒为假，则WHERE条件恒为假。

此时就不必要再对该SQL语句进行优化和执行了，加快了查询执行的速度

（五）表达式计算：

对可以求解的表达式，进行计算，得出结果。

示例：

WHERE col_1 = 1 + 2

变换为

WHERE col_1 = 3。

（六）等式变换：

化简条件（如反转关系操作符的操作数的顺序），从而改变某些表的访问路径

示例：

-a = 3

可化简为

a = -3

这样的好处是如果a上有索引，则可以利用索引扫描来加快访问。

（七）不等式变换：

化简条件，将不必要的重复条件去除。

示例：

a > 10 AND b = 6 AND a > 2

可化简为

b = 6 AND a > 10

（八）布尔表达式变换—谓词传递闭包

一些比较操作符，如“<”、“>”等，具有传递性，可以起到化简表达式的作用

示例：

a>b AND b>2

可以推导出a>b AND b>2 AND a>2，“a>2”是一个隐含条件，

这样把“a>2”和“b>2”分别下推到对应的关系上，

就可以减少参与比较操作“a>b”的元组了

（八）布尔表达式变换—布尔表达式被转换为等价的合取范式：

说明：

1 合取项只要有一个为假，整个表达式就为假，故代码中可以在发现一个合取项为假时，即停止其他合取项的判断，加快判断速度；

WHERE(0 > 1 AND s1 = 5)

2 另外因为AND操作符是可交换的，所以优化器可以按照先易后难的顺序计算表达式，一旦发现一个合取项为假时，即停止其他合取项的判断，加快判断速度。

WHERE(A.a+B.b > 100 AND A.b = 5 AND 0 > 1 )

先求解：0 > 1 ，值为假，其他不再求解

（八）布尔表达式变换—索引的利用：

如果一个合取项上存在索引，则先判断索引是否可用，如能利用索引快速得出合取项的值，则能加快判断速度。

同理，OR表达式中的子项也可以利用索引

示例：

WHERE (A.a> 100 AND A.b = 5 AND… )

情况1：A表的a列上存在索引，b列无索引，则利用a上的索引找出元组，“A.b = 5” 作为过滤条件使用

情况2：A表的a列上不存在索引，b列有索引，则利用b上的索引找出元组，“A.a> 100” 作为过滤条件使用

MySQL对条件化简技术的支持

（二）去除表达式中冗余的括号：支持

（三）常量传递：支持

（四）消除死码：支持

（五）表达式计算：支持

（八）布尔表达式变换—布尔表达式被转换为等价的合取范式：支持

（八）布尔表达式变换—索引的利用—AND操作符是可交换的：支持

（九）IS NULL表达式优化：支持利用索引，支持“IS NULL”表达式的优化。

MySQL不支持的条件化简技术

（一）把HAVING条件并入WHERE条件：不支持

（六）等式变换：不支持

（七）不等式变换：不支持

（八）布尔表达式变换—谓词传递闭包：不支持

5 外连接消除

外连接消除：

把外连接变为内连接

A OUTER JOIN B

变形为

A JOIN B

外连接消除的意义：

1 查询优化器在处理外连接操作时所需执行的操作和时间多于内连接

2 外连接消除后，优化器在选择多表连接顺序时，可以有更多更灵活的选择，从而可以选择更好的表连接顺序，加快查询执行的速度

3 表的一些连接算法（如块嵌套连接和索引循环连接等）在将规模小的或筛选条件最严格的表作为“外表”（放在连接顺序的最前面，是多层循环体的外循环层），可以减少不必要的I/O开销，能加快算法执行的速度

内连接语句：

SELECT *

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

INNER JOIN T3 ON P2(T2,T3)

WHERE P(T1,T2,T3)

FOR each row t1 in T1 {

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

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

IF P(t1,t2,t3) {

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

}

}

}

}

外连接语法：

SELECT *

FROM T1 LEFT JOIN

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

ON P1(T1,T2)

WHERE P(T1,T2,T3)

FOR each row t1 in T1 {

BOOL f1:=FALSE;

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

BOOL f2:=FALSE;

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

IF P(t1,t2,t3) {

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

}

f2=TRUE;

f1=TRUE;

}

IF (!f2) {

IF P(t1,t2,NULL) {

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

}

f1=TRUE;

}

}

IF (!f1) {

IF P(t1,NULL,NULL) {

t:=t1||NULL||NULL; OUTPUT t;

}

}

}

外连接消除的条件：

WHERE子句中的条件满足“空值拒绝”

（又称为“reject-NULL”条件）。

WHERE条件可以保证从结果中排除外连接右侧（右表）生成的值为NULL的行（即条件确保应用在右表带有空值的列对象上时，条件不满足，条件的结果值为FLASE或UNKONOWEN，这样右表就不会有值为NULL的行生成），所以能使该查询在语义上等效于内连接。

explain

SELECT * FROM X LEFT JOIN Y ON (X.X_num=Y.Y_num)

WHERE Y.Y_num IS NOT NULL;

语句一，使用TRUE作为ON的子句，WHERE子句包括连接条件：

SELECT * FROM t_1 LEFT JOIN t_2 ON true WHERE t_1_id = t_2_id;

语句三，使用ON和WHERE子句包括连接条件：

SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id WHERE t_1_id = t_2_id;

WHERE条件可以保证从结果中排除外连接右侧（右表）生成的值为NULL的行

外连接消除示例1：—辨析ON和WHERE的差异

ON t_1_id = t_2_id：

t_1_id 和 t_2_id 进行连接

WHERE t_1_id = t_2_id：

当t_1_id 和 t_2_id的值相等

语句四，外表的索引列出现在WHERE子句中：

EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id WHERE t_1_id>0;

语句五，内表的索引列出现在WHERE子句中： 外连接退化为内连接

EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id WHERE t_2_id>0;

外连接消除总结:

1 注意外连接与内连接的语义差别

2 外连接优化的条件：空值拒绝

3 外连接优化的本质：语义上是外连接，但WHER条件使得外连接可以蜕化为内连接

6 连接消除

去掉不必要的连接对象，则减少了连接操作

连接消除的条件：

无固定模式，具体问题具体处理

连接消除情况一：

唯一键/主键作为连接条件，三表内连接可以去掉中间表（中间表的列只作为连接条件）

CREATE TABLE A (a1 INT UNIQUE, a2 VARCHAR(9), a3 INT);

CREATE TABLE B (b1 INT UNIQUE, b2 VARCHAR(9), c2 INT);

CREATE TABLE C (c1 INT UNIQUE, c2 VARCHAR(9), c3 INT);

B的列在WHERE条件子句中只作为等值连接条件存在，则查询可以去掉对B的连接操作：

SELECT A.*, C.* FROM A JOIN B ON (a1=b1) JOIN CON (b1=c1);

相当于：

SELECT A.*, C.* FROM A JOIN C ON (a1= c1);

连接消除情况二：

一些特殊形式，可以消除连接操作（可消除的表除了作为连接对象外，不出现在任何子句中）。

示例：

SELECT MAX(a1) FROM A, B;/* 在这样格式中的MIN、MAX函数操作可以消除连接，去掉B表不影响结果；其他聚集函数不可以 */

SELECT DISTINCT a3 FROM A, B; /* 对连接结果中的a3列执行去重操作*/

SELECT a1 FROM A, B GROUP BY a1;/* 对连接结果中的a1列执行分组操作 */

连接消除情况三：

主外键关系的表进行的连接，可消除主键表，这不会影响对外键表的查询

连接消除示例1：—对主外键参照的表进行内连接，可以消除主键表，MySQL不支持

连接消除示例2：—对主外键参照的表进行外连接，可以消除主键表，MySQL不支持

连接消除总结：

注意连接消除与外连接消除的技术差别

连接消除去掉的是被连接的某个对象

外连接消除去掉的是外连接的语义，变形为内连接

7 嵌套连接消除

什么是嵌套连接消除：

连接存在多个层次，用括号标识连接的优先次序。

嵌套连接消除，就是消除嵌套的连接层次，把多个层次的连接减少为较少层次的连接，尽量“扁平化”

SQL语句的语义是B和C先连接，然后再和A连接。查询执行计划如下：

mysql> EXPLAIN EXTENDED SELECT * FROM A JOIN (B JOIN C ON B.b1=C.c1) ON A.a1=B.b1

WHERE A.a1 > 1;

+—-+————-+——-+————+——+——+———-+——————————————–

| id | select_type | table | partitions | type | rows | filtered | Extra

+—-+————-+——-+————+——+——+———-+——————————————–

| 1 | SIMPLE | A | NULL | ALL | 7 | 33.33 | Using where |

| 1 | SIMPLE | B | NULL | ALL | 7 | 14.29 | Using where; Using join buffer (Block Nested Loop) |

| 1 | SIMPLE | C | NULL | ALL | 7 | 14.29 | Using where; Using join buffer (Block Nested Loop) |

嵌套连接消除总结：

1 嵌套连接消除的连接的层次,这是一种连接的语义顺序的变化

2 连接消除，消掉的是一些被连接的对象

3 外连接消除，消掉的是外连接的语义，使得外连接变形为内连接

8 语义优化

数据完整性分为四类：

1 实体完整性（Entity Integrity）:自己

2 域完整性（Domain Integrity）: 自己的局部

3 参照完整性（Referential Integrity）: 自己与其他”实体”的关系

4 用户自定义完整性（User-definedIntegrity）:用户增加的限制

因为语义的原因，使得SQL可以被优化。包括两个基本概念：

1 语义转换。因为完整性限制等的原因使得一个转换成立的情况称为语义转换。

2 语义优化。因为语义转换形成的优化称为语义优化。

语义转换其实是根据完整性约束等信息对“某特定语义”进行推理，进而得到的一种查询效率不同但结果相同的查询。

语义优化是从语义的角度对SQL进行优化，不是一种形式上的优化，所以其优化的范围，可能覆盖其他类型的优化范围。

语义优化常见的方式：

1 连接消除（Join Elimination）：

对一些连接操作先不必评估代价，根据已知信息（主要依据完整性约束等，但不全是依据完整性约束）能推知结果或得到一个简化的操作。

例如：

利用A、B两个基表做自然连接，创建一个视图V，如果在视图V上执行查询只涉及其中一个基表的信息，则对视图的查询完全可以转化为对某个基表的查询。

2 连接引入（Join Introduction）。

增加连接有助于原始关系变小或原关系的选择率降低

3 谓词引入（Predicate Introduction）：

根据完整性约束等信息，引入新谓词，如引入基于索引的列，可能使得查询更快；

例如：

一个表上，有“c1<c2”的列约束，c2列上存在一个索引，查询语句中的WHERE条件有“c1>200”，则可以推知“c2>200”，

WHER条件变更为“c1>200 AND c2>200 AND c1<c2”，

由此可以利用c2列上的索引，对查询语句进行优化；如果c2列上的索引的选择率很低，则优化效果会更高。

4 检测空回答集（Detecting the Empty Answer Set）：

查询语句中的谓词与约束相悖，可以推知条件结果为FALSE，也许最终的结果集能为空；

例如：

CHECK约束限定“score”列的范围是60到100，而一个查询条件是“score<60”，则能立刻推知条件不成立。

5 排序优化（Order Optimizer）：

ORDERBY操作通常由索引或排序（sort）完成；如果能够利用索引，则排序操作可省略；另外，结合分组等操作，考虑ORDERBY操作的优化。

6 唯一性使用（Exploiting Uniqueness）：

利用唯一性、索引等特点，检查是否存在不必要的DISTINCT操作

例如：

在主键上执行DISTINCT操作，若有则可以把DISTINCT消除掉

MySQL支持的语义优化：

1. 语义优化中的检测空回答集技术，MySQL支持

3. 语义优化中的排序优化，MySQL支持，但条件较为苛刻。

4. 语义优化技术中的唯一性使用，MySQL支持。

MySQL不支持的语义优化：

2. 语义优化中的谓词引入技术，MySQL不支持。

5. 语义优化中的连接消除技术，MySQL不支持。

9 非SPJ的优化

非SPJ优化的意义：

历史:

早期的关系数据库系如System-R ，对GROUPBY和聚集等操作一般

都放在其所在的查询的最后进行处理，即在执行完所有的连接和选择操

作之后再执行GROUPBY和聚集。

优缺点:

处理方式比较简单，编码容易，但执行效率会比较低。

1. Group by优化：

SELECT COUNT(s.classID) as StudentNum, s.classID

FROM STUDENT AS s, CLASS AS c

WHERE S.ID=c.SID

GROUP BY s.classID

优化方式：

分组转换技术:

即对分组操作、聚集操作与连接操作的位置进行交换

常见分组转换技术:

1 分组操作下移： GROUPBY操作可能较大幅度减小关系元组的个数，如果能够

对某个关系先进行分组操作，然后再进行表之间的连接，很可能提高连接效

率。这种优化方式是把分组操作提前执行。下移的含义，是在查询树上，让分

组操作尽量靠近叶子结点，使得分组操作的结点低于一些选择操作。

2 分组操作上移：如果连接操作能够过滤掉大部分元组，则先进行连接后进行

GROUPBY操作，可能提高分组操作的效率。这种优化方式是把分组操作置后执

行。上移的含义，和下移正好相反。

对于带有GROUPBY等操作的非SPJ格式的SQL语句，在本节之前提及的技术都

适用，只是结合了GROUPBY操作的语义进行分组操作。因为GROUPBY操作下移

或上移均不能保证重写后的查询效率一定更好，所以，要在查询优化器中采用

基于代价的方式来估算某几种路径的优劣。

2. Order by优化：

常见ORDER BY 优化技术:

1 排序消除（Order By Elimination ，OBYE）。

优化器在生成执行计划前，将语句中没有必要的排序操作消除（如利用索

引），避免在执行计划中出现排序操作或由排序导致的操作（如在索引列上排

序，可以利用索引消除排序操作）。

2 排序下推（Sort push down ）。

把排序操作尽量下推到基表中，有序的基表进行连接后的结果符合排序的语

义，这样能避免在最终的大的连接结果集上执行排序操作。

3. Distinct优化：

常见DISTINCT 优化技术:

1 DISTINCT 消除（Distinct Elimination ）。

如果表中存在主键、唯一约束、索引等，则可以消除查询语句中的DISTIN

CT（这种优化方式，在语义优化中也涉及，本质上是语义优化研究的范畴）。

2 DISTINCT 推入（Distinct Push Down ）。

生成含DISTINCT的反半连接查询执行计划时，先进行反半连接再进行DIS

TICT操作；也许先执行DISTICT操作然后再执行反半连接，可能更优；这是利

用连接语义上确保唯一功能特性进行DISTINCT的优化。

3 DISTINCT 迁移（Distinct Placement ）：

对连接操作的结果执行DISTINCT，可能把DISTINCT移到一个子查询中优

先进行（有的书籍把这项技术称为“DISTINCT配置”）。

4. MySQL的非SPJ优化：

MySQL 的GROUP BY 优化技术:

MySQL对于GROUPBY的处理，通常采用的方式是扫描整个表、创建一个临时表

用以执行分组操作。查询执行计划中出现“Using temporary”字样表示MySQL采用了常规的处理方式。MySQL 不支持分组转换技术。

对于GROUPBY的优化，则尽量利用索引

MySQL 的GROUP BY 优化技术:

利用索引的条件是：分组子句中的列对象源自同一个btree索引（不支持利用

Hash索引进行优化）的全部或前缀部分的部分有序的键（分组使用的索引列与

索引建立的顺序不匹配则不能使用索引）。

主要的方式有:

1 Loose Index Scan：

直接用索引完成分组操作中对分组列的检索，不必考虑索引的全部键满足WHER

E子句，只要有部分匹配WHERE中的列对象即可（loose，利用索引中部分列

为“松散”）

2 Tight Index Scan：

索引中的全部键与WHERE子句中的列对象匹配（tight，利用索引中的全部列

为“严密”）

MySQL 的ORDER BY 优化技术:

在索引列上进行排序操作， MySQL支持利用索引进行排序优化。

排序下推，MySQL不支持。在非索引列上执行连接，然后排序

MySQL支持对于DISTINCT消除的优化技术。比如在有主键的e1列上执行DISTINCT

MySQL 的LIMIT优化技术:

1. LIMIT对单表扫描的影响：如果索引扫描可用且花费低于全表扫描，则用索引扫描实现LIMIT（LIMIT取很少量的行，否则优化器更倾向于使用全表扫描）。

2. LIMIT对排序的影响：如果LIMIT和ORDERBY子句协同使用，当取到LIMIT

设定个数的有序元组数后，后续的排序操作将不再进行。

3. LIMIT对去重的影响：如果LIMIT和DISTINCT子句协同使用，当取到LIMIT

设定个数的唯一的元组数后，后续的去重操作将不再进行。

4. LIMIT受分组的影响：如果LIMIT和GROUPBY子句协同使用，GROUPBY按索引有序计算每个组的总数的过程中，LIMIT操作不必计数直到下一个分组开始

计算。

GROUP LIMIT

1—- +1

1

1

2—- +1

2

3—- +1

… …

5. LIMIT 0：直接返回空结果集。

6. MySQL支持对不带HAVING子句的LIMIT进行优化。

10. 一些常见的启发式规则：

1. 嵌套连接消除:如果都是内连接,则可以把表示嵌套关系的括号去掉

A join (B join C) == A join B join C

2. 选择操作下推

3. 投影操作下推

11. 常见的一些经验规则：

1. 在索引键上执行排序操作,通常利用索引的有序性按序读取数据而不进行排序

2. 选择率低于10%时,利用索引的效果通常比读表数据的效果好

3. 当表的数据量较少时,全表扫描可能优于其它方式(如利用索引的方式)

物理优化：

逻辑查询优化，主要解决什么问题 ?

如何找出SQL语句等价的变换形式，使得SQL执行更高效

物理查询优化，主要解决什么问题 ?

1）从可选的单表扫描方式中，挑选什么样的单表扫描方式是最优的？

2）两个表做连接时，如何连接是最优的？

3）多个表连接，连接顺序有多种组合，哪种连接顺序是最优的？

4）多个表连接，连接顺序有多种组合，是否要对每种组合都探索？如果不全部探索，怎么找到最优的一种组合？

物理查询优化把逻辑查询执行计划变为物理操作符，供执行器执行。MySQL的逻辑查询执行计划阶段，会对一些子句和表达式进行计算，执行阶段不用再次计算。

1. 查询代价估算：

代价估算模型

总代价 = I/O 代价 + CPU 代价

COST = Pages * a_page_cpu_time + W * T

说明：

1 Pages为计划运行时访问的页面数，a_page_cpu_time是每个页面读取的时间

花费，其乘积反映了I/O花费。

2 T为访问的元组数，反映了CPU花费（存储层是以页面为单位，数据以页面的

形式被读入内存，每个页面上可能有多条元组，访问元组需要解析元组结构，

才能把元组上的字段读出，这消耗的是CPU）。如果是索引扫描，则会还会包

括索引读取的花费。

3 W为权重因子，表明I/O到CPU的相关性，又称选择率（selectivity）。选

择率用于表示在关系R中,满足条件“A <op> a”的元组数与R的所有元组数

N的比值。

选择率计算的常用方法

2. 单表扫描算法：

单表扫描：获取表的数据

单表扫描是完成表连接的基础。

1 全表扫描：

为获取表的全部元组，读取表对应的全部数据页。

2 局部扫描/ 范围扫描：

为获取表的部分元组，读取指定位置对应的数据页

常用的单表扫描算法

1 顺序扫描（ SeqScan ）：

从物理存储上按照存储顺序直接读取表的数据；当无索引可用，或访问表中的大部分数据，或表的数据量很小，使用顺序扫描效果较好。

2 索引扫描（ IndexScan ）。

根据索引键读索引，找出物理元组的位置；根据从索引中找到的位置，从存储读取数据页面；索引扫描可以将元组按排序的顺序返回；索引扫描有选择率存在，读数据花费的IO会显著减少；如果选择率很高的话，不适宜使用索引扫描。

3 只读索引扫描（ IndexOnlyScan ）。

根据索引键读索引，索引中的数据能够满足条件判断，不需要读取数据页面；比索引扫描少了读取数据的I/O花费。

4 行扫描（RowIdScan ）。

用于直接定位表中的某一行。对于元组，通常为元组增加特殊的列，可以通过特殊的列计算出元组的物理位置，然后直接读取元组对应的页面，获取元组；如 PostgreSQL中称行扫描为Tid扫描，此种方式是在元组头上增加名为“CTID”的列，用这列的值可以直接计算本条元组的物理存储位置。

5 并行表扫描（ ParallelTableScan ）。

对同一个表，并行地、通过顺序的方式获取表的数据，结果是得到一个完整的表数据。

6 并行索引扫描（ ParallelIndexScan ）。

对同一个表，并行地、通过索引的方式获取表的数据，结果合并在一起.

7 组合多个索引扫描。

有的系统称为MultipleIndexScan 。对同一个元组的组合条件（AND或者OR谓词组合的多个索引列上的多条件查询）进行多次索引扫描，然后在内存里组织一个位图，用位图描述索引扫描结果中 符合索引条件的元组位置。用组合多个索引（包括同一索引的多次使用）来处理单个索引扫描不能实现的情况。本质上不是单表的扫描方式，是构建在单表的多个索 引扫描基础上的。

单表扫描代价计算

单表扫描算法：

不同的单表扫描方式，有着不同的代价。

实践应用原则：

尽量少获取元组。

4. 两表连接算法

基本的两表连接算法

1 嵌套循环连接算法

常见的两表连接算法：嵌套循环连接算法 pk 块嵌套循环连接算法BNL(Block Nested Loop)

FOR EACH ROW r1 IN t1 {

FOR EACH ROW r2 IN t2 {

IF r1,r2 SATISFIES JOIN CONDITIONS

JOIN r1,r2

}

}

FOR EACH CHUNK c1 OF t1 {

IF c1 NOT IN MEMORY //系统一次读入多个页面，所以不需要每次都从存储系统读入，花费I/O

READ CHUNK c1 INTO MEMORY

FOR EACH ROW r1 IN CHUNK c1 {//从页面中分析出元组，花费CPU

FOR EACH CHUNK c2 OF t2 {

IF c2 NOT IN MEMORY

READ CHUNK c2 INTO MEMORY

FOR EACH ROW r2 IN c2 {//从页面中分析出元组，花费CPU

IF r1,r2 SATISFIES JOIN CONDITIONS

JOIN r1,r2

}

}

}

}

內连接、左外连接、半连接、反半连接等语义的处理

2 归并连接算法

归并排序连接算法，简称归并连接算法。

算法的步骤：

为两个表创建可用内存缓冲区数为M 的M个子表，每个子表排好序；然后读入每个子表的第一块到M个块中，找出其中最小的先进行两个表的元组的匹配，找出次小的匹配，…；依此类推，完成其他子表的两表连接。

1 归并连接算法要求内外表都是有序的，所以对于内外表都要排序。如

果连接列是索引列，可以利用索引进行排序。

2 归并连接算法适用于内连接、左外连接、右外连接、全外连接、半连

接、反半连接等语义的处理。

3 Hash连接算法

用连接列作为Hash的关键字，对内表进行Hash运算建立Hash表，然后对外表的每个元组的连接列用Hash函数求值，值映射到内表建立好的 Hash表就可以连接了；否则，探索外表的下一个元组。这样的Hash连接算法，称为简单Hash连接算法（Simple Hash Join，SHJ）。Hash连接算法适用于内连接、左外连接、右外连接、全外连接、半连接、反半连接等语义的处理。

进一步认识两表连接算法

连接操作代价计算

5. 多表连接算法

多表连接要解决的问题：

1 多表连接的顺序：表的不同的连接顺序，会产生许多不同的连接路径；不同的连接路径有着不同的效率。

2 多表连接的搜索空间：因为多表连接的顺序不同，产生的连接组合会有多种，如果这个组合的数目巨大，连接次数会达到一个很高的数量级，最大可能的连接次数是N！（N的阶乘）。

当N=5 ，这个数是120；

当N=10, 这个数是3628800；

当N=20, 这个数是2432902008176640000 。

所有的连接可能构成一个巨大的“搜索空间”。如何减小搜索空间，在一个可接受的时间范围内，高效地生成查询执行计划将成为一个难点。

多表连接顺序

多表间的连接顺序：表示了查询计划树的基本形态一棵树，就是一种查询路径。SQL查询语句的语义，可以由多棵这样的树表达，从中选择花费最少的树，就是最优查询计划形成的过程。

1. 左深连接树； 2. 右深连接树； 3. 紧密树

常用的多表连接算法

1 启发式

2 分枝界定计划枚举

3 爬山法

4 动态规划

5 System R 优化方法

多表连接算法的比较

MySQL的代价估算模型：

总代价 = I/O 代价 + CPU 代价 + Memory 代价 + Remote 代价

MySQL的单表扫描：

1.随机扫描（全表扫描/局部扫描）： 关键字 type = ALL

2.索引扫描（范围扫描）： 关键字 type = range, index

3.只读索引扫描（范围扫描）： 关键字 type= const

MySQL的两表连接算法：

1. BNL，块嵌套循环连接算法(Block Nested Loop)

2. BKA算法(Batched Key Access)

SET optimizer_switch=’mrr=on,mrr_cost_based=off,batched_key_access=on';

3. 贪婪-穷举算法：

贪婪算法，体现在基于有序的表进行连接时，每一次依照次序进行连接的表，贪婪算法都认为这种连接方式是最优的。所以在每一次连接的过程中，主要完成的任务，就是求解本次要连接的表对象的最佳访问方式。

可调优的参数：

1 optimizer_prune_level=1 ，剪枝参数

The optimizer_prune_level variable tells the optimizer to skip

certain plans based on estimates of the number of rows accessed for

each table.

2 optimizer_search_depth ，控制搜索空间的深度

The optimizer_search_depth variable tells how far into the

“future” of each incomplete plan the optimizer should look to

evaluate whether it should be expanded further.

4. 指定连接方式： STRAIGHT_JOIN

3. 索引

1. 索引的优点:

提高少量数据的获取/检索速度

2. 索引的缺点:

1) 占用存储空间

2) 多个索引耗费索引的挑选时间

3) 降低写操作的性能，需要实时维护索引

4) 并发情况下索引的维护高度复杂

3. 什么时候不使用索引:

1 数据的重复度高,即：选择率高

2 选择率高于10%,建议不考虑使用这个索引

3 表的数据量较少

4. 数据库选择索引的原则：

1. 代价估算模型计算代价，选择小代价的方式

2. 启发式规则排除或强制选择某类索引

5. MySQL支持的索引

B-tree : PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT

R-tree : 关键字可被识别，但内部被处理为BTree

Hash index: MEMORY tables

不同类型的索引可以支持的操作符：

For both BTREE and HASH indexes, comparison of a key

part with a constant value is a range condition when using

the =, <=>, IN(), IS NULL, or IS NOT NULL operators.

Additionally, for BTREE indexes, comparison of a key

part with a constant value is a range condition when using

the >, <, >=, <=, BETWEEN, !=, or <> operators, or LIKE

comparisons if the argument to LIKE is a constant string

that does not start with a wildcard character.

6. MySQL中利用索引做各种优化

1. 索引对WHERE子句的影响

2. 索引对读数据方式的影

3. 索引列用于表达式计算，索引不可用

4. 索引列与其它一些操作符配合使

5. 索引对内连接操作的影响(索引关键字作为连接条件，使用索引定位被连接表的元组)

6. 索引对外连接操作的影响

7. 索引对GROUP BY操作的影响(常规的分组操作是利用临时文件排序)

8. 索引对ORDER BY操作的影响(常规的排序操作是利用临时文件排序)

9. 索引对DISTINCT操作的影(常规的DISTINCT操作是全表扫描再去重)

10.索引对聚集函数操作的影响:–主键列求最值

11.索引对聚集函数操作的影响:–主键列带WHERE条件求最值

12.索引对聚集函数操作的影响:–COUNT函数

13.索引对聚集函数操作的影响:–SUM函数

14.索引对聚集函数操作的影响:–主键列带WHERE条件

15.索引对聚集函数操作的影响:—普通索引列求最值

16.索引对聚集函数操作的影响:—普通索引列带WHERE条件求最值

17.索引对聚集函数操作的影响:

—普通索引列,使用索引的前缀或中缀,带WHERE条件求最值

18.索引对非SPJ式的聚集函数操作的影响:–分组操作

19.索引对非SPJ式的聚集函数操作的影响:–排序操作

20.其它：

1 char(10) = varchar(10) != char(8) != varchar(6)

2 NULL <=> NULL

3 查看索引的使用情况： Show status like ‘handler_read%';

handler_read_key 的值高:索引查询多，查询效率越高；

handler_read_rnd_next 的值高:随机查询多，查询效率越低。

如何利用索引

索引列的位置对使用索引的影响

联合索引对索引使用的影响

多个索引对索引使用的影响

TPCH

TPC-H模型的评价指标

主要评价指标: 各个查询的响应时间

即从提交查询到结果返回所需时间

TPC-H 基准测试的度量单位: 每小时执行的查询数（QphH@size）

1. H表示每小时系统执行复杂查询的平均次数

2. size表示数据库规模的大小，它能够反映出系统在处理查询时的能力。

3 TPC-H模型查询语句特点

1. 具有高度复杂性

2. 使用各种各样的访问模式

3. 查询语句带有随机的特性

4. 检查可用数据的大多数数据

5. 22条SQL各不相同

6. 每次查询的参数可变

包括符合SQL92标准的

1. 多表连接/单表扫描

2. 各种子查询（包括IN、EXISTS类型的子查询）操作

3. HAVING 操作、GROUP BY

4. ORDER BY

5. UNION

6. 日期操作等

比较全面地检验了数据库应付各种语句的能力。

4 TPC-H模型的表结构

8张表（表上有些约束等需要满足，参见TPC-H规范），如下：

1. PART： 表示零件的信息

2. SUPPLIER ：表示供货商的信息

3. PARTSUPP ：表示供货商的零件的信息

4. CUSTOMER ：表示消费者的信息

5. ORDERS ： 表示订单的信息

6. LINEITEM ：表示在线商品的信息

7. NATION ： 表示国家的信息

8. REGION ： 表示地区的信息

关系代数对数据库查询优化的指导意义:

1 SQL输入：用户的查询语句

2 语法分析：词法分析+语法分析

3 语义检查：对象是否存在等（权限检查）

4 SQL优化：查询优化器

5 SQL执行：查询执行器

从运算符的角度考虑优化

1 选择运算符—基本选择性质

1)根据特点可得到的优化规则

对同一个表的同样选择条件，作一次即可

选择可有效减少在它的操作数中的元组数的运算（元组个数减少）

2)可优化的原因

幂等性：多次应用同一个选择有同样效果

2 选择运算符—分解有复杂条件的选择

1. 合取，合并多个选择为更少的需要求值的选择，多个等式则可以合并

2. 析取，分解它们使得其成员选择可以被移动或单独优化

2)可优化的原因

1. 合取的选择等价于针对这些单独条件的一序列选择[常量传递]

2. 析取的选择等价于选择的并集[OR转换并集规则]

3 选择运算符—选择和叉积

1)根据特点可得到的优化规则

选择尽可能先做

2)可优化的原因

关系有N和M行，先做积运算将包含N×M行。先做选择运算减少N

和M，则可避免不满足条件的元组作积的运算，节约时间并能减少结果

的大小

4 选择运算符—选择和集合运算

1)根据特点可得到的优化规则

选择下推到的集合运算之下

2)可优化的原因

选择在差集、交集和并集算子上满足分配律

5 选择运算符—选择和投影

1)根据特点可得到的优化规则

在投影之前进行选择

2)可优化的原因

如果选择条件中引用的字段是投影中的字段的子集，则选择与投影满足交换性

6 投影运算符—基本投影性质

1)根据特点可得到的优化规则

尽可能先做投影

2)可优化的原因

投影是幂等性的；投影可以减小元组大小

7 投影运算符—投影和集合运算

1)根据特点可得到的优化规则

投影下推到集合运算之下

2)可优化的原因

投影在差集、交集和并集算子上满足分配律

8 连接、笛卡儿积的交换律

做连接、做积运算，可以交换前后位置，其结果不变。如两表连接算法

中有嵌套连接算法，对外表和内表有要求，外表尽可能小则有利于

做“基于块的嵌套循环连接”，所以通过交换律可以把元组少的表作为

外表

9 连接、笛卡儿积的结合律

做连接、做积运算，如果新的结合有利于减少中间关系的大小，则可以优先处理

10 选择的串接定律

选择条件可以合并,使得可一次就检查全部条件，不必多次过滤元组，所以可以把同层的合取条件收集在一次，统一进行判断

11 选择与投影的交换

先投影后选择可以改为先选择后投影，这对于以行为存储格式的主流数据库而言，很有优化意义。行存总是在先获得元组后才能解析得到其中的列

先选择后投影可以改为带有选择条件中列的投影后再选择，最后再完成最外围的投影，这样，使得内圈的选择和投影可以同时进行

12 选择与笛卡儿积的分配律

条件下推到相关的关系上，先做选择后做积运算，这样可以减小中间结果的大小

13 选择与并的分配律

条件下推到相关的关系上，先做选择后做积运算，这样可以减小中间结果的大小

14 选择与差运算的分配律

条件下推到相关的关系上，先选择后做差运算，可以减小每个关系输出结果的大小

15 投影与笛卡儿积的分配律

先投影后做积，可减少做积前每个元组的长度，使得做积后得到的新元组的长度也变短

16 投影与并的分配律

先投影后做并，可减少做并前每个元组的长度

启发式规则对数据库查询优化的指导意义:

1 利用索引优化GROUPBY、ORDERBY、DISTINCT 操作

2 连接操作中多个子查询优化时需要排序决定优先级，MySQL让相关子查询优先于非相关子查询。

3 物化策略有2种，优先选择SJ_OPT_MATERIALIZE_SCAN 而不是SJ_OPT_MATERIALIZE_LOOKUP 。

4 两个关系连接的两种顺序中必然存在一个代价较小的：MySQL在构造内连接的时候，两个关系在连接前先排序，元组少的在前，MySQL认为这样的连接花费最小。

5 笛卡尔积操作具有较高代价，尽量不做笛卡儿积操作。

6 多表连接算法中的剪枝优化（optimizer_prune_level）

MYSQL 优化技术总结：

1 硬件层的优化

1 CPU:

个数/核数/频率/线程数/一级cache/二级cache

2 内存:

容量与64-bits/带宽

3 I/O:

seek(>100次/秒)/read/write(>10–20MB/s)

4网络:

带宽/传输协议

2 存储引擎优化

1 InnoDB： （事务多余查询）OLTP

1) innodb_buffer_pool_size ：caching data and indexes in memory，可配置为可用内存的（非物理内存）的50%–60%

2) innodb_buffer_pool_instances ：配合innodb_buffer_pool_size使用，把buf分区，增加并发度

2 MyISAM： （多为查询）OLAP

1) key_buffer_size：Index blocks for MyISAM tables are buffered and are shared

by all threads，可配置为可用内存的10-%-20%

2) table_open_cache：The number of open tables for all threads. 使用“SHOW

GLOBAL STATUS LIKE ‘Opened_tables';” 命令检查“Opened_tables”的值，太小则改大

秘诀：

磁盘类数据库，瓶颈在IO；

必须优先调整和IO有关的参数。

解决IO瓶颈的方法，就是缓存；

必须优先调整和缓存相关的参数。

3 表设计优化

1 表的存储引擎选择：事务型选InnoDB，非事务型选MyISAM等

2 表的压缩选择：压缩的表IO少，CPU空闲IO瓶颈大课采取压缩

3 表结构符合第三范式：更新类应用可让表的个数多些单表的列少一些；分析类的应用可让表个数少些单表的列多些

4 表数据物理分布策略：尽量让表的数据分散在不同的物理存储设备上。利用表空间技术把数据分散

5 表数据逻辑分布策略：利用分区技术把数据从逻辑上即分开

6 表的数据类型选择：数字类型优于字符类型；长度小的优于长度大的。变长的VARCHAR优于定长的CHAR。BLOB类型用BINARY VARCHAR替代，替代不了则用单独的表存放。如果比较BLOB类，则新建字段其值等于用MD5() 处理后的结果。BOLB类型甚至不存放到数据库内部，数据库只存储BLOB的路径。

7 启用完整性约束：使用NOT NULL标识字段; 设置default value。

8 其他：列名不超过18个字符。使用sample character set（如用latin1，尽量少用utf-8等，因为utf-8等占用的空间是latin1的3倍）

4 InnoDB优化

1 单表容量优化：OPTIMIZE TABLE statement to reorganize the table and compact any wasted space。

2 单表统计数据优化： ANALYZE TABLE tpch.customer;

3 启用压缩：测试各级压缩哪种有效

4 应用尽量使用短事务减少使用长事务：应用程序控制

5 事务管理：

5.1 写操作多：SET AUTOCOMMIT=0 or a START TRANSACTION statement,followed by a COMMIT statement after making all the changes.

5.2 读操作多：SET AUTOCOMMIT=1

6 加大日志文件和日志缓存：innodb_log_buffer_size + innodb_log_file_size

7 主键建立：使用最重要且是最常用的列作主键，务必建立主键而不是使用InnoDB默认方式

8 主键列的数据类型不要太长：浪费存储空间和内存，浪费其他索引的空间和内存

9 有效建立索引：除主键外，尽量建立联合索引而不是多个单列上分别建立secondary index

10 删除数据释放空间：Use TRUNCATE TABLE to empty a table, not DELETE FROM tbl_name.

11 数据刷出的方式：In some versions of GNU/Linux and Unix, flushing files to

disk with the Unix fsync() call (which InnoDB uses by default) and similar methods

is surprisingly slow. If database write performance is an issue, conduct

benchmarks with the innodb_flush_method parameter set to O_DSYNC.

5 库级优化

1 同一个库中表不要太多：设置table_open_cache 和 max_connections来调整。

2 启用查询缓存： 适用于特定场景.

3 使用长连接： 避免频繁使用短连接.推荐启用连接池.

4 主从架构： 复制技术，master完成写操作，slave完成读操作.

1) 优化读写操作

2) 提高备份速度，减少对master的影响

6 数据获取方式的优化

1 一次获取的数据尽量少：

查询获取数据,尽量带WHERE条件精确指定获取范围,且一次获取的数据量要少（应用层开发阶段必须注意）数据获取，遵循的基本原则：

—少：不要全表扫描。要用什么取什么

—准：带where条件获取，用谁取谁

—快：加索引到where条件涉及的列上，用谁则快速取到谁

—减少关联：没有直接联系，不要硬拉郎配。减少耦合，减少关联

2 不用select *： 臭名昭著，远远避之。

获取什么列就明确指定列名,查询语句中出现的列名，最好是索引的一部分。

3 LIMIT x：在满足应用需求的情况下可限制元组个数

7 利用索引优化

1 正确使用索引：每条查询，使用EXPLAIN验证

2 索引列做条件不参与运算：index_col <op> expression

如: col IN (value1, value2,…), col是索引列，才可以利用索引加快数据

获取速度. 操作符一侧的对象必须是不参与运算的索引列.

3 精确化查找条件：单表不做全部数据读取，多表连接不做全表扫描，务必带有WHERE子句限制数据，WHERE子句中列对象有合适的索引可用

4 等式/不等式的一些推理人工完成：

a>b AND b>3 ==> a>3 AND b>3 AND a>b

a列上有索引且选择率低

5 求最值建索引：单表求MIN/MAX/COUNT(*)的操作，最好在对应列上建立索引

6 GROUPBY、ORDERBY、DISTINCT作用在索引列上：

6.1 利用索引进行GROUPBY、ORDERBY、DISTINCT 操作（考虑在这些谓词

后面的对象上建立索引）

6.2 避免使用随机数等不确定的对象做排序分组操作，如不用：ORDER BY RAN

D()

7 建立主外键关系：尽管MySQL目前不支持带有主外键关系的表连接优化（

好处：符合第三范式，表明实体已经被拆分为小表，有利于减少读取的数据量）

8 引入新列：在表上增加新列，并在其上创建索引

SELECT * FROM tbl_name

WHERE hash_col=MD5(CONCAT(col1,col2))

AND col1=’constant’ AND col2=’constant';

BLOB的比较，也可以使用同样的方式。

9 存在范围查找，建立Btree索引：默认情况下是Btree。

10 索引类型：尽量使用主键索引/唯一索引。

11 创建索引: 索引的键值类型越短越好。在数值型列上创建索引最好。

12 少建索引：对InnoDB，主键不可用时，尽量用联合索引替换多个单列的second index。

13 删除索引：删除使用很少的索引。

14 只读索引：Covering Indexes，覆盖索引。 查询语句中出现的对象尽量限制在单个索引的全部列中。

15 前缀索引：尽量使用索引的前缀部分。

16 通配符：字符型索引列参与比较，另外各一个操作符前不用通配符。key LIKE ‘%abc’

17 强制索引：特定情况强制使用指定的索引

18 复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

8 暂存中间结果的三种优化方式

1 利用物化视图临时储存确定的查询结果/统计结果：

2 或者把结果永久储存到特定的表中

3 或者把结果暂时存在临时表中:

9 控制查询优化器

SET [GLOBAL|SESSION] optimizer_switch=’command[,command]…';

batched_key_access={on|off}

block_nested_loop={on|off}

engine_condition_pushdown={on|off}

firstmatch={on|off}

index_condition_pushdown={on|off}

index_merge={on|off}

index_merge_intersection={on|off}

index_merge_sort_union={on|off}

index_merge_union={on|off}

loosescan={on|off}

materialization={on|off}

mrr={on|off}

mrr_cost_based={on|off}

semijoin={on|off}

subquery_materialization_cost_based={on|off}

use_index_extensions={on|off}

10 重新组织数据

1 物理数据分布–重新组织表数据：

方法1：OPTIMIZE TABLE

方法2：特定表，常使用’expr1, expr2’排序，则‘ALTER TABLE … ORDER BY expr1, expr2’

2 查询优化逻辑使用的统计数据–分析表数据:

ANALYZE TABLE

3 查询优化逻辑使用的统计数据–调整系统参数：

11 连接技术

1 避免子查询：使用子查询上拉技术，人工优化子查询。WHERE子句中的IN/ALL/ANY/SOME子查询可以交给优化器优化

2 慎用不同的连接语义：慎用各种外连接、嵌套连接

3 明确连接条件:

ON子句指名连接列,尽量在主键和唯一键上做等值连接WHERE子句尽量利用索引中的主键索引和唯一索引

4 控制表的连接个数:

7表连接,可交给优化器处理;7表以上的连接,考虑连接的必要性和连接条件

12 其他

1 使用语义优化技术：熟悉表结构和应用需求，利用语义优化技术书写查询语句（设计阶段就需要开始考虑）

2 使用hint: 强迫优化器变更行为（优化器不是万能的，多数时候可信）

3 查询语句中慎用函数：特别检察查询语句中是否使用了函数，尤其是值不稳定的函数（对于每行元组，值总在变化），尽量不用

15 MySQL优化的问题分析、方法和思考

1 为数据库整体定个基调

1.1 掌握应用的特点—一定要熟悉自己面对的应用的特点

业务优先于技术

1.2 感性的认识

OLTP: INSERT/UPDATE/DELETE操作多于SELECT

OLAP: SELECT操作多于INSERT/UPDATE/DELETE

1.3 使用show status 命令查看参数值,了解各种SQL的执行频率

1、Com_select 执行select操作的计数，一次查询只累加1

2、Com_insert 执行insert操作的次数，对于批量插入的insert操作，只累加一次

3、Com_update 执行update操作的次数

4、Com_delete 执行delete操作的次数

其他类似的参数：Com_stmt_prepare Com_stmt_fetch…

1.4 使用SHOW PROCESSLIST 命令查看整体状态,先从整体入手了解数据库服务器的的状态

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST shows you which threads are running.

1.5 使用show status 命令查看参数值,了解事务被主动提交和回滚的情况

1、Com_commit 提交了的事务的计数，主动执行commit命令

2、Com_rollback 回滚了的事务的计数，主动执行rollback 命令

注意：自动提交/回滚的事务不计算在内

1.6 区分“索引读”还是“随机读”。这种情况，不从缓存区的角度出发，从单表扫描数据的方式的角度出发

show status like ‘handler_read%';

1.7 其他…

定期检查判断锁

SHOW STATUS LIKE ‘Table%';

显示慢查询的次数

show status like ‘Slow%';

服务器工作时间

show status like ‘Up%';

1.8 系统监控：如何启动

1 在文件my.cnf 中配置

[mysqld]

performance_schema=on

2 带参数启动mysqld

mysqld –performance-schema-instrument=’wait/synch/cond/%=counted’

1.9 系统监控：体系结构

1 setup_actors: How to initialize monitoring for new foreground threads 监控什么样的用户（主机/用户/角色）

2 setup_consumers: The destinations to which event information can be sent and stored 。监控什么事件（哪些事件可监控，有：事件阶段/事件语句/事件事务/事件等待/…）

3 setup_instruments: The classes of instrumented objects for which events can be collected 。监控什么仪表（哪类仪表可监控）

4 setup_objects: Which objects should be monitored 。监控数据库的对象（表/函数/存储过程/事件/触发器）

5 setup_timers: The current event timer 。监控哪类事件计时器

（空闲/等待/语句/事务/阶段）

1.10 系统监控：使用模式

1 全系统监控: 监控项全部开启；便于分析不确定的问题；影响系统性能

2 定制监控: 监控项部分开启；便于分析确定的问题；影响系统性能较少

1.11 其他监控：

1 Linux：

load、cpu、swap、disk、net

2 MySQL：

com、hit、innodb_rows 、innodb_pages 、innodb_data 、

innodb_log 、innodb_status

连接数…

2 分析，悄然从提问开始

提问题的步骤

Step0: 给出版本信息

SELECT version();

熟悉版本之间的差异

Step1: 给出表结构、索引等信息

show create table my-table-name;

show index from my-table-name;

Step2: 描述清楚查询语句的语义

查询语句的目的是…

查询语句的表对象的结构获取参见第一条

查询语句的表对象之间的关系是…

Step3: 给出查询执行计划/警告信息(文本形式给出查询执行计划)

EXPLAIN EXTENDED your-sql;

SHOW WARNINGS;

不建议使用’\G‘格式，简洁地获取主要信息：

EXPLAIN EXTENDED your-sql \G

Step4: 尽量给出更多的信息

1 自己疑虑之处

2 自己所做的测试和结果

3 自己搜索的类似问题链接，尤其是MySQL的bug的URL

3 查询语句的性能问题，怎么去定位？

索引6问：

1 检查索引是否被使用

2 检查索引是否被用对

3 检查索引列是否参与了计算

4 检查不同索引之间在WHERE/ON/HAVING条件下的选择率：选择率高于

10%，查找原因 或 禁用这个索引

5 检查索引被使用的方式和SQL语句中所有列对象，明确“只读索引”是否可行

6 检查索引与分组、排序、去重操作的关系

4 聘请一个分析问题的好帮手

析利器：—阅读更详细的查询执行计划

FORMAT=JSON技术

分析利器：—阅读更详细的查询执行过程

OPTIMIZER_TRACE 技术

set optimizer_trace_max_mem_size=300000;

set end_markers_in_json=true;

SET optimizer_trace=”enabled=on”;

SELECT …; // 用户的查询语句

SELECT trace FROM information_schema.optimizer_trace; //获取跟踪信息

详细的代价计算信息，尤其是表扫描和索引扫描的代价信息

1 EXPLAIN your-query ;

不用’\G’

简洁清晰的查询执行计划

2 EXPLAIN EXTENDED + SHOW WARNINGS

详细一点的查询执行计划

得出的警告信息，是查询优化结束后，根据综合的优化结果，显示信息。显示的是

最终的优化后的结果而不是中间过程

3 EXPLAIN JFORMAT=JSON + SHOW WARNINGS

更为详细的查询执行计划

4 OPTIMIZER_TRACE

边优化便输出当时的优化信息到缓冲中

忠实于过程，且有着更详细的过程数据，有助于分析优化的各个阶段

能显示各种操作的代价信息,尤其是各种索引扫描的代价，有助于分析索引问题

5 最慢的查询，怎么揪它出来？

检查慢查询功能是否开启：

show variables like ‘%slow%';

设置慢查询功能：

Global 参数，配置在my.cnf文件

log-slow-queries = /data/mysqldata/my-slow.log //日志目录

log-queries-not-using-indexes // 记录下没有使用索引的查询

Session 级参数：

long_query_time = 0.5 //记录下查询时间查过0.5秒

灵活改变，从大大小如先使用默认值2，消除掉2秒以上的查询后，改为1；类推

6 一条查询，究竟是慢在了哪里？

分析日志 – mysqldumpslow

1 -s：排序方式。c , t , l , r 表示记录次数、时间、查询时间的多少、返回的记录数排序

2 -t：返回前面多少条的数据

3 -g：包含什么，大小写不敏感

示例：

mysqldumpslow -s r -t 10 /slowquery.log #slow记录最多的10个语句

mysqldumpslow -s t -t 10 -g “left join” /slowquery.log #按照时间排

序前10中含有”left join”的

设置Profiler功能：

SET profiling = 1;

执行操作：

SELECT/EXPLAIN …

查询各个阶段性能：

SHOW PROFILES;

SHOW PROFILE ALL FOR QUERY 1;