MySQL调优-数据库表设计

一、表数据类型优化

1.使用更小的数据类型通常更好

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

2.使用简单的数据类型通常更好

简单数据类型的操作通常需要更少的CPU周期，例如：
1、整型比字符操作代价更低，根据字符集和校对规则相比之下，字符比较比整型比较更复杂，
2、使用mysql自建类型而不是字符串来存储日期和时间 
3、用整型存储IP地址 

3.尽量避免使用null

可以为null的列对于Mysql来说，是很难优化的，因为可以为null的列让索引、索引统计和值比较更加复杂
所以尽量避免设计出可以为null的列

4.详细细则

1. 整型类型

可以使用的整数类型，TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT
分别占用8、 16 、24 、32 、 64位存储空间，尽量使用满足需求的最小数据类型

2. 字符和字符串类型

• char
  • 根据定义的大小占用相同的存储空间
  • 最大长度为255
  • 自动删除末尾的空格
  • 检索效率和写效率会比varchar高，空间换时间
  • 使用场景：
    • 存储长度波动范围不大的数据，如md5
    • 存储定长数据，如身份证号，手机号
    • 存储较短字符，且经常更新的数据
• varchar
  • 根据实际保存的数据占用相同的空间，但是内存中占用和定义时同样大小的空间，如varcahr(10)和varchar(50)保存相同的内容，硬盘占用同样大小的空间，但是内存占用空间却是定义时的大小
  • 最大长度为65535
  • varchar(n) n <=255时，额外占用一个字节保存长度，n > 255，额外使用两个字节保存长度
  • 在mysql5.6之前变更长度，或者从255一下变更到255以上时，会导致锁表。
  • 使用场景：
    • 存储长度波动范围较大的数据，如：文章或者评论
    • 数据很少更新的场景，因为每次更新都需要计算并使用额外的空间保存数据的长度
    • 保存多字节字符，如：汉字，特殊字符

3. BLOB和text类型

• MySQL把BLOB和Text作为一个独立的对象来处理，两者都是为了保存较大数据而设计的数据类型，分别采用二进制和字符方式存储。

4.时间类型

• datetime
  • 占用8个字节
  • 精确到毫秒
  • 不依赖于MySQL底层设置的时区
  • 保存时间范围大
• timestamp
  • 占用4个字节
  • 使用整形存储，精确到秒
  • 时间范围是1970-01-01到2038-01-19
  • 依赖于MySQL底层设置的时区
  • 自动更新timestamp列的值
• date
  • 占用3个字节
  • 可以使用日期函数进行日期之间的计算
  • 可以保存范围为1000-01-01到9999-12-31之间的日期

5.枚举类型

• enum
  • 可以使枚举类型代替常用的字符串类型，MySQL存储枚举类型十分紧凑，会根据列表值得数据压缩到一个或者两个字节中，MySQL在内部会将每个值在列表的位置保存为整数，并且会在.frm中保存 "数字-字符串"的映射关系查找表
  • 案例：

create table enum_test(e enum('apple','orange','pig') not null);  
insert into enum_test(e) values('apple'),('orange'),('pig');  
select e+0 from enum_test;

6.特殊类型数据

有的人经常使用varchar(15)保存ip地址，然而他的本质是32位无符号整数，不是字符串，那我们可以使用INET_ATON()和INET_NTOA函数在这两种表示方法之间转换

二、合理使用范式和反范式

1.范式

• 优点
  • 合理使用范式，很少或不会产生重复数据
  • 范式化的数据更新通常比反范式更快
  • 范式化的数据较小，可以放在内存中，操作较快
• 缺点
  • 通常需要关联

2.反范式

• 优点
  • 所有的数据都在一张表中，避免关联
  • 可以设计有效的索引
• 缺点
  • 表冗余数据过多，删除或者更新表时，会导致有用的数据丢失

3.企业中通常很难严格遵守范式和反范式

• 从一个表冗余数据到另一个表，是排序的需要，减少不必要的io，通过空间换时间的方式优化，同时要保证数据的一致性
• 缓存衍生至。比如论坛中，该用户发了多少消息，可以通过查询该用户在论坛的消息数量。也可以在user表维护一个messagecnt字段，记录用户的发言次数，每次发言则更新该字段。
• 适当的拆分，当我们表中有Blob或者text较大数据的列时，且不经常使用，那么完全可以将其拆分到另一张表中，较少常用数据的空间，这样做的好处就是磁盘块保存的数据量大大增加，减少物理io次数，也提高了查询的效率

三、主键的选择

1.代理主键

• 无任何意义的数字序列，且与业务无关

2.自然主键

• 事务属性中唯一标识

3.推荐使用代理主键

• 与业务无关，维护容易
• 通用的键生成策略可以减少编码的数量，减少系统总体拥有成本

四、字符集的选择

字符集直接决定了数据在MySQL中的存储编码方式
由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异
所以通过使用合适的字符集，可以帮助我们尽可能减少数据量
进而减少IO操作次数

• 纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。
• 如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。
• MySQL的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量，进而降低 IO 操作次数并提高缓存命中率。

五、存储引擎的选择

	InnoDB	MyiSam
行锁	√	×
表锁	√	√
外键	√	×
事务	√	×
聚簇索引	聚簇	非聚簇
存储数据结构	frm、ibd	frm、myi、myd
记录条数	全表扫描	可以直接获取(不带任何条件，使用变量维护记录数)