Mysql高性能学习笔记-02
Mysql高性能学习笔记2
刘岩
Blog:suhuanzheng7784877.iteye.com
1.前言
高性能Mysql中的第二章-基准测试和第三章-服务器性能剖析是需要全局考虑的问题,不同的应用场景,基准测试的方式和输入数据是不太一样的。所以我们后续再讨论这两个问题,先放过去,直接进行优化schema和数据类型的这一话题。
2.优化数据类型
优化数据类型,基本上是用在建表和修改表的场景上,整个优化数据类型这一话题说下来,基本上都是集中于:对于DB数据的高效存储和高效查询。在原生的Mysql中,数据类型大体上分为以下几种:整数类型、实数类型、字符串类型、日期时间类型、位数类型、特殊类型。
优化数据类型基本上参照以下几个原则:
1):使用小类型的数据类型,能用int的别用long。小数据类型在磁盘寻址的时候占用更少的资源,也减少了CPU的运算时间,这样在iowait的时候就不会因为大字段而消耗过多资源。
2):简单类型优先,这个就需要结合应用层语言的知识来阐述了,比如Java中的int类型和Integer类型,哪个更耗资源,答案肯定是Integer,在《Java代码优化》中就曾经提出过,使用原始类型表述属性值。在Mysql也是如此,能使用最简单的类型代表字段的,尽量使用简单类型。这样更贴近于CPU原生支持的计算类型。比如使用整型存储时间戳;用整型存储IP地址;用整型存储货币浮点,在应用层,再用乘除法换算小数点的精度。
3):不是必要时刻,不要使用NULL,让所有字段哪怕是有默认的值,也要非空。对于优化索引,如果字段是NULL,无法对其NULL进行索引排列。不过InnoDB对于NULL是做了特殊的bit位存储。
3.整型类型
Tinyint(8位)
范围:无符号(0~256)、有符号(-128~127)
场景:一般用于存储数字字典,常量表的id,因为数据量十分有限,又是常量表,所以可以用它存储
Smallint(16位)
范围:无符号(0~65536)、有符号(-32768~32767)
场景:Tinyint的替代品,若常量表数据比较多,比如中国的省-市-自治区-区县-村镇,到这个范围下,基本够用了。中国有65536个村镇(区县)吗?
Mediumint(24位)
范围:无符号(0~16777216)、有符号(-8388608~8388607)
场景:1000w以内的数据,这个若是日志表,又是在一段时间内数据量可控,定时清理,Mediumint不失为是轻量级的int的一种id选择。
Int(32位):大多数场景,一般Java的int也支持不了这么长的整数位!
范围:无符号(0~4294967296)、有符号(-2147483648~2147483647)
场景:大多数的自增id场景,基本够用了。无符号40多亿数据,一般的中小型,互联网,基本够用。
Bigint(64位)范围:天文数字,在Java中必须特殊处理该数字类型——BigDecimal进行处理。
范围:无符号(0~18446744073709551616)、有符号(-922337203685478~922337203685477)。
场景:使用关系型数据库存储海量数据的id。千万大一位是亿,亿大一位是兆,兆在大一位是什么????不过数据量在这个范围,很难想象还用RDBMS进行管理。
有符号与无符号的最大区别就是是否支持负数。Unsigned一旦被选择上了,表示不允许负数,也就是存储无符号数。一般情况下无符号int类型的字段几乎可以满足系统要求了,就算是自增id类型。40多亿的mysql数据量也已经比较不小了。日交易量记录上千万比记录,一个月也就区区3亿记录。如果大于这个数量级的数据,又是实时数据,应该考虑分表分库。或者借助NoSQL,将数据量散列拆分开。扯远了,这里就是告诉大家,数值类型字段支持的范围。
4.实数类型
其实基本上也就是指含有小数的数,也就是浮点类型的数据类型。
Float:4个字节存储
Double:8个字节存储
Decimal:允许65个数字
这里有位仁兄总结的浮点型和定点型计算的文章,很不错http://www.163ns.com/zixun/post/5226.html。
基本上float可以用作百分比,有点误差没关系,double精确度比float大。而Decimal是完全金额类型计算。有的非敏感的,金额不是特别精确的系统业务场景,笔者也见过也有人使用double的。(你说那些不精确的,被四舍的钱都哪去了,都归谁了?100个人也就算了,如果涉及到1000w个人,每个人被四舍了的几厘钱,甚至到分钱误差,加起来够买房子了吧?)
存储以及计算消耗代价:Float<Double<Decimal
而原生的浮点类型,CPU可以直接参与计算,好像评价CPU的性能就是看待CPU每秒可以运行多少浮点型运算。
对于支持浮点类型计算的CPU厂商如下:AMD&Intel,至于谁的浮点计算能力更强,不同的产品系列,随时代而变吧。笔者个人倾向于因特尔的至强处理器。
5.字符串类型
字符串类型主要分为varchar、char与blob、text之间的PK了。
一定要将字符串类型的字段调优到极致,因为数据库中,我们面对最多的类型也就是字符串,而我们每天面对的最多的场景也就是对文字的处理。
varchar类型:用于存储可变长的字符串,比定长char类型节省空间(在通常情况下)。除非设置row_format=fixed,每一行是定长存储。varchar额外需要1~2个字节存储字符串的长度。当列的最大长度<=255字节,用1个字节存储长度。否则采取2个字节。而且在Mysql5以后,varchar字段不会将末尾的空格剔除了。
char类型:char是定长类型,那么在存取过程中,会根据字符串长度老老实实分配足够的空间。定长字符串类型不容易产生磁盘碎片,对于定长短列,char比varchar更有效。比如存储MD5或者SHA1值。
Blob类型:
存储二进制类型的大字段数据,没有排序规则以及字符集。
类型成员有:tinyblob;blob;mediumblob;longblob。
一般情况下存储图片、文档文件,用之。存储引擎在blob很大时借助外部存储(操作系统FS接口)进行特殊处理。
Text类型-对应于Oracle的clob:
存储字符方式存储大字段类型数据,有排序规则和字符集。
类型成员有:tinytext;text;mediumtext;longtext。
一般情况下存储文章,html页面内容。同理,在text很大时借助外部存储,进行特殊处理。
经验:
1)一般获取blob或者text记录的时候,将原始记录值进行截断——substring(字段名,大小)函数。之后再转换成为相应的字符串。这样可以使用到Mysql的内存临时表了,而避免了从磁盘上去取数据的IO。
2)临时表的大小超过配置的max_ heap_table_size(tmp_table_size)的时候内存临时表将使用磁盘临时表。(也就是说将内存密集型的case负载到了IO密集型)
1.枚举类型
Mysql存取枚举,紧凑。一般代替常用的字符串类型。Mysql将枚举列表的个数将其压缩位1~2个字节存储。之后,再将每一个枚举值保存为一个整数数字,将整数数字与枚举字符串的值做键值对儿的映射。也就是说,实际上表中引用枚举的字段值存储的是数字。
实验证明,着实如此。
CREATE TABLE `user2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `type` enum('魏','蜀','吴') CHARACTER SET utf8 DEFAULT '魏', PRIMARY KEY (`id`) ) ENGINE=InnoDB |
执行查询的时候将type字段都加上一个数字,得出来的结果居然是数字,证明枚举底层使用的是数值类型进行的存取枚举。而且若是非要枚举做外键,那么基于基准测试给出的结果,枚举与枚举之间的外键关联QPS是最高的。Mysql内部对枚举的数值做了相应的排序优化。
场景:能够使用枚举做常量时,尽量不要用字符串类型。
2.日期和时间
日期和时间类型有以下几种:date;time;year;timestamp;datetime;
date:相当于截取了datetime的date,范围时从公元0年1月1日,可以到公元9999年12月31日。
time:相当于截取了datetime的time,范围就是一天的24小时。
year:比较尴尬,临界值是69和70,输入69,基本上代表2069年。70就是代表1970年。范围值是0~99,分别代表,0~69:2000~2069;70~99:1970~1999。不是特殊情况,基本弃用。
最常用的应该是datetime与timestamp。
datetime:使用8个字节存储日期与时间,那么可以得出结论,date使用4个字节,time也是4个字节。精确到秒级别,与时区无关。范围是从1000年到9999年的日期和时间。
timestamp:使用4个字节存储日期与时间,不过范围只能表示从1970年~2038年。如果没有什么意外,看到这篇文章的同志们,大多数都能活到那一年,之后会不会出现timestamp2这种类型来扩大时间戳的范围,那就得看是不是有支持更大整型数值的类型出现了。在应用层使用long类型插入该字段的值,最后可以存储正确的日期时间,而且该字段依赖于时区。做国际化产品的时候需要特别注意!
3.SET类型
用于存储集合类型的集合类,集合元素里面基本上存储的是常量值,书中举了一个比较贴切的列子,就是权限控制的权限集合。其实也是代表一个人的聚合元素。但是呢,其实权限控制完全用整形也可以表示,就是类似于linux的权限数字,比如777代表该文件夹无任何限制可以被其他用户使用,访问,修改。
对于SET类型(mysql数据库中),在Java应用层获取该类型的值,使用字符串就可以,不过获取的值还需要另外处理,拆解字符串为字符串数组(使用,进行拆分)。
4.特殊字段-ipv4地址的存取
存取ip地址可以使用mysql中的两个函数将ipv4字符串转换成为整数,整数的存取比字符串快。两个特殊的函数是:
Ip地址转成数字:select inet_aton("192.168.1.1");
结果
+--------------------------+ | inet_aton("192.168.1.1") | +--------------------------+ | 3232235777 | +--------------------------+ |
数字转换成为ip地址
select inet_ntoa(3232235778);
结果为:
+-----------------------+ | inet_ntoa(3232235778) | +-----------------------+ | 192.168.1.2 | +-----------------------+ |
5.主键的类型选择
其实这是一个老生常谈的话题了。中国人,尤其他妈的某些拿着纳税人钱的传统IT企业,唉~总是对这些要命的细节,趋之若鹜!
基本上在单点使用的时候使用自增类型的无符号整型。它是单点最好的选择。而使用字符串,尤其是随即字符串——UUID,读写性能下降都比较大。
而在集群中就要根据场景视情况而决定是用整型还是UUID。整型比UUID多的步骤就是需要知道全局的主键标示是什么,也就是需要加锁(无论是排他还是读写锁),免不了锁的开销。其他多余的逻辑开销基本没有,而从底层存取来说,随着数据量的增大,整型的优势明显,如果并发量很大,而又是追求吞吐量,UUID优势略微明显。如果数据量也很大,并发量有很大,读多写少的情况,基本上采用整型。如果读写频率相当一致,就该考虑要牺牲一下数据的一致性和准确性保证吞吐量了,那么UUID和整型,基本上在此场景下都差不多了。
6.设计表结构的一些原则
1)字段个数尽量少:
很久以前..久以前..久以前,老师也是这么告诉我们的。表的字段别太多啊,字段多了,把它拆成两个表,拆分后的表如果字段还是很大,继续拆。那么什么是大表,怎么就叫做字段过多?笔者想,这个应该没有标准答案,读者可能处于不同的行业,不同的业务场景。这里给出笔者的经验和笔者的一家之言吧~姑妄言之姑妄听之。那些谩骂者,可以随时拍砖!!!
行业 |
场景 |
主表的平均字段个数 |
备注 |
传统IT |
MIS系统 |
16~25 |
OA最具代表性 |
|
财务系统 |
20~25 |
出纳、工资 |
|
资产管理系统 |
8~14 |
如果需要资源拓扑,表可以很多,但是每个表字段需要尽量少 |
|
组织机构管理 |
6~12 |
需要树形自关联,就是像递归 |
|
功能菜单 |
4~8 |
功能树,或者tab页那种 |
|
BOSS综合业务处理系统 |
12~40 |
因为BOSS系统的业务相当复杂,以业主用户作为主表,其他的围绕着业主的种种增值性的业务也相当于主表了,所以这个区间相当大 |
|
工作流系统 |
16~24 |
这个存疑,开源的工作流,如:JBPM,主表基本维持在12个左右。 |
|
银行系统-核心的用户信息 |
16~25 |
开户的时候,填写的信息比较多,但是呢,它是分别存储的,而且各银行表结构这个不统一,但大体上和BOSS差不多 |
互联网 |
SNS |
10~15 |
主表应该将用户的profile也算上,此时参考的是apache的shindig框架规范。但是它用了JPA的规范,生成的表,外键较多 |
|
微博 |
12~16 |
社交里面的从表基本上就有微博的功能,那么从表在此case中变为了主表,主要还是在处理外键关系,基本上多出的字段都是外键关联 |
|
内容发布-新闻 |
8~14 |
可以参考一下openCMS里的主表。 |
|
论坛BBS |
10~16 |
如果不在这个区间内,想想是否有表拆分的余地 |
…… |
…… |
…… |
需要大家的补充与修正了,一个人的精力有限,涉猎再广,也有没接触过的盲点! |
|
|
|
|
存储引擎要想将数据返回给Mysql服务层需要经过数据行缓冲,服务器层要将缓冲的内容解码(因为数据是从存储引擎返回的)为固定的各个列。
<!--EndFragment-->
如果列的个数很多,那么呢,这个数据行转换的代价,比较高!尤其是变长结构——特指myisam和innodb的变长结构,最具代表性,使用频率最高的的——varchar。
1)关联尽量少:
这个嘛,笔者是觉得,要根据自己的业务特点来!Mysql每个主表关联的操作,只能深到61个从表深度(记住,这里是深度,有点像Java的栈深度,不是广度哦)。笔者就在想,哪个应用能复杂到关联到这么深度?除非是这么个实际场景——你查到李tian yi(哦,现在媒体称李某某)的各种身份、年龄是假的,一旦有确凿的证据!先挖到他老子,他妈身上,之后再深度关联挖掘,挖出一堆有裙带性的贪官,顺着这些贪官再继续挖下去,估计不止61层的深度能明细得了吧,这就是“大国”的特色,水很深,别问,别想,别听,这世界不公平的事情多了去了。
书归正传,《高性能Mysql》的作者们提倡一般关联深度是12层以内。
2)枚举嘛
这个不必说了,大家在真实项目中用几次就知道枚举的实际场景,还有就是什么时候用枚举,什么时候用外键关联常量表(数据字典)。是、否;男、女、性别不明,这几个备选基本上几万年不会变吧!
一个人哪个国家的,这个还是常量表比较合适!万一呢,哪天因为战争,某大国(你懂得……)不复存在了,咱们的数据库枚举还得用alert去阻塞应用修正。
还有一点就是如果使用SET,那么要知道SET里面的元素是没有互斥性的,如果具有互斥性,那么还是使用枚举比较适合。
3)是否反范式化?
符合范式的优点:
1:查询的时候,只查询你所关心的表的局部字段数据,而不需要关联的多次IO,去别的表查询关联数据
2:对于更新操作,只需要关心更改的数据,而不是将整个大表中的某个记录进行行锁定进行更新,其实这里面已经蕴含着分桶,分堆管理的读写分离思想了。
3:基本上表都是小表,如果不是全局数据集的话,基本上可以放置在数据库的查询缓存中,也就是内存中进行缓存了。
4:范式化的表,基本上都是按照某种外键进行了分组,相当于在表设计的时候就进行了group by操作。那么查询SQL语句的复杂度,就可以简简单单的根据一个where 外键 = 某值,进行查询。
符合范式的缺点:
1:其实也是第一个优点的反面case,当一个查询需求需要全字段的数据的时候,不得不多次地随机IO的去关联表去查询整体的实体信息,比如SNS中的用户的profile信息。而反范式的话,那么都在一个表内,基本上都是(小部分不是)顺序IO,磁盘读取数据很快。
2:关联表越多,索引的工地不过关,那么可能造成关联索引,或者聚合索引失效。
不符合范式的优点与缺点正好是符合范式的颠倒,在此不赘述。
实际开发中基本是:根据项目业务场景,范式+反范式=混合范式使用。在产品、项目的不同阶段、数据量不同、用户量不同、并发量不同,会采取不同的混合策略。
1.牺牲数据时效性,换来高性能
如果各位朋友读过笔者的《Web站点单点压力优化》那几篇文章的话,应该记得其中有一个优化的环节。
在此再重复一下,当时是查询一个表的多条记录,用于grid列表展示,那么每个业务基本包含了两个查询事务,一个是用于分页的查询select count记录;另一个用于查询记录的普通select * from操作。随着记录增加,两次查询成本过高,虽然在同一个业务,但是却是两个事务,那么此时数据隐含着已经是有不一致的情况了,所以笔者干脆将count的记录放到了缓存表——memroy中,表名tableinfo,里面仅仅记录了表名,总记录数两个字段,定时任务定时执行select count语句,将结果赋值给该表的记录。
CREATE TABLE `tableinfo` ( `tablename` varchar(40) NOT NULL, `datacount` int(10) DEFAULT NULL, PRIMARY KEY (`tablename`), UNIQUE KEY `tablename` (`tablename`) USING HASH ) ENGINE=MEMORY DEFAULT CHARSET=utf8; |
该引擎是memroy,索引键是表名,索引类型,hash。每次启动应用的时候,也会扫描一遍业务表的总记录数,将最新的总记录数赋值给该表的记录。这样,每次执行count就可以从该表进行查询了,只要数据库服务不重启,该汇总表都是有效的。需要说明的是,该汇总信息肯定是牺牲数据的时效性的。
2.分桶的思想
其实分桶的思想在程序界处处可见,比如Java并发包的并发HashMap。将此思想应用到数据库理论中呢,其实就是读写分离的思想。
《高性能Mysql》里面的那个demo,实在已经很经典了。咱们呢,用一张图来阐述作者的意图吧。
<!--EndFragment-->
大家看一下,这种思想无论是代码中还是数据库,都是可以互相移植,互相借鉴的。
1.总结
最近有点忙,这第二篇的总结有点仓促,还好,后续章节慢慢还会继续总结。
其实,根据经验来看啊~优化schema的收益,却是比优化其他方便,带来的收益比较大。缺点也很明显,表结构变了,你的SQL有可能也要随之修改。这也是为什么在很多互联网公司,普通的研发人员,没有设计,修改表结构的权限,只有DBA才有这个权利。如果要修改表的结构,需要严格的审批流程。DBA对表结构有严格的控制权,没有说服力的或者拍脑袋就做表设计的团队,后续数据量上来了,再去修正schema,尤其是在很多个集群库的场景下,代价比较大。
PS:大家推荐一款比较好的画图工具吧,笔者总感觉画个图比较费时间,还画的比较难看!哪位朋友的审美是“苍老师”教的?怎么画出好看的图,希望不吝赐教!后续我们会讨论重点,索引!
<!--EndFragment--><!--EndFragment-->