MySQL 数据类型

MySQL 数据类型

MySQL 支持很多的数据类型,下面来看一下一些常见的数据类型

一、选择数据类型
1.整型类型

如果存储整数,可使用者几种整数类型
TYINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,分别使用8,16,24,32,64位存储空间,存储范围从 -2^(n-1) ~ 2^(n-1)-1,n是位数,当使用 UNSIGNED 时,表示不允许负数,可使正数上线提高一倍。

有符号和无符号类型使用相同的存储空间,并具有相同的性能

对于制定宽度的整数类型,INT(10),它不会限制指的合法范围,只限制显示字符的个数

2.实数类型

实数是带有小数的数字
DECIMAL 定点型 类型用于存储精度小数
DECIMAL(P , D)

  • P是表示有效数字数的精度。P范围为1〜65。
  • D是表示小数点后的位数。D的范围是0~30。MySQL要求D小于或等于(<=)P。
    FLOAT 单精度浮点数值 使用4个字节存储
    DOUBLE 双精度浮点数值 使用8个字节存储
    在这里插入图片描述

3.字符串类型
  • VARCHAR 可变长字符串 需要使用 1 或 2 个额外子节记录字符串的长度,MySQL 在存储盒检索时会保留末尾空格
  • CHAR 定长的 MySQL 会删除所有末尾空格 适合定长字段,如MD5码的密码,只存 Y 或 N 的值的字段

使用VARCHAR(5)和VARCHAR(200)存储 “abcd” 的空间开销是一样的吗?使用短的列有何优势?

两者的空间开销是一样的。但长列会消耗更多的内存,因为 MySQL 会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕,在利用磁盘临时表进行排序时也同样糟糕

BLOB 采用二进制存储 TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB
TEXT 采用字符方式存储 TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT
当 BLOB 和 TEXT 值太大时,InnoDB会使用专门的外部存储区来进行存储,此时每个值在行内需要 1~4 个字节存储一个指针,在排序时需要使用 SUSTRING(column,length)

使用枚举(ENUM)代替字符串
枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL 在存储枚举列时非常紧凑,会根据列表值的数量压缩到一个活两个字节中。MySQL 在内部会将每个值在列表中的位置保存位整数。枚举按内部存储的整数进行排序,可通过按照需要的顺序来定义枚举列。

在这里插入图片描述

4.日期和时间类型

DATETIME
TIMESTAMP 它和 UNIX 时间戳相同,可用FROM_NIXTIME()函数,把 Unix 时间戳转换成日期, UNIX_TIMESTAMP()函数把日期转换为 Unix 时间戳,它比 DATETIME 的空间效率更高

可以使用 BIGINT 类型存储微秒级别的时间戳,或使用 DOUOBLE 存储秒之后的小数部分

在这里插入图片描述

5.位数据类型

BIT 最大长度是 64 个位 可用CHAR(0)来代替
SET 它在 MySQL 中以一系列打包的位的集合来表示


在看完这些数据类型后,那该如何选择优化的数据类型呢?
下面几个简单的原则都有助于做出更好的选择

  • 更小的通常更好
    一般情况下,尽量使用可以正确存储数据的最小数据类型
  • 简单就好
    简单的数据类型的操作通常需要更少的 CPU 周期
  • 尽量避免 NULL
    因为 NULL 的列时的索引、索引统计和值比较都更复杂,当可为 NULL 的列被索引时,每个索引记录需要一个额外的字节

