表的最佳化:
1: 定長與變長分離
如id int, 佔4個位元組, char(4)佔4個字元長度,也是定長,
time 即每一單元值佔的位元組是固定的.
核心且常用字段,宜建成定長,放在一張表. 而varchar,
text,blob,這種變長字段,適合單放一張表,用主鍵與核心表關聯起來.
sql 執行查詢100000條資料會因為所有都是定常而跳過的非常迅速
2:常用字段和不常用字段要分離.
需要結合網站具體的業務來分析,分析字段的查詢場景,查詢頻度低的字段,單拆出來.
3:在1對多,需要關聯統計的字段上,添加冗餘字段.
減少關聯查詢
看如下BBS的效果統計的發帖數不要數而是透過在欄位下的添加冗餘字段，每次發文章更新文章數+1 這樣就會減少查詢強度
列選擇原則:
1:字段類型優先級整數> date,time > enum,char>varchar > blob,text
列的特徵分析:整數:定長,沒有國家/地區之分,沒有字符集的差異
比如tinyint 1,2,3,4,5 char(1) a,b,c,d,e, 從空間上,都是佔1個位元組,但是order
by 排序,前者快
原因: 後者需要考慮字符集與校對集(就是排序規則)
time定長,運算快,節省空間. 考慮時區,寫sql時不方便where
> '2005-10-12'; 時間存int 型；
enum: 能起來約束值的目的, 內部約束值的目的, 內部用整型來儲存,但與char聯查時,內部要經歷串與值的轉換
Char 定長, 考慮字符集和(排序)校對集
varchar, 不定長要考慮字符集的轉換與排序時的校對集,速度慢.
text/Blob 無法使用記憶體臨時表(排序等操作只能在磁碟上進行)
性別: 以utf8為例
char(1) , 3個字長位元組
enum ('男','女'); // 內部轉成數字來存,多了一個轉換過程
tinyint() , // 0 1 2 // 定長1個位元組.
sql 最佳化書籍《 MYSQL 高效能最佳化》
關於date/time的選擇,大師的明確意見,直接選int unsgined not null ,儲存時間戳http://www.xaprb.com/blog/2014/01/30/timestamps-in -mysql/
時間--->存成整型
2: 恰好夠用就行,不要慷慨(如smallint,varchar(N))
原因: 大的字段浪費內存,影響速度,
以年齡為例tinyint unsigned not null ,可以儲存255歲,足夠.用int浪費了3個位元組
以varchar(10) ,varchar(300)儲存的內容相同,但在表聯查時,varchar(300 )要花更多記憶體
3: 盡量避免用NULL()
原因: NULL不利於索引,要用特殊的字節來標註.
在磁碟上佔據的空間其實更大.(mysql5.7已對null做的改進,但查詢仍是不便)
實驗:
可以建立2張字段相同的表,一個允許為null,一個不允許為Null,各加入1萬條,查看索引文件的大小.可以發現,為null的索引要大些.(mysql5.5裡,關於null已經做了優化,大小區別已不明顯)
另外: null也不便於查詢,
where 列名=null;
where 列名!=null;都查不到值,
where 列名is null ,或is not null才可以查詢.
create table dictnn ( id int, word varchar(14) not null default '', key(word) )engine myisam charset utf8;
create table dictyn ( id int, word varchar(14), key(word) )engine myisam charset utf8;
alter table dictnn disable keys; alter table dictyn disable keys;
insert into dictnn select id,if(id%2,word,'') from dict limit 10000; insert into dictyn select id,if(id%2,word,null) from dict limit 10000;
alert table dictnn enable keys; alter table dictyn enable keys;
create table t2 ( id int, gender enum('man','woman'), key(gender) )engine myisam charset utf8;
create table t3 ( id int, gender char(5) not null default '', key(gender) )engine myisam charset utf8;
alter table t2 disable keys; alter table t3 disable keys;
insert into t2 select id,if(id%2,'man','woman') from dict limit 10000; insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;
alter table t2 enable keys; alter table t3 enable keys; mysql> select count(*) from t2 as ta,t2 as tb where ta.gender=tb.gender mysql> select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender
rrreeerrreeerrreeerrreeerrreee
列列
時間
Enumenum
10.5335333333353337
Enumchar
18.22
如果t2表的優勢不明顯,加大t3的gender列,char(15),
char(20)... t2表優勢逐漸明顯.
原因----無論enum('manmaman','womanwomanwoman')枚舉的字符多長,
內部都是用整型表示, 在內存中產生的數據大小不變,
而char型,卻在記憶體中產生的資料越來越多.
總結: enum 和enum類型關聯速度比較快
Enum 類型節省了IO
以上是mysql 最佳化（以上就是mysql ）表格的最佳化與列類型選擇的內容，更多相關內容請關注PHP中文網（www.php.cn）！
