高性能MySQL-Schema设计与管理

如有侵权，请联系～
如有错误，也欢迎批评指正～
本篇文章大部分是来自学习《高性能MySQL》的笔记

1、简述

一个好的物理设计和逻辑设计是高性能的基石。设计一个好的表结构，对未来的优化也会有很大的好处。应该根据系统要执行的语句来设计Schema，在设计的同时需要权衡很多因素。例如，是不是一定要满足范式，反范式的设计虽然会存在数据冗余，但是在一定程度上可以提供系统的查询性能。

2、 选择优化的数据类型

选择一个合适的数据类型对高性能有至关重要的作用。选择数据类型有三种原则：

• 越小越好：在保证范围的前提下，尽可能选择数据最小的数据类型。因为更小的数据类型更快，占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少。
• 越简单越好：简单的数据类型操作需要的CPU资源更少。
• 尽量避免NULL：NULL一般是默认的属性，通常情况下指定列为NOT NULL。如果列为NULL，那么对MySQL优化更难，因为对索引、索引统计和值比较都更复杂。并且为NULL的列需要更多的存储空间。

在选择数据类型的时候，先选择合适的大数据类型，再选择具体的数据类型。

2.1 整数类型

整数的类型：TINYINT【1个字节】、SMALLINT【2个字节】、MEDIUMINT【3个字节】、INT【4个字节】、BIGINT【8个字节】。
int(10)这里的10只是展示的位数，但是底层存储仍然是使用4个字节，即int(10)和int(20)占用的空间是一样的。

2.2 实数类型

带有小数的实数分为精确和非精确类型。

• 非精确类型：FLOAT【4个字节】和DOUBLE【8个字节】。采用科学记数法进行存储。
• 精确类型：DECIMAL。MySQL5.0 和更高版本中的DECIMAL类型允许最多65个数字。会根据存储的数据大小影响列的空间。例如DECIMAL(18,9)小数点两边将各存储9 个 数字，一共使用9 个字节:小数点前的数字用4 个字节，小数点后的数字用4 个字节， 小数点本身占1 个字节。

2.3 字符串类型

CHAR和VARCHAR类型：

VARCHAR和CHAR是两种最主要的字符串类型。
VARCHAR类型： 用来存储变长的字符串。需要额外的1到2个字节用来存储字符串的长度，对于最长长度小于等于255个字节需要1个字节存储长度，否则就需要2个字节。但是针对于频繁进行update的不适合使用，因为update可能会导致第二次存储的数据变大，原来的页没有足够的空间进行存储，从而导致页分裂等问题。同时在查询中存在临时表的时候，varchar的列会按照最大值进行分配，所以varchar不是越大越好。
使用场景：

• 平均长度和最大长度相差比较大
• 列更新的比较少
• 像utf-8复杂的字符集，每个字符使用的存储空间不同

char类型： 定长的，并且会默认删除字符串末尾的空格。如果长度不够，会使用空格进行填充。
使用场景：

• 存储很短的字符串，或者所有值都接近同一个长度。例如密码的MD5值。
• 频繁更新的
• 列非常短。例如 Y或者N。因为使用varchar会额外使用1个字节存储长度。

BLOB和TEXT类型：

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。当他们很大的时候，会进行页外存储，页内只存储一个1～4字节的指针。它只对每个列的最前max_ sort_Length字节而不是整个字符申做排序。如果只需要排序前面 一小部分字符，则可 以减小max_sort_Length的配置，或者使用ORDER BY SUSTRING(CoLumn, length)。

TEXT 类型用于存储长文本字符串，适合存储大块的文本数据，如文章、评论或描述性文本。有四个变种，各自支持不同的最大字节数：

• TINYTEXT：最大可存储 255 字节。
• TEXT：最大可存储 65,535 字节（约 64 KB）。
• MEDIUMTEXT：最大可存储 16,777,215 字节（约 16 MB）。
• LONGTEXT：最大可存储 4,294,967,295 字节（约 4 GB）。

BLOB 类型用于存储二进制数据，适合存储图像、音频、视频或任何类型的二进制文件。同样有四个变种，各自支持不同的最大字节数：

• TINYBLOB：最大可存储 255 字节。
• BLOB：最大可存储 65,535 字节（约 64 KB）。
• MEDIUMBLOB：最大可存储 16,777,215 字节（约 16 MB）。
• LONGBLOB：最大可存储 4,294,967,295 字节（约 4 GB）。

枚举类型：
枚举列可以把 一些不重复的字符串存储 成一个预定义的集合。MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到一 个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的frm文件中保存“数字- 字符串” 映射关系的“查找表”。所以如果以后想增加枚举，按照在最后顺序增加，不要插入，因为插入前后可能映射关系就改变了，但是表中已经存在的数据还是原来的映射数字。

create table enum_test(
	id int(20) primary key,
	gender enum('m', 'w') not null
);

枚举排序是按照内部存储的整数进行排序，而不是定义的字符串。

2.4 日期和时间类型

MySQL有很多的数据类型可以存储时间和日期，例如：YEAR、DATE。MySQL存储的最小时间粒度为秒。两个比较常用的日期类型：DATETIME和TIMESTAMP。
DATETIME: 使用8个字节进行存储，能够保存从1001年到9999年并且精确时间为秒。和时区无关
TIMESTAMP: 使用4个字节进行存储，记录的是UNIX时间戳。存储范围为1970.1.1到2038。和时区相关。

2.5 位数据类型

BIT：可以存储一个或者多个true或者false的值。BIT(5)可以存储5位，但最多64位。InnoDB存储引擎使用的是能够容纳的最小的整数类型进行存储，所以并不能节省空间。MySQL会把BIT当作字符串，即存储的二进制会转换为ASCII的字符，但是在数字上下文中，就又会转换为数字。最好避免使用这种类型。

SET：MySQL内部是以一系列打包的位的集合来表示的。主要缺点是改变列的定义的代价较高:需要ALTER TABLE。

create table tbl_switch(
	id int(20) primary key,
	switch set('video', 'zhibo') not null
);
insert into tbl_switch(switch) values('video,zhibo');

在整数列中按照位操作：这种方式比较难理解，因为不知道每一位表示的含义。但是不需要通过ALTER TABLE修改枚举。

2.6 JSON数据类型

MySQL支持原生的JSON数据类型【纯粹主义认为：数据库存储JSON是一种反范式】。通过测试发现，存储JSON和将JSON转换为相应的schema相比：

• 存储空间：后者占用的空间更小，因为前者会有额外的字符，例如大括号、方括号、冒号等等。
• 查询速度：后者的查询速度也会更快一些。前者JSON可以创建虚拟列进行创建索引提高速度。

是否使用JSON取决于数据库中存储JSON的便捷性是不是大于性能。例如一些管理后台，存储JSON比较方便，并且性能要求不高。

2.7 选择标识符

一般来说，标识符是每个记录的唯一标识。为标识符列选择合适的数据类型非常重要。标识符可能是另一个表的外键，所以在进行多表联查的时候，进行比较的概率更大。因此，标识符的数据类型和外键列的数据类型要保持完全一致，因为如果不一致可能存在潜在的风险。例如set、enum他们底层可能是使用的数字进行存储的，bit在不同的语意下可能是字符串或者数字，这都会导致查询不符合预期。

3、schema设计中的陷阱