MySQL中查詢慢的SQL語句的查找方法

這篇文章主要介紹了查找MySQL中查詢慢的SQL語句方法,需要的朋友可以參考下

如何在mysql找到效率慢的SQL語句呢？這可能是困然很多人的問題，MySQL透過慢查詢日誌定位那些執行效率較低的SQL 語句，用--log-slow-queries[=file_name]選項啟動時，mysqld會寫一個包含所有執行時間超過long_query_time 秒的SQL語句的日誌文件，透過查看這個日誌文件定位效率較低的SQL 。以下介紹MySQL中如何查詢慢的SQL語句

一、MySQL資料庫有幾個設定選項可以幫助我們及時擷取低效SQL語句

1，slow_query_log

這個參數設定為ON，可以捕捉執行時間超過一定數值的SQL語句。

2，long_query_time

當SQL語句執行時間超過此數值時，就會被記錄到日誌中，建議設定為1或更短。

3，slow_query_log_file

記錄日誌的檔案名稱。

4，log_queries_not_using_indexes

這個參數設定為ON，可以捕捉到所有未使用索引的SQL語句，儘管這個SQL語句有可能執行得挺快。

二、偵測mysql中sql語句的效率的方法

#1、透過查詢日誌

（1）、Windows下開啟MySQL慢查詢

MySQL在Windows系統中的設定檔一般是my.ini找到[mysqld]下面加上

#程式碼如下

log-slow -queries = F:/MySQL/log/mysqlslowquery。 log
long_query_time = 2

（2）、Linux下啟用MySQL慢查詢

MySQL在Windows系統中的設定檔一般是my.cnf找到[mysqld]下面加上

程式碼如下

log-slow-queries=/data/mysqldata/slowquery。 log
long_query_time=2

說明

log-slow-queries = F:/MySQL/log/mysqlslowquery。

為慢查詢日誌存放的位置，一般這個目錄要有MySQL的運行帳號的可寫權限，通常都將這個目錄設定為MySQL的資料存放目錄；
long_query_time=2中的2表示查詢超過兩秒才記錄；

2.show processlist 指令

WSHOW PROCESSLIST顯示哪些執行緒正在執行。您也可以使用mysqladmin processlist語句得到此資訊。

各列的意思與用途：

ID欄位

#一個標識，你要kill一個語句的時候很有用，用指令殺掉此查詢/*/mysqladmin kill 進程號。

user列

顯示單前用戶，如果不是root，這個指令就只顯示你權限範圍內的sql語句。

host列

顯示這個語句是從哪個ip的哪個連接埠上發出的。用於追蹤出問題語句的使用者。

db列

顯示這個程序目前連接的是哪個資料庫。

command欄位

顯示目前連線的執行的指令，一般就是休眠（sleep），查詢（query），連線（connect）。

time列

此這個狀態持續的時間，單位是秒。

state欄位

顯示使用目前連線的sql語句的狀態，很重要的列，後續會有所有的狀態的描述，請注意，state只是語句執行中的某一個狀態，一個sql語句，以查詢為例，可能需要經過copying to tmp table，Sorting result，Sending data等狀態才可以完成

info欄位

顯示這個sql語句，因為長度有限，所以長的sql語句就顯示不全，但是一個判斷問題語句的重要依據。

這個指令中最關鍵的就是state列，mysql列出的狀態主要有以下幾個：

