MySQL调优2

 

如何调优SQL的基础上,继续学习MySQL调优技术。

MySQL调优的维度

MySQL调优2

 在三角形的底部,成本越低。如业务需求。

 

2、测试数据准备

下载 https://github.com/datacharmer/test_db 测试数据

cd /home/files/test_db-master

执行 mysql -uroot -pxxx < employees.sql 

MySQL调优2

 

 

检查导入是否正常

 mysql -uroot -pxxx -t < test_employees_md5.sql

[root@VM_0_13_centos test_db-master]# mysql -uroot -pxxx -t < test_employees_md5.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+----------------------------------+
| table_name   | expected_records | expected_crc                     |
+--------------+------------------+----------------------------------+
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
+--------------+------------------+----------------------------------+
+--------------+------------------+----------------------------------+
| table_name   | found_records    | found_crc                        |
+--------------+------------------+----------------------------------+
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
+--------------+------------------+----------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| departments  | OK            | ok        |
| dept_emp     | OK            | ok        |
| dept_manager | OK            | ok        |
| employees    | OK            | ok        |
| salaries     | OK            | ok        |
| titles       | OK            | ok        |
+--------------+---------------+-----------+
+------------------+
| computation_time |
+------------------+
| 00:00:38         |
+------------------+
+---------+--------+
| summary | result |
+---------+--------+
| CRC     | OK     |
| count   | OK     |
+---------+--------+

  

3、MySQL慢查询

1) MySQL慢查询参数

MySQL调优2

 

 

 

 

 

2) MySQL使用方式

MySQL调优2

 

 

 

方式二

MySQL调优2

 

 

 这里使用第二种方式

set global slow_query_log  = ‘ON‘;
set global log_output = ‘FILE,TABLE‘;
set global long_query_time = 0.001; --1毫秒 需要重新连接数据库
show variables like ‘%long_query_time%‘ 查看是否生效
set global log_queries_not_using_indexes = ‘ON‘;


MySQL慢查询

select  * from employees
select * from `mysql`.slow_log;
可以看到有慢查询日志了

MySQL调优2

 

 rows_sent 表示有多少条数据返回客户端。

rows_examined: 表示这条SQL扫码了多少行。

 

显示慢查询文件所在的路径

show variables like ‘%slow_query_log_file%‘

MySQL调优2

 

 路径为:/var/lib/mysql/VM_0_13_centos-slow.log

 

查看慢日志文件 more VM_0_13_centos-slow.log

MySQL调优2

 

 

 3、Explian使用

找到慢SQL后,使用Explian查具体慢的原因

Explian结果输出字段

MySQL调优2

 

 

 

实例1

MySQL调优2

 

 

 type: ALL  全表扫描,性能最差

key,key_length 为空: 说明没有执行任何索引。

 rows: 扫描行数 2838426

 

实例2

explain
select * from employees e
left join salaries s on e.emp_no = s.emp_no
where e.emp_no = 10001

 

 

 结果:

MySQL调优2这里id都是1,从上到下依次执行; 如果id值不同,id值越大,越早执行。

 

4、Explain可视化工具

1) IDEA中,选择SQL语句,右键。选择Explian Plan(Raw)

MySQL调优2

 

 

 4、SQL性能分析

SQL内部分析性能包括

SHOW PROFILE

INFORMATION_SCHEMA.PROFILING

PERFORMANCE_SCHEMA (MySQL建议使用)

 

1) SHOW PROFILE是MySQl的一个性能分析命令,可以跟踪SQL各种资源消耗

查看是否支持

SELECT @@have_profiling;

 MySQL调优2

 

 

 YES说明支持。

 

查看是否开启

select @@profiling;

MySQL调优2

 

 

 0说明没有开启

 

开启profiling

set @@profiling = 1;

默认之后记录15条的历史

 

设置历史条数为100 

set profiling_history_size = 100;

 

查看profile

show profiles;

 

查询select * from salaries; 后执行show profiles;

MySQL调优2

 

 

 可以发现Query_ID 为49.

 

 

查询这条SQL的执行过程 show profile for query 49;

