MySQL 查询性能的优化涉及多个方面,其中包括库表结构、建立合理的索引、设计合理的查询。库表结构包括如何设计表之间的关联、表字段的数据类型等。这需要依据具体的场景进行设计。如下我们从数据库的索引和查询语句的设计两个角度介绍如何提高 MySQL 查询性能。
一、为什么查询速度会慢?
查询的生命周期
- 从客户端,到服务器
- 生成执行计划
- 执行
- 数据处理
- 包括排序
- 分组
- 返回结果给客户端
查询需要在不同的地方花费时间
- 网络
- CPU计算
- 生成统计信息和执行计划
- 锁等待（互斥等待）
- 向底层存储引擎检索数据的调用操作
- 根据存储引擎不同,可能还会产生大量的上下文切换以及系统调用
二、慢查询基础：优化数据访问
分析步骤
- 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时候也可能是访问了太多的列
- 确认 MySQL 服务器层是否在分析大量超过需要的数据行
- 是否向数据库请求了不需要的数据
几种示例:
- 查询不需要的记录
- 多表关联时返回全部列
- 总是取出全部列
- 重复查询相同的数据
适用范围:人们会告诉我们说这种有点浪费数据库资源的方式可以简化开发,因为能提高相同代码片段的复用性,如果清楚这样做的性能影响,那么这种做法也是值得考虑的。
- MySQL 是否在扫描额外的记录
最简单的衡量查询开销的三个指标如下
响应时间
实际上可以使用
快速上限估计法来估算查询的响应时间服务时间
数据库处理这个查询真正花了多长时间
排队时间
服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成,也可能是等待行锁,等等。
扫描的行数
返回的行数
一般 MySQL 能够使用如下三种方式应用WHERE条件,从好到坏依次为
- 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
- 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在 MySQL 服务器层完成的,但无须再回表查询记录。
- 从数据表中返回数据,然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在 MySQL 服务器层完成, MySQL 需要先从数据表读出记录然后过滤。
如果发现查询需要扫描大量的数据但只返回少数的行
- 重写这条 sql
- 使用索引覆盖扫描,把所有需要用的列都放到索引中,这样存储引擎无须回表获取对应行就可以返回结果了（在前面的章节中我们已经讨论过了）。
- 改变库表结构。例如使用单独的汇总表（这是我们在第4章中讨论的办法）。
三、重构查询的方式
一个复杂查询还是多个简单查询
将一个大查询分解为多个小查询是很有必要的,不过,在应用设计的时候,如果一个查询能够胜任时还写成多个独立查询是不明智的。
切分查询
原因
如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询
好处
这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中,就可以大大降低对服务器的影响,还可以大大减少删除时锁的持有时间
分解关联查询
好处
让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。如果关联中的某个表发生了变化,那么就无法使用查询缓存了,而拆分后,如果某个表很少改变,那么基于该表的查询就可以重复利用查询缓存结果了
例如,上面查询中的tag已经被缓存了,那么应用就可以跳过第一个查询。再例如,应用中已经缓存了ID为123、567、9098的内容,那么第三个查询的IN()中就可以少几个ID。另外,对 MySQL 的查询缓存来说(6)。
将查询分解后,执行单个查询可以减少锁的竞争。
在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
查询本身效率也可能会有所提升。这个例子中,使用IN()代替关联查询,可以让 MySQL 按照ID顺序进行查询,这可能比随机的关联要更高效。我们后续将详细介绍这点。
可以减少冗余记录的查询。在应用层做关联查询,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看,这样的重构还可能会减少网络和内存的消耗。
更进一步,这样做相当于在应用中实现了哈希关联,而不是使用 MySQL 的嵌套循环关联。某些场景哈希关联的效率要高很多（本章后续我们将讨论这点）。
四、查询执行的基础
MySQL 到底做了些什么
- 客户端发送一条查询给服务器。
- 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。
- 服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划。
- MySQL 根据优化器生成的执行计划,调用存储引擎的API来执行查询。
- 将结果返回给客户端。
MySQL 客户端/服务器通信协议
MySQL 客户端和服务器之间的通信协议是半双工
这意味着,在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器发送数据,这两个动作不能同时发生。所以,我们无法也无须将一个消息切成小块独立来发送。
参数
max_allowed_packet控制包长度
查询状态
Sleep
线程正在等待客户端发送新的请求。
Query
线程正在执行查询或者正在将结果发送给客户端。
Locked
在 MySQL 服务器层,该线程正在等待表锁。在存储引擎级别实现的锁,例如 InnoDB 的行锁,并不会体现在线程状态中。对于 MyISAM 来说这是一个比较典型的状态,但在其他没有行锁的引擎中也经常会出现。
Analyzing and statistics
线程正在收集存储引擎的统计信息,并生成查询的执行计划。
Copying to tmp table [on disk]
线程正在执行查询,并且将其结果集都复制到一个临时表中,这种状态一般要么是在做group by操作,要么是文件排序操作,或者是UNION操作。如果这个状态后面还有on disk标记,那表示 MySQL 正在将一个内存临时表放到磁盘上。
Sorting result
线程正在对结果集进行排序。
Sending data
这表示多种情况：线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。
查询缓存
实现原理
这个检查是通过一个对大小写敏感的哈希查找实现的
执行步骤
- 如果当前的查询恰好命中了查询缓存
- 检查一次用户权限(无须解析查询SQL语句的)
- 如果权限没有问题, MySQL 会跳过所有其他阶段,直接从缓存中拿到结果并返回给客户端
查询优化处理
MySQL 依照这个执行计划和存储引擎进行交互的过程
- 解析SQL
- 预处理
- 优化SQL执行计划
语法解析器和预处理
- MySQL 通过关键字将SQL语句进行解析,并生成一棵对应的
解析树。 - MySQL 解析器将使用 MySQL 语法规则验证和解析查询。
- 预处理器则根据一些 MySQL 规则进一步检查解析树是否合法
- 下一步预处理器会验证权限。这通常很快,除非服务器上有非常多的权限配置。
查询优化器
类型
MySQL 使用基于成本的优化器
实现原理
它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。
成本因素
每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存,它假设读取任何数据都需要一次磁盘I/O。
最初,成本的最小单位是随机读取一个4K数据页的成本,后来（成本计算公式）变得更加复杂,并且引入了一些因子来估算某些操作的代价,如当执行一次WHERE条件比较的成本。可以通过查询当前会话的Last_query_cost的值来得知 MySQL 计算的当前查询的成本。
SHOW STATUS LIKE 'last_query_cost';
会导致 MySQL 优化器选择错误的执行计划的原因
- 统计信息不准确。 MySQL 依赖存储引擎提供的统计信息来评估成本,但是有的存储引擎提供的信息是准确的,有的偏差可能非常大。例如, InnoDB 因为其MVCC的架构,并不能维护一个数据表的行数的精确统计信息。
- 执行计划中的成本估算不等同于实际执行的成本。所以即使统计信息精准,优化器给出的执行计划也可能不是最优的。例如有时候某个执行计划虽然需要读取更多的页面,但是它的成本却更小。因为如果这些页面都是顺序读或者这些页面都已经在内存中的话,那么它的访问成本将很小。 MySQL 层面并不知道哪些页面在内存中、哪些在磁盘上,所以查询实际执行过程中到底需要多少次物理I/O是无法得知的。
- MySQL 的最优可能和你想的最优不一样。你可能希望执行时间尽可能的短,但是 MySQL 只是基于其成本模型选择最优的执行计划,而有些时候这并不是最快的执行方式。所以,这里我们看到根据执行成本来选择执行计划并不是完美的模型。
- MySQL 从不考虑其他并发执行的查询,这可能会影响到当前查询的速度。
- MySQL 也并不是任何时候都是基于成本的优化。有时也会基于一些固定的规则,例如,如果存在全文搜索的
MATCH()子句,则在存在全文索引的时候就使用全文索引。即使有时候使用别的索引和WHERE条件可以远比这种方式要快, MySQL 也仍然会使用对应的全文索引。 - MySQL 不会考虑不受其控制的操作的成本,例如执行存储过程或者用户自定义函数的成本。
- 后面我们还会看到,优化器有时候无法去估算所有可能的执行计划,所以它可能错过实际上最优的执行计划。
优化策略可以简单地分为两种
静态优化
例如,优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值,如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效,即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种
编译时优化。可以直接对解析树进行分析,并完成优化。
动态优化
动态优化则和查询的上下文有关,也可能和很多其他因素有关,例如WHERE条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估,可以认为这是
运行时优化。
一些 MySQL 能够处理的优化类型
重新定义关联表的顺序
数据表的关联并不总是按照在查询中指定的顺序进行。决定关联的顺序是优化器很重要的一部分功能,
将外连接转化成内连接
并不是所有的
OUTER JOIN语句都必须以外连接的方式执行。诸多因素,例如WHERE条件、库表结构都可能会让外连接等价于一个内连接。 MySQL 能够识别这点并重写查询,让其可以调整关联顺序。使用等价变换规则
MySQL 可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断。例如,
（5=5 AND a>5）将被改写为a>5。类似的,如果有（a<b AND b=c） AND a=5则会改写为b>5 AND b=c AND a=5。这些规则对于我们编写条件语句很有用,我们将在本章后续继续讨论。优化
COUNT()、MIN()和MAX()例如,要找到某一列的最小值,只需要查询对应B-Tree索引最左端的记录, MySQL 可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点,在B-Tree索引中,优化器会将这个表达式作为一个常数对待。类似的,如果要查找一个最大值,也只需读取B-Tree索引的最后一条记录。如果 MySQL 使用了这种类型的优化,那么在 EXPLAIN 中就可以看到
Select tables optimized away。从字面意思可以看出,它表示优化器已经从执行计划中移除了该表,并以一个常数取而代之。类似的,没有任何WHERE条件的COUNT（*）查询通常也可以使用存储引擎提供的一些优化（例如, MyISAM 维护了一个变量来存放数据表的行数）。索引和列是否可为空通常可以帮助 MySQL 优化这类表达式。
预估并转化为常数表达式
例如,一个用户自定义变量在查询中没有发生变化时就可以转换为一个常数。数学表达式则是另一种典型的例子。这可以通过WHERE、USING或者ON语句来限制某列取值为常数。在上面的例子中,因为使用了USING子句,优化器知道这也限制了film_id在整个查询过程中都始终是一个常量。
当 MySQL 检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行优化处理。
覆盖索引扫描
当索引中的列包含所有查询中需要使用的列的时候, MySQL 就可以使用索引返回需要的数据,而无须查询对应的数据行,在前面的章节中我们已经讨论过这点了。
子查询优化
MySQL 在某些情况下可以将子查询转换一种效率更高的形式,从而减少多个查询多次对数据进行访问。
提前终止查询
一个典型的例子就是当使用了
LIMIT子句的时候。除此之外, MySQL 还有几类情况也会提前终止查询,例如发现了一个不成立的条件,这时 MySQL 可以立刻返回一个空结果。在发现已经满足查询需求的时候, MySQL 总是能够立刻终止查询。
等值传播
如果两个列的值通过等式关联,那么 MySQL 能够把其中一个列的WHERE条件传递到另一列上。
1
2
3
4SELECT film.film_id
FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id)
WHERE film.film_id > 500因为这里使用了film_id字段进行等值关联, MySQL 知道这里的WHERE子句不仅适用于flm表,而且对于flm_actor表同样适用。如果使用的是其他的数据库管理系统,可能还需要手动通过一些条件来告知优化器这个WHERE条件适用于两个表,那么写法就会如下：
... WHERE film.film_id > 500 AND film_actor.film_id > 500在 MySQL 中这是不必要的,这样写反而会让查询更难维护。列表
IN()的比较在很多数据库系统中,
IN()完全等同于多个OR条件的子句,因为这两者是完全等价的。在 MySQL 中这点是不成立的, MySQL 将IN()列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件,这是一个O（log n）复杂度的操作,等价地转换成OR查询的复杂度为O（n）,对于IN()列表中有大量取值的时候, MySQL 的处理速度将会更快。数据和索引的统计信息
MySQL 查询优化器在生成查询的执行计划时,需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息,包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。
MySQL 如何执行关联查询
在 MySQL 的概念中,每个查询都是一次关联,所以读取结果临时表也是一次关联。
什么是关联
MySQL 中
关联(14)一词所包含的意义比一般意义上理解的要更广泛。总的来说, MySQL 认为任何一个查询都是一次关联——并不仅仅是一个查询需要到两个表匹配才叫关联,所以在 MySQL 中,每一个查询,每一个片段（包括子查询,甚至基于单表的SELECT）都可能是关联。MySQL 关联执行的策略
嵌套循环关联,即 MySQL 先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为止。然后根据各个表匹配的行,返回查询中需要的各个列。 MySQL 会尝试在最后一个关联表中找到所有匹配的行,如果最后一个联表无法找到更多的行以后, MySQL 返回到上一层次关联表,看是否能够找到更多的匹配记录,依此类推迭代执行。
执行计划
MySQL 的执行计划步骤
MySQL 生成查询的一棵指令树
通过存储引擎执行完成这棵指令树并返回结果
最终的执行计划包含了重构查询的全部信息
如果对某个查询执行 EXPLAIN EXTENDED后,再执行 SHOW WARNINGS,就可以看到重构出的查询
关联查询优化器
关联优化器的评判标准
关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序
一般是根据扫描的行数来判断,因为会进行嵌套,循环,回溯的过程,如果扫描的行数越少,能够提前剔除,可以减少后面的基数
做了什么
关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树
当搜索空间非常大的时候,优化器不可能逐一评估每一种关联顺序的成本。这时,优化器选择使用
贪婪搜索的方式查找最优的关联顺序。optimizer_search_depth参数可以根据需要指定大小
技巧
STRAIGHT_JOIN
功能同join类似,但能让左边的表来驱动右边的表,能改表优化器对于联表查询的执行顺序。
排序优化
文件排序
当不能使用索引生成排序结果的时候, MySQL 需要自己进行排序,如果数据量小则在内存中进行,如果数据量大则需要使用磁盘
如何进行文件排序
如果需要排序的数据量小于
排序缓冲区, MySQL 使用内存进行快速排序操作。如果内存不够排序,那么 MySQL 会先将数据分块,对每个独立的块使用快速排序进行排序,并将各个块的排序结果存放在磁盘上,然后将各个排好序的块进行合并（merge）,最后返回排序结果。MySQL 有如下两种排序算法
- 两次传输排序（旧版本使用）
读取行指针和需要排序的字段,对其进行排序,然后再根据排序结果读取所需要的数据行。这需要进行两次数据传输,即需要从数据表中读取两次数据,第二次读取数据的时候,因为是读取排序列进行排序后的所有记录,这会产生大量的随机I/O,所以两次数据传输的成本非常高。当使用的是 MyISAM 表的时候,成本可能会更高,因为 MyISAM 使用系统调用进行数据的读取（ MyISAM 非常依赖操作系统对数据的缓存）。不过这样做的优点是,在排序的时候存储尽可能少的数据,这就让
排序缓冲区(21)中可能容纳尽可能多的行数进行排序。- 单次传输排序（新版本使用）
先读取查询所需要的所有列,然后再根据给定列进行排序,最后直接返回排序结果。这个算法只在 MySQL 4.1和后续更新的版本才引入。因为不再需要从数据表中读取两次数据,对于I/O密集型的应用,这样做的效率高了很多。另外,相比两次传输排序,这个算法只需要一次顺序I/O读取所有的数据,而无须任何的随机I/O。缺点是,如果需要返回的列非常多、非常大,会额外占用大量的空间,而这些列对排序操作本身来说是没有任何作用的。因为单条排序记录很大,所以可能会有更多的排序块需要合并。
查询执行引擎
有什么作用
在解析和优化阶段, MySQL 将生成查询对应的执行计划, MySQL 的查询执行引擎则根据这个执行计划来完成整个查询。
执行引擎的步骤
MySQL 只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中,有大量的操作需要通过调用存储引擎实现的接口来完成,这些接口也就是我们称为handler API的接口。查询中的每一个表由一个handler的实例表示。前面我们有意忽略了这点,实际上, MySQL 在优化阶段就为每个表创建了一个handler实例,优化器根据这些实例的接口可以获取表的相关信息,包括表的所有列名、索引统计信息,等等。
并不是所有的操作都由handler完成。例如,当 MySQL 需要进行表锁的时候。handler可能会实现自己的级别的、更细粒度的锁,如 InnoDB 就实现了自己的行基本锁,但这并不能代替服务器层的表锁。正如我们第1章所介绍的,如果是所有存储引擎共有的特性则由服务器层实现,比如时间和日期函数、视图、触发器等。
返回结果给客户端
执行步骤
- 即使查询不需要返回结果集给客户端, MySQL 仍然会返回这个查询的一些信息,如该查询影响到的行数
- 如果查询可以被缓存,那么 MySQL 在这个阶段也会将结果存放到查询缓存中。
- MySQL 将结果集返回客户端是一个增量、逐步返回的过程。
好处
- 服务器端无须存储太多的结果,也就不会因为要返回太多结果而消耗太多内存
- 这样的处理也让 MySQL 客户端第一时间获得返回的结果
- 结果集中的每一行都会以一个满足 MySQL 客户端/服务器通信协议的封包发送,再通过TCP协议进行传输,在TCP传输的过程中,可能对 MySQL 的封包进行缓存然后批量传输
五、MySQL 查询优化器的局限性
MySQL 的万能嵌套循环并不是对每种查询都是最优的。不过还好, MySQL 查询优化器只对少部分查询不适用,而且我们往往可以通过改写查询让 MySQL 高效地完成工作。
关联子查询
为什么要使用关联自查询
MySQL 的子查询实现得非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。
例如,我们希望找到Sakila数据库中,演员Penelope Guiness（他的actor_id为1）参演过的所有影片信息。很自然的,我们会按照下面的方式用子查询实现：
1 | SELECT * FROM |
因为 MySQL 对IN()列表中的选项有专门的优化策略,一般会认为 MySQL 会先执行子查询返回所有包含actor_id为1的film_id。一般来说,IN()列表查询速度很快,所以我们会认为上面的查询会这样执行：
1 | SELECT * FROM sakila.film |
1 | SELECT GROUP_CONCAT(film_id) |
Result:1,23,25,106,140,166,277,361,438,
499,506,509,605,635,749,832,939,970,980
1 | SELECT * FROM |
很不幸, MySQL 不是这样做的。 MySQL 会将相关的外层表压到子查询中,它认为这样可以更高效率地查找到数据行。
改进的写法
1 | SELECT * FROM sakila.film |
1 | SELECT * FROM sakila.film |
1 | SELECT film.* |
如何用好关联子查询
例子1
原始sql
EXPLAIN SELECT film_id, language_id FROM sakila.film WHERE NOT EXISTS( WHERE * FROM sakila.film_actor WHERE film_actor.film_id = film.film_id )\G一般会建议使用左外连接（LEFT OUTER JOIN）重写该查询,以代替子查询。理论上,改写后 MySQL 的执行计划完全不会改变。
优化1
EXPLAIN SELECT film.film_id, film.language_id FROM sakila.film LEFT OUTER JOIN sakila.film_actor USING(film_id) WHERE film_actor.film_id IS NULL\G可以看到,这里的执行计划基本上一样,下面是一些微小的区别：
- 表flm_actor的访问类型一个是DEPENDENT SUBQUERY,而另一个是SIMPLE。这个不同是由于语句的写法不同导致的,一个是普通查询,一个是子查询。这对底层存储引擎接口来说,没有任何不同。
- 对film表,第二个查询的Extra中没有
Using where,但这不重要,第二个查询的USING子句和第一个查询的WHERE子句实际上是完全一样的。 - 在第二个表film_actor的执行计划的Extra列有
Not exists。这是我们前面章节中提到的提前终止算法（early-termination algorithm）, MySQL 通过使用Not exists优化来避免在表film_actor的索引中读取任何额外的行。这完全等效于直接编写NOT EXISTS子查询,这个执行计划中也是一样,一旦匹配到一行数据,就立刻停止扫描。
例子2
原始sql
SELECT DISTINCT film.film_id FROM sakila.film INNER JOIN sakila.film_actor USING(film_id);我们知道一旦使用了
DISTINCT和GROUP BY,那么在查询的执行过程中,通常需要产生临时中间表。
优化1
SELECT film_id FROM sakila.film WHERE EXISTS(SELECT * FROM sakila.film_actor WHERE film.film_id = film_actor.film_id);- 再一次,我们需要通过测试来对比这两种写法,哪个更快一些。测试结果参考表6-2。表6-2：EXISTS和关联性能对比查询每秒查询数结果（QPS）
- INNER JOIN 185 QPS
- EXISTS子查询 325 QPS
在这个案例中,我们看到子查询速度要比关联查询更快些。
UNION 的限制
MySQL 无法将限制条件从外层下推到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上
解决办法
从临时表中取出数据的顺序并不是一定的,所以如果想获得正确的顺序,还需要加上一个全局的ORDER BY和LIMIT操作
索引合并优化
我们的 where 中可能有多个条件(或者join)涉及到多个字段,它们之间进行AND或者OR,那么此时就有可能会使用到index merge技术。index merge技术如果简单的说,其实就是：对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)。
等值传递
某些时候,等值传递会带来一些意想不到的额外消耗。例如,有一个非常大的IN()列表,而 MySQL 优化器发现存在WHERE、ON或者USING的子句,将这个列表的值和另一个表的某个列相关联。那么优化器会将IN()列表都复制应用到关联的各个表中。通常,因为各个表新增了过滤条件,优化器可以更高效地从存储引擎过滤记录。但是如果这个列表非常大,则会导致优化和执行都会变慢。在本书写作的时候,除了修改 MySQL 源代码,目前还没有什么办法能够绕过该问题（不过这个问题很少会碰到）。
并行执行
MySQL 无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性,但是 MySQL 做不到。这里特别指出是想告诉读者不要花时间去尝试寻找并行执行查询的方法。
哈希关联
在本书写作的时候,MySQL 并不支持哈希关联—— MySQL 的所有关联都是嵌套循环关联。不过,可以通过建立一个哈希索引来曲线地实现哈希关联。如果使用的是 Memory 存储引擎,则索引都是哈希索引,所以关联的时候也类似于哈希关联。可以参考第5章的创建自定义哈希索引部分。另外, MariaDB 已经实现了真正的哈希关联。
松散索引扫描与紧凑索引扫描
松散索引扫描
松散索引扫描相当于 Oracle 中的跳跃索引扫描（skip index scan）,就是不需要连续的扫描索引中得每一个元组,扫描时仅考虑索引中得一部分。当查询中没有 where 条件的时候,松散索引扫描读取的索引元组的个数和 groups 的数量相同。如果 where 条件包含范围预测,松散索引扫描查找每个 group 中第一个满足范围条件,然后再读取最少可能数的keys。松散索引扫描只需要读取很少量的数据就可以完成group by操作,因而执行效率非常高
使用松散索引扫描需要满足以下条件
- 查询在单一表上。
group by指定的所有列是索引的一个最左前缀,并且没有其它的列。比如表t1（ c1,c2,c3,c4）上建立了索引（c1,c2,c3）。如果查询包含group by c1,c2,那么可以使用松散索引扫描。但是group by c2,c3(不是索引最左前缀)和group by c1,c2,c4(c4字段不在索引中)。- 如果在选择列表select list中存在聚集函数,只能使用 min()和max()两个聚集函数,并且指定的是同一列（如果min()和max()同时存在）。这一列必须在索引中,且紧跟着
group by指定的列。比如,select t1,t2,min(t3),max(t3) from t1 group by c1,c2。 - 如果查询中存在除了
group by指定的列之外的其他部分,那么必须以常量的形式出现（除了min()和max()两个聚集函数）。 - 索引中的列必须索引整个数据列的值(full column values must be indexed),而不是一个前缀索引。比如,c1 varchar(20), INDEX (c1(10)),这个索引没发用作松散索引扫描。
紧凑索引扫描
紧凑索引扫描可能是全索引扫描或者范围索引扫描,取决于查询条件。当松散索引扫描条件没有满足的时候,group by仍然有可能避免创建临时表。如果在 where 条件有范围扫描,那么紧凑索引扫描仅读取满足这些条件的 keys（索引元组）。否则执行索引扫描。因为这种方式读取所有 where 条件定义的范围内的 keys,或者扫描整个索引当没有 where 条件,因而称作紧凑索引扫描。对于紧凑索引扫描,只有在所有满足范围条件的 keys 被找到之后才会执行分组操作。
使用紧凑索引扫描需要满足以下条件
在查询中存在常量相等 where 条件字段（索引中的字段）,且该字段在group by指定的字段的前面或者中间。来自于相等条件的常量能够填充搜索keys中的gaps,因而可能构成一个索引的完整前缀。索引前缀能够用于索引查找。如果要求对group by的结果进行排序,并且查找字段有可能组成一个索引前缀, MySQL 同样可以避免额外的排序操作,因为对有序的索引进行的查找已经按照顺序提取所有的keys。
最大值和最小值优化
原始sql
1 | SELECT MIN(actor_id) |
问题
因为在 first_name 字段上并没有索引,因此 MySQL 将会进行一次全表扫描。
解决办法
一个曲线的优化办法是移除MIN(),然后使用LIMIT来将查询重写如下：
1 | SELECT actor_id |
在同一个表上查询和更新
MySQL 不允许对同一张表同时进行查询和更新。这其实并不是优化器的限制,如果清楚 MySQL 是如何执行查询的,就可以避免这种情况。
例子
1 | UPDATE tbl AS outer_tbl |
原因
实际上,这执行了两个查询：一个是子查询中的SELECT语句,另一个是多表关联UPDATE,只是关联的表是一个临时表。子查询会在UPDATE语句打开表之前就完成,
解决办法
可以通过使用生成表的形式来绕过上面的限制,因为 MySQL 只会把这个表当作一个临时表来处理。
1 | UPDATE tbl |
六、查询优化器的提示
HIGH_PRIORITY 和 LOW_PRIORITY
HIGH_PRIORITY
用于SELECT语句的时候, MySQL 会将此SELECT语句重新调度到所有正在等待表锁以便修改数据的语句之前。实际上 MySQL 是将其放在表的队列的最前面,而不是按照常规顺序等待。HIGH_PRIORITY还可以用于INSERT语句,其效果只是简单地抵消了全局LOW_PRIORITY设置对该语句的影响。
LOW_PRIORITY
它会让该语句一直处于等待状态,只要队列中还有需要访问同一个表的语句——即使是那些比该语句还晚提交到服务器的语句。这就像一个过于礼貌的人站在餐厅门口,只要还有其他顾客在等待就一直不进去,很明显这容易把自己给饿坏。LOW_PRIORITY提示在SELECT、INSERT、UPDATE和DELETE语句中都可以使用。
总结
HIGH_PRIORITY和LOW_PRIORITY经常让人感到困惑。这两个提示并不会获取更多资源让查询积极工作,也不会少获取资源让查询消极工作。它们只是简单地控制了 MySQL 访问某个数据表的队列顺序。
DELAYED
这个提示对INSERT和REPLACE有效。 MySQL 会将使用该提示的语句立即返回给客户端,并将插入的行数据放入到缓冲区,然后在表空闲时批量将数据写入。日志系统使用这样的提示非常有效,或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成I/O的应用。这个用法有一些限制：并不是所有的存储引擎都支持这样的做法；并且该提示会导致函数LAST_INSERT_ID()无法正常工作。
STRAIGHT_JOIN
这个提示可以放置在SELECT语句的SELECT关键字之后,也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。
当 MySQL 没能选择正确的关联顺序的时候,或者由于可能的顺序太多导致 MySQL 无法评估所有的关联顺序的时候,STRAIGHT_JOIN都会很有用。在后面这种情况, MySQL 可能会花费大量时间在statistics状态,加上这个提示则会大大减少优化器的搜索空间。可以先使用 EXPLAIN 语句来查看优化器选择的关联顺序,然后使用该提示来重写查询,再看看它的关联顺序。当你确定无论怎样的where条件,某个固定的关联顺序始终是最佳的时候,使用这个提示可以大大提高优化器的效率。但是在升级 MySQL 版本的时候,需要重新审视下这类查询,某些新的优化特性可能会因为该提示而失效。
SQL_SMALL_RESULT 和 SQL_BIG_RESULT
SQL_BUFFER_RESULT
这个提示告诉优化器将查询结果放入到一个临时表,然后尽可能快地释放表锁。
这和前面提到的由客户端缓存结果不同。当你没法使用客户端缓存的时候,使用服务器端的缓存通常很有效。带来的好处是无须在客户端上消耗太多的内存,还可以尽可能快地释放对应的表锁。代价是,服务器端将需要更多的内存。
SQL_BIG_RESULT
告诉优化器结果集可能会非常大,建议使用磁盘临时表做排序操作。
SQL_CACHE 和 SQL_NO_CACHE
这个提示告诉 MySQL 这个结果集是否应该缓存在查询缓存中,下一章我们将详细介绍如何使用。
SQL_CALC_FOUND_ROWS
严格来说,这并不是一个优化器提示。它不会告诉优化器任何关于执行计划的东西。它会让 MySQL 返回的结果集包含更多的信息。查询中加上该提示 MySQL 会计算除去LIMIT子句后这个查询要返回的结果集的总数,而实际上只返回LIMIT要求的结果集。可以通过函数FOUND_ROW()获得这个值。（参阅后面的SQL_CALC_FOUND_ROWS优化部分,了解下为什么不应该使用该提示。）
FOR UPDATE 和 LOCK IN SHARE MODE
这也不是真正的优化器提示。这两个提示主要控制SELECT语句的锁机制,但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。对于INSERT...SELECT语句是不需要这两个提示的,因为对于 MySQL 5.0和更新版本会默认给这些记录加上读锁。（可以禁用该默认行为,但不是个好主意,在后面关于复制和备份的章节中将解释这一点。） 唯一内置的支持这两个提示的引擎就是 InnoDB 。另外需要记住的是,这两个提示会让某些优化无法正常使用,例如索引覆盖扫描。 InnoDB 不能在不访问主键的情况下排他地锁定行,因为行的版本信息保存在主键中。
USE INDEX、IGNORE INDEX 和 FORCE INDEX
USE INDEX则建议优化器使用该索引,但是如果优化器认为它会更快,则可以使用表扫描。如果使用,FORCE INDEX则即使它认为表扫描更有效,让优化器也可以使用此索引。
一些参数用来控制优化器的行为
optimizer_search_depth
这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于Statistics状态,那么可以考虑调低此参数。
optimizer_prune_level
该参数默认是打开的,这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。
optimizer_switch
这个变量包含了一些开启/关闭优化器特性的标志位。例如在 MySQL 5.1中可以通过这个参数来控制禁用索引合并的特性。前两个参数是用来控制优化器可以走的一些捷径。这些捷径可以让优化器在处理非常复杂的SQL语句时,仍然可以很高效,但这也可能让优化器错过一些真正最优的执行计划。所以应该根据实际需要来修改这些参数。
七、优化特定类型的查询
优化COUNT()查询
COUNT()的作用
- 统计某个列值的数量
在统计列值时要求列值是非空的（不统计NULL）。如果在
COUNT()的括号中指定了列或者列的表达式,则统计的就是这个表达式有值的结果数
- 统计行数
统计结果集的行数。当 MySQL 确认括号内的表达式值不可能为空时,实际上就是在统计行数。最简单的就是当我们使用
COUNT（*）的时候,这种情况下通配符*并不会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数。
关于 MyISAM 的神话
一个容易产生的误解就是： MyISAM 的COUNT()函数总是非常快,不过这是有前提条件的,即只有没有任何WHERE条件的COUNT（*）才非常快,因为此时无须实际地去计算表的行数。 MySQL 可以利用存储引擎的特性直接获得这个值。如果 MySQL 知道某列col不可能为NULL值,那么 MySQL 内部会将COUNT（col）表达式优化为COUNT（*）。当统计带WHERE子句的结果集行数,可以是统计某个列值的数量时, MyISAM 的COUNT()和其他存储引擎没有任何不同,就不再有神话般的速度了。所以在 MyISAM 引擎表上执行COUNT()有时候比别的引擎快,有时候比别的引擎慢,这受很多因素影响,要视具体情况而定。
简单的优化
反转条件,来减少需要扫描的行数
使用近似值
30秒统计一次,或者更久长时间的一次时间统计,如果业务对数值不那么敏感的话
更复杂的优化
增加类似Memcached这样的外部缓存系统
优化关联查询
- 确保
ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候,如果优化器的关联顺序是B、A,那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说,除非有其他理由,否则只需要在关联顺序中的第二个表的相应列上创建索引。 - 确保任何的
group by和ORDER BY中的表达式只涉及到一个表中的列,这样 MySQL 才有可能使用索引来优化这个过程。 - 当升级 MySQL 的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡儿积,不同类型的关联可能会生成不同的结果等。
优化子查询
关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询代替,至少当前的 MySQL 版本需要这样。本章的前面章节已经详细介绍了这点。尽可能使用关联并不是绝对的,如果使用的是 MySQL 5.6或更新的版本或者 MariaDB ,那么就可以直接忽略关于子查询的这些建议了。
优化GROUP BY和DISTINCT
GROUP BY
GROUP BY使用两种策略- 使用临时表
- 文件排序
通常采用查找表的标识列分组的效率会比其他列更高。
优化GROUP BY WITH ROLLUP
分组查询的一个变种就是要求 MySQL 对返回的分组结果再做一次超级聚合。
解决办法
- 可以使用
WITH ROLLUP子句来实现这种逻辑,但可能会不够优化。可以通过 EXPLAIN 来观察其执行计划,特别要注意分组是否是通过文件排序或者临时表实现的。然后再去掉WITH ROLLUP子句看执行计划是否相同。也可以通过本节前面介绍的优化器提示来固定执行计划。 - 也可以在 FROM 子句中嵌套使用子查询,或者是通过一个临时表存放中间数据,然后和临时表执行
UNION来得到最终结果。 - 最好的办法是尽可能的将
WITH ROLLUP功能转移到应用程序中处理
优化LIMIT分页
问题原因
在偏移量非常大的时候(27),例如可能是LIMIT 1000,20这样的查询,这时 MySQL 需要查询10 020条记录然后只返回最后20条,前面10 000条记录都将被抛弃,这样的代价非常高。
解决办法
- 在页面中限制分页的数量
- 优化大偏移量的性能
- 优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候,这样做的效率会提升非常大。
案例
原始sql
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
优化1
1
2
3
4
5
6SELECT film.film_id, film.description
FROM sakila.film
INNER JOIN (
SELECT film_id FROM sakila.film
ORDER BY title LIMIT 50, 5
) AS lim USING(film_id);这里的
延迟关联将大大提升查询效率,它让 MySQL 扫描尽可能少的页面,获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。优化2
1
2SELECT film_id, description FROM sakila.film
WHERE position BETWEEN 50 AND 54 ORDER BY position有时候也可以将
LIMIT查询转换为已知位置的查询,让 MySQL 通过范围扫描获得到对应的结果。例如,如果在一个位置列上有索引,并且预先计算出了边界值优化3
1
2SELECT * FROM sakila.rental
ORDER BY rental_id DESC`LIMIT`20;
LIMIT和OFFSET的问题,其实是OFFSET的问题,它会导致 MySQL 扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置,那么下次就可以直接从该书签记录的位置开始扫描,这样就可以避免使用OFFSET。
优化SQL_CALC_FOUND_ROWS
问题原因
MySQL 只有在扫描了所有满足条件的行以后,才会知道行数,所以加上这个提示以后,不管是否需要, MySQL 都会扫描所有满足条件的行,然后再抛弃掉不需要的行,而不是在满足LIMIT的行数后就终止扫描。所以该提示的代价可能非常高。
解决办法
- 一个更好的设计是将具体的页数换成
下一页按钮,假设每页显示20条记录,那么我们每次查询时都是用LIMIT返回21条记录并只显示20条,如果第21条存在,那么我们就显示下一页按钮,否则就说明没有更多的数据,也就无须显示下一页按钮了。 - 另一种做法是先获取并缓存较多的数据——例如,缓存1000条——然后每次分页都从这个缓存中获取。这样做可以让应用程序根据结果集的大小采取不同的策略,如果结果集少于1000,就可以在页面上显示所有的分页链接,因为数据都在缓存中,所以这样做性能不会有问题。如果结果集大于1000,则可以在页面上设计一个额外的
找到的结果多于1000条之类的按钮。这两种策略都比每次生成全部结果集再抛弃掉不需要的数据的效率要高很多。
优化UNION查询
问题原因
MySQL 总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中,以便优化器可以充分利用这些条件进行优化（例如,直接将这些子句冗余地写一份到各个子查询）。
注意:除非确实需要服务器消除重复的行,否则就一定要使用
UNION ALL,这一点很重要。如果没有ALL关键字, MySQL 会给临时表加上DISTINCT选项,这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字, MySQL 仍然会使用临时表存储结果。事实上, MySQL 总是将结果放入临时表,然后再读出,再返回给客户端。
静态查询分析
- Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式,然后给出所有可能存在潜在问题的查询,并给出足够详细的建议。
使用用户自定义变量
在哪些场景下我们不能使用用户自定义变量
- 使用自定义变量的查询,无法使用查询缓存。
- 不能在使用常量或者标识符的地方使用自定义变量,例如表名、列名和
LIMIT子句中。 - 用户自定义变量的生命周期是在一个连接中有效,所以不能用它们来做连接间的通信。
- 如果使用连接池或者持久化连接,自定义变量可能让看起来毫无关系的代码发生交互（如果是这样,通常是代码bug或者连接池bug,这类情况确实可能发生）。
- 在5.0之前的版本,是大小写敏感的,所以要注意代码在不同 MySQL 版本间的兼容性问题。
- 不能显式地声明自定义变量的类型。确定未定义变量的具体类型的时机在不同 MySQL 版本中也可能不一样。如果你希望变量是整数类型,那么最好在初始化的时候就赋值为
0,如果希望是浮点型则赋值为0.0,如果希望是字符串则赋值为’’,用户自定义变量的类型在赋值的时候会改变。 MySQL 的用户自定义变量是一个动态类型。 - MySQL 优化器在某些场景下可能会将这些变量优化掉,这可能导致代码不按预想的方式运行。
- 赋值的顺序和赋值的时间点并不总是固定的,这依赖于优化器的决定。实际情况可能很让人困惑,后面我们将看到这一点。
- 赋值符号
:=的优先级非常低,所以需要注意,赋值表达式应该使用明确的括号。使用未定义变量不会产生任何语法错误,如果没有意识到这一点,非常容易犯错。 - 优化排名语句
- 避免重复查询刚刚更新的数据
- 统计更新和插入的数量
- 确定取值的顺序
- 编写偷懒的
UNION - 用户自定义变量的其他用处
