登录/注册
fuyao-w
23
0
0
浏览量
粉丝
关注
查询性能优化
fuyao-w
2020-11-26 15:34:53
24
0

慢查询基础:优化数据访问

查询性能低下的基本原因是访问的数据太多。某些查询可能不避免地需要筛选大量的数据,但这并不常见。大部分查询性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询,通过下面两个步骤来分析很有效:

  1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时候也可能访问了太多的列。
  2. 确认MySQL 服务器是否在分析大量超过需要的数据行。

是否向数据库请求了不需要的数据

有些查询的请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃。这会给MySQL 服务器带来额外的负担,并增加网络开销,另外也消耗服务器的 CPU 和内存资源。

案例:

  1. 查询不需要的记录:

    使用 SELECT 查询出大量的结果,但是只使用少数。解决办法是在查询后面加上 LIMIT

  2. 多表关联时返回全部列:

  3. 总是取出全部列

    如果使用了缓存机制时取出全部的列比单独取出的列更有好处,正常情况下只需要取出需要的列。

  4. 重复查询相同的数据

    可以将常用的查询结果进行缓存,以避免重复查询已有的相同数据。

MySQL 是否在扫描额外的数据

对于MySQL 最简单的查询开销的三个指标为:

  • 响应时间
  • 扫描的行数
  • 返回的行数

没有那个指标能够完美地衡量查询的开销,但他们大致反映了 MySQL 在内部执行查询时需要访问多少数据,并可以大概推算出查询运行的时间。这三个指标都会记录到 MySQL 的慢日志中,所以检查慢日志记录是找出扫描行数过多的查询的好办法。

响应时间

响应时间是一个相对的值,它有两部分时间组成:服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待资源而没有真正执行查询的时间 ——可能是等I/O操作完成,也可能是等待行锁,等等。我们无法把响应时间细分到上面这些部分。一般最常见和最重要的等待是 I/O 和锁等待,但实际情况更加复杂。我们可以大概估计一下,期望的查询响应时间。

扫描的行数和返回的行数

分析查询时,查看该查询扫描的行数是非常有帮助的。 这在一定程度上能够说明该查询找到需要的数据的效率高不高。

理想情况下扫描的行数和返回的行数应该是相同的。但实际上这种情况很少,例如在做一个关联查询时,服务器必须要扫描多行才能生成结果集的一行。扫描的行数对返回的行数的比率通常很小,一般在 1:1 和10:1 之间,不过有时候这个值也可能非常大。

扫描的行数和返回类型

在评估查询开销的时候,需要考虑一下从表中找到某一行数据的成本。MySQL 有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果,也有一些访问方式可能无需扫描就能返回结果。

在 EXPLAIN 语句中的 type 列反映了访问类型。访问类型有很多种,从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列出的速度是从慢到快,扫描的行数也是从小到大。需要明白这些概念。

如果查询没有办法知道合适的访问类型,那么解决的最好办法通常就是增加一个合适的索引。

在 EXPLAIN 查询时 Extra 表示附加信息,其中 using where 表示MySQL 将通过 WHERE 条件来筛选出存储引擎返回的记录。

