schema与数据类型优化（一）

MySQL数据类型的优化

1.更小的通常更好

应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型
案例：设计两张表，设计不同的数据类型，查看表的容量。

2.简单就好

简单数据类型的操作通常需要更少的CPU周期，例如，
（1）、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，
（2）、使用mysql自建类型而不是字符串来存储日期和时间
（3）、用整型存储IP地址
**案例：**创建两张相同的表，改变日期的数据类型（一个字符串一个时间类型），查看SQL语句执行的速度。

**

3.尽量避免使用null**

(1)如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下null的列改为not null带来的性能提升比较小，所有没有必要将所有的表的schema（schema就是数据库对象的集合,所谓的数据库对象也就是常说的表,索引,视图,存储过程等）进行修改，但是应该尽量避免设计成可为null的列。
（2）含NULL复合索引无效.
（3）可为NULL的列会使用更多的存储空间，在Mysql中也需要特殊处理。
（4）当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引
（5）NULL值到非NULL值的更新无法做到原地更新，更容易发生索引分裂，从而影响性能。
（6）not in、!=查询在有NULL值的情况下返回永远为空结果，查询容易出错。
（7）innodb的特性所决定，非not null的值，需要额外的在字段存储，同时也会增加IO和存储的开销
例：
首先新建环境, sql语句如下

create table user(
id int primary key,
name varchar(10) not null,
nickname varchar(10) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

添加索引：
alter table user add index idx_user_name(name);
alter table nulltesttable add index idx_user_nickname(nickname);
查看执行：
explain select * from user where name=‘name’; // explain1
explain select * from user where nickname=‘name’; // explain2

从sql 执行可以看出, explain1中 key_len = 32, explain2中 key_len = 33
explain1的32 由来: 10(字段长度) * 3(utf8字符编码占用长度) + 2(varchar标识为变长占用长度)
explain2的32 由来: 10(字段长度) * 3(utf8字符编码占用长度) + 2(varchar标识为变长占用长度) + 1(null标识位占用长度)

添加数据：
insert into user(id,name,nickname) values(1,‘张三’,null);
insert into user(id,name,nickname) values(2,‘李四’,‘lisi’);

两个字符串拼接, 如果包含null值, 则返回结果为null.**
select concat(name,nickname) from user where id = 1; – out: null
select concat(name,nickname) from user where id = 2; – out: twothree

如果字段允许null值, 且这个字段被索引. 如下的查询可能会返回不正确的结果
select * from user where nickname <> ‘lisi’ – out: null
select count( nickname ) from user – out: 1

通常把可为NULL的列改为NOT NULL 带来的性能提升比较小，所以（调优时）没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。

第三个例子是仿照别人的地址：https://zhuxianfei.com/database/mysql/44904.html 不喜欢勿喷

4、实际细则

1、整数类型可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小数据类型。
2、浮点数&定点数

注意：定点数以字符串形式存储，对精度要求高时使用decimal较好；尽量避免对浮点数进行减法和比较运算。

3、字符串类型

1、varchar（varchar根据实际内容长度保存数据）
(1)、使用最小的符合需求的长度。
(2)、varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。
(3)、varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。
(4)、varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时，都会导致锁表。

应用场景：
1、存储长度波动较大的数据，如：文章，有的会很短有的会很长。
2、字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度
3、适合保存多字节字符，如：汉字，特殊字符等

2.char（char是固定长度的字符串）
(1)、最大长度：255
(2)、会自动删除末尾的空格
(3)、检索效率、写效率 会比varchar高，以空间换时间
(4)、如果插入数据的长度小于char的固定长度时，则用空格填充。

应用场景：
1、存储长度波动不大的数据，如：md5摘要
2、存储短字符串、经常更新的字符串

3、BLOB 和 TEXT
MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。
两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。

4、时间类型：

1.datetime
（1）占用8个字节
（2）与时区无关，数据库底层时区配置，对datetime无效
（3）可保存到毫秒
（4）可保存时间范围大
（5）不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性

2.timestamp
（1）占用4个字节
（2）时间范围：1970-01-01到2038-01-19
（3）精确到秒
（4）采用整形存储
（5）依赖数据库设置的时区
（6）自动更新timestamp列的值

3.date
（1）占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
（2）使用date类型还可以利用日期时间函数进行日期之间的计算
（3）date类型用于保存1000-01-01到9999-12-31之间的日期

注意：其他时间类型作为了解，开发中这三种类型最常用。

5、使用枚举代替字符串类型

有时可以使用枚举类代替常用的字符串类型，mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或两个字节中，mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查找表。

例：
create table enum_test(
e enum(‘fish’,‘apple’,‘dog’) not null
);
插入数据：
insert into enum_test(e) values(‘fish’),(‘dog’),(‘apple’);

select e+0 from enum_test;

6、特殊类型数据
人们经常使用varchar(15)来存储ip地址，然而，它的本质是32位无符号整数不是字符串，可以使用INET_ATON()和INET_NTOA函数在这两种表示方法之间转换
案例：
select inet_aton(‘1.1.1.1’)
select inet_ntoa(16843009)