MySQL優化大全

 1. 優化SQL

  1）通過show status了解各種sql的執行頻率

        show status like 'Com_%'
        了解 Com_select,Com_insert 的執行次數
   2)  通過Explain分析低效的sql語句
   3)   建立合適的索引
   4)   通過show status like 'Handler_%'查看索引的使用情況
         handler_read_key：根據索引讀取行的請求數。如果該值很大，說明你的查詢和表都建立了很好的索引，表明索引效率的很高
         Handler_read_rnd_key：根據固定位置讀取行的請求數。如果你執行很多需要排序的查詢，該值會很高。你可能有很多需要完整表掃描的查詢，或者你使用了不正確的索引用來多表查詢。

         Handler read rnd next：從數據文件中讀取行的請求數。如果你在掃描很多表，該值會很大。通常情況下這意味著你的表沒有做好索引，或者你的查詢語句沒有使用好索引字段。

   5)   定期分析表和檢查表
         analyze table test_table和check table test_table
         然后查看Msg_text字段的值是否是ok
   6）定期優化表 optimize table test_table
         如果對表的可變字段varchar blob，text等進行了很多更改， 則應用OPTIMIZE優化。

         在多數的設置中，您根本不需要運行OPTIMIZE TABLE。即使您對可變長度的行進行了大量的更新，您也不需要經常運行，每周一次或每月一次即可，只對特定的表運行。
       OPTIMIZE TABLE只對MyISAM, BDB和InnoDB表起作用。
       對于MyISAM表，OPTIMIZE TABLE按如下方式操作：
       如果表已經刪除或分解了行，則修復表。
       如果未對索引頁進行分類，則進行分類。
       如果表的統計數據沒有更新（并且通過對索引進行分類不能實現修復），則進行更新。
   7)  優化 order by orgroup by等

    詳細內容：SQL優化大全

2. 優化數據庫對象

1）選擇表合適存儲引擎：

            MyISAM:  應用時以讀和插入操作為主，只有少量的更新和刪除，并且對事務的完整性，并發性要求不是很高的.

            Innodb： 事務處理，以及并發條件下要求數據的一致性。除了插入和查詢外，包括很多的更新和刪除。（Innodb有效地降低刪除和更新導致的鎖定）。對于支持事務的InnoDB類型的表來說，影響速度的主要原因是AUTOCOMMIT默認設置是打開的，而且程序沒有顯式調用BEGIN 開始事務，導致每插入一條都自動提交，嚴重影響了速度?？梢栽趫绦衧ql前調用begin，多條sql形成一個事物（即使autocommit打開也可以），將大大提高性能。

            Memory：數據保存在RAM,快速訪問數據。要求表不能太大或者對mysql異常終止后不用恢復數據的

            Merge:      
2）優化表的數據類型,選擇合適的數據類型：

           原則：更小通常更好，簡單就好，所有字段都得有默認值,盡量避免null：

           例如：數據庫表設計時候更小的占磁盤空間盡可能使用更小的整數類型.(mediumint就比int更合適)

           比如時間字段：datetime和timestamp, datetime占用8個字節，而timestamp占用4個字節，只用了一半，而timestamp表示的范圍是1970—2037適合做更新時間

　　MySQL可以很好的支持大數據量的存取，但是一般說來，數據庫中的表越小，在它上面執行的查詢也就會越快。因此，在創建表的時候，為了獲得更好的性能，我們可以將表中字段的寬度設得盡可能小。例如，在定義郵政編碼這個字段時，如果將其設置為CHAR(255),顯然給數據庫增加了不必要的空間，甚至使用VARCHAR這種類型也是多余的，因為CHAR(6)就可以很好的完成任務了。同樣的，如果可以的話，我們應該使用MEDIUMINT而不是BIGIN來定義整型字段。
　　另外一個提高效率的方法是在可能的情況下，應該盡量把字段設置為NOT NULL，這樣在將來執行查詢的時候，數據庫不用去比較NULL值。
　　對于某些文本字段，例如“省份”或者“性別”，我們可以將它們定義為ENUM類型。因為在MySQL中，ENUM類型被當作數值型數據來處理，而數值型數據被處理起來的速度要比文本類型快得多。這樣，我們又可以提高數據庫的性能。

3) 字符串數據類型：char，varchar，text選擇區別
       (1)長度的區別，char范圍是0～255，varchar最長是64k，但是注意這里的64k是整個row的長度，要考慮到其它的column，還有如果存在not null的時候也會占用一位，對不同的字符集，有效長度還不一樣，比如utf8的，最多21845，還要除去別的column，但是varchar在一般情況下存儲都夠用了。如果遇到了大文本，考慮使用text，最大能到4G。 

      (2) 效率來說基本是char>varchar>text，但是如果使用的是Innodb引擎的話，推薦使用varchar代替

     (3)默認值  charchar和varchar可以有默認值，text不能指定默認值

