网站一直百度上搜不到是怎么回事,wordpress是用什么数据库的,wordpress安装到空间,软件开发与网站开发的区别文章目录 六、查询性能优化6.1 查询为什么会慢6.2 慢查询基础#xff1a;优化数据访问6.2.1 是否向数据库请求了不需要的数据查询不需要的记录多表关联时返回全部列总是取出全部列重复查询相同的数据 6.2.2 MySQL 是否在扫描额外的记录响应时间扫描的行数与返回的行数扫描的行… 文章目录 六、查询性能优化6.1 查询为什么会慢6.2 慢查询基础优化数据访问6.2.1 是否向数据库请求了不需要的数据查询不需要的记录多表关联时返回全部列总是取出全部列重复查询相同的数据 6.2.2 MySQL 是否在扫描额外的记录响应时间扫描的行数与返回的行数扫描的行数与返回的类型 6.3 重构查询的方式6.3.1 一个复杂查询还是多个简单查询6.3.2 切分查询6.3.3 分解关联查询 6.4 查询执行的基础6.4.1 MySQL 客户端/服务器通信协议查询状态 6.4.2 查询缓存6.4.3 查询优化处理语法解析器和预处理查询优化器数据和索引的统计信息MySQL如何执行关联查询执行计划关联查询优化器排序优化 6.4.4 查询执行引擎6.4.5 返回结果给客户端 6.5 MySQL查询优化器的局限性6.5.1 关联子查询是否使用关联子查询 6.5.2 UNION的限制6.5.3 索引合并优化6.5.4 等值传递6.5.5 并行执行6.5.6 哈希关联6.5.7松散索引扫描6.5.8 最大值和最小值优化6.5.9 在同一个表.上查询和更新 6.6 查询优化器的提示6.7 优化特定的查询6.7.1 优化COUNT()查询COUNT()的作用关于MyISAM的神话简单的优化使用近似值更复杂的优化 6.7.2 优化关联查询6.7.3 优化子查询6.7.4 优化GROUP BY和DISTINCT优化GROUP BY WITH ROLLUP 6.7.5 优化LIMIT分页6.7.6 优化SQL_CALC_FOUND_ROWS6.7.7 优化UNION查询6.7.8静态查询分析6.7.9 使用用户自定义变量优化排名语句避免重复查询刚刚更新的数据统计更新和插入的数量确定取值的顺序编写偷懒的UNION 附录 六、查询性能优化
6.1 查询为什么会慢
将查询看作一个任务那么它由一系列子任务组成实际我们所做的就是
消除一些子任务减少子任务的执行次数让子任务运行更快 查询的生命周期大概可分为 { 客户端 服务器 : 进行解析 , 生成执行计划 执行包括到存储引擎的调用以及用后的数据处理 { 排序 分组 结果返回客户端 查询的生命周期大概可分为\left\{ \begin{matrix} 客户端 \\ 服务器:进行解析,生成执行计划 \\ 执行包括到存储引擎的调用以及用后的数据处理 \left\{ \begin{matrix}排序\\分组\end{matrix} \right.\\ 结果返回客户端 \end{matrix} \right. 查询的生命周期大概可分为⎩ ⎨ ⎧客户端服务器:进行解析,生成执行计划执行包括到存储引擎的调用以及用后的数据处理{排序分组结果返回客户端
6.2 慢查询基础优化数据访问
查询性能低下最基本的原因是 访问的数据太多 。
某些查询可能不可避免地需要筛选大量数据但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。
确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行但有时候也可能是访问了太多的列。确认MySQL服务器层是否在分析大量超过需要的数据行。
6.2.1 是否向数据库请求了不需要的数据
查询不需要的记录
一个常见的错误是常常会误以为MySQL会只返回需要的数据实际上MySQL却是先返回全部结果集再进行计算。
一些开发者习惯使用这样的技术先使用SELECT语句查询大量的结果然后获取前面的N行后关闭结果集(例如在新闻网站中取出100条记录,但是只是在页面上显示前面10条)。他们认为MySQL会执行查询并只返回他们需要的10条数据然后停止查询。
实际情况是MySQL会查询出全部的结果集客户端的应用程序会接收全部的结果集数据然后抛弃其中大部分数据。最简单有效的解决方法就是在这样的查询后面加上LIMIT。
多表关联时返回全部列
如果你想查询所有在电影Academy Dinosaur中出现的演员千万不要按下面的写法编写查询:
mysql SELECT * FROM sakila.actor
- INNER JOIN sakila.film_actor USING(actor_id)
- INNER JOIN sakila.film USING(film_id)
- WHERE sakila.film.title Academy Dinosaur;
这将返回这三个表的全部数据列。
正确的方式应该是像下面这样只取需要的列:
mysql SELECT sakila.actor.* FROM sakila.actor...;
总是取出全部列
每次看到SELECT*的时候都需要用怀疑的眼光审视是不是真的需要返回全部的列?很可能不是必需的。
取出全部列会让优化器无法完成索引覆盖扫描这类优化还会为服务器带来额外的I/O、内存和CPU的消耗。因此一些DBA是严格禁止SELECT *的写法的这样做有时候还能避免某些列被修改带来的问题。
重复查询相同的数据
如果你不太小心很容易出现这样的错误一不断地重复执行相同的查询然后每次都返回完全相同的数据。
6.2.2 MySQL 是否在扫描额外的记录
在确定查询只返回需要的数据以后接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL,最简单的衡量查询开销的三个指标如下:
响应时间扫描的行数返回的行数
没有哪个指标能够完美地衡量查询的开销但它们大致反映了MySQL在内部执行查询时需要访问多少数据并可以大概推算出查询运行的时间。这三个指标都会记录到MySQL的慢日志中所以检查慢日志记录是找出扫描行数过多的查询的好办法。
响应时间 响应时间 { 服务时间实际处理任务花费时间 排队时间等待任务开始的时间 响应时间\left\{ \begin{matrix}服务时间实际处理任务花费时间\\排队时间等待任务开始的时间\end{matrix} \right. 响应时间{服务时间实际处理任务花费时间排队时间等待任务开始的时间
扫描的行数与返回的行数
理想情况:扫描行数返回行数实际一般 扫描:返回 的值一般在 1:1~10:1。
扫描的行数与返回的类型
MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果也有些访问方式可能无须扫描就能返回结果。
在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些速度是从慢到快扫描的行数也是从小到大。
需要明白扫描表、扫描索引、范围访问和单值访问的概念。
如果查询没有办法找到合适的访问类型那么解决的最好办法通常就是增加一个合适的索引。
一般MySQL能够使用如下三种方式应用WHERE条件从好到坏依次为:
在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。使用索引覆盖扫描(在Extra列中出现了Using index)来返回记录直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的但无须再回表查询记录。从数据表中返回数据然后过滤不满足条件的记录(在Extra列中出现Using Where)。这在MySQL服务器层完成MySQL需要先从数据表读出记录然后过滤。
6.3 重构查询的方式
在优化有问题的查询时目标应该是找到一个更优的方法获得实际需要的结果一而不一定总是需要从MySQL获取一模一样的结果集。
6.3.1 一个复杂查询还是多个简单查询
MySQL从设计上让连接和断开连接都很轻量级在返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多运行多个小查询现在已经不是大问题了。
MySQL内部每秒能够扫描内存中上百万行数据相比之下MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候,使用尽可能少的查询当然是更好的。但是有时候将一个大查询分解为多个小查询是很有必要的。
不过在应用设计的时候如果一个查询能够胜任时还写成多个独立查询是不明智的。
6.3.2 切分查询
有时候对于一个大查询我们需要“分而治之”将大查询切分成小查询每个查询功能完全一样只完成一小部分每次只返回一小部分查询结果。
删除旧的数据就是一个很好的例子。定期地清除大量数据时如果用一个大的语句一次性完成的话则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能同时还可以减少MySQL复制的延迟。
6.3.3 分解关联查询 就是把一个复杂的关联查询拆解到业务中去分开查询 用分解关联查询的方式重构查询有如下的优势: 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。另外对MySQL的查询缓存来说,如果关联中的某个表发生了变化那么就无法使用查询缓存了而拆分后如果某个表很少改变那么基于该表的查询就可以重复利用查询缓存结果了。 将查询分解后执行单个查询可以减少锁的竞争。 在应用层做关联可以更容易对数据库进行拆分更容易做到高性能和可扩展。查询本身效率也可能会有所提升。这个例子中使用IN()代替关联查询可以让MySQL按照ID顺序进行查询这可能比随机的关联要更高效。 可以减少冗余记录的查询。在应用层做关联查询意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看,这样的重构还可能会减少网络和内存的消耗。 更进一步这样做相当于在应用中实现了哈希关联而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。
6.4 查询执行的基础
当希望MySQL能够以更高的性能运行查询时最好的办法就是弄清楚MySQL是如何优化和执行查询的。
根据图6-1我们可以看到当向MySQL发送一个请求的时候MySQL到底做了些什么:
客户端发送一条查询给服务器。服务器先检查查询缓存如果命中了缓存则立刻返回存储在缓存中的结果。否则进入下一阶段。服务器端进行SQL解析、预处理再由优化器生成对应的执行计划。MySQL 根据优化器生成的执行计划调用存储引擎的API来执行查询。将结果返回给客户端。
6.4.1 MySQL 客户端/服务器通信协议
MySQL客户端和服务器之间的通信协议是“半双工”的在任何一个时刻要么是由服务器向客户端发送数据要么是由客户端向服务器发送数据这两个动作不能同时发生。所以我们无法也无须将一个消息切成小块独立来 发送。
这种协议让MySQL通信简单快速但是也从很多地方限制了MySQL。一个明显的限制是这意味着没法进行流量控制。一旦一端开始发生消息另一端要接收完整个消息才能响应它。 客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候参数max_allowed_ packet 就特别重要了。一旦客户端发送了请求它能做的事情就只是等待结果了。 相反的一般服务器响应给用户的数据通常很多由多个数据包组成。当服务器开始响应客户端请求时客户端必须完整地接收整个返回结果而不能简单地只取前面几条结果然后让服务器停止发送数据。这种情况下客户端若接收完整的结果然后取前面几条需要的结果或者接收完几条结果后就“粗暴”地断开连接都不是好主意。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。 多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。
MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源所以接收全部结果并缓存通常可以减少服务器的压力让查询能够早点结束、早点释放相应的资源。
当使用多数连接MySQL的库函数从MySQL获取数据时其结果看起来都像是从MySQL服务器获取数据而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题但是如果需要返回一个很大的结果集的时候这样做并不好因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集就能大大减少内存的消耗这种情况下可以不使用缓存来记录结果而是直接处理。
这样做的缺点是对于服务器来说需要查询完成后才能释放资源所以在和客户端交互的整个过程中服务器的资源都是被这个查询所占用的。
我们看看当使用PHP的时候是什么情况。首先下面是我们连接MySQL的通常写法: ?php
$link mysql_connect(localhost, user , p4ssword);
$result mysql_query(SELECT * FROM HUGE_TABLE, $link);
while($row mysql_fetch_array($result)) {// Do something with result
}
?
这段代码看起来像是只有当你需要的时候才通过循环从服务器端取出数据。而实际上,在上面的代码中在调用mysql_query() 的时候PHP就已经将整个结果集缓存到内存中。
下面的while循环只是从这个缓存中逐行取出数据相反如果使用下面的查询用mysql_unbuffered_ query() 代替mysql_query(), PHP则不会缓存结果:
?php
$link mysql_connect(localhost, user , password);
$result mysql_unbuffered_query(SELECT * FROM HUGE_TABLE, $link);
while($row mysql_fetch_array($result)) {// Do something with result
}
? mybatis的缓存使用-MyBatis的缓存机制 查询状态
对于一个MySQL连接或者说一个线程任何时刻都有一个状态该状态表示了MySQL当前正在做什么。
有很多种方式能查看当前的状态最简单的是使用 SHOW FULL PROCESSLIST 命令( 该命令返回结果中的Command列就表示当前的状态)。在一个查询的生命周期中状态会变化很多次。
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
这表示多种情况:线程可能在多个状态之间传送数据或者在生成结果集或者在向客户端返回数据。
6.4.2 查询缓存
在解析一个查询语句之前如果查询缓存是打开的那么MySQL会优先检查这个查询是否命中查询缓存中的数据。
这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同那也不会匹配缓存结果,这种情况下查询就会进入下一阶段的处理。
如果当前的查询恰好命中了查询缓存那么在返回查询结果之前MySQL会检查一次用户权限。
这仍然是无须解析查询SQL语句的因为在查询缓存中已经存放了当前查询需要访问的表信息。
如果权限没有问题MySQL会跳过所有其他阶段直接从缓存中拿到结果并返回给客户端。这种情况下查询不会被解析,不用生成执行计划不会被执行。
6.4.3 查询优化处理
查询的生命周期的下一步是将一个SQL转换成一个执行计划MySQL再依照这个执行计划和存储引擎进行交互。
这包括多个子阶段:解析SQL、预处理、优化SQL执行计划。
这个过程中任何错误(例如语法错误)都可能终止查询。
语法解析器和预处理
首先MySQL通过关键字将SQL语句进行解析并生成一棵对应的“解析树”。
MySQL解析器将使用MySQL语法规则验证和解析查询。
预处理器则根据一些MySQL规则进一步检查解析树是否合法。
下一步预处理器会验证权限。这通常很快除非服务器上有非常多的权限配置。
查询优化器
一条查询可以有很多种执行方式最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
MySQL使用基于成本的优化器它将尝试预测一个查询使用某种执行计划时的成本并选择其中成本最小的一个。
最初成本的最小单位是随机读取一个4K数据页的成本后来(成本计算公式)变得更加复杂并且引入了一些“因子”来估算某些操作的代价,如当执行一次 WHERE条件比较的成本。
可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。 有很多种原因会导致MySQL优化器选择错误的执行计划如下所示:
统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本但是有的存储引擎提供的信息是准确的有的偏差可能非常大。 例如InnoDB因为其MVCC的架构并不能维护一个数据表的行数的精确统计信息。 执行计划中的成本估算不等同于实际执行的成本。所以即使统计信息精准优化器给出的执行计划也可能不是最优的。 例如有时候某个执行计划虽然需要读取更多的页面但是它的成本却更小。因为如果这些页面都是顺序读或者这些页面都已经在内存中的话那么它的访问成本将很小。MySQL层面并不知道哪些页面在内存中、哪些在磁盘上所以查询实际执行过程中到底需要多少次物理I/O是无法得知的。 MySQL的最优可能和你想的最优不-一样。你可能希望执行时间尽可能的短但是MySQL只是基于其成本模型选择最优的执行计划而有些时候这并不是最快的执行方式。所以这里我们看到根据执行成本来选择执行计划并不是完美的模型。MySQL从不考虑其他并发执行的查询这可能会影响到当前查询的速度。MySQL也并不是任何时候都是基于成本的优化。有时也会基于一些固定的规则 例如如果存在全文搜索的MATCH()子句则在存在全文索引的时候就使用全文索引。即使有时候使用别的索引和WHERE条件可以远比这种方式要快MySQL 也仍然会使用对应的全文索引。 MySQL不会考虑不受其控制的操作的成本例如执行存储过程或者用户自定义函数的成本。优化器有时候无法去估算所有可能的执行计划所以它可能错过实际上最优的执行计划。 MySQL的查询优化器是一个非常复杂的部件它使用了很多优化策略来生成一个最优的执行计划。
优化策略可以简单地分为两种
一种是静态优化静态优化可以直接对解析树进行分析并完成优化。 例如优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种“编译时优化”。 一种是动态优化动态优化则和查询的上下文有关也可能和很多其他因素有关。 例如WHERE条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估可以认为这是“运行时优化”。
在执行语句和存储过程的时候动态优化和静态优化的区别非常重要。
MySQL对查询的静态优化只需要做一次但对查询的动态优化则在每次执行时都需要重新评估。有时候甚至在查询的执行过程中也会重新优化。
下面是一些MySQL能够处理的优化类型:
重新定义关联表的顺序
数据表的关联并不总是按照在查询中指定的顺序进行。决定关联的顺序是优化器很重要的一部分功能。
将外连接转化成内连接
并不是所有的 OUTER JOIN 语句都必须以外连接的方式执行。
诸多因素例如WHERE条件、库表结构都可能会让外连接等价于一个内连接。MySQL能够识别这点并重写查询让其可以调整关联顺序。
使用等价变换规则
MySQL可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断。例如(55 AND a5) 将被改写为a5。类似的如果有(ab AND bc) AND a5 则会改写为b5 AND bc AND a5。
这些规则对于我们编写条件语句很有用我们将在本章后续继续讨论。
优化COUNT()、 MIN() 和MAX()
索引和列是否可为空通常可以帮助MySQL优化这类表达式。
例如要找到某一列的最小值只需要查询对应B-Tree索引最左端的记录MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点,在B-Tree索引中优化器会将这个表达式作为一个常数对待。
类似的,如果要查找一个最大值也只需读取B-Tree索引的最后一条记录。
如果MySQL使用了这种类型的优化那么在EXPLAIN中就可以看到“ Select tables optimized away”。从字面意思可以看出它表示优化器已经从执行计划中移除了该表并以一个常数取而代之。
类似的没有任何WHERE条件的COUNT(*)查询通常也可以使用存储引擎提供的一些优化(例如MyISAM维护了一个变量来存放数据表的行数)。
预估并转化为常数表达式
当MySQL检测到一个表达式可以转化为常数的时候就会一直把该表达式作为常数进行优化处理。
例如一个用户自定义变量在查询中没有发生变化时就可以转换为一个常数。
数学表达式则是另一种典型的例子。在优化阶段有时候甚至一个查询也能够转化为一个常数。
一个例子是在索引列上执行MIN()函数。甚至是主键或者唯一键查找语句也可以转换为常数表达式。
如果WHERE子句中使用了该类索引的常数条件MySQL可以在查询开始阶段就先查找到这些值这样优化器就能够知道并转换为常数表达式。
下面是一个例子:
mysql EXPLAIN SELECT film.film_id,film_actor.actor_id
- FROM sakila.film
- INNER JOIN sakila.film_actor USING(film_id)
- WHERE film.film_id 1; MySQL分两步来执行这个查询也就是上面执行计划的两行输出。 第一步先从film表找到需要的行。因为在film_id 字段上有主键索引所以MySQL优化器知道这只会返回一行数据优化器在生成执行计划的时候就已经通过索引信息知道将返回多少行数据。因为优化器已经明确知道有多少个值(WHERE条件中的值)需要做索引查询所以这里的表访问类型是const。 在执行计划的第二步MySQL将第一步中返回的film_id列当作一个已知取值的列来处理。因为优化器清楚在第一步执行完成后该值就会是明确的了。注意到正如第一步中一样使用film_actor字段对表的访问类型也是const。
另一种会看到常数条件的情况是通过等式将常数值从一个表传到另一个表这可以通过WHERE、USING 或者ON语句来限制某列取值为常数。
在上面的例子中因为使用了USING子句优化器知道这也限制了film_id在整个查询过程中都始终是一个常量——因为它必须等 于WHERE子句中的那个取值。 覆盖索引扫描
当索引中的列包含所有查询中需要使用的列的时候MySQL就可以使用索引返回需要的数据而无须查询对应的数据行。
子查询优化
MySQL在某些情况下可以将子查询转换-种效率更高的形式从而减少多个查询多次对数据进行访问。
提前终止查询
在发现已经满足查询需求的时候MySQL总是能够立刻终止查询。-一个典型的例子就是当使用了LIMIT子句的时候。
除此之外MySQL还有几类情况也会提前终止查询例如发现了一个不成立的条件这时MySQL可以立刻返回一个空结果。
从下面的例子可以看到这一点: 从这个例子看到查询在优化阶段就已经终止。
除此之外MySQL在执行过程中如果发现某些特殊的条件则会提前终止查询。
当存储引擎需要检索“不同取值”或者判断存在性的时候MySQL都可以使用这类优化。例如我们现在需要找到没有演员的所有电影。
等值传播
如果两个列的值通过等式关联那么MySQL能够把其中一个列的WHERE条件传递到另一列上。
例如我们看下面的查询:
mysql SELECT film.film_id
- FROM sakila.film
- INNER JOIN sakila.film_actor USING(fi1m_ id)
- WHERE film.film_ id 500;
因为这里使用了film_ id字段进行等值关联MySQL知道这里的WHERE子句不仅适用于film表,而且对于film_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架构由多个层次组成。在服务器层有查询优化器却没有保存数据和索引的统计信息。
统计信息由存储引擎实现不同的存储引擎可能会存储不同的统计信息(也可以按照不同的格式存储统计信息)。
某些引擎,例如Archive引擎,则根本就没有存储任何统计信息.
因为服务器层没有任何统计信息所以MySQL查询优化器在生成查询的执行计划时需要向存储引擎获取相应的统计信息。
存储引擎则提供给优化器对应的统计信息包括:
每个表或者索引有多少个页面每个表的每个索引的基数是多少数据行和索引长度索引的分布信息… …
优化器根据这些信息来选择一个最优的执行计划。
MySQL如何执行关联查询
MySQL中“关联”一词所包含的意义比一般意义上理解的要更广泛。总的来说MySQL认为任何一个查询都是一次“关联”一并不仅仅是一个 查询需要到两个表匹配才叫关联所以在MySQL中每一个查询每一个片段(包括子查询甚至基于单表的SELECT)都可能是关联。
对于UNION查询MySQL先将一系列的 单个查询结果放到一个临时表中然后再重新读出临时表数据来完成UNION查询。在MySQL的概念中每个查询都是一次关联所以读取结果临时表也是一次关联。 当前MySQL关联执行的策略很简单:
MySQL对任何关联都执行嵌套循环关联操作即MySQL先在一个表中循环取出单条数据然后再嵌套循环到下一个表中寻找匹配的行依次下去直到找到所有表中匹配的行为止。
然后根据各个表匹配的行返回查询中需要的各个列。
MySQL会尝试在最后一个关联表中找到所有匹配的行如果最后一个关联表无法找到更多的行以后MySQL返回到上一层次关联表看是否能够找到更多的匹配记录依此类推迭代执行。
按照这样的方式查找第一个表记录再嵌套查询下一个关联表然后回溯到上一个表在MySQL中是通过嵌套循环的方式实现一正如其名“嵌套循环关联”。 如下例子中的简单查询:
mysql SELECT tbl1.co1, tbl2.col2
- FROM tbl1 INNER JOIN tbl2 USING(col3)
- WHERE tbl1.col1 IN(5,6);
假设MySQL按照查询中的表顺序进行关联操作我们则可以用下面的伪代码表示MySQL将如何完成这个查询:
outer_iter iterator over tbl1 where col1 IN(5,6)
outer_row outer_iter.next
while outer_rowinner_iter iterator over tbl2 where col3 outer_row.col3inner_row inner_ iter.nextwhile inner_ rowoutput[outer row.col1,inner_row.col2]inner_row inner_iter.nextendouter_row outer_iter.next
end上面的执行计划对于单表查询和多表关联查询都适用如果是一个单表查询那么只需完成上面外层的基本操作。
对于外连接上面的执行过程仍然适用。
例如我们将上面查询修改如下:
mysql SELECT tbl1.col1, tbl2.col2
- FROM tbl1 LEFT OUTER JOIN tbl2 USING(col3)
- WHERE tbl1.col1 IN(5,6);
对应的伪代码如下:
outer_iter iterator over tbl1 where col1 IN(5,6)
outer_ row outer_ iter.next
while outer_ rowinner_ iter iterator over tbl2 where col3 outer_ row.col3inner_ row inner_ iter.nextif inner_ rowwhile inner_rowoutput [outer_ row.col1, inner_ row.col2]inner_row inner_ iter.nextendelseoutput[outer_row.col1, NULL ]endouter_row outer_iter.next
end
或者如下6-2“泳道图” 从本质上说MySQL对所有的类型的查询都以同样的方式运行。例如MySQL在FROM子句中遇到子查询时先执行子查询并将其结果放到一个临时表中,然后将这个临时表当作一个普通表对待(正如其名‘派生表”)。 MySQL在执行UNION查询时也使用类似的临时表在遇到右外连接的时候MySQL将其改写成等价的左外连接。
简而言之当前版本的MySQL会将所有的查询类型都转换成类似的执行计划。
不过不是所有的查询都可以转换成上面的形式。
例如全外连接就无法通过嵌套循环和回溯的方式完成这时当发现关联表中没有找到任何匹配行的时候则可能是因为关联是恰好从一个没有任何匹配的表开始。这大概也是MySQL并不支持全外连接的原因。
还有些场景虽然可以转换成嵌套循环的方式但是效率却非常差。
执行计划
和很多其他关系数据库不同MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一棵指令树然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。
如果对某个查询执行EXPLAIN EXTENDED 后再执行SHOWWARNINGS就可以看到重构出的查询。
任何多表查询都可以使用一棵树表示例如可以按照图6-3执行一个四表的关联操作。 在计算机科学中这被称为一颗平衡树。但是这并不是MySQL执行查询的方式。
MySQL总是从一个表开始一直嵌套循环、回溯完成所有表关联。所以MySQL的执行计划总是如图6-4所示是一棵左测深度优先的树。 关联查询优化器
MySQL优化器最重要的一部分就是关联查询优化它决定了多个表关联时的顺序。
通常多表关联的时候可以有多种不同的关联顺序来获得相同的执行结果。联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。
下面的查询可以通过不同顺序的关联最后都获得相同的结果:
mysql SELECT film.film_id,film.title,film.release_year,actor.actor.id,
actor.first_ name,actor.last_ name
- FROM sakila.film
- INNER JOIN sakila.film_actor USING(film_ id)
- INNER JOIN sakila.actor USING(actor_ id);
容易看出可以通过一些不同的执行计划来完成上面的查询。
例如MySQL可以从film表开始,使用film actor表的索引film id来查找对应的actor. id值,然后再根据actor表的主键找到对应的记录。Oracle 用户会用下面的术语描述: “film表作为驱动表先查找fle_ actor 表,然后以此结果为驱动表再查找actor表”。
这样做效率应该会不错我们再使用EXPLAIN看看MySQL将如何执行这个查询:
与我们假设的不同。
我们先使用STRAIGHT_J0IN关键字按照之前的顺序执行 MySQL基础之STRAIGHT JOIN用法简介 这里是对应的EXPLAIN输出结果:
我们来分析一下为什么MySQL会将关联顺序倒转过来:可以看到关联顺序倒转后的第一个关联表只需要扫描很少的行数。在
两种关联顺序下第二个和第三个关联表都是根据索引查询速度都很快不同的是需要扫描的索引项的数量是不同的:
将film表作为第一个关联表时会找到951条记录然后对film_ actor和actor表进行嵌套循环查询。如果MySQL选择首先扫描actor表只会返回200条记录进行后面的嵌套循环查询。
换句话说倒转的关联顺序会让查询进行更少的嵌套循环和回溯操作。为了验证优化器的选择是否正确我们单独执行这两个查询并且看看对应的Last_ query_cost状态值。我们看到倒转的关联顺序的预估成本为241而原来的查询的预估成本为1154。 关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树。如果可能优化器会遍历每一个表然后逐个做嵌套循环计算每–棵可能的执行计划树的成本最后返回一个最优的执行计划。
不过如果有超过n个表的关联那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间”,搜索空间的增长速度非常快。当搜索空间非常大的时候优化器选择使用“贪婪”搜索的方式查找“ 最优”的关联顺序。
实际上当需要关联的表超过optimizer_ search_depth的限制的时候就会选择“ 贪婪”搜索模式了(optimizer_ search_ depth 参数可以根据需要指定大小)。
排序优化
无论如何排序都是一个成本很高的操作所以从性能角度考虑应尽可能避免排序或者尽可能避免对大量数据进行排序。
无法使用索引排序的时候MySQL 需要自己进行排序如果数据量小则在内存中进行如果数据量大则需要使用磁盘不过MySQL将这个过程统一称为文件排序(filesort) 即使完全是内存排序不需要任何磁盘文件时也是如此。
如果需要排序的数据量小于“排序缓冲区”MySQL使用内存进行“快速排序”操作。如果内存不够排序那么MySQL会先将数据分块对每个独立的块使用“ 快速排序”进行排序并将各个块的排序结果存放在磁盘上然后将各个排好序的块进行 合并(merge) 最后返回排序结果。
MySQL有如下两种排序算法
两次传输排序(旧版本使用)
读取行指针和需要排序的字段对其进行排序然后再根据排序结果读取所需要的数据行。
这需要进行两次数据传输即需要从数据表中读取两次数据第二次读取数据的时候因为是读取排序列进行排序后的所有记录这会产生大量的随机I/O所以两次数据传输的成本非常高。当使用的是MyISAM表的时候成本可能会更高因为MyISAM使用系统调用进行数据的读取(MyISAM非常依赖操作系统对数据的缓存)。
不过这样做的优点是在排序的时候存储尽可能少的数据这就让“排序缓冲区”中可能容纳尽可能多的行数进行排序。
单次传输排序(新版本使用)
先读取查询所需要的所有列然后再根据给定列进行排序最后直接返回排序结果。
这个算法只在MySQL 4.1和后续更新的版本才引入。因为不再需要从数据表中读取两次数据,对于I/O密集型的应用,这样做的效率高了很多。另外,相比两次传输排序,这个算法只需要一次顺序I/O读取所有的数据而无须任何的随机I/O。
缺点是如果需要返回的列非常多、非常大会额外占用大量的空间而这些列对排序操作本身来说是没有任何作用的。因为单条排序记录很大,所以可能会有更多的排序块需要合并。 两种算法都有各自最好和最糟的场景。当查询需要所有列的总长度不超过参数max_ length_ for_ sort_data 时MySQL使用“单次传输排序”可以通过调整这个参数来影响MySQL排序算法的选择。
MySQL文件排序使用的临时空间可能比较多。MySQL在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。原因在于MySQL在排序时对每一个排序记录都会分配一个足够长的定长空间来存放。
这个定长空间必须足够长以容纳其中 最长的字符串 例如如果是VARCHAR列则需要分配其完整长度;如果使用UTF-8字符集那么MySQL将会为每个字符预留三个字节。 在关联查询的时候如果需要排序MySQL会分两种情况来处理这样的文件排序。
如果ORDER BY 子句中的所有列都来自关联的第一个表那么MySQL在关联处理第一个表的时候就进行文件排序。如果是这样那么在MySQL的EXPLAIN结果中可以看到Extra字段会有“Using filesort 。除此之外的所有情况MySQL 都会先将关联的结果存放到一个临时表中然后在所有的关联都结束后再进行文件排序。这种情况下在MySQL的EXPLAIN结果的Extra字段可以看到“Using temporary; Using filesort”。 如果查询中有LIMIT的话LIMIT也会在排序之后应用所以即使需要返回较少的数据临时表和需要排序的数据量仍然会非常大。MySQL 5.6在这里做了很多重要的改进。当只需要返回部分排序结果的时候例如使用了LIMIT子句MySQL不再对所有的结果进行排序而是根据实际情况选择拋弃不满足条件的结果然后再进行排序。
6.4.4 查询执行引擎
在解析和优化阶段MySQL将生成查询对应的执行计划MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是-一个数据结构而不是和很多其他的关系型数据库那样会生成对应的字节码。
相对于查询优化阶段查询执行阶段不是那么复杂:MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中有大量的操作需要通过调用存储弓|擎实现的接口来完成这些接口也就是我们称为“handler API”的接口。
查询中的每一个表由一个handler的实例表示。MySQL在优化阶段就为每个表创建了一个handler实例优化器根据这些实例的接口可以获取表的相关信息包括表的所有列名、索引统计信息等等。 存储引擎接口有着非常丰富的功能但是底层接口却只有几十个这些接口像“搭积木”一样能够完成查询的大部分操作。简单的接口模式让MySQL的存储引擎插件式架构成为可能但也给优化器带来了一定的限制。
并不是所有的操作都由handler完成。例如当MySQL需要进行表锁的时候。handler可能会实现自己的级别的、更细粒度的锁如InnoDB就实现了自己的行基本锁但这并不能代替服务器层的表锁。6.4.5 返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端MySQL仍然会返回这个查询一些信息如该查询影响到的行数。
如果查询可以被缓存那么MySQL在这个阶段也会将结果存放到查询缓存中。
MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如我们回头看看前面的关联操作一旦服务器处理完最后一个关联表开始生成第一条结果时MySQL就可以开始向客户端逐步返回结果集了。
这样处理有两个好处:
服务器端无须存储太多的结果也就不会因为要返回太多结果而消耗太多内存。这样的处理也让MySQL客户端第一时间获得返回的结果。
结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送再通过TCP协议进行传输在TCP传输的过程中可能对MySQL的封包进行缓存然后批量传输。
6.5 MySQL查询优化器的局限性
对于开发中使用的版本是否存在这类缺陷 请自行在使用中判断。6.5.1 关联子查询
MySQL的子查询实现得非常糟糕。
最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。
例如我们希望找到Sakila数据库中演员Penelope Guiness (他的actor_ id为1)参演过的所有影片信息。很自然的我们会按照下面的方式用子查询实现:
mysql SELECT * FROM sakila.film
- WHERE film_id IN(
- SELECT film_id FROM sakila.film_actor WHERE actor_id 1
- );
因为MySQL对IN()列表中的选项有专门的优化策略一般会认为MySQL会先执行子查询返回所有包含actor_id为1的film_id。一般来说IN()列表查询速度很快所以我们会认为上面的查询会这样执行:
-- SELECT GROUP_ CONCAT(film_id) FROM sakila.film _actor WHERE actor_id 1;
-- Result: 1,23,25, 106, 140,166,277,361,438,499, 506, 509, 605, 635, 749,832, 939,970, 980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25, 106, 140, 166,277,361,438,499, 506, 509, 605,635, 749,832,939,970,980);
很不幸MySQL不是这样做的。MySQL会将相关的外层表压到子查询中它认为这样 可以更高效率地查找到数据行。也就是说MySQL会将查询改写成下面的样子:
SELECT * FROM sakila.film
WHERE EXISTS (SELECT * FROM sakila.film actor WHERE actor_id 1AND film_actor.film_id film.film_id
);
这时子查询需要根据film_id来关联外部表film,因为需要film_ id字段,所以MySQL认为无法先执行这个子查询。通过EXPLAIN我们可以看到子查询是一个相关子查询(DEPENDENT SUBQUERY) ( 可以使用EXPLAIN EXTENDED 来查看这个查询被改写成了什么样子) : 可以看到MySQL将film表全部扫描出来然后判断exists里面的条件可以用下面的办法来重写这个查询:
mysql SELECT film.* FROM sakila.film
- INNER JOIN sakila.film_actor USING(film_id)
- WHERE actor_id 1;
是否使用关联子查询
建议自己写一遍后判断。
6.5.2 UNION的限制
有时MySQL无法将限制条件从外层“下推”到内层这使得原本能够限制部分返回 结果的条件无法应用到内层查询的优化上。
如果希望UNION的各个子句能够根据LIMIT只取部分结果集或者希望能够先排好序再合并结果集的话就需要在UNION的各个子句中分别使用这些子句。
例如想将两个子查询结果联合起来然后再取前20条记录那么MySQL会将两个表都存放到同一个临时表中然后再取出前20行记录:
(SELECT first_name,last_nameFROM sakila.actorORDER BY last_name)
UNION ALL
(SELECT first_name,last_nameFROM sakila.customerORDER BY last_name)
LIMIT 20;
这条查询将会把actor中的200条记录和customer表中的599条记录存放在一个临时表中然后再从临时表中取出前20条。可以通过在UNION的两个子查询中分别加上一个LIMIT 20来减少临时表中的数据:
(SELECT first name, last_ nameFROM sakila.actorORDER BY last_name
LIMIT 20)
UNION ALL
(SELECT first_name, last_nameFROM sakila.customerORDER BY last_nameLIMIT 20)
LIMIT 20;
现在中间的临时表只会包含40条记录了除了性能考虑之外在这里还需要注意一点:
从临时表中取出数据的顺序并不是一定的所以如果想获得正确的顺序还需要加上一个全局的ORDERBY和LIMIT操作。
6.5.3 索引合并优化
前文
6.5.4 等值传递
某些时候等值传递会带来一些意想不到的额外消耗。
例如有-个非常大的IN()列表,而MySQL优化器发现存在WHERE、ON或者USING的子句将这个列表的值和另-一个表的某个列相关联。
那么优化器会将IN()列表都复制应用到关联的各个表中。通常因为各个表新增了过滤条件优化器可以更高效地从存储引擎过滤记录。但是如果这个列表非常大则会导致优化和执行都会变慢。至书出版时间除了修改MySQL源代码目前还没有什么.办法能够绕过该问题(不过这个问题很少会碰到)。
6.5.5 并行执行
MySQL无法利用多核特性来并行执行查询。
很多其他的关系型数据库能够提供这个特性但是MySQL做不到。
6.5.6 哈希关联
至书出版时间MySQL并不支持哈希关联——MySQL 的所有关联都是嵌套循环关联。
不过可以通过建立一个哈希索引来曲线地实现哈希关联。
如果使用的是Memory存储引擎则索引都是哈希索引所以关联的时候也类似于哈希关联。
MariaDB 已经实现了真正的哈希关联。
6.5.7松散索引扫描
由于历史原因MySQL并不支持松散索引扫描也就无法按照不连续的方式扫描一个索引。
通常MySQL的索引扫描需要先定义一个起点和终点即使需要的数据只是这段索引中很少数的几个MySQL仍需要扫描这段索引中每一个条目。
假设我们有如下索引(a, b)有下面的查询:
mysql SELECT ... FROM tbl WHERE b BETWEEN 2 AND 3;
索引的前导字段是列a在查询中只指定了字段b, 于是MySQL无法使用这个索引从而只能通过全表扫描找到匹配的行如图6-5所示。 松散的查询则是可以通过查询所有a1下的b然后再查询所有a2下的b但是MySQL不支持。
MySQL 5.0之后的版本在某些特殊的场景下是可以使用松散索引扫描的例如在一个分组查询中需要找到分组的最大值和最小值。
6.5.8 最大值和最小值优化
对于MIN()和MAX()查询MySQL的优化做得并不好。
mysql SELECT MIN(actor_id) FROM sakila.actor WHERE first_name PENELOPE;
first_name 字段上并没有索引此时MySQL将会进行一次全表扫描。
如果MySQL能够进行主键扫描,那么理论上,当MySQL读到第一个满足条件的记录的时候,就是我们需要找的最小值了因为主键是严格按照actor_ id 字段的大小顺序排列的。
但是MySQL这时只会做全表扫描我们可以通过查看SHOW STATUS 的全表扫描计数器来验证这一点。一个曲线的优化办法是移除MIN()然后使用LIMIT来将查询重写如下:
mysql SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
- WHERE first_name PENELOPE LIMIT 1;
这个策略可以让MySQL扫描尽可能少的记录数。如果你是一个完美主义者可能会说这个SQL已经无法表达她的本意了。一般我们通过SQL告诉服务器我们需要什么数据,由服务器来决定如何最优地获取数据不过在这个案例中我们其实是告诉MySQL如何去获取我们需要的数据通过SQL并不能一眼就看出我们其实是想要一个最小值。确实如此有时候为了获得更高的性能我们不得不放弃一些原则。
6.5.9 在同一个表.上查询和更新
MySQL不允许对同一张表同时进行查询和更新。
这其实并不是优化器的限制如果清楚MySQL是如何执行查询的就可以避免这种情况。
下面是一个无法运行的SQL虽然这是一个符合标准的SQL语句。这个SQL语句尝试将两个表中相似行的数量记录到字段cnt中:
mysql UPDATE tbl AS outer_tbl
- SET cnt (
- SELECT count(*) FROM tbl AS inner_tbl
- WHERE inner_tbl.type outer_tbl.type
- );
ERROR 1093 (H000): You cant specify target table outer_tbl for update in FROM
clause
可以通过使用生成表的形式来绕过上面的限制因为MySQL只会把这个表当作一个临 时表来处理。
实际上这执行了两个查询:
一个是子查询中的SELECT语句一个是多表关联UPDATE,只是关联的表是一个临时表
子查询会在UPDATE语句打开表之前就完成所以下面的查询将会正常执行:
mysql UPDATE tbl
- INNER JOIN(
- SELECT type, count(*) AS cnt
- FROM tbl
- GROUP BY type
- ) AS der USING(type)
- SET tbl.cnt der.cnt;
6.6 查询优化器的提示
提示、引导优化器如何生成最终的SQL语句已达到预想的效果。
这里可以直接查看书中文字略。
MySQL中文文档-优化器提示MySQL英文文档-Optimizer Hints
6.7 优化特定的查询
6.7.1 优化COUNT()查询
COUNT()的作用
COUNT()是一个特殊的函数有两种非常不同的作用
可以统计某个列值的数量
在统计列值时要求列值是 非空的 (不统计NULL)。
如果在COUNT()的括号中指定了列或者列的表达式则统计的就是这个表达式有值的结果数(NOT NULL)。
可以统计行数
最简单的就是当我们使用COUNT(*)的时候这种情况下通配符*并不会像我们猜想的那样扩展成所有的列实际上它会忽略所有的列而直接统计所有的行数。
我们发现一个最常见的错误就是在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数最好使用COUNT(*)这样写意义清晰性能也会很好。
关于MyISAM的神话
一个容易产生的误解就是:
MyISAM的COUNT()函数总是非常快
不过这是有前提条件的,即只有没有任何WHERE条件的COUNT(*)才非常快,因为此时无须实际地去计算表的行数。
MySQL可以利用存储引擎的特性直接获得这个值。如果MySQL知道某列coL不可能为NULL值那么MySQL内部会将COUNT(col)表达式优化为COUNT(*)
当统计带WHERE子句的结果集行数可以是统计某个列值的数量时MyISAM的COUNT()和其他存储引擎没有任何不同。
所以在MyISAM引擎表上执行COUNT( )有时候比别的引擎快有时候比别的引擎慢。
简单的优化
有时候可以使用MyISAM在COUNT(*)全表非常快的这个特性来加速一些特定条件的COUNT()的查询。
在下面的例子中我们使用标准数据库world来看看如何快速查找到所有ID大于5的城市。
可以像下面这样来写这个查询:
mysql SELECT COUNT(*) FROM world.City WHERE ID 5;
通过SHOW STATUS 的结果可以看到该查询需要扫描4097行数据。
如果将条件反转一下却可以将扫描的行数减少到5行以内:
mysql SELECT (SELECT COUNT(*) FROM world.City) - COUNT(*)
- FROM world.City WHERE ID 5; 在邮件组和IRC聊天频道中通常会看到这样的问题:如何在同一个查询中统计同一个列的不同值的数量以减少查询的语句量。
例如假设可能需要通过一个查询返回各种不同颜色的商品数量
不能使用OR语句(比如SELECT COUNT(color ‘blue’ OR color‘red’) FROM items;), 因为这样做就无法区分不同颜色的商品数量不能在WHERE条件中指定颜色( 比如SELECT COUNT(*) FROM ‘items WHERE color’ blue’ AND color’ RED’ ; )因为颜色的条件是互斥的。
下面的查询可以在一定程度上解决这个问题。
mysql SELECT SUM(IF(color blue, 1, 0)) AS blue,SUM(IF(color red, 1, 0))
- AS red FROM items;
也可以使用COUNT()而不是SUM()实现同样的目的只需要将满足条件设置为真不满 足条件设置为NULL即可:
mysql SELECT COUNT(color blue OR NULL) AS blue, COUNT(color red OR NULL)
- AS red FROM items;
使用近似值
有时候某些业务场景并不要求完全精确的COUNT值此时可以用近似值来代替。
EXPLAIN出来的优化器估算的行数就是一个不错的近似值执行EXPLAIN并不需要真正地去执行查询所以成本很低。
更复杂的优化
通常来说COUNT()都需要扫描大量的行(意味着要访问大量数据)才能获得精确的结果因此是很难优化的。
除了前面的方法在MySQL层面还能做的就只有索引覆盖扫描了。
如果这还不够,就需要考虑修改应用的架构,可以增加汇总表,或者增加类似Memcached这样的外部缓存系统。
“快速精确和实现简单”三者永远只能满足其二必须舍掉其中一个。
6.7.2 优化关联查询
确保ON或者USING子句中的列上有索引确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列当升级MySQL的时候需要注意:关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡儿积不同类型的关联可能会生成不同的结果等
6.7.3 优化子查询
关于子查询优化书中因为但是得版本给出的最重要的优化建议就是尽可能使用关联查询代替。
实际如何,需要自己跑一遍
6.7.4 优化GROUP BY和DISTINCT
在很多场景下MySQL都使用同样的办法优化这两种查询事实上MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化这也是最有效的优化办法。
在MySQL中当无法使用索引的时候GROUP BY 使用两种策略来完成:
使用临时表文件排序来做分组
对于任何查询语句这两种策略的性能都有可以提升的地方。可以通过使用提示SQL_BIG_RESULT 和SQL_SMALL_RESULT 来让优化器按照你希望的方式运行。 如果需要对关联查询做分组(GROUP BY), 并且是按照查找表中的某个列进行分组那么通常采用查找表的标识列分组的效率会比其他列更高。
但显然不是所有的关联语句的分组查询都可以改写成在SELECT中直接使用非分组列的形式 的。
甚至可能会在服务器上设置SQL_MODE来禁止这样的写法。
如果是这样也可以通过MIN()或者MAX()函数来绕过这种限制但一定要清楚SELECT 后面出现的非分组列一定是直接依赖分组列并且在每个组内的值是唯一的或者是业务上根本不在乎这个值 具体是什么:
mysql SELECT MIN(actor.first_ name), MAX(actor.last_name), ...;
较真的人可能会说这样写的分组查询是有问题的确实如此。从MIN()或者MAX()函数的用法就可以看出这个查询是有问题的。但若更在乎的是MySQL运行查询的效率时这样做也无可厚非。如果实在较真的话也可以改写成下面的形式:
mysql SELECT actor.first_name, actor.last_name, c.cnt
- FROM sakila.actor
-INNER JOIN (
- SELECT actor_id, COUNT(*) AS cnt
- FROM sakila.film_actor
- GROUP BY actor_id
-) AS C USING(actor_id);
这样写更满足关系理论但成本有点高因为子查询需要创建和填充临时表,而子查询中创建的临时表是没有任何索引的。
在分组查询的SELECT中直接使用非分组列通常都不是什么好主意因为这样的结果通常是不定的当索引改变或者优化器选择不同的优化策略时都可能导致结果不一样。
大多数这种查询最后都导致了故障(因为MySQL不会对这类查询返回错误)而且这种写法大部分是由于偷懒而不是为优化而故意这么设计的。
事实上我们建议将MySQL的SQL_MODE 设置为包含ONLY_FULL GROUP_BY,这时MySQL会对这类查询直接返回一个错误提醒你需要重写这个查询。 如果没有通过ORDER BY 子句显式地指定排序列当查询使用GROUP BY子句的时候结果集会自动按照分组的字段进行排序。
如果不关心结果集的顺序而这种默认排序又导致了需要文件排序则可以使用ORDER BY NULL 让MySQL不再进行文件排序。也可以在GROUPBY子句中直接使用DESC或者ASC关键字,使分组的结果集按需要的方向排序。
优化GROUP BY WITH ROLLUP
分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合。可以使用WITH ROLLUP 子句来实现这种逻辑但可能会不够优化。可以通过EXPLAIN来观察其执行计划特别要注意分组是否是通过文件排序或者临时表实现的。然后再去掉WITH ROLLUP子句看执行计划是否相同。也可以通过本节前面介绍的优化器提示来固定执行计划。
很多时候如果可以在应用程序中做超级聚合是更好的虽然这需要返回给客户端更多的结果。也可以在FROM子句中嵌套使用子查询,或者是通过一个临时表存放中间数据然后和临时表执行UNION来得到最终结果。
最好的办法是尽可能的将WITH ROLLUP 功能转移到应用程序中处理。
6.7.5 优化LIMIT分页
在系统中需要进行分页操作的时候我们通常会使用LIMIT加上偏移量的办法实现同时加上合适的ORDER BY 子句。
如果有对应的索引通常效率会不错否则MySQL需要做大量的文件排序操作。
一个非常常见又令人头疼的问题就是在偏移量非常大的时候例如可能是LIMIT1000 , 20这样的查询这时MySQL需要查询10 020条记录然后只返回最后20条前面10000条记录都将被拋弃这样的代价非常高。
要优化这种查询要么是在页面中限制分页的数量要么是优化大偏移量的性能。
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会提升非常大。
考虑下面的查询:
mysql SELECT fi1m_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
如果这个表非常大那么这个查询最好改写成下面的样子:
mysql SELECT 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子句。
有时候也可以将LIMIT查询转换为已知位置的查询让MySQL通过范围扫描获得到对应的结果。例如如果在一个位置列上有索引并且预先计算出了边界值上面的查询就可以改写为:
mysql SELECT film_id, description FROM sakila.film
- WHERE position BETWEEN 50 AND 54 ORDER BY position;
6.7.6 优化SQL_CALC_FOUND_ROWS
分页的时候,另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS 提示(hint),这样就可以获得去掉LIMIT以后满足条件的行数因此可以作为分页的总数。
看起来MySQL做了一些非常“高深”的优化像是通过某种方法预测了总行数。但实际上,MySQL只有在扫描了所有满足条件行以后才会知道行数所以加上这个提示以后不管是否需要MySQL都会扫描所有满足条件的行然后再抛弃掉不需要的行而不是在满足LIMIT的行数后就终止扫描。所以该提示的代价可能非常高。
一个更好的设计是将具体的页数换成“下一页”按钮假设每页显示20条记录那么我们每次查询时都是用LIMIT返回21条记录并只显示20条如果第21条存在那么我们就显示“下一页”按钮否则就说明没有更多的数据也就无须显示“下一页按钮了。另一种做法是先获取并缓存较多的数据一例如 缓存1000 条一然后 每次分页都从这个缓存中获取。这样做可以让应用程序根据结果集的大小采取不同的策略如果结果集少于1 000就可以在页面上显示所有的分页链接因为数据都在缓存中所以这样做性能不会有问题。如果结果集大于1 000则可以在页面上设计一个额外的“找到的结果多于1000条”之类的按钮。
这两种策略都比每次生成全部结果集再抛弃掉不需要的数据的效率要高很多。
有时候也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值。
当需要精确结果的时候再单独使用COUNT(*)来满足需求这时如果能够使用索引覆盖扫描则通常也会比SQL_CALC_ FOUND_ROWS快得多。
6.7.7 优化UNION查询
MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT. ORDER BY等子句“下推”到UNION的各个子查询中以便优化器可以充分利用这些条件进行优化。
除非确实需要服务器消除重复的行否则就一定要使用UNION ALL 这一点很重要。如果没有ALL关键字MySQL会给临时表加上DISTINCT选项这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字MySQL仍然会使用临时表存储结果。事实上, MySQL总是将结果放入临时表,然后再读出再返回给客户端。
6.7.8静态查询分析
Percona Toolkit 中的pt-query advisor能够解析查询日志、分析查询模式然后给出所有可能存在潜在问题的查询并给出足够详细的建议。这像是给MySQL所有的查询做一次全面的健康检查。它能检测出许多常见的问题诸如我们前面介绍的内容。
6.7.9 使用用户自定义变量
用户自定义变量是一个容易被遗忘的MySQL特性但是如果能够用好发挥其潜力在某些场景可以写出非常高效的查询语句。
在查询中混合使用过程化和关系化逻辑的时候自定义变量可能会非常有用。
单纯的关系查询将所有的东西都当成无序的数据集合并且一次性操作它们。MySQL则采用了更加程序化的处理方式。MySQL的这种方式有 它的弱点但如果能熟练地掌握则会发现其强大之处而用户自定义变量也可以给这种方式带来很大的帮助。
用户自定义变量是-个用来存储内容的临时容器在连接MySQL的整个过程中都存在。
可以使用下面的SET和SELECT语句来定义它们 :
mysql SET one: 1;
mysql SET min_actor: (SELECT MIN(actor_id) FROM sakila.actor);
mysql SET last_week: CURRENT_DATE-INTERVAL 1 WEEK;
select、declare、set均可申明变量预赋值具体可以参考-SQL server数据库declare和set、用法技巧,赋值建议使用:而不是
然后可以在任何可以使用表达式的地方使用这些自定义变量:
mysql SELECT ... WHERE col last_week;
下我们不能使用用户自定义变量:
使用自定义变量的查询无法使用查询缓存。不能在使用常量或者标识符的地方使用自定义变量,例如表名、列名和LIMIT子句中。用户自定义变量的生命周期是在一个连接中有效所以不能用它们来做连接间的通信。如果使用连接池或者持久化连接自定义变量可能让看起来毫无关系的代码发生交互。在5.0之前的版本是大小写敏感的所以要注意代码在不同MySQL版本间的兼容性问题。不能显式地声明自定义变量的类型。确定未定义变量的具体类型的时机在不同MySQL版本中也可能不一样。如果你希望变量是整数类型,那么最好在初始化的时候就赋值为0,如果希望是浮点型则赋值为0.0如果希望是字符串则赋值为,用户自定义变量的类型在赋值的时候会改变。MySQL的用户自定义变量是一个动态类型。MySQL优化器在某些场景下可能会将这些变量优化掉这可能导致代码不按预想的方式运行。赋值的顺序和赋值的时间点并不总是固定的这依赖于优化器的决定。实际情况可能很让人困惑后面我们将看到这一点。赋值符号: 的优先级非常低所以需要注意赋值表达式应该使用明确的括号。使用未定义变量不会产生任何语法错误如果没有意识到这一点非常容易犯错。
优化排名语句
下面的例子展示了如何使用变量来实现一个类似“行号(row number)”的功能:
mysql SET rownum : 0;
mysql SELECT actor_ id, rownum : rownum 1 AS rownum
- FROM sakila.actor LIMIT 3;
在我们来看一个更复杂的用法。我们先编写一个查询获取演过最多电影的前10位演员然后根据他们的出演电影次数做一个排名如果出演的电影数量一样则排名相同。我们先编写一个查询返回每个演员参演电影的数量:
mysql SELECT actor_id, COUNT(*) as cnt
- FROM sakila.film_actor
- GROUP BY actor_id
- ORDER BY cnt DESC
- LIMIT 10; 现在我们再把排名加上去这里看到有四名演员都参演了35部电影所以他们的排名应该是相同的。
我们使用三个变量来实现:
一个用来记录当前的排名一个用来记录前一个演员的排名还有一个用来记录当前演员参演的电影数量
只有当前演员参演的电影的数量和前一个演员不同时排名才变化。 mysql SET curr_cnt: 0, prev_cnt: 0, rank: 0;
mysql SELECT actor_id,
- curr_cnt: COUNT(*) AS cnt,
- rank: IF(prev_cnt curr_cnt,rank 1, rank) AS rank,
- prev_cnt: curr_cnt AS dummy
- FROM sakila.film_actor
- GROUP BY actor_id
- ORDER BY cnt DESC
- LIMIT 10; 可以使用子查询生成子表
mysql SET curr_cnt: 0, prev_cnt: 0, rank: 0;
mysql SELECT actor_id,
- curr_cnt: cnt AS cnt,
- rank: IF(prev_cnt curr_cnt,rank 1, rank) AS rank,
- prev_cnt: curr_cnt AS dummy
- FROM (SELECT actor_id,COUNT(*) as cnt
- FROM sakila.film_actor
- GROUP BY actor_id
- ORDER BY cnt DESC
- LIMIT 10
- )AS der; 避免重复查询刚刚更新的数据
ySQL并不支持像PostgreSQL那样的UPDATE RETURNING 语法这个语法可以帮你在更新行的时候同时返回该行的信息。
在MySQL中你可以使用变量来解决这个问题。
例如我们的一个客户希望能够更高效地更新一条记录的时间戳同时希望查询当前记录中存放的时间戳是什么。
简单地可以用下面的代码来实现:
UPDATE t1 SET lastUpdated NOW() WHERE id 1;
SELECT lastUpdated FROM t1 WHERE id 1;
使用变量我们可以按如下方式重写查询:
UPDATE t1 SET lastUpdated NOW() WHERE id 1 AND now: NOW();
SELECT now;
上面看起来仍然需要两个查询需要两次网络来回但是这里的第二个查询无须访问任何数据表所以会快非常多。
统计更新和插入的数量
当使用了INSERT ON DUPLICATE KEY UPDATE的时候,如果想知道到底插入了多少行数据,到底有多少数据是因为冲突而改写成更新操作的?
现办法的本质如下:
INSERT INTO t1(c1, c2)
VALUES(4, 4), (2, 1), (3, 1)
ON DUPLICATE KEY UPDATE C1 VALUES(c1) (0*(x :x1 ) );
当每次由于冲突导致更新时对变量x自增一次。然后通过对这个表达式乘以0来让其不影响要更新的内容。
另外MySQL的协议会返回被更改的总行数所以不需要单独统计这个值。
确定取值的顺序
使用用户自定义变量的一个最常见的问题就是没有注意到在赋值和读取变量的时候可能是在查询的不同阶段。
例如在SELECT子句中进行赋值然后在WHERE子句中读取变量则可能变量取值并不如你所想。下面的查询看起来只返回一个结果但事实并非如此:
mysql SET rownum: 0;
mysql SELECT actor_id, rownum:rownum 1 AS cnt
-FROM sakila.actor
- WHERE rownum 1; 因为WHERE和SELECT是在查询执行的不同阶段被执行的。如果在查询中再加入ORDERBY的话结果可能会更不同
mysql SET rownum : 0;
mysql SELECT actor_id, rownum : rownum 1 AS cnt
- FROM sakila.actor
- WHERE rownum 1
- ORDER BY first name;
这是因为ORDERBY引入了文件排序而WHERE条件是在文件排序操作之前取值的所以这条查询会返回表中的全部记录。解决这个问题的办法是让变量的赋值和取值发生在执行查询的同一阶段:
mysq1 SET rownum : 0;
mysql SELECT actor.id, rownum AS rownum
- FROM sakila.actor
- WHERE (rownum : rownum 1) 1; 编写偷懒的UNION
假设需要编写一个UNION查询其第一个子查询作为分支条件先执行如果找到了匹配的行则跳过第二个分支。
在某些业务场景中确实会有这样的需求比如先在一个频繁访问的表中查找“ 热”数据找不到再去另外一个较少访问的表中查找“冷”数据。(区分热数据和冷数据是一个很好的提高缓存命中率的办法)。
下面的查询会在两个地方查找一个用户一个主用户表、 一个长时间不活跃的用户表不活跃用户表的目的是为了实现更高效的归档。
SELECT id FROM users WHERE id 123
UNION ALL
SELECT id FROM users_archived WHERE id 123;
上述的查询可以用用户变量来优化
SELECT GREATEST(found : -1, id) AS id, users AS which_tbl
FROM users WHERE id 1
UNION ALL
SELECT id, users_archived
FROM users_archived WHERE id 1 AND found IS NULL
UNION ALL
SELECT 1reset FROM DUAL WHERE ( found : NULL ) IS NOT NULL;其余案例略
附录
《高性能MySQL》 Baron Schwartz, Peter Zaitsev, Vadim Tkachenko 著 宁海元 周振兴 彭立勋 翟卫祥 刘辉 译
