查询性能优化

表结构优化、索引优化、查询优化缺一不可，需要齐头并进。

嵌套循环算法

MySQL 的联表算法是基于嵌套循环算法（nested-loop algorithm）而衍生出来的一系列算法，根据不同条件而选用不同的算法

• 在使用索引关联的情况下，有 Index Nested-Loop join 和 Batched Key Access join 两种算法;
• 在未使用索引关联的情况下，有 Simple Nested-Loop join 和 Block Nested-Loop join 两种算法;

// Simple Nested-Loop
for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}


// Block Nested-Loop
for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

Simple Nested-Loop 最为简单粗暴，毫无性能可言，事实上不会使用这种算法。

Block Nested-Loop 缓存块嵌套循环连接，简称 BNL，是对 SNL 的一种优化。一次性缓存多条驱动表的数据到 Join Buffer，然后拿 Join Buffer 里的数据批量与内层循环读取的数据进行匹配。将内部循环中读取的每一行与缓冲区中的所有记录进行比较，这样就可以减少内层循环的读表次数。原来内层循环的读表次数是外层循环的行数 n，如今读表次数为 n/buffer_size。

Index Nested-Loop 索引嵌套循环，简称 INL，是基于被驱动表的索引进行连接的算法；驱动表的记录逐条与被驱动表的索引进行匹配，避免和被驱动表的每条记录进行比较。

Batched Key Access 是对INL的进一步优化，详见文档。

查询速度为什么会变慢

查询需要在不同的地方花费时间，包括网络，CPU 计算，生成统计信息和执行计划，锁等待等操作，尤其是底层存储引擎检索数据，这些检索需要内存操作、CPU 操作和内存不足时的 IO 操作。

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

分析步骤

1. 确认应用程序是否检索大量不必要的的数据，太多行或太多列。
2. 确认 MySQL 服务器层是否在分析大量不必要的数据行。

是否向数据库请求了多余的数据

• 查询不需要的记录
  使用 SELECT 查询所有结果，获取前面的 N 行后关闭，实际上 MySQL 已经查询出全部结果。需要多少查多少，用 LIMIT 进行限制。

• 取出全部列
  不要使用 SELECT * FROM a INNER JOIN b INNER JOIN C;这样的语句。很多时候取出全部列是不必要的。并且，取出全部列会让优化器无法完成索引覆盖这类优化，还会为服务器带来额外的 IO、内存和 CPU 的消耗。
  有时候出于开发效率，应用程序可以通过缓存进行复用等角度也可以查询这些多余的数据，需要进行权衡。

• 重复查询相同的数据
  利用缓存，减少重复查询。

MySQL 是否扫描额外的记录

衡量查询开销的三个指标：

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

响应时间 = 服务时间 + 排队时间。服务时间是数据库处理查询真正花的时间。排队时间是为了等待某些资源花费的时间——可能是 IO，也可能是锁等等。可以使用“快速上限估计法”估算查询响应时间：了解一个查询需要哪些索引以及它的执行计划，然后计算大概需要多少个顺序和随机 IO，乘以其在具体硬件条件下一次 IO 消耗的时间。

扫描的行数和返回的行数对查询速度的影响很大。但并不是所有的行的访问代价都是相同的，较短的行的访问速度快，内存中的行比磁盘中的行访问速度快。扫描行与返回行的比值通常很小，一般在 1:1 和 10:1 之间。

MySQL 中有好几种访问方式可以返回一行结果，有些要扫描很多行，有些甚至无需扫描行。
EXPLAIN 语句中的 type 列反应了访问类型。速度从慢到快，扫描行数从多到少分别是：

• 全表扫描
• 索引扫描
• 范围扫描
• 唯一索引查询
• 常数引用

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

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

如果发现查询需要扫描大量的数据，但只返回很少的行，可以尝试一下技巧：

• 使用索引覆盖扫描，把需要的列都放到索引中
• 改变表结构，如使用单独的汇总表
• 重写这个复杂的查询，让优化器能以更优化的方式执行。

重构查询的方式

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

传统的实现中强调数据库完成尽可能多的工作，其逻辑在于认为网络通信，查询解析和优化是一件代价很高的事情。但这对于 MySQL 不适用，MySQL 在设计上让连接和断开链接都很轻量，对小结果集友好。

在其他条件相同的时候，使用尽可能少的查询更有利。但有的时候，将一个大查询分解成为多个小查询是必要的。