4）MySQL中float數據類型的問題

      (1) .FLOAT或DOUBLE列與具有數值類型的數值進行比較，不能使用等式(=)比較.這個是因為浮點數精度的問題,會產生誤差。

  (2)對貨幣等對精度敏感的數據，應該用定點數表示或存儲

      數據庫選擇合適的數據類型存儲還是很有必要的，對性能有一定影響。這里在零碎記錄兩筆，對于int類型的，如果不需要存取負值，最好加上unsigned；對于經常出現在where語句中的字段，考慮加索引，整型的尤其適合加索引。

 5）在InnoDB數據表設計中，我們需要注意幾點：

    1. 顯式的定義一個 INT 類型自增字段的主鍵，這個字段可以僅用于做主鍵，不做其他用途
    2. 如果不顯式定義主鍵的話，可能會導致InnoDB每次都需要對新數據行進行排序，嚴重損害性能
    3. 盡量保證不對主鍵字段進行更新修改，防止主鍵字段發生變化，引發數據存儲碎片，降低IO性能
    4. 如果需要對主鍵字段進行更新，請將該字段轉變成一個唯一索引約束字段，另外創建一個沒有其他業務意義的自增字段做主鍵
    5. 主鍵字段類型盡可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT
    6. 主鍵字段放在數據表的第一順序

3. 優化索引

　　索引是提高數據庫性能的常用方法，它可以令數據庫服務器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(), MIN()和ORDERBY這些命令的時候，性能提高更為明顯.

    那該對哪些字段建立索引呢？一般說來，索引應建立在那些將用于JOIN, WHERE判斷和ORDER BY排序的字段上。盡量不要對數據庫中某個含有大量重復的值的字段建立索引。對于一個ENUM類型的字段來說，出現大量重復值是很有可能的情況，例如customerinfo中的“province”..字段，在這樣的字段上建立索引將不會有什么幫助；相反，還有可能降低數據庫的性能。我們在創建表的時候可以同時創建合適的索引，也可以使用ALTER TABLE或CREATE INDEX在以后創建索引

1）. 普通索引

        普通索引(由關鍵字KEY或INDEX定義的索引)的唯一任務是加快對數據的訪問速度。因此，應該只為那些最經常出現在查詢條件(WHERE column = …)或排序條件(ORDER BY column)中的數據列創建索引。只要有可能，就應該選擇一個數據最整齊、最緊湊的數據列(如一個整數類型的數據列)來創建索引。

2）. 唯一索引

        普通索引允許被索引的數據列包含重復的值。比如說，因為人有可能同名，所以同一個姓名在同一個”員工個人資料”數據表里可能出現兩次或更多次。
如果能確定某個數據列將只包含彼此各不相同的值，在為這個數據列創建索引的時候就應該用關鍵字UNIQUE把它定義為一個唯一索引。這么做的好處：一是簡化了MySQL對這個索引的管理工作，這個索引也因此而變得更有效率；二是MySQL會在有新記錄插入數據表時，自動檢查新記錄的這個字段的值是否已經在某個記錄的這個字段里出現過了；如果是，MySQL將拒絕插入那條新記錄。也就是說，唯一索引可以保證數據記錄的唯一性。事實上，在許多場合，人們創建唯一索引的目的往往不是為了提高訪問速度，而只是為了避免數據出現重復。

3）. 主索引

        在前面已經反復多次強調過：必須為主鍵字段創建一個索引，這個索引就是所謂的”主索引”。主索引與唯一索引的唯一區別是：前者在定義時使用的關鍵字是PRIMARY而不是UNIQUE。

 4）. 外鍵索引

         如果為某個外鍵字段定義了一個外鍵約束條件，MySQL就會定義一個內部索引來幫助自己以最有效率的方式去管理和使用外鍵約束條件。

 5）. 復合索引

        索引可以覆蓋多個數據列，如像INDEX(columnA, columnB)索引。這種索引的特點是MySQL可以有選擇地使用一個這樣的索引。如果查詢操作只需要用到columnA數據列上的一個索引，就可以使用復合索引INDEX(columnA, columnB)。不過，這種用法僅適用于在復合索引中排列在前的數據列組合。比如說，INDEX(A, B, C)可以當做A或(A, B)的索引來使用，但不能當做B、C或(B, C)的索引來使用。

