第三章、服务器性能剖析

前言

项	版本号	说明
Mysql	5.6.37 MySQL Community Server (GPL)	5.0以上版本即可

本系列文章饰演的mysql版本信息如下：

项	版本号	说明
Mysql	5.6.37 MySQL Community Server (GPL)	5.0以上版本即可

3.1、性能优化简介

任务时间 = 执行时间 + 等待时间
因此，优化分为两个部分优化

• 执行时间优化：
  优化子任务，降低子任务执行频率或者提升子任务效率
• 等待时间优化：这部分的消耗大多数都在IO这块

3.2、性能剖析

• 值得优化的查询：
  • 根据Amdahl定律，占总响应时间比重小的查询不不值得优化，对占用总查询时间比重不到5%的查询如何优化，收益不会超过5%。
  • 优化成本应该大于收益。
• 异常情况：某些执行次数很少，但是严重影响用户体验的，需要优化。
• 隐藏的细节，不要过度的依赖平均值，如果某次查询比平均值高出很多也应该引起重视。

注意事项：
性能剖析本身会导致服务器变慢。

3.3、性能剖析工具及方法

• Profiler：MySQL自带的性能分析工具

• 3.3.1、查看是否已经启用profile，默认是关闭的。

mysql> show profile;
Empty set, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

• 3.3.2、启用profiling，用户变量，作用域是每次连接。

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

• 3.3.3、使用show profile,查询最近一条sql的执行信息。

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000055 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000007 |
| init                 | 0.000011 |
| optimizing           | 0.000007 |
| executing            | 0.000010 |
| end                  | 0.000004 |
| query end            | 0.000003 |
| closing tables       | 0.000003 |
| freeing items        | 0.000007 |
| cleaning up          | 0.000011 |
+----------------------+----------+
11 rows in set, 1 warning (0.00 sec)

• 3.3.4、使用show profiles,查询服务器上执行语句列表(查询id，花费时间，语句) 。

mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00096650 | select * from city             |
|        2 | 0.00034450 | show tables                    |
|        3 | 0.00026525 | select * from actor limit 1,15 |
+----------+------------+--------------------------------+
3 rows in set, 1 warning (0.00 sec)

• 3.3.5、 show profile for query Query_ID;
  查询知道sql的执行信息。

慢查询日志(slow query log)

慢查询日志是比较常用的查询方案。

long_query_time     ：  设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s  
slow_query_log      ：  指定是否开启慢查询日志  
log_slow_queries    ：  指定是否开启慢查询日志(该参数要被slow_query_log取代，做兼容性保留)  
slow_query_log_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log  
min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志  
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引  

• 查询当前版本

mysql> show variables like 'version'; 
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.6.37 |
+---------------+--------+
1 row in set (0.00 sec)

• 查询慢查询日志相关的信息

mysql> show variables like '%slow%'; 
+---------------------------+-------------------------------------------------+
| Variable_name             | Value                                           |
+---------------------------+-------------------------------------------------+
| log_slow_admin_statements | OFF                                             |
| log_slow_slave_statements | OFF                                             |
| slow_launch_time          | 2                                               |
| slow_query_log            | OFF                                             |
| slow_query_log_file       | /var/lib/mysql/izt4nb1qclhh1mccy6zrbcz-slow.log |
+---------------------------+-------------------------------------------------+

• 设置慢查询日志

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

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

设置全局session级别,然后查看慢查询日志

vim /var/lib/mysql/izt4nb1qclhh1mccy6zrbcz-slow.log

explain关键字

mysql> explain select * from city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL |  600 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

如下图可以看到两个sql执行的信息是不一样的。

mysql> explain select * from city where city_id =1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | city  | const | PRIMARY       | PRIMARY | 2       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

这里就先记录这些功能，下一篇开始Schema与数据类型优化

《高性能MySQL读书笔记》

准备篇-mysql安装
准备篇-Sakila数据库
第一章、MySQL架构及历史
第二章、MySQL基准测试
第三章、服务器性能剖析