mysql优化-表的优化与列类型的选择

表的优化

1. 定长与变长分离
   
   • 如id int，占4个字节，char(4)占4个字符长度，也是定长。即每一单元值占的字节是固定的。核心且常用字段，宜建成定长，放在一张表。
   • varchar，text，blob变长字段，适当单放一张表，用主键与核心表关联起来。
2. 常用字段和不常用字段分离
   
   • 需要结合实际应用场景，分析字段的查询场景，查询频度低的字段单拆出来
3. 合理增加冗余字段
   
   • 空间换时间

列类型选择

1. 字段类型优先级

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

   列的特点分析:
   整型: 定长,没有国家/地区之分,没有字符集的差异
   time:定长,运算快,节省空间. 考虑时区;
   enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化
   Char:定长, 考虑字符集和(排序)校对集
   varchar:不定长 要考虑字符集的转换与排序时的校对集,速度慢.
   text/Blob:无法使用内存临时表

2. 够用就行，不要慷慨

   原因: 大的字段浪费内存,影响速度,
   以年龄为例，tinyint unsigned not null ,可以存储255岁,足够. 用int浪费了3个字节。
   以varchar(10) ,varchar(300)存储的内容相同, 但在表联查时,varchar(300)要花更多内存。

3. Enum列的说明

   • enum列在内部是用整型来储存的
   • enum列与enum列相关联速度最快
   • enum列比(var)char 的弱势—在碰到与char关联时，要转化，要花时间。
     优势在于，当char非常长时，enum依然是整型固定长度。当查询的数据量越大时，enum的优势越明显。但有时也这样用—–就是在数据量特别大时,可以节省IO。

create table t2 (
id int,
gender enum('man','woman'),
key(gender)
)engine InnoDB charset utf8;

create table t3 (
id int,
gender char(5) not null default '',
key(gender)
)engine InnoDB charset utf8;

insert into t2 select id,if(id%2,'man','woman') from dict limit 10000;
insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;

mysql> select gender+1 from t2 where id = 1;
+----------+
| gender+1 |
+----------+
|        2 |
+----------+
1 row in set

mysql>