MySQL 性能调优——SQL 优化

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

影响因素	说明	解决方案
SQL 查询速度	存在慢查询 SQL
大表和大事务	MySQL 单表行数超过千万行或单表数据超过10GB就会影响性能。
数据库存储引擎
数据库参数配置	配置不当会直接影响性能。
服务器硬件
网卡流量
磁盘 IO		使用更快的磁盘设备，比如 RAID 卡、SSD、Fusion-IO 等；检查是否存在其他大量消耗磁盘性能的计划任务，如果存在，则调整计划任务，做好磁盘维护。

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

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

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

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

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

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

1.SQL调优

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

• 通过慢查日志获取存在性能问题的 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 秒的