常見的 MySQL 優化方法

選擇最合適的字段屬性

MySQL 是一種關系型數據庫，可以很好地支持大數據量的存儲，但是一般來說，數據庫中的表越小，在它上面執行的查詢也就越快。因此，在創建表的時候，為了獲得更好的性能，我們可以將表中字段的寬度舍得盡可能小。

例如：

1. 在定義郵政編碼這個字段時，如果將其設置為char(255)，顯然給數據庫增加了不必要的空間，甚至使用varchar這種類型也是多余的，因為char(6)就可以很好地完成了任務。
2. 如果可以的話，我們應該是用MEDIUMINT而不是BIGINT來定義整形字段。
3. 對于某些文本字段來說，例如“省份”或者“性別”，我們可以將他們定義為ENUM類型。因為在MySQL中，ENUM類型被當做數值型數據來處理，而數值型數據被處理起來的速度要比文本類型要快得多。

盡量把字段設置為 NOT NULL

在可能的情況下，盡量把字段設置為NOT NULL。這樣在將來執行查詢的時候，數據庫不用去比較NULL值。

使用連接（JOIN）來代替子查詢（Sub-Queries）

MySQL從4.1開始支持SQL的子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然后把這個結果作為過濾條件用在另一個查詢中。

使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，并且寫起來也很容易。如下所示：

SELECT * FROM customerinfo
WHERE customerid NOT IN (SELECT customerid FROM salesinfo)

如果使用連接（JOIN）來完成這個工作，速度將會快很多，尤其是當salesinfo表中對CustomerID建有索引的話，性能將會更好，查詢如下：

SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.customerid=salesinfo.customerid
WHERE salesinfo.customerid IS NULL

連接（JOIN）之所以更有效率一些，是因為MySQL不需要在內存中創建臨時表來完成這個邏輯上需要兩個步驟的查詢工作。

另外，如果你的應用程序有很多JOIN查詢，你應該確認兩個表中JOIN的字段是被建立過索引的。這樣MySQL內部 會啟動為你優化JOIN的SQL語句的機制。而且這些被用來JOIN的字段，應該是相同的類型的。

使用聯合（UNION）來代替手動創建的臨時表

MySQL從4.0的版本開始支持union查詢，它可以把需要使用臨時表的兩條或更多的select查詢合并的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證數據庫整齊、高效。使用union來創建查詢的時候，我們只需要用UNION作為關鍵字把多個select語句連接起來就可以了，要注意的是所有select語句中的字段數目要想同。下面的例子就演示了一個使用UNION的查詢。

SELECT name, phone FROM client UNION
SELECT name, birthdate FROM author UNION
SELECT name, supplier FROM product

另外，當我們可以確認不可能出現重復結果集或者不在乎重復結果集的時候盡量使用union all而不是union，因為union和union all的差異主要是前者需要將兩個或者多個結果集合并后再進行唯一性過濾操作，這就會涉及到排序，增加大量的CPU運算，增大資源消耗及延遲。

事務

盡管我們可以使用子查詢（Sub-Queries）、連接（JOIN）和聯合（UNION）來創建各種各樣的查詢，但不是所有的數據庫操作都可以只用一條或少數幾條SQL語句就可以完成的。更多的時候是需要用到一系列的語句來完成某種工作。但是在這種情況下，當這個語句塊中的某一條語句運行出錯的時候，整個語句塊的操作就會變得不確定起來。設想一下，要把某個數據同時插入兩個相關聯的表中，可能會出現這樣的情況：第一個表中成功更新后，數據庫突然出現意外狀況，造成第二個表中的操作沒有完成，這樣，就會造成數據的不完整，甚至會破壞數據庫中的數據。要避免這種情況，就應該使用事務，它的作用是：要么語句塊中每條語句都操作成功，要么都失敗。換句話說，就是可以保持數據庫中數據的一致性和完整性。

一般來說，事務必須滿足四個條件：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。

1. 原子性：一個事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾(Rollback)到事務開始的狀態，就像這個事務從來沒有執行過一樣。
2. 一致性：在事務開始之前和事務結束之后，數據庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及后續數據庫可以自發性地完成預定的工作。
3. 隔離性：數據庫允許多個事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導致數據的不一致。事務隔離分為不同的級別，包括讀未提交（Read uncommitted）、讀已提交（Read committed）、可重復讀（repeateable read）和串行化（Serializable）。
4. 持久性：事務處理結束后，對數據的修改就是永久的，即便系統故障也不會丟失。

