mysql数据类型整理

mysql数据类型：

1.尽量使用可以正确存储数据的最小数据类型。更小的数据类型常更快，因为它们占用更少的磁盘、内存和cpu缓存，并且处理时需要的cpu周期更少。

2.整型比字符操作代价更低，因为字符集和校对规则使字符比较比整型比较更复杂。

3.尽量避免使用null,因为null的列使得索引、索引统计和值比较更复杂。
4.整数类型  tinyint  8; smallint 16;mediumint 24;int 32; bigint 64位
   整数类型又可选的unsiged,表示不允许负值
5.实数类型 decimal,可以指定精度，用于存储精确的小数，如decimal(18,9),小数点两边各存储9个数字。 浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。
6.字符串类型  varchar和char类型
varchar存储可变长字符串，比定长类型更节省空间。（例外，mysql表使用row_format=fixed创建，会浪费空间）
但由于是变长的，update时可能会使行变的比原来更长，导致需要做额外的工作
下列情况使用varchar合适：
1.字符串列的最大长度比平均长度大很多
2.列的更新很少，所以碎片不是问题
3.使用了像utf-8这样复杂的字符集，每个字符都使用不同的字节进行存储

char是定长的，根据字符串长度分配足够空间。当存储char时，mysql会删除所有的末尾空格，适合
1.存储很短的字符串，或者所有值长度接近同一个长度
2.经常变更的字符串

blob和text类型都是为粗才能很大的数据而设计的字符串类型，分别次用二进制和字符方式存储 

尽量避免使用blob和text类型。如果无法避免，在所有用到blob字段的地方都使用substring(column,length)将列值转换为字符串（order by中也适用），这样就可以使用内存临时表了

7.日期和时间类型
datetime  精度到秒，把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储；
timestamp 和unix时间戳相同，只能表示1970年到2038年，使用4个字节存储；
mysql提供了from_unixtime()函数把unix时间转换为日期，并提供了unix_timestamp()函数把日期转换为unix时间戳

通常应尽量使用timestamp,因为它比datetime空间效率高

8.选择标识符
为标识列选择数据类型时，应选择和关联表中的对应列一样的类型
整数类型通常是标识符列最好的选择，因为它很快并且可以使用auto_increment

尽量避免使用字符串类型作为标识列，因为它很消耗空间，并且比整数类型慢。

如果存储uuid值，应移除“-”符号，更好的做法是，使用unhex（）函数转换uuid值为16字节的数字，并且存储在一个binary(16)列中，检索时通过hex()函数来格式化为十六进制格式

9.当研究一个cpu占用非常高德案例时，发现使用了非常宽的表，然而只有一小部分列会在实际中用到，这时转换的代价就非常高。

10.范式化与反范式化
1N范式：将字段拆分为不可再拆分；
2N范式：解决多对多问题；
3N范式：解决1对多问题；

范式优点：
1.更新操作更快；
2.重复数据较少或没有，所以需要修改的数据很少；
3.范式的表更小，可以更好的放进内存，执行操作更快；
4.很少的冗余数据，意味着检索表时很少用到distinct和group by语句

反范式化的优点：
所有数据在一张表中，可以避免表关联。

实际中通常是两者混用


11.缓存表 表示存储那些可以比较简单地从schema其他表获取数据的表，如逻辑上冗余的数据。
汇总表，保存的是使用group by 语句聚合数据的表

在使用汇总表和缓存表时，必须决定是实时维护数据还是定期重建

12.计时器表
表设计
create table daily_hit_counter(
day date not null,
slot tinyint unsigned not null,
cnt int unsingned not null,
primary key (day ,slot)
) ENGINE = InnoDB;


insert into daily_hit_counter (day,slot,cnt)
values (current_date,rand()*100,1)
on duplicate key update cnt = cnt + 1;
如果希望减少表的行数，避免表变得太大，可以写一个周期执行的任务，合并所有结果到0号槽，并且删除其他的槽：
update daily_hit_counter as c 
inner join (
select day, sum(cnt) as cnt, min(slot) as mslot
from daily_hit_counter 
group by day 
) as x Using(day)
set c.cnt = if(c.slot = x.mslot ,x.cnt,0),
c.slot = if(c.slot = x.mslot,0,c.slot);

delete from daily_hit_counter where slot <> 0 and cnt = 0;


13.加快 alter table操作的速度
mysql执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除就表。对应大表来说，可能会花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下，alter table操作可能会花费数个小时到数天，这将导致mysql服务中断。

常用的技巧：
1.先在一台不提供服务的机器上执行alter table 操作，然后和提供服务的主库进行切换；
2.用要求的表结构创建一张和原标无关的新表，然后通过重命名和删表操作交换两张表，可使用工具来完成：如facebook的online schema change