一般MySQL 能够使用如下三种方式应用 WHERE 条件,从好到坏依次为:

  • 在索引中使用 WHERE 条件来过滤不匹配的记录。这是在存储引擎层完成的。
  • 使用索引覆盖扫描(在 Extra 列中出现了 Using index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在 MySQL 服务器层完成的,但无需在回表查询记录。
  • 从数据表中返回数据,然后过滤不满足条件的记录(Extra列中出现 Using Where)。这在MySQL 服务器层完成,MySQL 需要先从数据表读出记录然后过滤。

如果发现索引需要扫描大量的数据但只返回少数的行,那么通常尝试下面的技巧去优化它:

  • 使用索引覆盖扫描,把所有需要用的列都放到索引中,这样存储引擎可以无须回表获取行就可以返回结果了。
  • 改变库表的结构,例如使用单独的汇总表。
  • 重写这个复杂的查询。

重构查询的方式

在优化有问题的查询的时候,目标是找到一个更优的办法找到实际需要的结果——而不是一定总是要获取到一模一样的结果集。可以通过重构来提高查询的性能。

一个复杂查询还是多个简单查询

设计查询的时候一个需要考虑的问题是,是否需要将一个复杂的查询分成多个简单的查询。在传统实现中,总是强调需要数据库层完成尽可能多的工作,这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。

但是这样的想法对于MySQL 并不适用,MySQL 从设计上让连接和断开都很轻量级,在返回一个小的查询结果方面很高效。而且现代的网络速度比以前要快很多,无论是带宽还是延迟。

MySQL 内部每秒能够扫描内存中上百万行数据,相比之下,MySQL 响应给客户端就慢得多了。在其他条件都相同的时候,使用极可能少的查询当然是更好的。但是有时候,将一个大查询分解为多个小查询是很有必要的。

切分查询

有时候对于一个大查询我们需要“分而治之”,将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果。

删除旧的数据就是很好的例子。定期地清除大量数据时,如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据、占满整个事物日制、耗尽系统资源、阻塞很多小的但很重要的查询。将一个大的 DELETE 语句切分成多个叫较小的查询可以尽可能小地影响MySQL 性能,同时还可以减少MySQL 复制的延迟。

分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地,可以对每一个表进行一次单表查询,然后将结果在应用程序中进行关联。

使用分解关联查询的方式重构查询有如下优势:

  • 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。如果将关联查询拆分之后,某个表很少改变,那么基于该表的查询就可以重复利用查询缓存结果了。
  • 将查询分解后,执行单个查询可以减少锁的竞争。
  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做做到高性能和扩展。
  • 查询本身效率可能会有所提升。
  • 可以减少冗余记录的查询。在应用层做关联查询,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看,这样的重构还可能会较少网络和内存的消耗。
  • 更进一步,这样做相当于在应用中实现了哈希关联,而不是使用MySQL 的嵌套循环关联,某些场景哈希关联的效率要高很多。

在很多场景下,通过重构查询将关联放到应用程序中将会更加高效,这样的场景有很多,比如:当应用能够方便地缓存单个查询的结果的时候,当可以将数据分布到不同的MySQL服务器上的时候。当能够使用 in函数的方式代替关联查询的时候、当查询中使用同一个数据表的时候。

查询执行的基础

当我们向MySQL 发送一个请求的时候,发生了什么:

  1. 客户端使用通信协议发送一条查询给服务器。
  2. 服务器会先检查缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。
  3. 服务器端进行SQL 解析、预处理,再由优化器生成对应的执行计划。
  4. MySQL 根据优化器生成的执行计划,调用存储引擎的API 来执行查询。
  5. 将结果返回给客户端。

MySQL 客户端/服务器通信协议

一般来说,不需要去理解MySQL 通信协议的内部实现细节,只需要大致理解通信协议是如何工作的。MySQL 客户端和服务器之间的通信是“半双工”的,这意味着,在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器发送数据。

这种协议让MySQL 通信简单快速,但也从很多地方限制了 MySQL,一个明显的限制是,这意味着没法进行流量控制。一旦一端开始发送消息,另一端要接收完整和消息才能响应它。

客户端用一个单独的数据包将查询传递给服务器。这也是为什么当查询的语句很长的时候,参数 max_allow_packet 就特别重要了。一旦客户端发送了请求,它能做的事情就是等待结果了。

相反的,一般服务器相应该用户的数据通常很多,由多个数据包组成。当服务器开始响应客户端请求时,客户端必须完整地结构整个返回结果,而不能简单地只取前面几条结果,然后让服务器停止发送数据。在这种情况下,客户端若接受完整地结果,然后取前面几条需要的结果,或者接受完几条结果后就“粗暴”地断开连接,都不是好主意。这也是在必要时候一定要在查询中加上LIMIT 限制的原因。

多数连接MySQL 的库函数都可以获取全部结果集并缓存到内存里,还可以逐行获取需要的数据。MySQL 通常需要等待所有的数据都已经发送给客户端才能释放这条查询所占用的资源,所以接收全部结果并缓存通常可以减少服务器的压力,让查询能够早点结束,早点释放相应的资源。

当使用多数连接MySQL 的库函数从 MySQL 获取数据时,其结果看起来都像是从MySQL 服务器获取数据,而实际上都是从这个库函数的缓存获取数据。多数情况先这没什么问题,但是如果需要返回一个很大的结果集的时候,这样做并不好,因为库函数会花费很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集,就能大大减少内存的消耗,这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是,对于服务器来说,需要查询完成后才能释放资源,所以在和客户端交互的整个过程中,服务器的资源收拾被整个查询所占用的。

查询状态

对于一个MySQL 连接,或者说一个线程,任何时刻都有一个状态,该状态表示了 MySQL 当前正在做什么。有很多中方式能查看当前的状态,最简单的是使用 SHOW FULL PROCESSLIST 命令(该命令返回结果集中的Command 列就表示当前的状态)。在一个查询的声明周期中,状态会变化很多次。

Sleep

​ 线程正在等待客户端发送新的请求。

Query

​ 线程正在执行查询或者正在将结果发送给客户端。

Locked

​ 在 MySQL 服务器层,该线程正在等待表锁。在存储引擎级别实现的锁,记录 InnoDB 的行 锁,并不会体现在线程状态中。对于MyISAM 来说这是一个比较典型的状态,但在其他没有行锁的引擎中也经常会出现。

Aanlyzing and statistics

​ 线程正在分析存储引擎的统计信息,并生成查询的执行计划。

Copying to tmp table [on disk]

​ 线程正在执行查询,并且将其结果集都复制到一个临时表中,这种状态一般要么是在做Group By 操作,要么是文件排序操作,或者是 UNION操作。如果这个状态后面还有“on disk”标记,那表示MySQL 正在讲一个内存临时表放到磁盘上。

Sorting result

​ 线程正在对结果集进行排序

Sending data

​ 这表示多种情况:线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。

查询缓存

在解析一个查询语句之前,如果查询缓存是打开的,那么MySQL 会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同,也不会匹配缓存结果,这种情况下缓存就会进入下一个阶段的处理。

如果当前的查询恰好命中了查询缓存,那么在返回查询结果之前 MySQL 会检查一次用户权限。这仍然是无需解析查询语句的,因为在查询缓存中已经存放了当前查询需要访问的信息,如果权限没有问题,MySQL 会跳过所有其他阶段,直接将缓存中的结果返回给客户端。这种情况下,不用生成执行计划,不会被执行。

查询优化处理

查询的声明周期的下一步是将一个SQL 转换成一个执行计划,MySQL 在依照这个执行计划和存储引擎进行交互。包括多个子阶段:解析SQL、预处理、优化 SQL执行计划。这个过程中任何错误(例如语法错误)都可能终止查询。这里不打算详细介绍 MySQL 内部实现,而只选择性地介绍其中几个独立的部分,在实际执行过程中,这几部分可能一起执行也可能单独执行。

语法解析器和预处理

首先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 可以使用一些等价变换来简化并规范表达式。他可以合并和减少一些比较,还可以溢出一些恒成立和恒不成立的判断。

优化 COUNT()、MIN()、和MAX()

​ 索引个列是否可以为空通常可以帮助MySQL优化这个类表达式。例如,要找到某一列的最小值,只需要查询 B-Tree 索引最左端的记录,MySQL 可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点,在B-Tree 索引中,优化器会将这个表达式作为一个常数对待。如果MySQL 使用了这种类型的优化,那么在 EXPLAIN 中就可以看到 “Select tables optimized away ”。从字面意思可以看出,它表示优化器已经从执行计划中移除了该表,并以一个常数取而代之。

类似的,没有任何 WHERE 条件的 COUNT(*) 查询通常也可以使用存储引擎提供的一些优化(例如,MyISAM 维护了一个变量来存放数据表的行数)。

预估并转化为常数表达式

​ 当MySQL 检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行优化处理。例如,一个用户自定义边变量在查询中没有发生变化时就可以转换为一个常数。数学表达式则是另一种典型的例子。

让人惊讶的是,在优化阶段,有时候甚至一个查询也能够转化为一个常数。一个例子是在索引列上执行 MIN() 函数。甚至是主键或者唯一建查找语句也可以转换为常数表达式。如果WHERE 子句中使用了该类索引的常数条件,MySQL 可以在查询开始阶段就先查找到这些值,这样优化器就能够知道并转换为常数表达式。

覆盖索引扫描

​ 当索引中的列包含所有查询中需要使用的列的时候,MySQL 就可以使用索引返回需要的数据,而无需查询对应的数据行。

子查询优化

​ MySQL 在某些情况下可以将子查询转换为一种效率更高的形式,从而减少多个查询对多次数据进行访问。

提前终止查询

在发现已经满足查询需求的时候, MySQL 总是能够立刻终止查询。一个典型的例子就是当使用了 LIMIT 子句的时候。除此之外,MySQL 还有几类情况也会提前终止查询,例如发现了一个不成立的条件,这时MySQL 可以立刻返回一个空结果。或者在用DISTINCT()、NOT EXIST() 或者 LEFT JOIN 类型的查询时,MySQL只要遍历到有数据不符合要求,也会立即停止查询。

等值传播

​ 如果两个列的值通过等式关联,那么MySQL 能够把其中一个列的 WHERE 条件传递到另一个列上。

列表 IN() 的比较

​ 在很多数据库系统中,IN() 完全等同于多个 OR 条件的子句,因为这两者是完全等价的。在MySQL 中这点是不成立的,MySQL 将 IN() 列表中的数据线进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件,这是一个O(log n)复杂度的操作,等价地转换成 OR 查询的复杂对为O(n),对于IN() 列表中有大量取值的时候,MySQL 的处理速度将会更快。

数据和索引统计信息

MySQL 架构由多个层次组成。在服务器层有查询优化器,却没有保存数据和索引的统计信息。统计信息由存储引擎实现,不同的存储引擎可能会存储不同的统计信息。统计信息由存储引擎实现,不同的存储引擎可能会存储不同的统计信息(也可以按照不同的格式存储统计信息)。某些引擎,例如 Archive 引擎,则根本就没有存储任何统计信息!

因为服务器层没有任何统计信息,所以MySQL 查询优化器在生成查询的执行计划时,需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息,包括:每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。

MySQL 如何执行关联查询

MySQL 中 “关联” 一词所包含的意义比一般意义上理解的要更广泛。总的来说。MySQL 认为任何一次查询都是一次”关联“——并不仅仅是一个查询需要到两个表匹配才叫关联,所以在MySQL 中,每一个查询,每一个片段(包括子查询,甚至基于单表的 SELECT)都可能是关联。

所以,理解 MySQL 如何执行关联查询至关重要。我们先来看一个 UNION 查询的例子。对于 UNION 查询,MySQL 先将一系列的单个查询结果放到一个临时表中,然后再重新读出临时表数据来完成UNION 查询。在MySQL 的概念中,每个查询都是一次关联,所以读取结果临时表也是一次关联。

当前 MySQL 关联执行的策略很简单:MySQL 对任何关联都执行嵌套循环关联操作,即 MySQL 先在一个表中循环读取出单条数据,然后在嵌套循环到下一个表中寻找匹配的行,依次下去,知道找到所有表中匹配的行为止。然后依据各个表匹配的行,返回查询中需要的各个列。MySQL 会尝试在最后一个关联表中找到所有匹配的行,如果最后一个关联表无法找到更多的行以后,MySQL 返回到上一层关联表,看是否能够找到更多的匹配记录,以此类推迭代执行。

按照这样的方式查找第一个表记录,在嵌套查询下一个关联表,然后回溯到上一个表,在MySQL 中是通过嵌套循环的方式实现。

执行计划

和很多其他关系数据库不同,MySQL并不会生成查询字节码来执行查询。MySQL 生成查询的一棵指令树,然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

关联查询优化器

MySQL 优化器最重要的一部分就是查询关联优化,它决定了多个表关联时的顺序。通常多表关联的时候,可以有多种不同的关联顺序来获得相同的执行效果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

排序优化

无论如何排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序或者尽可能避免对大量数据进行排序。

当不能使用索引生成排序结果的时候,MySQL 需要自己进行排序,如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,不过MySQL 将这个过程同一成为文件排序,即使完全是内存排序不需要任何磁盘文件时也是如此。

如果需要排序的数据量小于“排序缓冲区”,MySQL 使用内存进行快速排序操作。如果内存不够,那么MySQL 会先将数据分块,对每个独立的块使用”快速排序“进行排序,并将各个块的排序结果存放在磁盘上,然后将各个排好序的块进行合并,最后返回排序结果。

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 会分两种情况来处理这样的文件排序。如果 OREDER BY 子句中的所有列都来自关联的第一个表MySQL,那么 MySQL 在关联处理第一个表的时候就进行文件排序。如果是这样,那么在MySQL 的 EXPLAIN 结果中可以看到 Extra 字段可以看到 “ Using temporary;Using filesort ”。如果查询中有 LIMIT 的话,LIMIT 也会在排序之后是使用,所以即使需要返回较少的数据,临时表和需要排序打的数据量仍然会非常大。

MySQL 5.6 在这里做了很多重要的改进。当主需要返回部分排序结果的时候,列入使用了 LIMIT 子句,MySQL 不再对所有的结果进行排序,而是根据实际情况,选择抛弃不满足条件的结果,然后进行排序。

查询执行引擎

相对于查询优化阶段,查询执行阶段不是那么复杂:MySQL 只是简单地根据执行计划给出的指令逐步执行。再根据执行计划逐步执行的过程中,有大量的操作需要通过调用存储引擎的接口来完成,这些接口也就是我们成为“handler API” 的接口。查询中的每一个表由一个 handler 的实例表示。实际上,MySQL 在优化阶段就为每个表创建了一个handler 实例,优化器根据这些实例的接口可以获取表的相关信息,包括表的所有列名,索引统计信息等。

存储引擎接口有着非常丰富的功能,但是底层接口却只有几十个,这些接口像“搭积木” 一样能够完成查询的大部分操作。

返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。几十查询不需要返回结果给客户端,MySQL 仍然会返回这个查询的一些信息,如该查询影响到的行数。

如果查询可以被缓存,那么 MySQL 在这个阶段也会将结果放到查询缓存中。

MySQL 将结果集返回给客户端是一个增量,逐步返回的过程。例如当服务器处理完,开始生成第一条结果时,MySQL 就可以开始向客户端逐步返回结果集了。

这样处理有两个好处:服务器无须存储太多的结果,也就不会因为要返回太多结果而消耗太多内存。另外这样的处理也让MySQL 客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足 MySQL客户端/服务器通信协议的封包发送,在通过 TCP协议进行传输,在 TCP 传输的过程中,可能会对 MySQL 的封包进行缓存然后批量传输。

MySQL 查询优化器的局限性

MySQL 的万能 “嵌套查询” 并不是对每一种查询都是最优的。不过还好,MySQL 查询优化器只对少部分查询不使用,而我们往往可以通过改写查询让MySQL 高效地完成工作。还有一个好消息,MySQL 5.6 版本正式发布后,会消除很多 MySQL 原本的限制,让更多的查询能够以尽可能高的效率完成。

关联子查询

MySQL 的子查询实现的非常糟糕。最糟糕的一类查询时 WHERE 条件中包含 IN() 的子查询语句。例如,我们希望找到 Sakila 数据库中,演员 Penelope Guiness (它的 actor_id 为 1) 参演过得所有影片信息。很自然的我们户按照下面的方式用子查询实现:

SELECT * FROM saklia.film WHERE film_id IN( SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

一位 MySQL 对 IN() 列表中的选项有专门的优化策略,一般会认为 MySQL 会先执行子查询返回所有包含 actor_id 为 1 的 film_id 。但是,MySQL 并不会先执行子查询。MySQL 会将相关的外层表压倒子查询中,它认为这样可以更高效地查找到数据行。也就是说,MySQL 会将查询改写成下面的样子:

SELECT * FROM sakila.film WHERE EXISTS (SELECT * FROM sakila.film_actor WHERE actor_id =1 AND film_actor.film_id = film.film_id)

这时,子查询需要根据 film_id 来关联外部表的film,因为需要 film_id 字段,所以MySQL 认为无法先执行这个子查询。MySQL 会先对 film 表进行全表扫描,然后根据返回的 film_id 逐个执行子查询。如果数据量大,则查询效率会跟糟糕。我们可以重写这个查询进行优化。

SELECT film.* FROM sakila.film INNER JOIN sakila.film_actor USING(film_id)

WHERE actor_id = 1

另一个优化的办法是使用函数 GROUP_CONCAT() 在 IN() 中构造一个由逗号分隔的列表。有时候这比上面的使用关联改写更快。因为使用 IN() 加子查询,性能经常会非常糟,所以通常建议使用 EXISTS() 等效的改写查询来获取更好的效率。下面是另一种改写 IN() 加子查询的办法:

SELECT * FROM sakila.film WHERE EXISTS (SELECT * FROM sakila.film_actor WHERE actor_id =1 AND film_actor.film_id = film.film_id);

如何用好关联子查询

并不是所有关联子查询的性能都会很差。有时候重写的性能并不会比关联子查询好到哪里去。可以通过测试来具体判断应该使用什么样的查询方法。

UNION的限制

有时,MySQL 无法将限制条件从外层“下推”到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果希望 UNION 的各个子句能够根据 LIMIT 只取部分结果集,或者希望能够先排好序在合并结果集的话,就需要在 UNION 的各个子句中分别使用这些子句。例如,想将两个子查询结果联合起来,然后在取前20 条记录,那么 MySQL 会将讲个表都存放在同一个临时表中,然后在取出前 20 行记录:

(SELECT first_name,last_name FROM sakila.actor ORDER BY last_name) UNION ALL(SELECT first_name,last_name FROM sakila.customer ORDER BY last_name ) LIMIT 20

这条查询将会把 actor 中的 200 条记录和 customer 表中的 599 条记录存放在一个临时表中,然后在从临时表中取出前20条。可以通过在 UNION 的两个子查询中分别加上一个 LIMIT 20 来减少临时表中的数据:

(SELECT first_name,last_name FROM sakila.actor ORDER BY last_name LIMIT 20) UNION ALL(SELECT first_name,last_name FROM sakila.customer ORDER BY last_name LIMIT 20) LIMIT 20

现在中间的临时表只会包含40 条记录了,除了性能考虑之外,在这里还需要注意一点:从临时表中取出数据的顺序并不是一定的,所以如果想获得正确的顺序,还需要加上一个全局的 ODER BY 和 LIMIT 操作。

索引合并优化

前面已经讨论过,在5.0 和更新的版本中,当 WHERE 子句中包含多个复杂条件的时候, MySQL 能够访问单个表的多个索引合并和交叉过滤的方式来定位需要查找的行。

等值传递

某些时候,等值传递会带来一些意想不到的额外消耗。例如,有一个非常大的 IN() 列表,而 MySQL 优化器发现存在 WHERE、ON 或者 USING 的子句,将这个表的值和另一个表的的某个列相关联。

那么优化器会将 IN() 列表都复制应用到关联的各个表中。通常,因为各个表新增了过滤条件,优化器可以更高效地从存储引擎过滤记录。但是如果这个列表非常大,则会导致优化和执行都会变慢。

并行执行

MySQL 无法利用多核特性来执行并行查询。很多其他的关系型数据库能够提供这个特性,但是MySQL 做不到。

哈希关联

目前 MySQL 并不支持哈希关联—— MySQL 的所有关联都是嵌套循环关系。不过可以通过建立一个哈希索引来曲线地实现哈希关联。如果使用的是 Memory 存储引擎,则索引都是哈希索引,所以关联的时候也类似于哈希关联。另外 MariaDB 已经实现了真正的哈希关联。

松散索引扫描

由于历史原因,MySQL 并不支持松散索引扫描,也就无法按照不连续的方式扫描一个索引。通常,MySQL 的索引扫描需要先定义一个起点和终点,即使需要的数据只是这段索引中很少数的几个,MySQL 仍需要扫描这段索引中每一个条目。

5.0 之后的版本,在某系特殊的场景下是可以使用松散索引扫描的,例如,在一个分组中查询中需要找到分组的最大值和最小值:

EXPLAIN SELECET actor_id,MAX(film_id) FROM sakila.film_actor GROUP BY actor_id

在 EXPLAIN 中的 Extra 字段显示 “Using index for group-by”,表示这里将使用松散索引扫描。在MySQL 很好地支持松散索引扫描之前,一个简单的绕过问题的办法就是给前面的列加上可能的常数值。在 MySQL 5.6 之后的版本,关于松散索引扫描的一些限制将会通过“索引条件下推”的方式解决。

最大值和最小值优化

对于 MIN() 和 MAX() 查询,MySQL 的优化做的并不好。

SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = "PENELOPE"

因为在 first_name 字段上并没有索引,因此 MySQL 将会进行一次全表扫描。如果MySQL 能够进行主键扫描,那么理论上,当 MySQL 读到第一个满足条件的记录的时候,就是我们需要找的最小值了,因为主键是严格按照 actor_id 字段的大小顺序排列的。但是 MySQL 这时只会做全表扫描,我们可以通过查看 SHOW STATUS 的全表扫描计数器来验证这点。一个曲线的优化办法是移除 MIN() ,然后是使用 LIMIT 来将查询重写如下:

SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = "PENELOPE" LIMIT 1

这个策略可以让 MySQL 扫描尽可能少的记录数。

在同一个表上查询和更新

MySQL 不允许对同一张表同时进行查询个更新。这其实并不是优化器的限制,如果清除 MySQL 是如何执行查询的,就可以避免这种情况。下面是一个无法运行的 SQL,虽然这是一个符合标准的 SQL语句。这个 SQL 与尝试将两个表中相似的行的数量记录到字段 cnt 中:

UPDATE tb1 AS outer_tb1 SET cnt = (SELECT count(*) FROM tb1 AS inner_tb1 WHERE inner_tb1.type = outer_tb1.type);

可以通过使用生成表的形式来绕过上面的限制,因为 MySQL 只会把这个表当做一个临时表来处理。实际上,这执行了两个查询:一个是子查询中的 SELECT 语句,另一个是多表关联 UPDATE,只是关联的表是一个临时表。子查询会在 UPDATE 语句打开表之前就完成,所以下面的查询将会正常执行:

UPDATE tb1 INNER JOIN ( SELECT type,count(*) AS cnt FROM tb1 GROUP BY type) AS der USING(type) SET tb1.cnt = der.cnt

查询优化器的提示(hint)

如果对优化器选择的执行计划不满意,可以使用优化器提供的几个提示(hint) 来控制最终的执行计划。但是需要注这样会给维护带来额外的困难,在 MySQL 版本升级的时候,“优化器提示” 很可能会让新版本的优化策略失效

优化特定类型的查询

优化 COUNT() 查询

COUNT() 是一个特殊的函数,有两种非常不同的作用:它可以统计某个列值的数量,也可以统计行数。在统计列值时要求列值时非空的。如果在 COUNT() 的括号中指定了列或者列的表达式,则统计的就是这个表达式有值的结果数。另一个作用是统计结果集的行数。当 MySQL 确认括号内的表达式值不可能为空时,实际上就是统计行数。最简单的就是使用 COUNT(*) 的时候,这种情况下通配符 * 并不会像我们猜想的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数。

我们发现一个最常见的错误就是,在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数,最好使用 COUNT(*) ,这样写意义清晰,性能也会很好。

关于 MyISAM 的神话

一个容易产生的误解就是:MyISAM 的COUNT() 函数总是非常快,不过这是有前提条件的,即只有没有任何 WHERE 条件的 COUNT(*) 才非常快,因为此时无需实际地区计算表的行数。MySQL 可以利用存储引擎的特性直接获得这个值。如果MySQL 知道某列 col 不可能为 NULL 值,那么MySQL 内部会将COUNT(col) 表达式优化为 COUNT( *)。

当统计带 WHERE 子句的结果集行数,可以是统计某个列值的数量时,MyISAM 的COUNT() 和其他存储引擎没有任何不同。

简单的优化

有时候可以使用 MyISAM 在 COUNT(*) 全表非常快的特性,来加速一些特定条件的 COUNT() 的查询。在下面的例子中,我们使用标准数据库 world 来看看如何快速找到 ID 大于5的城市。可以向下面这样来写这个查询:

SELECT COUNT(*) FROM world.City WHERE ID > 5

通过SHOW STATUS 的结果可以看到该查询需要扫描40005行数据。如果将条件反转一下,先查找 ID 小于等于5的城市数,然后用总城市数一减就能得到同样的结果,确可以将扫描的行数减少到5行以内:

SELECT (SELECT COUNT(*) FROM world.City) - COUNT( *) FROM world.City WHERE ID <= 5

这样做可以大大减少需要扫描的行数,是因为在查询优化阶段会将其中的子查询直接当做一个常数来处理。

在邮件组和 IRC 聊天频道中,通常会看到这样的问题:如何在同一个查询中统计同一个列的不同值的数量,以减少查询的语句量。例如,查询返回各种不同颜色的商品数量,此时不能是使用OR 语句,因为这样做就无法区分不同颜色的商品数量;也不能在WHERE 条件中使用 AND 指定不同的颜色,因为颜色是互斥的。下面的查询可以在一定程度上解决这个问题。

SELECT SUM(IF(color = "blue",1,0)) AS blue,SUM(IF(color = "res",1,0)) AS red FROM items

也可以使用COUNT() 而不是SUM() 实现同样的目的,只需要将满足条件设置为真,不满足条件设置为NULL 即可:

SELECT COUNT(color = "blue" OR NULL) AS blue, COUNT(color = "red" OR NULL) AS red FROM ITEMS

使用近似值

有时候某些业务场景并不要求完全精确的 COUNT() 值,此时可以用近似值来代替。EXPLAIN 出来的优化器估算的行数就是一个不错的近似值,执行EXPLAIN 并不需要真正地去执行查询,所以成本很低。

很多时候,计算精确值的成本非常高,而计算近似值则非常简单。曾经有一个客户希望统计它的网站的当前活跃用户数是多少,这个活跃用户数保存在缓存中,过期时间为30分钟,所以每隔30分钟需要重新计算并放入缓存中。因此这个活跃用户数本身就不是精确值,所以使用近似值代替是可以接受的。另外如果要精确统计在线人数,通常 WHERE 条件会很复杂,一方面需要剔除当前非活跃用户,另一方面还要剔除系统中某些特定 ID 的“默认用户”,去掉这些约束条件对总数的影响很小,但却可能很好地提升该查询的性能。更进一步地优化则可以尝试删除 DISTINCT 这样的约束来避免文件排序,这样重写过的查询要比原来的精确统计的查询快很多,而返回的结果则几乎相同。

更复杂的优化

通常来说,COUNT() 都需要扫描大量的行才能获得精确的结果。除了前面的方法,在MySQL 层面还能做的就只有索引覆盖扫描了。如果这样还不够,就需要考虑修改应用的架构,可以增加汇总表。或者增加类似 Redis 这样的缓存系统。可能很快你就会发现陷入到一个熟悉的困境,“快速,精确和简单实现”,三者永远只能满足其二,必须舍弃掉一个。

优化关联查询

这个话题几乎一直在讨论,这里需要特别提到的是:

  • 确保 ON 或者 USING 子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A 和 表B用 c 关联的时候,如果优化器的关联顺序是 B,A ,那么就不需要再B 表的对应列上创建索引。没有用到的索引只会带来额外的负担。一般来说,除非有其他理由,否则只需要在关联顺序中的第二个表的相应列上创建索引。
  • 确保任何的 GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列,这样的 MySQL 才有可能使用索引来优化这个过程。
  • 当升级MySQL 的时候需要注意:关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡尔积,不同类型的关联可能会产生不同的结果。

优化子查询

关于子查询优化我们给出的最终要的优化建议就是尽可能使用关联查询来代替,知道当前的MySQL 版本需要这样。但并不绝对的,如果使用的是 M有SQL 5.6 或者更新的版本或者 MariaDB,那么就可以直接或略关于子查询的这些建议了。

优化GROUP BY 和 DISTINCT

在很多场景下,MySQL 都使用同样的办法优化这两种查询,事实上,MySQL 优化器在内部处理的时候互相转化这两类查询。他们都可以使用索引来优化,这也是最有效的优化办法。

在 MySQL 中,当无法使用索引的时候,GROUP BY 使用两种策略来完成:使用临时表或者文件排序来做分组。对于任何查询语句,这两种策略的性能都有可以提升的地方。可以通过使用提示 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 来让优化器按照你希望的方式运行。

如果需要对关联做分组(GROUP BY),并且是按照表中的某个列进行分组,那么通常采用查找表的表示列分组的效率会比其他列更高。

在分组查询的 SELECT 中直接使用非分组列通常都不是什么好主意,因为这样的结果通常是不定的,当索引改变,或者优化器选择不同的优化策略时都可能导致结果不一样。我们碰到的大多数这种查询最后都导致了故障(因为MySQL 不会对这类查询返回错误),而且这种写法大部分都是由于偷懒而不是为优化故意这么设计的。建议始终使用含义明确的语法。事实上,我们建议将MySQL 的SQL_MODE 设置为包含 ONLY_FULL_GROUP_BY,这时 MySQL会对这类查询直接返回一个错误,提醒你需要重写这个查询。

如果没有通过 ORDER BY 子句显示地执行排序列,当查询使用 GROUP BY 子句的时候,结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序,而这种默认排序有导致了需要文件排序,则可以使用 ORDER BY NULL,让MySQL 不在进行文件排序。也可以在 GROUP BY 子句中直接使用 DESC 或者 ASC 关键字,是分组的结果集按照需要的方向排序。

优化 GROUP BY WITH ROLLUP

分组查询的一个变种就是要求MySQL 对返回的分组结果在做一次超级聚合。可以使用 WITH ROLLUP 子句来实现这种逻辑,但可能会不够优化。可以通过 EXPLAIN 来观察其执行计划,特别要注意分组是否是通过文件排序或者临时表实现的。然后再去掉 WITH ROLLUP 子句看执行计划是否相同。也可以使用优化器提示开固定执行计划。

很多时候,如果可以,在应用程序中做超级聚合是更好的,虽然这需要返回给客户端更多的结果。也可以在 FROM 子句中嵌套使用子查询,或者是通过一个临时表存放中间数据,然后和临时表执行 UNION 来得到最终结果。

最好的办法是尽可能的将 WITH ROLLUP 功能转移到应用程序中处理。

优化 LIMIT 分页

在系统中需要进行分页操作的时候,我们通常会使用LIMIT 加上偏移量的办法实现,同时加上格式的 ORDER BY 子句。如果有对应的索引,效率通常会不错,否则,MySQL 需要做大量的文件排序操作。

一个非常常见有令人头疼的问题就是,在偏移量非常大的时候,例如可能是 LIMIT 1000,20 这样的查询,这时 MySQL 需要查询 10020 条记录然后只返回最后20条,前面1000条记录会被抛弃,这样的代价非常高。如果所有的页面访问的频率都相同,那么这样的查询平均需要访问半个表的数据。要优化这种查询,要么是在页面中限制分页的数量,要么是优化大偏移量的性能。

SELECT film_id ,description FROM sakila.film ORDER BY title LIMIT 50,5

如果这个表非常大,那么这个查询最好改写正下面的样子:

SELECT film.film_id ,film.description FROM sakila.film INNER JOIN ( SELECT film_id FROM sakila.film OEDER BY title LIMIT 50,5) AS lim USING(film_id)

这里的“延迟关联”将大大提升查询效率,他让 MySQL 扫描尽可能少的页面,获取需要的访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的 LIMIT 子句。

有时候也可以将 LIMIT 查询转换为一直位置的查询,让 MySQL通过范围扫描获得到对应的结果。例如。如果在一个位置列上有索引,并且预先计算出了边界值,上面的查询就可以改写为:

SELECT film_id,description FROM sakila.film WHERE postion BETWEEN 50 AND 54 OEDER BY position

对数据进行排名的问题也与此类似,但往往还会同时和 GROUP BY 混合使用。在这种情况下通常都需要预先计算并存储排名信息。

LIMIT 和 OFFSET 的问题,其实是 OFFSET 的问题,它会导致 MySQL 扫描大量不需要的行然后在抛弃掉。如果可以使用书签记录上次取数据的位置,那么下次就可以直接从该书签记录的位置开始扫描,这样就可以避免使用OFFSET。例如,若需要按照租借记录做翻页,那么可以根据最新一条租借记录向后追溯,这种做法可行是因为租借记录的主键是单调增长的。首先是使用下面的查询获得第一组结果:

SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20

假设上面的查询返回的是主键为16049到16030 的租借记录,那么下一页查询就可以从16030这个点开始:

SELECT * FROM sakila.rental WHERE rentl_id < 10630 ORDER BY rental_id DESC LIMIT 20

该技术的好处是无论翻页到多么后面,其性能都会很好。

其他优化办法还包括使用预先计算的汇总表,或者关联到一个冗余表,冗余表只包含主键列和需要做排序的数据列。还可以使用Sphinx 优化一些搜索操作。’

优化 SQL_CALC_FOUND_ROWS

分页的时候,另一个常用的技巧是在LIMIT 语句中加上 SQL_CALC_FOUND_ROWS 提示(hint),这样就可以获得去掉 LIMIT 以后满足条件的行数,因此可以作为分页的总数。再用 SELECT FOUND_ROWS(); 获取到总记录数。MySQL 做了一些非常“高深的优化”,像是通过某种方法预测了总行数,但实际上,MySQL 只有在扫描了所有满足条件的行以后,才会知道行数,所以加上这个提示以后,不管是否需要,MySQL都会扫描所有满足条件的行,然后再抛弃掉不需要的行,而不是在满足 LIMIT 的行数后就终止扫描,所以该提示的代价可能非常高。

一个更好的设计是将具体的也换成“下一页”按钮。另一种做法是获取并缓存。有时候也可以考虑使用EXPLAIN 的结果中的rows 列的值来作为结果集总数的近似值。当需要单独使用 COUNT(*) 来满足需求,这时候如果能够使用索引覆盖扫描通常也会比 SQL_CALC_FOUND_ROWS 快得多。

优化 UNION 查询

MySQL 总是通过创建并填充临时表的方式来执行 UNION查询。因此很多优化策略在UNION 查询中都没法很好地使用。经常需要手工地将 WHERE、LIMIT、ORDER BY 等子句“下推到” UNION 的各个子查询中,以便于优化器可以充分利用这些条件进行优化。

除非确实需要服务器消除重复的行,否则就一定使用 UNION ALL,这一点很重要。如果没有ALL关键字,MySQL 会给临时表加上 DISTINCE 选项,这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL 关键字,MySQL 仍然会使用临时表存储结果。事实上,MySQL 总是将结果放入临时表,然后再读出,再返回给客户端,虽然很多时候这样做没有必要。

静态查询分析

Percona Toolkit 中的 pt-query-advisor 能够解析查询日志、分析查询模式,然后给出所有可能存在潜在问题的查询,并给出足够详细的建议。这像是给 MySQL 所有的查询做一次全面的健康检查。

使用用户定义自变量

在查询中混合使用过程化和关系化逻辑的时候,自定义变量可能会非常有用。单纯的关系查询将所有的东西都当成无序的数据集合,并且一次性操作他们。MySQL 则采用了更加程序化的处理方式。MySQL 的这种方式有他的弱点,但如果能熟练地掌握,则会发现其强大之处。

可以使用下面的SET 和 SELECT 语句来定义他们:

SET @last_week := CURRENT_DATE - INTERVAL 1 WEEK

SELECT ... WHERE COL <= @last_week

在了解自定义变量的强大之前,我们再看看他自身的一些属性和限制,看看在哪些场景下我们不能使用用户自定义变量:

  • 使用自定义变量的查询,无法使用查询缓存
  • 不能在使用常量或者标识符的地方使用自定义变量,例如,表名,列名和LIMIT子句中。
  • 用户自定义变量的声明周期是在一个连接中有效,所以不能用他们来做连接件的通信。
  • 如果使用连接池或者持久化连接,自定义变量可能让看起来毫无关系的代码发生交互(如果是这样,通常是代码 bug 或者连接池 bug ,这类情况确实可能发生)。
  • 在5.0 版本之前是大小写敏感的。注意兼容问题
  • 不能显示地声明自定义变量的类型。确定未定义变量的具体类型的时机在不同MySQL 版本中也可能不一样。如果希望变量是整数类型,那么最好在初始化的时候就赋值为0。
  • MySQL 优化器在某些场景下可能会将这些变量优化掉,这可能导致代码不按预想的方式运行。
  • 赋值的顺序和赋值的时间并不总是固定的,这依赖于优化器的决定。实际情况可能会让人困惑。
  • 赋值符号 := 的优先级非常低,所以需要注意,赋值表达式应该使用明确的括号。
  • 使用未定义变量不会产生任何语法错误,如果没有意识到这一点,非常容易犯错。

优化排名语句

可以使用自定义变量记录演过不同数量的电影的演员的排名,其中数量相同的演员排名相同。普通情况下需要两次计算。而使用自定义变量:

SET @ curr_cnt := 0 ,@prev_cnt :=0 ,@rank := 0;

SELECT actor_id,@ curr_cnt := COUNT(*) AS cnt ,

@rank := IF(@prev_cnt <> @curr_cnt,@rank+1,@rank) AS rank,

FROM (

SELECT actor_id,COUNT(*) AS cnt

FROM sakila.film_actor

Group BY actor_id

ORDER BY cut DESC

LIMIT 10)

注意如果不生成临时表可能不会正确得到结果。

避免重复查询刚刚更新的数据

如果在更新行的同时希望获得该行的信息,要怎么做才能避免重复的查询呢?不幸的是,MySQL 并不支持像PostgreSQL 那样的 UPDATE RETURNING 语法。这个语法可以在更新行同时返回该行信息。现在,假如我们希望能更高效地更新一条记录的时间戳,同时希望查询当前记录中存放的时间戳是什么。:

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),(1,2) ON DUPLICATE KEY UPDATE C1 = VALUES(C1) + (0 * @X := @X +1)

当每次由于冲突导致更新时对变量 x自增一次。然后通过对这个表达式 乘以 0来让其不要影响要更新的内容。另外 MySQL 直接返回被更改的总行数,不需要单独统计这个值。

确定取值的顺序

使用用户自定义变量的一个最常见的问题就是没有注意到在赋值和读取变量的时候可能是在查询的不同阶段。解决办法是将赋值语句放到 LAST() 函数中。

SET @row_num := 0

SELECT actor_id ,first_name,@rownum AS rownum

FROM sakila.actor

WHERE @rownum <= 1

ORDER BY first_name ,LAST(0,@rownum := @rownum +1)

这样可以在完全不改变排序顺序的时候完成赋值操作(ALST 总返回0)。这个技巧在不希望对子句的执行结果有影响又要完成变量赋值的时候很有用。在这个例子中,无需在返回值中新增额外列。

这样的函数含有 GREATEST(),LENGHT(),ISNULL(),IF()和COLESCE() 可以单独使用也可以组个使用。

编写偷懒地 UNION

假如我们在使用UNION的时候在第一个表找到后就停止查找:

SELECT GREATEST(@fond := -1,id) AS id,"users" AS which_tb1

FROM users WHERE id =1

UNION ALL

SELECT id, "user_archived"

FROM user_archived WHERE id =1 and @fond IS NULL

UNION ALL

SELECT 1,"reset" FROM DUAL WHERE (@fond:= NULL ) IS NOT NULL

一旦在第一个表中找到了记录我们就定义一个 @found 。通过在结果列中做一次赋值来实现,然后将赋值放在函数 GREATEST() 避免返回额外的数据。最后我们需要在查询的末尾将变量重置为NULL ,这样保证遍历时不干扰后面的结果。

其他用处

  • 检查运行时计算总数和平均值
  • 模拟 GROUP 语句中的函数 FIRST() 和 LAST()
  • 对大量数据做一些数据计算
  • 计算一个大表的 MD5 散列值
  • 编写一个样本处理函数,当样本中的数据超过某个边界值的时候将其变成0
  • 模拟读、写游标
  • 在SHOW 语句的WHERE 子句中加入变量值
暂无评论