二、sschema设计中的注意点
  • 太多的列
    MySQL 的存储引擎 API 工作时需要在服务器和存储引擎之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容加码成各个列,从行缓冲将编码过的列转换成行数据结构的操作代价非常高,转换的代价依赖于列的数量,因此,不要使用宽表
  • 太多的关联
    MySQL 限制每个关联操作最多 61 张表,但“实体-属性-值”(EAV)需要许多自关联,可能会超过这个限制,太多关联情况下,解析和优化查询的代价也会成为 MySQL 的问题,单个查询最好在 12 个表以内做关联
  • 全能的枚举
    防止过度使用枚举,在枚举列表中增加一个新的记录时(如果不在队尾增加),就要做一次 ALTER TABLE 操作,这是一种阻塞操作
  • 变相的枚举
    枚举(ENUM)存储一组单个值,和集合(SET)存储一组单个或多个值,有时容易造成混乱
  • 非此发明(Not Invent Here)的 NULL
    避免使用 NULL,即使需要存储一个实际上的 “空值” 到表中,可以使用0,某个特殊值,或者空字符串作为代替

三、范式与反范式

在了解完数据类型后在设计表结构是,通过有完全范式化,完全反范式化,以及两者折中。在范式数据库中,每个事实数据会出现且只出现一次,在反范式数据库中,信息是冗余的。

先来讲一下范式和反范式的优缺点
范式的优点

  • 范式化的跟新操作通常比反范式化快
  • 范式化只需要修改更少的数据
  • 范式化的表通常更小,可放在内存里,执行操作更快
  • 很少多余的数据意味着检索列表数据时更少需要 DISTINCT 或 GROUP BY 语句
    范式缺点
  • 通常需要关联多表查询,这不但代价昂贵,也可能使一些索引策略失效
    反范式的优点就是关联少,但会出现字段的冗余

既然各有各有的优劣,哪如何选择最佳设计?
实际情况中两者经常要混合使用,这往往会导致需要更新多张表的情况。至于这个问题,就要考虑更新的频率和更新的时长,并和秩序 SELECT 的频率进行比较。


四、缓存表和汇总表

缓存表通常来存储那些可以比较简单地从表中获取(但每次获取的速度比较慢)数据的表
汇总表则保存的是使用 GROUP BY 语句聚合数据的表,通常用于数据统计

物化视图

在传统的汇总表中还是需要读取原表数据,每次读取还是会耗费大量时间,这是就可以使用物化视图,MySQL 需要使用 Flexviews 来说实现物化视图,它有如下功能

  • 变更数据抓取,可以读取服务器的二进制日至并且解析相关行的变更
  • 可以帮助常见和挂你视图的定义的存储过程
  • 可以应用变更到数据的无话视图工具
    它可以增量重新计算物化视图内容,这意味着不需要通过查询原始数据i 啊更新视图,例如创建了一张汇总表用语每个分组的行数,此后增加一行数据到源表中,Flexviews 简单的给相应的组的行数加一即可
计数器表

假如现在需要缓存一个用户的好友数,文件的加载次数,如果在表中保存计数器,在更新计数器时哭闹碰到并发问题,那创建一张独立的表存储计数器通常是个很好的选择。
但这样依然会在更新记录时,产生一个全局的互斥锁(mutex),是的事务只能串行执行,哪如何解决呢?
可以将计数器保存在多行中,每次随机啊选择一行进行更新,如给这张表增加100行数据,随机选择进行更新,要获取统计结果时,需要使用聚合查询 sum


五、ALTER TABLE

在数据量大的表中,修改表结构,往往是个费时的事,MySQL 会用新的结构创建一个空表,再从旧表中查询数据,插入到新表,最后删除旧表。在内存不足,表又大,又有索引时,更是如此。有时还会造成 MySQL 服务中断

那如何减少这种情况呢?
1.先在不提供服务的一台机子上进行 ALTER TABLE ,然后和提供服务的主库进行切换。
2.影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表,然后通过重命名和删除炒作交换两张表 ,可用 online schma change 工具

在有些情况下不需要重建表

  • 移除(不是增加)一个列的 AUTO_INCREMNET 属性
  • 增加、移除、更改 ENUM 和 SET 常量,如果移除的是已经有行数据用到其值的常量,查询将返回一个空字符串
    它是通过创建一个新的 .frm 文件,然后用它替换掉已经存在的那张表的 .frm 文件
展开阅读全文

没有更多推荐了,返回首页