忘记密码   免费注册 查看新帖 | 论坛精华区

ChinaUnix.net

  平台 论坛 博客 认证专区 大话IT 视频 徽章 文库 沙龙 自测 下载 频道自动化运维 虚拟化 储存备份 C/C++ PHP MySQL 嵌入式 Linux系统
12下一页
最近访问板块 发新帖
查看: 12568 | 回复: 10

由MySQL中char和varchar效率想到的 [复制链接]

论坛徽章:
0
发表于 2010-04-27 09:12 |显示全部楼层
对比char和varchar随便在google或百度中搜一下得到的结论大概都是"char定长,varchar不定长,char要比varchar占用更多的空间,由于定长char的效率高于varchar,char最大255,varchar最大65536"更高级一点的数据是"char在存入和取出的时候,会自动把末尾的空格去掉,varchar会额外的多用1-2个字节来存放字符长度,列中有一个varchar会自动把char转换成varchar,而当varchar长度小于4时,自动的把varchar转换成char..."

一般认为空间换时间,现在磁盘又大又不值钱!

当使用全表都是char这的字段的时候,那么表属性Row_format是Fixed也就是静态表,与之对应的自然就是动态表Dynamic,静态表比动态表效率要高,主要是因为,基于两点:

1)没有碎片,每行的长度是固定,所以在频繁更新的场景下,尤其是某个字段由小变大.

2)方便数据文件指针的操作,在myisam中有一个数据文件指针的概念,是用来指向数据文件,比如在索引中指向数据文件.静态表的偏移量的固定不变的,而在动态表中每行的数据长度大小不一,就可能导致数据更多的开销

基于以上两点,所以静态表在查询,修改拥有较大优势,但是这个优势也是有适用场景的.

首先猜想char由于每次存取都会自动的削掉末尾的空格,而且数据文件也大,所以会使用更多的cpu和内存资源,尤其在取的时候,要是长短差距较大的时候,还是会很浪费操作的.

其次验证想法,首先测试插入性能,建了2张表并调用sp分别插入200W的数据

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的数据为138.21s,而isam_vc少了约20s为119.80s,这应该是每次插入的时候,mysql要把char字段填充到255的操作.观察数据文件大小,isam_vc为235.8M而isam_char为1.4G!

再测试查询,为了保证两者一致,首先把isam_char中数据删除,再把isam_vc中数据导入isam_char中,无索引,禁闭query_cache

truncate table isam_char;##观察了下数据文件,被穷空成0

insert into isam_char select * from isam_vc;##观察了下数据文件,依然是1.4G

select * from isam_char limit 1555555,5;

select * from isam_vc limit 1555555,5;

依旧多次查询去平均值,isam_char表平均值为10.50s,而isam_vc则为1.51s!

再看下创建索引,以及索引的使用情况

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)

还是不理想,所以在长度很不固定情况下使用char或静态表是很不理想的事,当然这里做的是全索引扫描,动态表的索引要小于动态表,估计最大的优势是使用索引去找数据和update,为了验证,再次测试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的数据差了1倍多!同时动态表的数据增大为480.7M.顺便读了几次数据,依然有差距

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)

不使用索引,做全表扫描,动态表快于静态表,这可能是数据文件大小的差异吧.

由于数据源和时间的问题,今天就不做扫描完索引再去读数据的实验了.

基本得出结论:mysql里,准确的说是myisam引擎中,静态表又或者char的长处在于更新操作,读取(不使用索引读取)和插入相比varchar又或动态表,并无优势.

本测试环境为winXP+mysql-5.1.44-community,配置为mysql默认配置.无修改.

注:做这个测试说明我好奇心很强,还有就是我真的很无聊,假如你看了,觉得错了,有明显漏洞的地方,欢迎指正!你也别骂我,我知道我是菜鸟,mysql程序不是有个选项叫做—i-am-a-dummy吗,我就是一个dummy,呵呵,浪费你的时候,我知道我错了!

附:插数据的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

论坛徽章:
0
发表于 2010-04-27 13:32 |显示全部楼层
也不是肯定的,要看对于什么引擎,以及适合什么样的具体场合才行。

论坛徽章:
9
每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25
发表于 2010-04-27 13:41 |显示全部楼层
赞好学并实践的好同志~~~

论坛徽章:
0
发表于 2010-04-27 16:06 |显示全部楼层
回复 3# cenalulu


   

论坛徽章:
0
发表于 2010-05-10 17:04 |显示全部楼层
学习了

论坛徽章:
0
发表于 2012-03-31 11:54 |显示全部楼层
三好学生!

论坛徽章:
0
发表于 2012-03-31 12:37 |显示全部楼层
好想法,关键是长度,建议你把长度设为30,60,90再给下测试结果

论坛徽章:
0
发表于 2012-05-03 13:34 |显示全部楼层
回复 7# kerlion


   

论坛徽章:
0
发表于 2012-08-26 10:08 |显示全部楼层
建议你把长度设为30,60,90再给下测试结果

论坛徽章:
7
金牛座
日期:2014-10-11 23:04:04水瓶座
日期:2014-10-10 02:06:57戌狗
日期:2014-09-26 16:39:442015亚冠之首尔
日期:2015-06-23 15:37:0015-16赛季CBA联赛之天津
日期:2016-01-22 18:58:2915-16赛季CBA联赛之佛山
日期:2016-05-31 19:18:0815-16赛季CBA联赛之同曦
日期:2016-08-10 16:26:33
发表于 2012-08-26 11:29 |显示全部楼层
lz有心人  学习了
您需要登录后才可以回帖 登录 | 注册

本版积分规则

久等啦!10张门票开启你的DTCC2017之旅

2017中国数据库技术大会将于2017年5月11-13日如约而至,本届大会以“数据驱动•价值发现”为主题,共设定2大主场和21个技术专场,云集海内外120+位技术大牛,共同探讨Oracle、MySQL、NoSQL、云端数据库、区块链、深度学习等领域的前瞻性热点话题。
即日起,填写DTCC2017会前调查问卷,即有机会赢取价值2600元的大会门票1张!仅限10张!
----------------------------------------
活动截止时间:2017年5月5日统一公布

问卷入口>>
  

北京皓辰网域网络信息技术有限公司. 版权所有 京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员  联系我们:
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP