1.使用explain語句去查看分析結果

如explain select * from test1 where id=1;

會出現(xiàn)：id selecttype table type possible_keys key key_len ref rows extra各列。

其中，

type=const表示通過索引一次就找到了；

key=primary的話，表示使用了主鍵；

type=all,表示為全表掃描；

key=null表示沒用到索引。

type=ref，因為這時認為是多個匹配行，在聯(lián)合查詢中，一般為REF。

2.MYSQL中的組合索引

假設表有id,key1,key2,key3,把三者形成一個組合索引，則如：

[sql] view plain copy

where key1=....

where key1=1 and key2=2

where key1=3 and key2=3 and key3=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:mysql5logsmysqlslow.log

把超過1秒的記錄在慢查詢日志中

可以用mysqlsla來分析之。也可以在mysqlreport中，有如

DMS分別分析了select ,update,insert,delete,replace等所占的百分比

4.MYISAM和INNODB的鎖定

myisam中，注意是表鎖來的，比如在多個UPDATE操作后，再SELECT時，會發(fā)現(xiàn)SELECT操作被鎖定了，必須等所有UPDATE操作完畢后，再能SELECT

innodb的話則不同了，用的是行鎖，不存在上面問題。

5.MYSQL的事務配置項

innodb_flush_log_at_trx_commit=1

表示事務提交時立即把事務日志flush寫入磁盤，同時數(shù)據(jù)和索引也更新，很費性能。

innodb_flush_log_at_trx_commit=0

事務提交時，不立即把事務日志寫入磁盤，每隔1秒寫一次，MySQL掛了可能會丟失事務的數(shù)據(jù)。

innodb_flush_log_at_trx_commit=2 ，在整個操作系統(tǒng) 掛了時才可能丟數(shù)據(jù)，一般不會丟失超過1-2秒的更新。

事務提交時，立即寫入磁盤文件(這里只是寫入到系統(tǒng)內核緩沖區(qū)，但不立即刷新到磁盤，而是每隔1秒刷新到磁盤，同時更新數(shù)據(jù)和索引)，

這種方案是不是性價比好一些，當然如何配置，決定于你對系統(tǒng)數(shù)據(jù)安全性的要求。

explain用法詳解

EXPLAIN tbl_name或：EXPLAIN [EXTENDED] SELECT select_options

前者可以得出一個表的字段結構等等，后者主要是給出相關的一些索引信息，而今天要講述的重點是后者。 舉例

[sql] view plain copy

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)

各個屬性的含義

id :select查詢的序列號

select_type:select查詢的類型，主要是區(qū)別普通查詢和聯(lián)合查詢、子查詢之類的復雜查詢。

a.SIMPLE：查詢中不包含子查詢或者UNION

b.查詢中若包含任何復雜的子部分，最外層查詢則被標記為：PRIMARY

c.在SELECT或WHERE列表中包含了子查詢，該子查詢被標記為：SUBQUERY

d.在FROM列表中包含的子查詢被標記為：DERIVED(衍生)

e.若第二個SELECT出現(xiàn)在UNION之后，則被標記為UNION；若UNION包含在 FROM子句的子查詢中，外層SELECT將被標記為：DERIVED

f.從UNION表獲取結果的SELECT被標記為：UNION RESULT

table :輸出的行所引用的表。

type :聯(lián)合查詢所使用的類型，表示MySQL在表中找到所需行的方式，又稱“訪問類型”。

type顯示的是訪問類型，是較為重要的一個指標，結果值從好到壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，

一般來說，得保證查詢至少達到range級別，最好能達到ref。

ALL: 掃描全表

index: 掃描全部索引樹

range: 掃描部分索引，索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行，常見于between、等的查詢

ref: 非唯一性索引掃描，返回匹配某個單獨值的所有行。常見于使用非唯一索引即唯一索引的非唯一前綴進行的查找

eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描

const, system: 當MySQL對查詢某部分進行優(yōu)化，并轉換為一個常量時，使用這些類型訪問。

如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量。system是const類型的特例，當查詢的表只有一行的情況下， 使用system。

NULL: MySQL在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引。

如下所示：

possible_keys:指出MySQL能使用哪個索引在該表中找到行。查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用。

如果是空的，沒有相關的索引。這時要提高性能，可通過檢驗WHERE子句，看是否引用某些字段，或者檢查字段不是適合索引。

key :顯示MySQL實際決定使用的鍵。如果沒有索引被選擇，鍵是NULL。

key_len:顯示MySQL決定使用的鍵長度。表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度。

