当前位置 : 主页 > 数据库 > mysql >

设计性能更优MySQL数据库schema

来源:互联网 收集:自由互联 发布时间:2022-12-24
目录 数据类型优化 选择优化的数据类型 整数类型 实数类型 字符串类型 日期和时间类型 位数据类型 MySQL schema设计中的缺陷 范式和反范式 范式的优点和缺点 反范式化的优点和缺点 混
目录
  • 数据类型优化
  • 选择优化的数据类型
    • 整数类型
    • 实数类型
    • 字符串类型
    • 日期和时间类型
    • 位数据类型
  • MySQL schema设计中的缺陷
    • 范式和反范式
      • 范式的优点和缺点
      • 反范式化的优点和缺点
      • 混用范式化和反范式化
    • 缓存表和汇总表
      • 物化视图
        • 计数器表
      • 加快alter TABLE操作的速度

        数据类型优化

        首先我们介绍一下这个schema:

        schema(发音 “skee-muh” 或者“skee-mah”,中文叫模式)是数据库的组织和结构

        选择优化的数据类型

        mysql支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。不管存储那种类型的数据,下面几个简单的原则你需要记住。

        • 更小的通常更好
        • 简单就好
        • 尽量避免null

        整数类型

        我们有以下几种整数类型:

        TINYINTSMALLINTMEDIUMINTINTBIGINT8位16位24位32位64位

        他们的存储空间范围从-2(N-1)次方到2的(N-1)次方减一。N是位数

        整数类型有可选的unsigned属性,表示不允许负值,这大致可以使正数的上限提高一倍。

        实数类型

        实数嘛,就是带有小数部分的数字,然而,它不只是为了存储小数部分;也可以使用decimal存储比bigint还大的整数。

        decimal(18,9)小数点两边各存储9个数字,一个使用9个字节;小数点前的数字使用4个字节,小数点后的数字使用4个字节,小数点本身占一个字节。

        因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用decimal--例如存储财务数据,但是在数据量比较大的时候们可以使用bigint代替decimal,将存储的数据根据小数的位数乘以相应的倍数即可。

        字符串类型

        varchar

        用于存储可变长的字符串,如果MySQL使用 ROW_FORMAT=FIXED创建的话,每一行都会使用定长存储,这会很浪费空间。

        varchar需要使用1个或者2个额外的字节记录字符串的长度。例如:varchar(10)的列需要11个字节的存储空间。

        但是由于行是变长的在update的时候可能使行变得比原来更长,这就导致需要额外的工作。例如myisam会将行拆成不同的片段存储,innodb则需要分裂页来使行可以放进页内。

        char

        char类型是定长的,当存储char值时。mysql会删除所有的末尾空格。

        char适合存储很短的字符串,或者所有值都接近同一个长度。例:char十分适合存储密码的md5值。

        对于经常变更的数据,char也比varchar好,因为定长的char类型不容易产生碎片。

        BLOB和TEXT

        都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。

        MySQL把每个blob和text值当做一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当blob和text值太大时,innodb会使用专门的外部存储区域进行存储,此时每个值在行内需要1--4个字节来存储一个指针,然后在外部的存储区域存储实际的值。

        BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据,没有排序规则或字符集,而text类型有字符集和排序规则。

        使用枚举enum代替字符串类型

        有时候可以使用枚举列来代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。mysql在存储枚举时非常紧凑,会根据列表值的数量压缩到一个或者两个字节中。mysql内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存“数字-字符串‘映射关系的查找表。

        我们看看下面的例子:

        create TABLE  enum_test(
                e ENUM('fish','apple','dog') NOT NULL
        );
        ​
        INSERT INTO enum_test(e) VALUES ('fish'),('dog'),('apple');
        SELECT e + 0 FROM enum_test;

        SELECT e FROM enum_test;

        所以使用数字作为enum枚举常量,这种双重性很容易导致混乱,例如enum(’1‘,’2‘,’3‘)。所以尽量别这么用。

        另外一个让人大吃一惊的事情是:

        枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。

        枚举最不好的地方是,字符串列表是固定的,添加或删除字符串必须使用ALTER TABLE 。因此对于一系列未来可能改变的字符串,枚举并不是一个好主意。

        日期和时间类型

        DATETIME

        这个类型可以保存大范围的值,从1001年到9999年,精度为秒,他把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。

        TIMRSTAMP

        就像它的名字一样TIMESTAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数。它和unix时间戳相同。timestamp值使用4个字节的存储空间,因此他的范围比datetime 小的多。只能表示从1970到2038年MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期,并且提供Unix_TIMESTAMP()函数把日期转换为Unix时间戳。

        位数据类型

        BIT

        mysql把bit当做字符串类型,而不是数字类型,当检索bit(1)的值时,结果是一个包含二进制0或1 的字符串,而不是ascii码的0,1。

        SET

        如果需要保存很多true/false值,可以考虑合并这些列到一个set数据类型,他在mysql内部是以一系列打包的位的集合来表示的。这样可以有效的利用空间,并且MySQL有像FIND_IN_SET()和FIELD()这样的函数,方便地在查询中使用。

        它的主要缺点是改变列的定义的代价较高:需要alter TABLE,这对大表来说是非常昂贵的操作。

        MySQL schema设计中的缺陷

        上一篇:MySQL数据库的触发器的使用
        下一篇:没有了
        网友评论