专栏首页KEN DO EVERTHING深入学习MySQL 03 Schema与数据类型优化

深入学习MySQL 03 Schema与数据类型优化

Schema是什么鬼

schema就是数据库对象的集合,这个集合包含了各种对象如:表、视图、存储过程、索引等。为了区分不同的集合,就需要给不同的集合起不同的名字,默认情况下一个用户对应一个集合,用户的schema名等于用户名,并作为该用户缺省schema。所以schema集合看上去像用户名。

如果把database看作是一个仓库,仓库很多房间(schema),一个schema代表一个房间,table可以看作是每个房间中的储物柜,user是每个schema的主人,有操作数据库中每个房间的权利,就是说每个数据库映射的user有每个schema(房间)的钥匙。

选择优化的数据类型

选择正确的数据类型对于获得高性能至关重要,一下3个原则能有助于更好的选择。

1.更小的通常更好 更小的数据类型通常更快,因为它们占用更少的磁盘,内存,CPU。

2.简单就好 简单数据类型的操作通常需要更少的CPU周期。例如:整型比字符操作代价更低,因为字符集和校对规则使字符比较比整型比较更复杂。

3.尽量避免NULL 通常情况下最好制定列为NOT NULL,除非真的需要存储NULL值

可为NULL列的缺点:

  • 使得索引,索引统计和值比较都更复杂
  • 使用更多的存储空间,在MySQL中也需要特殊处理

数据类型

整数

存储整数可以使用这几种:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64为存储空间。

整数类型可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。例如:TINYINT UNSIGNED可以存储的范围是0~255,而TINYINT的存储范围是-128 ~ 127

有符号和无符号类型使用相同的存储空间,且具有相同的性,因此根据实际情况选取即可

实数:带小数的数

3种数据类型可以存储实数:float,double,decimal

float和double存储的数据有可能不准确,decimal可存储精确小数,存储财务数据或经度要求高时使用decimal

float和double不准确的原因:mysql中float和double分别分配了32位、64位的存储空间,当float、double类型的数据转换为二进制时,第32位/64位之后的数都会被截断,从而造成数据有可能不准确

decimal的存储需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL。在数据量比较大的时候,可以考虑使用BIGINT代替DECIMAL,将对应的值扩大N倍。

字符串

char:

  • 长度范围:0~255
  • 存储:固定长度,存储数据未达到指定长度时,右边填充空格以达到指定的长度;当查询CHAR值时,尾部的空格被删除掉。
  • 优点:效率高

varchar:

  • 长度范围:0~65535
  • 存储:可变长度,保存时只保存需要的字符数,另加一个字节来记录长度(如果列声明的长度超过255,则使用两个字节);存储数据未达到指定长度时不进行填充;当值保存和检索时尾部的空格仍保留
  • 优点:节省空间

需要分情况来决定: 当确定字符串为定长、数据变更频繁、数据检索需求少时,使用char; 当不确定字符串长度、对数据的变更少、查询频繁时,使用varchar。

大数据

blob:

  • 数据类型:tinyblob、smallblob、blob、mediumblob、longblob
  • 存储:采用二进制方式存储,无排序规则和字符集

text:

  • 数据类型:tinytext、smalltext、text、mediumtext、longtext
  • 存储:采用字符方式存储,有排序规则和字符集

MySQL会把每个blob和text当做独立的对象处理,存储引擎存储时会做特殊处理,当值太大,innoDB使用专门的外部存储区域进行存储,行内存储指针,然后在外部存储实际的值;

mysql对他们的列排序:只对每列前max_sort_length字节排序,且不能将列全部长度的字符串进行索引

日期和时间

datetime:

  • 范围:1001年~9999年
  • 存储:8个字节存储,以YYYYMMDDHHMMSS的格式存储,与时区无关

timestamp:

  • 范围:1970年~2038年
  • 存储:4个字节存储,存储格式与UNIX时间戳相同,与时区有关

1.通常尽量使用timestamp,因为它的空间效率高

2.可以使用BIGINT类型存储微秒级别的时间戳

