【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调用，观察结果。

计数器表：

在统计场景下计数器表很好用，可以加快统计效率，但是需要注意高并发写入下锁的等待问题

可以通过异步处理解决，当然更推崇使用更多的行，然后每次随机选择一行去进行技术增长，最后统计所有行的信息。

MySQL 数据结构

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。