天天看點
傳回

MySQL慢日志查詢實踐

慢日志查詢的主要功能就是，記錄sql語句中超過設定的時間門檻值的查詢語句。例如，一條查詢sql語句，我們設定的門檻值為1s，當這條查詢語句的執行時間超過了1s，則将被寫入到慢查詢配置的日志中.

慢日志查詢作用

慢日志查詢的主要功能就是，記錄sql語句中超過設定的時間門檻值的查詢語句。例如，一條查詢sql語句，我們設定的門檻值為1s，當這條查詢語句的執行時間超過了1s，則将被寫入到慢查詢配置的日志中. 慢查詢主要是為了我們做sql語句的優化功能.

慢查詢配置項說明

登入mysql服務，使用如下指令

  1. mysql> show variables like \'%query%\';

  2. +------------------------------+-----------------------------------------+

  3. | Variable_name | Value |

  4. +------------------------------+-----------------------------------------+

  5. | binlog_rows_query_log_events | OFF |

  6. | ft_query_expansion_limit | 20 |

  7. | have_query_cache | YES |

  8. | long_query_time | 10.000000 |

  9. | query_alloc_block_size | 8192 |

  10. | query_cache_limit | 1048576 |

  11. | query_cache_min_res_unit | 4096 |

  12. | query_cache_size | 33554432 |

  13. | query_cache_type | OFF |

  14. | query_cache_wlock_invalidate | OFF |

  15. | query_prealloc_size | 8192 |

  16. | slow_query_log | OFF |

  17. | slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |

  18. +------------------------------+-----------------------------------------+

  19. 13 rows in set (0.01 sec)

這裡，我們隻需要關注三個配置項即可。 1.slowquerylog 該配置項是決定是否開啟慢日志查詢功能，配置的值有ON或者OFF. 2.slowquerylogfile 該配置項是慢日志查詢的記錄檔案,需要手動建立. 3.longquery_time 該配置項是設定慢日志查詢的時間門檻值，當超過這個門檻值時，慢日志才會被記錄.配置的值有0(任何的sql語句都記錄下來)，或者>0(具體的門檻值).該配置項是以秒為機關的，并且可以設定為小數. 4.log-queries-not-using-indexes 該配置項是為了記錄未使用到索引的sql語句.

如何配置慢查詢

配置慢查詢功能的方式有兩種，一種是使用mysql的配置檔案配置,另外一種是使用mysql指令配置.這裡建議使用配置檔案配置，因為在指令配置的過程中發現有時候配置項在set指令的時候是成功了，但是查詢還是沒設定。 1.配置檔案配置

  1. // 找到[mysqld],在其下面添加如下代碼即可.

  2. slow_query_log=ON

  3. slow_query_log_file=/usr/local/mysql/var/localhost-slow.log

  4. long_query_time=0

  5. log-queries-not-using-indexes = 1

  6. // 配置好後，重新開機mysql服務

2.使用指令配置

  1. // 這裡就簡單些一個配置項就行了，其他的配置項均按照此方法配置

  2. mysql> set slow_query_log=ON;

配置好之後，檢視mysql慢查詢日志是否配置成功.

  1. mysql> show variables like \'%query%\';

  2. +------------------------------+-----------------------------------------+

  3. | Variable_name | Value |

  4. +------------------------------+-----------------------------------------+

  5. | binlog_rows_query_log_events | OFF |

  6. | ft_query_expansion_limit | 20 |

  7. | have_query_cache | YES |

  8. | long_query_time | 0.000000 |

  9. | query_alloc_block_size | 8192 |

  10. | query_cache_limit | 1048576 |

  11. | query_cache_min_res_unit | 4096 |

  12. | query_cache_size | 33554432 |

  13. | query_cache_type | OFF |

  14. | query_cache_wlock_invalidate | OFF |

  15. | query_prealloc_size | 8192 |

  16. | slow_query_log | ON |

  17. | slow_query_log_file | /usr/local/mysql/var/localhost-slow.log |

  18. +------------------------------+-----------------------------------------+

  19. 13 rows in set (0.01 sec)
如何檢視慢日志記錄