Checking table
　正在檢查資料表（這是自動的）。
Closing tables
　正在將表中修改的資料刷新到磁碟中，同時正在關閉已經用完的表。這是一個很快的操作，如果不是這樣的話，就應該確認磁碟空間是否已經滿了或者磁碟是否正處於重負中。
Connect Out
　複製從伺服器正在連接主伺服器。
Copying to tmp table on disk
　由於臨時結果集大於tmp_table_size，正在將臨時表從內存存儲轉為磁碟存儲以此節省內存。
Creating tmp table
　正在建立臨時表以存放部分查詢結果。
deleting from main table
　伺服器正在執行多表刪除中的第一部分，剛刪除第一個表。
deleting from reference tables
　伺服器正在執行多表刪除中的第二部分，正在刪除其他表的記錄。
Flushing tables
　正在執行FLUSH TABLES，等待其他執行緒關閉資料表。
Killed
　發送了一個kill請求給某個線程，那麼這個線程將會檢查kill標誌位，同時會放棄下一個kill請求。 MySQL會在每次的主循環中檢查kill標誌位，不過有些情況下該線程可能會過一小段才能死掉。如果該線程程被其他線程鎖住了，那麼kill請求會在鎖釋放時馬上生效。
Locked
　被其他查詢鎖住了。
Sending data
　正在處理SELECT查詢的記錄，同時正在把結果傳送給客戶端。
Sorting for group
　正在為GROUP BY做排序。
　Sorting for order
　正在為ORDER BY做排序。
Opening tables
　這個過程應該會很快，除非受到其他因素的干擾。例如，在執ALTER TABLE或LOCK TABLE語句行完以前，資料表無法被其他執行緒開啟。正嘗試開啟一個表。
Removing duplicates
　正在執行一個SELECT DISTINCT方式的查詢，但是MySQL無法在前一個階段優化掉那些重複的記錄。因此，MySQL需要再次去掉重複的記錄，然後再把結果傳送給客戶端。
Reopen table
　獲得了對一個表的鎖，但是必須在表結構修改之後才能得到這個鎖。已經釋放鎖，關閉資料表，正嘗試重新開啟資料表。
Repair by sorting
　修復指令正在排序以建立索引。
Repair with keycache
　修復指令正在利用索引快取一個一個地建立新索引。它會比Repair by sorting慢些。
Searching rows for update
　正在講符合條件的記錄找出來以備更新。它必須在UPDATE要修改相關的記錄之前就完成了。
Sleeping
　正在等待客戶端發送新請求.
System lock
　正在等待取得一個外部的系統鎖定。如果目前沒有運行多個mysqld伺服器同時請求同一個表，那麼可以透過增加--skip-external-locking參數來禁止外部系統鎖定。
Upgrading lock
　INSERT DELAYED正在嘗試取得一個鎖定表以插入新記錄。
Updating
　正在搜尋匹配的記錄，並且修改它們。

User Lock
　正在等待GET_LOCK()。
Waiting for tables
　該執行緒得到通知，資料表結構已經被修改了，需要重新開啟資料表以取得新的結構。然後，為了能的重新開啟資料表，必須等到所有其他執行緒關閉這個表。以下幾種情況下會產生此通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
　INSERT DELAYED已經處理完了所有待處理的插入操作，正在等待新的請求。
　大部分狀態對應很快的操作，只要有一個執行緒保持同一個狀態好幾秒鐘，那麼可能是有問題發生了，需要檢查一下。
　還有其他的狀態沒在上面列出來，不過它們大部分只是在查看伺服器是否有存在錯誤是才用得著。

例如如圖：

3、explain來了解SQL執行的狀態

#explain顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。

使用方法，在select語句前加上explain就可以了：

例如：

explain select surname,first_name form a,b where a.id=b.id

結果如圖

EXPLAIN欄位的解釋

table

顯示這一行的資料是關於哪一資料表的

type

這是重要的列，顯示連線使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL

possible_keys

##顯示可能會應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個適當的語句

key

#實際使用的索引。如果為

NULL，則沒有使用索引。很少的情況下，MYSQL會選擇最佳化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或用IGNORE INDEX（indexname）來強制MYSQL忽略索引

key_len

#使用的索引的長度。在不損失精確性的情況下，長度越短越好

ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數

rows

MYSQL認為必須檢查的用來傳回請求資料的行數

Extra

關於MYSQL如何解析查詢的額外資訊。將在表4.3中討論，但這裡可以看到的壞的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，結果是檢索會很慢

## extra列回傳的描述的意義

Distinct

一旦MYSQL找到了與行相符的行，就不再搜尋了

##Not exists

MYSQL優化了

LEFT

JOIN，一旦它找到了符合LEFT JOIN標準的行，就不再搜尋了

Range checked for each

Record（index