切分查询

如定期删除数据时，用一个大的语句一次性完成需要锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

-- 大查询
DELETE FROM message WHERE created < DATA_SUB(NOW(), INTERBAL 3 MONTH);

-- 小查询 一次删除一万条
rows_affected = 0
do {
    rows_affected = do_query(
        "DELETE FROM message WHERE created < DATA_SUB(NOW(), INTERBAL 3 MONTH) 
        LIMIT 10000"
    ) 
} while rows_affected > 0

分解关联查询

分解关联查询将关联操作转移到应用程序中，优势如下：

• 让缓存的效率更高。很多应用程序会缓存单表查询的结果进行复用。另外，对于 MySQL 的查询缓存（query cacheA）来说，关联中的表发生了变化就无法使用缓存了，拆分后如果某个表很少改变，那就可以重复利用查询缓存。
• 单个查询减少锁的竞争。
• 在应用层关联更容易对数据库进行拆分，更容易提高性能和扩展性
• 拆分查询有时可以指定查询条件中的查询顺序（使用 IN()），提高效率
• 减少冗余记录的查询
• 拆分后相当于在应用中实现了哈希关联，而不是使用嵌套循环关联（MySQL 新版本中已经实现了哈希关联）

执行查询的基础

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

MySQL 客户端和服务器之间的通信协议是“半双工”的。发送和接受无法同时进行，无法也无须将一个消息切成小块独立发送。

这种协议让通信简单快速，也从很多地方限制了 MySQL。一个明显的限制是无法进行流量控制，一段发送的消息全部发完另一端才能响应。多数连接 MySQL 的库函数可以获取全部结果难道缓存中或逐行获取数据。默认一般是全部获取到内存中。MySQL 通常要等所有数据发送完成后才能释放资源，接收全部数据并缓存可以尽早释放相应的资源。对于很大的查询结果集，为了减少内存的消耗可以不缓存而直接处理，但相应的服务器被该资源长时间占用。

一个 MySQL 连接为一个线程，使用 SHOW FULL PROCESSLIST; 命令可以查询线程状态。

查询缓存 Query Cache

如果查询缓存是打开的，MySQL 会优先检查是否命中缓存中的数据。这个检查通过一个对大小写敏感的哈希查找实现，即使有一个字节不同（包括注释和空格）。

如果命中了查询缓存，会检查用户权限，通过则直接从缓存中获取结果。

优化查询处理

将一个 SQL 转换为一个执行计划包括几个子阶段：解析 SQL，预处理，优化 SQL 执行计划。

语法解析器对语句进行语法验证，生成解析树。

预处理器则根据一些 MySQL 规则进一步检查解析树是否合法，如检查数据表和列是否存在，名字和别名是否有歧义。然后进行权限验证。

优化器将检查合法的语法树转化为执行计划。MySQL 使用基于成本的优化器。最初，成本的最小单位是随机读取一个 4K 数据页的成本，后来成本计算公式变得更加复杂，并引入了一些因子来进行估算，如执行一次 WHERE 比较的成本。可以通过 SHOW STATUS LIKE 'Last_query_cost'; 得知上条语句的查询成本。

>  SELECT COUNT(*) FROM sakila.film_actor;
+----------+
| count(*) |
+----------+
|     5462 |
+----------+
1 row in set (0.00 sec)

> SHOW STATUS LIKE 'Last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1104.399000 |
+-----------------+-------------+
1 row in set (0.00 sec)

结果表示 MySQL 的优化器认为大概需要 1104 个数据页的随机查找才能完成上面的查询。这通过每个表或索引的页面个数、索引的基数、索引和数据行的长度、索引分布情况计算得出。

导致 MySQL 优化器选择错误执行计划的可能原因：

• 统计信息不准确。InnoDB 因其 MVCC 架构不能维护一个数据表的准确行数统计信息。
• 执行计划中的成本不等于实际成本。收到页面读取方式（顺序/随机）、页面存储位置（内存/磁盘），是否缓存等影响。
• 成本模型无法真实反应执行速度
• MySQL 没有考虑其它并发执行的查询的影响。
• 有时基于固定规则执行。如存在全文搜索 MATCH() 子句，则存在全文索引的时候就使用全文索引。
• 不考虑其他不受控制的操作的成本。如执行存储过程或用户自定义函数的成本。
• 无法估计可能的执行计划

