MySQL的一些优化在日常的业务开发中，你是否遇到过慢查询的问题？你是否在面对这种问题是无从下手？本以为自己掌握了建表优

在日常的业务开发中，你是否遇到过慢查询的问题？你是否在面对这种问题是无从下手？本以为自己掌握了建表优化、索引优化武林秘籍，可以降妖除魔，笑对九九八十一难，没想到在业务查询时又陷入了泥沼之中。今天我们就来讲下慢查询中的问题。

慢查询

慢查询根据字面意思，就是SQL查询速度比较慢。在2C业务中，慢查询非常影响用户体验。试想一下，当你想要查看某个App上的消息列表时，过了很久才加载出来（当然可能是网络的问题），这时你是否有卸载它的冲动呢？查询性能低下的最基本原因是访问的数据太多。虽然有些情况下，我们确实需要很多数据，但这种情况很少见。针对于性能查的慢查询一般都可以通过减少访问的数据量的方式来优化。我们可以通过下面两个步骤来进行分析：

确认业务应用中是否需要检索了超过需要的数据（太多的行或列）；
MySQL服务层是否在分析大量超过需要的数据行。

向数据库中请求多余的数据

查询不必要的行记录

MySQL在查询时会返回所有满足条件的全部结果在进行计算。当我们确定要获取前N行记录时，

低情商的做法是不加LIMIT把所有数据查询出来，然后获取前N行记录。高情商做法是在SELECT后面加上LIMIT。

多表关联时返回所有的列

高情商做法：只返回需要的列

单表查询时返回所有的列

高情商做法：只返回需要的列。并非绝对，我们也可以查询出全部列放到缓存里，减少后续查询。

重复查询

重复查询指针对同一查询返回相同的数据，这是我们可以把查询结果放到缓存中，避免重复查询。

MySQL是否在分析扫描额外的数据行

对于MySQL，最简单的衡量查询开销的三个指标如下：

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

这三个指标会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的办法。

扫描的行数和实际返回的行数

理想情况下，扫描的行数和返回的行数应该是相同的，但实际情况并非如此。扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间。

扫描的行数和访问类型

在EXPLAIN语句中的type列反应了访问类型，访问类型如下：全表扫描（ALL）、索引扫描（index）、范围扫描（range）、

针对每种访问类型我们给出例子，其中数据库表结构如下：


CREATE TABLE `film_lang` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',

`film_id` int NOT NULL DEFAULT '0' COMMENT 'film id',

`lang_id` int NOT NULL DEFAULT '0' COMMENT '语言id',

`film_name` varchar(100) NOT NULL DEFAULT '' COMMENT '电影名'

`ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`mtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

`is_deleted` tinyint NOT NULL DEFAULT '0' COMMENT '是否删除 1-已删除',

PRIMARY KEY (`id`),

UNIQUE KEY `uk_film_id_lang_id` (`film_id`,`lang_id`),

