mysql 性能调优（转）

mysql 性能 dba linux java 

Mysql高性能学习笔记2

刘岩

[email protected]

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