[Mysql实战指南数字类型] 带你全面了解数据库表设计/常规类型的设计选用?

最新推荐文章于 2023-03-07 17:14:18 发布

是汤圆丫

最新推荐文章于 2023-03-07 17:14:18 发布

阅读量662

点赞数 1

分类专栏：数据库SQL相关 SQL 优化相关工作总结相关

汤圆

本文链接：https://blog.csdn.net/sqL520lT/article/details/116975903

版权

工作总结相关同时被 3 个专栏收录

88 篇文章 1 订阅

订阅专栏

数据库SQL相关

19 篇文章 2 订阅

订阅专栏

SQL 优化相关

9 篇文章 0 订阅

订阅专栏

你需要了解的MySQL

- - 数字类型:避免自增的踩坑

数字类型:避免自增的踩坑

在进行表结构设计时，数字类型是最为常见的类型之一，但要用好数字类型并不如想象得那么简单，比如：

怎么设计一个互联网海量并发业务的自增主键？用 INT 就够了？

怎么设计账户的余额？用 DECIMAL 类型就万无一失了吗？

以上全错:

整数类型:

MySQL数据库支持标准支持的整形类型: INT,SMALLINT。此外,MySQL数据库也支持诸如TINYINT,MEDIUMINT 和
BIGINT整形类型

(表1 显示了各种整形所占用的存储空间及取值范围):
在这里插入图片描述

在整型类型中，有 signed 和 unsigned 属性，其表示的是整型的取值范围，默认为 signed。在设计时，我不建议你刻意去用 unsigned 属性，因为在做一些数据分析时，SQL 可能返回的结果并不是想要得到的结果。

来看一个“销售表 sale”的例子，其表结构和数据如下。这里要特别注意，列 sale_count 用到的是 unsigned 属性（即设计时希望列存储的数值大于等于 0）：

mysql> SHOW CREATE TABLE sale\G
*************************** 1. row ***************************
       Table: sale
