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

1. 选择合适的类型：更小的通常更好、简单就好、尽量避免NULL
2. 数据类型：
    -0- 数值类型 
        1. 整数类型： TINYINT（1字节，相当于byte），SMALLINT（2字节，相当于short），MEDIUMINT（3字节），INT（4字节），BIGINT（8字节，相当于long）
        2. 浮点类型：FLOAT（4字节），DOUBLE(8字节)、DECIMAL（小数点前4字节、小数点1字节，小数点后4字节，共9字节）
    -1- 字符串类型
        1. CHAR和VARCHAR的区别:
            1. char是固定长度，char(1)占用一个字节，varchar是可变长度，varchar(1)占用两个字节（还有一个字节存储长度），varchar（1000）占用1002个字节，当长度超过255时，需要两个字节存储长度
            2. varchar会产生碎片，char不会产生碎片，因此当更新较多时，使用char比varchar好
            3. 使用varchar的情况：字符串列的最大长度比平均长度大很多时；列更新很少，所以不会产生碎片；使用了UTF-8这样的复杂字符集，每个字符都使用不同的字节数存储
        2. BLOB和TEXT类型都是存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。排序时可以只对字符串前max_sort_length字节进行排序，或者使用order by substring(column,length)
    -2- binary和varbinary存储的是二进制字符串，填充采用的是\0，大小写更加敏感，每次按一个字节，根据字节的数值进行比较，进行字符串比较时，使用它们比varchar和char更好，存储的形式是字节码
    -3- ENUM和SET类型，实际存储的是数字，由数字映射到具体类型，即压缩了存储。
    -4- DATETIME和TIMESTAMP的区别：
        1. DATETIME保存的范围是1600~9999年，精度为秒，时间格式YYYYMMDDHHMMSS，与时区无关，使用了8个字节的存储空间
        2. TIMESTAMP保存了从1970年1月1日午夜以来的秒数，与UNIX的时间戳相同，使用了4个字节的存储空间，存储范围比DATETIME小得多，只能表示1970年到1938年，显示依赖于时区
        3. 除特殊要求外，通常尽量使用timestamp，因为timestamp空间效率高，并且提供时区支持
    -5- BIT位数据类型，慎用
3. 标示符的选择
    1. 尽量使用整数来作标示符，它们可以AUTO_INCREMENT
    2. 使用字符串不是好的选择，它们消耗空间，插入慢是因为会随机写到索引的不同位置，查询慢是因为逻辑上相邻的行为分布在磁盘和内存的不同地方，使得局部性原理失效
    3. 使用UUID时，应该移除-符号，但是不如整数好


4. IP地址应该用无符号整数表示，MySQL提供INET_ATON()和INET_NTOA()函数来转化两种方式

5. MySQL schema设计中的陷阱：太多的列、太多的关（12张表以内）联、全能的枚举、变相的枚举（当取值单一时，用enum代替set）、非此发明的null（null可用）

6. 范式和反范式：
    1. 范式的优点：更新快、较少的冗余数据、更少需要distinct和group by语句，缺点在于多表的关联操作耗时
    2. 反范式的优点：不需要关联表，缺点：存在大量冗余数据，更新操作慢

7. alter 修改表结构，使用alter table 表名 alter column 列名 set default x，这会直接修改frm文件，速度很快。
    1. 当需要移除一个列的AUTO_INCREMENT属性，或增加移除或更改enum和set常量的值时，可以使用如下操作
        1. 创建一张有相同结构的空表（影子表），并对它的表结构作出修改，create table x like source，注：alter column（设置、删除列，直接修改.frm，因此速度快）、modify column（不能重命名，涉及表结构，速度慢）、change column（重命名、列类型变更、列位置）语句修改列是不同的
        2. 执行flush tables with read load，这会关闭所有正在使用的表，并且禁止任何表被打开
        3. 交换.frm文件，使用mv命令即可
        4. 执行unlock tables 来释放第2步的读锁