MySQL中的关联（join）查询，总体来说，MySQL认为任何一个查询都是一次关联，不光是查询两个表匹配才叫关联。所以，理解MySQL如何执行关联查询至关重要。MySQL的关联是：嵌套循环关联，举例如下：
参考一个实际例子 《MySQL SQL优化之 STRAIGHT_JOIN》 全面介绍优化过程。
排序优化，不管怎么样，从性能角度，应该尽可能避免排序，或者尽可能避免对大量数据进行排序。第三章讲了索引排序，快速，当不能直接使用索引时，MySQL就会自己进行排序，数据量小时在内存中排序，数据量大时使用到磁盘。量小于“排序缓冲区”时，MySQL使用内存进行“快速排序”。如果内存不够，MySQL先将数据分块，每块使用快速排序，然后将各块结果放在硬盘上，然后合并（merge），最后返回排序结果。
注意：MySQL排序过程统称为文件排序（filesort），概念上的，即使排序发生在内存，而不是磁盘文件中。
mysql查询优化器的局限性
关联子查询
mysql的子查询实现的非常糟糕，最糟糕的一类查询是where条件中包含IN() 的子查询。
select * from film where film_id in (select film_id from film_actor where actor_id=1);
例如上面一个查询，本意是查询actor_id为1的演员演的电影，查询语句使用了in()。
因为mysql对in()列表中的选项有专门的优化策略，一般会认为mysql会先执行子查询返回所有包含actor_id为1的film_id。一般来说，in()列表查询速度很快，所以我们认为上面的查询会这样执行：
-- SELECT * FROM sakila.film-- 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 = 1
AND film_actor.film_id = film.film_id);
这时，子查询需要根据film_id来关联外部表film，因为需要film_id字段，所以mysql认为无法先执行这个子查询。通过explain我们可以看到子查询是一个相关子查询（dependent subquery）:
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+----------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+----------------------------------------------------------------------+
| 1 | SIMPLE | film | <null> | ALL | PRIMARY | <null> | <null> | <null> | 5 | 100.0 | <null> |
| 1 | SIMPLE | film_actor | <null> | ALL | <null> | <null> | <null> | <null> | 12 | 8.33 | Using where; FirstMatch(film); Using join buffer (Block Nested Loop) |
+----+-------------+------------+------------+------+---------------+--------+---------+--------+------+----------+----------------------------------------------------------------------+
mysql 会选择先对film表进行全表扫描，然后根据返回的film_id逐个进行子查询。
我们可以很容易的用内连接的方式改写这个查询：
SELECT f.*
FROM film f
INNER JOIN film_actor fa ON f.id=fa.film_id
WHERE fa.actor_id=1;
UNION的限制
有时，mysql无法将限制条件从外层下推到内存，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先排好序再合并结果，就需要在union的各个子句中分别使用这些子句。 例如，想将两个子查询结果联合起来，然后再取前20条记录，那么mysql会将两个表都存放到同一个临时表中，然后再取出前20条记录：
(select first_name, last_name from actor order by last_name)
union all
(select first_name, last_name from customer order by last_name)
limit 20;
这条查询将会把actor中的200条记录和customer表中的500条记录存放在一个临时表中，然后再从临时表中取出前20条。可以通过在union的两个子查询中分别加上一个limit20来减少临时表中的数据：
(select first_name, last_name from actor order by last_name limit 20)
union all
(select first_name, last_name from customer order by last_name limit 20)
limit 20;
现在临时表中只会包含40条记录了，除了性能考虑外，这里还需要注意的一点：从临时表中取出数据的顺序并不是一定的，所以如果想要获得正确的顺序，还需要加上一个全局的order by和limit操作。
最大值和最小值优化
select min(id) from actor where first_name='Penelope';
因为在first_name字段上并没有索引，因此mysql将会进行一次全表扫描。 如果mysql能够进行主键扫描，那么理论上，当mysql读到第一个满足条件的记录的时候，就是我们需要的最小值了，因为主键是严格按照id字段的大小顺序排列的。但是mysql这时只会在全表扫描，我们可以通过查看show status的全表扫描计数器来验证这一点。 一个曲线的优化办法是移除min()，然后使用limit来将查询重写如下：
select id from actor use index (primary) where first_name='Penelope' limit 1;
这个策略可以让mysql扫描尽可能少的记录数。
在同一个表上查询和更新
mysql不允许对同一张表同事进行查询和更新。下面就是一个无法运行的sql：
UPDATE tbl AS outer_tbl
SET cnt = (
SELECT count(*) FROM tbl AS inner_tbl
WHERE inner_tbl.type = outer_tbl.type
);
我们可以通过生成表的形式来绕过上面的限制，因为mysql只会把这个表当作一个临时表来处理。 这实际是执行了两个查询：一个是子查询中的select语句，另一个是多表关联update，只是关联的表是一个临时表。
UPDATE tbl
INNER JOIN(
SELECT type, count(*) AS cnt
FROM tbl
GROUP BY type
) AS der USING(type)
SET tbl.cnt = der.cnt;
优化特定类型的查询
优化count()查询
count()是一个特殊的函数，有两种非常不同的作用：它可以统计某个列的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）.
关于MyISAM的神话
一个容易产生的误解就是：MyISAM的count()函数总是非常快的，不过这时有前提条件的，即只有没有任何条件的count(*)从非常快，因为此时无需实际的去计算表的行数，可以利用引擎存储的值直接获取这个值。 当统计带有where子句的结果集行数，可以是统计某个列值的数量时，MyISAM的count()和其他存储引擎没有任何不同，就不再有神话般的速度了。
简单的优化
有时候可以使用MyISAM在count(*) 全表非常快的这个特性，来加速一些特定条件的count()查询。 例如，下面这个查询：
select count(*) from city where id>5;
通过show status的结果可以看到该查询需要扫描4097行数据，如果将条件反转一下，先查找id小于等于5的城市数，然后再用总城市数一减就能得到相同的结果，却可以将扫描的行数减少到5行以下：
select (select count(*) from city)-count(*) from city where id<=5
使用近似值
有时候某些业务场景并不要求完全精确的count值，此时可以用近似值来代替。explain出来的优化器估算的行数就是一个不错的近似值，执行explain并不需要真正的执行查询，所以成本很低。
优化关联查询
- 确保on或using子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。
- 确保任何的group by 和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程。
- 当升级mysql的时候需要注意：关联语法，运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会编程笛卡尔积，不同类型的关联可能会发生不同的结果等。
优化group by 和distinct
在很多场景下，mysql都会使用同样的办法优化这两种查询，事实上，mysql优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是优化的最有效的办法。
在mysql中，当无法使用索引的时候，group by使用两种策略来完成：使用临时表或者文件排序来分组。
如果需要对关联查询做分组（group by），并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。
优化limit分页
在偏移量非常大的时候，例如可能是limit 10000,20 这样的查询，这时mysql需要查询100020条记录，最后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。 要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。
优化此类分页查询的一个最简单的办法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回需要的列。 对于偏移量很大的时候，这样做的效率会提升非常大。
select id, description from film order by title limit 50, 5;
如果表非常大，那么这个查询最好改写成下面的样子：
select id,description from film inner join (select id from film order by title limit 50,5) as lim using (id);
这里的“延迟关联”将大大提升查询效率，它让mysql扫描尽可能少的页面，获取需要访问的记录后再根据关联回表查询需要的所有列。这个技术也可以用于优化关联查询中的limit子句。
有时候也可以将limit查询转换为已知位置的查询，让mysql通过范围扫描获取相应的结果。例如在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为：
select id, description from film where position bettween 50 and 54 order by position;
limit和offset的问题，其实是offset的问题，它会导致mysql扫描大量不需要的行然后抛弃掉。如果可以使用书签来记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描。这样就可以避免使用offset。
select * from rental order by id desc limit 20;
假设上面的查询返回的是主键为16049到16030的记录，那么下一页查询就可以从16030这个点开始：
select * from rental where id<16030 order by id desc limit 20;
该技术的好处是无论翻页到多么后面，其性能都会很好。
另一种做法是获取并缓存较多的数据，例如缓存1000条，然后每次分页都从缓存中获取。这样做可以让应用程序根据结果集的大小采取不同的策略，如果结果集少于1000，就可以在页面上显示所有的分页连接，因为数据都在缓存中，所以这样做性能不会有问题。如果记过集大于1000，则可以在页面上设计一个额外的“找到的结果多余1000条”之类的按钮。
有时候也可以考虑使用explain的结果中的rows列的值来作为结果集的总数的近似值。
优化union查询
mysql总是通过创建并填充临时表的方式来执行union查询。 除非确实需要服务器消除重复的行，否则就一定要使用union all，这一点很重要。如果没有all关键字，mysql会给临时表加上distinct选项，这会导致对正嗯临时表的数据做唯一性检查。 这样做的代价非常高。
使用用户自定义变量
用户自定义变量是一个容易被遗忘的mysql特性，如果能够用好，发回其潜力，在某些场景下可以写出非常高效的查询语句。 在查询中混合使用过程化和关系化逻辑的时候，自定义变量可能会非常有用。 用户自定义变量是一个用来存储内容的临时容器，在连接mysql的整个过程中都存在。
set @one :=1;
set @min_actor := (select min(actor_id) from actor);
set @last_week := current_date-interval 1 week;
然后在任何可以使用表达式的地方使用这些自定义变量：
select ... where col<=@last_week;
在了解自定义变量的强大之前，我们再看看它自身的一些属性和限制，看看在哪些场景下我们不能使用用户自定义变量：
- 使用自定义变量的查询，无法使用查询缓存
- 不能在使用变量或者标识符的地方使用自定义变量，比如表名，列名，和limit子句中
- 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信
- 如果使用连接池或者持久化连接，自定义变量可能让看起来毫无关系的代码发生交互
- 在5.0之前的版本，是大小写敏感的，所以要注意代码在不同mysql版本间的兼容性问题
- 不能显式的声明自定义变量的***类型。确定未定义变量的具体类型的时机在不同mysql版本中也可能不一样。如果你希望变量是整数类型，那么最好在初始化的时候就赋值为0，如果希望是浮点型，就赋值为0.0，如果希望是字符串就赋值为“”，用户自定义变量的类型在赋值的时候会改变，mysql的用户自定义变量是一个动态类型。
- mysql 优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按照预想的方式运行
- 赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定。
- 赋值符号:=的优先级非常低，所以需要注意，赋值表达式应该使用明确的括号。使用未定义变量了不会产生任何语法错误，如果没有意识到这一点，非常容易犯错。
避免重复查询刚刚更新的数据
如果在更新行的同时又希望获得该行的信息，要怎么做才能避免重复的查询呢？不幸的是，mysql并不支持像postgresql那样的update returning语法，这个语法可以帮你在更新行的同时返回该行的信息。 还好在mysql中你可以使用变量来解决这个问题。 例如，我们的一个客户希望能够高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么。 简单的，可以用下面的代码来实现：
update t1 set last_updated = now() where id=1;
select last_updated from t1 where id=1;
使用变量，我们可以按如下方式重写查询：
update t1 set loast_updated = now() where id=1 and @now := now();
select @now;
上面看起来仍然需要两个查询，需要两次网络来回，但是这里的第二个查询无需访问任何数据表，所以会非常快。
统计更新和插入的数量
当使用了 insert on duplicate key 的时候，如果想知道到底插入了多少行数据，到底有多少数据是因为冲突而改写成更新操作的？ Kerstian Kohntopp在他的博客上给出了一个解决这个问题的办法：
insert into t1 (c1, c2) values (4,4), (2, 1), (3, 1) on duplicate key update c1=values(c1) + (0 * (@x := @x+1))
当每次由于冲突导致更新时对变量@x自增一次。然后通过对这个表乘以0来让其不影响要更新的内容。
用户自定义变量的其他用处
通过一些实践，可以了解所有用户自定义变量能够做的有趣的事情，例如下面这些用法：
- 查询运行时计算总数和平均值
- 模拟group 语句中的函数 first()和last()
- 对大量数据做一些数据计算
- 计算一个大表的MD5散列值
- 编写一个样本处理函数，当样本中的数值超过某个边界值的时候将其变成0
- 模拟读/写游标
- 在show语句的where子句中加入变量值
