表的优化

　　上班快一个月了，工作还是比较充实的。虽然是个小公司，但是每周开两次会，一次分享，老板还是比较重视团队的培养，和团队技术的进步。当然了，开会时间并不长，只是说说近期的工作进度之类的，不拖泥带水！以前，学习的时候，总是觉得很多环境情况遇不到，因此学习的时候总是有意的跳过一些内容，就比如说数据库的优化。总觉得自己遇到的项目应该都不大，毕竟圈子限制。但是，来到现在的公司，公司虽然只有两年多，但是已经遇到了类似的问题。比如，mysql 的主从，mycat 的读写分离。公司虽然用的都是阿里云，但是这种技术还是需要同事们都掌握的。随着业务规模的扩大，这方面的问题可能将来还会遇到。

　　所以说知识这东西，不是用不到，只是不知道什么时候能用到，因此不要给自己人为的划个圈圈不往出走。人生很多时候其实就是要迈出那一步，走走就会不一样。

 

一、建表原则
1、定长与变长分离
如：
id int     占 4 个字节
char(4) 占 4 个字节长度，也是定长
time
即每一个单元值占用的字节是固定的。
查10001行，因为行固定，所以可以跳过10000行
这种表是 fixed，即长度是固定的

而 varchar, text, blob 这种变长字段，适合单放一张表，用主键与核心表关联起来。

 

2、常用字段和不常用字段要分离
需要结合具体的业务来分析，分析字段的查询场景，查询频度低调字段，单拆出来。

 

3、在1对多，需要关联统计的字段上添加冗余字段
反范式设计
计算机的优化中，不外乎是用空间换时间，用时间换空间

商品类型表（商品类型id，商品类型名称）
商品明细表（商品id,商品名称,所属商品类型id）

为了获得每类商品有多少中商品， 就需要关联查询， 给“商品类型表”增加一个“数量”字段， 可以提交统计查询的速度。这样做是用“空间换时间”。

 

二、列类型选择
列选择原则
1、字段类型优先级
整型 > date time > enum char > varchar > blob text
列的特点分析
整型：定长，没有国家/地区之分，没有字符集的差异
比如：tinyint 1, 2, 3, 4, 5 <--> char(1) a, b, c,d
从空间上，都是占用1个字节，但是 order by 排序，前者快
原因：后者需要考虑字符集与校对集（就是排序规则）

比如：
对a b c d 进行排序
自然理解：
a b c d
从计算机的ascii来讲是：
b d a c

time 定长 运算快 节省空间 考虑时区写 sql 不方便 where > '2018-10-4';
enum 枚举类型 内部仍然用整型存储 能起约束的作用，但与char联查时，内部要经历串与值的转化。
enum('男', '女')
char 定长 要考虑字符集和（排序）校对集
varchar 不定长 要考虑字符集的转换与排序时的校对集 速度慢
text / blob 无法使用内存临时表（排序等操作只能在磁盘上进行）

以性别为例：
char(1) 3个字长字节 char 表示一个字符，如果使用 utf8 类型，那么占用3个字节，因为 utf8 占用 1～3 个字节
enum('男','女') 内部转换成数字来存，多了一个转换过程
tinyint() 0 1 2 定长 1 个字节

2、够用就行，不要慷慨(如smallint, varchr(n))
原因：大的字段浪费内存，影响速度

以年龄为例 tinyint unsigned not null ，可以存储 255 岁，足够。用 int 浪费 3 个字节。
以varchar(10)，varchar(300)存储的内容相同，用多少给多少，但在表联查时varchar(300)要花更多内存.

3、尽量避免用null
原因：null 不利于索引，要用特殊的字节来标注。
在磁盘上占据的空间其实更大(mysql 5.5 已对 null 做改进，但是查询仍是不便)

实验：
可以建立 2 张字段相同的表，一个允许为null，一个不允许为null，各加入1万条数据。查看索引文件的大小。可以发现，为null的索引要大些。（mysql 5.5里，关于null已经做了优化，大小区别已不明显）。
另外，null也不便于查询。

where 列名 = null 或 where 列名 != null 都查不到值
where 列名 is null 或 where 列名 is not null 可以查询

 1 create table dictnn(
 2 id int,
 3 word varchar(14) not null default '',
 4 key(word)
 5 )engine myisam charset utf8;
 6 
 7 create table dictyn(
 8 id int,
 9 word varchar(14),
10 key(word)
11 )engine myisam charset utf8;
12 
13 alter table dictnn disable keys;
14 alert table dictyn disable keys;
15 
16 insert into dictnn select id, if(id%2, word, '') from dict limit 10000;
17 insert into dictyn select id, if(id%2, word, null) from dict limit 10000;
18 
19 alert table dictnn enable keys;
20 alert table dictyn enable keys;

 

enum列的说明：
1、enum 列在内部是用整型来存储的
2、enum 列与 enum 列相关联速度最快
3、enum 列比 (var)char 的弱势——在碰到与char关联时，要转化，要花时间
4、优势在于当char非常长时，enum依然是整型固定长度
5、enum与char/varchar关联，因为要转化，速度要比enum->enum,char->char要慢，但有时也这样用——就是在数据量特别大时，可以节省i/o。

 

　　学习的时候的一些记录，也许很多东西不准确，或者对于专业的 dba 或有经验的开发人员而言这都不算什么，但是至少这些对我而言以前并不是很在意，虽然多少了解一些，可是从来没有整理，也从来没有实践。记录在这里，以备后用吧。