【MySQL调优】Schema与数据类型优化

schema优化就是指逻辑设计
选择合适的数据类型：
1. 更小的通常更好
2. 简单就好
3. 尽量避免NULL（null字段将会多占用1个字节来存储是否为null）

基本数据类型

整数类型：
TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
分别占用
8，16，24，32，64位的存储空间
还可以区分是否只能为正数

实数类型：
浮点类型：float和double，分别占用4个字节和8个字节
应该尽量只在对小数进行精确计算的时候才能使用DECIMAL，当然有可能的时候还是建议使用倍数然后存储在正整数。

字符串类型：
VARCHAR与CHAR
VARCHAR会有一位或者两位 255上下，是变长的
CHAR是定长的
会有自动截断的场景

BLOB和TEXT：
不建议在数据表中直接使用，分别属于BLOB和TEXT两个大家族
BLOB以二进制形式存储，而TEXT有自己的字符集和排序规则
针对他们的排序，可以指定使用多长的前置字符串 max_sort_length

ENUM枚举：
ENUM有自己的内部文件排序规则，将不会按照单纯的字符串顺序进行排序
在多表关联的时候，enum实际上自己实现了整数类型向字符串（或者其他枚举适用类型）的映射
我们都知道，在MySQL服务中，针对整数的比较处理速度要高于字符串，所以在实际的设计当中也可以尽可能使用整数作为主键或者关联键，提升数据库查询速度
同时，ENUM关联ENUM的速度也是比VARCHAR关联VARCHAR快的

日期和时间：
MySQL最多支持到秒级数据
TIMESTAMP与DATETIME
datetime范围更广，默认1001到9999年，与时区无关，采用8个字节存储
timestamp保存了从1970年1月1日以来的时间，最多只能到2038年的某天，所以在使用过程中应当注意边界条件，timestamp可以自动实现时区的转换，在多时区场景下访问timestamp和datetime将会得到很不一样的结果，所以在应用过程中尽量选择一种单一的时间类型，同时，timestamp某人的类型是not null
timestamp具有更高的时间效率，只占用4个字节。

位数据类型：
BIT:
最大长度为64位，通过BIT(N)来指定N位的位存储
MySQL把BIT当成字符串来处理而非数字，但是在数字上下文场景中又会将其转换为数字，所以在使用的时候需要务必小心，考虑清楚上下文条件，在看能不能得到正确的结果
在整数列上进行按位操作可以节约大量的空间，比如访问控制ACL，但是带来的后果是数据库设计的可读性变差，需要大量的文档来指导用户某个位0或1的真正含义

标识符的选择

所谓标识列，英文是identifiler column，在MySQL中多为主键或关联键，外键。
选择合适的标识符，需要考虑MySQL服务器对其的处理情况，同时也要考虑其在不同的存储引擎中的存储情况。
整数类型最好，因为可以设置成AUTO_INCREMENT，保证了有序性和磁盘放置的紧密型
ENUM和SET不推荐使用，因为扩展会带来很多麻烦的问题，他们更适合存储诸如性别，产品类型，逻辑状态等固定不变的信息
字符串类型应当尽可能避免，因为它会占用大量的空间，尤其是针对InnoDB这种聚簇索引类型
使用MD5()、SHA1()或者UUID()可以获得标识符不错的随机性，但是会导致插入速度很慢，以及批量的查询也可能会变得很慢，因为他们在物理逻辑空间上并不是有序排放的
针对IPV4地址，推荐使用MySQL自建函数INET_ATON()和INET_NTOA()来将其保存为无符号整数类型。

Schema设计中应当避开的陷阱

太多的列
太多的关联关系
全能的枚举
变相的枚举
Not Invent Here的NULL（就是不要害怕得完全不使用null，有时候null的场景会比一个魔鬼数字好很多）

范式与反范式

老生常谈的问题，范式使得数据更加精简，占用空间更小，维护起来更容易，但是查询时可能会存在很多的关联关系
反范式场景下通过提供数据冗余，牺牲了部分数据维护的有效程度来使得查询速度更快
二者各有优缺点，这里我的建议是，融汇变通的使用范式与反范式，在存储空间和查询效率，数据维护效率之间进行权衡

缓存表与汇总表

缓存表中的数据可能是从某个表中查出来的部分数据，使用缓存表可以加快数据处理能力，同时不至于锁死主表，高并发场景下可以牺牲部分时效性来换取更高的性能
而汇总表中的数据大多是group by或者sum之后的结果，这个数据在数据库中是不存在的，使用汇总表可以预先完成一部分数据计算逻辑，减轻查询时服务器的负担
这些表与普通的OLTP操作表存在本质上的区别，应用场景也不尽相同
物理化视图：
物理化视图指的是具备CDC功能，分析mysql日志，直接记录数据变更情况并生成相同结果的一个中间状态。
Flexviews可以很好的为我们提供类似的功能，具体的思路是：
写出一个SELECT语句从已经存在的数据表中获取目标数据，使用Flexviews生成SQL语句向Flexviews的API调用，观察结果。
计数器表：
在统计场景下计数器表很好用，可以加快统计效率，但是需要注意高并发写入下锁的等待问题
可以通过异步处理解决，当然更推崇使用更多的行，然后每次随机选择一行去进行技术增长，最后统计所有行的信息。

文章来源: zclhit.blog.csdn.net，作者：zclhit_，版权归原作者所有，如需转载，请联系作者。

原文链接：zclhit.blog.csdn.net/article/details/104649207