mysql explain介紹

優化：

1思想的優化想出一種降低業務邏輯的實作方法。

2軟體執行效率優化

1.如果order by 沒有利用到索引，那麼将會出現filesort，如果sort_buffer不夠大，filesort過程則需要使用臨時檔案 ,filesort優化，主要通過調整環境來達到，如下

2.設定參數，優化order by 時可能出現的file sort：

将sort_buffer_size = 1m read_rnd_buffer_size = 1m

修改為sort_buffer_size = 16m read_rnd_buffer_size = 16m

避免order by 過程 進行filesort排序過程臨時檔案的産生。從3秒->0.7秒左右

3.去掉distinct,因為distinct加order by,mysql将自動使用臨時表

distinct的優化方式詳見：http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

4.修改jdbc的url，增加參數useserverprepstmts=false，使得query cache生效，

這個參數就是讓參數與sql連接配接成整一個字元串，調試對參數中的單引号做了轉義，應該

不用擔心sql注入攻擊了。另外，是否會導緻服務端對查詢重複的編譯而導緻的性能下降就不清楚了.

explain是用來分析sql語句，幫助優化的一個指令。

explain的文法如下：

explain [extended] select … from … where …

如果使用了extended，那麼在執行完explain語句後，可以使用show warnings語句查詢相應的優化資訊。

比如我們執行 select uid from user where uname=’scofield’ order by uid 執行結果會有

+—-+————-+——-+——-+——————-+———+———+——-+——+——-+  

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |  

這些東西。

id: select的識别符，這是select的查詢序列号。

select_type: select類型，有以下幾種不同的類型

(1).simple: 簡單的select（不使用union或子查詢）

(2).primary: 最外面的select，如果我們使用union或子查詢，第一個查詢将會是這個類型

(3).union: 使用union查詢時，除第一個語句外的所有語句會傳回這個類型

(4).dependent union:　union中的第二個或後面的select語句，取決于外面的查詢。

(5).union result: union的結果。

(6).subquery: 子查詢中的第一個select。

(7).dependent subquery: 子查詢中的第一個select，取決于外面的查詢。

(8).derived : 衍生表會傳回這個類型。如：select * from (select * from jos_content) as a;。

其中 table 表示是哪個表的資料。

type : 表示表的連接配接類型，性能由好到差的類型類型為

(system(表中僅有一行，即常量表)，

const(單表中最多有一個比對行)，

eq_ref(對于前面的每一行,在此表中隻查詢一條記錄)，

ref(使用普通的索引)，

ref_or_null(和ref類似，但是條件中包含對于null查詢),

index_merge(索引合并優化),

unique_subquery(in的後面是一個查詢主鍵字段的子查詢),

index_subquery(類似unique_subquery，主要是in的後面是查詢非唯一索引字段的子查詢),

range(單表中的範圍查詢),

index(對于目前的每一行，都通過查詢索引來得到資料),

all(對于目前的每一行,都通過全表掃描來得到資料))

type比較重要。表示連結的類型。連結類型由好到壞的，依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

一般情況，至少要達到 range 級别，最好是 ref 級别。否則可能會有性能問題。

possible_keys 是指可以應用到該表的索引，如果為null則沒有。

key 是指用到的索引。

key_len 是索引的長度，在不影響查詢精度的情況下，值越小越好 。

ref 是指索引的那一列被使用了。一般會是個常數。

rows mysql認為必須檢查的用來傳回請求資料的行數 。

extra 是指額外的資訊。也是比較重要的 。

但這裡可以看到的壞的例子是using temporary和using filesort，意思mysql根本不能使用索引，結果是檢索會很慢

(1).distinct

一旦mysql找到了與行相聯合比對的行，就不再搜尋了

(2).not exists

mysql優化了left join，一旦它找到了比對left join标準的行， 就不再搜尋了

(3).range checked for each

record（index map:#）

沒有找到理想的索引，是以對于從前面表中來的每一個行組合，mysql檢查使用哪個索引，并用它來從表中傳回行。這是使用索引的最慢的連接配接之一

(4).using filesort

看到這個的時候，查詢就需要優化了。mysql需要進行額外的步驟來發現如何對傳回的行排序。它根據連接配接類型以及存儲排序鍵值和比對條件的全部行的行指針來排序全部行

(5).using index

列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表傳回的，這發生在對表的全部的請求列都是同一個索引的部分的時候

(6).using temporary

看到這個的時候，查詢需要優化了。這裡，mysql需要建立一個臨時表來存儲結果，這通常發生在對不同的列集進行order by上，而不是group by上

(7).using where

使用了where從句來限制哪些行将與下一張表比對或者是傳回給使用者。如果不想傳回表中的全部行，并且連接配接類型all或index，這就會發生，或者是查詢有問題

如果為 where used 說明使用了where語句。如果 type為 all 或者 index ，一般會出現這樣的結果。這樣的問題，一般是查詢需要改進。

在一般稍大的系統中，基本盡可能的減少join ，子查詢等等。mysql就使用最簡單的查詢，這樣效率最高。至于 join 等，可以放在應用層去解決。

3. 使用explain分析低效sql的執行計劃。

在查詢到效率低的sql語句後，那我們可以使用explain或者desc指令擷取myswl如何執行select語句的資訊，包括在select語句執行過程中表如何連接配接和連接配接的順序。

例如你想計數xxxx年公司的銷售額，那麼需要操作sales和comapny table，并對money字段進行sum操作。看看怎麼使用explain：

explain select sum(moneys) from sales a company b where a.company_id = b.id and a.year=xxxx \g;(注意加上\g是為了更好的看)