mysql pool-recycle_MySQL 性能优化

1. SQL语句优化

1.1 Sakila示例数据库安装

2、解压后会得到三个文件：

sakila-schema.sql：用于创建表结构和数据库

sakila-data.sql：用于插入数据

sakila.mwb

3、进入 MySQL 中，创建数据库、数据表以及插入数据：

# 将路径替换成你自己的路径

mysql -u root -p

# 创建表结构

source C:/Users/hj/Desktop/sakila-db/sakila-db/sakila-schema.sql

# 插入数据

source C:/Users/hj/Desktop/sakila-db/sakila-db/sakila-data.sql

1.2 慢查询

如何发现有问题的 SQL？使用 mysql 慢查询日志对有效率问题的 SQL 进行监控：

# 查看慢查询日志是否开启，off 为关闭

show variables like 'slow_query_log';

# 查看 log_queries_not_using_indexes 是否开启

show variables like '%log%';

# 开启 log_queries_not_using_indexes

set global log_queries_not_using_indexes=on;

# 开启慢查询日志

set global slow_query_log=on;

# 查看慢查日志记录时间，0 表示时刻都会记录，生产环境中不能为 0

show variables like 'long_query_time';

# 设置查询记录的时间(即查询超过 0s 的SQL语句都会被记录在慢查日志中)

set global long_query_time=0;

# 查看慢查询日志存储位置

show variables like 'slow_query_log_file';

# 指定慢查询日志存储位置

set global show_query_log_file='/var/lib/mysql/homestead-slow.log';

# 记录没有使用索引的sql

set global log_queries_not_using_indexes=on;

设置慢查日志记录大致流程如下：

查看慢查询日志是否开启 --> 查看 log_queries_not_using_indexes 是否开启 --> 开启慢查询日志 --> 开启 log_queries_not_using_indexes --> 设置慢查记录时间(查询时间)--> 执行 SQL 语句检查 --> 查看慢查日志所在位置 -->查看慢查日志内容

慢查日志内容

TCP Port: 3306, Named Pipe: MySQL

Time Id Command Argument

# Time: 2019-07-01T03:03:13.305562Z

# User@Host: root[root] @ localhost [::1] Id: 8

# Query_time: 0.000434 Lock_time: 0.000135 Rows_sent: 10 Rows_examined: 10

use sakila;

SET timestamp=1561950193;

select * from film limit 10;

1.3 慢查日志分析工具

mysqldumpslow(官方)

MySQL 官方提供的慢查日志分析工具，安装 MySQL 后就自带的。

# 查看参数列表，用的比较多的参数有 -s、-t 等

mysqldumpslow -h;

# 分析慢查询日志中前三条比较慢的sql

mysqldumpslow -t 3 /var/lib/mysql/homestead-slow.log | more ;

# 输出样式效果(次数、时间、锁、行、操作用户以及查询命令)

Count:1 Time:0.00s Lock=0.00s Rows=10.0

root[rppt]@localhost

select * from store

1.4. pt-query-digest 工具

安装

yum install perl-DBI

rpm -ivh MySQL-shared-compat-5.6.31-1.el6.x86_64.rpm

yum install perl-DBD-MySQL

yum install perl-IO-Socket-SSL

yum install perl-Time-HiRes

rpm -ivh perl-TermReadKey-2.30-3.el6.rfx.x86_64.rpm

rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm

# 需要的rpm包可以从这里下载：http://rpm.pbone.net/

# 输出到文件

pt-query-digest slow-log > slow_log.report

# 输出到数据库

pt-query-digest slow.log -review \

h=127.0.0.1.D=test, p=root, P=3306,u=root,t=query_review \

--cretae-reviewtable \

--review-history t=hostname_slow

基本使用：

# 查看参数列表

pt-query-digest --help

# 分析慢查询日志中前三条比较慢的sql

pt-query-digest /var/lib/mysql/homestead-slow.log | more

# 输出分为三部分

1.显示除了日志的时间范围，以及总的sql数量和不同的sql数量

2.Response Time:响应时间占比 Calls:sql执行次数

3.sql的具体日志

剖析报告

报告中包括一共有 9.10k 个操作，1.17K 个语句，列举出语句执行时间，锁时间，剖析报告覆盖的时间等。

如何通过慢查询日志发现有问题的SQL？

# 查询次数多且每次查询占用时间长的SQL

通常为pt-query-digest分析的前几个查询

# IO大的SQL(数据库主要瓶颈出现在IO层次)

注意pt-query-digest分析中的Rows examine项

# 未命中索引的SQL

注意pt-query-digest分析中的Rows examine和Rows Send的对比

Tips：pt-query-digest 只支持 Linux

参考文章：

1.5 通过 explain 查询和分析 SQL 的执行计划