由MySQL中char和varchar效率想到的

xiao7ng

對(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)在磁盤(pán)又大又不值錢(qiá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ù)更多的開(kāi)銷(xiāo)

基于以上兩點(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ò)了,有明顯漏洞的地方,歡迎指正!你也別罵我,我知道我是菜鳥(niǎo),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

yueliangdao0608

也不是肯定的，要看對(duì)于什么引擎，以及適合什么樣的具體場(chǎng)合才行。

cenalulu

贊好學(xué)并實(shí)踐的好同志～～～

xiao7ng

回復(fù) 3# cenalulu


   

xiehc

學(xué)習(xí)了

pygmalion666

三好學(xué)生！

jmyychen

回復(fù) 7# kerlion


   

ccd135

建議你把長(zhǎng)度設(shè)為30,60,90再給下測(cè)試結(jié)果

wiliiwin

lz有心人  學(xué)習(xí)了