概述

數(shù)據(jù)庫的優(yōu)化肯定是先分析再具體優(yōu)化，前面已經(jīng)介紹了在對mysql數(shù)據(jù)庫sql做優(yōu)化時(shí)的一些方法，今天主要從創(chuàng)建表、設(shè)計(jì)表及具體sql優(yōu)化幾個(gè)方面來介紹一些性能優(yōu)化的方法。

[[271829]]

01.創(chuàng)建表時(shí)的性能優(yōu)化

1. 永遠(yuǎn)為每張表設(shè)置一個(gè) ID

每張表都應(yīng)該設(shè)置一個(gè) ID 字段為主鍵，該主鍵應(yīng)為 INT 或 UNSIGNED 類型，并設(shè)置上自動增加的 AUTO_INCREMENT 標(biāo)志。因?yàn)槭褂? VARCHAR 類型的主鍵，會使得性能下降。

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

2. 為搜索字段建索引

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

3. 使用 ENUM 而不是 VARCHAR

ENUM 類型是非常快和緊湊的。在實(shí)際上，其保存的是 TINYINT，但其外表上顯示為字符串。這樣一來，用這個(gè)字段來做一些選項(xiàng)列表變得相當(dāng)?shù)耐昝馈?/p>

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

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

4. 盡可能的使用 NOT NULL

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

5. 把IP地址存成 UNSIGNED INT

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

6.必須使用UTF8字符集

萬國碼，無需轉(zhuǎn)碼，無亂碼風(fēng)險(xiǎn)，節(jié)省空間

02.設(shè)計(jì)表時(shí)的性能優(yōu)化

1. 選擇正確的存儲引擎

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

InnoDB 的趨勢會是一個(gè)非常復(fù)雜的存儲引擎，對于一些小的應(yīng)用，它會比 MyISAM 還慢。他是它支持“行鎖” ，于是在寫操作比較多的時(shí)候，會更優(yōu)秀。并且，他還支持更多的高級應(yīng)用，比如：事務(wù)。

相對來說支持事務(wù)、行級鎖、并發(fā)性能更好、CPU及內(nèi)存緩存頁優(yōu)化使得資源利用率更高，所以大多數(shù)情況下還是用innodb引擎吧。

2. 固定長度的表會更快

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

固定長度的表會提高性能，因?yàn)?MySQL 搜尋得會更快一些，因?yàn)檫@些固定的長度是很容易計(jì)算下一個(gè)數(shù)據(jù)的偏移量的，所以讀取的自然也會很快。而如果字段不是定長的，那么，每一次要找下一條的話，需要程序找到主鍵。

并且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的字段會浪費(fèi)一些空間，因?yàn)槎ㄩL的字段無論你用不用，他都是要分配那么多的空間。

3. 垂直分割

“垂直分割”是一種把數(shù)據(jù)庫中的表按列變成幾張表的方法，這樣可以降低表的復(fù)雜度和字段的數(shù)目，從而達(dá)到優(yōu)化的目的。(因?yàn)槟壳暗墓窘?jīng)常一張表有很多個(gè)字段，太過復(fù)雜，這個(gè)也是后面需要去做分割的)

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

例二： 有一個(gè)叫 “last_login” 的字段，它會在每次用戶登錄時(shí)被更新。但是，每次更新時(shí)會導(dǎo)致該表的查詢緩存被清空。所以，你可以把這個(gè)字段放到另一個(gè)表中，這樣就不會影響你對用戶ID，用戶名，用戶角色的不停地讀取了，因?yàn)椴樵兙彺鏁湍阍黾雍芏嘈阅堋?/p>

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

03.優(yōu)化 SQL 語句

1. 使用查詢緩存

查看是否開啟緩存：

mysql> select @@query_cache_type;

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

query_cache_type = 1query_cache_size = 600000

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

2. 當(dāng)只要一行數(shù)據(jù)時(shí)使用 LIMIT 1

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

3. 在 JOIN 表的時(shí)候使用相當(dāng)類型的例，并將其索引

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

4. 避免 SELECT *

從數(shù)據(jù)庫里讀出越多的數(shù)據(jù)，那么查詢就會變得越慢。所以，應(yīng)該養(yǎng)成需要什么就取什么的好的習(xí)慣。

5. 拆分大的 DELETE 或 INSERT 語句

如果你需要在一個(gè)在線的網(wǎng)站上去執(zhí)行一個(gè)大量的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個(gè)網(wǎng)站停止相應(yīng)。因?yàn)檫@兩個(gè)操作是會鎖表的，表一鎖住了，別的操作都進(jìn)不來了。

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

04.其它

1. EXPLAIN 你的 SELECT 查詢

使用 EXPLAIN 關(guān)鍵字可以讓你知道 MySQL 是如何處理你的 SQL 語句的。這可以幫你分析你的查詢語句或是表結(jié)構(gòu)的性能瓶頸。

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

2. 從 PROCEDURE ANALYSE() 取得建議

PROCEDURE ANALYSE() 會讓 MySQL 去分析字段和其實(shí)際的數(shù)據(jù)，并會提供一些有用的建議(只是建議)。只有表中有實(shí)際的數(shù)據(jù)，這些建議才會變得有用，因?yàn)橐鲆恍┐蟮臎Q定是需要有數(shù)據(jù)作為基礎(chǔ)的。

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