《Mysql實(shí)例MySQL 建表的優(yōu)化策略 小結(jié)》要點(diǎn)：
本文介紹了Mysql實(shí)例MySQL 建表的優(yōu)化策略 小結(jié)，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

目錄
1. 字符集的選擇 1
2. 主鍵 1
3. 外鍵 2
4. 索引 2
4.1. 以下情況適合于創(chuàng)建索引 2
4.2. 以下的情況下不適合創(chuàng)建索引 3
4.3. 聯(lián)合索引 3
4.4. 索引長度 4
5. 特殊字段 4
5.1. 冗余字段 4
5.2. 分割字段 4
5.3. BLOB和CLOB 5
6. 特殊 5
6.1. 表格分割 5
6.2. 使用非事務(wù)表類型 5

1. 字符集的選擇
如果確認(rèn)全部是中文,不會(huì)使用多語言以及中文無法表示的字符,那么GBK是首選.
采用UTF-8編碼會(huì)占用3個(gè)字節(jié),而GBK只需要2個(gè)字節(jié).
2. 主鍵
盡可能使用長度短的主鍵
系統(tǒng)的自增類型AUTO_INCREMEN, 而不是使用類似uuid()等類型.如果可以使用外鍵做主鍵,則更好.比如1：1的關(guān)系,使用主表的id作為從表的主鍵.
主鍵的字段長度需要根據(jù)需要指定.
tinyint 從 2的7次方-1 ：-128 到 127
smallint 從 2的15次方-1 ：-32768 到 32767
mediumint 表示為 2的23次方-1： 從 -8388608 到8388607
int 表示為 2的31次方-1
bigint 表示為 2的63次方-1

在主鍵上無需建單獨(dú)的索引,因?yàn)橄到y(tǒng)內(nèi)部為主鍵建立了聚簇索引.
允許在其它索引上包含主鍵列.
3. 外鍵
外鍵會(huì)影響插入和更新性能,對(duì)于批量可靠數(shù)據(jù)的插入,建議先屏蔽外鍵檢查.
對(duì)于數(shù)據(jù)量大的表,建議去掉外鍵,改由應(yīng)用程序進(jìn)行數(shù)據(jù)完整性檢查.
盡可能用選用對(duì)應(yīng)主表的主鍵作作為外鍵,避免選擇長度很大的主表唯一鍵作為外鍵.
外鍵是默認(rèn)加上索引的
4. 索引
創(chuàng)建索引,要在適當(dāng)?shù)谋?適當(dāng)?shù)牧袆?chuàng)建適當(dāng)數(shù)量的適當(dāng)索引.在查詢優(yōu)先和更新優(yōu)先之間做平衡.
4.1. 以下情況適合于創(chuàng)建索引
在經(jīng)常需要搜索的列上,可以加快搜索的速度
在作為主鍵的列上,強(qiáng)制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)
在經(jīng)常用在連接的列上,這些列主要是一些外鍵,可以加快連接的速度
在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引,因?yàn)樗饕呀?jīng)排序,其指定的范圍是連續(xù)的
在經(jīng)常需要排序的列上創(chuàng)建索引,因?yàn)樗饕呀?jīng)排序,這樣查詢可以利用索引的排序,加快排序查詢時(shí)間
在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引,加快條件的判斷速度.

4.2. 以下的情況下不適合創(chuàng)建索引
對(duì)于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引.這是因?yàn)?既然這些列很少使用到,因此有索引或者無索引,并不能提高查詢速度.相反,由于增加了索引,反而降低了系統(tǒng)的維護(hù)速度和增大了空間需求.
對(duì)于那些只有很少數(shù)據(jù)值的列也不應(yīng)該增加索引.這是因?yàn)?由于這些列的取值很少,例如人事表的性別列,在查詢的結(jié)果中,結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例,即需要在表中搜索的數(shù)據(jù)行的比例很大.增加索引,并不能明顯加快檢索速度.
對(duì)于那些定義為text, image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引.這是因?yàn)?這些列的數(shù)據(jù)量要么相當(dāng)大,要么取值很少.
當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí),不應(yīng)該創(chuàng)建索引.這是因?yàn)?修改性能和檢索性能是互相矛盾的.
如果表數(shù)據(jù)很少,比如每個(gè)省按市做匯總的表,一般低于2000,且數(shù)據(jù)量基本沒有變化.此時(shí)增加索引無助于查詢性能,卻會(huì)極大的影響更新性能.