MySQL 能处理的优化类型：

• 重新定义关联表的顺序
• 将外连接转化为内链接
• 使用等价变换规则。如将 (a<b AND b<c) AND a=5 改写为 b>5 AND b=c AND a=5
• 优化 COUNT(), MIN() 和 MAX()。有时，要找某一列的最小值需要查询 B 树索引的最左端（EXPLAIN 中看到 Select tables optimized away），类似的，最大值找索引的最后一个记录。MyISAM 维护了数据表行数的精确值，对于没有筛选条件的 COUNT(*) 查询可以直接使用。
• 预估并转化为常数表达式。检测到表达式可以转化为常数时，将其转化。对数学表达式，甚至一个查询（如带 MIN()，MAX()，主键或唯一键查找语句） 都能转化为常数。
• 覆盖索引扫描
• 子查询优化
• 提前终止查询。如 LIMIT 子句或条件不成立返回空结果。
• 等值传播。如果两个列的值通过等式关联，则把一个列的 WHERE 条件传递到另一个列上使得存储引擎可以更高效地过滤数据。
• 列表 IN() 的比较。MySQL 中 IN() 并非不等同于多个 OR 条件子句。MySQL 将 IN() 列表中的数据先进行排序，然后通过二分查找的方式确定列表中的值是否满足条件，IN() 列表中有大量取值时速度相较 OR 快很多。

嵌套循环关联：先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回需要的各个列。MySQL 会尝试在最有一个关联表中找到所有匹配的行，然后回溯到上一层关联表继续查找。
本质上，MySQL 对多有类型的查询都以这样的方式运行。FROM 子句遇到子查询是，先执行子查询并将结果放在一个临时表中，然后将临时表当作普通表一样对待。在 MySQL 中每一个查询，每一个片段（包括子查询，甚至单表 select）都可能是关联。

执行计划：MySQL 生成查询的一颗指令树，然后通过存储引擎完成这颗指令树并返回结果。最终的执行计划包含了查询的所有信息。在对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS就可以看到重构的查询，其与原查询语义相同，但查询语句可能不同。

MySQL 的执行计划是一颗左侧深度优先的树。

关联查询优化器通过评估多表关联时不同顺序的成本来选择代价最小的关联顺序。

-- 自动优化顺序
SELECT film.film_id, film.title, film.release_year, actor.actor_id, actor.last_name
FROM film
INNER JOIN film_actor USING(film_id)
INNER JOIN actor USING(actor_id)

+------+-------------+------------+--------+------------------------+---------------------+---------+---------------------------+------+-------------+
| id   | select_type | table      | type   | possible_keys          | key                 | key_len | ref                       | rows | Extra       |
+------+-------------+------------+--------+------------------------+---------------------+---------+---------------------------+------+-------------+
|    1 | SIMPLE      | actor      | index  | PRIMARY                | idx_actor_last_name | 137     | NULL                      |  200 | Using index |
|    1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id | PRIMARY             | 2       | sakila.actor.actor_id     |   13 | Using index |
|    1 | SIMPLE      | film       | eq_ref | PRIMARY                | PRIMARY             | 2       | sakila.film_actor.film_id |    1 |             |
+------+-------------+------------+--------+------------------------+---------------------+---------+---------------------------+------+-------------+

-- 固定顺序
EXPLAIN SELECT STRAIGHT_JOIN film.film_id, film.title, film.release_year, actor.actor_id, actor.last_name
FROM film
INNER JOIN film_actor USING(film_id)
INNER JOIN actor USING(actor_id)

+------+-------------+------------+--------+------------------------+----------------+---------+----------------------------+------+-------------+
| id   | select_type | table      | type   | possible_keys          | key            | key_len | ref                        | rows | Extra       |
+------+-------------+------------+--------+------------------------+----------------+---------+----------------------------+------+-------------+
|    1 | SIMPLE      | film       | ALL    | PRIMARY                | NULL           | NULL    | NULL                       | 1000 |             |
|    1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id | idx_fk_film_id | 2       | sakila.film.film_id        |    2 | Using index |
|    1 | SIMPLE      | actor      | eq_ref | PRIMARY                | PRIMARY        | 2       | sakila.film_actor.actor_id |    1 |             |
+------+-------------+------------+--------+------------------------+----------------+---------+----------------------------+------+-------------+

