高性能MySQL-Schema与数据类型优化

良好的逻辑设计和物理设计是高性能的基石,应该根据系统将要执行的查询语句设计schema,但记住这往往需要权衡各种因素。例如,反范式的设计可以加快某些类型的查询,但同时也可能使一些查询变慢。比如添加计数表和汇总表是很好的优化查询的方式,但这些表的维护成本很高。

==================================================================

1.1 选择优化的数据类型

MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。以下罗列选择的原则:

1)更小的通常更好。一般情况下,应该使用可以正确存储数据的最小数据类型。例如,只需存储0-200整数,那么相比 int 来说 tinyint unsigned 更好。更小的数据类型通常更快,占用更少的磁盘空间、内存、CPU缓存,并且处理时需要的CPU周期也更少。但是需要确保没有低估存储值的范围,因为未来要在schema中多个地方增加数据类型的范围是一个耗时和痛苦的操作。

2)简单就好。简单数据类型的操作通常需要更少的CPU周期,例如整数型比字符型操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整型更复杂。另外的例子,使用MySQL内建的date、time、datetime来存储日期和时间,而不是使用字符串,还有使用整型存储IP地址,也不要使用字符型。

3)尽量避免使用NULL。创建时尽量使用NOT NULL,因为NULL值会使该列的索引、索引统计、值的比较都更复杂,而且会使用更多存储空间。特别是一定要在列上建立索引时,一定是NOT NULL。

如何选择数据类型,第一步,需要确定合适的大类型,第二步,在大类型中选择小类型。

==================================================================

1.1.1 整数类型

整数类型主要包括 TINYINT(8位,-128~127)、SMALLINT(16位)、MEDIUMINT(24位)、INT(32位)、BIGINT(64位),他们的存储的值的范围为:-2^(N-1)至2^(N-1)-1,以上都可以使用UNSIGNED,取值范围为0~2^N-1。

注意:这个选择仅影响数据在内存和硬盘中的存储,不会对整数的计算产生影响,因为MySQL计算时均转换为64位 BIGINT 进行。

注意:MySQL可以为整数类型指定宽度,这只是显示的宽度,如INT(5),显示5个字符宽度,如123,会显示00123,因此不会影响取值,也就是说比如INT(5)和INT(11)在存储和计算时是相同的,没有区别。

==================================================================

1.1.2 实数类型

实数类型包括 FLOAT(不精确类型)、DOUBLE(不精确类型)、DECIMAL(精确类型),支持指定精度,如DECIMAL(18,9),表示小数点两边各9个数字。

注意:由于指定精度,会使得MySQL悄悄地选择不同的数据类型,或者在存储时对值自动取舍,因此建议只指定数据类型,不指定精度,精度根据需要在前端取舍。

注意:如果真需要DECIMAL,建议使用乘以10^N取得整数后使用INT(或BIGINT)数据类型,前端显示时根据需要除以10^N获得正确的结果,或者将指数N对应以INT存入数据库,这样使MySQL得到优化。

==================================================================

4.1.3 字符串类型

MySQL有多种字符串类型,每种可以定义不同的字符集、排序规则(校对规则),这些东西会很大程度上影响性能。

VARCHAR和CHAR是两种基本的类型,不幸的是,很难解释清楚两种类型是怎么存储在磁盘和内存中的,因为这跟存储引擎有关。不同的存储引擎(MyISAM、InnoDB、。。。)都不同。

VARCHAR类型,用于存储可变长字符串,它比定长类型节省空间,因为它仅使用必要的空间,即,越短的字符串使用越少空间。除非使用 ROW_FORMAT=FIXED,每行定长,这样会浪费空间。另外,VARCHAR使用1~2个额外字节记录字符串的长度,如果列长度小于或等于255,使用1个字节表示(8位),如果大于255,使用2个字节。

VARCHAR类型,虽然节约了空间,但由于行是变长的,在UPDATE时可能会使行变得更长,这就导致额外的工作;如果行变长后,页内没有更多空间可以存储,这种情况下,就需要拆分片段(MyISAM)或分裂页(InnoDB)来处理。

VARCHAR类型,适用情况,列最大长度比平均长度大很多,且很少更新,因为碎片将不是问题。

