MySQL----Schema与数据类型优化（1）

1，前言

读《高性能MySQL》所作笔记

2，选择优化的数据类型

选择正确的数据类型对于获得高性能至关重要
下列是几个简单的原则，有助于做出更好的选择

更小的通常更好

值得注意的是，选择小数据类型的同时，要注意数据的范围。因为在schema的多个地方增加数据类型是一个非常耗时和痛苦的操作。若无法确定那个数据类型是最好的，那就选择不会超出范围的最小的数据类型

简单就好

例如整数相比字符操作代价会更低【例如排序】
例子

1. 应该使用MySQL内建的类型而不是字符串来存储日期和时间
2. 应该用整形存储IP地址【MySQL中有提供专门的方法供转换】

尽量避免使用NULL

实际上查询中包含可为NULL的列，对于MySQL来说更加难以优化，因为可为NULL的列会使得索引，索引统计和值比较都更加复杂

慷慨是不明智的

尽量为数据分配真正需要的时间

在为列选择数据类型的时候，第一部需要确定合适的大类型：数字、字符串、时间等；下一步就是选择具体类型！【相同大类型下的不同子类型数据有时也有一些特殊的行为和属性】

DATETIME 和 TIMESTAMP

• 相同点：两者都可以存储相同类型的数据：时间和日期，精确到秒
• 不同点：
  • TIMESTAMP只是用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力
  • TIMESTAMP允许的时间范围要小得多

可以说，因为TIMESTAMP的特殊功能要比DATETIME的要多，所以其能表示的时间范围要比DATETIME小

2.1，数字

有两种类型的数字：整数、实数

2.1.1，整数类型

整数类型	位数(N)	范围
TINYINT	8	-2(N-1) ~ 2(N-1)-1
SMALLINT	16	-2(N-1) ~ 2(N-1)-1
MEDIUMINT	24	-2(N-1) ~ 2(N-1)-1
INT	32	-2(N-1) ~ 2(N-1)-1
BIGINT	64	-2(N-1) ~ 2(N-1)-1

值得一提的是，MySQL可以为整数类型指定宽度，例如INT(11)，但大多数应用来说，这是没有意义的；它并不会限制值的合法范围，只是规定了MySQL的一些交互工具（比如MySQL命令客户端）用来显示字符的个数。而对于存储与计算来说，INT(1)和INT(8)是相同的

2.1.2，实数类型

• 标准的浮点运算
  • FLOAT：4个字节存储
  • DOUBLE：8个字节存储
• 存储精确的小数
  • DECIMAL【MySQL5.0及以后，将数字打包保存到一个二进制字符串中（每4个字节存储9个数字）】
  • 示例：DECIMAL(18,9)小数点两边将存储9个字节，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
  • DECIMAL只是一种存储格式，在计算中DECIMAL会转换为DOUBLE类型

2.2，字符串类型

2.2.1，VARCHAR和VARCHAR

VARCHAR和VARCHAR是两种最主要的字符串类型，但这些数据是怎么存储在磁盘与内存中的，这与存储引擎的具体实现有关

• VARCHAR
  • VARCHAR用于存储可变长字符串，是最常见的字符串数据类型。VARCHAR通常用额外的1个字节或者2个字节进行数据的存储，例如VARCHAR(10)需要1个字节进行长度信息的存储；VARCHAR(1000)需要2个字节进行长度信息的存储【VARCHAR中有专门的字节来记录数据的长度】
  • 值得注意的是：VARCHAR由于存储的是可变长的数据，在UPDATE时可能使行变得比原来更长，并且如果此时页内没有更多的空间进行存储，依据存储引擎的不同，所进行的处理方式也是不同的：InnoDB会进行页的分裂，MyISAM会将行拆分成不同的片段进行存储
• CHAR
  • CHAR类型是定长的，因此适用于存储一些经常改变的值，这样就不会存在页内存储空间不够的问题。
  • CHAR类型在存储的时候，MySQL会删除所有的末尾空格（在MySQL4.1和更老的版本中VARCHAR也是这样实现的，就是说这些版本中的CHAR和VARCHAR的逻辑是一样的，区别只是存储格式的不同）
  • CHAR值会根据需要采用空格进行填充以方便比较
  • 适用情景：
    • CHAR适用于存储很短的字符串，或者所有的值都接近于同一个长度
    • CHAR要比VARCHAR好，因为定长的CHAR类型不容易产生碎片
    • 对于经常变更的数据，CHAR要比VARCHAR在存储空间上更有效率【例如存储一个Y或者N的值。用CHAR(1)要比VARCHAR(1)好，因为前者需要1个字节，后者则需要2个字节】

值得一提的是：InnoDB会将过长的VARCHAR转换为BLOB进行存储

2.2.2，BINARY和VARBINARY

BINARY和VABINARY它们存储的是二进制字符串
他们与CHAR、VARCHAR相似，但是他们存储的是字节码而不是字符
在填充上也不一样：MySQL填充BINARY采用的是\0（零字节）而不是空格，检索的时候也不会去掉填充值
相较于CHAR和VARCHAR的优势

• 大小写敏感【由于存储的是字节码】
• MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较【因此二进制比较要比字符比较简单的多，所以更快】

2.2.3，BLOB和TEXT类型

BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式进行存储

BLOB类型(二进制类型)	TEXT类型(字符类型)
TINYBLOB	TINYTEXT
SMALLBLOB	SMALLTEXT
BLOB	TEXT
MEDIUMBLOB	MEDIUMTEXT
LONGBLOB	LONGTEXT

与其他类型不同，MySQL在处理BLOB和TEXT值的时候会将其当作一个独立的对象进行处理。存储引擎在进行处理的时候通常会做特殊处理，当BLOB和TEXT值太大的时候，InnoDB会使用专门的外部存储区域进行存储，此时每个值在行内需要1-4个字节存储一个指针，然后在外部存储区域存储实际的值

BLOB和TEXT之间仅有的不同在于BLOB存储的是二进制数据，没有排序规则或字符集，而TEXT有字符集和排序规则

MySQL对BLOB和TEXT类型进行排序时与其他类型是不同的：它只对每个列的最前max_sort_length而不是整个字符串进行比较

因为某些存储引擎不支持BLOB和TEXT类型(例如Memory)。在这种条件下，最好的方案是不使用BLOB和TEXT类型，如果实在无法避免。有一个技巧就是在所有用到BLOB字段的地方都使用SUBSTRING(column,length)将列值转换为字符串

未完待续……