概述

數據庫的優化肯定是先分析再具體優化，前面已經介紹了在對mysql數據庫sql做優化時的一些方法，今天主要從創建表、設計表及具體sql優化幾個方面來介紹一些性能優化的方法。

[[271829]]

01.創建表時的性能優化

1. 永遠為每張表設置一個 ID

每張表都應該設置一個 ID 字段為主鍵，該主鍵應為 INT 或 UNSIGNED 類型，并設置上自動增加的 AUTO_INCREMENT 標志。因為使用 VARCHAR 類型的主鍵，會使得性能下降。

這里，只有一個情況是例外，那就是 “關聯表” 的 “外鍵”，也就是說，這個表的主鍵，通過若干個別的表的主鍵構成。我們把這個情況叫做 “外鍵”。比如：有一個 “學生表” 有學生的 ID，有一個 “課程表” 有課程 ID，那么，“成績表” 就是 “關聯表” 了，其關聯了學生表和課程表，在成績表中，學生 ID 和課程 ID 叫 “外鍵” 其共同組成主鍵。

2. 為搜索字段建索引

這個簡單來說就是創建表時，如果后面針對這個表的查詢總會涉及到某個字段，或者在代碼里面寫好了的字段，這種就可以考慮去建索引。

3. 使用 ENUM 而不是 VARCHAR

ENUM 類型是非常快和緊湊的。在實際上，其保存的是 TINYINT，但其外表上顯示為字符串。這樣一來，用這個字段來做一些選項列表變得相當的完美。

如果你有一個字段，比如 “國家”，你知道這些字段的取值是有限而且固定的，那么，你應該使用 ENUM 而不是 VARCHAR。

ENUM 是 MySQL 數據庫特有的字段類型，使用后會影響遷移到其它數據庫。所以，如果以后要改數據庫的情況，一定要慎用。

4. 盡可能的使用 NOT NULL

應該總是讓你的字段保持 NOT NULL，這樣相對比較節省空間(NULL 也是需要空間的)。

5. 把IP地址存成 UNSIGNED INT

如果使用整形來存放 IP 而不是 VARCHAR(15) 字段，節省了很多的空間(需要寫一個 IP 轉換的函數)。

6.必須使用UTF8字符集

萬國碼，無需轉碼，無亂碼風險，節省空間

02.設計表時的性能優化

1. 選擇正確的存儲引擎

MyISAM 適合于一些需要大量查詢的應用，但其對于有大量寫操作并不是很好。甚至你只是需要 update 一個字段，整個表都會被鎖起來，而別的進程，就算是讀進程都無法操作直到讀操作完成。另外，MyISAM 對于 SELECT COUNT(*) 這類的計算是超快無比的。

InnoDB 的趨勢會是一個非常復雜的存儲引擎，對于一些小的應用，它會比 MyISAM 還慢。他是它支持“行鎖” ，于是在寫操作比較多的時候，會更優秀。并且，他還支持更多的高級應用，比如：事務。

相對來說支持事務、行級鎖、并發性能更好、CPU及內存緩存頁優化使得資源利用率更高，所以大多數情況下還是用innodb引擎吧。

2. 固定長度的表會更快

表中沒有如下類型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一個這些字段，那么這個表就不是“固定長度靜態表”了，這樣，MySQL 引擎會用另一種方法來處理。

固定長度的表會提高性能，因為 MySQL 搜尋得會更快一些，因為這些固定的長度是很容易計算下一個數據的偏移量的，所以讀取的自然也會很快。而如果字段不是定長的，那么，每一次要找下一條的話，需要程序找到主鍵。

并且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的字段會浪費一些空間，因為定長的字段無論你用不用，他都是要分配那么多的空間。

3. 垂直分割

“垂直分割”是一種把數據庫中的表按列變成幾張表的方法，這樣可以降低表的復雜度和字段的數目，從而達到優化的目的。(因為目前的公司經常一張表有很多個字段，太過復雜，這個也是后面需要去做分割的)

例一：在Users表中有一個字段是家庭地址，這個字段是可選字段，相比起，而且你在數據庫操作的時候除了個人信息外，你并不需要經常讀取或是改寫這個字段。那么，為什么不把他放到另外一張表中呢? 這樣會讓你的表有更好的性能，大量的時候，對于用戶表來說，只有用戶ID，用戶名，口令，用戶角色等會被經常使用。小一點的表總是會有好的性能。

例二： 有一個叫 “last_login” 的字段，它會在每次用戶登錄時被更新。但是，每次更新時會導致該表的查詢緩存被清空。所以，你可以把這個字段放到另一個表中，這樣就不會影響你對用戶ID，用戶名，用戶角色的不停地讀取了，因為查詢緩存會幫你增加很多性能。

另外，需要注意的是，這些被分出去的字段所形成的表，你不會經常性地去Join他們，不然的話，這樣的性能會比不分割時還要差，而且，會是極數級的下降。

03.優化 SQL 語句

1. 使用查詢緩存

查看是否開啟緩存：

mysql> select @@query_cache_type;

開啟緩存，修改 my.cnf，在末尾加入，重啟MySQL生效：

query_cache_type = 1query_cache_size = 600000

啟用MySQL查詢緩存可以極大地減低數據庫服務器的CPU使用率，實際使用情況是：開啟前CPU使用率120%左右，開啟后降到了10%。不過使用查詢的緩存的限制非常多。當使用場景中以只讀為主，很少有更新的情況時，再考慮開啟查詢緩存。

2. 當只要一行數據時使用 LIMIT 1

在這種情況下，加上 LIMIT 1 可以增加性能。這樣一樣，MySQL 數據庫引擎會在找到一條數據后停止搜索，而不是繼續往后查少下一條符合記錄的數據。

3. 在 JOIN 表的時候使用相當類型的例，并將其索引

如果有很多 JOIN 的操作，JOIN 的字段應該加索引，同時保證這些字段的類型一致。

4. 避免 SELECT *

從數據庫里讀出越多的數據，那么查詢就會變得越慢。所以，應該養成需要什么就取什么的好的習慣。

5. 拆分大的 DELETE 或 INSERT 語句

如果你需要在一個在線的網站上去執行一個大量的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的，表一鎖住了，別的操作都進不來了。

執行這種大量的 DELETE 和 INSERT，可以分成幾部分執行，每執行一部分就暫停一下再執行。

04.其它

1. EXPLAIN 你的 SELECT 查詢

使用 EXPLAIN 關鍵字可以讓你知道 MySQL 是如何處理你的 SQL 語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。

查看 rows 列可以讓我們找到潛在的性能問題。

2. 從 PROCEDURE ANALYSE() 取得建議

PROCEDURE ANALYSE() 會讓 MySQL 去分析字段和其實際的數據，并會提供一些有用的建議(只是建議)。只有表中有實際的數據，這些建議才會變得有用，因為要做一些大的決定是需要有數據作為基礎的。

mysql>select * from mysql.user procedure analyse();