MySQL 性能调优——SQL 优化

数据库性能的影响因素很多，包括：

影响因素说明解决方案

SQL 查询速度

存在慢查询 SQL

大表和大事务

MySQL 单表行数超过千万行或单表数据超过10GB就会影响性能。

数据库存储引擎

数据库参数配置

配置不当会直接影响性能。

服务器硬件

网卡流量

磁盘 IO

使用更快的磁盘设备，比如 RAID 卡、SSD、Fusion-IO 等；检查是否存在其他大量消耗磁盘性能的计划任务，如果存在，则调整计划任务，做好磁盘维护。

注意：MySQL 5.7 是单线程的服务，并不支持多 cpu 并发运算，意味着每条 sql 只能用到 1 个 cpu。

如何定位并优化慢查询 SQL？具体场景具体分析，大致思路如下：

SQL 查询优化，索引优化，库表结构优化需要齐头并进。本篇主要记录了一些 SQL 调优的经验。

在进行库表结构设计时，我们要考虑到以后的查询要如何的使用这些表，同样，编写 SQL 语句的时候也要考虑到如何使用到目前已经存在的索引，或是如何增加新的索引才能提高查询的性能。

想要对存在性能问题的查询进行优化，需要能够找到这些查询，下面先看下如何获取有性能问题的 SQL。

1.SQL调优

获取有性能问题的 SQL 的两种方法：

1.根据慢查询日志定位慢查询 SQL

MySQL 慢查询日志是一种性能开销比较低的解决方案，主要性能开销在磁盘 IO 和存储日志所需要的磁盘空间。对于磁盘 IO 来说，由于写日志是顺序存储，开销基本上忽略不计，所以主要需要关注的还是磁盘空间。

MySQL 提供了以下参数用于控制慢查询日志：

slow_query_log：是否启动慢查询日志，默认不启动，on 启动；
slow_query_log_file：指定慢查询日志的存储路径及文件，默认保存在 MySQL 的数据目录中；
long_query_time：指定记录慢查询日志 SQL 执行时间的阈值，单位秒，默认10，对于一个繁忙的系统，改为0.001比较合适；
log_queries_not_using_indexes：是否记录未使用索引的 SQL；

开启慢查询日志有两种方式，第一种是通过配置 /etc/my.cnf 文件开启，是永久性的，第二种是通过设置全局变量开启，MySQL 重启后会失效。

设置全局变量的 SQL 如下：

set global slow_query_log = on;
set global long_query_time = 1;

和二进制日志不同，慢查询日志会记录所有符合条件的 SQL，包括查询语句、数据修改语句、已经回滚的 SQL。

慢查询日志中记录的内容：

# Query_time: 0.000220             //执行时间，可以精确到毫秒，220毫秒
# Lock_time: 0.000120              //所使用锁的时间，可以精确到毫秒
# Rows_sent: 1                     //返回的数据行数
# Rows_examined: 1                 //扫描的数据行数
SET timestamp=1538323200;          //执行sql的时间戳
SELECT c FROM test1 WHERE id =100;    //sql

通常情况下，在一个繁忙的系统中，短时间内就可以产生几个 G 的慢查询日志，人工检查几乎是不可能的，为了快速分析慢查询日志，必须借助相关的工具。

常用的慢查询日志工具：

1、mysqldumpslow：一个常用的，MySQL 官方提供的慢查询日志分析工具，随着 MySQL 服务器的安装而被安装。可以汇总除查询条件外其他完全相同的 SQL，并将分析结果按照参数中所指定的顺序输出。

2、pt-query-digest：用于分析 MySQL 慢查询的一个工具。

2.实时获取性能问题SQL

为了更加及时的发现当前的性能问题，我们还可以通过实时的方法来获取有性能问题的 SQL。最方便的一种方法就是利用 MySQL information_schema 数据库下的 PROCESSLIST 表来实现实时的发现性能问题 SQL。例如下面这条 SQL 表示查询出当前服务器中执行时间超过 1 秒的 SQL：