事務以BEGIN關鍵字開始，COMMIT關鍵字結束。在這之間的一條SQL操作失敗，那么，ROLLBACK命令就可以把數據庫恢復到BEGIN開始之前的狀態。

BEGIN;INSERT INTO salesinfo SET customerid=14;UPDATE inventory SET quantity=11 WHERE item='book';
COMMIT;

事務的另一個作用是當多個用戶同時使用相同的數據源時，他可以使用鎖定數據庫的方式來為用戶提供一種安全的訪問機制，這樣可以保證用戶的操作不被其它的用戶所干擾。

鎖定表

盡管事務是維護數據庫完整性的一個非常好的方法，但卻因為它的獨占性，有時會影響數據庫的性能，尤其是在很大的應用系統中。由于在事務執行的過程中，數據庫將會被鎖定，因此其它的用戶請求只能暫時等待直到該事務結束。如果一個數據庫系統只有少數幾個用戶來使用，事務造成的影響不會成為一個太大的問題；但假設有成千上萬的用戶同時訪問一個數據庫系統，例如訪問一個電子商務網站，就會產生比較嚴重的響應延遲。

其實，有些情況下我們可以通過鎖定表的方法來獲得更好的性能。下面的例子就用鎖定表的方法來完成前面一個例子中事務的功能。

LOCK TABLE inventory WRITE SELECT quantity FROM inventory WHERE Item='book';
...
UPDATE inventory SET quantity=11 WHERE Item='book'; UNLOCKTABLES

這里，我們用一個select語句取出初始數據，通過一些計算，用update語句將新值更新到表中。包含有WRITE關鍵字的LOCKTABLE語句可以保證在UNLOCKTABLES命令被執行之前，不會有其它的訪問來對inventory進行插入、更新或者刪除的操作。

使用外鍵

鎖定表的方法可以維護數據的完整性，但是它卻不能保證數據的關聯性。這個時候我們就可以使用外鍵。

例如，外鍵可以保證每一條銷售記錄都指向某一個存在的客戶。在這里，外鍵可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一條沒有合法CustomerID的記錄都不會被更新或插入到salesinfo中。

CREATE TABLE customerinfo(customerid int primary key) engine = innodb;CREATE  TABLE salesinfo(salesid int not null,
customerid int not null, 
primary key(customerid, salesid),
foreign key(customerid)  
references customerinfo(customerid) on delete cascade) engine = innodb;

注意例子中的參數“ON DELETE CASCADE”。該參數保證當customerinfo表中的一條客戶記錄被刪除的時候，salesinfo表中所有與該客戶相關的記錄也會被自動刪除。如果要在MySQL中使用外鍵，一定要記住在創建表的時候將表的類型定義為事務安全表InnoDB類型。該類型不是MySQL表的默認類型。定義的方法是在CREATETABLE語句中加上engine=innoDB。

使用索引

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

那該對哪些字段建立索引呢？

一般說來，索引應建立在那些將用于JOIN、WHERE判斷和ORDERBY排序的字段上。盡量不要對數據庫中某個含有大量重復的值的字段建立索引，比如ENUM類型的字段，在這樣的字段上建立索引有可能降低數據庫的性能。

我們在創建表的時候可以同時創建合適的索引，也可以使用ALTERTABLE或CREATEINDEX在以后創建索引。此外，MySQL從版本3.23.23開始支持全文索引和搜索。全文索引在MySQL中是一個FULLTEXT類型索引，但僅能用于MyISAM類型的表。對于一個大的數據庫，將數據裝載到一個沒有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX創建索引，將是非常快的。但如果將數據裝載到一個已經有FULLTEXT索引的表中，執行過程將會非常慢。

避免函數索引

SELECT * FROM t WHERE YEAR(d) >= 2016;

由于MySQL不像Oracle那樣支持函數索引，即使d字段有索引，也會直接全表掃描。應改為：

SELECT * FROM t WHERE d >= '2016-01-01';

用 IN 來替換 OR

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

應改為：

SELECT * FROM t WHERE LOC_IN IN (10, 20, 30);

分組統計可以禁止排序

默認情況下，MySQL對所有GROUP BY col1，col2…的字段進行排序。如果查詢包括GROUP BY，想要避免排序結果的消耗，則可以指定ORDER BY NULL禁止排序。

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

批量 INSERT 插入

INSERT INTO t (id, name) VALUES(1, 'Bea');
INSERT INTO t (id, name) VALUES(2, 'Belle');
INSERT INTO t (id, name) VALUES(3, 'Bernice');

應改為：

INSERT INTO t (id, name) VALUES(1, 'Bea'), (2, 'Belle'), (3, 'Bernice');