查询性能优化

2018-03-02  本文已影响0人  syca

MySQL查询优化器的局限性

关联子查询

MySQL的子查询实现的非常糟糕，最糟糕的一类查询是where条件中包含IN的子查询语句

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

MySQL会将查询改写成下面的样子：

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

UNION的限制

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

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

索引合并优化

5.0和更新的版本中，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的IN列表，MySQL优化器发现存在WHERE、ON或者USING的字句，将这个列表的值和另一个表的某个列相关联。
那么优化器会将IN列表的值都复制并应用到关联的各个表中。一般来说，增加了过来条件应该可以让优化器更高效地从存储引擎过滤记录，但是如果这个列表非常大，则会导致优化和执行都会变慢。

并行执行

MySQL无法利用多核特性来并行执行查询。

哈希关联

MySQL并不支持哈希关联——MySQL的所有关联都是嵌套循环关联。

松散索引扫描

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

select ... from tb1 where b between 2 and 3;

假设我们有索引(a,b)，因为索引的前导字段是列a，但是在查询中只指定了字段b，所以MySQL无法使用这个索引，从而只能全表扫描。但是如果了解索引的物理结构的话，就会发现其实是可以利用索引来加速这个查询的。

最大值和最小值优化

SELECT min(actor_id)
FROM sakila.actor
WHERE first_name = 'PENELOPE';

上面的查询会进行全表扫描，因为MySQL不会使用主键进行扫描

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

一般我们通过sql告诉服务器我们需要什么数据，由服务器来决定如何最优地获取数据，但是有时候我们会告诉MySQL如何去获取我们需要的数据，这时，通过sql也许并不能一眼就看出我们想要做什么，有时候为了更高的性能，我们不得不放弃一些原则。

在同一个表上查询和更新

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

可以使用生成表的形式绕过上面的限制，例如下面这种方式中MySQL会把INNER JOIN中的查询生成的表作为一个临时表来处理。

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）来控制最终的执行计划。

优化特定类型的查询

优化COUNT()查询

count()是一个特殊的函数，有两种非常不同的作用：

MyISAM的count()函数不是总是快的，除非查询中没有任何where条件，否则MyISAM的count()和其他存储引擎没有任何不同。

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

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

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

SELECT
  sum(if(color = 'blue', 1, 0)) AS blue,
  sum(if(color = 'red', 1, 0))  AS red
FROM items;

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

使用EXPLAIN出来的优化器估算的行数

优化关联查询

优化子查询

使用关联查询代替，但是MySQL 5.6以后不需要这样做。

优化GROUP BY和DISTINCT

很多场景下，MySQL都是用同样的办法优化这两种查询，事实上，MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是最有效的优化办法。
当无法使用索引时，GROUP BY使用临时表或者文件排序来做分组。对于任何查询语句，这两种策略的性能都有可以提升的地方，可以通过使用SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。

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;

不是所有的关联语句的分组查询都可以改写成在select中直接使用非分组列的形式的。甚至可能会在服务器上设置SQL_MOD（ONLY_FULL_GROUP_BY）E来禁止这样的写法。如果是这样，也可以通过MIN和MAX函数来绕过这种限制，但一定要清楚，select后面出现的非分组列一定是直接依赖分组列，并且在每个组内的值是唯一的。否则结果可能是不确定的。

GROUP BY默认会按照分组字段进行排序，如果不需要这个排序可以使用ORDER BY NULL。

优化LIMIT分页

在系统中需要进行分页操作时，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错。但是当偏移量非常大的时候，还是会有些问题，MySQL需要遍历非常多的数据。

优化此类分页查询的最简单办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。

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子句。

OFFSET会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从书签记录的位置开始扫描。

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

# 返回主键为16049到16030的记录

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

优化SQL_CALC_FOUND_ROWS

SQL_CALC_FOUND_ROWS会获取去掉LIMIT后满足的行数。可以用FOUND_ROW()函数获取这个值。

假设每页显示20条记录，那么每次查询21条记录但只显示20条，如果21条存在，那么就显示下一页按钮。

或者先获取并缓存较多的数据，比如1000条，然后每次分页都从这个缓存中获取。

优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中没法使用。经常需要我们自己把where、limit、order by等子句“下推”到UNION的各个子查询中。

另外，除非确实需要消除重复的行，否则一定要使用UNION ALL。如果没有加ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对临时表的数据做唯一性检查。

静态查询分析

Percona Toolkit中的pt-query-advisor

使用用户自定义变量

案例

使用MySQL构建一个队列表

使用消息的方式来通知各个消费者。具体的，可以使用一个带有注释的sleep()函数做超时处理，如下：

select /* waiting on unsent_emails */ sleep(10000);

当向对列表中新增一批数据后，可以通过show processlist根据注释找到当前正在休眠的线程，并将其KILL。你可以使用函数GET_LOCK()和RELEASE_LOCK()来实现通知，或者可以在数据库之外实现，例如使用一个消息服务。

之后就是如何让消费者标记正在处理的记录。

CREATE TABLE unsent_emails (
  id     INT          NOT NULL PRIMARY KEY AUTO_INCREMENT,
  status ENUM ('unsent', 'claimed', 'sent'),
  owner  INT UNSIGNED NOT NULL             DEFAULT 0,
  ts     TIMESTAMP,
  KEY (owner, status, ts)
);

BEGIN;
SELECT id
FROM unsent_emails
WHERE owner = 0 AND status = 'unsent'
LIMIT 10 FOR UPDATE;
-- result: 123, 456, 789
UPDATE unsent_emails
SET status = 'claimed', owner = connection_id()
WHERE id IN (123, 456, 789);
COMMIT;


SET AUTOCOMMIT = 1;
COMMIT;
UPDATE unsent_emails
SET status = 'claimed', owner = connection_id()
WHERE owner = 0 AND status = 'unsent'
LIMIT 10;
SET AUTOCOMMIT = 0;
SELECT id
FROM unsent_emails
WHERE owner = connection_id() AND status = 'claimed';


UPDATE unsent_emails
SET owner = 0, status = 'unsent'
WHERE owner NOT IN (0, 10, 20, 30) AND status = 'claimed'
      AND ts < current_timestamp - INTERVAL 10 MINUTE;

计算两点之间的距离

使用用户自定义的函数

上一篇下一篇

猜你喜欢

热点阅读