線上mysql慢查詢最佳化二例 (1)—轉載

mysql的慢查詢最佳化是個老生常談的話題.本文結生產資料庫中遇到的實際問題,舉例說明.

開啟慢查詢支援

首先要開通慢查詢日誌,修改my.cnf設定檔,添加如下選項:
log-slow-queries = slow.log
long_query_time = 1
----如果打了patch,可以指定更小的值
log-queries-not-using-indexes

然
後,把slow.log按天來切割,例如
slow.log.20100405

分析慢查詢日誌

分析慢查詢語句的最重要的工具,當然是mysql官方提供的mysqldumpslow了.它的詳細用法,請參考我之前的文章

mysqldumpslow -s t -t 5
slow.log.20100405
>analyse.txt

上面的語句將慢查詢語句按照總時間排序,不是mysqldumpslow預設的按照語句執行的平均時間排序.原因是,總時間最長的語句,才是真正需要最佳化的慢查詢語句,而且這種語句的最佳化往往能帶來較好的效率提升.

在top5的語句中,我找到了如下的一條sql語句:

Count:
48 Time=206.79s (9926s) Lock=0.00s (0s) Rows=1.0 (48),
work[work]@[10.81.6.106]
SELECT MAX(img_id) as max_img_id FROM t_mis_pic
WHERE N AND img_id%N='S'
(每天執行了48次,平均每次206s,絕對是慢的需要最佳化了)

根據上述的sql語句模型,去slow.log中找到真實的sql語句中的一條:

SELECT MAX(img_id) as max_img_id FROM t_mis_pic WHERE 1 AND
img_id%2='0';

好了,現在來看看我們有沒有可能最佳化它.

定位問題,最佳化

分析慢查詢語句的工具,首先是explain,如果不能解決,再考慮用profile.
來
show一下表的結構:

show create table t_mis_pic /G
Table: t_mis_pic
Create
Table: CREATE TABLE `t_mis_pic` (
`img_id` bigint(20) unsigned NOT
NULL,
`add_time` int(10) unsigned NOT NULL,
`is_del` tinyint(3) unsigned
NOT NULL default '0',
PRIMARY KEY (`img_id`),
KEY `add_time`
(`add_time`,`is_del`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set
(0.00 sec)

可以看到,img_id列上是有主鍵的.

再explain一下:
Explain SELECT
MAX(img_id) as max_img_id FROM t_mis_pic WHERE 1 AND img_id%2='0'
/G
*************************** 1. row ***************************
id:
1
select_type: SIMPLE
table: t_mis_pic
type: index ----對整個索引的掃描

possible_keys: NULL
key: add_time
----利用了索引,但是不是主鍵索引,只是
add_time索引本身包含了主鍵列,比表的資料小,所以mysql選擇了它

key_len: 5
ref:
NULL ----無法根據條件來實現過濾和快速尋找

rows:
89964741 -----快要1億的資料,全索引掃描,導致了慢查詢

Extra:
Using where; Using index ---利用到了索引和where條件陳述式

上面的分析已經很清楚了,mysql無法有效利用主鍵索引,而且由於資料量太大,導致了慢查詢語句.再來看看錶的狀態:
show
table status like 't_mis_pic' /G
…
Rows: 85532594
Avg_row_length:
55
Data_length: 4771020800
Max_data_length: 0
Index_length:
2581594112
…
從上面的統計中,看到索引的量佔到了資料的一半以上,都很巨大.所以mysql
即使掃描索引也需要很長的時間.現在我們已經知道問題所在了,那麼我們應該怎麼最佳化它呢?

從 SELECT MAX(img_id) as
max_img_id FROM t_mis_pic WHERE 1 AND
img_id%2='0';
這句話來分析,是想要找到最大的為偶數的img_id.那麼我們正好可以利用img_id上的主鍵索引,先直接找到img_id的最大值,然後找到小於等於這個最大值的並且為偶數的
img_id值,就是我們想要的結果.

先explain一下:

explain select MAX(img_id) as
max_img_id FROM t_mis_pic /G:
…
rows: NULL
Extra: Select tables
optimized away
…
結果表明,mysql不用查詢表就可以得到最大的img_id值.那麼我們繼續改寫
SELECT
MAX(img_id) as max_img_id FROM t_mis_pic WHERE 1 AND img_id%2='0'
這條語句:
select img_id as max_img_id
FROM t_mis_pic
where
img_id<=(select MAX(img_id) FROM t_mis_pic) -----wehre條件已經成為where img_id<=常數

and img_id%2=0 ----限制 img_id為偶數

order by img_id desc ----利用 img_id上的索引,從最大的img_id開始,往前尋找為偶數的img_id

limit 1
---返回合格最大的img_id

根據上面的分析,再來explain一下看看是
否符合我們的預期:

explain select img_id as max_img_id FROM t_mis_pic where
img_id<=(select MAX(img_id) FROM t_mis_pic) and img_id%2=0 order by img_id
desc limit 1 /G

*************************** 1. row
***************************
id: 1
select_type: PRIMARY
table:
t_mis_pic
type: range ----mysql仍然進行索引掃描,但是此處是
range掃描,不需要掃描整個index.

possible_keys: PRIMARY
key:
PRIMARY
key_len: 8
ref: NULL
rows: 23548910 -----sql語句本身只需 要返回一行,並不需要檢查23548910
這麼多行,此處的數字可以忽略.實際上,mysql最多隻需要掃描2行.

Extra: Using where; Using index
-----mysql掃描的是索引,而不是表本身

*************************** 2.
row ***************************
…
rows: NULL
Extra: Select tables
optimized away ----最佳化的不能再最佳化了

在備機上執行這條語句,瞬間即可完成.這個慢查詢語句可以完全消除.

回頭看看slow.log的每天統計結果中,
SELECT MAX(img_id) as max_img_id FROM t_mis_pic WHERE N AND img_id%N='S'
語句每天執行48次,每次執行平均時間為200s左右,而最佳化後,執行的時間可以忽略不計,效果還是非常明顯的.

其他方法

既然所有的查詢都用的是img_id%2這個條件,那麼我們可以給該表增加一列,儲存的值就是img_id%2後的結果.然後再給這一列建立索引.但是速度仍然比不上上面改寫後的sql語句,究其原因,因為img_id%2的結果不是奇數就是偶數,區分度太小,給這樣的列建立索引的效果就不明顯了.事實上,在其他商務資料庫中,比如oracle,它的函數索引就是這個原理.針對這個例子,oracle的位元影像索引就更快了.