【Mysql】Schema与数据类型优化

Schema优化

Schema的设计非常重要，良好的Schema设计能够提高mysql的性能
一些错误的Schema设计

太多的列

太多的关联

数据类型优化

mysql支持很多数据类型，在项目开发时，如何根据业务选择正确的数据类型非常重要

原则

• 更小的通常更好
  一般情况下，应该尽量使用可以正常存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少
• 简单就好
  简单数据类型需要更少的CPU周期，整型比字符串操作代价更低，比如应该用整型存储ip地址，使用mysql内建的date，time，datetime而不是字符串储存时间
• 尽量避免null
  通常情况下最好制定列为not null，因为可为null的列使得索引、索引统计和值比较都更复杂。可为null的列占据更多的存储空间，被索引时也需要一个额外的字节

选择数据类型时首先确定合适的大类型，比如数字、字符串、时间，然后选择具体类型

整数类型

数字有两个类型：整数和实数，如果存整数可以选择

TINYINT , SMALLINT , MEDIUMINT , INT , BIGINT 分别使用 8 16 24 32 64位存储空间，存储的值范围从 -2的(N-1)次方到2的（N-1）次方-1，N是存储空间位数

证书类型有可选的UNSIGNED属性，表示不允许负值，可以使正数的上限提高一倍

mysql可以为整数类型指定宽度，比如INT（11），但是没有什么太大意义，INT（1）和INT（20）是相同的

实数类型

实数就是带小数部分的数字。然而，它们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数

CPU不支持对DECIMAL的直接计算，在MySQL5.0以及更高版本中，MySQL服务器自身实现了对DECIMAL的高精度计算，相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的控件，FLOAT使用4个字节，DOUBLE占用8个字节，DOUBLE相比FLOAT有更高的精度和更大的范围，Mysql使用DOUBLE作为内部浮点计算的类型，在数据量较大时采用BIGINT

字符串类型

VARCHAR和CHAR类型
VARCHAR和CHAR类型是最主要的字符串类型

VARCHAR
VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型，他比定长类型更省空间，因为它仅仅使用必要的空间

VARCHAR需要使用1或者2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节，则只使用一个字节表示，否则使用2个字节，MySQL 5.0以上的VARCHAR在存储和检索时会保留末尾空格，过长的VARCHAR存储为BLOB

在以下场景使用VARCHAR

• 字符串列的最大长度比平均长度大很多
• 列的更新很少

注：VARCHAR虽然是可变长字符串，但是还是要根据业务需要设置长度，因为变长是需要消耗性能的，如果差距较大意味着CPU消耗大

使用VARCHAR（5）和VARCHAR（200）存储‘hello’的空间开销是一样的，但是更长的列会消耗更多的了内存，因为mysql通常会分配固定大小的内存来保存内部值。最好的策略是只分配真正需要的空间

CHAR
CHAR是定长的，当存储CHAR时会删除所有的末尾空格，CHAR适合存储很短的字符串，或者所有值都接近同一个长度，对于经常变动的值CHAR的表现也比VARCHAR要好很多

日期和时间类型

mysql有很多类型保存日期和时间值，比如YEAR和DATE，mysql能存储的最小时间粒度为秒，mysql提供两种近似的日期类型，DATETIME和TIMESTAMP，在很多场景他们是类似的，但是也有区别

DATETIME
这个类型能保存大范围的值，从1001年到9999年，精度为秒，使用8个字节空间

TIMESTAMP
TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数，只使用4个字节的存储空间，范围要比DATETIME小很多，只能表示1970年到2038年

除了特殊情况，通常应该尽量使用TIMESTAMP，因为它的空间效率更高