前言
| 项 | 版本号 | 说明 |
|---|---|---|
| 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基准测试
第三章、服务器性能剖析
