目录
Mysql优化(出自官方文档) - 第一篇
1 WHERE Clause Optimization
1.1 从MySQL8.0开始,当where后面的数字超过类型范围的时候,将自动转换为where 1
, 如:
# CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);
SELECT * FROM t WHERE c ≪ 256;
-≫ SELECT * FROM t WHERE 1; /*自动转换为where 1*/
1.2 当COUNT(*)
的时候,对于MyISAM
或者其他内存数据库,结果将直接从表信息中获取,不用检索数据;
1.3 在一个多表查询中,所有的const table
都被优先读出来,const table
的定义如下:
一个空的表或者只有一行数据的表
在where中,限定
primary key
或者unique index
等于一个常量值,如下面所示:SELECT * FROM t WHERE primary_key=1; SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
1.4 在一个join
中,当order by
和group by
的目标列都出自同一张表,那么这张表在join中会被优先当做第一张表来处理。
1.5 如果说order by
和group by
的目标列不同,或者order by
和group by
的目标列来自于多张表,而不是join队列里面的第一张表时,那么一个临时表会被创建。
1.6 当使用SQL_SMALL_RESULT
(在Mysql中成为查询提示符)标识符的时候,那么Mysql会在内存中创建一个临时表,如:
select SQL_SMALL_RESULT a.id, count(1) from t1 a straight_join t2 b on a.id= b.id1 group by a.id
1.7 曾经的Mysql在决定使用index
还是table scan
时,采取的判断依据是最好的index
是否覆盖超过30%的表数据,但是现在不这样做了,现在的判断因素比较多,会根据表size,行数和I/O block size等因素来判断。
2 Range Optimization
对于range查找的定义
分为两种,分别为
HASH
和BTREE
索引:- 对于
HASH
索引,当条件里出现=, <=>, IN, IS NULL, IS NOT NULL
- 对于
BTREE
索引,当条件里出现>, <, >=, <=, BETREEN, !=, <>, LIKE
时,需要注意的是,对于LIKE
,不能以通配符开头。
- 对于
对于单列的range查找(Range Access Method for Single-Part Indexes)
Mysql首先会将
where
后面的条件尝试优化为单纯的range
查找,下面的操作将会被执行。- 不能构造range的条件将会被去掉
- 产生空集的条件被删掉。
如下面的例子:
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z')
首先,条件中的
nonkey = 4
和key1 LIKE '%b'
不属于范围查找的范畴,因此会被替换为TRUE
,替换结果为:(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR (key1 < 'bar' AND TRUE) OR (key1 < 'uux' AND key1 > 'z')
然后,继续对里面的条件进行压缩,可以看到下面的条件是可以直接得到结果的:
(key1 LIKE 'abcde%' OR TRUE)
该条件始终为TRUE
;(key1 < 'uux' AND key1 > 'z')
该条件始终为FALSE
替换后的结果为:
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
删除掉所有的
TRUE
和FALSE
(key1 < 'abc') OR (key1 < 'bar')
最后,对该条件进行进一步的压缩,得到的最终结果为:
(key1 < 'bar')
虽然这种做法可能会导致最终结果的范围被放大,但是MySQL在上面的range查询完成后,还会根据非range的条件在做最后的过滤,从而得到正确的结果。比如:
nonkey = 4
和LIKE '%b'
会在最终返回给用户前做过滤。对于多列的range查找(Range Access Method for Multiple-Part Indexes)
多列range查找在MySQL内部实际上是单列的一个扩展,对于多列的range查找,会被优化一个元组,然后在进行查找,如下面的例子:
key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
该range查找设计到了三个key,分别为
key_part1
,key_part2
和key_part3
,该条件会被优化为:('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
inf
表示无穷大的意思,虽然优化后的范围变大了,但是这样子更有利于Mysql进行范围查找。对于带有
or
的条件,会被优化为两个范围,如下面的例子:(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)
对于range查找数据量的评估(Equality Range Optimization of Many-Valued Comparisons)
Mysql评估range查找的时候,主要使用两种技术,
index dives
和index statistics
,这两种技术各有优缺点:index dives
:会对每一行进行评估,随着比较的表达式增加,优化器也需要花费更多的时间去生成对row进行评估的方法,结果准确,但是花费时间较多。index statistics
:准确度不如index dives
,但是速度快
在Mysql里面,可以使用
eq_range_index_dive_limit
系统变量来决定使用哪种策略的条件。Skip Scan Range Access Method
通常来讲,
range scan
的效率要远远高于full index scan
的效率,但是有时候,我们没办法使用range scan
的技术,比如下面这个例子:CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2)); INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (2,1), (2,2), (2,3), (2,4), (2,5); INSERT INTO t1 SELECT f1, f2 + 5 FROM t1; INSERT INTO t1 SELECT f1, f2 + 10 FROM t1; INSERT INTO t1 SELECT f1, f2 + 20 FROM t1; INSERT INTO t1 SELECT f1, f2 + 40 FROM t1; ANALYZE TABLE t1; EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
因为
select
查询的条件只作用于f2
,但是索引创建的是f1
和f2
,在这种场景下,Mysql会使用一种叫做Skip Scan
的技术,其类似于Loose Index Scan
,操作的步骤如下:- 首先在第一个index列中查找出不同的值,该例子中为
f1
- 然后在第二个index列中进行一个
range scan
操作,在本例子中就是分别在f1 = 1
和f1 = 2
的条件下,分别进行f2 > 40
的操作。
以上面的为例,操作步骤如下:
- 1 首先查找出
f1
中所有的不同的列,先限定f1 = 1
- 2 在
f1 = 1
和f2 > 40
的条件下,进行range scan
操作 - 3 限定
f1 = 2
- 4 在
f1 = 2
和f2 > 40
的条件下,进行range scan
操作
- 首先在第一个index列中查找出不同的值,该例子中为
3 Engine Condition Pushdown Optimization
引擎条件下推优化:目前仅支持NDB
存储引擎,可以避免节点之间发送没有匹配到的数据。
举例,假设有下面这张表:
CREATE TABLE t1 (
a INT,
b INT,
KEY(a)
) ENGINE=NDB;
下面这条语句会使用条件下推的方式进行优化:
SELECT a, b FROM t1 WHERE b = 10;
使用EXPLAIN
看到的结果如下:
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;
原因如下:
- 第一条语句作用的范围在索引上,使用索引查询的效率要比条件下推的效率高很多
- 第二条语句虽然作用在索引上,但是并不是直接的使用索引,如果改成
b = 9
,那么,就会采用引擎下推的优化技术。
符合下面的比较条件的,均可以使用条件下推优化:
- column [NOT] LIKE pattern
- column IS [NOT] NULL
- column IN (value_list)
- column BETWEEN constant1 AND constant2
其中的pattern
,value_list
均必须是常量或者字面量
4 Index Condition Pushdown Optimization(ICP)
类似于上一节的优化,索引条件下推优化可以让Mysql将这些条件下推到存储引擎中,从而可以保证存储引擎只返回匹配的数据,避免了存储引擎遍历索引带来的过多IO问题,同样的,根据其名字可以看出来,该种优化技术只适用于索引上,其他条件不适用。
首先讲解下使用该种技术和不适用的区别,当不适用该种优化技术时,Mysql的处理流程如下:
- 首先获取到下一行的索引,然后利用索引读取到整行出来(该过程需要存储引擎来做)
- 对该行进行
where
过滤,然后继续处理下一行
当使用该种技术时,处理流程如下:
- 获取到下一行的索引,但是并不先读取整行
- 对索引列进行
where
过滤(该过程由存储引擎完成) - 如果满足条件,在利用索引列读取到整行出来(该过程由存储引擎完成)
- 继续对剩余的列进行
where
过滤
这种优化技术适用于以下条件:
当需要访问表的整行时,
ICP
适用于range, ref, eq_ref, ref_or_null
(注:EXPLAIN
返回的结果,详情可见这里。)- 适用于
InnoDB
和MyISAM
- 对于
InnoDB
来讲,只适用于二级索引,因为InnoDB
的聚集索引,完整的记录已经被读上来了,所以不能起到减少IO的作用 - 对于
InnoDB
来讲,该优化适用于创建在virtual generated columns.
上面的二级索引。 - 子查询中的条件无法被下推
stored functions
无法被下推,存储引擎无法调用stored functions
触发器无法被下推
举个例子,假设有一张表叫做people
,其索引列为:(zipcode, lastname, firstname)
,有下面的SQL查询语句:
SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
如果没有索引条件下推优化,那么存储引擎就需要读取出所有zipcode='95054'
的行,然后在进行where
条件过滤;
如果使用了索引条件下推优化,存储引擎还可以对lastname
进行匹配过滤,如果匹配的话,在进行表整行的读取。