Mysql性能优化之数据类型优化

一、选择正确的数据类型对于获得高性能至关重要
1.1更小的通常更好
占用更少的磁盘、内存和CPU缓存
1.2尽量避免null
如果查询中包含可为null的列，对Mysql来说更难优化，因为可为null的列使得索引、索引统计和值都更复杂。会使用更多的存储空间.
2、整数和实数
整数tinyint、smallint、mediumint、int、bigint分别使用8,16,24,32,64位存储空间
实数delcimal存储精确的小数支持精确运算、float和double支持浮点运算进行近似计算
3、字符串类型
varchar和char类型
varchar类型用于存储可变长字符串，比定长类型更节省空间。varchar需要使用1或者2个额外字节记录字符串长度,如果列的最大长度小于或等于255字节需要使用1个字节表示否则使用2个字节，比如一个varchar(10)的列需要11个字节的存储空间。varchar(1000)的列需要1002个字节。
char类型是定长的，char很适合存储很短的字符串，或者所有值都接近同一个长度
4、blob和text类型
blob和text类型分别采用二进制和字符方式存储
5、enum枚举类型
枚举字符串列表是固定的，添加或删除字符串必须使用alter table。
6、日期和时间类型
datetime和timestamp。
datetime能保存很大范围的值，从1001年到9999年精度为秒，他把日期封装到YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。
timestamp类型保存了从1970年1月1日午夜以来的描述，timestamp只使用了4个字节的存储空间只能表示1970年到2038年。timestamp默认为not null。
除了特殊行为之外，通常应该尽量使用timestamp，因为它比datatime空间效率更高。
7、选择标识符(identifier)
为标识列选择数据类型的时候应该选择与关联表一样的类型，整数通常是标识列最好的选择，并且可以使用auto_increment。
二、mysql schema设计中的陷阱
2.1太多的列
mysql的存储引擎api工作时需要在服务层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换行数据结构的操作代价是非常高的
2.2太多的关联
mysql限制了每个关联操作最多只能有61张表，单个查询最好在12个表以内做关联
2.3alter table操作的速度
mysql的alter table操作的性能对大表来说是个大问题。mysql执行大部分修改表结构操作的方法是用新的结构创建一个空白表，从旧表中查出所有数据插入新表，然后删除旧表。
所有的modify column操作都将导致表重建,理论上列的默认值存储在.frm文件中，但是可以通过alter column操作来改变列的默认值
alter table xxx
alter column xxx set default 5;
alter table 是件让人痛苦的操作，大部分情况下都会锁表并且重建整张表。