您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记
5-4 MySQL的查询优化考察点
发布时间：2020-10-05 18:50:07编辑：雪饮阅读（）
pt-query-digest
pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOW PROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析.
安装pt-query-digest
要使用该工具前需要先安装该工具
首先将该工具下载
[root@localhost src]# wget percona.com/get/pt-query-digest
--2020-10-05 06:13:34-- http://percona.com/get/pt-query-digest
Resolving percona.com (percona.com)... 74.121.199.234
Connecting to percona.com (percona.com)|74.121.199.234|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://www.percona.com/get/pt-query-digest [following]
--2020-10-05 06:13:37-- https://www.percona.com/get/pt-query-digest
Resolving www.percona.com (www.percona.com)... 74.121.199.234
Connecting to www.percona.com (www.percona.com)|74.121.199.234|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 529350 (517K)
Saving to: ‘pt-query-digest’
100%[=========================================================================================================>] 529,350 27.9KB/s in 17s
2020-10-05 06:13:56 (31.2 KB/s) - ‘pt-query-digest’ saved [529350/529350]
然后给用户（u）身份以执行（x）权限
[root@localhost src]# chmod u+x pt-query-digest
然后将其移动到用户默认命令集所在目录
[root@localhost src]# mv ./pt-query-digest /usr/bin/
然后安装该工具所需依赖
[root@localhost src]# yum install perl-DBI perl-Digest-MD5
Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Loading mirror speeds from cached hostfile
* base: mirrors.neusoft.edu.cn
* extras: mirrors.163.com
* updates: mirrors.163.com
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed
--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl >= 5.008_001 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl >= 5.008 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl >= 5.004 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(warnings) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(vars) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(utf8) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(strict) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(constant) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(base) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(UNIVERSAL) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Tie::Hash) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Symbol) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Storable) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Scalar::Util) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Math::BigInt) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(IPC::Open3) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(IO::Select) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(IO::File) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(IO::Dir) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Getopt::Long) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(File::Spec) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(File::Basename) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Fcntl) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Exporter) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Errno) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(DynaLoader) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Data::Dumper) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Cwd) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Config) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(Carp) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(:MODULE_COMPAT_5.16.3) for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: /usr/bin/perl for package: perl-DBI-1.627-4.el7.x86_64
---> Package perl-Digest-MD5.x86_64 0:2.52-3.el7 will be installed
--> Processing Dependency: perl(Digest::base) >= 1.00 for package: perl-Digest-MD5-2.52-3.el7.x86_64
--> Running transaction check
---> Package perl.x86_64 4:5.16.3-295.el7 will be installed
--> Processing Dependency: perl-libs = 4:5.16.3-295.el7 for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(Socket) >= 1.3 for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl-macros for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl-libs for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(threads::shared) for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(threads) for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(Time::Local) for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(Time::HiRes) for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(Socket) for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(Pod::Simple::XHTML) for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(Pod::Simple::Search) for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(Filter::Util::Call) for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(File::Temp) for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: perl(File::Path) for package: 4:perl-5.16.3-295.el7.x86_64
--> Processing Dependency: libperl.so()(64bit) for package: 4:perl-5.16.3-295.el7.x86_64
---> Package perl-Carp.noarch 0:1.26-244.el7 will be installed
---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed
---> Package perl-Digest.noarch 0:1.17-245.el7 will be installed
---> Package perl-Exporter.noarch 0:5.68-3.el7 will be installed
---> Package perl-Getopt-Long.noarch 0:2.40-3.el7 will be installed
--> Processing Dependency: perl(Pod::Usage) >= 1.14 for package: perl-Getopt-Long-2.40-3.el7.noarch
--> Processing Dependency: perl(Text::ParseWords) for package: perl-Getopt-Long-2.40-3.el7.noarch
---> Package perl-PathTools.x86_64 0:3.40-5.el7 will be installed
---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed
--> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch
---> Package perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 will be installed
---> Package perl-Storable.x86_64 0:2.45-3.el7 will be installed
---> Package perl-constant.noarch 0:1.27-2.el7 will be installed
--> Running transaction check
---> Package perl-File-Path.noarch 0:2.09-2.el7 will be installed
---> Package perl-File-Temp.noarch 0:0.23.01-3.el7 will be installed
---> Package perl-Filter.x86_64 0:1.49-3.el7 will be installed
---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed
--> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
--> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
--> Processing Dependency: perl(Encode) for package: perl-IO-Compress-2.061-2.el7.noarch
---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed
---> Package perl-Pod-Simple.noarch 1:3.28-4.el7 will be installed
--> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.28-4.el7.noarch
---> Package perl-Pod-Usage.noarch 0:1.63-3.el7 will be installed
--> Processing Dependency: perl(Pod::Text) >= 3.15 for package: perl-Pod-Usage-1.63-3.el7.noarch
--> Processing Dependency: perl-Pod-Perldoc for package: perl-Pod-Usage-1.63-3.el7.noarch
---> Package perl-Socket.x86_64 0:2.010-5.el7 will be installed
---> Package perl-Text-ParseWords.noarch 0:3.29-4.el7 will be installed
---> Package perl-Time-HiRes.x86_64 4:1.9725-3.el7 will be installed
---> Package perl-Time-Local.noarch 0:1.2300-2.el7 will be installed
---> Package perl-libs.x86_64 4:5.16.3-295.el7 will be installed
---> Package perl-macros.x86_64 4:5.16.3-295.el7 will be installed
---> Package perl-threads.x86_64 0:1.87-4.el7 will be installed
---> Package perl-threads-shared.x86_64 0:1.43-6.el7 will be installed
--> Running transaction check
---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed
---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed
---> Package perl-Encode.x86_64 0:2.51-7.el7 will be installed
---> Package perl-Pod-Escapes.noarch 1:1.04-295.el7 will be installed
---> Package perl-Pod-Perldoc.noarch 0:3.20-4.el7 will be installed
--> Processing Dependency: perl(parent) for package: perl-Pod-Perldoc-3.20-4.el7.noarch
--> Processing Dependency: perl(HTTP::Tiny) for package: perl-Pod-Perldoc-3.20-4.el7.noarch
---> Package perl-podlators.noarch 0:2.5.1-3.el7 will be installed
--> Running transaction check
---> Package perl-HTTP-Tiny.noarch 0:0.033-3.el7 will be installed
---> Package perl-parent.noarch 1:0.225-244.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===================================================================================================================================================
Package Arch Version Repository Size
===================================================================================================================================================
Installing:
perl-DBI x86_64 1.627-4.el7 base 802 k
perl-Digest-MD5 x86_64 2.52-3.el7 base 30 k
Installing for dependencies:
perl x86_64 4:5.16.3-295.el7 base 8.0 M
perl-Carp noarch 1.26-244.el7 base 19 k
perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k
perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k
perl-Data-Dumper x86_64 2.145-3.el7 base 47 k
perl-Digest noarch 1.17-245.el7 base 23 k
perl-Encode x86_64 2.51-7.el7 base 1.5 M
perl-Exporter noarch 5.68-3.el7 base 28 k
perl-File-Path noarch 2.09-2.el7 base 26 k
perl-File-Temp noarch 0.23.01-3.el7 base 56 k
perl-Filter x86_64 1.49-3.el7 base 76 k
perl-Getopt-Long noarch 2.40-3.el7 base 56 k
perl-HTTP-Tiny noarch 0.033-3.el7 base 38 k
perl-IO-Compress noarch 2.061-2.el7 base 260 k
perl-Net-Daemon noarch 0.48-5.el7 base 51 k
perl-PathTools x86_64 3.40-5.el7 base 82 k
perl-PlRPC noarch 0.2020-14.el7 base 36 k
perl-Pod-Escapes noarch 1:1.04-295.el7 base 51 k
perl-Pod-Perldoc noarch 3.20-4.el7 base 87 k
perl-Pod-Simple noarch 1:3.28-4.el7 base 216 k
perl-Pod-Usage noarch 1.63-3.el7 base 27 k
perl-Scalar-List-Utils x86_64 1.27-248.el7 base 36 k
perl-Socket x86_64 2.010-5.el7 base 49 k
perl-Storable x86_64 2.45-3.el7 base 77 k
perl-Text-ParseWords noarch 3.29-4.el7 base 14 k
perl-Time-HiRes x86_64 4:1.9725-3.el7 base 45 k
perl-Time-Local noarch 1.2300-2.el7 base 24 k
perl-constant noarch 1.27-2.el7 base 19 k
perl-libs x86_64 4:5.16.3-295.el7 base 689 k
perl-macros x86_64 4:5.16.3-295.el7 base 44 k
perl-parent noarch 1:0.225-244.el7 base 12 k
perl-podlators noarch 2.5.1-3.el7 base 112 k
perl-threads x86_64 1.87-4.el7 base 49 k
perl-threads-shared x86_64 1.43-6.el7 base 39 k
Transaction Summary
===================================================================================================================================================
Install 2 Packages (+34 Dependent packages)
Total download size: 13 M
Installed size: 39 M
Is this ok [y/d/N]: y
Downloading packages:
(1/36): perl-Carp-1.26-244.el7.noarch.rpm | 19 kB 00:00:00
(2/36): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm | 32 kB 00:00:00
(3/36): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm | 57 kB 00:00:01
(4/36): perl-Digest-MD5-2.52-3.el7.x86_64.rpm | 30 kB 00:00:01
(5/36): perl-Digest-1.17-245.el7.noarch.rpm | 23 kB 00:00:03
(6/36): perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00:03
(7/36): perl-DBI-1.627-4.el7.x86_64.rpm | 802 kB 00:00:05
(8/36): perl-Exporter-5.68-3.el7.noarch.rpm | 28 kB 00:00:01
(9/36): perl-5.16.3-295.el7.x86_64.rpm | 8.0 MB 00:00:06
(10/36): perl-File-Temp-0.23.01-3.el7.noarch.rpm | 56 kB 00:00:00
(11/36): perl-File-Path-2.09-2.el7.noarch.rpm | 26 kB 00:00:00
(12/36): perl-Encode-2.51-7.el7.x86_64.rpm | 1.5 MB 00:00:04
(13/36): perl-Getopt-Long-2.40-3.el7.noarch.rpm | 56 kB 00:00:00
(14/36): perl-HTTP-Tiny-0.033-3.el7.noarch.rpm | 38 kB 00:00:00
(15/36): perl-Net-Daemon-0.48-5.el7.noarch.rpm | 51 kB 00:00:00
(16/36): perl-Filter-1.49-3.el7.x86_64.rpm | 76 kB 00:00:00
(17/36): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00:00
(18/36): perl-PathTools-3.40-5.el7.x86_64.rpm | 82 kB 00:00:00
(19/36): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:00:01
(20/36): perl-Pod-Perldoc-3.20-4.el7.noarch.rpm | 87 kB 00:00:00
(21/36): perl-Scalar-List-Utils-1.27-248.el7.x86_64.rpm | 36 kB 00:00:00
(22/36): perl-Storable-2.45-3.el7.x86_64.rpm | 77 kB 00:00:00
(23/36): perl-Pod-Escapes-1.04-295.el7.noarch.rpm | 51 kB 00:00:00
(24/36): perl-Pod-Usage-1.63-3.el7.noarch.rpm | 27 kB 00:00:00
(25/36): perl-Text-ParseWords-3.29-4.el7.noarch.rpm | 14 kB 00:00:00
(26/36): perl-Time-Local-1.2300-2.el7.noarch.rpm | 24 kB 00:00:00
(27/36): perl-Socket-2.010-5.el7.x86_64.rpm | 49 kB 00:00:00
(28/36): perl-Time-HiRes-1.9725-3.el7.x86_64.rpm | 45 kB 00:00:00
(29/36): perl-Pod-Simple-3.28-4.el7.noarch.rpm | 216 kB 00:00:01
(30/36): perl-constant-1.27-2.el7.noarch.rpm | 19 kB 00:00:00
(31/36): perl-libs-5.16.3-295.el7.x86_64.rpm | 689 kB 00:00:00
(32/36): perl-podlators-2.5.1-3.el7.noarch.rpm | 112 kB 00:00:00
(33/36): perl-parent-0.225-244.el7.noarch.rpm | 12 kB 00:00:00
(34/36): perl-threads-shared-1.43-6.el7.x86_64.rpm | 39 kB 00:00:00
(35/36): perl-threads-1.87-4.el7.x86_64.rpm | 49 kB 00:00:00
(36/36): perl-macros-5.16.3-295.el7.x86_64.rpm | 44 kB 00:00:01
---------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.1 MB/s | 13 MB 00:00:11
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : 1:perl-parent-0.225-244.el7.noarch 1/36
Installing : perl-HTTP-Tiny-0.033-3.el7.noarch 2/36
Installing : perl-podlators-2.5.1-3.el7.noarch 3/36
Installing : perl-Pod-Perldoc-3.20-4.el7.noarch 4/36
Installing : 1:perl-Pod-Escapes-1.04-295.el7.noarch 5/36
Installing : perl-Text-ParseWords-3.29-4.el7.noarch 6/36
Installing : perl-Encode-2.51-7.el7.x86_64 7/36
Installing : perl-Pod-Usage-1.63-3.el7.noarch 8/36
Installing : 4:perl-libs-5.16.3-295.el7.x86_64 9/36
Installing : 4:perl-macros-5.16.3-295.el7.x86_64 10/36
Installing : perl-Storable-2.45-3.el7.x86_64 11/36
Installing : perl-Exporter-5.68-3.el7.noarch 12/36
Installing : perl-constant-1.27-2.el7.noarch 13/36
Installing : perl-Socket-2.010-5.el7.x86_64 14/36
Installing : perl-Time-Local-1.2300-2.el7.noarch 15/36
Installing : perl-Carp-1.26-244.el7.noarch 16/36
Installing : 4:perl-Time-HiRes-1.9725-3.el7.x86_64 17/36
Installing : perl-PathTools-3.40-5.el7.x86_64 18/36
Installing : perl-Scalar-List-Utils-1.27-248.el7.x86_64 19/36
Installing : perl-File-Temp-0.23.01-3.el7.noarch 20/36
Installing : perl-File-Path-2.09-2.el7.noarch 21/36
Installing : perl-threads-shared-1.43-6.el7.x86_64 22/36
Installing : perl-threads-1.87-4.el7.x86_64 23/36
Installing : perl-Filter-1.49-3.el7.x86_64 24/36
Installing : 1:perl-Pod-Simple-3.28-4.el7.noarch 25/36
Installing : perl-Getopt-Long-2.40-3.el7.noarch 26/36
Installing : 4:perl-5.16.3-295.el7.x86_64 27/36
Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 28/36
Installing : perl-Net-Daemon-0.48-5.el7.noarch 29/36
Installing : perl-Data-Dumper-2.145-3.el7.x86_64 30/36
Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 31/36
Installing : perl-IO-Compress-2.061-2.el7.noarch 32/36
Installing : perl-PlRPC-0.2020-14.el7.noarch 33/36
Installing : perl-Digest-1.17-245.el7.noarch 34/36
Installing : perl-Digest-MD5-2.52-3.el7.x86_64 35/36
Installing : perl-DBI-1.627-4.el7.x86_64 36/36
Verifying : perl-HTTP-Tiny-0.033-3.el7.noarch 1/36
Verifying : perl-threads-shared-1.43-6.el7.x86_64 2/36
Verifying : perl-Storable-2.45-3.el7.x86_64 3/36
Verifying : 1:perl-Pod-Escapes-1.04-295.el7.noarch 4/36
Verifying : perl-IO-Compress-2.061-2.el7.noarch 5/36
Verifying : perl-Exporter-5.68-3.el7.noarch 6/36
Verifying : perl-constant-1.27-2.el7.noarch 7/36
Verifying : perl-PathTools-3.40-5.el7.x86_64 8/36
Verifying : perl-Socket-2.010-5.el7.x86_64 9/36
Verifying : 1:perl-parent-0.225-244.el7.noarch 10/36
Verifying : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 11/36
Verifying : perl-Net-Daemon-0.48-5.el7.noarch 12/36
Verifying : perl-Digest-MD5-2.52-3.el7.x86_64 13/36
Verifying : 4:perl-libs-5.16.3-295.el7.x86_64 14/36
Verifying : perl-File-Temp-0.23.01-3.el7.noarch 15/36
Verifying : 1:perl-Pod-Simple-3.28-4.el7.noarch 16/36
Verifying : perl-Time-Local-1.2300-2.el7.noarch 17/36
Verifying : perl-DBI-1.627-4.el7.x86_64 18/36
Verifying : 4:perl-macros-5.16.3-295.el7.x86_64 19/36
Verifying : 4:perl-5.16.3-295.el7.x86_64 20/36
Verifying : perl-Carp-1.26-244.el7.noarch 21/36
Verifying : perl-Data-Dumper-2.145-3.el7.x86_64 22/36
Verifying : 4:perl-Time-HiRes-1.9725-3.el7.x86_64 23/36
Verifying : perl-Scalar-List-Utils-1.27-248.el7.x86_64 24/36
Verifying : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 25/36
Verifying : perl-Pod-Usage-1.63-3.el7.noarch 26/36
Verifying : perl-PlRPC-0.2020-14.el7.noarch 27/36
Verifying : perl-Encode-2.51-7.el7.x86_64 28/36
Verifying : perl-Pod-Perldoc-3.20-4.el7.noarch 29/36
Verifying : perl-podlators-2.5.1-3.el7.noarch 30/36
Verifying : perl-File-Path-2.09-2.el7.noarch 31/36
Verifying : perl-threads-1.87-4.el7.x86_64 32/36
Verifying : perl-Filter-1.49-3.el7.x86_64 33/36
Verifying : perl-Getopt-Long-2.40-3.el7.noarch 34/36
Verifying : perl-Text-ParseWords-3.29-4.el7.noarch 35/36
Verifying : perl-Digest-1.17-245.el7.noarch 36/36
Installed:
perl-DBI.x86_64 0:1.627-4.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7
Dependency Installed:
perl.x86_64 4:5.16.3-295.el7 perl-Carp.noarch 0:1.26-244.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7
perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7 perl-Digest.noarch 0:1.17-245.el7
perl-Encode.x86_64 0:2.51-7.el7 perl-Exporter.noarch 0:5.68-3.el7 perl-File-Path.noarch 0:2.09-2.el7
perl-File-Temp.noarch 0:0.23.01-3.el7 perl-Filter.x86_64 0:1.49-3.el7 perl-Getopt-Long.noarch 0:2.40-3.el7
perl-HTTP-Tiny.noarch 0:0.033-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7 perl-Net-Daemon.noarch 0:0.48-5.el7
perl-PathTools.x86_64 0:3.40-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7 perl-Pod-Escapes.noarch 1:1.04-295.el7
perl-Pod-Perldoc.noarch 0:3.20-4.el7 perl-Pod-Simple.noarch 1:3.28-4.el7 perl-Pod-Usage.noarch 0:1.63-3.el7
perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 perl-Socket.x86_64 0:2.010-5.el7 perl-Storable.x86_64 0:2.45-3.el7
perl-Text-ParseWords.noarch 0:3.29-4.el7 perl-Time-HiRes.x86_64 4:1.9725-3.el7 perl-Time-Local.noarch 0:1.2300-2.el7
perl-constant.noarch 0:1.27-2.el7 perl-libs.x86_64 4:5.16.3-295.el7 perl-macros.x86_64 4:5.16.3-295.el7
perl-parent.noarch 1:0.225-244.el7 perl-podlators.noarch 0:2.5.1-3.el7 perl-threads.x86_64 0:1.87-4.el7
perl-threads-shared.x86_64 0:1.43-6.el7
Complete!
工具的使用
这里以慢查询日志文件为例，首先开启你的mysql服务器的慢查询日志让其可以生成日志文件。
开启慢查询日志如：
然后重启后可以看到慢查询日志文件路径
然后我这里用了两个表都是4000多条数据，然后直接积卡尔基。
select * from phome_ecms_news,phome_ecms_news_data_1;
那么就是要查询16亿的数据，后来我实在等不及了就手动关闭了。
那么慢查询日志就有了
然后把该日志文件传递到Linux中用pt-query-digest直接分析慢查询文件如：
[root@localhost src]# pt-query-digest DESKTOP-BG9HNHK-slow.log
# 200ms user time, 20ms system time, 22.02M rss, 184.65M vsz
# Current date: Mon Oct 5 06:34:59 2020
# Hostname: localhost.localdomain
# Files: DESKTOP-BG9HNHK-slow.log
# Overall: 0 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Query size 390 390 390 390 390 0 390
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# =========== =========== =========== =========== =========== ===== ======
Profiling
Profiling开启后可以使得mysql记录sql查询概况与一个表中。
开启profiling
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (2.34 sec)
然后随便查一个sql
mysql> select * from test;
+----+------+------+------+----------+
| id | a | b | c | name |
+----+------+------+------+----------+
| 1 | 1 | 2 | 3 | |
| 2 | 11 | 22 | 33 | |
| 3 | NULL | NULL | NULL | 张三 |
| 4 | NULL | NULL | NULL | 李四 |
| 5 | NULL | NULL | NULL | 李wu |
| 6 | NULL | NULL | NULL | 李wang |
| 7 | NULL | NULL | NULL | 李wang2 |
| 8 | NULL | NULL | NULL | wang2 |
+----+------+------+------+----------+
然后就可以看到该条sql的查询概况了
mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.43060825 | select * from test |
+----------+------------+--------------------+
1 row in set, 1 warning (0.03 sec)
如果要查看概况表中某条查询的历程，则如
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000037 |
| checking permissions | 0.000003 |
| Opening tables | 0.000676 |
| init | 0.000011 |
| System lock | 0.000004 |
| optimizing | 0.000002 |
| statistics | 0.000008 |
| preparing | 0.000005 |
| executing | 0.000001 |
| Sending data | 0.000031 |
| end | 0.000002 |
| query end | 0.000003 |
| closing tables | 0.000004 |
| freeing items | 0.000020 |
| cleaning up | 0.000005 |
+----------------------+----------+
15 rows in set, 1 warning (0.30 sec)
这里最后一个参数“1”就是上面概况表中的Query_ID
查看mysql线程列表
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 9 | root | localhost:61395 | xy | Query | 2 | starting | show processlist |
+----+------+-----------------+------+---------+------+----------+------------------+
1 row in set (2.27 sec)
出类拔萃的desc
Desc命令可以像explain一样直接调出某条sql的查询计划
mysql> desc select * from test;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.19 sec)
关键字词：mysql,desc,explain,pt-query-digest,Profiling,慢查询,processlist
下一篇：pdo异常处理