如果鍵是NULL，長度就是NULL。文檔提示特別注意這個值可以得出一個多重主鍵里mysql實際使用了哪一部分。

注：key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據(jù)表定義計算而得，不是通過表內檢索出的。

ref:顯示哪個字段或常數(shù)與key一起被使用。

rows:這個數(shù)表示mysql要遍歷多少數(shù)據(jù)才能找到，表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)，在innodb上可能是不準確的。

Extra:包含不適合在其他列中顯示但十分重要的額外信息。

Only index，這意味著信息只用索引樹中的信息檢索出的，這比掃描整個表要快。

using where是使用上了where限制，表示MySQL服務器在存儲引擎受到記錄后進行“后過濾”(Post-filter)，如果查詢未能使用索引，

Using where的作用只是提醒我們MySQL將用where子句來過濾結果集。

impossible where 表示用不著where，一般就是沒查出來啥。

Using filesort(MySQL中無法利用索引完成的排序操作稱為“文件排序”)當我們試圖對一個沒有索引的字段進行排序時，就是filesoft。它跟文件沒有任何關系，實際上是內部的一個快速排序。

Using temporary(表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢)，使用filesort和temporary的話會很吃力，WHERE和ORDER BY的索引經(jīng)常無法兼顧，

如果按照WHERE來確定索引，那么在ORDER BY時，就必然會引起Using filesort，這就要看是先過濾再排序劃算，還是先排序再過濾劃算。

• 最后，再看一個查詢計劃的例子：

第一行：id列為1，表示第一個select，select_type列的primary表示該查詢?yōu)橥鈱硬樵?#xff0c;table列被標記為，表示查詢結果來自一個衍生表，其中3代表該查詢衍生自第三個select查詢，即id為3的select。[select d1.name......]

第二行：id為3，表示該查詢的執(zhí)行次序為2(4→3)，是整個查詢中第三個select的一部分。因查詢包含在from中，所以為derived。[select id,name from t1 where other_column='']

第三行：select列表中的子查詢，select_type為subquery，為整個查詢中的第二個select。[select id from t3]

第四行：select_type為union，說明第四個select是union里的第二個select，最先執(zhí)行。[select name,id from t2]

第五行：代表從union的臨時表中讀取行的階段，table列的表示用第一個和第四個select的結果進行union操作。[兩個結果union操作]

關于MySQL執(zhí)行計劃的局限性：

EXPLAIN不會告訴你關于觸發(fā)器、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況

EXPLAIN不考慮各種Cache

EXPLAIN不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作

部分統(tǒng)計信息是估算的，并非精確值

EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看。

備注：

filesort是通過相應的排序算法,將取得的數(shù)據(jù)在內存中進行排序。

MySQL需要將數(shù)據(jù)在內存中進行排序，所使用的內存區(qū)域也就是我們通過sort_buffer_size 系統(tǒng)變量所設置的排序區(qū)。這個排序區(qū)是每個Thread 獨享的，所以說可能在同一時刻在MySQL 中可能存在多個 sort buffer 內存區(qū)域。

在MySQL中filesort 的實現(xiàn)算法實際上是有兩種：

雙路排序：是首先根據(jù)相應的條件取出相應的排序字段和可以直接定位行數(shù)據(jù)的行指針信息，然后在sort buffer 中進行排序。

單路排序：是一次性取出滿足條件行的所有字段，然后在sort buffer中進行排序。

在MySQL4.1版本之前只有第一種排序算法雙路排序，第二種算法是從MySQL4.1開始的改進算法，主要目的是為了減少第一次算法中需要兩次訪問表數(shù)據(jù)的IO操作，將兩次變成了一次，但相應也會耗用更多的sortbuffer 空間。當然，MySQL4.1開始的以后所有版本同時也支持第一種算法。

MySQL主要通過比較我們所設定的系統(tǒng)參數(shù) max_length_for_sort_data的大小和Query 語句所取出的字段類型大小總和來判定需要使用哪一種排序算法。

如果 max_length_for_sort_data更大，則使用第二種優(yōu)化后的算法，反之使用第一種算法。所以如果希望 ORDER BY 操作的效率盡可能的高，一定要注意max_length_for_sort_data 參數(shù)的設置。如果filesort過程中，由于排序緩存的大小不夠大，那么就可能會導致臨時表的使用。 ==max_length_for_sort_data的默認值是1024。==

總結

以上是生活随笔為你收集整理的mysql使用常量列_MySQL 执行计划的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內容還不錯，歡迎將生活随笔推薦給好友。