总结作为开发人员重点注意的内容！这是一篇有关高性能MYSQL第四章schema相关的笔记。
0.前言
在项目中，数据库表列有两个text字段，用来存储大文本，在数据规模达到40万后，如果查询没命中索引，发现耗时会达到5s以上，在表中删除这两个大字段后，就算全表扫描，耗时也不过0.6s左右。
解决方案：
- 将这类不常参加查询的大字段放入详情表中，但对现有程序影响过大（懒）。
- 查询字段统统加上索引。
1.选择合适的数据类型
- 选择合适的数据范围，能用 tinyint 不用 int。
- 字段越简单越好。存储时间使用datetime而不是字符串，存储ip用整数而不用字符串。
- 尽量避免null。null 会使索引更加复杂，null改为not null性能提升较小，但设计时，逻辑上不可能为null的值，最好加上not null限制。
datetime 和 timestamp 比较
相同点: 都能存储日期、时间，精确到秒。
不同点: timestamp 只占 datetime 一半存储空间，具备时区功能，同时允许的时间范围小得多。
1.1.1 整数类型
类型及存储空间
- tinyint(1字节)
- smallint(2字节)
- mediumint(3字节)
- int(4字节)
- bigint(8节字)
int(11) 其中11只是指定显示宽带，对存储空间无任何影响。
可以指定类型为unsinged，但java不支持unsinged, 使用相同类型可能会有麻烦。
1.1.2 实数类型
精确类型：decimal, 不精确类型: double, float。
decimal 支持存储和计算的精确。计算性能比浮点类型差一点。
decimal(11,4)表示支持4位精确小数，整数部分最多只能有7位数字。
decimal类型4个字节存储9个数字，decimal(18,9)占用9个字节，前面9位整数占4个字节，小数点1个字节，后面小数占4个字节。
mysql5.0版本及以上，decimal 最多存储65个数字。
浮点类型在表示同样范围，通常比decimal使用更少的空间。
在数据量比较大的情况下，可以用bigint代替decimal, 将小数的位数乘以相应的倍数转为bigint,可避免不精确的问题。
1.1.3字符串类型
varchar 和char
varchar存储可变长的字符串，使用额外的1个或两个字节额外存储空间，表示字符串的长度。
varchar在table指定row_format=fixed情况下，也是定长的，这会导致空间的浪费。
varchar(256)使用一个字节存储字符字符数。varchar(1000)使用两个字节存储字符数。
update varchar字段可能会导致列变得更长，innodb存储引擎会导致出现分裂页的情况出现，myisam将行拆分成不同的段存储。
varchar适用场景：最长字符串比平均字符串长度长很多。
char是定长的，不容易产生碎片，适用于存储密码或md5值, 或表示性别，F/M, varchar(1)需要两个字节，char(1)需要一个字节。
char 会截断末尾的空格。
varchar(5)和varchar(100)都能存储5个字符，但varchar(100)性能会比varchar(5)糟糕，所以最好选择合适的长度。
text与blob类型
text存储大文本，blob存储二进制数据。
大文本有关的类型: tinytext, smalltext, text, mediumtext, longtext。
二进制有关的类型: tinyblob, smallbolob, blob, mediumblob, longblob。
当text,blob太大时，会作为一个对象单独存储，行中使用指针指向数据位置。
个人建议少用或不用这种类型，或拆除到详情表，减少对查询带来影响
enum类型
字段定义：animal enum("dog", "fish", "cat")
enum 实际存储为整数，并在.frm文件中保存字符串-整数对应关系。
看个列子更容易明白:
create table enum_test(
id int auto_increment,
animal enum("dog", "fish", "cat"),
primary key(id)
);
insert into enum_test(animal)
values
("dog"),
("cat"),
("dog"),
("fish")
;
对该表进行查询:
select animal+0, animal
from enum_test
order by animal
;
可以看到排序是按照整数排序的，即定义的顺序进行排序，而不是字符串序。
解决方案：定义时按照字符串序定义。
自定义顺序排序使用field函数，但这样将无法使用索引：
select animal+0, animal
from enum_test
order by field(animal, "cat", "fish", "dog");
字段join效率比较: enum join enum > varchar join varchar > enum join varchar = varchar join enum。
使用alter talbe 在枚举类型中添加字符串时，会重建整个表，除非总是在末尾添加值。
坏处：当枚举值为数字时，很容易发生困惑，尽量避免这种情况。
1.1.4 时间类型
datetime类型:
- 存储范围大: (1001年-9999年)精度为秒，将值存储为 YYYYMMDDHHMMSS的大整数中，占用八字节。
timestamp:
- 存储范围小:(1970年-2038年)，精度为秒，存储从1970-1-1年以来的秒数，占用4字节。
- unix_timestamp()支持日期->秒数, from_unixtime()支持秒数->日期。
- 具备时区概念，在mysql服务器，操作系统，连接串都可以指定时区，推荐在连接串指定时区：（jdbc）serverTimezone=Asia/Shanghai
- 插入时没有指定会使用当前时间作为默认值
不推荐自己使用整数值来存储时间，这和内部存储没什么差别，没带来什么好处却多了额外的处理逻辑。
书中推荐使用timestamp, 个人推荐使用datetime，timestamp花活太多。
1.1.5 位数据类型
bit类型
bit列可以存储多个true/false值，bit(17)可以存储17个true/false值，myisam 使用一个比特来存储一个单独的true/false值，innodb使用最小的整数值来存储bit列的值。
myisam: bit(17)使用17个比特存储。
innodb: tinyint 即可存储。
高性能mysql认为应该慎用这个类型，所以就不深究了。
set 类型
好处: 存储使用打包的位存储，占用空间少。
坏处: 无法通过索引查找，修改列定义代价较高：需要alter table。
权限控制使用例子:
create table acl_test(
id int not null auto_increment,
perms set('can_read', 'can_write', 'can_delete'),
primary key(id)
);
insert into acl_test(perms)
values
('can_read,can_write'),
('can_read,can_delete')
;
select *
from acl_test
where find_in_set('can_write', perms)
;
查询结果:
1.1.6 主键及其他
主键最好选择自增id为主键，对数据性能比较好，同时相对应orm也支持，可能存在某些orm层框架不支持联合主键的情况。
ip与int转换函数: inet_aton(), inet_ntoa()。
1.2 schema错误设计
- 列太多（几千个列）
- join 太多，mysql 限制最多可以join 61张表，实际为了性能考虑，最好一次查询join不超过12张表
- 错误使用枚举, 如一个枚举字段存储国家，枚举值有几十上百个, 更好的解决办法是使用关联表去存储国家信息，用整数去映射。