MySQL高性能实战——part2——Schema和数据类型优化

前言：
此文借鉴《MySQL高性能》一书，还有MySQL官方文档，笔者将通过自身的一些实战经验和阅读习惯对本书进行一个总结，整理，归纳出企业级开发中常用的优化案列和部分概念！！
​

官方文档地址：
https://dev.mysql.com/doc/refman/5.7/en/
​

本文：由于数据库知识庞大且复杂，笔者将以，以下几个模块进行拆分解读，请大家按需自取！！！

1. MySQL高性能实战——part1——MySQL架构与历史
2. MySQL高性能实战——part2——Schema和数据类型优化
3. MySQL高性能实战——part3——分析SQL,定位慢SQL(性能优化的前提)
4. MySQL高性能实战——part4——高性能索引使用
5. MySQL高性能实战——part5——查询性能优化
6. MySQL主从复制与读写分离，分库分表

​

​

版本说明：
不同数据库版本可能对SQL的执行以及部分特性产生巨大的影响，所以在这里明确一下版本
参考书籍版本：5.1或5.5
实战案列实际使用版本：5.7
​

MySQL高性能实战——part2——Schema和数据类型优化

为什么需要Schema和数据类型优化？

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

MySQL独有的特性和实现细节对性能的影响也很大。本章和聚焦在索引优化的下一章,覆盖了 MySQL特有的 schema设计方面的主题。我们假设读者已经知道如何设计数据库,所以本章既不会介绍如何入门数据库设计,也不会讲解数据库设计方面的深入内容。

这一章关注的是 MySQL数据库的设计,主要介绍的是 My SQL数据库设计与其他关系型数据库管理系统的区别。如果需要学习数据库设计方面的基础知识,建议阅读 Clare Churcher的 Beginning Database Design( Apress出版社)一书。

本章内容是为接下来的两个章节做铺垫。在这三章中,我们将讨论逻辑设计、物理设计和查询执行,以及它们之间的相互作用。这既需要关注全局,也需要专注细节。还需要理解整个系统以便弄清楚各个部分如何相互影响。如果在阅读完索引和査询优化章节后再回头来看这一章,也许会发现本章很有用,很多讨论的议题不能孤立地考虑

一.优化数据类型

1.数据类型优化简单原则

MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要,下面几个简单的原则都有助于做出更好的选择

1. 更小的通常更好。一般情况下,应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快因为它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少
2. 简单就好。简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。这里有两个例子:一个是应该使用 MySQL内建的类型 而不是字符串来存储日期和时间,另外一个是应该用整型存储IP地址
3. 尽量避免NULL 很多表都包含可为NULL(空值)的列,即使应用程序并不需要保存NULL，也是如此,这是因为可为NULL是列的默认属性(没有指定NOT NULL 默认都是允许为NULL)。通常情况下最好指定列为 NOT NULL,除非真的需要存储NUL值。如果查询中包含可为NULL的列,对 MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个额外的字节 在 MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。通常把可为NULL的列改为N0 T NULL带来的性能提升比较小,所以(调优时)没有必要首先在现有 schema中査找并修改掉这种情况,除非确定这会导致问题。但是,如果计划在列上建索引,就应该尽量避免设计成可为NULL的列.
   1. 列如 表示性别 我们可以：将默认值设置为0, 1男 ,2女

​

所以我们在实际创建表时应该

1. 先确定列适合的大类型
   1. 比如数字还是字符串，还是时间等！
2. 在大类型中确定具体类型
   1. 很多 MySQL的数据类型可以存储相同类型的数据,只是存储的长度和范围不一样、允许的精度不同,或者需要的物理空间(磁盘和内存空间)不同
   2. 相同大类型的不同子类型数据有时也有一些特殊的行为和属性
   3. 例如:DATETIME和 TIMESAMP列都可以存储相同类型的数据:时间和日期,精确到秒然而 TIMESTAMP只使用 DATETIME一半的存储空间,并且会根据时区变化,具有特殊的自动更新能力。另一方面, TIMES TAMP允许的时间范围要小得多,有时候它的特殊能力会成为障碍。
   4. 列如：比较常见的，status状态码，我们可以使用tinyint去代替int，大部分情况下tinyint能满足我们的需求