當(dāng)增加索引時(shí),會(huì)提高檢索性能,但是會(huì)降低修改性能.當(dāng)減少索引時(shí),會(huì)提高修改性能,降低檢索性能.因此,當(dāng)對(duì)修改性能的要求遠(yuǎn)遠(yuǎn)大于檢索性能時(shí),不應(yīng)該創(chuàng)建索引.
4.3. 聯(lián)合索引
在特定查詢里,聯(lián)合索引的效果高于多個(gè)單一索引,因?yàn)楫?dāng)有多個(gè)索引可以使用時(shí),MySQL只能使用其中一個(gè).
在查詢里,同時(shí)用到了聯(lián)合索引包含的前幾個(gè)列名,都會(huì)使用到聯(lián)合索引,否則將部分或不會(huì)用到.比如我們有一個(gè)firstname、 lastname、age列上的多列索引,我們稱這個(gè)索引為fname_lname_age.當(dāng)搜索條件是以下各種列的組合時(shí),MySQL將使用 fname_lname_age索引：
firstname,lastname,age
firstname,lastname
firstname
從另一方面理解,它相當(dāng)于我們創(chuàng)建了(firstname,lastname,age)、(firstname,lastname)以及(firstname)這些列組合上的索引.
4.4. 索引長度
對(duì)于CHAR或者Varchar的列,索引可以根據(jù)數(shù)據(jù)的分布情況,用列的一部分參與創(chuàng)建索引.
create index idx_t_main on t_main(name(3));
這里就是指定name的前三個(gè)字符參與索引,而不是全部
最大允許的長度為1000個(gè)字節(jié),對(duì)已GBK編碼則是500個(gè)漢字
5. 特殊字段
5.1. 冗余字段
就是用空間換取時(shí)間.如果大表查詢里經(jīng)常要join某個(gè)基礎(chǔ)表,且這個(gè)數(shù)據(jù)基本不變,比如人的姓名,城市的名字等.一旦基礎(chǔ)表發(fā)生變動(dòng),則需要更新所有涉及到的冗余表.
5.2. 分割字段
如果經(jīng)常出現(xiàn)以某個(gè)字段的某個(gè)局部進(jìn)行檢索和匯總(substring()),可以考慮將這一部分獨(dú)立出來.
比如統(tǒng)計(jì)姓名里,每種姓氏的人數(shù),可以考慮實(shí)現(xiàn)就按照姓和名分別保存,而不是一個(gè)字段.
還有就是某些上下級(jí)結(jié)構(gòu)的實(shí)現(xiàn),也可以考慮將不同的級(jí)別放在不同的字段里.
5.3. BLOB和CLOB
此類字段一般數(shù)據(jù)量很大,建議設(shè)計(jì)上數(shù)據(jù)庫可以只保存其外部連接,而數(shù)據(jù)以其它方式保存,比如系統(tǒng)文件.
6. 特殊
6.1. 表格分割
如果一個(gè)表有許多的列,但平時(shí)參與查詢和匯總的列卻并不是很多,此時(shí)可以考慮將表格拆分成2個(gè)表,一個(gè)是常用的字段,另一個(gè)是很少用到的字段.
6.2. 使用非事務(wù)表類型
MySQL支持多種表類型,其中InnoDB類型是支持事物的,而MyISAM類型是不支持的,但MyISAM速度更快.對(duì)于某些數(shù)據(jù),比如地理行政劃分,民族等不可能參與事務(wù)的數(shù)據(jù),可以考慮用MyISAM類型的表格.
但I(xiàn)nnoDB的表,將無法用MyISAM表數(shù)據(jù)做外鍵約束了.
MyISAM表參與的事務(wù),其InnoDB表可以正常的提交和回滾,但不影響MyISAM表.

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/1243.html