Mysql效能最佳化

標籤：

效能最佳化分為四大塊：

        表結構的最佳化（含索引）    SQL語句的最佳化    底層參數最佳化    硬體配置最佳化 一.表結構    為欄位選擇合適的資料類型（int>char>varchar 快）    表結構的設計，將欄位多的表分解成多個表，增加中間表，    混用範式與反範式，適當冗餘（可用觸發器更新冗餘列）    索引：        1.索引的類型：                1.b+索引，參見《MySQL三部曲--1    資料庫為什麼要用B+樹結構，InnoDB與MyISAM區別》                2.Hash索引                    缺點：hash索引並不是按照索引值順序儲存的，所以無法用於排序                               只支援等值尋找，不能用於範圍尋找                                需要解決hash衝突，如果在低選擇性的列上建立hash索引，解決衝突代價很大                3.R-tree                    MyISAM支援空間索引，R樹是B樹 向多維空間發展的另一種形式，它將對象空間按範圍劃分，每個結點都對應一個地區和                       一個磁碟頁，非葉結點的磁碟頁中儲存其所有子結點的地區範圍，非葉結點的 所有子結點的地區都落在它的地區範圍之內；                    葉結點的磁碟頁中儲存其地區範圍之內的所有空間對象的外接矩形。R樹是一種動態索引結構。（大部分人不使用）                4.全文索引                     解決出現這樣的問題使“分詞”成為全文索引的關鍵技術。目前有兩種基本的方法：                    二元法 它把所有有可能的每兩兩漢字的組合看為一個片語，這樣就沒有維護詞庫的開銷。                    詞庫法 它使使用詞庫中的詞作為切分的標準，這樣也出現了詞庫跟不上詞彙發展的問題，除非你維護詞庫。                5.其他索引                    還有一些儲存引擎使用不同類型的索引，如TokuDB使用分形樹索引                註：當資料量較少時，大部分情況下簡單的全表掃描更高效。對於中大型表，索引非常有效。單對於超大型表，建立和使用                       索引的代價將隨之增長（維護索引），這時可以直接區分查詢需要的一組資料，而不是一條條匹配，                       列如分區表（http://www.cnblogs.com/zemliu/archive/2013/07/21/3203511.html）二.SQL最佳化    1.最佳化子查詢：MySQL需要為內層查詢語句的查詢結果建立一個暫存資料表。MySQL需要插銷這些暫存資料表。所以在MySQL中可以使用串連查詢來代替子查詢。串連查詢不需要建立暫存資料表，其速度比子查詢要快。        用join 代替 子查詢  如：     2.1 單引號：當資料是int型的時候，where id =‘10‘ 將不會使用索引，索引注意單引號    2.2distinct：可用於去重，（代替group by）select distinct name from student；    2.3order by：預設是由低到高升序（ASC），DESC表示降序（不寫預設升序，一個列對應一個升或降）    2. 為查詢快取最佳化你的查詢         像 NOW() 和 RAND() 或是其它的諸如此類的SQL函數都不會開啟查詢快取，因為這些函數的返回是會不定的易變的。所以，你所需要的就是用一個變數來代替MySQL的函數，從而開啟緩衝。    3. 當只要一行資料時使用 LIMIT 1     4.在Join表的時候使用相當類型的例，並將其索引,     5.避免 SELECT *      6.永遠為每張表設定一個ID,使用 VARCHAR 類型來當主鍵會使用得效能下降,在MySQL資料引擎下，還有一些操作需要使用主鍵，在這些情況下，主鍵的效能和設定變得非常重要，比如，叢集，分區……      7. 使用 ENUM 而不是 VARCHAR ,ENUM 類型是非常快和緊湊的。在實際上，其儲存的是 TINYINT，但其外表上顯示為字串。這樣一來，用這個欄位來做一些選項列表變得相當的完美。     8.從 PROCEDURE ANALYSE() 取得建議 
PROCEDURE ANALYSE() 會讓 MySQL 幫你去分析你的欄位和其實際的資料，並會給你一些有用的建議。只有表中有實際的資料，這些建議才會變得有用，因為要做一些大的決定是需要有資料作為基礎的。      9. 儘可能的使用 NOT NULL      10.無緩衝的查詢          mysql_unbuffered_query() 發送一個SQL語句到MySQL而並不像mysql_query()一樣去自動fethch和緩衝結果。這會相當節約很多可觀的記憶體，尤其是那些會產生大量結果的查詢語句，並且，你不需要等到所有的結果都返回，只需要第一行資料返回的時候，你就可以開始馬上開始工作於查詢結果了。      11.固定長度的表會更快              char > varchar     12.小心“永久連結”             你只有有限的連結數，記憶體問題，檔案控制代碼數，等等。 三.底層參數最佳化          MySQL參數的最佳化：記憶體中會為MySQL保留部分的緩衝區。這些緩衝區可以提高MySQL的速度。緩衝區的大小都是在MySQL的設定檔中進行設定的。

       下面對幾個重要的參數進行詳細介紹：

• key_buffer_size：表示索引緩衝的大小。這個值越大，使用索引進行查詢的速度就越快
• table_cache：表示同時開啟的表的個數。這個值越大，能同時開啟的表的個數就越多。這個值不是越大越好，因為同時開啟的表過多會影響作業系統的效能。
• query_cache_size：表示查詢緩衝區的大小。使用查詢快取區可以提高查詢的速度。這個方式只使用與修改操作少且經常執行相同的查詢操作的情況；預設值是0.
• Query_cache_type：表示查詢快取區的開啟狀態。0表示關閉，1表示開啟。
• Max_connections：表示資料庫的最大串連數。這個串連數不是越大越好，因為串連會浪費記憶體的資源。
• Sort_buffer_size：排序緩衝區的大小，這個值越大，排序就越快。
• Innodb_buffer_pool_size：表示InnoDB類型的表和索引的最大緩衝。這個值越大，查詢的速度就會越快。這個值太大了就會影響作業系統的效能。

四.硬體設定最佳化        CPU     記憶體     固態硬碟 架構：

 

Mysql效能最佳化