表设计中的陷阱与建议

1. 数字类型

1.1 整型类型

数量字段用 unsigned 还是signed？ 一般来讲，数量不可能为负值，用unsigned是理所应当的事情。但是如下情况，做此类减法的统计，那么报错就来了。大意是计算值为负，超出了unsigned的范围。这个可以通过SET sql_mode='NO_UNSIGNED_SUBTRACTION';解决。但是如果有符号整型可以满足需求，那么就不要使用无符号整型，否则就会有类似不必要的麻烦。

MariaDB [mysql]> CREATE TABLE Item 
    -> (
    -> id int NOT NULL,
    -> count int unsigned default 0,
    -> want_count int unsigned default 0
    -> );
Query OK, 0 rows affected (0.06 sec)

MariaDB [mysql]> Insert into Item (id, count, want_count) values (1, 100000, 5000);
Query OK, 1 row affected (0.01 sec)

MariaDB [mysql]> select want_count - count from Item;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`mysql`.`Item`.`want_count` - `mysql`.`Item`.`count`)'
MariaDB [mysql]> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select want_count - count from Item;
+--------------------+
| want_count - count |
+--------------------+
|             -95000 |
+--------------------+
1 row in set (0.01 sec)

1.2 浮点类型和高精度型

float和double都是浮点类型，后者精度比前者高。但是这两个时非标准的，精度计算可能存在误差。所以较新版的mysql8.0 已经开始建议废弃。可以使用DECIMAL替代，DECIMAL(9,3) 9 是精度（整数位数），3 是标度（小数点后的位数）。用户的工资、账户的余额等精确到小数点后 2 位的业务都可以使用此类型。如果数据量大，且精度要求更高，可以使用BIGINT替代，根据需求乘以倍数即可。比如，精确到万分之一，则把所有金额乘以1万存到BIGINT中。这样同样可以解决浮点和高精度类型运算消耗资源高的问题，以及存储不定长带来的问题。

1.3 主键自增

一般来讲，整型配合auto_increment可以实现主键自增。不过要注意到int上线42亿左右，如果业务频繁这个很容易达到。所以，尽量采用BIGINT做自增主键，而不是INT。另外，MySQL 8.0 之前，自增不持久化，如果遇到数据库重启，自增值可能会存在回溯问题！

如果是海量业务，尤其是涉及到分布式架构，用 UUID 做主键或业务自定义生成主键是更好的选择。

mysql> select UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 7ad9b7d2-b499-11eb-8166-5254001a19ec |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select UUID();
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 7b61c72b-b499-11eb-8166-5254001a19ec |
+--------------------------------------+
1 row in set (0.00 sec)

UUID

UUID = 时间低（4字节）- 时间中高+版本（4字节）- 时钟序列 - MAC地址

2. 字符串类型

2.1 特殊字段要添加约束

比如性别只能是男女，余额必须大于0等。 如果不添加约束，那么表中的数据可能变脏导致语义不清

 CREATE TABLE Student(
     id INT PRIMARY KEY, 
     name VARCHAR(20) NOT NULL UNIQUE,
     sex CHAR(1) CHECK(sex='F' OR sex='M'),
     age INT DEFAULT 18
     );

mysql8.0之后支持check，使用ENUM 也可以，只不过ENUM 只支持mysql。

mysql> desc Student_e;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | NO   | PRI | NULL    |       |
| name  | varchar(20)   | NO   | UNI | NULL    |       |
| sex   | enum('M','F') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | 18      |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> Insert into Student_e(id,name,sex,age) values(1,"xiaoming","T",20);
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

2.2 密码不要直接存储

一般情况，数据库中存放用户密码的hash值，直接存放明文，很多情况下是不安全不合规的。为了更高的安全性，可以加盐（动态盐最好了）和多种可选算法。

 

3. 日期类型

存储日期类型的字段有三种方案，DATETIME、TIMESTAMP、INT

TIMESTAMP最大数值能用到2038年，这也快到了，不建议用。INT存储时间，对数据库维护人员来说不那么友好。 而DATETIME的精度可以做到更高，而且不用考虑上限问题。

mysql> SELECT NOW(6);
+----------------------------+
| NOW(6)                     |
+----------------------------+
| 2021-05-14 16:33:00.643074 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW(4);
+--------------------------+
| NOW(4)                   |
+--------------------------+
| 2021-05-14 16:34:16.6732 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2021-05-14 16:34:21 |
+---------------------+
1 row in set (0.00 sec)

另外，对更新操作，表中可加一个更新时间，方便维护。

    last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

4. 非结构存储

mysql8.0 支持json类型，这将会极大的方便开发。一些固定的字段，我们可在表中单独添加一列，不固定的信息则可以放进json中。这要比多个一些冗余字段要好的多，也比放到varchar中要便捷，省去来回解析json了。不过json一般是变长的，变长的就容易引起内存碎片。

DROP TABLE IF EXISTS User;

CREATE TABLE User (
    id BIGINT NOT NULL,
    loginInfo JSON,
    PRIMARY KEY(id)
);

然后插入数据

mysql> SET @info = '
    '> {
    '> "autograph" : "no pain,no gain",
    '> "wx" : "coder"
    '> }
    '> ';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO User VALUES (1024,@info);
Query OK, 1 row affected (0.02 sec)

mysql> select * from User;
+------+-------------------------------------------------+
| id   | loginInfo                                       |
+------+-------------------------------------------------+
| 1024 | {"wx": "coder", "autograph": "no pain,no gain"} |
+------+-------------------------------------------------+
1 row in set (0.00 sec)

搜索

mysql> SELECT
    ->     id,
    ->     JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.phone")) phone,
    ->     JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wx")) wx
    -> FROM User;
+------+-------+-------+
| id   | phone | wx    |
+------+-------+-------+
| 1024 | NULL  | coder |
+------+-------+-------+
1 row in set (0.00 sec)

5. 表压缩提高吞吐

在innodb存储引擎中数据是按照表空间来组织存储的，表空间文件是文件系统的物理文件。表空间是由一个个页组成的，一页默认为16K，这些也用来存储表中一行行的数据。操作系统读取数据是页为单位的，如果一页中包含的表记录越多，那么吞吐量就越大。而进行表压缩之后，相比未压缩，增加的记录就越多。

数据库可以根据记录、页、表空间进行压缩，实际用的比较多的是页压缩。

如果业务系统，CPU富余，但IO负载更不上，可以用压缩提高IO吞吐。

CREATE TABLE Stu (
    id BIGINT NOT NULL,
    loginInfo JSON,
    PRIMARY KEY(id)
)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

创建表时ROW_FROMAT 设为 COMPRESS，将启用页压缩功能，KEY_BLOCK_SIZE 设置为 8，将会把一个 16K 的页压缩为 8K。不过一个压缩页在内存缓冲池中，存在压缩和解压两个页，开销也比较大。

表压缩主要用于字符类型比较大的表上（VARCHAR，VARBINARY和BLOB和TEXT类型），读操作远远多于写操作。