mysql性能优化笔记（1） 建表原则和字段设计

建表原则：

1.表必须定义主键，默认为ID，整型自增。

2.ID字段作为自增主键，禁止在非事务内作为上下文作为条件进行数据传递，禁止非自增非数字类型主键设计出现。

3.禁止使用外键，触发器，存储过程

4.常用字段与不常用字段分离

5.表默认使用InnoDB,国内表字符集默认使用gbk,国际默认使用utf8的表

6.单表一到两年内数据量超过500W或者数据容量超过10G考虑分表，且需要提前考虑历史数据迁移或者应用自行删除历史数据

7.单表记录大小禁止超过8K（列长度（中文）*2（gbk）/3(utf8)+列长度(英文)*1）

8.日志类数据不建议存储在 MySQL 上，优先考虑 Hbase ，如需要存储请使用压缩表存储

字段设计

1.表被索引列必须定义为not null,并设置default值

2.禁止使用float、double类型，建议使用decimal替代

3.禁止使用blob，text类型保留大文本、文本、图片，建议使用其他方式存储（文件服务器）， mysql 只保存指针信息

4.禁止使用varchar类型作为主键语句设计

5.varchar长度设计需要根据业务实际需要进行长度控制，禁止预留过长空间

6.加入冗余字段，提高查询速度

如阅读数量，可以加到文章表中，每次阅读都+1

7.字段类型优先级

整型 >date,time > enum char > varchar > blob text

整型：定长，没有国家/地区之分，没有字符集的差异

比如：tinyint 1,2,3,4 char（1） a，b，c，d

从空间上，都是占一个字节，但是order by排序，前者快

原因：后者需要考虑字符集与校对集（就是排序规则）

time 定长

enum 主要起到约束目的，内部用整型来存储

char 定长

varchar 变长

text/Blob 无法用到内存临时表

附录：

整型所占字节，以及能表示的最大长度

类型	字节	最大值	最小值
tinyint unsigned	1	255	0
smallint unsigned	2	65535	0
mediumint unsigned	3	2^24-1	0
int unsigned	4	2^32-1	0
bigint unsigned	8	2^64-1	0
tinyint signed	1	127	-128
smallint signed	2	32767	-32768
mediumint signed	3	2^23-1	-2^23
int signed	4	2^31-1	-2^31
bigint signed	8	2^63-1	-2^63