MySQL中查詢?nèi)罩九c慢查詢?nèi)罩镜幕緦W(xué)習(xí)教程
更新時(shí)間：2015年12月23日 16:33:42 作者：I’m Me!
這篇文章主要介紹了MySQL中查詢?nèi)罩九c慢查詢?nèi)罩镜幕緦W(xué)習(xí)教程,文中還提到了MySQL自帶的Mysqldumpslow日志分析工具的使用,需要的朋友可以參考下
一、查詢?nèi)罩?
查詢?nèi)罩居涗汳ySQL中所有的query，通過"--log[=file_name]"來打開該功能。由于記錄了所有的query，包括所有的select，體積比較大，開啟后對(duì)性能也有比較大的影響，所以請(qǐng)大家慎用該功能。一般只用于跟蹤某些特殊的sql性能問題才會(huì)短暫打開該功能。默認(rèn)的查詢?nèi)罩疚募麨椋篽ostname.log.
----默認(rèn)情況下查看是否啟用查詢?nèi)罩荆?br />
[root@node4 mysql5.5]# service mysql start
Starting MySQL.... [ OK ]
[root@node4 mysql5.5]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log';
+--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | back_log | 50 | | general_log | OFF | | innodb_locks_unsafe_for_binlog | OFF | | log | OFF | | relay_log | | | slow_query_log | OFF | | sync_binlog | 0 | | sync_relay_log | 0 | +--------------------------------+-------+ 8 rows in set (0.00 sec)
----備注：log和general_log這兩個(gè)參數(shù)是兼容的。而默認(rèn)的情況下查詢?nèi)罩臼遣婚_啟的
----使用下面的命令是開啟查詢?nèi)罩?br />mysql> set global log=1;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show variables like '%log';
+--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | back_log | 50 | | general_log | ON | | innodb_locks_unsafe_for_binlog | OFF | | log | ON | | relay_log | | | slow_query_log | OFF | | sync_binlog | 0 | | sync_relay_log | 0 | +--------------------------------+-------+ 8 rows in set (0.00 sec) ----其中l(wèi)og參數(shù)是過時(shí)的，在啟動(dòng)選項(xiàng)中使用log參數(shù)的話，會(huì)在err日志中顯示出來。 ----修改my.cnf文件，添加log的參數(shù)設(shè)置
[root@node4 mysql5.5]# vi my.cnf [root@node4 mysql5.5]# cat ./my.cnf |grep '^log=' log=/tmp/mysqlgen.log ----清空err日志 [root@node4 mysql5.5]# cat /dev/null > /tmp/mysql3306.err [root@node4 mysql5.5]# ll /tmp/mysql3306.err -rw-rw---- 1 mysql root 0 Jul 31 07:50 /tmp/mysql3306.err [root@node4 mysql5.5]# service mysql start
Starting MySQL... [ OK ] ----啟動(dòng)數(shù)據(jù)庫(kù)后查看err日志的內(nèi)容
[root@node4 mysql5.5]# cat /tmp/mysql3306.err
130731 07:51:32 mysqld_safe Starting mysqld daemon with databases from /opt/mysql5.5/data 130731 7:51:32 [Warning] The syntax '--log' is deprecated and will be removed in a future release. Please use '--general-log'/'--general-log-file' instead. 130731 7:51:33 InnoDB: The InnoDB memory heap is disabled 130731 7:51:33 InnoDB: Mutexes and rw_locks use InnoDB's own implementation 130731 7:51:33 InnoDB: Compressed tables use zlib 1.2.3 130731 7:51:33 InnoDB: Initializing buffer pool, size = 128.0M 130731 7:51:33 InnoDB: Completed initialization of buffer pool 130731 7:51:33 InnoDB: highest supported file format is Barracuda. 130731 7:51:33 InnoDB: Waiting for the background threads to start 130731 7:51:34 InnoDB: 1.1.8 started; log sequence number 1625855 130731 7:51:34 [Note] Event Scheduler: Loaded 0 events 130731 7:51:34 [Note] /opt/mysql5.5/bin/mysqld: ready for connections. Version: '5.5.22-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution ----使用最新的參數(shù) ----general_log和general_log_file。
[root@node4 mysql5.5]# service mysql stop
Shutting down MySQL. [ OK ]
[root@node4 mysql5.5]# vi my.cnf [root@node4 mysql5.5]# cat ./my.cnf |grep '^general'
general_log = 1 general_log_file = /tmp/mysqlgen.log
[root@node4 mysql5.5]# service mysql start
Starting MySQL... [ OK ]
[root@node4 mysql5.5]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log';
+--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | back_log | 50 | | general_log | ON | | innodb_locks_unsafe_for_binlog | OFF | | log | ON | | relay_log | | | slow_query_log | OFF | | sync_binlog | 0 | | sync_relay_log | 0 | +--------------------------------+-------+ 8 rows in set (0.04 sec)
mysql> show variables like '%file';
+---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | ft_stopword_file | (built-in) | | general_log_file | /tmp/mysqlgen.log | | init_file | | | local_infile | ON | | pid_file | /tmp/mysql3306.pid | | relay_log_info_file | relay-log.info | | slow_query_log_file | /opt/mysql5.5/data/node4-slow.log | +---------------------+-----------------------------------+ 7 rows in set (0.00 sec) ----在上面的操作中可以看到已經(jīng)啟用查詢?nèi)罩荆⑶椅募夸浭?tmp/mysqlgen.log。 ----查詢?nèi)罩居涗浟四男〇|西？
進(jìn)行下面的查詢
mysql> show databases;
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | test2 | +--------------------+ 5 rows in set (0.08 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> use test2;
Database changed
mysql> show tables;
+-----------------+ | Tables_in_test2 | +-----------------+ | course | | jack | | sc | | student | | t | | teacher | +-----------------+ 6 rows in set (0.07 sec)
mysql> drop table t;
Query OK, 0 rows affected (0.13 sec)
mysql> select * from sc;
Empty set (0.04 sec) ----可以看到上面的操作都記錄在了mysqlgen.log里面。
[root@node4 ~]# tail -f /tmp/mysqlgen.log
/opt/mysql5.5/bin/mysqld, Version: 5.5.22-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
130731 7:55:41 1 Query show databases
130731 7:55:56 1 Query SELECT DATABASE()
1 Init DB test
130731 7:55:59 1 Query show tables
130731 7:56:19 1 Query SELECT DATABASE()
1 Init DB test2
130731 7:56:23 1 Query show tables
130731 7:56:27 1 Query drop table t
130731 7:56:39 1 Query select * from sc
二、慢查詢?nèi)罩?br />
顧名思義，慢查詢?nèi)罩局杏涗浀氖菆?zhí)行時(shí)間較長(zhǎng)的query，也就是我們常說的slow query，通過設(shè)--log-slow-queries[=file_name]來打開該功能并設(shè)置記錄位置和文件名，默認(rèn)文件名為hostname-slow.log，默認(rèn)目錄也是數(shù)據(jù)目錄。
慢查詢?nèi)罩静捎玫氖呛?jiǎn)單的文本格式，可以通過各種文本編輯器查看其中的內(nèi)容。其中記錄了語句執(zhí)行的時(shí)刻，執(zhí)行所消耗的時(shí)間，執(zhí)行用戶，連接主機(jī)等相關(guān)信息。MySQL還提供了專門用來分析滿查詢?nèi)罩镜墓ぞ叱绦騧ysqlslowdump，用來幫助數(shù)據(jù)庫(kù)管理人員解決可能存在的性能問題。
----使用log_slow_queries參數(shù)打開慢查詢，由于該參數(shù)已經(jīng)過時(shí)，因此在err日志中將出現(xiàn)提示信息
----修改my.cnf文件，添加log_slow_queries參數(shù) [root@node4 ~]# vi /opt/mysql5.5/my.cnf [root@node4 ~]# cat /opt/mysql5.5/my.cnf |grep '^log_slow' log_slow_queries = /tmp/mysqlslow.log ----清空err日志內(nèi)容： [root@node4 ~]# cat /dev/null > /tmp/mysql3306.err [root@node4 ~]# service mysql start
Starting MySQL.... [ OK ]
----查看err日志的信息 [root@node4 data]# tail -f /tmp/mysql3306.err
02:26:28 mysqld_safe Starting mysqld daemon with databases from /opt/mysql5.5/data 2:26:28 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead. 2:26:28 [Warning] You need to use --log-bin to make --binlog-format work. 2:26:28 InnoDB: The InnoDB memory heap is disabled 2:26:28 InnoDB: Mutexes and rw_locks use InnoDB's own implementation 2:26:28 InnoDB: Compressed tables use zlib 1.2.3 2:26:28 InnoDB: Initializing buffer pool, size = 128.0M 2:26:28 InnoDB: Completed initialization of buffer pool 2:26:28 InnoDB: highest supported file format is Barracuda. 2:26:28 InnoDB: Waiting for the background threads to start 2:26:30 InnoDB: 1.1.8 started; log sequence number 3069452 2:26:30 [Note] Event Scheduler: Loaded 0 events 2:26:30 [Note] /opt/mysql5.5/bin/mysqld: ready for connections. Version: '5.5.22-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
----使用slow_query_log和slow_query_log_file [root@node4 ~]# vi /opt/mysql5.5/my.cnf [root@node4 ~]# cat /opt/mysql5.5/my.cnf |grep '^slow_query'
slow_query_log = 1 slow_query_log_file = /tmp/mysqlslow.log1
[root@node4 ~]# service mysql start
Starting MySQL... [ OK ]
[root@node4 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%slow%';
+---------------------+---------------------+ | Variable_name | Value | +---------------------+---------------------+ | log_slow_queries | ON | | slow_launch_time | 10 | | slow_query_log | ON | | slow_query_log_file | /tmp/mysqlslow.log1 | +---------------------+---------------------+ rows in set (0.00 sec)
----關(guān)于slow_launch_time參數(shù)，首先修改一下參數(shù)值 mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%long_query%';
+-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ row in set (0.00 sec)
----進(jìn)行一下相關(guān)操作，查看/tmp/mysqlslow.log1的內(nèi)容
mysql> select database();
+------------+ | database() | +------------+ | NULL | +------------+ row in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table t as select * from information_schema.tables;
Query OK, 85 rows affected (0.38 sec) Records: 85 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 85 rows affected (0.05 sec) Records: 85 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 170 rows affected (0.03 sec) Records: 170 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 340 rows affected (0.05 sec) Records: 340 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 680 rows affected (0.08 sec) Records: 680 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 1360 rows affected (0.29 sec) Records: 1360 Duplicates: 0 Warnings: 0
mysql> insert into t select * from t;
Query OK, 2720 rows affected (1.49 sec) Records: 2720 Duplicates: 0 Warnings: 0 ----在這里已經(jīng)超過1s了，查看/tmp/mysqlslow.log1
[root@node4 data]# tail -f /tmp/mysqlslow.log1
# Time: 130801 2:36:25 # User@Host: root[root] @ localhost [] # Query_time: 2.274219 Lock_time: 0.000322 Rows_sent: 0 Rows_examined: 5440 use test; SET timestamp=1375295785; insert into t select * from t; ----log_queries_not_using_indexes參數(shù)實(shí)驗(yàn)
mysql> show variables like '%indexes%';
+-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+ row in set (0.00 sec)
mysql> set log_queries_not_using_indexes = 1;
ERROR 1229 (HY000): Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set global log_queries_not_using_indexes = 1;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%indexes%';
+-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+ row in set (0.00 sec)
mysql> desc t;
+-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(2048) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ rows in set (0.05 sec)
----下面的命令是查看索引的 mysql> show index from t;
Empty set (0.01 sec)
mysql> select * from t where engine='xxx';
Empty set (0.18 sec) # Time: 130801 2:43:43 # User@Host: root[root] @ localhost [] # Query_time: 0.185773 Lock_time: 0.148868 Rows_sent: 0 Rows_examined: 5440 SET timestamp=1375296223; select * from t where engine='xxx';
PS：slow query log相關(guān)變量
命令行參數(shù)：
--log-slow-queries
指定日志文件存放位置，可以為空，系統(tǒng)會(huì)給一個(gè)缺省的文件host_name-slow.log
系統(tǒng)變量
log_slow_queries
指定日志文件存放位置，可以為空，系統(tǒng)會(huì)給一個(gè)缺省的文件host_name-slow.log
slow_query_log
slow quere log的開關(guān)，當(dāng)值為1的時(shí)候說明開啟慢查詢。
slow_query_log_file
指定日志文件存放位置，可以為空，系統(tǒng)會(huì)給一個(gè)缺省的文件host_name-slow.log
long_query_time
記錄超過的時(shí)間，默認(rèn)為10s
log_queries_not_using_indexes
log下來沒有使用索引的query，可以根據(jù)情況決定是否開啟
三、Mysqldumpslow
如果日志內(nèi)容很多，用眼睛一條一條看會(huì)累死，mysql自帶了分析的工具，使用方法如下：
[root@node4 data]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
您可能感興趣的文章:
- 深入mysql慢查詢?cè)O(shè)置的詳解
- 詳解MySql的慢查詢分析及開啟慢查詢?nèi)罩?/a>
- mysql 開啟慢查詢 如何打開mysql的慢查詢?nèi)罩居涗?/a>
- MySQL慢查詢查找和調(diào)優(yōu)測(cè)試
- MySQL慢查詢優(yōu)化之慢查詢?nèi)罩痉治龅膶?shí)例教程
- MySQL 開啟慢查詢?nèi)罩镜姆椒?/a>
- 對(duì)MySQL慢查詢?nèi)罩具M(jìn)行分析的基本教程
- MySQL慢查詢之pt-query-digest分析慢查詢?nèi)罩?/a>
- MySQL慢查詢?nèi)罩镜呐渲门c使用教程
- mysql慢查詢使用詳解
- mysql慢查詢操作實(shí)例分析【開啟、測(cè)試、確認(rèn)等】
相關(guān)文章
這篇文章主要為大家詳細(xì)介紹了centos 6.4下使用rpm離線安裝mysql的相關(guān)資料，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-08-08
使用MYSQL TIMESTAMP字段進(jìn)行時(shí)間加減運(yùn)算問題
這篇文章主要介紹了使用MYSQL TIMESTAMP字段進(jìn)行時(shí)間加減運(yùn)算問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-02-02
這篇文章主要介紹了RHEL6.5編譯安裝MySQL5.6.26教程的相關(guān)資料,需要的朋友可以參考下2015-10-10
在MySQL中,我們經(jīng)常需要對(duì)數(shù)據(jù)進(jìn)行拆分和處理,其中一個(gè)常見需求就是按逗號(hào)分割字符串,具有一定的參考價(jià)值,感興趣的可以了解一下2023-11-11
MySQL通過ibd文件恢復(fù)數(shù)據(jù)的操作過程
通常,在InnoDB存儲(chǔ)引擎下,一個(gè)表由三部分組成：數(shù)據(jù)字典（.frm文件）、表空間（.ibd文件）和日志文件,其中,.frm文件存儲(chǔ)了表的定義,.ibd文件存儲(chǔ)了表的數(shù)據(jù)和索引,本文給大家記錄一次MySQL通過ibd文件恢復(fù)數(shù)據(jù)的操作過程,需要的朋友可以參考下2024-10-10
解決Windows10下mysql5.5數(shù)據(jù)庫(kù)命令行中文亂碼問題
重置系統(tǒng)后，很久之前安裝的MySQL數(shù)據(jù)庫(kù)出現(xiàn)了控制臺(tái)查詢中文亂碼問題，時(shí)間太久早已經(jīng)不記得怎么設(shè)置了。下面通過本文給大家分享Windows10下解決MySQL5.5數(shù)據(jù)庫(kù)命令行中文亂碼問題，一起看看吧2017-07-07
解決SQLyog連接MySQL出現(xiàn)錯(cuò)誤Plugin caching_sha2_password co
當(dāng)使用SQLyog連接MySQL時(shí),如果遇到插件caching_sha2_password無法加載的錯(cuò)誤,可以通過更改密碼并將其標(biāo)識(shí)為mysql_native_password來解決,具體步驟包括：打開命令提示符窗口,登錄MySQL,修改密碼并更換插件,然后使用新密碼連接SQLyog2025-01-01
MySQL提升大量數(shù)據(jù)查詢效率的優(yōu)化神器
這篇文章主要介紹了MySQL提升大量數(shù)據(jù)查詢效率的優(yōu)化神器，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下2022-07-07
MySQL是一個(gè)需要賬戶名密碼登錄的數(shù)據(jù)庫(kù),登陸后使用,它提供了一個(gè)默認(rèn)的root賬號(hào),下面這篇文章主要給大家介紹了關(guān)于MySQL命令行登入的兩種方式,需要的朋友可以參考下2023-04-04
詳細(xì)聊聊MySQL中auto_increment有什么作用
auto_increment是用于主鍵自動(dòng)增長(zhǎng)的,從1開始增長(zhǎng),下面這篇文章主要給大家介紹了關(guān)于MySQL中auto_increment有什么作用的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06