4. 表鎖的問題

     跟性能相關的最重要的區別就是 MyISAM 和 InnoDB 實現的鎖機制不一樣！ MyISAM 使用的是表鎖， 而 InnoDB實現的是行鎖。

1) MyISAM為表級鎖

        由于MyISAM寫進程優先獲得鎖，使得讀鎖請求靠后等待隊列。不僅如此，即使讀請求先到鎖等待隊列，寫請求后 到，寫鎖也會插到讀鎖請求之前！這是因為MySQL認為寫請求一般比讀請求要重要。
        如果在大量更新操作的情況下，使得很難獲得讀鎖。從而造成阻塞。
        所以MyIsam不適合做大量更新操作的原因

2 )INNODB的行鎖是基于索引實現，如果不通過索引訪問數據，Innodb會使用表鎖

表級鎖更適合以查詢為主，只有少量按索引條件更新數據的應用。

行級鎖更適合于有大量按索引條件并發更新少量不同數據，同時又并發查詢。因為只鎖定要操作的行， 所以可以多個線程同時操作不同的行（只要不操作其他線程已經鎖定的行）。

5. MySQL server服務器配置優化

   1）使用show variables 了解服務器參數
   2）show status 了解服務器運行狀態，如鎖等待情況，當前連接數等
   3）影響mysql性能的重要參數：

         key_buffer_size設置索引塊的緩存大?。?span style="font-family: Helvetica, Tahoma, Arial, sans-serif;line-height: 25.200000762939453px">key_buffer_size是對MyISAM表性能影響最大的一個參數

        通過：

    mysql> show global status like 'key_read%';
+——————-+————+
| Variable_name     | Value      |
+——————-+————+
| Key_read_requests | 3465117712 |
| Key_reads         | 624        |
+——————-+————+

   Key_read_requests：從緩存讀取索引的請求次數。
   Key_reads：從磁盤讀取索引的請求次數。

   通常人們認為Key_read_requests / Key_reads越大越好

    需要適當加大key_buffer_size 

         table_cache數據庫打開表的緩存數量 ，每個連接進來，都會至少打開一個表緩存。因此
         table_cache和max_connections有關， 例如 對于200個并行運行的連接，應該讓表的緩存至少是200 *N
         N 是可以執行查詢的一個連接中的表的最大數

    4)  還有innodb_buffer_pool_size等innodb參數的設置

6. 磁盤IO優化

　　對于我們數據庫調優來說，磁盤I/O優化是首屈一指的調優重點，我們都知道木桶原理，短板絕對整體的好壞，而數據庫系統中這個短板正是由于我們使用的硬件設備里最弱的磁盤所導致。很多時候，我們會發現系統中I/O累得要死，而CPU卻在那里空閑等待，主要是由于I/O執行響應時間太長，處理讀寫 的速度遠遠趕落后于CPU的處理速度，這時我們會盡可能的讓操作放到內存中進行，由磁盤與CPU的關系，轉變成內存與CPU的關系。但是，我們始終不能回 避磁盤I/O的弱點，優化是必須的。

        磁盤搜索是巨大的性能瓶頸。當數據量變得非常大以致于緩存性能變得不可能有效時，該問題變得更加明顯。對于大數據庫，其中你或多或少地隨機訪問數據，你可以確 信對讀取操作需要至少一次硬盤搜索，寫操作需要多次硬盤搜索。要想使該問題最小化， 應使用搜索次數較少的磁盤。

1）使用磁盤陣列  RAID (廉價磁盤冗余陣列)

         RAID就是按照一定的策略將數據分布到若干物理磁盤上，這樣不僅增強了數據存儲的可靠性，而且提高數 據讀寫的性能 （RAID有不能的級別）
         1) 讀寫很頻繁的，可靠性要求也很高的，最好RAID 10
         2) 數據讀很頻繁，寫相對較少的，對可靠性一定要求的，選擇RAID 5

         3) 數據讀寫都很頻繁，但是可靠性要求不高的可以選擇RAID 0

2) 使用符號鏈接 分布I/O

          MYSQL在默認的情況下，數據庫和數據表都存放在參數datadir定義的目錄下，這樣如果不使用RAID或者邏輯卷，所有的數據都存放在一個磁盤設備上，無法發揮多磁盤并 行讀寫的優勢。

         可以將表和數據庫從數據庫目錄移動到其它的位置并且用指向新位置的符號鏈接進行替換。推薦的方法只需要將數據庫通過符號鏈接指到不同的磁盤。符號鏈接表僅作為是 最后的辦法。

        符號鏈接一個數據庫的方法是，首先在一些有空閑空間的硬盤上創建一個目錄，然后從 MySQL 數據目錄中創建它的一個符號鏈接。

      例如：