通过对比两种关联顺序，相比原顺序，优化后的顺序第一个关联表只需要扫描更少的行数，而第二和第三个关联表都是根据索引查询速度很快，因而优化后查询速度更快。通过 Last_query_cost 也可以看出来。

当表的数量过多，需要关联的表超过optimizer_search_depth时，使用 贪婪模式 进行搜索。

排序优化。从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据及逆行排序。

当不能使用索引生成排序结果的时候，MySQL 将利用内存或磁盘进行排序（文件排序 filesort）。

内存足够时，MySQL 使用内存进行“快速排序”。内存不够时，将数据分块 ，对每个独立的块使用“快速排序”，结果保存在磁盘上，将排好的块进行合并，返回排序结果。

MySQL 的两种排序算法：

• 两次传输排序：读取行指针和需要排序的行，完成排序后根据结果读取需要的数据行。第二次读取数据行会产生大量的随机 IO，成本高。
• 单次传输排序：读取查询所需的所有列，然后根据给定列进行排序，最后直接返回排序结果。此算法只需一次顺序 IO 读取所有数据。缺点是，如果需要返回的列非常多、非常大会占用大量的空间，造成浪费。单条排序记录很大所以可能有更多的排序块需要合并。

进行文件排序时，占用的临时空间可能很大。MySQL 为每行排序数据分配一个足以容纳其最长字符串的定长空间。

在关联查询中如果需要排序，分两种情况处理。

• ORDER BY 子句中的所有列来自关联的第一个表。MySQL 在关联第一个变的时候就进行文件排序，Extra 字段为 Using filesort。
• 除此之外。MySQL 先进行所有关联再进行文件排序。Extra 字段为 Using temporary; Using filesort。

LIMIT 子句在排序之后应用。MySQL 5.6 进行了改进，不再对所有结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后进行排序。

查询执行引擎

查询执行过程有大量操作需要通过调用存储引擎的接口（handler API）完成。查询中的每一个表由一个 handler 的实例表示。MySQL 在优化阶段就为每个表创建了一个 handler 实例。

并不是所有操作都由 handler 完成，如果是所有存储引擎共有的特性则由服务器层实现。

返回结果给客户端

MySQL 返回结果集是一个增量、逐步返回的过程。如在关联操作中，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL 就可以开始向客户端逐步返回结果集了。这样处理服务器无须存储太多结果，节省内存，另外也能让客户端第一时间获得结果。

结果集的每一行都会以一个满足 MySQL 通信协议的封包发送，再通过 TCP 协议传输，传输过程中可能对 MySQL 的封包进行缓存然后批量传输。

MySQL 查询优化器的局限性

• 关联子查询

MySQL 的IN(sub query)语句效率不高，可改写为关联查询。MariaDB 进行了一定的优化，可以跟踪一下。

使用子查询有时候也会比关联查询更加清晰、快速：

-- 关联查询，需要 DISTICT 剔除重复数据
-- 查询过程中会产生临时中间表，影响效率
SELECT DISTINCT film.film_id FROM film 
INNER JOIN film_actor USING(film_id)

-- 子查询
SELECT  film.film_id FROM film 
WHERE EXISTS(SELECT * from film_actor
WHERE film.film_id = film_actor.film_id)

• UNION 的限制

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

如当使用 UNION 和 LIMIT 语句，将两个结果集合并后取部分结果集，MySQL 会将两个表的所有结果都存放到临时表中，可以在两个 UNION 子查询中都添加 LIMIT 进行优化。

• 索引合并优化

当 WHERE 子句中包含多个复杂条件的时候，MySQL 能够访问单个表的多个索引以合并和交叉过滤的方式定位需要查找的行。、

• 等值传递

当有一个很大了 IN() 列表时，等值传播的特性会带来很大开销。

• 并行执行

MySQL 无法并行执行查询

• 哈希关联

MariaDB 支持 hash join。
MySQL 8.0.18 版本增加了对 hash join 的支持。

版本对照

MariaDB	MySQL
10.0-1	5.6
10.1-3	5.7
10.3-4	8.0

• 松散索引扫描

相当于 Oracle 中的跳跃索引扫描（skip index scan）

MySQL 只在某些特定场景下可以使用松散索引扫描，如在一个分组查询中找到最大（小）值

SELECT actor_id, MAX(film_id)
FROM film_actor
GROUP BY actor_id

有索引 (actor_id, film_id)，可以跳跃式的扫描每个 actor_id 段的最后一个记录，就是相应的 MAX(film_id)。

