mysql数据库使用分析工具进行慢查询分析

优化sql语句步骤:
1.发现问题
2.分析执行计划
3.优化索引
4.改写sql
(再达不到优化效果的话进行数据库分库分表)

1.发现问题途径

1.用户上报性能问题
2.慢查询日志发现问题SQL
3.数据库实时监控长时间运行的SQL

2.设置MYSQL

set global slow_query_log = on/off （慢查询开关）
set global slow_query_log_file = /地址（慢查询地址）
set global long_query_time = xx.xx秒（超过XX秒会被记录）
set global log_queries_not_using_indexes = on/off （记录没有使用索引的慢查询）

3.分析慢查询日志

1.mysqldumpslow (mysql自带)
2.pt-query-digest

4.安装percona-toolkit-3.0.13

下载软件包:

wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-1.el7.x86_64.rpm

安装依赖包:
yum install -y perl-DBD-MySQL.x86_64 perl-DBI.x86 perl-Time-HiRes.x86_64 perl-IO-Socket-SSL.noarch perl-TermReadKey.x86_64 perl-Digest-MD5

安装
rpm -ivh percona-tookot-3.0.13-1.el7.x86_64.rpm

输入pt  按 tab 获取所有软件

5.慢查询日志设置与查询日志

查看各参数的值
show variables like 'slow_query_log ';
show variables like 'slow_query_log_file ';
show variables like 'long_query_time ';
show variables like 'log_queries_not_using_indexes ';

设置各参数的值
set global slow_query_log = on/off （慢查询开关）
set global slow_query_log_file = /地址 （慢查询地址）
set global long_query_time = xx.xx秒 （超过XX秒会被记录）
set global log_queries_not_using_indexes = on/off （记录没有使用索引的慢查询）

查看日志
mysql> show variables like 'slow_query_log_file';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
1 row in set (0.01 sec)

 more  /var/lib/mysql/localhost-slow.log
# Time: 2019-06-07T08:48:26.661099Z
# User@Host: root[root] @ localhost []  Id:    12
# Query_time: 0.001453  Lock_time: 0.000257 Rows_sent: 1  Rows_examined: 0
SET timestamp=1559897306;
EXPLAIN select * from student;

其中
query_time(执行sql时间)
lock_time(锁的时间)
rows_sent(查询返回的行数)
Rows_examined(扫描的行数)

6.使用工具分析日志

1.使用mysqldumpslow 分析
 mysqldumpslow /var/lib/mysql/localhost-slow.log

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
show variables  like  'S'

其中  
count(执行次数)
time(执行时间)
lock(锁的时间)
rows(行数)

2.使用pt-query-digest 分析

 pt-query-digest /var/lib/mysql/localhost-slow.log

 Query 1: 0.00 QPS, 0.00x concurrency, ID 0x751417D45B8E80EE5CBA2034458B5BC9 at byte 1475
 Scores: V/M = 0.00
 Time range: 2019-06-07T08:23:55 to 2019-06-07T08:48:11
 Attribute    pct   total     min     max     avg     95%  stddev  median
 ============ === ======= ======= ======= ======= ======= ======= =======
 Count         15       4
 Exec time     29    24ms   992us    11ms     6ms    11ms     4ms     9ms
 Lock time     41    15ms   423us     8ms     4ms     8ms     3ms     7ms
 Rows sent     52      20       5       5       5       5       0       5
 Rows examine   4     104      26      26      26      26       0      26
 Query size     7      56      14      14      14      14       0      14
 String:
 Databases    data
 Hosts        localhost
 Users        root
#Query_time distribution
  1us
  10us
 100us
   1ms  ################################################################
  10ms  #####################
 100ms
    1s
  10s+
show databases\G

6.实时监控长时间运行的SQL

select id,user,host,db,command,time,state,info 
from information_schema.PROCESSLIST 
WHERE TIME>=60
(sql执行时间大于60秒的SQL)

7.分析执行计划

为什么分析执行计划
1.SQL如何访问表中的数据
2.了解SQL如何使用表中的索引
3.了解SQL所使用的查询类型

获取执行计划 EXPLAIN select * from XXX

8.分析执行计划

id列

为数字或者为no
id列相同时由上到下执行
id不同时,由大到小

select_type列

子查询
simple 不包含子查询或者 UNION查询
primary 查询中如果包含任何子查询,最外层的标记为primary
subquery 子查询
dependent subquery （依赖关系子查询）

UNION查询
union union查询的第二条或者之后的
dependent union （依赖关系子查询 ,union 语句作为子查询,union查询的第二条或者之后的）
union result 产生的结果集
derived 出现在from子句中的子查询

table列

输出表的名称
<union M,N> 由ID为M，N union后产生的结果集
<derived N>/<subquery N> 由ID为N的查询产生的结果集

partitions列

查分区表

type 列（性能从高到低）

system (性能高)
const连接类型,当查询表只有一行时
const
表中值有且只有一行匹配,或者利用where 查询某个常量的值,主键或唯一索引查询是效率最高的方式
eq_ref(常出现在join查询 )
唯一索引或主键索引查找,对每个索引键,表中只有一条记录与之匹配
ref
非唯一索引查找,返回匹配某个单独值的所有行
ref_or_null
类似于ref 增加了null值的查询
index_merge
索引合并
range
索引范围素描(between , > , <)
index
full index scan 全索引扫描
all (性能最低)
全表扫描

key列

(possible_keys)可能会被使用到的索引
(keys)会被使用到的索引
(key_len) 实际使用索引的最大长度 (字节)

ref列

列出哪些列被用于索引查找

rows列

预估扫描行数

filtered列

返回行数与扫描行数的百分比(越高就性能越高)

extra列

distinct 找到第一个值后立刻停止找同值的动作
not exists 使用not exists优化使用不存在于某个条件的查询
using filesort 常见使用order by 或者 group by 查找
using index 使用了覆盖索引(直接通过索引获取数据,不访问表)
using temporary使用了临时表
using where 使用了where
select tables optimized away 操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）。

mysql数据库 使用分析工具 进行慢查询分析