SELECT id,user,host,db,command,time,state,info FROM information_schema.PROCESSLIST WHERE TIME>=1

然后我们可以通过脚本周期性的来执行这条 SQL，实时的发现哪些 SQL 执行的是比较慢的。

2.使用explain分析SQL

explain 可以帮助我们分析 select 语句，让我们知道查询效率低下的原因。这个关键字一般放在 select 语句的前面，用于描述 MySQL 如何执行查询操作以及 MySQL 成功返回结果集需要执行的行数，执行会输出一些 explain 的字段。例如：

EXPLAIN SELECT name FROM person_info_large order by name desc;

需要注意的是，执行 explain 并不会真正的执行 SQL，而是对 SQL 做了一些分析，速度非常快。

explain 关键字段：

id：表示了 MySQL 的执行顺序，id 越大越先执行；
type：表示 MySQL 找到数据行的方式；
key：实际使用的索引；
Extra：额外信息。

type 字段的返回值，性能从最优到最差：

system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_merge -> unique_subquery -> index_subquery -> range -> index -> all

index 和 all 表示本次查询走的是全表扫描。如果 type 值是这两个，表明 SQL 是需要优化的。

Extra 中出现了以下两种意味着 MySQL 根本不能使用索引，效率会受到重大影响，应尽可能对此进行优化：

Extra 项说明

Using filesort

表示 MySQL 会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为 “文件排序”。

Using temporary

表示 MySQL 在对查询结果排序时使用的临时表，常见于排序 order by 和分组查询 group by。

3.修改 SQL或者尽量让SQL走索引

例如为上面 SQL 中的 name 加索引：

ALTER TABLE person_info_large add index idx_name(name);

MySQL的查询优化器

2.SQL的解析预处理及生成执行计划

找到了那些查询存在性能问题的 SQL，那么下面我们就看下，为什么这些 SQL 会存在性能问题？

为了搞清楚这个问题，我们先来看下 MySQL 服务器处理一条 SQL 请求所需要经历的步骤都有哪些：

客户端通过 MySQL 的接口发送 SQL 请求给服务器，这一步通常不会影响查询性能；MySQL 服务器检查是否可以在查询缓存中命中该 SQL，如果命中，则立即返回存储在缓存中的结果，否则进入下一阶段；MySQL 服务器进行 SQL 解析，预处理，再由 SQL 优化器生成对应的执行计划；根据执行计划，调用存储引擎 API 来查询数据；将结果返回给客户端。

这就是 MySQL 服务器处理查询请求的整个过程。在第二到第五步，都有可能对查询的响应速度造成影响，下面来分别看下这些过程可能对查询的响应速度有影响的因素都有些什么：

在解析查询语句前，如果查询缓存是打开的，那么 MySQL 优先检查这个查询是否命中查询缓存中的数据，这个检查是通过一个对大小写敏感的 Hash 查找实现的。由于 Hash 查找只能进行全值匹配，所以请求的查询和缓存中的查询就算只有一个字节的不同，那么也不会匹配到缓存中的结果，这种情况下，查询就会进入到下一阶段处理。如果正好命中查询缓存，在返回查询结果之前，MySQL 就会检查用户权限，也是无需解析 SQL 语句的，因为在查询缓存中，已经存放了当前查询所需要访问的表的信息mysql 优化，如果权限没有问题，MySQL 会跳过所有的其他阶段，直接从缓存中拿到结果，并返回给客户端，这种情况下查询是不会被解析的，也不会生成查询计划，不会被执行。

可以发现，从查询缓存中直接返回结果并不容易。

查询缓存对 SQL 性能的影响：

对于一个读写频繁的系统来说，查询缓存很可能会降低查询处理的效率。所以在这种情况下建议大家不要使用查询缓存。

对查询缓存影响的一些系统参数：

