高性能mysql 读书笔记 Schema与数据类型优化

1.选择优化的数据类型

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

1.更小的通常更好
一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为他们占用更少的磁盘、内存、CPU缓存，并且处理时需要的CPU周期也更少。
2.简单就好
简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低。应该使用MySQL内建的类型而不是字符串来存储日期和时间，另外应该用整型存储IP地址。之后具体讨论。
3.尽量避免NULL
NULL是列的默认属性。如果查询中包含可为NULL的列，堆MySQL来说更难优化，因为可以为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要额外的字节，在MyISAM里甚至可以导致固定大小的索引变为可变大小。
InnoDB使用单独的bit存储NULL值，所以对于系数数据有很号的空间效率。但这一点并不会适用与MyISAM。

1.1 整数类型

MySQL数据类型	含义（有符号）
tinyint(m)	1个字节 范围(-128~127)
smallint(m)	2个字节 范围(-32768~32767)
mediumint(m)	3个字节 范围(-8388608~8388607)
int(m)	4个字节 范围(-2147483648~2147483647)
bigint(m)	8个字节 范围(±9.22*10的18次方)

整数类型有可选的unsigned属性表示不允许负值，所以可以存储的正数上限提高一倍。有符号和无符号使用相同的存储空间，并且有相同的性能。

你的选择只能决定MySQL是怎么在内存和磁盘中保存数据的。然而，整数计算一般使用64位的bigint整数，即使在32位环境也是如此的。（一些聚合函数是例外，他们使用decimal或double）

MySQL可以为整数类型指定宽度，例如int（11），对大多数应用是没有意义的，它不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显式字符的个数。对于存储和计算来说，int（1）和int（20）是相同的。

1.2 实数类型

MySQL数据类型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数，Mysql服务器自身实现了DECIMAL的高精度计算，相对而言，CPU直接支持原生浮点计算，所以浮点计算明显更快。

浮点和DECIMAL类型都可以指定精度，对于DECIMAL可以指定小数点前后所允许的最大位数，这会影响列的空间消耗。

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间，所以应该尽量只在对小数进行精确计算时才使用。

1.3 字符串类型

MySQL支持多种字符串类型，每种类型还有很多变种。

1.3.1 varchar和char类型

VARCHAR和CHAR是两种最主要的字符串类型。

VARCHAR类型存储可变长字符串，所以对性能也有帮助，但在UPDATE时可能使行变得比原来长，这就导致需要做额外的工作。

varchar需要使用1或2个额外字节记录字符串的长度：如果列的最大长度长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

CHAR是定长的，不需要存储字符长度，适合存储很短的字符串（末尾会用空格填充）。

1.3.2 binary和varbinary

与char和varchar类型类似，但他们存储的是二进制字符串。二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。另外char类型使用空格填充，而binary是使用\0(零字节)填充。
二进制比较比字符比较简单很多，也快很多。

1.3.3 blob和text类型

blob和text都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。blob可以存储照片。

BLOB有4种类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

TEXT也有4种类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些类型同BLOB类型一样，有相同的最大长度和存储需求。

当blob和text值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

MySQL堆blob和text列进行排序与其他类型不同：它支队每个列的最前max_sort_length(一个数字)字节而不是整个字符串做排序。不能将blob和text列全部长度的字符串进行索引。

如果查询使用了blob或text列并且需要使用隐式临时表，将不得不使用磁盘临时表（注意，这里是磁盘，很慢的）。这会导致严重的性能开销。最好的解决办法是尽量避免使用blob和text。如果无法避免在所有用到blob字段的地方都使用substring（column，length）将列值转换为字符串。确保大小不超过max_heap_table_size或tmp_table_size。超过使用MyISAM磁盘临时表，不超过则使用内存临时表。

1.3.4 使用枚举类型（ENUM）代替字符串类型

如果存储的字符串类型是可枚举的，那么尽量使用枚举类型。
MySQL会将字符串映射成数字，存储大小会根据列表值的数量压缩到一个或两个字节中。

比如枚举类型为fish，apple，dog，那么在.frm文件中保存“数字-字符串”映射关系的“查找表”。然后在表中存储的是1,2,3,。分别对应fish，apple，dog。

正因为存储的是数字，所以进行排序的时候按存储的数字来进行排序。所以如果需要按字符排序只能按顺序来定义枚举类型。

1.4 日期和时间类型

