查询性能优化

作者: syca | 来源:发表于2018-03-02 13:45 被阅读0次

MySQL性能调优
MySQL性能优化(慢查询)
《高性能Mysql》-查询优化
Day2:MySQL慢查询基础-查询慢原因
mysql性能优化-慢查询分析、优化索引和配置
mysql调优
mysql性能优化-慢查询分析、优化索引和配置
MySQL数据库优化总结
性能优化-索引
查询性能优化

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

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()是一个特殊的函数，有两种非常不同的作用：

统计某个列值的数量，在统计列值时是不统计NULL值的
统计结果集的行数，即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出来的优化器估算的行数

优化关联查询

确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑关联的顺序。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
升级MySQL的时候要注意关联语法、运算符优先级等其他可能发生变化的地方，因为以前是普通关联的地方可能会变成笛卡儿积。

优化子查询

使用关联查询代替，但是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;

尽量少做事，可以的话就不要做任何事情。除非不得已，否则不要使用轮询，因为这会增加负载，而且还会带来很多低产出的工作。
尽可能快地完成需要做的事情。尽量用update代替先select for update再update的写法，因为事务提交的速度越快，持有的锁时间就越短，可以大大减少竞争和加速串行执行效率。将已经处理完成和未处理的数据分开，保证数据集足够小。
某些查询是无法优化的；考虑使用不同的查询或者不同的策略去实现相同的目的。通常对于select for update就需要这样处理。

计算两点之间的距离

使用用户自定义的函数

MySQL性能调优
MYSQL查询语句优化 mysql的性能优化包罗甚广：索引优化，查询优化，查询缓存，服务器设置优化，操作系统和硬...
MySQL性能优化(慢查询)
1 MySQL性能优化之慢查询 1.1 性能优化的思路首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语...
《高性能Mysql》-查询优化
优化性能需要查询优化、索引优化、库表结构优化这三辆马车齐头并进。这篇文章主要围绕查询优化，要对查询进行优化首先需要...
Day2:MySQL慢查询基础-查询慢原因
说在前面：查询优化、索引优化、库表结构优化是查询性能优化的三驾马车。完成一个完整的查询生命周期，查询需要在...
mysql性能优化-慢查询分析、优化索引和配置
一、优化概述二、查询与索引优化分析 1性能瓶颈定位 Show命令慢查询日志 explain分析查询 profi...
mysql调优
一、查询优化、索引优化、库表结构优化 1. 查询性能优化 1.1）优化数据访问：1、是否向数据库请求了不需要的...
mysql性能优化-慢查询分析、优化索引和配置
目录一、优化概述二、查询与索引优化分析 1性能瓶颈定位 Show命令慢查询日志 explain分析查询 pr...
MySQL数据库优化总结
《高性能MySQL》指导性能优化1.表优化2.索引优化3.查询优化4.服务器优化5.系统与硬件优化稳定优化1....
性能优化-索引
概述索引优化是对查询性能优化最有效的手段，索引能轻易将查询性能提高几个数量级。索引类型在Mysql中，索引是...
查询性能优化
MySQL查询优化器的局限性关联子查询 MySQL的子查询实现的非常糟糕，最糟糕的一类查询是where条件中包含...