MySQL中的数据类型和schema优化

osc_yizwdm15 2020-11-10 13:38:59
java Python Mysql InnoDB myisam


Python实战社群

Java实战社群

长按识别下方二维码,按需求添加

扫码关注添加客服

进Python社群▲

扫码关注添加客服

进Java社群

作者丨草捏子

来源丨草捏子(ID:chaycao)

最近在学习MySQL优化方面的知识。本文就数据类型和schema方面的优化进行介绍。

1. 选择优化的数据类型

MySQL支持的数据类型有很多,而如何选择出正确的数据类型,对于性能是至关重要的。以下几个原则能够帮助确定数据类型:

  1. 更小的通常更好

    应尽可能使用可以正确存储数据的最小数据类型,够用就好。这样将占用更少的磁盘、内存和缓存,而在处理时也会耗时更少。

  2. 简单就好

    当两种数据类型都能胜任一个字段的存储工作时,选择简单的那一方,往往是最好的选择。例如整型和字符串,由于整型的操作代价要小于字符,所以当在两者之间选择时,选择整型通常能够获得更好的性能。

  3. 尽量避免NULL

    当列可为NULL时,对于MySQL来说,在索引和值比较等方面需要做更多的工作,虽然对性能的影响不是很大,但也应尽量避免设计为可为NULL。

除了以上原则,在选择数据类型时,需遵循的步骤:首先确定合适的大类型,例如数据、字符串、时间等;然后再选择具体的类型。下面将讨论大类型下的一些具体类型,首先是数字,有两种类型:整数和实数。

1.1 整数类型

整数类型和所占用的空间如下:

整数类型 空间大小(bit)
TINYINT 8
SMALLINT 16
MEDIUMINT 24
INT 32
BIGINT 64

整数类型所能存储的范围和空间大小有关:-2^(N-1)至2^(N-1)-1,其中N为空间大小的位数。

整数类型具有UNSIGNED的可选属性,当声明时,表示不允许负数,则存储范围变为:0至2^(N)-1,扩大了一倍。

在MySQL中,还可以为整数类型指定宽度,例如INT(1),但这样的意义并不大,并不会限制值的合法范围,仍能存储-2^31至2^31-1的值,所影响的是与MySQL的交互工具显示字符的个数。

1.2 实数类型

实数类型的对比如下:

实数类型 空间大小(Byte) 取值范围 计算精度
FLOAT 4 负数:-3.4E+38~-1.17E-38;非负数:0、1.17E-38~3.4E+38 近似计算
DOUBLE 8 负数:-1.79E+308~-2.22E-308;非负数:0、2.22E-308~1.79E+308 近似计算
DECIMAL 与精度有关 同DOUBLE 精确计算

从上面可以看出,FLOAT和DOUBLE都有固定的空间大小,但同时由于是使用标准的浮点运算,所以只能近似计算。而DECIMAL则可以实现精确计算,与此同时占用的空间会相较更大,所耗费的计算开销也更多。

DECIMAL所占空间大小与指定的精度有关,例如DECIMAL(M,D):

  • M为整个数字的最大长度,取值范围为[1, 65],默认值为10;

  • D为小数点后的长度,取值范围为[0, 30],且D <= M,默认值为0。

MySQL在存储DECIMAL类型时会作为二进制字符串存储,每4个字节存9个数字,当不足9位时,数字的占用空间如下:

数字个数 占用空间(Byte)
1、2 1
3、4 2
5、6 3
7、8 4

小数点前后将分别存储,同时小数点也要占1个字节。下面举两个计算的例子:

  1. DECIMAL(18, 9):整数部分长度为9,占用4个字节。小数部分长度为9,占用4个字节。同时加上小数点1个字节,则总共占用9个字节。

  2. DECIMAL(20, 9):整数部分长度为14,占用7(4+3)个字节。小数部分长度为9,占用4个字节。同时加上小数点1个字节,则总共占用12个字节。

可以看出DECIMAL的空间占用还是很大的,因此只有当需要对小数进行精确计算时,才需要使用DECIMAL。除此之外,我们还可以使用BIGINT代替DECIMAL,例如需要保证小数点后5位的计算,可以将值乘上10的5次方后作为BIGINT存储,这样能同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

1.3 字符串类型

最常用的字符串类型当属VARCHAR和CHAR。VARCHAR作为可变长字符串,会使用1或2个额外字节记录字符串的长度,当最大长度未超过255时,只需1个字节记录长度,超过255,则需2个字节。VARCHAR的适用场景

  1. 最大长度比平均长度大很多;

  2. 列的更新少,避免碎片;

  3. 使用复杂的字符集,如UTF-8,每个字符能使用不同的字节存储。

CHAR则为定长字符串,根据定义的字符串长度分配足够的空间,适用场景

  1. 长度短;

  2. 长度相近,例如MD5;

  3. 经常更新。

除了VARCHAR和CHAR,针对存储大字符串,可以使用BLOB和TEXT类型。BLOB和TEXT的区别在于,BLOB是以二进制方式存储,而TEXT是以字符方式存储。这也导致,BLOB类型的数据没有字符集的概念,无法按字符排序,而TEXT类型则有字符集的概念,可以按字符排序。两者的使用场景,也由存储格式决定了,当存储二进制数据时,例如图片,应使用BLOB,而存储文本时,例如文章,则应使用TEXT类型。

1.4 日期和时间类型

MySQL中所能存储的最小时间粒度为秒,常用的日期类型有DATETIME和TIMESTAMP。

类型 存储内容 空间大小(Byte) 时区概念
DATETIME 格式为YYYYMMDDHHMMSS的整数 8
TIMESTAMP 从1970年1月1日零点以来的秒数 4