除了特殊行为，通常也应该尽量使用timestamp。

1.4.1 datetime

• datetime能保存大范围的值，从1001年到9999年。
• 精度为秒。
• 它把日期和时间封装成YYYYMMDDHHMMSS的整数中。例如2019年11月21日13点17分21秒会保存为20191121131721。
• 使用8个字节存储。
• 可排序。

1.4.2 timestamp

• timestamp类型保存了1970年1月1日0点0分0秒以来的秒数。
• timestamp只使用4个字节的存储空间。
• 只能表示1970年到2038年。
• MySQL提供了from_unixtime()函数吧Unix时间戳转换为日期，并提供了unix_timestamp()函数把日期转换为Unix时间戳。
• 显式依赖于时区，比如，存储值为0的timestamp在美国懂不时区显式为“1969-12-31 19:00:00”，与格林尼治时间差5个小时。

1.5 位数据类型

1.5.1 bit

在MySQL5.0之前，bit是tinyint的同义词。5.0以后，这是一个完全不同的数据类型。尽量避免使用bit。

• 可以使用bit列在一列中存储一个或多个true/false值。
• MyISAM会打包存储bit列，比如bit(17)，实际上是使用3个字节来存储（3*8>17）。
• 其他引擎例如Memory和InnoDB，为每个bit列使用最小整数类型来存储（一个整型4个字节）。所以MyISAM的的策略在空间上是较好的。
• MySQL将bit当作字符串类型，而不是数字类型。检索bit（1）时，结果是一个包含0或1的字符串，而不是ascii的‘0’或‘1’。在数字上下文的场景中检索时，结果将字符串转换成的数字。正是因为如此，尽量避免使用bit

1.5.2 set

• 如果需要保存很多true/false值，可以考虑合并这些列到一个set数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。
• 主要缺点是改变列的定义代价太高，需要alter table，这堆大表来说是非常昂贵的。
• 无法在set列上通过索引查找。

1.6 选择标识符

标识符用来命名一些对象, 如数据库、表、列、变量等, 以便在脚本中的其他地方引用。
当选择标识列的类型时，不仅仅需要考虑存储类型。还需要考虑MySQL对这种类型怎么执行计算和比较。
一旦选定了一种类型，要确保在所有关联表中都使用同样的类型，类型之间需要精准匹配，包括像unsigned这样的属性，混用不同数据类型可能导致性能问题，即使没有性能问题，在比较操作时隐式类型转换也可能导致很难发现的错误。

1.6.1 整数类型

整数通常是标识列最好的选择，因为它们很快并且可以使用auto_increment。

1.6.2 enum和set类型

对于标识列来说，enum和set类型通常是一个糟糕的选择。enum和set列适合存储固定信息，例如有序的状态、产品类型、人的性别。
尽量避免这样做。

1.6.3 字符串类型

尽量避免使用字符串类型作为标识列，因为它很消耗空间，并且通常比数字类型慢。
MyISAM默认堆字符串压缩索引，这回导致查询慢的多。（测试有6倍性能下滑）

对于完全“随机”的字符串也需要多加注意，例如MD5()、SHA1()、UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致insert以及一些select语句会变得很慢：

• 因为插入值会随机地写到索引的不同位置，所以是的insert语句更慢。这会导致页分裂、磁盘随机访问，以及对于聚簇存储引擎产生聚簇索引碎片。
• select语句会变的更慢。因为逻辑上相邻的行会分布在磁盘和内存的不同地方。
• 随机值导致缓存堆所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。如果整个数据集都一样的“热”，那么缓存任何一部分特定数据到内存都没有好处；如果工作集比内存大，缓存将会有很多刷新和不命中。

如果存储UUID值，则应该移除“-”符号；或者更好的做法是，用UNHEX()函数转换UUID值为16字节的数字，并且存储在一个BINARY(16)列中。检索时可以通过HEX()函数来格式化为十六进制格式。
UUID()生成的值与加密散列函数例如SHA1()生成的值不同的特征：UUID值虽然分布也不均匀，但还是有一定的顺序的。尽管如此，但还是不如递增的整数好用。

1.7 特殊类型数据

某些类型不直接与内置类型一致，低于秒级精度的时间戳就是一个例子。

另外一个例子是一个IPv4地址。人马经常使用varchar（15）来存储IP地址，然而它实际上是32未无符号整数，不是字符串。

