本文向您介紹通過(guò)優(yōu)化查詢(xún)語(yǔ)句等措施環(huán)境數(shù)據(jù)庫(kù)壓力過(guò)大導(dǎo)致運(yùn)行緩慢甚至死機(jī)的方法。
過(guò)年這段時(shí)間由于線(xiàn)上數(shù)據(jù)庫(kù)經(jīng)常壓力過(guò)大導(dǎo)致響應(yīng)非常緩慢甚至死機(jī)，咬咬牙下大決心來(lái)解決效率不高的問(wèn)題!
首先是由于公司秉承快速開(kāi)發(fā)原則，頻繁上線(xiàn)，導(dǎo)致每次忽視了性能問(wèn)題!日積月累，所以導(dǎo)致系統(tǒng)越來(lái)越慢，所以如果你的系統(tǒng)查詢(xún)語(yǔ)句本來(lái)就優(yōu)化的很好了可能參考意義不大!
提取慢查詢(xún)?nèi)罩疚募瑧?yīng)該在你的DataDir目錄下面
通過(guò)程序處理慢查詢(xún)文件，將文件格式的慢查詢(xún)導(dǎo)入到數(shù)據(jù)庫(kù)中：
1 mysql> desc slow_query;
2 +---------------+-------------+------+-----+---------+-------+
3 | Field | Type | Null | Key | Default | Extra |
4 +---------------+-------------+------+-----+---------+-------+
5 | Date | varchar(32) | NO | | | | 查詢(xún)發(fā)生的時(shí)間
6 | user | varchar(64) | NO | | | |
7 | host | varchar(64) | NO | | | |
8 | content | text | NO | | | | 將Statement進(jìn)行Mask后的語(yǔ)句，
便于Group By
9 | query_time | int(11) | NO | | | | 查詢(xún)所用時(shí)間，直接性能指標(biāo)
10 | lock_time | int(11) | YES | | 0 | | 等待鎖定的時(shí)間
11 | rows_sent | int(11) | YES | | 0 | | 返回的結(jié)果行數(shù)
12 | rows_examined | int(11) | YES | | 0 | | 掃描行數(shù)
13 | statement | text | YES | | NULL | | 實(shí)際查詢(xún)語(yǔ)句
14 +---------------+-------------+------+-----+---------+-------+
|
然后發(fā)揮您的想象力在這個(gè)表中盡力捕捉你想捕捉的，那類(lèi)型語(yǔ)句壓力最大、掃描行數(shù)最多、等鎖最久……
比如：
優(yōu)化后：
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)
|
優(yōu)化前：
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)
|
再比如，優(yōu)化前：
基本信息：
慢查詢(xún)統(tǒng)計(jì)從 2008-02-17 17:59:34 到2008-02-18 22:45:22時(shí)間段，接近29個(gè)小時(shí)的數(shù)據(jù);
總共有慢查詢(xún)28914個(gè)，平均一小時(shí)有1000個(gè)慢查詢(xún);(花了一天優(yōu)化降到每小時(shí)100個(gè)的樣子了，成就感啊)
所有慢查詢(xún)耗費(fèi)總時(shí)間75690秒;
慢查詢(xún)時(shí)間設(shè)置是大于2秒
參數(shù)說(shuō)明：
sum--總執(zhí)行時(shí)間(秒);
count--執(zhí)行次數(shù);
avg--平均執(zhí)行時(shí)間(秒);
content--類(lèi)似SQL語(yǔ)句的表達(dá)通式，其中'DD'代表數(shù)字;
statement--某一條具體執(zhí)行的SQL語(yǔ)句
由于訪(fǎng)問(wèn)時(shí)的鎖，導(dǎo)致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 +-----+------+---------+--------------------------+
|
說(shuō)明程序中還是存在一些忘記釋放事務(wù)鎖的情況
最耗費(fèi)資源的10個(gè)查詢(xún)：
其中第1，2，5應(yīng)該是同一類(lèi)查詢(xún)，這樣的話(huà)這一類(lèi)查詢(xún)占總查詢(xún)的一半以上，每分鐘出現(xiàn)10個(gè)以上這樣的慢查詢(xún)，需要重點(diǎn)解決!
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數(shù)據(jù)庫(kù)提升性能的八種方法
- 優(yōu)化MySQL插入方法的五個(gè)妙招