• 最大值和最小值优化

SELECT MIN(actor_id) FROM actor WHERE first_name = 'PENELOPE';

以上查询中，first_name字段没有索引，MySQL 会进行全表扫描。而事实上因为主键 actor_id 是升序排列的，因此在找到第一个满足first_name = 'PENELOPE'的记录时就可以结束查询。因此可以改写为：

SELECT actor_id FROM actor USE INDEX(PRIMARY)
WHERE first_name = 'PENELOPE' LIMIT 1;

• 在同一张表上查询和更新

MySQL 不允许对同一张表同时进行查询和更新。但是可以通过使用生成表的形式绕过限制，关联时不是关联自身，而是关联临时表。

UPDATE tb as outer_tb
    set cnt = (
        SELECT COUNT(*) FROM tb AS inner_tb
        WHERE inner_tb.type = outer_tb.type
    );

-- ERROR 1093 (HY000): Table 'outer_tb' is specified twice, both as a target for 'UPDATE' and as a separate source for data

UPDATE tb
    INNER JOIN(
        SELECT type, COUNT(*) AS cnt
        FROM tb
        GROUP BY type
    ) AS der USING(type)
SET tb.cnt = der.cnt;

• hint
  • HIGH_PRIORITY / LOW_PRIORITY
  • DELAYED
  • STRAIGHT_JOIN
  • SQL_SMALL_RESULT / SQL_BIG_RESULT：前者告诉优化器结果集会很小，可以将结果放在林村中的索引临时表，避免排序操作；后者说明结果集非常大，建议使用磁盘临时表进行排序操作。
  • SQL_BUFFER_RESULT
  • SQL_CACHE / SQL_NO_CACHE
  • SQL_CALC_FOUND_ROWS
  • FOR UPDATE / LOCK IN SHARE MODE
  • USE INDEX / IGNORE INDEX / FORCE INDEX

因版本而异，详见官方手册

优化特定类型的查询

优化 COUNT() 查询

在 COUNT() 的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。

对于没有任何 WHERE 条件的 COUNT(*)，MyISAM 可以直接返回结果。

可以利用 MyISAM 此特性对特定语句进行优化

-- 原查询
SELECT COUNT(*) FROM world.city where ID > 5;

-- 优化 只需要扫描前 5 条就行
SELECT (SELECT COUNT(*) FROM world.city) - COUNT(*) 
FROM world.city where ID <= 5;

一个查询中统计一个列不同值的数量

SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL) AS red FROM items;

在对精度要求不高的时候可以使用近似值代替

优化关联查询

• 确保 ON 或者 USING 子句中的列上有索引。创建索引的时候要考虑关联的顺序，当表 A 和表 B 通过列 c 关联的时候，如果优化器的关联顺序是 B、A，则只需要在 A.c（内层）上创建索引。
• 确保 GROUP BY 和 ORDER BY 中的表达式只涉及一个表中的列，这样 MySQL 才能使用索引进行优化。
• 升级时注意，关联语法、运算符优先级等可能发生变化。

优化 GROUP BY 和 DISTINCT

在 GROUP BY 子句中使用查找表的标识列分组的效率比其他列高。但当所需字段为非分组列时，部分服务器可能禁用这种写法（ORACLE 是不支持的），强行使用也可能带来问题。

如果没有 ORDER BY 子句显式地指定排序列，当使用 GROUP BY 子句时，结果集会自动按照分组的字段进行排列。如果不关心结果集的顺序，这种默认排序有导致了需要文件排序操作，则可以使用 ORDER BY NULL，让 MySQL 不进行文件排序。

可以直接在 GROUP BY 子句中使用 DESC 或 ASC 指定排序方向。

尽可能将 GROUP BY WITH ROLLUP 的 ROLLUP 部分放在应用程序中进行。

优化 LIMIT 分页

分页查询中，非常靠后的页偏移量很大，很多的记录被抛弃，代价很高。

优化此类分页查询的最简单办法就是尽可能地使用索引覆盖扫描，而不是所有的列，然后再进行一次关联操作返回需要的列。

对于部分情况（主键排序），可以记录上次查询中最后一条记录的 ID 作为下一次查询的条件，避免使用偏移量。如

SELECT * FROM rental
WHERE rental_id < 16030
ORDER BY rental_id DESC LIMIT 20。

优化 SQL_CALC_FOUND_ROWS