MySQL调优2

 

 

 可以发现这个步骤执行时间最久。 Sending data 0.000745

 

查询这条SQL内存执行信息

show profile memory for query 49;

查询各种各样的信息

show profile ALL for query 49;

 

分析完成之后,关闭profiling

set @@profiling = 0;

MySQL官方文档声明SHOW PROFILE已被废弃,并建议使用Performance Schema作为替代。

 

2)  INFORMATION_SCHEMA.PROFILING

打开profiling 

set @@profiling = 1;

 

执行SQL 

select * from salaries;

 

查看Query_ID为125  show profiles;

MySQL调优2

 

 

显示profile

select STATE, format(DURATION,6) AS DURATION
FROM information_schema.PROFILING
WHERE  QUERY_ID = 125 ORDER BY SEQ  

显示结果如下:

MySQL调优2

 

 

3) PERFORMANCE_SCHEMA 

 查看是否开启 

select * from performance_schema.setup_actors;

MySQL调优2

 

 默认是开启的。任意主机发过来的请求,任意用户,任意角色都开启了。

 

只监控指定用户执行的SQL(实际项目建议使用)

MySQL调优2

 

 执行如下SQL,开启监控项

UPDATE performance_schema.setup_instruments
SET ENABLED  = ‘YES‘, TIMED = ‘YES‘
WHERE NAME LIKE ‘%statement/%‘;

UPDATE performance_schema.setup_instruments
SET ENABLED  = ‘YES‘, TIMED = ‘YES‘
WHERE NAME LIKE ‘%stage/%‘;



UPDATE performance_schema.setup_consumers
SET ENABLED  = ‘YES‘
WHERE NAME LIKE ‘%events_statements_%‘;


UPDATE performance_schema.setup_consumers
SET ENABLED  = ‘YES‘
WHERE NAME LIKE ‘%events_stages_%‘;

 

使用开启监控的用户,执行SQL语句,比如:

SELECT * FROM salaries

  

执行如下SQL,获得语句EVENT_ID

select EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000, 6) AS DURATION, SQL_TEXT
FROM performance_schema.events_statements_history_long
where SQL_TEXT LIKE ‘%salaries%‘

  结果如下图:

MySQL调优2

 

 

分析执行的SQL语句

select event_name as Stage, TRUNCATE(TIMER_WAIT/1000000000000, 6) AS DURATION
FROM performance_schema.events_statements_history_long
where NESTING_EVENT_ID = 400

  

 

 

4) 三种方式对比

SHOW PROFILE    简单,方便,已废弃。

INFORMATION_SCHEMA.PROFILING  和SHOW PROFILE  本质是一样的 (已废弃)

PERFORMANCE_SCHEMA (MySQL建议使用) 目前来说使用不够方便。

如何选择: 目前可以继续使用SHOW PROFILE 

 

6、OPTIMIZER_TRACE  优化器跟踪

跟踪优化器做出的各种决策

 了解优化器的执行细节

理解SQL的执行过程,进而优化SQL

MySQL调优2

 

 开启 optimizer_trace 

 set optimizer_trace = "enabled=on", end_markers_in_json = on;

记录最近30条SQL语句

set optimizer_trace_offset = -30, optimizer_trace_limit = 30;

 

执行需要分析的SQL语句

select *
from salaries
where from_date = ‘1986-06-26‘
and to_date = ‘1987-06-26‘

 

使用如下语句分析

select * from information_schema.OPTIMIZER_TRACE limit 30

 

 

7、SQL诊断命令

如果数据库出了问题,应该如何定位呢,下面介绍常用的SQL诊断命令

1) 查看当前正在运行的线程

show [FULL] processlist

几个information_schema表常用的操作

MySQL调优2

 

 MySQL调优2

 

 

2)  查看服务器相关信息

show status

 

3) 查看MySQL的变量

show variables;

 

4)  查看表以及视图的状态

show table status

 

5) 查看索引相关信息

show INDEX from employees;

查看表employees的索引信息

 

MySQL调优2

上一篇:plsql的安装教程


下一篇:flask+flask_sqlalchemy+flask_script+flask_migrate 创建一个flask项目(一)