MySQL数据类型优化

Schema与数据类型优化

通过性能剖析进行优化

性能剖析一般有两个步骤：测量任务所花费的时间，然后对结果进行统计和排序，将重要的任务排在前面。

两种类型的性能剖析：

1. 基于执行时间的分析
2. 基于等待的分析。判断任务在什么地方被阻塞的时间最长

4.1 选择优化的数据类型

1. 更小的通常更好。

   占用更少的磁盘、内存和CPU缓存，且处理时需要的CPU周期也更少。更小的数据类型通常更快

2. 简单就好

   简单数据类型的操作通常需要更少的CPU周期。例如整型比字符串操作代价更低，因为字符集和校验规则（排序规则）使字符比较比整型比较更复杂

3. 尽量避免NULL

   如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL中也需要特殊处理。

4.1.1 整数类型

​ 有两种类型的数字：整数和实数。如果存储整数，可以使用这几种类型：tinyint、smallint、mediumint、int 、bitint、分别占用8、16、24、32、64位存储空间。范围从 -2^n-1 到 2^n-1，其中n是存储空间的位数。如果使用无符号属性，正数上限提升一倍。

​ MySQL可以为整数类型指定宽度，例如int(11)，它不会限制指的合法范围，只是控制一些交互工具用来显示字符的个数。

4.1.2 实数类型

实数是带小数部分的数字。

float（当我们把数据插入float类型时，mysql会优先保留6位有效数字，对右侧的数字进行四舍五入） 和double类型支持使用标准的浮点运算进行近似运算。

decimal（定点型，不存在误差） 类型用于存储精确的小数。高版本支持 精确计算。

4.1.3 字符串类型

varchar

​ varchar类型用于存储可变长字符串，比定长类型更省空间，因为它仅使用必要的空间。varchar需要使用1或2个额外的字节记录字符串的长度。如果列的最大长度小于等于255字节，使用1个字节表示，否则使用2个。对于update操作，可能使行变得更长，导致需要额外的工作，如果行占用增长，页内没有空间，对于InnoDB则需要分裂页来使行可以放进页内。存储和检索的时候会保留末尾空格。

char

​ char类型是定长的：根据定义的长度分配足够的空间。当存储char值时候，会删除掉所有末尾的空格。适合存储很短的字符串或者所有值接近同一长度。定长存储不容易产生碎片。

注意：varchar(10)和varchar(100)存储’hello’的空间消耗是一样的。但是 更长的列活消耗更多的内存，因为MySQL通常会分配固定大小的内存快来保存内部值。

使用枚举代替字符串类型

有时候可以使用枚举代替常用的字符串类型。枚举可以把一些不重复的字符串存储成一个预定义的集合。内部回把每个值在列表中的位置保存为整数，在表的.frm文件中记录“数字-字符串”的映射关系的“查找表”。内部是按照整数排序的。

4.1.4 日期与时间类型

datetime

​ 能保存大范围的值，从1001到9999年，精度为秒。把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节存储空间。

timestamp

​ 保存了从1970年1月1日午夜以来的秒数，使用4个字节的存储空间，目前上限是2038年。timestamp显示的值也依赖于时区。

​ 默认情况下，如果插入的时候没有指定timestamp列的值，MySQL则设置这个列的值为当前时间，更新也会（除非指定时间）。timestamp的列默认为not null 。