MySQL调优2

2021-07-07 06:11:33

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

MySQL调优的维度

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

2、测试数据准备

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

cd /home/files/test_db-master

执行 mysql -uroot -pxxx < employees.sql

检查导入是否正常

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慢查询参数

2） MySQL使用方式

方式二

这里使用第二种方式

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;
可以看到有慢查询日志了

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

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

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

show variables like ‘%slow_query_log_file%‘

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

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

3、Explian使用

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

Explian结果输出字段

实例1

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

结果：

这里id都是1，从上到下依次执行；如果id值不同，id值越大，越早执行。

4、Explain可视化工具

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

4、SQL性能分析

SQL内部分析性能包括

SHOW PROFILE

INFORMATION_SCHEMA.PROFILING

PERFORMANCE_SCHEMA （MySQL建议使用)

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

查看是否支持

SELECT @@have_profiling;

YES说明支持。

查看是否开启

select @@profiling;

0说明没有开启

开启profiling

set @@profiling = 1;

默认之后记录15条的历史

设置历史条数为100

set profiling_history_size = 100;

查看profile

show profiles;

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

可以发现Query_ID 为49.

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

可以发现这个步骤执行时间最久。 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;

显示profile

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

显示结果如下：

3） PERFORMANCE_SCHEMA

查看是否开启

select * from performance_schema.setup_actors;

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

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

执行如下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%‘

　　结果如下图：

分析执行的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

开启 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表常用的操作

2) 查看服务器相关信息

show status

3) 查看MySQL的变量

show variables；

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

show table status

5) 查看索引相关信息

show INDEX from employees;

查看表employees的索引信息

MySQL调优2

码农公寓

相关文章