Create Table: CREATE TABLE `sale` (
  `sale_date` date NOT NULL,
  `sale_count` int unsigned DEFAULT NULL,
  PRIMARY KEY (`sale_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql> SELECT * FROM sale;
+------------+------------+
| sale_date  | sale_count |
+------------+------------+
| 2020-01-01 |      10000 |
| 2020-02-01 |       8000 |
| 2020-03-01 |      12000 |
| 2020-04-01 |       9000 |
| 2020-05-01 |      10000 |
| 2020-06-01 |      18000 |
+------------+------------+
6 rows in set (0.00 sec)

其中，sale_date 表示销售的日期，sale_count 表示每月的销售数量。现在有一个需求，老板想要统计每个月销售数量的变化，以此做商业决策。这条 SQL 语句需要应用到非等值连接，但也并不是太难写：

SELECT
    s1.sale_date, s2.sale_count - s1.sale_count AS diff
FROM
    sale s1
        LEFT JOIN
    sale s2 ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date
ORDER BY sale_date;

然而,在执行的过程中,由于列sale_count用到了unsigned属性,会抛出这样的结果。

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`s2`.`sale_count` - `test`.`s1`.`sale_count`)'

可以看到,MySQL提示用户计算的结果超出了范围,其实这里MySQL要求unsigned数值相减以后依然为unsigned,否则就会报错。

为了避免这个错误，需要对数据库参数 sql_mode 设置为 NO_UNSIGNED_SUBTRACTION，允许相减的结果为 signed，这样才能得到最终想要的结果：

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
SELECT
    s1.sale_date,
    IFNULL(s2.sale_count - s1.sale_count,'') AS diff
FROM
    sale s1
    LEFT JOIN sale s2 
    ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date
ORDER BY sale_date;
+------------+-------+
| sale_date  | diff  |
+------------+-------+
| 2020-01-01 |       |
| 2020-02-01 | 2000  |
| 2020-03-01 | -4000 |
| 2020-04-01 | 3000  |
| 2020-05-01 | -1000 |
| 2020-06-01 | -8000 |
+------------+-------+
6 rows in set (0.00 sec)

浮点类型和高精度类型

除了整型类型，数字类型常用的还有浮点和高精度类型。

MySQL 之前的版本中存在浮点类型 Float 和 Double，但这些类型因为不是高精度，也不是 SQL 标准的类型，所以在真实的生产环境中不推荐使用，否则在计算时，由于精度类型问题，会导致最终的计算结果出错。

更重要的是，从 MySQL 8.0.17 版本开始，当创建表用到类型 Float 或 Double 时，会抛出下面的警告：MySQL 提醒用户不该用上述浮点类型，甚至提醒将在之后版本中废弃浮点类型。

Specifying number of digits for floating point data types is deprecated and will be removed in a future release

而数字类型中的高精度 DECIMAL 类型可以使用，当声明该类型列时，可以（并且通常必须要）指定精度和标度，例如：

salary DECIMAL(8,2)

其中，8 是精度（精度表示保存值的主要位数），2 是标度（标度表示小数点后面保存的位数）。通常在表结构设计中，类型 DECIMAL 可以用来表示用户的工资、账户的余额等精确到小数点后 2 位的业务。

然而，在海量并发的互联网业务中使用，金额字段的设计并不推荐使用 DECIMAL 类型，而更推荐使用 INT 整型类型（下文就会分析原因）。

业务表结构设计:

在真实业务场景中，整型类型最常见的就是在业务中用来表示某件物品的数量。例如上述表的销售数量，或电商中的库存数量、购买次数等。在业务中，整型类型的另一个常见且重要的使用用法是作为表的主键，即用来唯一标识一行数据。

整型结合属性 auto_increment，可以实现自增功能，但在表结构设计时用自增做主键，希望你特别要注意以下两点，若不注意，可能会对业务造成灾难性的打击：

用 BIGINT 做主键，而不是 INT；
自增值并不持久化，可能会有回溯现象（MySQL 8.0 版本前）。

从表 1 可以发现，INT 的范围最大在 42 亿的级别，在真实的互联网业务场景的应用中，很容易达到最大值。例如一些流水表、日志表，每天 1000W 数据量，420 天后，INT 类型的上限即可达到。

因此，（敲黑板 1）用自增整型做主键，一律使用 BIGINT，而不是 INT。不要为了节省 4 个字节使用 INT，当达到上限时，再进行表结构的变更，将是巨大的负担与痛苦。

那这里又引申出一个有意思的问题：如果达到了 INT 类型的上限，数据库的表现又将如何呢？是会重新变为 1？我们可以通过下面的 SQL 语句验证一下：

mysql> CREATE TABLE t (
    ->     a INT AUTO_INCREMENT PRIMARY KEY
    -> );
mysql> INSERT INTO t VALUES (2147483647);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t VALUES (NULL);
ERROR 1062 (23000): Duplicate entry '2147483647' for key 't.PRIMARY'

可以看到，当达到 INT 上限后，再次进行自增插入时，会报重复错误，MySQL 数据库并不会自动将其重置为 1。

第二个特别要注意的问题是，（敲黑板 2）MySQL 8.0 版本前，自增不持久化，自增值可能会存在回溯问题！

mysql> SELECT * FROM t;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.01 sec)
mysql> DELETE FROM t WHERE a = 3;
Query OK, 1 row affected (0.02 sec)
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec

可以看到，在删除自增为 3 的这条记录后，下一个自增值依然为 4（AUTO_INCREMENT=4），这里并没有错误，自增并不会进行回溯。但若这时数据库发生重启，那数据库启动后，表 t 的自增起始值将再次变为 3，即自增值发生回溯。具体如下所示：

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 s

若要彻底解决这个问题，有以下 2 种方法：

升级 MySQL 版本到 8.0 版本，每张表的自增值会持久化；

若无法升级数据库版本，则强烈不推荐在核心业务表中使用自增数据类型做主键。

其实，在海量互联网架构设计过程中，为了之后更好的分布式架构扩展性，不建议使用整型类型做主键，更为推荐的是字符串类型（这部分内容将在下次分享详细介绍）。

资金字段设计

在用户余额、基金账户余额、数字钱包、零钱等的业务设计中，由于字段都是资金字段，通常程序员习惯使用 DECIMAL 类型作为字段的选型，因为这样可以精确到分，如：DECIMAL(8,2)。

CREATE TABLE User (
  userId BIGINT AUTO_INCREMENT,
  money DECIMAL(8,2) NOT NULL,
  ......
)

（敲黑板3）在海量互联网业务的设计标准中，并不推荐用 DECIMAL 类型，而是更推荐将 DECIMAL 转化为整型类型。也就是说，资金类型更推荐使用用****分单位存储，而不是用元单位存储。如1元在数据库中用整型类型 100 存储。

金额字段的取值范围如果用 DECIMAL 表示的，如何定义长度呢？因为类型 DECIMAL 是个变长字段，若要定义金额字段，则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99，百万级的资金存储。

用户的金额至少要存储百亿的字段，而统计局的 GDP 金额字段则可能达到数十万亿级别。用类型 DECIMAL 定义，不好统一。

另外重要的是，类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。因此，推荐使用 BIG INT 来存储金额相关的字段。

字段存储时采用分存储，即便这样 BIG INT 也能存储千兆级别的金额。这里，1兆 = 1万亿。

这样的好处是，所有金额相关字段都是定长字段，占用 8 个字节，存储高效。另一点，直接通过整型计算，效率更高。

注意，在数据库设计中，我们非常强调定长存储，因为定长存储的性能更好。

我们来看在数据库中记录的存储方式，大致如下：

在这里插入图片描述

当上图发生更新的时候,记录一原先的空间无法容纳更新后记录一的存储空间,因此,这是数据库会将记录一标记删除,寻找新的空间给新记录一使用!

在这里插入图片描述
上图的记录一表示的就是原先记录一占用的空间,而这个空间后续将变成碎片空间,无法继续使用,除非人为地进行表空间的碎片整理。(碎片空间下面有简单的了解)

那么当我们用BIG INT存储金额字段的时候,如何表示小数点中数据呢?

其实这部分直接交给前端做就ok了? 作为数据库本身,只需要按照分的形式进行存储！

总结

不推荐使用整型类型的属性 Unsigned，若非要使用，参数 sql_mode 务必额外添加上选项 NO_UNSIGNED_SUBTRACTION；
自增整型类型做主键，务必使用类型 BIGINT，而非 INT，后期表结构调整代价巨大,这里BIGINT 一般用于最多的非核心业务表,后续了解动态时间+UUID+业务id作为主键；
MySQL 8.0 版本前，自增整型会有回溯问题，做业务开发的你一定要了解这个问题；
当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误；
不要再使用浮点类型 Float、Double，MySQL 后续版本将不再支持上述两种类型；
账户余额字段，设计是用整型类型，而不是 DECIMAL 类型，这样性能更好，存储更紧凑

碎片空间查看以及提高效率:

以MySQL为例，碎片的存在十分影响性能

MySQL 的碎片是 MySQL 运维过程中比较常见的问题，碎片的存在十分影响数据库的性能，本文将对 MySQL 碎片进行一次讲解。

判断方法：

MySQL 的碎片是否产生，通过查看

select ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE from information_schema.TABLES where TABLE_SCHEMA=‘tft_card_service’ and TABLE_NAME=‘card_type’ limit 1;

对表的meta data的查询需要使用information_schema.tables， table_schema是数据库的名称，table_name是具体的表明，table_type指的是表的类型

这个命令中 Data_free 字段，如果该字段不为 0，则产生了数据碎片。

产生的原因：

1. 经常进行 delete 操作

经常进行 delete 操作，产生空白空间，如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用，久而久之就产生了碎片；

演示：

创建一张表，往里面插入数据，进行一个带有 where 条件或者 limit 的 delete 操作，删除前后对比一下 Data_free 的变化。

删除前：