CHAR类型,是定长类型,根据指定的字符串长度分配足够的空间;CHAR类型适合于存储很短的字符串,或者所有值都接近同一个长度。例如,CHAR类型非常适合于存储密码的MD5值,因为MD5是一个定长的值。另一方面,CHAR类型也适合存储经常变更的数据,因为定长的CHAR不容易产生碎片。对于非常短的列,CHAR比VARCHAR在存储空间上也更有效率。如,用CHAR(1)存储只有Y和N的值,如果用单字节字符集只需要一个字节,但VARCHAR(1)在单字节情况下则需要两个字节,因为还有一个额外字节用于记录长度。

注意:字符串长度定义不是字节数,而是字符数;多字节字符集会需要更多的空间存储单个字符。

注意:字符集(Charset):是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称,包括各国家文字、标点符号、图形符号、数字等。,常见字符集名称:ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等;

注意:字符编码(Character Encoding):是一套法则,使用该法则能够对自然语言的字符的一个集合(如字母表或音节表),与其他东西的一个集合(如号码或电脉冲)进行配对。即在符号集合与数字系统之间建立对应关系,它是信息处理的一项基本技术。通常人们用符号集合(一般情况下就是文字)来表达信息。而以计算机为基础的信息处理系统则是利用元件(硬件)不同状态的组合来存储和处理信息的。元件不同状态的组合能代表数字系统的数字,因此字符编码就是将符号转换为计算机可以接受的数字系统的数,称为数字代码。UTF8是编码方法;

注意:慷慨是不明智的,比如,使用VARCHAR(5)或VARCHAR(100)存储“hello”,空间开销一样,因为VARCHAR类型是可变长的,但因为MySQL通常会分配固定大小的内存块来存储内部值(意思是定义的长100>10,分配的内存块就大?),尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

BLOB和TEXT类型,BLOB以二进制存储(1、0数据流,搞硬件编程的比较熟悉),TEXT以字符形式存储,都是用来存储很大数据的类型,注意,他们也是字符串类型,只是存储形式不同。

BLOB和TEXT类型,在MySQL中会被作为对象处理,尽量避免使用。

使用枚举(ENUM)代替字符串类型,枚举会将列的长度压缩到1个或2个字节,内部都保存为整数,而在表的.frm文件中保存“数字-字符串”的映射关系查找表。

注意:避免使用数字作为枚举常量,比如 enum('1', '2', '3'),如果要枚举的真是数字,不如直接使用 TINYINT 来得直接,正确的使用情况类似于 enum('private', 'group', 'public')。

注意:枚举类型列排序时使用的是映射关系中的整数值,而不是按枚举的字符串排序。所以最好的办法是定义时就先排好序,如上面的例子,应写为 column_type enum('group', 'private', 'public') not null default 'private'。

注意:需要保证枚举列的字符串值能够确定,否则未来要扩展类别,就需要使用 ALTER TABLE。

注意:因为枚举保存为整数,因此查找时必须转换为字符串,所以枚举在执行查找时有一定开销,尤其是当前表的枚举列与其他表相同的VARCHAR列或CHAR列关联查询时,会较不使用枚举更慢。例如 关联条件为 ON A.ENUM_TYPE = B.CHAR_TYPE 时。但什么情况都需要一个平衡,使用枚举可以缩减表的大小,可能会缩减30%;当然关联后虽然慢,但可能会节省I/O。

==================================================================

1.1.4 日期和时间类型

MySQL提供许多类型保存日期和时间,最小粒度为秒。具体看看

DATETIME,存储范围1001年~9999年,封装格式为 YYYYMMDDHHMMSS,与时区无关,8个字节(64位?),使用ANSI标准定义的日期和时间表示方法显示,如“2014-01-11 22:05:33”,注意这是显示。

TIMESTAMP,时间戳,记录了一个累计秒数,从1970年1月1日午夜(格林尼治标准时间)以来。4个字节,与UNIXTIME相同。比DATETIME小很多了,最大记录从 1970年~2038年的时间。转换:FROM_UNIXTIME() 将时间戳转换为日期,UNIX_TIMESTAMP() 将日期转换为时间戳。

TIMESTAMP值与时区有关,DATETIME与时区无关,DATETIME记录的是客户端提交到数据库的当地时间字符串。如果提交数据时不指定TIMESTAMP的值,则MySQL默认提供当前时间赋值,且默认NOT NULL。

