mysql 最佳化（1）表的最佳化與列類型選擇

表的最佳化:

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

用整型來儲存的

2: enum列與enum列相關聯速度最快

3: enum列比(var)char的弱勢---在碰到與char關聯時,要轉化.要花時間.

4: 優點在於,當char非常長時,enum依然是整型固定長度.

當查詢的資料量越大時,enum的優勢越明顯.

5: enum與char/varchar關聯,因為要轉換,速度要比enum->enum,char->char要慢,

但有時也這樣用-----就是在數據量特別大時,可以節省IO.

試驗:

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）！