MySQL資料庫優化方法

一、表類型MyISAM 和 InnoDB的區別

區別：

1. InnoDB支持事務，MyISAM不支持，對於InnoDB每一條SQL語言都預設封裝成事務，自動提交，這樣會影響速度，所以最好把多條SQL語言放在begin和commit之間，組成一個事務；

2. InnoDB支持外鍵，而MyISAM不支持。對一個包含外鍵的InnoDB表轉為MYISAM會失敗；

3. InnoDB是聚集索引，數據文件是和索引綁在一起的，必須要有主鍵，通過主鍵索引效率很高。但是輔助索引需要兩次查詢，先查詢到主鍵，然後再通過主鍵查詢到數據。因此，主鍵不應該過大，因為主鍵太大，其他索引也都會很大。而MyISAM是非聚集索引，數據文件是分離的，索引保存的是數據文件的指針。主鍵索引和輔助索引是獨立的。

4. InnoDB不保存表的具體行數，執行select count(*) from table時需要全表掃描。而MyISAM用一個變數保存了整個表的行數，執行上述語句時只需要讀出該變數即可，速度很快；

5. Innodb不支持全文索引，而MyISAM支持全文索引，查詢效率上MyISAM要高；

如何選擇：

1. 是否要支持事務，如果要請選擇innodb，如果不需要可以考慮MyISAM；

2. 如果表中絕大多數都只是讀查詢，可以考慮MyISAM，如果既有讀寫也挺頻繁，請使用InnoDB。

3. 系統奔潰後，MyISAM恢復起來更困難，能否接受；

4. MySQL5.5版本開始Innodb已經成為Mysql的預設引擎(之前是MyISAM)，說明其優勢是有目共睹的，如果你不知道用什麼，那就用InnoDB，至少不會差。

二、數據表的優化

一般來說數據表越小，執行查詢的速度就越快。

1、垂直拆分。就是把一個表的欄位拆分成多個表。例如文章或新聞表中，放置內容的欄位通常非常大，那麼就可以將內容欄位拆分開來，放到一張表中。在查詢的時候可以使用join查詢。

2.水平拆分。就是按行拆分表，例如用戶表，假定一個表中只存放一百萬條數據，當用戶數量每多一百萬時就新建一張表。

3、欄位設置。例如能設置成NOT NULL就不要保持預設的NULL，當執行查詢的時候，資料庫就不用去比較NULL值，從而提高效率；變長類型長度設置夠用就好，比如能設置成varchar(20)的就不要設置成varchar(200);對於某些只有固定幾個值可選的欄位，例如“狀態”，“性別”等，可以將它們定義為tinyint或int類型，因為在MySQL中，數值型數據被處理起來的速度要比文本類型快得多。

三、SQL語句優化

1、對查詢進行優化，主要是儘量避免執行全表掃描。

2、MySQL只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及LIKE 'ABC%'類型的LIKE語句；!=，<>操作符，將使引擎放棄使用索引而進行全表掃描。

3、應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。

4、應儘量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，可以使用UNION 操作符合併兩個或多個 SELECT 語句的結果集

5、作用LIKE '%ABC'或LIKE '%ABC%'類型的查詢也將導致全表掃描。若要提高效率，可以考慮使用全文檢索，在MySQL5.6以下只有MyISAM類型的表支持，在MySQL5.6以上Innodb引擎表也提供支持全文檢索，語句格式：

SELECT * FROM tab_name WHERE MATCH (列名1,列名2...列名n) AGAINST('詞1 詞2 詞3 ... 詞m'[ IN NATURAL LANGUAGE MODE| IN BOOLEAN MODE]);