KEY `ix_mtime` (`mtime`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='电影语言表'

ALL

EXPLAIN SELECT * FROM film_lang WHERE film_name = 'popo'

index

EXPLAIN SELECT mtime FROM film_lang

range

EXPLAIN SELECT * FROM film_lang WHERE film_id >= 3

ref

EXPLAIN SELECT * FROM film_lang WHERE film_id = 1

const

EXPLAIN SELECT * FROM film_lang WHERE id = 1

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

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

一般情况下，如果发现扫描的行远远多于实际需要的行，我们可以尝试已下几种方式去优化：

构建索引，使得查询的列放到索引中，使用覆盖索引避免回表；
改变库表结构，例如使用汇总表；
重构查询方式。

重构查询方式

一个复杂的查询 OR 多个简单的查询

切分查询

分解关联查询

分解关联查询指的是不需要再进行表与表之间的关联，可以对每一个表进行单表查询，然后查询的结果在应用查询中关联。使用分解关联查询的方式有如下优点：

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的查询结果；
执行单个查询可以较少锁的竞争；
在应用层做关联，可以更容易对数据库做拆分，更容易做到高性能和可扩展；
查询本身效率可能会有所提升；
可以较少冗余数据的查询。

MySQL查询基础

MySQL在执行一条查询时，到底做了些什么呢？

客户端向MySQL服务器发送查询语句；
服务器检查缓存中是否存在，如果存在命中缓存直接返回结果，否则进入下一步；
服务器进行SQL解析、预处理，再通过优化器生成执行计划；
MySQL根据优化器生成的执行计划，调用存储引擎的API执行查询；
将结果返回给客户端。

客户端/服务端通信协议

MySQL客户端与服务器端的采用半双工进行通信，这也意味着在同一时刻，要么是客户端向服务器发送请求数据，要么是服务端向客户端发送数据。半双工通信有一个明显的劣势：无法进行流量控制，一旦服务器端向客户端发送数据，客户端必须接受所有的服务器端的数据才能进行响应。

多数连接MySQL的库函数都可以获取到MySQL服务器的所有结果集并放到缓存中，这样应用程序就可以一行行获取数据。这样做的好处是让查询尽快结束，早点释放相应的资源。

查询状态

对于MySQL连接，任何时刻都有一个状态，该状态表明了MySQL当前正在做什么。MySQL查看当前状态的命令是SHOW FULL PROCESSLIST。以下是对状态的解释。

Sleep

线程正在等待客户端连接

Query

线程正在执行查询或者正在将结果发送给客户端

Locked

在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的航所，并不会体现在线程状态中。

Analyzing and statistics

线程正在收集存储引擎的统计信息，并生成查询的执行计划。

Copying to tmp table [on disk]

线程正在执行查询，并且将结果集复制到一个临时表中。这种状态要么是在做GROUP BY操作，要么是在文件排序操作，要么是在做UNION操作。状态后加“on disk”标记，表示MySQL正在将一个内存临时表放到磁盘上。

Sorting result

线程正在对结果集排序

Sending data

表示多种情况：线程可能在多个状态之间传送数据，或者生成结果集，或者在向客户端返回数据。

查询缓存

MySQL查询缓存的原理是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，也不会匹配缓存结果。如果当前查询命中了缓存，在返回结果之前MySQL会检查一次用户权限。

查询优化处理

预发解析器和预处理

解析器作用：验证是否用错关键字，关键字顺序对不对等；

预处理器：查找的表是否存在，列是否存在，验证权限是否合法。

查询优化器

MySQL基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中最小的一个。优化器在评估成本时并不考虑任何层面的缓存。它假设任何数据都需要一次磁盘IO。会有很多原因导致MySQL优化器选择错误的执行计划。

统计信息不准确。统计信息是由存储引擎层给出的，但有些存储引擎给出的统计信息并不准确；
执行计划中的成本估算不等同于实际执行的成本。比如有两个SQL查询，其中一个需要读取多个页面，但是这条查询都是顺序读取或者都在内存的话，执行成本反而会很低；
MySQL是基于成本模型选择最优的执行计划，但是有时候这却不是最快的；
MySQL从不考虑其他并发执行的查询；
MySQL不会考虑不受其控制的操作成本，例如执行存储过程或者用户自定义函数；

下面是MySQL可以处理的优化类型：

重新定义表的关联顺序

数据表的关联顺讯并不总是按照查询时制定的顺序进行的。优化器会对关联顺序进行优化

将外连接转化为内连接

并不是所有的OUTER JOIN语句都必须以外连接的方式执行。有时候外连接等价与内连接，MySQL能够识别并重写查询。

使用等价变化规则

例如，（5=5 AND a>5）可以被改写成（a > 5）。（a < b AND b = c） AND a = 5 可以改写成 b > 5 AND b = c AND a = 5。

优化COUNT()、MIN()和MAX()

索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如，要找到某列的最小值，只需要查询对用的B-TREE索引最左端记录即可。

覆盖索引扫描

当索引中的列包含所有的使用列的时候，MySQL就可以使用索引返回需要的数据，不需要查询对应的数据行。

子查询优化

提前终止查询

一个典型的例子是LIMIT字句的使用。除此之外，当MySQL发现一个不成立的条件时，立刻返回一个空结果（select * from students where id = -1）；当MySQL发现某些特殊条件时，会提前终止查询。当存储引擎需要检索”不同取值“或者判断存在性的时候，都可以使用这列优化。

等值传播

如果两个列的值通过等式连接，那么MySQL能够把其中一列的WHERE条件传递到另外一列上。


SELECT film.film_id FROM sakila.film INNER JOIN akila.film_actor USING(film_id) WHERE film.film_id > 500

等价于


SELECT film.film_id FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE film.film_id > 500 AND sakila.film_id > 500

列表IN的比较

MySQL会将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。

数据和索引的统计信息

MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息，这些统计信息包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。

MySQL如何执行关联查询

首先来查看选UNION查询的例子，对于UNION查询，MySQL将一系列查询结果放到一个临时表中，然后重新读出临时表来完成UNION查询。

MySQL关联查询的策略很简单：嵌套循环关联操作（多重for循环）。MySQL现在一个表中取出单行数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有的行数据。


SELECT tb1.col1, tbl2.col2 FROM tbl1 INNER JOIN tbl2 USING(col3) WHERE tbl1.col1 IN(5, 6)

伪代码如下：


for out_row := range tbl1.col1 IN(5, 6) {

for inner_row := range col3 = out_row.col3 {

}

}

执行计划

和很多关系型数据库不同的是，MySQL并不会生成查询字节码来执行查询，而是会生成查询的指令树，存储引擎执行完成这课指令树并返回结果

关联查询优化

MySQL最重要的是关联查询优化，他决定了多个表关联的顺序，不同的关联顺序可能获得不同的执行结果。优化器通过评估不同关联查询的成本选择其中最低的成本。

排序优化

当不能通过索引生成排序结果时，MySQL需要自己进行排序。如果数据量小就在内存中进行，否则需要使用磁盘。MySQL将这个过程统一称为文件排序(filesort)。

MySQL有如下两种排序算法：

两次传输排序

读取行指针和需要排序的字段，对其排序，然后根据排序结果读取所需要的数据行。这需要两次数据传输。第二次读取数据时，因为是读取排序列进行排序后的所有记录，会产生大量的随机IO，成本非常高。

单次传输排序

先读取查询所需要的所有列，然后根据给定列进行排序，最后直接返回排序结果。相比二次传输排序，只需要进行一次IO。但如果查询的列非常多，会额外占用大量的空间。

执行查询引擎

返回结果给客户端

MySQL将结果集返回给客户端是一个增量、逐步返回的过程。当服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以向客户端逐步返回结果集了。

优化

优化特定类型的查询

优化COUNT()查询

COUNT()的作用

COUNT()是一个特殊的函数，有两个不同的作用：既可以统计某个列值的数量，也可以统计行数。

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

COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值补位空时，实际上就是统计行数。

由此延伸出一个问题，count(*), count(1)和count(列名)的区别？

count(*)和count(1)没什么区别
count(*)计算表中的所有行，包括空值，count(列名)计算表中的非空值。

优化COUNT

MyISAM的count(*)可以利用存储引擎的特性直接获得这个值，前提条件是没有任何WHERE条件。
可以采用非操作

比如下面的sql：


SELECT COUNT(*) FROM world.CITY WHERE ID > 5;

可以转换成


SELECT (SELECT COUNT(*) FROM world.CITY - COUNT(*) FROM world.CITY WHERE ID <= 5)

这样可以大大较少需要扫描的行数。

使用近似值

有些某些业务场景并不要求完全精确COUNT值，此时可以利用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值，而执行EXPLAIN并不需要真正去执行查询，所以成本很低。

在MySQL层面加上覆盖索引扫描。

优化关联查询

确保ON或者USING子句中的列上有索引。在创建索引时要考虑到表的关联顺序。当表A和表B用列c关联的时候，如果优化器关联顺序是B、A，那么就不必要在B表的对应列上加索引了。
确保任何的GROUP BY和ORDER BY中的表达式只涉及到表中的一个列。

优化子查询

最重要的优化是尽可能使用关联查询代替

优化GROUP BY和DISTINCT

在很多场景下，MySQL都使用相同的优化策略来优化这两个查询。它们都可以利用索引来进行优化，这就是最有效的优化办法。

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

优化LIMIT分页

优化分页查询的一个最简单的方法是尽可能使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联操作在返回所有的列信息。对于偏移量很大的查询，这样做的效率提升会非常大。考虑下面的查询：


SELECT film_id, desc FROM sakila.film ORDER BY title LIMIT 50, 5;

可以转换为


SELECT film.film_id, film. desc FROM sakila.film INNER JOIN (

SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5

) AS lim USING(film_id)

)

MySQL的一些优化

慢查询