map :#）沒有找到理想的索引，因此對於從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，並用它來從表中返回行。這是使用索引的最慢的連接之一

Using filesort

看到這個的時候，查詢就需要優化了。 MYSQL需要進行額外的步驟來發現如何對傳回的行排序。它根據連接類型以及儲存排序鍵值和匹配條件的全部行的行指標來排序全部行

Using index

列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表返回的，這發生在對錶的全部的請求列都是同一個索引的部分的時候

Using temporary

看到這個的時候，查詢需要優化了。這裡，MYSQL需要建立一個臨時表來儲存結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上

Where used

使用了WHERE從句來限制哪些行將與下一張表相符或是回傳給使用者。如果不想返回表中的全部行，並且連接類型ALL或index，這就會發生，或者是查詢有問題不同連接類型的解釋（按照效率高低的順序排序）

const

表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際上就是常數，因為MYSQL先讀這個值然後把它當做常數來對待

eq_ref

在連接中，MYSQL在查詢時，從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用

ref

##這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或是這些類型的部分（例如，利用最左邊前綴）時發生。對於先前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴根據索引匹配的記錄多少—越少越好

range

這個連接類型使用索引返回一個範圍中的行，例如使用>或632c03783964e94e7213469e7fe08442 create index idx_sales_year on sales(year);
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
创建索引后，这条语句的执行计划如下：

mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G; 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: a 
type: ref 
possible_keys: idx_sales_year 
key: idx_sales_year 
key_len: 4 
ref: const 
rows: 3 
Extra: 
*************************** 2. row *************************** 
id: 1 
select_type: SIMPLE 
table: b 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 12 
Extra: Using where 
2 rows in set (0.00 sec)

可以发现建立索引后对 a 表需要扫描的行数明显减少（从全表扫描减少到 3 行），可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显，使用索引优化 sql 是优化问题 sql 的一种常用基本方法，在后面的章节中我们会具体介绍如何使索引来优化 sql 。
本文主要介绍的是MySQL慢查询分析方法，前一段日子，我曾经设置了一次记录在MySQL数据库中对慢于1秒钟的SQL语句进行查询。想起来有几个十分设置的方法，有几个参数的名称死活回忆不起来了，于是重新整理一下，自己做个笔记。
　　对于排查问题找出性能瓶颈来说，最容易发现并解决的问题就是MySQL慢查询以及没有得用索引的查询。

　　OK，开始找出MySQL中执行起来不“爽”的SQL语句吧。
　　MySQL慢查询分析方法一：
　　这个方法我正在用，呵呵，比较喜欢这种即时性的。
　　MySQL5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。
　　MySQL> show variables like 'long%';
　　注：这个long_query_time是用来定义慢于多少秒的才算“慢查询”
　　

+-----------------+-----------+
　　| Variable_name | Value |
　　+-----------------+-----------+
　　| long_query_time | 10.000000 |
　　+-----------------+-----------+
　　1 row in set (0.00 sec)
　　MySQL> set long_query_time=1;
　　注： 我设置了1, 也就是执行时间超过1秒的都算慢查询。
　　Query OK, 0 rows affected (0.00 sec)
　　MySQL> show variables like 'slow%';
　　+---------------------+---------------+
　　| Variable_name | Value |
　　+---------------------+---------------+
　　| slow_launch_time | 2 |
　　| slow_query_log | ON |
　　注：是否打开日志记录
　　| slow_query_log_file | /tmp/slow.log |
　　注： 设置到什么位置
　　+---------------------+---------------+
　　3 rows in set (0.00 sec)
　　MySQL> set global slow_query_log='ON'

　　注：打开日志记录
　　一旦slow_query_log变量被设置为ON，MySQL会立即开始记录。
　　/etc/my.cnf 里面可以设置上面MySQL全局变量的初始值。
　　long_query_time=1 slow_query_log_file=/tmp/slow.log
　　MySQL慢查询分析方法二:
　　MySQLdumpslow命令
　　/path/MySQLdumpslow -s c -t 10 /tmp/slow-log
　　这会输出记录次数最多的10条SQL语句，其中：
　　-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙;
　　-t, 是top n的意思，即为返回前面多少条的数据;
　　-g, 后边可以写一个正则匹配模式，大小写不敏感的;
　　比如
　　/path/MySQLdumpslow -s r -t 10 /tmp/slow-log
　　得到返回记录集最多的10个查询。
　　/path/MySQLdumpslow -s t -t 10 -g “left join” /tmp/slow-log
　　得到按照时间排序的前10条里面含有左连接的查询语句。
 
