對(duì)比char和varchar隨便在google或百度中搜一下得到的結(jié)論大概都是"char定長(zhǎng),varchar不定長(zhǎng),char要比varchar占用更多的空間,由于定長(zhǎng)char的效率高于varchar,char最大255,varchar最大65536"更高級(jí)一點(diǎn)的數(shù)據(jù)是"char在存入和取出的時(shí)候,會(huì)自動(dòng)把末尾的空格去掉,varchar會(huì)額外的多用1-2個(gè)字節(jié)來(lái)存放字符長(zhǎng)度,列中有一個(gè)varchar會(huì)自動(dòng)把char轉(zhuǎn)換成varchar,而當(dāng)varchar長(zhǎng)度小于4時(shí),自動(dòng)的把varchar轉(zhuǎn)換成char..."
一般認(rèn)為空間換時(shí)間,現(xiàn)在磁盤又大又不值錢!
當(dāng)使用全表都是char這的字段的時(shí)候,那么表屬性Row_format是Fixed也就是靜態(tài)表,與之對(duì)應(yīng)的自然就是動(dòng)態(tài)表Dynamic,靜態(tài)表比動(dòng)態(tài)表效率要高,主要是因?yàn)?基于兩點(diǎn):
1)沒(méi)有碎片,每行的長(zhǎng)度是固定,所以在頻繁更新的場(chǎng)景下,尤其是某個(gè)字段由小變大.
2)方便數(shù)據(jù)文件指針的操作,在myisam中有一個(gè)數(shù)據(jù)文件指針的概念,是用來(lái)指向數(shù)據(jù)文件,比如在索引中指向數(shù)據(jù)文件.靜態(tài)表的偏移量的固定不變的,而在動(dòng)態(tài)表中每行的數(shù)據(jù)長(zhǎng)度大小不一,就可能導(dǎo)致數(shù)據(jù)更多的開銷
基于以上兩點(diǎn),所以靜態(tài)表在查詢,修改擁有較大優(yōu)勢(shì),但是這個(gè)優(yōu)勢(shì)也是有適用場(chǎng)景的.
首先猜想char由于每次存取都會(huì)自動(dòng)的削掉末尾的空格,而且數(shù)據(jù)文件也大,所以會(huì)使用更多的cpu和內(nèi)存資源,尤其在取的時(shí)候,要是長(zhǎng)短差距較大的時(shí)候,還是會(huì)很浪費(fèi)操作的.
其次驗(yàn)證想法,首先測(cè)試插入性能,建了2張表并調(diào)用sp分別插入200W的數(shù)據(jù)
CREATE TABLE `isam_char` (
`content` char(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `isam_vc` (
`content` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
call insert_isam(2000000,'isam_char');
call insert_isam(2000000,'isam_vc');
多次平均isam_char插入200W的數(shù)據(jù)為138.21s,而isam_vc少了約20s為119.80s,這應(yīng)該是每次插入的時(shí)候,mysql要把char字段填充到255的操作.觀察數(shù)據(jù)文件大小,isam_vc為235.8M而isam_char為1.4G!
再測(cè)試查詢,為了保證兩者一致,首先把isam_char中數(shù)據(jù)刪除,再把isam_vc中數(shù)據(jù)導(dǎo)入isam_char中,無(wú)索引,禁閉query_cache
truncate table isam_char;##觀察了下數(shù)據(jù)文件,被窮空成0
insert into isam_char select * from isam_vc;##觀察了下數(shù)據(jù)文件,依然是1.4G
select * from isam_char limit 1555555,5;
select * from isam_vc limit 1555555,5;
依舊多次查詢?nèi)テ骄?isam_char表平均值為10.50s,而isam_vc則為1.51s!
再看下創(chuàng)建索引,以及索引的使用情況
mysql> create index index_char on isam_char(content);
Query OK, 2000001 rows affected (2 min 56.33 sec)
Records: 2000001 Duplicates: 0 Warnings: 0
mysql> create index index_vc on isam_vc(content);
Query OK, 2000001 rows affected (1 min 31.98 sec)
Records: 2000001 Duplicates: 0 Warnings: 0
mysql> select count(*) from isam_char where content=('iamauperman!iwillbeahero!!');
+----------+
| count(*) |
+----------+
| 199669 |
+----------+
1 row in set (0.56 sec)
mysql> select count(*) from isam_vc where content=('iamauperman!iwillbeahero!!');
+----------+
| count(*) |
+----------+
| 199669 |
+----------+
1 row in set (0.31 sec)
還是不理想,所以在長(zhǎng)度很不固定情況下使用char或靜態(tài)表是很不理想的事,當(dāng)然這里做的是全索引掃描,動(dòng)態(tài)表的索引要小于動(dòng)態(tài)表,估計(jì)最大的優(yōu)勢(shì)是使用索引去找數(shù)據(jù)和update,為了驗(yàn)證,再次測(cè)試update.
mysql> update isam_char set content='iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!';
Query OK, 2000001 rows affected (54 min 54.25 sec)
Rows matched: 2000001 Changed: 2000001 Warnings: 0
mysql> update isam_vc set content='iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!iwillbeahero!!iamauperman!';
Query OK, 2000001 rows affected (1 hour 59 min 25.73 sec)
Rows matched: 2000001 Changed: 2000001 Warnings: 0
果然啊,更新200W的數(shù)據(jù)差了1倍多!同時(shí)動(dòng)態(tài)表的數(shù)據(jù)增大為480.7M.順便讀了幾次數(shù)據(jù),依然有差距
mysql> select count(*) from isam_vc IGNORE INDEX(index_vc) where content='iamauperman!iwillbeahero!
!';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (10.55 sec)
mysql> select count(*) from isam_char IGNORE INDEX(index_char) where content='iamauperman!iwillbeah
ero!!';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (14.98 sec)
不使用索引,做全表掃描,動(dòng)態(tài)表快于靜態(tài)表,這可能是數(shù)據(jù)文件大小的差異吧.
由于數(shù)據(jù)源和時(shí)間的問(wèn)題,今天就不做掃描完索引再去讀數(shù)據(jù)的實(shí)驗(yàn)了.
基本得出結(jié)論:mysql里,準(zhǔn)確的說(shuō)是myisam引擎中,靜態(tài)表又或者char的長(zhǎng)處在于更新操作,讀取(不使用索引讀取)和插入相比varchar又或動(dòng)態(tài)表,并無(wú)優(yōu)勢(shì).
本測(cè)試環(huán)境為winXP+mysql-5.1.44-community,配置為mysql默認(rèn)配置.無(wú)修改.
注:做這個(gè)測(cè)試說(shuō)明我好奇心很強(qiáng),還有就是我真的很無(wú)聊,假如你看了,覺(jué)得錯(cuò)了,有明顯漏洞的地方,歡迎指正!你也別罵我,我知道我是菜鳥,mysql程序不是有個(gè)選項(xiàng)叫做—i-am-a-dummy嗎,我就是一個(gè)dummy,呵呵,浪費(fèi)你的時(shí)候,我知道我錯(cuò)了!
附:插數(shù)據(jù)的sp
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_isam`(p int,t char(10))
begin
set @i = 0;
set @str = "iamauperman!iwillbeahero!!";
repeat
#select name into @str from mysql.help_topic order by rand() limit 1;
set @str=substr(@str,1,25);
set @ins_str = repeat(@str,floor(rand()*10));
set @cmd=concat('insert into ',t,' (content) values("',@ins_str,'")');
prepare stmt from @cmd;
execute stmt;
set @i = @i+1;
until @i > p end repeat;
end
本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)
點(diǎn)擊舉報(bào)。