注意:以上优势,建议使用 TIMESTAMP,而不是 DATETIME,2038年以后怎么办?

==================================================================

1.1.5 位数据类型

主要有 BIT 和 SET,避免使用。

==================================================================

1.1.6 选择标识符(identifier)

标识列的数据类型,最好选择整数,且符合上面的讲的原则,能TINYINT的不INT;不要选用ENUM、SET、CHAR、VARCHAR,除非没有办法。

另外,标识列一般还与外键有关,所以记住,标识列不管在主表还是关联表中,选择的数据类型要完全一致,精确匹配,像 INT 和 UNSINGED INT 都会产生性能问题,即使没有性能问题,也可能导致难以发现的错误。因为如果不是精确匹配,MySQL需要进行隐式类型转换。

标识列不能使用字符型的原因,慢,消耗空间,对于MyISAM,字符型要使用压缩索引,导致查询慢6倍。且对于使用随机生成的字符串更要注意,如使用MD5()、SHA1()、UUID(),这些新产生的值会分布在很大的空间范围内,使得INSERT和SELECT都很慢,可能分布于不同的内存和磁盘不同位置,不会相邻,来回扫描,导致整个数据集都成为热数据。

==================================================================

1.1.7 特殊数据类型

两个例子,低于秒级的存储,IPv4的存储,人们经常错误使用 VARCHAR(15)存储IP地址,实际应使用32位 UNSIGNED INT存储,因为IP地址本身就是整数,小数点只是为了显示,因此应利用 MySQL提供的 INET_ATON()和INET_NTOA()切换。

==================================================================

1.2 MySQL Schema 设计中的陷阱

太多的列(上千个)、太多的关联(单个查询不要超过12个表)、全能的枚举、变相的枚举、非此发明的NULL(为避免不用NULL,不要乱给值如0000-00-00 00:00:00,给个0就好了)。

==================================================================

1.3 范式和反范式

一般都是先进行范式化,然后根据需要进行反范式化,取各自的有点进行折中。

范式的优点:表小、不重复、更新快;

范式的缺点:纯粹范式,查询基本都需要关联,代价昂贵。

反范式的优点:反范式恰当的话,不需要关联,快!

反范式的缺点:更新关联数据的难处。

混用:实际中是混用,部分的范式化schema、缓存表、以及其他技巧,貌似是除了有经验可以预先处理外,边开发可以边改动表的结构,将范式化的某些列,缓存到需要的主表中。

==================================================================

1.4 缓存表和汇总表(重要!!!)

缓存表:表示存储那些可以比较简单从schema其他表获取数据的表,而这样的数据每次获取的速度又比较慢,且在逻辑上有冗余的数据。缓存表需要开发者决定是实时维护还是定期重建,定期重建不仅节省资源,还可以保持表不会有很多碎片。定期重建注意使用“影子表”保持原表的可用性。

汇总表:保存的是使用 GROUP BY 语句聚合数据的表,例如,数据不是逻辑上冗余的。

物化视图:常被认为是一种功能,MySQL不提供这一功能;但实际上,物化视图指的就是缓存表,以及与之相关的一系列解决方案。使用 Justin Swanhart 开发的 Flexviews 可以实现自己的物化视图。实际使用中,只要你告诉 Flexviews 你希望从源数据库或表提前信息的 Select 语句,Flexviews自己将其转换,获取结果存储下来。开发者只需要面向 Flexviews查询就行了,再不用查询 数据库。Flexviews能做到这样,是因为 Flexviews 基于行的二进制日志更新数据,他的提前对象是数据库日志。

计数器表:创建独立计数器表记录网站访问量、用户朋友数字、文件下载次数等,是个好主意,这种表小且快。但有一个问题,如果是InnoDB会执行行级锁,如果是MyISAM会执行表级锁,会造成高并发串行执行问题。书中列出了 使用 InnoDB 会用到的技巧(随机槽、INSERT INTO...ON DUPLICATE KEY UPDATE)。

问题:以上内容,都在增加读操作速度,但会造成写操作变慢,甚至增加读操作和写操作的开发难度。

==================================================================

1.5 加快 ALTER TABLE 操作的速度

本节讲到两个重要技术,来通过速度,一是修改 .frm 文件,二是对于MyISAM表先载入数据,再创建索引。

==================================================================

1.6 总结

推荐阅读更多精彩内容