MySQL CREATE TABLE 簡單設計模板交流

CREATE TABLE TEMPLATE

CREATE TABLE [table_name] (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '物理主键',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    [delete_time TIMESTAMP DEFAULT NULL COMMENT '删除时间',]

    [template]

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '模板表';

1. 性能更好, unsigned 不涉及 反码和补码 转码消耗
2. 表示物理主键更广 [-2^63, 2^63-1] -> [0, 2^64-1]
3. mysql 优化会细微好点. select * from * where id < 250; 原先是 select * from * where -2^63 <= id and id < 250; 现在是 select * from * where 0 <= id and id < 250;
4. 如果有些语言中没有 unsigned, 需要把关人备注为 signed 使用范围是 [0, 2^63-1]
5. 如果 int 也能满足业务, 也可以用 int, 节省 4 字节. 看业务把控和取舍.

1. 对于缺少 COMMENT 详细注释的, 推荐把关人 或 DBA 打回修改或拒绝操作
2. 修改和补充 COMMENT

-- 修改表注释
ALTER TABLE [table_name] COMMENT '[COMMENT]';


-- 修改字段注释
UPDATE information_schema.COLUMNS SET column_comment = '[COMMENT]' 
    WHERE TABLE_SCHEMA= '[database_name]' 
        AND TABLE_NAME='[table_name]' AND COLUMN_NAME= '[column_name]'

分析 3: 为什么用 TIMESTAMP 表示时间 ?

1. TIMESTAMP 和 int 一样都是 4 字节. 用它表示时间戳更精简更友好.
2. 业务不再关心时间的创建和更新相关业务代码. 省心, 省代码

CREATE TABLE [table_name] (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '物理主键',
    update_time BIGINT NOT NULL COMMENT '更新时间',
    create_time BIGINT NOT NULL COMMENT '创建时间',
    [delete_time BIGINT DEFAULT NULL COMMENT '删除时间',]

    [template]

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '模板表';

MySQL 的 utf8 不是标准的 utf8 unicode 字符集编码. 

正规 UTF-8 编码是使用 1-6 字节表示 unicode 字符. 

但 MySQL utf8 只使用了 1-3 字节表示一个字符,

CREATE TABLE 小部分场景

AUTO_INCREMENT

倒排索引

同样这个通用名词也很人费解. 如果叫 反向索引 很好理解.

例如我们普通的 id -> data 是(正向)索引, data 中关键 key -> id 是反向(Inverted index)索引

倒排索引为什么叫倒排索引?

1. InnoDB 中 AUTO_INCREMENT 配置大致说明

• 1.1 innodb_auto_inc_lock_mode=0 (traditional lock mode)
• 获取表锁 (AUTO-INC 锁, 特殊表锁), 语句执行结束后释放, 不需要等事务结束.
• 分配的值也是一个个分配，是连续的. (如果事务 rollback 了这个 auto_increment 值就会浪费掉, 从而造成间隙)
• 1.2 innodb_autoinc_lock_mode=1 (consecutive lock mode, MySQL 8.0 之前默认选项)
• 对于不确定插入数量的语句(例如 INSERT ... SELECT, REPLACE ... SELECT 和 LOAD DATA)
• innodb_autoinc_lock_mode=0 一样获取表锁, 其他的确定数量的语句在执行前先批量获取 id,
• 之后再走的是轻量级互斥锁, 如果其他事务已经获取表锁, 这个时候也需要等待.
• 1.3 innodb_autoinc_lock_mode=2 (interleaved lock mode, MySQL 8.0+ 默认)
• 采用乐观锁, CAS 更新计数器获取. 正常情况性能最好, 因为没有表锁和轻量级互斥锁.
• 但在高并发引发的 高 CPU load 场景会适得其反, 加剧这种 CPU 浪费.
• AUTO_INCREMENT CAS 频率高, 同一个语句操作内部 CAS INC 大概率也会让 id 间隙变大.

2. InnoDB 中 AUTO_INCREMENT 实现大致思路

-- 大致方式通过行级锁(排他锁) MAX(id) -> AUTO_INCREMENT init value
SELECT MAX(ai_col) FROM [table_name] FOR UPDATE;

在 MySQL 8.0 之后, 持久化存储在磁盘. 每次更新会写入 redo log 中, 也会刷入 innodb 引擎系统表中记录下来.

小部分场景

交流 1: 如果考虑分布式场景呢, 高性能领域呢 ?

-- sequece id 生成器表
CREATE TABLE sequece (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '物理主键, 自增 id',
    stub char(1) NOT NULL DEFAULT '' COMMENT '打桩靶子',
    UNIQUE KEY unique_key_stub (stub)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '古老分布式 id 生成器';


-- 获取 id
DELIMITER $
BEGIN
REPLACE INTO sequece(stub) VALUES ('X');
SELECT LAST_INSERT_ID();
COMMIT
$

-- step 标识增长步长, 也标识分布式机器数

show global variables like 'auto_increment%'

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

-- auto_increment_increment 全局步长
-- auto_increment_offset 自增起始值

-- 设定自增步长
-- set session 设置当前会话链接, set global 设置当前 ID 机器
set global auto_increment_increment=step

for i : [0, step)
    CREATE TABLE sequece (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '物理主键, 自增 id',
        stub char(1) NOT NULL DEFAULT '' COMMENT '打桩靶子',
        UNIQUE KEY unique_key_stub (stub)
    ) ENGINE=InnoDB AUTO_INCREMENT = [offset + i] DEFAULT CHARSET=utf8mb4 COMMENT = '古老分布式 id 生成器';

交流 2: 为什么官方推荐 AUTO_INCREMENT 当主键, 而很少见到 UUID 等等?

伪共享 (false sharing) 的非标准定义为:

缓存系统中是以缓存行 (cache line) 为单位存储的. 当多线程修改互相独立的变量时,

如果这些变量共享同一个缓存行, 就会无意中影响彼此的性能, 这就是伪共享.

后记