MySQL性能分析及explain的使用說明
更新時(shí)間：2011年08月21日 00:36:04 作者：
本文我們主要介紹了MySQL性能分析以及explain的使用，包括：組合索引、慢查詢分析、MYISAM和INNODB的鎖定、MYSQL的事務(wù)配置項(xiàng)等，希望能夠?qū)δ兴鶐椭?/div>
1.使用explain語句去查看分析結(jié)果
如explain select * from test1 where id=1;會(huì)出現(xiàn)：id selecttype table type possible_keys key key_len ref rows extra各列。
其中，
type=const表示通過索引一次就找到了；
key=primary的話，表示使用了主鍵；
type=all,表示為全表掃描；
key=null表示沒用到索引。type=ref,因?yàn)檫@時(shí)認(rèn)為是多個(gè)匹配行，在聯(lián)合查詢中，一般為REF。
2.MYSQL中的組合索引
假設(shè)表有id,key1,key2,key3,把三者形成一個(gè)組合索引，則
如：
where key1=....
where key1=1 and key2=2
where key1=3 and key3=3 and key2=2
根據(jù)最左原則，這些都是可以使用索引的，如from test where key1=1 order by key3，用explain分析的話，只用到了normal_key索引，但只對where子句起作用，而后面的order by需要排序。
3.使用慢查詢分析
在my.ini中：
long_query_time=1
log-slow-queries=d:\mysql5\logs\mysqlslow.log
把超過1秒的記錄在慢查詢?nèi)罩局?
可以用mysqlsla來分析之。也可以在mysqlreport中，有如
DMS分別分析了select ,update,insert,delete,replace等所占的百份比
4.MYISAM和INNODB的鎖定
myisam中，注意是表鎖來的，比如在多個(gè)UPDATE操作后，再SELECT時(shí)，會(huì)發(fā)現(xiàn)SELECT操作被鎖定了，必須等所有UPDATE操作完畢后，再能SELECT
innodb的話則不同了，用的是行鎖，不存在上面問題。
5.MYSQL的事務(wù)配置項(xiàng)
innodb_flush_log_at_trx_commit=1
表示事務(wù)提交時(shí)立即把事務(wù)日志寫入磁盤，同時(shí)數(shù)據(jù)和索引也更新。
innodb_flush_log_at_trx_commit=0
事務(wù)提交時(shí)，不立即把事務(wù)日志寫入磁盤，每隔1秒寫一次
innodb_flush_log_at_trx_commit=2
事務(wù)提交時(shí)，立即寫入磁盤文件（這里只是寫入到內(nèi)核緩沖區(qū)，但不立即刷新到磁盤，而是每隔1秒刷新到盤，同時(shí)更新數(shù)據(jù)和索引
explain用法
EXPLAIN tbl_name或：EXPLAIN [EXTENDED] SELECT select_options
前者可以得出一個(gè)表的字段結(jié)構(gòu)等等，后者主要是給出相關(guān)的一些索引信息，而今天要講述的重點(diǎn)是后者。
舉例
mysql> explain select * from event;
+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |
+—-+————-+——-+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)
各個(gè)屬性的含義
id
select查詢的序列號(hào)
select_type
select查詢的類型，主要是區(qū)別普通查詢和聯(lián)合查詢、子查詢之類的復(fù)雜查詢。
table
輸出的行所引用的表。
type
聯(lián)合查詢所使用的類型。
type顯示的是訪問類型，是較為重要的一個(gè)指標(biāo)，結(jié)果值從好到壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般來說，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref。
possible_keys
指出MySQL能使用哪個(gè)索引在該表中找到行。如果是空的，沒有相關(guān)的索引。這時(shí)要提高性能，可通過檢驗(yàn)WHERE子句，看是否引用某些字段，或者檢查字段不是適合索引。
key
顯示MySQL實(shí)際決定使用的鍵。如果沒有索引被選擇，鍵是NULL。
key_len
顯示MySQL決定使用的鍵長度。如果鍵是NULL，長度就是NULL。文檔提示特別注意這個(gè)值可以得出一個(gè)多重主鍵里mysql實(shí)際使用了哪一部分。
ref
顯示哪個(gè)字段或常數(shù)與key一起被使用。
rows
這個(gè)數(shù)表示mysql要遍歷多少數(shù)據(jù)才能找到，在innodb上是不準(zhǔn)確的。
Extra
如果是Only index，這意味著信息只用索引樹中的信息檢索出的，這比掃描整個(gè)表要快。
如果是where used，就是使用上了where限制。
如果是impossible where 表示用不著where，一般就是沒查出來啥。
如果此信息顯示Using filesort或者Using temporary的話會(huì)很吃力，WHERE和ORDER BY的索引經(jīng)常無法兼顧，如果按照WHERE來確定索引，那么在ORDER BY時(shí)，就必然會(huì)引起Using filesort，這就要看是先過濾再排序劃算，還是先排序再過濾劃算。
關(guān)于MySQL性能分析及explain用法的知識(shí)就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！
如explain select * from test1 where id=1;會(huì)出現(xiàn)：id selecttype table type possible_keys key key_len ref rows extra各列。
其中，
type=const表示通過索引一次就找到了；
key=primary的話，表示使用了主鍵；
type=all,表示為全表掃描；
key=null表示沒用到索引。type=ref,因?yàn)檫@時(shí)認(rèn)為是多個(gè)匹配行，在聯(lián)合查詢中，一般為REF。
2.MYSQL中的組合索引
假設(shè)表有id,key1,key2,key3,把三者形成一個(gè)組合索引，則
如：
復(fù)制代碼 代碼如下:
where key1=....
where key1=1 and key2=2
where key1=3 and key3=3 and key2=2
根據(jù)最左原則，這些都是可以使用索引的，如from test where key1=1 order by key3，用explain分析的話，只用到了normal_key索引，但只對where子句起作用，而后面的order by需要排序。
3.使用慢查詢分析
在my.ini中：
long_query_time=1
log-slow-queries=d:\mysql5\logs\mysqlslow.log
把超過1秒的記錄在慢查詢?nèi)罩局?
可以用mysqlsla來分析之。也可以在mysqlreport中，有如
DMS分別分析了select ,update,insert,delete,replace等所占的百份比
4.MYISAM和INNODB的鎖定
myisam中，注意是表鎖來的，比如在多個(gè)UPDATE操作后，再SELECT時(shí)，會(huì)發(fā)現(xiàn)SELECT操作被鎖定了，必須等所有UPDATE操作完畢后，再能SELECT
innodb的話則不同了，用的是行鎖，不存在上面問題。
5.MYSQL的事務(wù)配置項(xiàng)
innodb_flush_log_at_trx_commit=1
表示事務(wù)提交時(shí)立即把事務(wù)日志寫入磁盤，同時(shí)數(shù)據(jù)和索引也更新。
innodb_flush_log_at_trx_commit=0
事務(wù)提交時(shí)，不立即把事務(wù)日志寫入磁盤，每隔1秒寫一次
innodb_flush_log_at_trx_commit=2
事務(wù)提交時(shí)，立即寫入磁盤文件（這里只是寫入到內(nèi)核緩沖區(qū)，但不立即刷新到磁盤，而是每隔1秒刷新到盤，同時(shí)更新數(shù)據(jù)和索引
explain用法
EXPLAIN tbl_name或：EXPLAIN [EXTENDED] SELECT select_options
前者可以得出一個(gè)表的字段結(jié)構(gòu)等等，后者主要是給出相關(guān)的一些索引信息，而今天要講述的重點(diǎn)是后者。
舉例
復(fù)制代碼 代碼如下:
mysql> explain select * from event;
+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |
+—-+————-+——-+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)
各個(gè)屬性的含義
id
select查詢的序列號(hào)
select_type
select查詢的類型，主要是區(qū)別普通查詢和聯(lián)合查詢、子查詢之類的復(fù)雜查詢。
table
輸出的行所引用的表。
type
聯(lián)合查詢所使用的類型。
type顯示的是訪問類型，是較為重要的一個(gè)指標(biāo)，結(jié)果值從好到壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般來說，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref。
possible_keys
指出MySQL能使用哪個(gè)索引在該表中找到行。如果是空的，沒有相關(guān)的索引。這時(shí)要提高性能，可通過檢驗(yàn)WHERE子句，看是否引用某些字段，或者檢查字段不是適合索引。
key
顯示MySQL實(shí)際決定使用的鍵。如果沒有索引被選擇，鍵是NULL。
key_len
顯示MySQL決定使用的鍵長度。如果鍵是NULL，長度就是NULL。文檔提示特別注意這個(gè)值可以得出一個(gè)多重主鍵里mysql實(shí)際使用了哪一部分。
ref
顯示哪個(gè)字段或常數(shù)與key一起被使用。
rows
這個(gè)數(shù)表示mysql要遍歷多少數(shù)據(jù)才能找到，在innodb上是不準(zhǔn)確的。
Extra
如果是Only index，這意味著信息只用索引樹中的信息檢索出的，這比掃描整個(gè)表要快。
如果是where used，就是使用上了where限制。
如果是impossible where 表示用不著where，一般就是沒查出來啥。
如果此信息顯示Using filesort或者Using temporary的話會(huì)很吃力，WHERE和ORDER BY的索引經(jīng)常無法兼顧，如果按照WHERE來確定索引，那么在ORDER BY時(shí)，就必然會(huì)引起Using filesort，這就要看是先過濾再排序劃算，還是先排序再過濾劃算。
關(guān)于MySQL性能分析及explain用法的知識(shí)就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！
相關(guān)文章
深入理解MySQL雙字段分區(qū)(OVER(PARTITION BY A,B)
本文主要介紹了MySQL中的窗口函數(shù)雙字段分區(qū)功能(OVER(PARTITION BY A,B),分析其在數(shù)據(jù)分組和性能優(yōu)化中的應(yīng)用,提高查詢效率,具有一定的參考價(jià)值,感興趣的可以了解一下2024-09-09
MySQL之InnoDB存儲(chǔ)引擎中的索引用法及說明
這篇文章主要介紹了MySQL之InnoDB存儲(chǔ)引擎中的索引用法及說明,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2025-06-06
這篇文章主要為大家介紹了MySQL數(shù)據(jù)庫事務(wù)安全transaction的示例講解教程，事務(wù)就是將一組操作封裝成一個(gè)執(zhí)行單元，要么一塊執(zhí)行成功，要么一塊失敗，不存在部分執(zhí)行成功的情況。事務(wù)保證了執(zhí)行的穩(wěn)定性，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步2022-06-06
MySQL數(shù)據(jù)庫的約束與設(shè)計(jì)解讀
文章介紹了數(shù)據(jù)庫的約束類型,包括非空約束、默認(rèn)約束、唯一約束、主鍵約束、外鍵約束和check約束,并介紹了數(shù)據(jù)庫設(shè)計(jì)的三大范式：第一范式、第二范式和第三范式,還介紹了如何根據(jù)業(yè)務(wù)需求進(jìn)行數(shù)據(jù)庫設(shè)計(jì)和創(chuàng)建實(shí)體-關(guān)系圖2026-01-01
Eclipse與MySQL數(shù)據(jù)庫的連接教程(已實(shí)操)
用eclipse編寫的好的代碼,我們怎么才能連接到數(shù)據(jù)庫呢?下面這篇文章主要給大家介紹了關(guān)于Eclipse與MySQL數(shù)據(jù)庫連接的相關(guān)資料,下面的操作是經(jīng)本人驗(yàn)證,確實(shí)可行,需要的朋友可以參考下2023-05-05
MySQL 主從復(fù)制部署及驗(yàn)證(示例詳解)
本文介紹MySQL主從復(fù)制部署步驟及學(xué)校管理數(shù)據(jù)庫創(chuàng)建腳本,包含表結(jié)構(gòu)設(shè)計(jì)、示例數(shù)據(jù)插入和查詢語句,用于驗(yàn)證主從同步功能,感興趣的朋友一起看看吧2025-07-07
這篇文章主要給大家介紹了關(guān)于sql語句示例之case?when作為where條件的相關(guān)資料,在SQL語句中CASE WHEN子句是根據(jù)條件表達(dá)式的結(jié)果來執(zhí)行不同的邏輯操作,它使用在WHERE子句中,以根據(jù)特定的條件在查詢結(jié)果中過濾數(shù)據(jù),需要的朋友可以參考下2023-08-08
Mysql BinLog存儲(chǔ)機(jī)制與數(shù)據(jù)恢復(fù)方式
這篇文章主要介紹了Mysql BinLog存儲(chǔ)機(jī)制與數(shù)據(jù)恢復(fù)方式,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-06-06
詳解MySQL誤操作后怎樣進(jìn)行數(shù)據(jù)恢復(fù)
在大家日常操作數(shù)據(jù)庫時(shí)候難免會(huì)因?yàn)椤按笠狻倍`操作，那么誤操作后怎樣進(jìn)行數(shù)據(jù)恢復(fù)呢，下面跟著小編一起來學(xué)習(xí)學(xué)習(xí)。2016-08-08
Table ‘xxx’ is marked as crashed and should be repaired 錯(cuò)誤解決
這些東西都是從其他地方找來的一些解決MYSQL數(shù)據(jù)庫這個(gè)錯(cuò)誤的方法，并不一定適用于神跡數(shù)據(jù)庫，僅僅供參考一下，具體的解決方法還是需要摸索。2009-04-04