在配置慢查詢之前，我們已經導入了示例的資料檔案,這裡就不做單獨的示範了。mysql官方資料庫示例 。接下來，我們就開始做查詢操作.

  1. mysql> select * from city where city=\'Salala\';

  2. +---------+--------+------------+---------------------+

  3. | city_id | city | country_id | last_update |

  4. +---------+--------+------------+---------------------+

  5. | 444 | Salala | 71 | 2006-02-15 04:45:25 |

  6. +---------+--------+------------+---------------------+

  7. 1 row in set (0.01 sec)

此時，我們根據配置的慢查詢日志記錄檔案/usr/local/mysql/var/localhost-slow.log,發現該檔案記錄了上面的指令操作.

  1. # Time: 2019-01-17T08:12:27.184998Z

  2. # User@Host: root[root] @ localhost [] Id: 4

  3. # Query_time: 0.002773 Lock_time: 0.001208 Rows_sent: 1 Rows_examined: 600

  4. SET timestamp=1547712747;

  5. select * from city where city=\'Salala\';

上訴檔案配置内容說明 1.Time 該日志記錄的時間 2.User@Host MySQL登入的使用者和登入的主機位址 3.Querytime一行 第一個時間是查詢的時間、第二個是鎖表的時間、第三個是傳回的行數、第四個是掃描的行數 4.SET timestamp 這一個是MySQL查詢的時間 5.sql語句 這一行就很明顯了，表示的是我們執行的sql語句 總結 由于我們配置longquerytime=0,是以所有的sql語句都将被記錄下來，這裡我們假設，僅僅是假設。我們設定的longquerytime=5，然而上面的第三項中Querytime大于5，如果是實際項目中不屬于正常範圍，則需要對其進行優化，當然優化的方式有很多種，下面我們使用簡單的索引方式進行優化一下。

優化sql語句

1.先檢視原本的sql語句執行結構

  1. mysql> explain select * from city where city=\'Salala\'\G;

  2. *************************** 1. row ***************************

  3. id: 1

  4. select_type: SIMPLE

  5. table: city

  6. partitions: NULL

  7. type: ALL

  8. possible_keys: NULL

  9. key: NULL

  10. key_len: NULL

  11. ref: NULL

  12. rows: 600

  13. filtered: 10.00

  14. Extra: Using where

  15. 1 row in set, 1 warning (0.00 sec)

  16. mysql> create index idx_cityName on city(`city`);

  17. Query OK, 0 rows affected (0.03 sec)

  18. Records: 0 Duplicates: 0 Warnings: 0

看得出，該sql語句是進行了全盤掃描。我們則用索引對其簡單的優化一下。 2.建立索引

  1. mysql> create index idx_cityName on city(`city`);

  2. Query OK, 0 rows affected (0.03 sec)

  3. Records: 0 Duplicates: 0 Warnings: 0

3.在用explain分析一次

  1. mysql> explain select * from city where city=\'Salala\'\G;

  2. *************************** 1. row ***************************

  3. id: 1

  4. select_type: SIMPLE

  5. table: city

  6. partitions: NULL

  7. type: ref

  8. possible_keys: idx_cityName

  9. key: idx_cityName

  10. key_len: 152

  11. ref: const

  12. rows: 1

  13. filtered: 100.00

  14. Extra: NULL

  15. 1 row in set, 1 warning (0.00 sec)

通過建立索引，我們發現此時隻掃描了一行，并且是使用的索引掃描，是以大大提高了一個mysql查詢的效率。

MySQL慢查詢使用總結

在平常的開發中，慢查詢作為MySQL優化的一個途徑，是非常有用的。它會記錄下我們一些查詢時間長的sql語句，對其語句我們進行分析，進而達到sql查詢語句的最優化。但是慢日志查詢開啟之後，針對sql查詢會通過磁盤I/O将相關的記錄寫入到磁盤檔案中，增加了一個磁盤的I/O讀寫。是以，我們該功能用在開發、測試環境上，而不用在生産環境中去。

慢日志查詢工具

由于慢日志查詢檔案越到後期，内容越多。我們對其分析的壓力越大，是以我們需要借助某些工具實作快速分析。這些工具還沒完全使用熟悉，後期單獨寫一篇文章介紹該類型的工具，這裡隻是羅列一下工具名稱。 1.mysqldumpslow 2.pt-query-digest 3.mysqltop（天兔Lepus）

上一篇: Spring 定時器配置執行個體
下一篇: iOS開發之