简单点的方法：
打开 my.ini ,找到 [mysqld] 在其下面添加   long_query_time = 2 log-slow-queries = D:/mysql/logs/slow.log #设置把日志写在那里，可以为空，系统会给一个缺省的文件 #log-slow-queries = /var/youpath/slow.log linux下host_name-slow.log log-queries-not-using-indexes   long_query_time 是指执行超过多长时间（单位是秒）的sql会被记录下来，这里设置的是2秒。
以下是mysqldumpslow常用参数说明，详细的可应用mysqldumpslow -help查询。   -s，是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序（从大到小），ac、at、al、ar表示相应的倒叙。   -t，是top n的意思，即为返回前面多少条数据。 www.jb51.net  -g，后边可以写一个正则匹配模式，大小写不敏感。   接下来就是用mysql自带的慢查询工具mysqldumpslow分析了（mysql的bin目录下 ），我这里的日志文件名字是host-slow.log。   列出记录次数最多的10个sql语句   mysqldumpslow -s c -t 10 host-slow.log   列出返回记录集最多的10个sql语句  mysqldumpslow -s r -t 10 host-slow.log   按照时间返回前10条里面含有左连接的sql语句   mysqldumpslow -s t -t 10 -g "left join" host-slow.log   使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化起到非常大的帮助
 
在日常开发当中，经常会遇到页面打开速度极慢的情况，通过排除，确定了，是数据库的影响，为了迅速查找具体的SQL，可以通过Mysql的日志记录方法。
-- 打开sql执行记录功能
set global log_output='TABLE'; -- 输出到表
set global log=ON; -- 打开所有命令执行记录功能general_log, 所有语句: 成功和未成功的.
set global log_slow_queries=ON; -- 打开慢查询sql记录slow_log, 执行成功的: 慢查询语句和未使用索引的语句
set global long_query_time=0.1; -- 慢查询时间限制(秒)
set global log_queries_not_using_indexes=ON; -- 记录未使用索引的sql语句
-- 查询sql执行记录
select * from mysql.slow_log order by 1; -- 执行成功的：慢查询语句,和未使用索引的语句
select * from mysql.general_log order by 1; -- 所有语句： 成功和未成功的.
-- 关闭sql执行记录
set global log=OFF;
set global log_slow_queries=OFF;
-- long_query_time参数说明
-- v4.0, 4.1, 5.0, v5.1 到 5.1.20(包括):不支持毫秒级别的慢查询分析(支持精度为1-10秒);
-- 5.1.21及以后版本 :支持毫秒级别的慢查询分析, 如0.1;
-- 6.0 到 6.0.3: 不支持毫秒级别的慢查询分析(支持精度为1-10秒);
-- 6.0.4及以后:支持毫秒级别的慢查询分析;
通过日志中记录的Sql，迅速定位到具体的文件，优化sql看一下，是否速度提升了呢？
 
本文针对MySQL数据库服务器查询逐渐变慢的问题， 进行分析，并提出相应的解决办法，具体的分析解决办法如下：会经常发现开发人员查一下没用索引的语句或者没有limit n的语句，这些没语句会对数据库造成很大的影...
 
