過年這段時間由于線上數據庫經常壓力過大導致響應非常緩慢甚至死機，咬咬牙下大決心來解決效率不高的問題!
首先是由于公司秉承快速開發原則，頻繁上線，導致每次忽視了性能問題!日積月累，所以導致系統越來越慢，所以如果你的系統查詢語句本來就優化的很好了可能參考意義不大!
提取慢查詢日志文件，應該在你的DataDir目錄下面
通過程序處理慢查詢文件，將文件格式的慢查詢導入到數據庫中：
1 mysql> desc slow_query;
2 +---------------+-------------+------+-----+---------+-------+
3 | Field | Type | Null | Key | Default | Extra |
4 +---------------+-------------+------+-----+---------+-------+
5 | Date | varchar(32) | NO | | | | 查詢發生的時間
6 | user | varchar(64) | NO | | | |
7 | host | varchar(64) | NO | | | |
8 | content | text | NO | | | | 將Statement進行Mask后的語句，
便于Group By
9 | query_time | int(11) | NO | | | | 查詢所用時間，直接性能指標
10 | lock_time | int(11) | YES | | 0 | | 等待鎖定的時間
11 | rows_sent | int(11) | YES | | 0 | | 返回的結果行數
12 | rows_examined | int(11) | YES | | 0 | | 掃描行數
13 | statement | text | YES | | NULL | | 實際查詢語句
14 +---------------+-------------+------+-----+---------+-------+
|
然后發揮您的想象力在這個表中盡力捕捉你想捕捉的，那類型語句壓力最大、掃描行數最多、等鎖最久……
比如：
優化后：
mysql> select sum(query_time)/count(*),count
(*),sum(query_time),min(Date),Max(Date) from slow where Date>'2008-02-20 22:50:52'
and Date<'2008-02-21 17:34:35';
+--------------------------+----------+-----------------+---------------------+---------------------+
| sum(query_time)/count(*) | count(*) | sum(query_time) | min(Date) | Max(Date) |
+--------------------------+----------+-----------------+---------------------+---------------------+
| 5.7233 | 2197 | 12574 | 2008-02-20 22:51:16 | 2008-02-21 17:34:10 |
+--------------------------+----------+-----------------+---------------------+---------------------+
1 row in set (0.09 sec)
|
優化前：
mysql> select sum(query_time)/count(*),count(*),sum(query_time),min(Date),Max(Date) from slow
where Date>'2008-02-17 22:50:52' and Date<'2008-02-18 17:34:35';
+--------------------------+----------+-----------------+---------------------+---------------------+
| sum(query_time)/count(*) | count(*) | sum(query_time) | min(Date) | Max(Date) |
+--------------------------+----------+-----------------+---------------------+---------------------+
| 2.5983 | 16091 | 41810 | 2008-02-17 22:50:58 | 2008-02-18 17:34:34 |
+--------------------------+----------+-----------------+---------------------+---------------------+
1 row in set (0.15 sec)
|
再比如，優化前：
基本信息：
慢查詢統計從 2008-02-17 17:59:34 到2008-02-18 22:45:22時間段，接近29個小時的數據;
總共有慢查詢28914個，平均一小時有1000個慢查詢;(花了一天優化降到每小時100個的樣子了，成就感啊)
所有慢查詢耗費總時間75690秒;
慢查詢時間設置是大于2秒
參數說明：
sum--總執行時間(秒);
count--執行次數;
avg--平均執行時間(秒);
content--類似SQL語句的表達通式，其中'DD'代表數字;
statement--某一條具體執行的SQL語句
由于訪問時的鎖，導致update非常慢：
1 mysql> select count(*) as n,sum(query_time) as s, sum(query_time)/count(*) as avg,
substring_index(statement,' ',2)
as u from slow where statement like 'update%' and query_time>14 group by u;
2 +-----+------+---------+--------------------------+
3 | n | s | avg | u |
4 +-----+------+---------+--------------------------+
5 | 7 | 112 | 16.0000 | update conversation |
6 | 151 | 2413 | 15.9801 | update user |
7 | 4 | 65 | 16.2500 | update user_modification |
8 +-----+------+---------+--------------------------+
|
說明程序中還是存在一些忘記釋放事務鎖的情況
最耗費資源的10個查詢：
其中第1，2，5應該是同一類查詢，這樣的話這一類查詢占總查詢的一半以上，每分鐘出現10個以上這樣的慢查詢，需要重點解決!
1 mysql> select sum(query_time) as sum, count(*) as count, sum(query_time)/count(*)
as avg,statement from slow wher
2 e host like '%69.12.23.%' group by content order by sum desc limit 0,10\G
3 *************************** 1. row ***************************
4 sum: 27326
5 count: 11681
6 avg: 2.3394
7 ………… |
【編輯推薦】
- 詳解MySQL數據庫提升性能的八種方法
- 優化MySQL插入方法的五個妙招