应该用无符号整数存储IP地址。MySQL提供inet_aton()和inet_ntoa()函数在这两种表示方法之间转换。

2.MySQL schema设计中的陷阱

因为MySQL的实现机制，可能会犯一些只有在MySQL下发生的特定错误。我们从设计schema的方法方向上讨论如何规避陷阱。

2.1 太多的列

MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作是非常高的。 MyISAM的定长行结构实际上与服务器层的行结构正好匹配使用不需要转换。但是MyISAM变长行结构和InnoDB的行结构则总是需要转换。
如果有太多的列，但是你在实际查询中只使用一小部分的列，那么此时转换的代价就很高了。

2.2 太多的关联

“实体-属性-值”（eva）设计模式是很糟糕的。
解析和优化查询的代价也会成为MySQL的问题。
一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。

2.3 全能的枚举

注意防止过度使用枚举。
在枚举列表中增加一个新成员，且不是在末尾增加时就要做一次alter table操作。
alter table的开销大，特别是对大表。

2.4 变相的枚举

枚举（ENUM）列允许在列中存储一组定义值中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。有时候这可能容易导致混乱。

2.5 非次发明的NULL

之前写过少使用NULL（因为空间和时间的开销），但是遵循这个原则也不要走极端。
在某一些场景使用NULL可能会起到比较好的效果。

比如伪造的全0可能导致很多问题。

3 范式和反范式

3.1范式的优点和缺点

优点：

• 范式化的更新操作通常比反范式化要快。
• 当数据较好地范式化时，就只有很少或没有重复数据，所以只需要修改更少的数据。
• 范式化的表通常更小，可以更号地放在内存里，所以执行操作会更快。
• 很少有多余的数据意味着检索列表数据时更少需要distinct或group by语句。

缺点：

• 范式化设计的schema的缺点是通常需要关联。稍微复杂的一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

3.2 反范式的优点和缺点

优点：

• 因为所有数据都在一张表中，避免了关联。
• 全表扫描，当数据比内存大时可能比关联要快，因为避免了随机I/O。（全表扫描基本是顺序I/O）
• 能使用更有效的索引策略

缺点：

• 存储数据量大
• 更改和删除可能导致大量错误，以至于要做更多修改或删除。

3.3 混用范式化和反范式化

范式化和反范式化的schema各有优劣，怎么选择最佳的设计？
事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实世界中很少会这么极端的使用。在实际应用中经常混用，可能使用部分范式化的schema、缓存表，以及其他技巧。
最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在MySQL5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。
在我们的网站实例中，可以在user表和message表中都存储account_type字段，而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效的获取数据。
但是现在更新用户的账号类型的操作代价就高了，因为需要同时更新两张表。至于这会不会是一个问题，需要考虑更新的频率以及更新的市场，并和执行SELECT查询的频率进行比较。
另一个从父表冗余一些数据到子表的理由是排序的需要。例如，在范式化的schema里通过作者的名字对消息做排序的代价将会非常高，但是如果在message表中缓存author_name字段并且建好索引，则可以非常高效的完成排序。
缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（像很多论坛做的），可以每次执行一个昂贵的子查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。

4.缓存表和汇总表（没掌握）

缓存表（汇总表）
假如统计一个网站24小时发出的消息数，在一个比较忙碌的网站下不可能随时维护一个精准的计数器。实时计算统计值是很昂贵的操作，因为要扫描表中的大部分数据。代替方案是每小时生成一张汇总表，这样比实时计算要高效的多。

简单的说就是维护一些复杂 耗时的计算 那么用汇总表是比较好的选择。

4.1 物化视图（没掌握）

物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。MySQL并不原生支持物化视图，可以使用Justin Swanhart的开源工具Flexviews，也可以自己实现物化视图。

4.2 计数器表

如果在表中保存计数器，则在更新计数器时可能碰到并发问题。但如果创建一个独立的表存储计数器是个好主义。这样可以使计数器表小且快。使用独立的表可以帮助避免查询缓存失效。

以网站点击量为例子，计数器表最好尽量简单。

create table hit_counter(
cnt int unsigend not null
)engine=innodb;

每次点击都会导致对计数器进行更新：

update hit_counter set cnt = cnt + 1;

但有一个问题，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁（行级锁）。这会使得这些事务只能串行执行。要获得更高的并发性能，可以将计数器保存在多行，每次随机选择一行进行更新。
这种做法关键在于网站点击修改多，而读取少，读取的时候还是要读取多个行然后相加。

