小小编
在如何调优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的索引信息
