Skip to main content

注意事项2



真正的核心业务表，一定不要用自增键做主键，主要有 6 个原因：

自增存在回溯问题；
自增值在服务器端产生，存在并发性能问题；
自增值做主键，只能在当前实例中保证唯一，不能保证全局唯一；
公开数据值，容易引发安全问题，例如知道地址http://www.example.com/User/10/，很容猜出 User 有 11、12 依次类推的值，容易引发数据泄露；
MGR（MySQL Group Replication） 可能引起的性能问题；
分布式架构设计问题。


需要特别注意的是，在存储时间时，UUID 是根据时间位逆序存储， 也就是低时间低位存放在最前面，高时间位在最后，即 UUID 的前 4 个字节会随着时间的变化而不断“随机”变化，并非单调递增。而非随机值在插入时会产生离散 IO，从而产生性能瓶颈。这也是 UUID 对比自增值最大的弊端。

为了解决这个问题，MySQL 8.0 推出了函数 UUID_TO_BIN，它可以把 UUID 字符串：

通过参数将时间高位放在最前，解决了 UUID 插入时乱序问题；
去掉了无用的字符串”-“，精简存储空间；
将字符串其转换为二进制值存储，空间最终从之前的 36 个字节缩短为了 16 字节。
下面我们将之前的 UUID 字符串 e0ea12d4-6473-11eb-943c-00155dbaa39d 通过函数 UUID_TO_BIN 进行转换，得到二进制值如下所示：

SELECT UUID_TO_BIN('e0ea12d4-6473-11eb-943c-00155dbaa39d',TRUE) as UUID_BIN;



CREATE TABLE User (

    id  BINARY(16) NOT NULL,

    name VARCHAR(255) NOT NULL,

    sex CHAR(1) NOT NULL,

    password VARCHAR(1024) NOT NULL,

    money INT NOT NULL DEFAULT 0,

    register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

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

    uuid CHAR(36) AS (BIN_TO_UUID(id)),

    CONSTRAINT chk_sex CHECK (sex = 'M' OR sex = 'F'),

    PRIMARY KEY(id)

);


对于分布式架构的核心业务表，我推荐类似如下的设计，比如：
PK = 时间字段 + 随机码（可选） + 业务信息1 + 业务信息2 ......



8.0 一下自建函数

CREATE FUNCTION MY_UUID_TO_BIN(_uuid BINARY(36))

    RETURNS BINARY(16)

    LANGUAGE SQL  DETERMINISTIC  CONTAINS SQL  SQL SECURITY INVOKER

    RETURN

        UNHEX(CONCAT(

            SUBSTR(_uuid, 15, 4),

            SUBSTR(_uuid, 10, 4),

            SUBSTR(_uuid,  1, 8),

            SUBSTR(_uuid, 20, 4),

            SUBSTR(_uuid, 25) ));

CREATE FUNCTION MY_BIN_TO_UUID(_bin BINARY(16))

    RETURNS  CHAR(36)

    LANGUAGE SQL  DETERMINISTIC  CONTAINS SQL  SQL SECURITY INVOKER

    RETURN

        LCASE(CONCAT_WS('-',

            HEX(SUBSTR(_bin,  5, 4)),

            HEX(SUBSTR(_bin,  3, 2)),

            HEX(SUBSTR(_bin,  1, 2)),

            HEX(SUBSTR(_bin,  9, 2)),


表压缩：不仅仅是空间压缩

在进行表结构设计时，除了进行列的选择外，还需要考虑存储的设计，特别是对于表的压缩功能的设计，总结来说：

MySQL 中的压缩都是基于页的压缩；
COMPRESS 页压缩适合用于性能要求不高的业务表，如日志、监控、告警表等；
COMPRESS 页压缩内存缓冲池存在压缩和解压的两个页，会严重影响性能；
对存储有压缩需求，又希望性能不要有明显退化，推荐使用 TPC 压缩；
通过 ALTER TABLE 启用 TPC 压缩后，还需要执行命令 OPTIMIZE TABLE 才能立即完成空间的压缩。

CREATE TABLE Transaction （

  transactionId BINARY(16) PRIMARY KEY,

  .....

)

COMPRESSION=ZLIB | LZ4 | NONE;

除了 MySQL 目前还无法支持数据分片功能外，其他方面 MySQL 的优势会更大一些，特别是 MySQL 是通过二维表格存储 JSON 数据，从而实现文档数据库功能。这样可以通过 SQL 进行很多复杂维度的查询，特别是结合 MySQL 8.0 的 CTE（Common Table Expression）、窗口函数（Window Function）等功能，而这在 MongoDB 中是无法原生实现的。

另外，和 Memcached Plugin 不同的是，MySQL 默认会自动启用 X Plugin 插件，接着就可以通过新的 X Protocol 协议访问 MySQL 中的数据，默认端口 33060，你可以通过下面命令查看有关 X Plugin 的配置：

mysql> SHOW VARIABLES LIEK '%mysqlx%';

要通过 X Protocol 管理 MySQL 需要通过新的 MySQL Shell 命令，默认并不安装，需要单独安装。下载地址：https://dev.mysql.com/downloads/shell/
mysqlsh root@localhost/test

或许有同学会问为什么 KV 数据库、文档数据库不单独使用额外的数据库呢？这是因为在企业的数据中心部门，会要求尽可能的收敛技术栈。这样对后续技术架构的稳定性，人员培养，长远来看，会有更大的收益。