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

举报
ChillRay 发表于 2020/12/30 00:19:04 2020/12/30
【摘要】 schema优化就是指逻辑设计 选择合适的数据类型: 1. 更小的通常更好 2. 简单就好 3. 尽量避免NULL(null字段将会多占用1个字节来存储是否为null) 基本数据类型 整数类型: TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 分别占用 8,16,24,32,64位的存储空间 还可以区分是否只能为正数 实数类型: 浮点类...

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

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。