高性能MySQL第三版 - 6-查询性能优化 - 《编程学习书籍》

6.2 慢查询基础：优化数据访问
- 6.2.1 是否向数据库请求了不需要的数据
  - 多表关联时返回全部列
- 6.2.2 MySQL是否在扫描额外的记录
  - 扫描的行数和访问类型
6.3 重构查询的方式
6.4 查询执行的基础
- 6.4.3 查询优化处理
  - 查询优化处理
6.5 MySQL查询优化器的局限性
6.8 案例
- 6.8.1 使用MySQL构建一个队列表

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

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

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

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

多表关联时返回全部列

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';

上述将返回三个表的全部数据。正确的方式应该是下面这样只取需要的列。

SELECT sakila.`actor`.* FROM sakila.`actor` ...

6.2.2 MySQL是否在扫描额外的记录

在确定查询返回了需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标如下：

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

扫描的行数和访问类型

在EXPLAIN语句中的type列反应了访问类型，访问类型有很多种，从全表扫描到索引扫描、范围扫描，唯一索引查询，常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是从小到大。
如果没有办法找打合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。索引是让MySQL以最高效，扫描行数最小的方式找到需要的记录。

SELECT *FROM sakila.film_actor WHERE film_id = 1;

explain显示MySQL预估需要访问10行数据。换句话说，查询优化器认为这种访问类型可以高效地完成查询。如果没有合适的索引。mysql不得不使用一种更糟糕的访问类型。例如删除索引后再次查询

ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;
ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;
EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G

MariaDB [(none)]> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5462
        Extra: Using where
1 row in set (0.000 sec)

变成了一个全表扫描(ALL)，这里的“Using Where”表示MySQL将通过WHERE条件筛选存储引擎返回的记录。
一般MySQL能够使用如下三种方式应用where条件，从好到坏依次为：

在索引中使用where条件来过滤不匹配的记录。这里是存储引擎层完成的。
使用索引覆盖扫描，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的。但无须在回表查询记录。
从数据表中返回数据，然后过滤不满足条件的记录。这在MySQL服务器层完成，但无须在回表查询。

如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它。

使用覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了。
改变库表结构。例如使用单独的汇总表。
重写这个复杂的查询，让MySQL优化器能够更优化的方式执行这个查询。

6.3 重构查询的方式

在优化问题的查询时，目标应该是找到一个更优的方法获得实际需要的结果——而不一定总是需要从MySQL获取一模一样的结果集。有时候，可以将查询转换一种写法让其返回一样的结果，但是性能更好。但是可以通过修改应用代码，用另一种方式完成查询，最终达到一样目的。

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

6.3.2 切分查询

 -- 间隔三个月
 SELECT  DATE_SUB(NOW(),INTERVAL 3 MONTH) ;

6.3.3 分解关联查询

6.4 查询执行的基础

当发送一个请求的时候，MySQL做了以下内容：

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

6.4.3 查询优化处理

查询优化处理

MySQL使用基于成本的优化器，它将预测一个查询使用哦目中执行计划是的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据页的成本，后来(成本计算公式)变得更加复杂。可以通过查询当前会话的Last_query_const 的值来得知MySQL计算成本的当前查询的成本。

SELECT SQL_NO_CACHE
    COUNT(*) 
FROM
    sakila.film_actor;
-- 
SHOW STATUS LIKE 'Last_query_cost' ;

6.5 MySQL查询优化器的局限性

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

6.5.1 关联子查询

MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE条件中包含in的子查询语句。

explain 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()列表查询速度很快，所以我们会认为上面的查询会这样执行。

很不幸，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我们可以看到子查询是一个相关子查询。

根据EXPLAIN的输出我们可以看到，MySQL先选择对file表进行全表扫描，然后根据返回的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);

MariaDB在原有的优化器基础上做了大量的改进，例如这里提到的IN()加子查询。

如何用好关联子查询

并不是所有的关联子查询的性能都很差。

EXPLAIN SELECT film_id, language_id FROM sakila.film
  WHERE NOT EXISTS(
  SELECT * FROM sakila.film_actor
  WHERE film_actor.film_id = film.film_id
  ) \G

一般会建议使用左外连接重写该查询，以代替子查询。

  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

6.5.2 UNION的限制

6.7.2 优化关联查询

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

6.7.3 优化子查询

“尽可能使用关联”并不是绝对的，如果使用的是MySQL5.6或更新的版本或者mariadb，那么可以直接忽略关于子查询的这些建议。

6.7.4 优化GROUP BY 和DISTINCT

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

如果需要对关联查询做分组(GROUP BY)，并且是按照查询表中的某个列进行分组，那么通常采用查询表的标识列分组的效率比其他列更高。例如，下面的查询效率不会更好。

 SELECT actor.first_name, actor.last_name, COUNT(*)
  FROM sakila.film_actor
  INNER JOIN sakila.actor USING(actor_id)
  GROUP BY actor.first_name, actor.last_name;

如果按照下面的写法效率会更高

SELECT actor.first_name, actor.last_name, COUNT(*)
  FROM sakila.film_actor
  INNER JOIN sakila.actor USING(actor_id)
  GROUP BY film_actor.actor_id ;

6.7.5 优化LIMIT分页

在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现。同时加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错。否则，MySQL需要做大量的文件排序操作。
一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是LIMIT 10000,20这样的查询，这是mysql需要查询10020条记录然后只返回最后20条，前面的10000条记录将被抛弃，这样的代价非常高。如果所有的页面被访问的频率相同，那么这样的查询平均需要访问半个数据库。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大量偏移量的性能。
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作在返回所需要的列，对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询

 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
  ORDER BY title LIMIT 50, 5
  ) AS lim USING(film_id);

这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录再根据关联列返回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。
有时候也可以将LIMIT查询转换成已知位置的查询，让MySQL通过范围扫描获得到对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写成为：

SELECT film_id, description FROM sakila.film
  WHERE position BETWEEN 50 AND 54 ORDER BY position ;

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

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

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

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

SELECT * FROM sakila.rental
  WHERE rental_id < 16030
  ORDER BY rental_id DESC LIMIT 20 ;

6-查询性能优化