《高性能MySQL》（第三版）之三：MySQL数据类型与优化

《高性能MySQL》（第三版）之三：MySQL数据类型与优化 

◎    数据类型选择原则：
1、    尽量使用能正常存储所需数据的最小的数据类型。（更快，占用资源很少）
2、    简单原则，例如整型比字符代价低、mysql自带日期类型代替字符串来存储时间
3、    尽量避免null，最好指定列not null,除非确实需要存null。
这条规则对性能提升较小，但也应该尽量遵守。可为null的列使得索引、索引统计和值比较变得更复杂，占用更多的存储空间。

 

◎    数据类型如下：
1、    整数类型
          tinyint        8位
          smallint        16位
          mediumint    24位
          int            32位
          bigint        64位

UNSIGNED 属性，便是不允许负值，可以使正数上限提高一倍。有无符号都是使用相同的存储空间，性能也相同。

Mysql可为整数类型指定宽度，例如int(11)，只是用来显示字符个数，对存储和计算无实际意义。
2、    实数类型
decimal  用来存储精确的小数，decimal只是一种存储格式，在计算中decimal会转为double类型。

针对浮点，建议只指定数据类型，不指定精度，因为指定精度会使mysql悄悄选择不同的数据类型，或者在存储时对值进行取舍。

存储同样的数据范围的值时，浮点比decimal使用更少的空间，mysql默认使用double作为内部浮点计算的类型。

Decimal需要额外的空间和计算开销，所以尽量只在对小数进行精确计算的时候才会使用decimal，例如财务数据，如果数据很大，可以考虑使用bigint代替【对原数据进行位数值的倍数的扩充，使得小数变整数】

3、    字符串类型

varchar 
          存储可变长字符串，比定长类型省空间，使用1或2个额外字节存储字符串的长度。

          适用情形：
          1、    字符串列的最大长度比平均长度大得多
          2、    列的更新很少，碎片不是问题

char 
              定长类型，
              适合存储很短的字符串，或者值都接近一个长度，定长的char不易产生碎片

BLOB和TEXT类型
          分别采用二进制和字符方式，用于存储很大的数据。所以BLOB没有排序规则或字符集，而TEXT有。对这两种类型的排序，只对每个列的最前max_sort_length字节而不是整个字符串做排序。

          尽量避免使用。

使用枚举（ENUM）代替字符串类型
              枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。所以可以按照需要的顺序或者字母顺序来定义枚举列。

4、日期和时间类型

日期类型
          datetime 与时区无关，8个字节，YYYYMMDDHHMMSS格式 ，范围从1001到9999，精度为秒

          timestamp，4个字节，范围只能1970到2038年，保存1970年1月1日以来的秒数，默认 not null,能默认设置为当前时间，能根据时区自动更新。

          一般选择timestamp。比datetime空间效率更高。

5、位数据类型（技术上都属于字符串类型）
          bit 尽量避免使用，mysql当其为字符串类型而不是数字类型
          set 不再展开细说，平时基本没用过

6、选择标识符
    总结：
          最好用整数，不但快还能使用自增长；
          不要用ENUM和SET，它们适合存储固定信息；
          也尽量避免使用字符串作为标识列，因为很消耗空间还比数字慢；

          ps:对于随机的字符串，例如md5或者uuid，生成的新值分布在很大空间内，导致insert、select语句变慢（原因如下：
                        插入值会随机写到索引的不同位置，导致页分裂、磁盘随机访问，对聚簇存储引擎产生聚簇索引碎片，导致insert变慢
                        逻辑上相邻的行会分布在磁盘、内存的不同地方，导致查询变慢）
         建议若存储uuid值，移除 – 号，或UNHEX()函数把uudi值转为16字节的数字

◎    Mysql数据库表设计注意点：

          1、    不要太多的列，因为存储引擎API通过 行缓冲格式 在 服务器层和存储引擎层之间拷贝数据，然后在服务器层把缓冲内容解码成各个列，这个操作代价非常高，列如果太多的话，你懂的。
          2、    不要太多关联，单个查询最好在12个表以内做关联，否则解析和优化查询的代价会成为问题。
          3、    防止过度使用枚举，一旦枚举需要新增，就要Alter操作表

          ps:mysql的索引可以存储null值，但oracle不会。

◎    范式
          范式的优点【针对反范式】概括为  更新操作更快、表小可以放内存更快执行操作，操作数据量小；缺点是需要关联。

          反范式，所有数据都在一张表，可以更好避免关联，避免随机I/O

          常见的反范式的方法是：复制 或者 缓存，在不同的表中存储相同的特定列，进行数据冗余

◎    缓存表和汇总表
              听名字就能理解什么意思。例如近期公司的发电量预测其实就可以使用这种思路，原数据只在一张表中，里面存放了各个电站各个月份的发电量预测的数据。现在业务每次要查所有电站的未来12个月的每月的预测发电量之和，类似这种需求就可以新增一个汇总表来处理。提前计算统计把数据放到汇总表，业务直接从汇总表查询。在数据量很大的情形下，比每次直接从原表执行sql的统计查询效率快很多。

◎    瞧一瞧 Alter table
          mysql大部分修改表的操作都是用新结构创建新表，旧表查出所有数据插入新表，再删除旧表。

          列的默认值实际存在表的.frm文件。但是并不是所有的alter table 都会引起表重建。alter table有如下三种，是有区别的。

          1、    alter table alter column 
                    设置或删除列的默认值，直接修改.frm文件而不涉及表数据，很快
          2、alter table change column 
                        列的重命名、列类型的变更以及列位置的移动
          3、alter table modify column ,
                    列的列类型的变更以及列位置的移动

                    ps:所有的modify操作都将导致表重建

 

--------------- OVER  -------------
下期预告： 《高性能MySQL》（第三版）之四：MySQL高性能索引 

码字不易，感谢支持！ 点关注，下次相遇不迷路！

欢迎关注微信公众号：独行侠的守望
           - - - 无论如何都要坚定的前行，去拼搏，去奋斗。爱生活，爱自己！

                 码农一枚，主要分享生活日常、兴趣爱好，记录成长。欢迎一起交流！

                            

      -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 

        本文同步发布于个人网站：https://www.yuanhuiying.com    进入主站点击“愚默博客“标签页进行访问。