create table hit_counter(
	slot tinyint unsigned nut null primary key,
	cnt int unsigned not null
	)engine = innodb;

预先在这张表增加100行数据。现在选择一个随机的槽（slot）进行更新：

update hit_counter set cnt = cnt + 1 where slot = RAND() * 100;

要获得统计结果，需要下面这样的聚合查询：

select sum(cnt) from hit_counter;

一个常见的需求是每隔一段时间开始一个新的计数器（例如每一天）。如果需要这么做，则可以在简单的修改一下这个表设计：

create table daily_hit_counter(
	day date not null,
	slot tinyint unsigned not null,
	cnt int unsigned not null,
	primary key(day,slot)
)engine=InnoDB;

在这个场景中，可以不用像前面的例子那样预先生成行，而用on duplicate key update代替：

insert into daily_hit_counter(day,slot,cnt)
	values(current_date,rand()*100,1)
	on duplicate key update cnt = cnt + 1;

如果希望减少表的行数，以避免表便得到太大，可以写一个周期执行的任务，合并所有结果到0号槽，并且删除其他槽：

updae daily_hit_counter as c
	inner join(
		select day,sum(cnt) as cnt,min(slot) as mslot
		from daily_hit_counter
		group by day 
	)as x using(day)
set c.cnt  = if(c.slot = x.mslot,x.cnt,0),
	  c.slot = if(c.slot = x.mslot, 0, c.slot); 

为了挺高查询速度，有时候需要一些额外的索引，冗余，汇总表等，这样数据的写入会更慢一些，但是数据的读取会更快一些，这也是常用的优化手段。

5 加快ALTER TABLE操作的速度

我们下面要演示的技巧是不受官方支持的，也没有文档记录，并且也可能不能正常工作，采用这些技术需要自己承担风险。建议在执行之前首先备份数据！

ALTER TABLE 操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，如何从旧表中查除所有数据插入新表，如何删除旧表。这样可能会花费很长的时间，如果内存不足而表又很大，而且还有很多所有的情况下尤其如此。许多人有这样的经验，ALTER TABLE操作需要花费数个小时甚至数天才能完成。

对于常见的场景，能使用的技巧只有两种：

1. 现在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换。
2. 另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删除表操作交换两张表。

不是所有alter table操作都会引起重建。有两种方法可以改变或删除一个列的默认值。以修改电影的默认租赁期限为例子：

1.先阐述很慢的方法。

alter table sakila.film
modify column rental_duration tinyint(3) not null default 5;

这个操作拷贝了整张表到一张新表，甚至列的类型，大小和可否为NULL属性都没改变。
理论上MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要动表本身。然而MySQL还没有采用这种优化的方法，所有的modify column操作都将导致表重建。

2.另一种方法是通过alter column操作来改变列的默认值：

alter table sakila.film 
alter column rental_duration set default 5;

这个语句会直接修改.frm文件而不设计表数据。所以这个操作非常快。

5.1 只修改.frm文件

下面这些操作是有可能不需要重建表的：

• 移除（不是增加）一个列的auto_increment属性。
• 增加、移除，或更改enum和set常量。如果移除的是语句有行数据用到其值的常量，查询将会返回一个空字符串。

基本的操作是为想要的表结构创建一个新的.frm文件，然后用它替换调语句存在的那张.frm文件，像下面这样：

1. 创建一张有相同表结构的空表，并进行所需要的修改（例如增加enum常量）。
2. 执行flush tables with read lock。这将会关闭所有正在使用的表，并且禁止任何表被打开。
3. 交换.frm文件。
4. 执行unlock tables来释放第2步的读锁。

5.2 快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引：

alter table test.load_data disable keys;
-- load the data

alter table test.load_data enable keys;

这个技巧能够发挥作用，是因为构建索引的工作被延迟到数据完全载入以后，这个时候语句可以通过排序来构建索引。这样做会快很多，并且使得索引树的碎片更少更紧凑。

下面是操作步骤：

1. 用需要的表结构创建一张表，但是不包括索引。
2. 载入数据到表中以构建.MYD文件。
3. 按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的.frm和.myi文件。
4. 获取读锁并刷新表。
5. 重命名第二张表的.frm和.MYI文件，让MySQL认为是第一张表的文件。
6. 释放读锁。
7. 使用repair table来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。

这个操作步骤对大表来说会快很多。