选择标识符(identifier,主键)
  • 整数类型通常是标识列最好的选择,因为它们很快并且可以使用AUTO_INCREMENT
  • 应该避免使用字符串类型作为标识列,因为它们很消耗空间,并且通常比数字类型慢
  • 对于完全"随机"的字符串也需要多加注意。例如:MD5(),SHAI()或者UUID()产生的字符串。这些函数生成的新值也任意分布在很大空间内,这会导致INSERT和一些SELECT语句很缓慢
  • 要确保在所有关联表中的外键与主键使用同样的类型。用不同数据类型可能导致性能问题或在比较操作时隐式类型转换也可能导致很难发现错误。
另外

IP地址实际是32位无符号整数,MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。(inet_ntoa(3507806248) --> 209.20.224.40 )

范式和反范式

通常建议使用范式化设计,因为范式化通常会使得执行操作更快。但这并不是绝对的,范式化也是有缺点的,通常需要关联查询,不仅代价昂贵,也可能使一些索引策略无效。

所以,我们有时需要混同范式化和反范式化,比如一个更新频率低的字段可以冗余在别的表中,避免关联查询

缓存表和汇总表

  • 缓存表:存储那些可以比较容易的从schema其他表获取(但每次获取速度缓慢)数据的表
  • 汇总表:保存的是使用GROUP BY语句聚合数据的表。实时计算统计值是很昂贵的操作。

在使用缓存表和汇总表时,必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序,但是定期重建并不只是节省资源,可以保持表不会有很多碎片,以及完全顺序组织的索引。

总结

  • 使用小而简单的合适数据类型
  • 尽量避免使用NULL值
  • 关联条件中使用的列尽量使用相同的数据类型
  • 尽量使用整型做主键
  • 切勿过度范式化,范式化与反范式化混用有时是最好的选择

参考: 《高性能MySql》 https://www.cnblogs.com/csniper/p/5509620.html http://blog.leanote.com/post/weibo-007/mysql_float_double_decimal https://www.cnblogs.com/Jtianlin/p/5143873.html

本文分享自微信公众号 - java从心(javaFollowHeart),作者:a丶ken

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-06-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • HashMap在JDK7和JDK8中的区别

    在[深入浅出集合Map]中,已讲述了HashMap在jdk7中实现,在此就不再细说了

    KEN DO EVERTHING
  • 【从0到1学算法】 数组和链表

    今天讲最基本的数据结构,数组和链表。如果你已经滚瓜烂熟,可以跳过本文或选择查缺补漏。

    KEN DO EVERTHING
  • 推荐几个高质量图片网站,再也不怕没图装X了

    Unsplash是一个免费高质量照片的网站,照片都是真实的摄影,照片分辨率也很大,全是生活中的景象作品,清新的生活气息图片可以作为桌面壁纸,也可以应用于文章配图...

    KEN DO EVERTHING
  • 超融合超越企业传统存储绕不开的六个问题

     在虚拟化及云计算技术大规模应用于企业数据中心的科技浪潮中,存储性能无疑是决定企业核心应用是否能被虚拟化、云化的关键考量指标之一。传统的做法是升级存储设备,从低...

    孙杰
  • 大数据小视角1:从行存储到RCFile

    数据的布局结构深刻的影响着数据处理的效率与性能,在底层的存储系统之中如何组织数据。如何对数据进行布局会直接影响数据查询引擎的设计与实现,并且也影响着存储空间的利...

    HappenLee
  • 区块链加上云存储,能玩出什么花样?

    一夜之间硬盘会坏掉,网站也会倒闭,难道就没有一个数据安全存储的地方吗?当然是有的! 当云存储首次出现时,被誉为革命性的,即使在今天的技术革命中,它仍然发挥着作用...

    企鹅号小编
  • MySQL性能优化(一)-- 存储引擎和三范式

    存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以...

    码农小胖哥
  • 2018年企业存储的7大趋势

    今天的企业比以往任何时候都产生和存储海量的数据,且这样的趋势丝毫没有放缓的迹象。大数据、物联网和分析的崛起促进了数据指数级的增长,这一增长趋势正在推动组织扩大其...

    SDNLAB
  • sql server存储过程

    sofu456
  • 听说Mysql你很豪横?-------------深入解析mysql数据库中的事务!

    2、 确保表中的每列都和主键相关(不然东一句西一句就乱了) 每张表中只有一个主键 建立在第一范式之上的,一个表中只能保存一种数据 不可以把多种数据保存在同...

    不吃小白菜

扫码关注云+社区

领取腾讯云代金券