query_cache_type: 设置查询缓存是否可用，可以设置为ON、OFF、DEMAND，DEMAND表示只有在查询语句中使用 SQL_CACHE 和 SQL_NO_CACHE 来控制是否需要缓存。
query_cache_size: 设置查询缓存的内存大小，必须是1024字节的整数倍。 
query_cache_limit: 设置查询缓存可用存储的最大值，如果知道很大不会被缓存，可以在查询上加上 SQL_NO_CACHE 提高效率。
query_cache_wlock_invalidate: 设置数据表被锁后是否返回缓存中的数据，默认关闭。
query_cache_min_res_unit: 设置查询缓存分配的内存块最小单位。

对于一个读写频繁的系统来说，可以把 query_cache_type 设置为 OFF，并且把 query_cache_size 设置为 0。

当查询缓存未启用或者未命中则会进入下一阶段，也就是需要将一个 SQL 转换成一个执行计划，MySQL 再依据这个执行计划和存储引擎进行交互，这个阶段包括了多个子过程：解析 SQL，预处理，优化 SQL 执行计划。在这个过程中，出现任何错误，比如语法错误等，都有可能中止查询的过程。

在语法解析阶段，主要是通过关键字对 MySQL 语句进行解析，并生成一棵对应的 “解析树”。这一阶段，MySQL 解析器将使用 MySQL 语法规则验证和解析查询，包括检查语法是否使用了正确的关键字、关键字的顺序是否正确等。

预处理阶段则是根据 MySQL 规则进一步检查解析树是否合法，比如检查查询中所涉及的表和数据列是否存在、检查名字或别名是否存在歧义等。

如果语法检查全部都通过了，查询优化器就可以生成查询计划了。

会造成 MySQL 生成错误的执行计划的原因：

MySQL 的查询优化器可以优化的 SQL 类型：

以上这些就是 MySQL 查询优化器可以自动对查询所做的一些优化。经过查询优化器改写后的 SQL，查询优化器会对其生成一个 SQL 执行计划，然后 MySQL 服务器就可以根据执行计划调用存储引擎的 API，通过存储引擎获取数据了。

3.确定查询处理各个阶段的耗时

SQL 查询优化的主要目的就是减少查询所消耗的时间，加快查询的响应速度。下面来介绍如何度量查询处理各个阶段所消耗的时间。

对于一个存在性能问题的 SQL 来说，必须知道在查询的哪一阶段消耗的时间最多，然后才能有针对性的进行优化。度量查询处理各个阶段所消耗的时间，常用的方法有两种：

4.特定SQL的查询优化

前面介绍的方法，已经可以获取一个存在性能问题的 SQL 和获取一个 SQL 在执行的各个阶段所消耗的时间了。得到这些信息后，我们就可以针对性的对 SQL 进行优化了，下面举几个对特定 SQL 优化的案例：

1.大表的更新和删除

对于大表的数据修改最好要分批处理，比如我们要在一个 1000 万行记录的表中删除/更新 100 万行记录，那么我们最好分多个批次进行删除/更新，一次只删除/更新 5000 行记录，避免长时间的阻塞，并且为了减少对主从复制带来的压力，每次删除/修改数据后需要暂停几秒。这里提供一个可以完成这样工作的 MySQL 存储过程的实例：

DELIMITER $$
USE 'db_name'$$
DROP PROCEDURE IF EXISTS 'p_delete_rows'$$
CREATE DEFINER='mysql'@'127.0.0.1' PROCEDURE 'p_delete_rows'()
BEGIN
		DECLARE v_rows INT;
		SET v_rows = 1;
		WHERE v_rows > 0
		DO
				DELETE FROM table_name WHERE id >= 9000 AND id <= 290000 LIMIT 5000;
				SELECT ROW_COUNT() INTO v_rows;
				SELECT SLEEP(5);
		END WHERE;
END$$
DELIMITER;

大家可以根据自己的情况来修改这个存储过程，或者使用自己熟悉的开发语言实现这个处理过程，使用这个存储过程只需要修改 DELETE FROM table_name WHERE id >= 9000 AND id

（编辑：沈阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!