TIMESTAMP显示的值将依赖于时区,意味在不同时区查询到的值将不一样。除了以上列出的不同,TIMESTAMP还具有一个特殊属性,在插入和更新时,如果没有指定第一个TIMESTAMP列的值,将会设置这个列的值为当前时间。

我们在开发过程中,应尽量使用TIMESTAMP,主要是因为其空间大小仅需DATETIME的一半,空间效率更高。

如果我们想存储的日期和时间精确到秒之后,怎么办?由于MySQL并未提供,所以我们可以使用BIGINT存储微妙级别的时间戳,或者使用DOUBLE存储秒之后的小数部分。

1.5 选择标识符

通常来说整数是标识符的最好选择,主要是因为其简单,计算快,且可使用AUTO_INCREMENT。

2. 范式和反范式

简单来说,范式就是一张数据表的表结构所符合的某种设计标准的级别。第一范式,属性不可分割,现在的RDBMS系统建成的表都是符合第一范式的。而第二范式,则是消除非主属性对码(可以理解为主键)的部分依赖。第三范式消除非主属性对码的传递依赖。具体的介绍,可以读读知乎上的这个回答(https://www.zhihu.com/question/24696366/answer/29189700)

严格范式化的数据库中,每个事实数据会出现且只出现一次,不会出现数据冗余,这样所能带能带来的好处有:

  1. 更新操作更快;

  2. 修改更少的数据;

  3. 表更小,更好地放内存中,执行操作更快;

  4. 更少需要DISTINCT或GROUP BY。

但也由于数据分散存在各张表中,查询时需要对表进行关联。而反范式的优点则是不用进行关联,将数据冗余存储。

在实际应用中,不会出现完全的范式化或完全的反范式化,时常需要混用范式和反范式,使用部分范式化的schema,往往是最好的选择。关于数据库设计,在网上看到这样一段话,大家可以感受下。

数据库设计应该分为三个境界:

第一境界:刚入门数据库设计,范式的重要性还未深刻理解。这时候出现的反范式设计,一般会出问题。

第二境界:随着遇到问题解决问题,渐渐了解到范式的真正好处,从而能快速设计出低冗余、高效率的数据库。

第三境界:再经过N年的锻炼,是一定会发觉范式的局限性的。此时再去打破范式,设计更合理的反范式部分。

范式就像武侠里面的招数,初学者妄想不按招数来,只能死的很难堪。毕竟招数都是高手总结归纳的精华。而随着武功提高,招数熟练之后,必然是发现招数的局限性,要么忘掉招数,要么自创招数。

只要努力,加上多熬几年,总能达到第二个境界,总会觉得范式是经典。此时能不过分依赖范式,快速突破范式局限性的人,自然是高手。

4. 缓存表和汇总表

除了上述说到的反范式,在表中存储冗余数据,我们还可以创建一张完全独立的汇总表或缓存表,来满足检索的需要。

缓存表,指的是存储可以从schema其他表中获取数据的表,也就是逻辑上冗余的数据。而汇总表,则指的是存储使用GROUP BY等语句聚合数据,计算出的不冗余的数据。

缓存表,可用于优化搜索和检索查询语句,这里可以使用的技巧有对缓存表使用不同的存储引擎,例如主表使用InnoDB,而缓存表则可使用MyISAM,获得更小的索引占用空间。甚至可以将缓存表放到专门的搜索系统中,例如Lucene。

汇总表,则是为了避免实时计算统计值所带来的高昂代价,代价来自两方面,一是需要扫描表中的大部分数据,二是建立特定的索引,会对UPDATE操作有影响。例如,查询微信过去24小时的朋友圈数量,则可固定每1小时扫描全表,统计后写一条记录到汇总表,当查询时,只需查询汇总表上最新的24条记录,而不必每次查询时都去扫描全表进行统计。

在使用缓存表和汇总表时,必须决定是实时维护数据还是定期重建,这取决于我们的需求。定期重建相比实时维护,能节省更多的资源,表的碎片更少。而在重建时,我们仍需保证数据在操作时可用,需要通过“影子表”来实现。在真实表后创建一张影子表,当填充好数据后,通过原子的重命名操作来切换影子表和原表。

5. 加快ALTER TABLE操作的速度

当MySQL在执行ALTER TABLE操作时,往往是新建一张表,然后把数据从旧表查出并插入到新表中,再删除旧表,如果表很大,这样需要花费很长时间,且会导致MySQL的服务中断。为了避免服务中断,通常可以使用两种技巧

  1. 在一台不提供服务的机器上执行ALTER TABLE操作,然后再与提供服务的主库进行切换;

  2. “影子拷贝”,建立一张与原表无关的新表,在数据迁移完成后,通过重命名操作进行切换。

但也不是所有的ALTER TABLE操作会引起表重建,例如在修改字段的默认值时,使用MODIFY COLUMN会进行表重建,而使用ALTER COLUMN则不会进行表重建,操作速度很快。这是因为ALTER COLUMN在修改默认值时,会直接修改了存在表的.frm文件(存储字段的默认值),而并未重建表。

参考

  1. 《高性能MySQL》

  2. MySQL DECIMAL 数据类型

(https://my.oschina.net/u/559356/blog/3057960)

程序员专栏 扫码关注填加客服 长按识别下方二维码进群

近期精彩内容推荐:  

 女友认为年薪50万是平均水平,怎么办?

 全国最大直男论坛的性感女神翻车了

 IntelliJ IDEA超全优化设置,效率杠杠的!

 非常有用的 Python 技巧


在看点这里好文分享给更多人↓↓

版权声明
本文为[osc_yizwdm15]所创,转载请带上原文链接,感谢
https://my.oschina.net/u/4370838/blog/4711139

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云