分页时可以在 LIMIT 语句中加上 SQL_CALC_FOUND_ROWS hint，可以获得所有满足条件的行数以便计算页数，但事实上它就是通过扫描所有行实现的，因此改提示的代价很高。

实践中可以不显示页数，只显示下一页，假设每页显示 20 条，那么就一次查询 21 条，如果 21 条存在则激活“下一页”按钮。另一种做法是一次缓存较多的数据。

优化 UNION 查询

WHERE、LIMIT、ORDER BY 等子句需要手动“下推”

除非必须消除重复行，否则使用 UNION ALL 避免唯一性检查

使用用户自定义变量

用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程中都存在。可以使用 SET 和 SELECT 语句来定义：

> SET @one      := 1;
> SET @min_actor:= (SELECT MIN(actor_id) FROM actor);
> SET @last_week:= CURRENT_DATE - INTERVAL 1 WEEK;

然后可以在任何使用表达式的地方使用这些自定义变量。

不能使用用户自定义变量的场景：

• 使用自定义变量的查询无法使用查询缓存。
• 不能在使用常量或标识符的地方使用自定义变量，如表名、列名和 LIMIT 子句中。
• 用户自定义变量的生命周期为一个连接，不可以跨连接通信。
• 如果使用连接池或者持久化连接，自定义变量可能让看起来毫无关系的代码发生交互。
• 不能显式声明变量类型，其为动态类型。
• 优化器在某些场景可能会将这些变量优化掉，导致意想不到的错误。
• 赋值表达式需要使用括号，:=的优先级很低。
• 使用未定义变量不会报语法错误

总之使用时要注意赋值的顺序和赋值的时间

应用场景：

• 优化排名语句

SET @curr_cnt := 0 , @prev_cnt := 0 , @rank := 0;
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;

• 更新的同时获取数据

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 := @x + 1))

• 确定取值的顺序

SET @rownum := 0;
-- where 语句 在 select 之前执行
SELECT actor_id, @rownum := @rownum + 1 AS cnt
FROM sakila.actor
WHERE @rownum <= 1;

--  Using where; Using index 
+----------+------+
| actor_id | cnt  |
+----------+------+
|       58 |    1 |
|       92 |    2 |
+----------+------+

SET @rownum := 0;
-- 让赋值和取值发生在查询的同一个阶段（子句）可结果以上问题
SELECT actor_id, @rownum AS cnt
FROM sakila.actor
WHERE (@rownum := @rownum + 1) <= 1;

-- Using where; Using index
+----------+------+
| actor_id | cnt  |
+----------+------+
|       58 |    1 |
+----------+------+

set @rownum:=0;
SELECT actor_id,first_name, 
    @rownum AS cnt FROM sakila.actor WHERE (@rownum := @rownum + 1) <= 1 
ORDER BY first_name;

-- Using where; Using filesort
+----------+------------+------+
| actor_id | first_name | cnt  |
+----------+------------+------+
|        1 | PENELOPE   |  200 |
+----------+------------+------+

-- 这个语句的执行顺序貌似是 where -> order by ->select
-- @rownum <= 2 会返回三条
-- 子句中表达式的执行可能和子句的执行顺序不一致？
SET @rownum := 0;
SELECT actor_id,first_name, 
    @rownum AS cnt FROM sakila.actor WHERE @rownum <= 1
ORDER BY first_name, least(0, @rownum := @rownum +1);

-- Using where; Using temporary; Using filesort
+----------+------------+------+
| actor_id | first_name | cnt  |
+----------+------------+------+
|        2 | NICK       |    2 |
|        1 | PENELOPE   |    1 |
+----------+------------+------+

order by 问题留待讨论

// 想法是，当没有 order by 子句时迭代式执行
for row in table{
    where()
    select()
}

// 当有 order by 语句时
for row in table{
    where()
}

for row in filtered{
    select()
}

order_by()

• lazy UNION

当user表找不到数据时，才查询 user_archived

SELECT GREATEST(@found := -1, id) AS id, 'user' AS which_tbl
FROM user WHERE id = 1
UNION ALL
    SELECT id, 'user_archived'
    FROM user_archived WHERE id = 1 AND @found IS NULL
UNION ALL
    SELECT 1, 'reset' FROM DUAL WHERE (@found := NULL) IS NOT NULL

总结

优化需要三管齐下：不做、少做、快速地做。