MySQL——SQL优化

如何定位并优化慢查询 SQL？大致思路如下：

• 根据慢查询日志定位慢查询 SQL；
• 使用 explain 等工具分析 SQL；
• 修改 SQL 或者尽量让 SQL 走索引。

SQL调优

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

• 通过慢查日志获取存在性能问题的 SQL；
• 实时获取存在性能问题的 SQL；

慢SQL定位

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

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

首选，需要确认是否已经开启了慢查询日志：

show variables like "%slow%"

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 的慢查询日志，人工检查几乎是不可能的，为了快速分析慢查询日志，必须借助相关的工具。

常用的慢查询日志工具：

• mysqldumpslow：一个常用的，MySQL 官方提供的慢查询日志分析工具，随着 MySQL 服务器的安装而被安装。可以汇总除查询条件外其他完全相同的 SQL，并将分析结果按照参数中所指定的顺序输出。
• 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 执行的是比较慢的。

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 字段：

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

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

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

Extra字段

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

Extra 项	说明
Using filesort	表示 MySQL 会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为 “文件排序”。
Using temporary	表示 MySQL 在对查询结果排序时使用的临时表，常见于排序 order by 和分组查询 group by。

特定SQL的查询优化

大表/大数据量的更新和删除

对于大表的数据修改最好要分批处理，比如我们要在一个 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;

如何修改大表的表结构

对于 InnoDB 存储引擎来说，对表中的列的字段类型进行修改或者改变字段的宽度时还是会锁表，同时也无法解决主从数据库延迟的问题。

解决方案：

在主服务器上建立新表，新表的结构就是修改之后的结构，再把老表的数据导入到新表中，并且在老表上建立一系列的触发器，把老表数据的修改同步更新到新表中，当老表和新表的数据同步后，再对老表加一个排它锁，然后重新命名新表为老表的名字，最好删除重命名的老表，这样就完成了大表表结构修改的工作。这样处理的好处是可以尽量减少主从延迟，以及在重命名之前不需要加任何的锁，只需要在重命名的时候加一个短暂的锁，这对应用通常是无影响的，缺点就是操作比较复杂。好在有工具可以帮我们实行这个过程，这个工具同样是 percona 公司 MySQL 工具集中的一个，叫做 pt-online-schema-change：

pt-online-schema-change \
--alter="MODIFY c VARCHAR(150) NOT NULL DEFAULT ''" \
--user=root --password=password D=db_name,t=table_name \
--charset=utf8 --execute

这个命令就是把 db_name 数据库下的 table_name 表中 c 列的宽度改为 VARCHAR(150)。

如何优化not in和<>查询

MySQL 查询优化器可以自动的把一些子查询优化为关联查询，但是对于存在not in和<>这样的子查询语句来说，就无法进行自动优化了，这就造成了会循环多次来查找子表来确认是否满足过滤条件，如果子查询恰好是一个很大的表的话，这样做的效率会非常低，所以我们在进行 SQL 开发时，最好把这类查询自行改写成关联查询。

改写前：

SELECT id,name,email 
FROM customer 
WHERE id 
NOT IN(SELECT id FROM payment)

优化改写后：

SELECT a.id,a.name,a.email 
FROM customer a 
LEFT JOIN payment b ON a.id=b.id 
WHERE b.id IS NULL

优化COUNT()查询

COUNT函数的作用：

• 统计某个列的数量
• 统计行数

统计列值的时候要求列值是非空的。列值为空的列将不会被统计到。

当MySQL确认括号内的表达式值不可能为空时，实际上是在统计行数。

优化关联查询

优点建议：

• 确保关联查询的列上有索引。通常只需要在关联顺序中的第二个表的相应列创建索引。
• 确保任何分组和排序的表达式中只涉及一个表中的列。

优化子查询

优化建议

• 尽可能使用关联查询代替。

优化group和distinct

优化建议：

• 如果需要对关联查询表中的某个列做分组，通常采用主表的标识列分组效率会更高

优化Limit分页

偏移量大的时候，查询效率会很糟糕。

优化建议：

• 尽量使用索引覆盖扫描，再做关联查询返回所需的列。

例如：

原SQL：

select file_id,description from sakila.film order by title Limit 50,5

改写之后：

select file_id,description from sakila.film  
inner join 
(select file_id from sakila.film order by title Limit 50,5) as lim
using(file_id ) 

优化UNION查询

优化建议：

• 除非确定要消除重复的行，否则就一定要使用UNION ALL。如果没有ALL关键字，会给临时表加上distinct选项，导致对整个临时表数据做唯一性检查，代价非常高。