MySQL——Schema与数据类型优化（一）

Schema与数据类型优化

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计schama，这往往需要权衡各种元素。例如，反范式的设计可以加快某些类型的查询，但同时可能使另一类型的查询变慢。 ——《高性能MySQL》

• 选择优化的数据类型

  • 选择优化数据类型概述

    对于MySQL来说他是支持很多数据类型的，那么对于MySQL来说选择合适的数据类型对高性能是有很大的帮助的；通常MySQL遵循以下几类规则。

    1. 数据类型更小通常更好

       数据类型越小，他占用的磁盘空间就越小，并且CPU处理该数据的周期就越短

    2. 数据类型越简单越好

       通常来说，数据类型怎么才算简单，比如说整型数据类型比字符串类型更加简单；还比如说MySQL自带的时间类型比字符串来存储时间更加简单。

    3. 尽量避免NULL

       通常来说尽量避免数据table中太多的NULL值，首先在查询NULL值的列来说是难以优化的。

  • 整数类型

    有两种常见的数据数字：整数和实数。通常整数类型主要有：TINYINT，SMALLINT，MEDIUINT，INT，BIGINT。存储的空间分别是8,16,24,32,64位存储空间

    作为整数类型有可选的UNSIGNED属性表示不允许负数存在，同时作为数据位数从0开始计算；

    同时使用指定整数类型指定宽度，例如INT(11)，首先这样的做法对时间的INT类型的存储是不会产生改变的，只是规定了MySQL部分客户端显示字符个数

  • 实数类型

    实数是带有小数部分的数值。对于MySQL来说在存储实数类型的时候，他们不止是为了存储小数部分，MySQL还有DECIAML存储比BIGINT更大的整数。

    在相关财务数据存储的时候，我们更加偏向使用BIGINT代替DECIMAL，在计算出来的结果里将计算出来的数据乘以相应的倍数，然后存储在BIGINT里，这样可以避免浮点类型计算不精确和DECIMAL精确计算代价高的问题。

  • 字符串类型

    1. VARCHAR类型

       VARCHAR类型用于存储可变长字符串，是常见的字符串类型，VARCHAR需要使用1或2额外的字节记录字符串长度：假如列最大长度小于或者等于255字节，则使用1一个字节表示，大于255则使用两个字节表示。

    2. CHAR类型

       CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间；CHAR类型和VARCHAR相比较，后者在更新的时候更容易产生空间碎片，前者不容易产生空间碎片。

       关于VARCHAR和CHAR去存储同一个字符串，显然CHAR更占优势，尽管他们的空间开销是一样的，但是事实证明更长的列会消耗更大的内存。

    3. BLOB和TEXT类型

       用来存储较大的字符串数据类型

    4. 使用枚举（ENUM）来代替字符串类型

       有时候可以使用枚举列来代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举列表的时非常紧凑，MySQL在内部存储的时候其实是一个整数，然后维护一张“数字-字符串”映射关系的“查找表”，来维护相应的数据

    5. 日期和时间类型

       MySQL可以使用许多类型来保存日期和时间值，目前DATETIME和TIMETAMP两个时间类型，通常来说在满足需求的情况下尽量使用TIMESTAMP替换DATETIME作为时间类型来存储，作为TIMESTAMP相比于DATETIME的空间效率更高

    6. 位数据类型

       • BIT

         MySQL中把BIT存储为二进制的字符串，比如说“9”在ASCII码中的数值为57，在BIT中存储中为“0011101”；当我们使用如下SQL查询时，获取到的值是不一样的

         select a, a+0 from bittest;

         所获取到的值是

         a | a + 0
         ---|---
         9 | 57
         

         这里的57表示的是“9”数值在ASCII码中的位子