MySQL之SQL优化系列（一）

作为一个不知道几分之几的程序员，日常性能测试的关注点之一就是SQL的执行效率，SQL优化。

案例库

mysql提供有类似oracle的scott库（root/tiger，至今记得接触oracle时候这个趣闻，创始人是库名，猫的名字是密码）的案例库sakila，基本可以使用来完成SQL优化的测试。

sakila库下载地址

压缩包中有三个文件：sakila-schema.sql，sakila-data.sql，sakila.mwb；sakila-schema.sql建库及表，sakila-data.sql插数据，sakila.mwb可以使用mysql workbench打开，是sakila的数据模型（MySQL Mode）

MySQL workbench官方下载地址
度娘盘地址        密码：25y4

安装完成：

mysql> use sakila;
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

show status查看SQL执行频率

MySQL可以通过show [session|global] status命令来查看服务器状态信息；其中session，global为可选参数，session表示显示当前连接的统计结果，global表示统计自数据库上次启动至今的信息，不写默认为session。
示例：

mysql> show status like 'Com_%';        
+-----------------------------+-------+ 
| Variable_name               | Value | 
+-----------------------------+-------+ 
| Com_admin_commands          | 0     | 
| Com_assign_to_keycache      | 0     | 
| Com_alter_db                | 0     | 
| Com_alter_db_upgrade        | 0     | 
| Com_alter_event             | 0     | 
| Com_alter_function          | 0     | 
| Com_alter_instance          | 0     | 
| Com_alter_procedure         | 0     | 
| Com_alter_server            | 0     | 
| Com_alter_table             | 2     | 
| Com_alter_tablespace        | 0     | 
| Com_alter_user              | 0     | 
| Com_analyze                 | 0     | 
| Com_begin                   | 0     | 
| Com_binlog                  | 0     | 
| Com_call_procedure          | 0     | 
| Com_change_db               | 3     | 
| Com_change_master           | 0     | 
| Com_change_repl_filter      | 0     | 
| Com_check                   | 0     | 
| Com_checksum                | 0     | 
| Com_commit                  | 15    | 
| Com_create_db               | 1     | 
...

统计当前连接下的信息。

参数解释

Com_xxx表示每个xxx语句执行的次数，通常比较关注的是以下几个：
◆ Com_select： 执行SELECT操作的次数，执行一次累加1；
◆ Com_insert： 执行INSERT操作的次数，对于批量插入的INSERT操作，只累加一次；
◆ Com_update： 执行UPDATE操作的次数。
◆ Com_delete： 执行DELETE操作的次数。
上述参数对于所有的存储引擎的表操作都会进行累计，下面的参数，只针对InnoDB存储引擎：
◆ Innodb_read：SELECT查询返回的行数。
◆ Innodb_rows_inserted：执行INSERT操作插入的行数。
◆ Innodb_rows_updated：执行UPDATE操作更新的行数。
◆ Innodb_rows_deleted：执行DELETE操作删除的行数。
通过以上参数，比较容易得出当前数据库的应用是插入更新为主还是查询操作为主，以及各种类型的SQL大致执行的比例是多少。（对于更新操作的技术，是对执行次数的技术，不论commit还是rollback都会累加）。
对于事务型应用，通过Com_commit和Com_rollback可以了解事务提交和回滚的情况，对于回滚操作很频繁的数据库，可能意味着应用编写存在问题。
另外，一下几个参数便于了解数据库的基本情况：
◆ Connections：尝试连接MySQL服务器的次数。
◆ Uptime：服务器工作时间。
◆ Slow_queried：慢查询次数

定位执行效率低的SQL

我在工作中，使用过两种方式来查看当前执行效率低的SQL：
一：查看线程：show processlist
示例：

mysql> show processlist;
+----+------+-----------------+--------+---------+-------+----------+------------------+
| Id | User | Host            | db     | Command | Time  | State    | Info             |
+----+------+-----------------+--------+---------+-------+----------+------------------+
|  6 | root | localhost:16830 | test1  | Sleep   | 18721 |          | NULL             |
|  7 | root | localhost:17154 | test1  | Sleep   | 18721 |          | NULL             |
| 18 | root | localhost:47691 | sakila | Query   |     0 | starting | show processlist |
| 19 | root | localhost:49737 | NULL   | Sleep   |   393 |          | NULL             |
| 20 | root | localhost:49738 | sakila | Sleep   |   393 |          | NULL             |
+----+------+-----------------+--------+---------+-------+----------+------------------+
5 rows in set (0.00 sec)

该命令可以查看当前MySQL在进行的线程，包括状态，信息（是否锁表）等，便于实时查看SQL执行情况

二：慢查询日志定位
MySQL的慢查询日志位置可以命令查看：

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------------------------+
| Variable_name       | Value                                                     |
+---------------------+-----------------------------------------------------------+
| slow_query_log      | OFF                                                       |
| slow_query_log_file | /home/data/mysql/centos7-db-slow.log 	       		  |
+---------------------+-----------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

默认情况下慢查询是关闭的，慢查询日志保存路径可以看见。
只需要给slow_query_log设置值即可开启慢查询：

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------------------------+
| Variable_name       | Value                                                     |
+---------------------+-----------------------------------------------------------+
| slow_query_log      | ON                                                        |
| slow_query_log_file | /home/data/mysql/centos7-db-slow.log			  |
+---------------------+-----------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

同时，可以配置慢查询时间，以及慢查询日志保存路径：
在mysql安装路径下找到my.cnf文件，在其中增加或者修改long_query_time和slow_query_log_file即可完成配置(需要注意保存慢查询日志文件的文件夹的权限，属主和属组是mysql)。

#[slowsql]
slow_query_log = on
slow_query_log_file =/var/lib/mysql/new_slow.log
long_query_time = 1
log_output = file

或者命令行更改也可以：

mysql> show variables like '%long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> set long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set, 1 warning (0.00 sec)

慢查询示例

当MySQL开启了慢查询，并且慢查询时间也根据自己情况进行了更改，下面实验下：

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------------------------------+
| Variable_name       | Value                                                     |
+---------------------+-----------------------------------------------------------+
| slow_query_log      | ON                                                        |
| slow_query_log_file | /home/data/mysql/centos7-db-slow.log			  |
+---------------------+-----------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

可以看见，当前系统慢查询是开启状态，且一旦出现慢查询，会记录日志在/home/data/mysql/centos7-db-slow.log中。

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set, 1 warning (0.00 sec)

慢查询时间设置为2s，一旦有SQL执行时间超过2s，就会被认为是慢查询，记录在慢查询日志中。

执行4s查询：

mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)

该SQL执行时间为固定的4s，按照设置，肯定是慢查询了，进入日志文件，查看日志：

MySQL, Version: 5.7.21 (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2018-03-09T08:34:16.502235Z
# User@Host: root[root] @ localhost [::1]  Id:     2
# Query_time: 4.000101  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use sakila;
SET timestamp=1520584456;
select sleep(4);

以上就是慢查询内容，包含数据库，SQL执行时间，具体SQL，即定位到执行效率低的SQL，进行优化。

后记

定位到慢查询的SQL之后，接下来就是对低效SQL进行分析，后序再说…