高性能mysql_schema/数据优化

建立表时的字段设计优化查询

1. 数据类型大小
   优先选择更小的数据类型，使用的硬盘，内存，cpu资源将会更小。选择不会超过范围的最小类型。

2. 简单数据类型
   简单数据类型需要更少的CPU周期，整型比字符操作代价低
   例如：timestap或者datetime存储时间，不用字符串；整型存储IP，不用字符串

3. 避免使用null值
   使用null对于索引 索引统计 值比较都更复杂，可以为null的列通常需要更多存储空间，作为索引时，每个记录需要一个额外的字节。设置为not null可以提升性能较小，设计时需要避免。
   但是innodb会对null使用单独的bit，对稀疏数据有比较好的空间效率，但是myisam不行。

字符型
varchar一般来说使用时去掉多余的空间（也可以设置定长），但是需要2个额外字节记录字符串长度，但是在update时存在变长的情况使得更新操作不够高效。
（大小主要影响内存分配，对硬盘资源消耗差别不大）
适用于 ：
最大长度比平均长度大很多
列更新少
utf-8的复杂字符集

char适用于定长的情况，例如md5的密码，但是会删除末尾的空格。
适用于：
最大长度与平均长度差别不大
md5 哈希值定长类（使用varhcar每次都会减少字长，而且内存和硬盘中的表现形式也可能不太一样）
少量信息，减少记录字符占用

timestamp datetime
timestamp只占用datetime一般的空间，同时可以适应时区，可以自动更新，但是允许的时间范围少很多

整型：
tinyint smallint mediumint int bigint 8 16 24 32 64 位的存储空间； unsigned 使得范围变为非负值，正值范围扩大一倍。
int（）设定的长度只会制定显示的长度

实数型：
float double decimal（精度更高）

BLOB TXT：
BLOB 二进制 无排序规则或字符集
TXT有
两者过大时，引擎会使用专门的外部存储空间存储数据，需要额外的1~4个字节存储指针
列排序 选取max_sort_length

枚举
enum
create …{
e enum(a,b,c) not null
}
insert into … values(‘a’)
…(1)
通过数字集合替代字符，实际存储为数字，节省空间

BIT SET
可以用bit表示一连串的TRUE FALSE
create table an_bit (id bit(8));
insert into an_bit values (b’11′) 表示插入二进制 11
b’value’符号写位字段值

select * 查询为空
select id+0 查询为十进制数字
select bin(id+0) 查询为二进制

set可以存储大量的 TRUE FALSE类型，select时获得的是累计和（enum获取的是组合值 1+2+4=7）

特殊类型：
IP（32位无符号整数） 使用inet_aton() 和inet_ntoa()存储

schema设计优化：

陷阱：
1 太多的列，导致服务器性能损耗严重
2 太多的关联，EAV设计模式（糟糕的），mysql限制关联表少于61张。考虑到并发效率，12个表以内是最好的
3 全枚举，推荐用整数作为外键关联到字典表，或查找表查找具体值；否则新增枚举值使用alter table回产生阻塞
4 短集合，建议转为枚举，避免混乱
5 NULL的使用，尽量避免，但是对于某些设计 NULL可能带来某些优化

关于NULL的匹配：
1 匹配 != X时，会将NULL自动剔除
2 通配符 % 无法匹配NULL
3 分组时null作为单独的分组 group by
4 聚集函数忽略null count
5 select null not in set(null,…) 返回 null（in的话也返回null） ； x not in set(…) 返回0或者1 代表FALSE/TRUE； x not in set(null…)，返回null，和bull做 in not in匹配结果会返回null（只要集合不存在该值，总会与null比较，则返回null；存在则返回1或者0）

范式：
范式化：
更新更快，占用更少，内存执行更快，查询检索需要更少的distinct或者group by
反范式化：关联更少，数据很大时进行全表扫描 可以避免随机IO

缓存表 汇总表：
缓存表：可以简单从别的表获取 但是每次获取速度较慢的数据
汇总表：使用group by之类的聚合函数形成的累积数据

加速alter table：
默认值的修改
modify column xxx tinyint not null default 5
alter column xxx set default
修改默认值时采用第一种方法相当于一次全表的复制
第二个操作知识修改了.frm文件中的默认值，不涉及表修改
可以用到：
移除 auto_increment属性
增加移除更改enum set常量
替换.frm文件（替换时锁定表）

操作过程：
新建表，建立关系，更改为新的属性，锁表，替换frm文件后解锁，然后删除新的表

总结：