$ mkdir /dr1/databases/test  
$ ln -s /dr1/databases/test /path/to/datadir

      注意：只有 MyISAM 表完全支持符號鏈接。對于其它表類型，如果試圖在操作系統 中的文件上用前面的任何語句使用符號鏈接，可能會出現奇怪的問題。
對于 MyISAM 表的符號鏈接的處理如下：
     1. 在數據目錄指，一定會有表定義文件、數據文件和索引文件。數據文件和索引文件可 以移到別處和在數據目錄中符號鏈接替代。表定義文件不能進行符號鏈接替換。
     2. 可以分別通過符號鏈接將數據文件和索引文件指到不同的目錄。

     3. 如果 mysqld 沒有運行，符號鏈接可以從服務器命令行使用 ln -s 手動完成。同樣，通過使用 DATA DIRECTORY 和 INDEX DIRECTORY 選項創建表，你可以指示運行的 MySQL 服務器執行符號鏈接。
     4. myisamchk 不用數據文件或索引文件替換符號鏈接。它直接工作在符號鏈接指向的文件。任何臨時文件創建在數據文件或索引文件所處的目錄中。
     5. 注釋：當你刪掉一個表時，如果該表使用了符號鏈接，符號鏈接和該符號鏈接指向的 文件都被刪除掉。這就是你不應以系統 root 用戶運行 mysqld 或允許系統用戶對 MySQL數據庫目錄有寫訪問權限的原因。
     6. 如果你用 ALTER TABLE … RENAME 重命名一個表并且不將表移到另一個數據庫，數據庫目錄中的符號鏈接被重新命名為一個新名字并且數據文件和索引文件也相應地重新命名。
     7. 如果你用 ALTER TABLE … RENAME 移動一個表到另一個數據庫，表移動到另一個數據庫目錄。舊的符號鏈接和其所指向的文件被刪除。換句話說，新表不再被鏈接。
     8. 如果不使用符號鏈接，你應對 mysqld 使用 –skip-symbolic-links 選項以確保沒有人能夠使用 mysqld 來刪除或重新命名數據目錄之外的文件。

    表符號鏈接還不支持以下操作：
    1. ALTER TABLE 忽略 DATA DIRECTORY 和 INDEX DIRECTORY 表選項。
    2. BACKUP TABLE 和 RESTORE TABLE 不考慮符號鏈接。
    3. .frm 文件必須絕不能是一個符號鏈接（如前面所述，只有數據和索引文件可以是符鏈接）。如果試圖這樣做（例如，生成符號鏈接）會產生不正確的結果。

3) 禁止操作系統更新文件的atime屬性

7. 應用優化

1 )使用連接池
對于訪問數據庫來說，建立連接的代價比較昂貴，因此，我們有必要建立 " 連接池 " 以提高訪問的性能。我們可以把連接當作對象或者設備，池中又有許多已經建立的連接，訪 問本來需要與數據庫的連接的地方，都改為和池相連，池臨時分配連接供訪問使用，結果返 回后，訪問將連接交還。

2)減少對mysql的訪問，使用mem緩存等

3)負載均衡，復制分流查詢操作
           利用mysql的主從復制，分流更新操作和查詢操作
          1), 創建復制賬號：Gran replication slave on *.* to 'rel'@'10.0.1.2' identified by '123456'
          2), 修改主服務器的配置my.conf 開啟binlog和設置server-id
          3), 將主服務器的數據一致性恢復到從服務器，保證將要復制的數據時一只的，否則出問題
          4), 在從服務器上修改配置my.conf
               server-id=2
               master-host=10.0.1.3
               master-user='rel'
               master-password='123456'
               master-port='3306'
          5), 從服務器啟動slave線程： start slave
          show processlist 查看。

4) 分布式cluster 數據庫架構

8. 對表進行分拆

1）水平劃分
    如果某個表的數據太多，預期有上千條甚至上億以上，我們可以化整為0：拆表。
    這里就涉及到拆表的算法：
    記錄日志的表，也可以按周或者按月來拆。
    記錄用戶信息的表，按用戶id的hash算法來拆。

2）垂直拆分
　　如果表記錄數并不多，可能也就2、3萬條，但是字段卻很長，表占用空間很大，檢索表時需要執行大量I/O，嚴重降低了性能。這個時候需要把大的字段拆分到另一個表，并且該表與原表是一對一的關系。

轉自：http://blog.csdn.net/hguisu/article/details/5713180