​

​

2.数字类型优化

1.整数类型

存储整数,可以使用这几种整数类型:

整数类型	存储位空间	存储范围（N代表存储位）
TINYINT	8	-2(n-1) 到 2(n-1)
SMALLINT	16	-2(n-1) 到 2(n-1)
MEDIUMINT	24	-2(n-1) 到 2(n-1)
INT	32	-2(n-1) 到 2(n-1)
BIGINT	64	-2(n-1) 到 2(n-1)

一些冷知识：

1. 你的选择决定 MySQL是怎么在内存和磁盘中保存数据的。然而,整数计算一般使用64位的 BIGINT整数,即使在32位环境也是如此。(一些聚合函数是例外,它们使用DECIMAL或D0UBLE进行计算)。
2. MySQL可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的:它不会限制值的合法范围,只是规定了 MySQL的一些交互工具(例如 MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的。

2.实数类型

实数是带有小数部分的数字。然而,它们不只是为了存储小数部分;也可以使用DECIMAL存储比BGIN还大的整数。
​

MYSQL既支持精确类型,也支持不精确类型。
​

FL0AT和D0UBLE类型支持使用标准的浮点运算进行近似计算。如果需要知道浮点运算是怎么计算的,则需要研究所使用的平台的浮点数的具体实现。
​

DECIMAL类型用于存储精确的小数。在 MySQL5.0和更高版本, DECIMAL类型支持精确计算。 MySQL4.1以及更早版本则使用浮点运算来实现 DECIAML的计算,这样做会因为精度损失导致一些奇怪的结果。在这些版本的 MySQL中, DECIMAL只是一个“存储类型”。因为CPU不支持对 DECIMAL的直接计算,所以在 MySQL5.0以及更高版本中, MySQL服务器自身实现了 DECIMAL的高精度计算。相对而言,CPU直接支持原生浮点计算,所以浮点运算明显更快。
​

浮点和 DECIMAL类型都可以指定精度。对于 DECIMAL列,可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。 My SQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如, DECIMAL(18,9)小数点两边将各存储9个数字,一共使用9个字节:小数点前的数字用4个字节,小数点后的数字用4个字节,小数点本身占1个字节。
​

MySQL5.0和更高版本中的 DECIMAL类型允许最多65个数字。而早期的 MySQL版本中这个限制是254个数字,并且保存为未压缩的字符串(每个数字一个字节)。然而,这些(早期)版本实际上并不能在计算中使用这么大的数字,因为 DECIMAL只是一种存储格式;在计算中 DECIMAL会转换为D0UBLE类型。
​

有多种方法可以指定浮点列所需要的精度,这会使得 MySQL悄悄选择不同的数据类型,或者在存储时对值进行取舍。这些精度定义是非标准的,所以我们建议只指定数据类型,不指定精度。
​

浮点类型在存储同样范围的值时,通常比 DECIMAL使用更少的空间。FL0AT使用4个字节存储。D0UBLE占用8个字节,相比FL0AT有更高的精度和更大的范围。和整数类型一样,能选择的只是存储类型; MySQL使用DOUBLE作为内部浮点计算的类型因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL-—例如存储财务数据。
​

但在数据量比较大的时候,可以考虑使用 BIGINT代替DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分,则可以把所有金额乘以一百万,然后将结果存储在 BIGINT里,这样可以同时避免浮点存储计算不精确和 DECIMAL精确计算代价高的问题。
​

​

3.有符号和无符号数

整数类型有可选的 UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。
​

例如：
TINYINT的属性为UNSIGNED时可以存储的范围是0~255,而 TINYINT的存储范围是-128127。
​

有符号和无符号类型使用相同的存储空间,并具有相同的性能,因此可以根据实际情况选择合适的类型。

create table test(
    id   bigint unsigned auto_increment  primary key 
)charset = utf8,engine =INNODB;

​

4.优化点总结

1. 整数类型有可选的 UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。
   1. 在已知整数类型为非负数时通过**unsigned属性 **来提升我们数字的存储范围，来避免使用存储位大的整数类型
2. 我们只能确定数字类型的存储类型，但却无法确定计算数据时所使用数字类型
   1. 整数计算一般使用64位的 BIGINT整数,即使在32位环境也是如此
   2. MySQL使用DOUBLE作为内部浮点计算的类型
3. MySQL可以为整数类型指定宽度,例如INT(11),对大多数应用这是没有意义的:它不会限制值的合法范围,只是规定了 MySQL的一些交互工具(例如 MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说,INT(1)和INT(20)是相同的。
4. 需要精确计算时使用DECIMAL类型，但在数据量比较大的时候,可以考虑使用 BIGINT代替DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可，效率更高！

3.字符串类型

1.char和varchar

VARCHAR和CHAR是两种最主要的字符串类型。不幸的是,很难精确地解释这些值是怎么存储在磁盘和内存中的,因为这跟存储引擎的具体实现有关。下面的描述假设使用的存储引擎是 InnoDB和/或者 MyISAM。如果使用的不是这两种存储引擎,请参考所使用的存储引擎的文档。
​

VARCHAR
VARCHAR类型用于存储可变长字符串,是最常见的字符串数据类型。它比定长类型更节省空间,因为它仅使用必要的空间(例如,越短的字符串使用越少的空间)。
​

VARCHAR需要使用1或2个额外字节记录字符串的长度
如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。
假设采用 latin1字符集,一个 VARCHAR(10)的列需要11个字节的存储空间。 VARCHAR(1000)的列则需要1002个字节,因为需要2个字节存储长度信息。
​

​

VARCHAR节省了存储空间,所以对性能也有帮助。但是,由于行是变长的,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。如果一个行占用的空间增长,并且在页内没有更多的空间可以存储,在这种情况下,不同的存储引擎的处理方式是不一样的。
例如：

1. MyISAM会将行拆成不同的片段存储,
2. InnodB则需要分裂页来使行可以放进页内。
3. 其他一些存储引擎也许从不在原数据位置更新数据。

页分裂可以看看此文
https://zhuanlan.zhihu.com/p/98818611


下面这些情况下使用 VARCHAR是合适的:字符串列的最大长度比平均长度大很多列的更新很少,所以碎片不是问题;
​

CHAR
CHAR类型是定长的: MySQL总是根据定义的字符串长度分配足够的空间。
​

当存储CHAR值时, MySQL会删除所有的末尾空格(在 MySQL4.1和更老版本中 VARCHAR也是这样实现的——也就是说这些版本中CHAR和 VARCHAR在逻辑上是一样的,区别只是在存储格式上)。CHAR值会根据需要采用空格进行填充以方便比较。
​

1. CHAR适合存储很短的字符串,或者所有值都接近同一个长度。例如,CHAR非常适合存储密码的MD5值,因为这是一个定长的值。
2. 对于经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。
3. 对于非常短的列,CHAR比VARCHAR在存储空间上也更有效率。
4. 例如用CHAR(1)来存储只有Y和N的值,如果采用单字节字符集5只需要一个字节,但是 VARCHAR(1)却需要两个字节,因为还有个记录长度的额外字节。

与CHAR和 VARCHAR类似的类型还有 BINARY和 VARBINARY,它们存储的是二进制字符串。二进制字符串跟常规字符串非常相似,**但是二进制字符串存储的是字节码而不是字符**。填充也不一样: MySQL填充 BINARY采用的是\0(零字节)而不是空格,在检索时也不会去掉填充
​

当需要存储二进制数据,并且希望 MySQL使用字节码而不是字符进行比较时,这些类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。 MySQL比较BINARY字符串时,每次按一个字节,并且根据该字节的数值进行比较。因此,二进制比较比字符比较简单很多,所以也就更快。（字符串比较涉及到字符串的字符集编码，如果字符集编码 不一致的话会导致字符集编码的转换，导致性能下降，同时也可能导致索引失效）


面试题
使用 VARCHAR(5)和 VARCHAR(200)存储’hello’的空间开销是一样的吗？使用更短的列有什么优势吗?
是一样的，事实证明有很大的优势。更长的列会消耗更多的内存,因为 MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。所以最好的策略是只分配真正需要的空间。
​

​

2.BL0B和TEXT

BL0B和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。实际上,它们分别属于两组不同的数据类型家族
​

字符类型

1. TINYTEXT
2. SMALLTEXT
3. TEXT
4. MEDIUMTEXT
5. LONGTEXT;

​

对应的二进制类型

1. TINYBL0B
2. SMALLBL0B
3. BL0B
4. MEDIUMBL0B
5. L0NGBL0B

BL0B是 SMALLBL0B的同义词,TEXT是 SMALLTEXT的同义词。
​

与其他类型不同, My SQL把每个BL0B和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BL0B和TEXT值太大时, InnODB会使用专门的“外部”,存储区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储区域存储实际的值。
​

BL0B和TEXT家族之间仅有的不同是BL0B类型存储的是二进制数据,没有排序规则或字符集,而TEXT类型有字符集和排序规则。
​

MySQL对BL0B和TEXT列进行排序与其他类型是不同的:**它只对每个列的最前max sort length字节而不是整个字符串做排序。**如果只需要排序前面一小部分字符,则可以减小 max sort length的配置,或者使用 ORDER BY SUSTRING( colum, length)。
​

MySQL不能将BL0B和TEXT列全部长度的字符串进行索引,也不能使用这些索引消除排序。
​

3.优化点总结：

1. 字符串列的最大长度比平均长度大很多列的更新很少使用varchar
2. 在已知字符串长度的情况选择合适的，字符类型宽度，更长的列会消耗更多的内存,因为 MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕，能选varchar(10)就不用varchar（100）
3. CHAR适合存储很短的字符串,或者所有值都接近同一个长度。例如,CHAR非常适合存储密码的MD5值,因为这是一个定长的值。
4. 非常短的列使用CHAR，因为VARCHAR还有个记录长度的额外字节。在存储空间上也更有效率。
5. 尽量避免使用BL0B和TEXT，如果使用了，在查询的时候，没有使用到BL0B和TEXT的列，就避免select * 操作
6. 在对BL0B和TEXT字段排序 和 创建索引时会失效，需要配置max sort length 或者ORDER BY SUSTRING( colum, length)进行使用

​

​

​

​

4.日期和时间类型

MySQL可以使用许多类型来保存日期和时间值,例如YEAR和DATE。
MySQL能存储的最小时间粒度为秒( MariaDB支持微秒级别的时间类型)。但是 MySQL也可以使用微秒级的粒度进行临时运算,我们会展示怎么绕开这种存储限制。大部分时间类型都没有替代品,因此没有什么是最佳选择的问题。唯一的问题是保存日期和时间的时候需要做什么。 MySQL提供两种相似的日期类型: DATETIME和TIMESTAMP。对于很多应用程序,它们都能工作,但是在某些场景,一个比另一个工作得好。让我们来看一下
​

1.DATETIME

这个类型能保存大范围的值,从1001年到9999年,精度为秒。它把日期和时间封装到格式为 YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。默认情况下, MySQL以一种可排序的、无歧义的格式显示 DATETIME值
例如
“2008-01-162237:08”。这是ANSI标准定义的日期和时间表示方法
​

​

2.TIMESTAMP

就像它的名字一样, TIMETAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它和UNX时间戳相同。 TIMESTAMP只使用4个字节的存储空间
因此它的范围比 DATETIME小得多:只能表示从1970年到2038年。 MySQL提供了FROM UNIXTIME()函数把Unix时间戳转换为日期,并提供了 UNIX TIMESTAMP()函数把日期转换为Unix时间戳


TIMES TAMP显示的值也依赖于时区。 MySQL服务器、操作系统,以及客户端连接都有时区设置。因此,存储值为0的 TIMESTAMP在美国东部时区显示为“1969-12-3119:00:00”,与格林尼治时间差5个小时。有必要强调一下这个区别:如果在多个时区存储或访问数据, TIMESTAMP和 DATETIME的行为将很不一样。前者提供的值与时区有关系,后者则保留文本表示的日期和时间
除了特殊行为之外,通常也应该尽量使用 TIMES TAMP,因为它比 DATETIME空间效率更高。
有时候人们会将Unix时间截存储为整数值,但这不会带来任何收益。用整数保存时间截的格式通常不方便处理,所以不推荐这样做
​

如果需要存储比秒更小粒度的日期和时间值怎么办?
MySQL目前没有提供合适的数据类型,但是可以使用自己的存储格式:可以使用 BIGINT类型存储微秒级别的时间截,或者使用D0UBLE存储秒之后的小数部分。这两种方式都可以,或者也可以使用 MariaDB替代 MySQL。
​

3.优化点总结：

1. 除了特殊行为之外,通常也应该尽量使用 TIMESTAMP,因为它比 DATETIME空间效率更高。因为TIMESTAMP使用的是4个字节存储，而DATETIME却是8字节存储
2. 具体的使用还是看实际的业务需要，TIMESTAMP存储的时间和时区有关，且时间范围为1970年到2038年，而DATETIME却把日期和时间封装到格式为 YYYYMMDDHHMMSS的整数中，且时间范围为1001年到9999年！

二.范式和反范式

不阐述过多，事实是,完全的范式化和完全的反范式化 schema都是实验室里才有的东西:在真实世界中很少会这么极端地使用。在实际应用中经常需要混用,可能使用部分范式化的schema、缓存表,以及其他技巧。

三.缓存表和汇总表的使用

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据。然而,有时也需要创建一张完全独立的汇总表或缓存表(特别是为满足检索的需求时)。如果能容许少量的脏数据,这是非常好的方法,但是有时确实没有选择的余地(例如,需要避免复杂、昂贵的实时更新操作)。
​

术语“缓存表”和“汇总表”没有标准的含义。我们用术语“缓存表”来表示存储那些可以比较简单地从 schema其他表获取(但是每次获取的速度比较慢)数据的表(例如,逻辑上冗余的数据)。而术语“汇总表时,则保存的是使用GR0UPBY语句聚合数据的表(例
如,数据不是逻辑上冗余的)。也有人使用术语“累积表( Roll-Up Table)”称呼这些表。因为这些数据被“累积”了。
​

实际优化案列
如果应用在表中保存计数器,则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张独立的表存储计数器通常是个好主意,这样可使计数器表小且快。
​

应该让事情变得尽可能简单,假设有一个计数器表,只有一行数据,记录网站的点击次数问题在于,对于任何想要更新这一行的事务来说,这条记录上都有一个全局的互斥锁( mutex)。这会使得这些事务只能串行执行。要获得更高的并发更新性能,也可以将计数器保存在多行中,每次随机选择一行进行更新。
​

看案列：
我们可以生成多个数据行代表多个槽，每次随机对槽进行更新，避免事务的锁限制，提升我们的性能

#创建点击计数表
create table hit_counter(
    slot tinyint unsigned not null primary key  comment '点击量统计的槽',
    cnt int unsigned not null  comment '计数'
)charset =utf8,engine =INNODB;

#初始化生成100行  计数的槽
create procedure initSlot()
begin
declare i int default 1;
while i<=100 do
insert into hit_counter values (i,0);
set i = i+1;
end while ;
end ;

#调用存储过程
call initSlot;

#用户点击时 随机对一个槽添加点击量
update hit_counter set cnt=cnt+1 where slot =round(rand() *100);

#计算点击量总和
select sum(cnt) from hit_counter

​

​