Mysql高性能 - 2. schema与数据类型优化

1. 数据类型优化

1. 选择更小的数据类型
    varchar(50)能满足的字段不要设置为varchar(100)，如果新加字段没有指定长度，数据库会预留最大长度出来，极大的浪费资源。MySQL4.1以前，VARCHAR数据类型所支持的最大长度255，5.0以上版本支持65535字节长度，utf8编码下最多支持21843个字符(不为空)。
    
2. 尽量使用简单的数据类型
　　能用整型的不使用字符串。不要使用整型来存储日期和时间格式，要使用内建类型时间和日期格式。ip地址在保存的时候应该使用整型，原因：待补充

3. 尽量避免null
　　null的列会影响在此列建立索引，且会占用更多的空间，建议给默认值。后期查询，比较大小时，null的记录也会被忽略，影响结果。

 2. 关于浮点型

float: float存储方式就是一位整数，6位小数，以及乘以10的多少次方。能保留从左到右7位数字的有效性，多的部分会四舍五入。占用4个字节
double：与float类似，可保存16位数字。占用8个字节。

double 和 float 的区别是 double 精度高，有效数字 16 位，float 精度 7 位。但 double 消耗内存是 float 的两倍，double 的运算速度比 float 慢得多

decimal cpu不支持对decimal的计算，mysql5.0以上的版本的服务器实现了对decimal的计算。mysql使用4个字节存储九个数字。decimal(18,9)将占用9个字节，小数点前后9个数字各占4个字节，小数点占一个。
因为decimal需要占用额外的空间和计算，所以在小数位要求不是必须精确的情况下，建议使用浮点型。如果必须要使用decimal而且数据量巨大的情况下，使用bigint来保存，然后存储小数位数即可。

decimal和numeric用法一摸一样，可以指定小数位数decimal(16,3)，从而指定精度，而float和double能保留的有效数字是固定的。

 3. 关于字符串

1. 当最大长度比平均长度要大很多时，使用varchar比char好。
2. 在update较多的情况，且字符长度差异不是特别大的情况下，建议使用char
3. varchar在update时，现有值与之前的值的长度不同时，容易产生碎片。

4. 时间格式

1. timestamp占用4个字节，只能表示1970-2038之间的时间数据
2. datetime占用8个字节，可以保存1001-9999之间的时间数据
3. 无特殊要求的情况下，使用timestamp

 

5. 复制表

1. create as 只复制数据，没有表结构，比如索引什么的。
create table table_name as 
select * from table_old

2. create like 只复制表结构，不复制数据（再使用insert into补充数据）
create table table_name like table_old

3. oracle复制表结构的方法
create table tship_count1
select * from  tship_count where 1=2

6. 修改表alter table

-- 设置或删除列的默认值。该操作会直接修改.frm文件而不涉及表数据。此操作很快
-- 1. ALTER COLUMN
 ALTER TABLE  dsp_ad_center.XianDuan ALTER COLUMN xxx SET DEFAULT 100;


-- 2. MODIFY COLUMN
-- 会引起表的重建
 ALTER TABLE  dsp_ad_center.XianDuan MODIFY COLUMN xxx  INT NOT NULL DEFAULT 101;

 
-- 3. CHANGE COLUMN  -- 列的重命名、列类型的变更以及列位置的移动 ,会引起表的重建。是对modify的扩充
-- 语法： -- CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
 ALTER TABLE  dsp_ad_center.XianDuan CHANGE COLUMN xxx xxx INT FIRST