本文针对MySQL数据库服务器查询逐渐变慢的问题， 进行分析，并提出相应的解决办法，具体的分析解决办法如下：
会经常发现开发人员查一下没用索引的语句或者没有limit n的语句，这些没语句会对数据库造成很大的影响，例如一个几千万条记录的大表要全部扫描，或者是不停的做filesort，对数据库和服务器造成io影响等。这是镜像库上面的情况。
而到了线上库，除了出现没有索引的语句，没有用limit的语句，还多了一个情况,mysql连接数过多的问题。说到这里，先来看看以前我们的监控做法
1. 部署zabbix等开源分布式监控系统，获取每天的数据库的io，cpu，连接数
2. 部署每周性能统计，包含数据增加量，iostat，vmstat，datasize的情况
3. Mysql slowlog收集，列出top 10
以前以为做了这些监控已经是很完美了，现在部署了mysql节点进程监控之后，才发现很多弊端
第一种做法的弊端: zabbix太庞大，而且不是在mysql内部做的监控，很多数据不是非常准备，现在一般都是用来查阅历史的数据情况
第二种做法的弊端：因为是每周只跑一次，很多情况没法发现和报警
第三种做法的弊端: 当节点的slowlog非常多的时候，top10就变得没意义了，而且很多时候会给出那些是一定要跑的定期任务语句给你。。参考的价值不大
那么我们怎么来解决和查询这些问题呢
对于排查问题找出性能瓶颈来说，最容易发现并解决的问题就是MYSQL的慢查询以及没有得用索引的查询。
OK，开始找出mysql中执行起来不“爽”的SQL语句吧。

方法一： 这个方法我正在用，呵呵，比较喜欢这种即时性的。

Mysql5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。

 mysql> show variables like 'long%'; 注：这个long_query_time是用来定义慢于多少秒的才算“慢查询”
 

+-----------------+-----------+
 | Variable_name | Value |
 +-----------------+-----------+
 | long_query_time | 10.000000 |
 +-----------------+-----------+
 1 row in set (0.00 sec)
 mysql> set long_query_time=1; 注： 我设置了1, 也就是执行时间超过1秒的都算慢查询。
 Query OK, 0 rows affected (0.00 sec)
 mysql> show variables like 'slow%';
 +---------------------+---------------+
 | Variable_name | Value |
 +---------------------+---------------+
 | slow_launch_time | 2 |
 | slow_query_log | ON | 注：是否打开日志记录
 | slow_query_log_file | /tmp/slow.log | 注： 设置到什么位置
 +---------------------+---------------+
 3 rows in set (0.00 sec)

 mysql> set global slow_query_log='ON' 注：打开日志记录
 一旦slow_query_log变量被设置为ON，mysql会立即开始记录。
 /etc/my.cnf 里面可以设置上面MYSQL全局变量的初始值。
 long_query_time=1
 slow_query_log_file=/tmp/slow.log

方法二:mysqldumpslow命令

 /path/mysqldumpslow -s c -t 10 /tmp/slow-log
 这会输出记录次数最多的10条SQL语句，其中：
 -s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
 -t, 是top n的意思，即为返回前面多少条的数据；
 -g, 后边可以写一个正则匹配模式，大小写不敏感的；
 比如
 /path/mysqldumpslow -s r -t 10 /tmp/slow-log
 得到返回记录集最多的10个查询。
 /path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log
 得到按照时间排序的前10条里面含有左连接的查询语句。
最后总结一下节点监控的好处
1． 轻量级的监控，而且是实时的，还可以根据实际的情况来定制和修改
2． 设置了过滤程序，可以对那些一定要跑的语句进行过滤
3． 及时发现那些没有用索引，或者是不合法的查询，虽然这很耗时去处理那些慢语句，但这样可以避免数据库挂掉，还是值得的
4. 在数据库出现连接数过多的时候，程序会自动保存当前数据库的processlist，DBA进行原因查找的时候这可是利器
5. 使用mysqlbinlog 来分析的时候，可以得到明确的数据库状态异常的时间段
有些人会建义我们来做mysql配置文件设置

调节tmp_table_size 的时候发现另外一些参数
Qcache_queries_in_cache 在缓存中已注册的查询数目
Qcache_inserts 被加入到缓存中的查询数目
Qcache_hits 缓存采样数数目
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory 查询缓存的空闲内存总数
Qcache_free_blocks 查询缓存中的空闲内存块的数目
Qcache_total_blocks 查询缓存中的块的总数目
Qcache_free_memory 可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度

以上是MySQL中查詢慢的SQL語句的查找方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！