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 的执行计划