数据库设计最佳实践：从建模到分库分表

-- 需求分析阶段产出的数据字典示例
-- 字段名      类型        长度    必填    说明
-- order_id    BIGINT      20      Y       订单唯一ID
-- user_id     BIGINT      20      Y       下单用户
-- order_no    VARCHAR     32      Y       订单号（业务可读）
-- amount      DECIMAL     12,2    Y       实付金额
-- status      TINYINT     4       Y       订单状态
-- created_at  DATETIME    0       Y       下单时间

┌─────────────┐       ┌──────────────┐
│  用户 (User) │       │  订单 (Order) │
│─────────────│       │──────────────│
│ user_id PK  │──1:N──│ order_id PK  │
│ 用户名       │       │ user_id FK   │
│ 邮箱         │       │ 金额          │
│ 注册时间     │       │ 状态          │
└─────────────┘       │ 创建时间      │
                       └──────────────┘
                              │ 1:N
                              ▼
                       ┌──────────────┐
                       │ 订单项(Item) │
                       │──────────────│
                       │ item_id PK   │
                       │ order_id FK  │
                       │ 商品名称      │
                       │ 数量          │
                       └──────────────┘

-- 用户表
CREATE TABLE `user` (
    `user_id`    BIGINT       NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    `username`   VARCHAR(32)  NOT NULL COMMENT '用户名',
    `email`      VARCHAR(128) NOT NULL COMMENT '邮箱',
    `status`     TINYINT      NOT NULL DEFAULT 1 COMMENT '状态 1-正常 0-禁用',
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `uk_email` (`email`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 订单表
CREATE TABLE `order` (
    `order_id`   BIGINT       NOT NULL AUTO_INCREMENT COMMENT '订单ID',
    `user_id`    BIGINT       NOT NULL COMMENT '用户ID',
    `amount`     DECIMAL(10,2) NOT NULL COMMENT '订单金额',
    `status`     TINYINT      NOT NULL DEFAULT 0 COMMENT '状态 0-待支付 1-已支付 2-已取消',
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`order_id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_created_at` (`created_at`),
    CONSTRAINT `fk_order_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 物理设计阶段的关键决策
-- 1. 选择存储引擎：InnoDB（事务+行锁+外键）
-- 2. 字符集：utf8mb4（支持emoji）
-- 3. 字段类型精炼
CREATE TABLE `order` (
    `order_id`   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  -- 无符号扩大范围
    `user_id`    BIGINT UNSIGNED NOT NULL,
    `amount`     DECIMAL(12,2) NOT NULL,                   -- 总金额至多 10^10
    `status`     TINYINT UNSIGNED NOT NULL DEFAULT 0,      -- 小范围枚举用 TINYINT
    `pay_time`   DATETIME DEFAULT NULL,                    -- 允许 NULL 表示未支付
    `version`    INT UNSIGNED NOT NULL DEFAULT 0,          -- 乐观锁
    PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='订单表物理设计';

-- 反模式：用逗号分隔存储多值
CREATE TABLE `user_tag_bad` (
    `user_id` BIGINT PRIMARY KEY,
    `tags`    VARCHAR(500) COMMENT '标签，逗号分隔'  -- "VIP,老用户,付费会员"
);

-- 查询"VIP"用户：全表扫描 + 无法命中索引
SELECT * FROM `user_tag_bad` WHERE FIND_IN_SET('VIP', `tags`) > 0;

-- 正确做法：关联表
CREATE TABLE `user_tag` (
    `user_id`  BIGINT NOT NULL,
    `tag_name` VARCHAR(32) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`, `tag_name`),
    KEY `idx_tag_name` (`tag_name`)          -- 按标签检索
);

-- 查询"VIP"用户
SELECT u.* FROM `user` u
JOIN `user_tag` ut ON u.`user_id` = ut.`user_id`
WHERE ut.`tag_name` = 'VIP';

-- 正确的联合索引示例
-- 查询场景：按用户ID查订单，按时间排序
CREATE INDEX `idx_user_time` ON `order` (`user_id`, `created_at`);

-- 能命中 idx_user_time：
EXPLAIN SELECT * FROM `order` WHERE `user_id` = 100 ORDER BY `created_at` DESC;
-- 不能完全命中：
EXPLAIN SELECT * FROM `order` WHERE `created_at` > '2024-01-01';  -- 全表扫描

-- 假设索引 (user_id, status, amount)
-- 以下查询只需扫描索引，无需回表
EXPLAIN 
SELECT `user_id`, `status`, `amount` 
FROM `order` 
WHERE `user_id` = 100;
-- Extra 列显示：Using index

-- 若要加 created_at，则需回表
EXPLAIN 
SELECT `user_id`, `status`, `amount`, `created_at` 
FROM `order` 
WHERE `user_id` = 100;
-- Extra 列可能显示：Using index condition (ICP)

-- 找出冗余索引
-- idx_a_b(a, b) 完全覆盖 idx_a(a)，后者可删除
-- idx_b(b) 与 idx_a_b(a, b) 不重叠，不能删

-- MySQL 检查冗余索引
SELECT 
    table_schema, table_name,
    GROUP_CONCAT(index_name ORDER BY seq_in_index) AS indexes
FROM information_schema.statistics
WHERE table_schema = 'your_db'
GROUP BY table_schema, table_name, index_name;

-- 查看索引使用情况（MySQL）
SELECT 
    index_name,
    rows_read,
    rows_deleted,
    rows_updated
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db' AND object_name = 'order';

-- 重建碎片化索引
ALTER TABLE `order` ENGINE=InnoDB;  -- 重建表（重组聚簇索引）
OPTIMIZE TABLE `order`;             -- 整理碎片（InnoDB 下相当于 ALTER）

-- 定期检查索引碎片率
SELECT 
    table_name, 
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND(data_free / 1024 / 1024, 2) AS free_mb
FROM information_schema.tables
WHERE table_schema = 'your_db'
  AND data_free > 100 * 1024 * 1024;  -- 碎片超过 100MB

架构示意：

┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│  用户库 DB1  │  │  订单库 DB2  │  │  商品库 DB3  │
│  user        │  │  order      │  │  product    │
│  user_addr   │  │  order_item │  │  category   │
│             │  │  payment    │  │  inventory  │
└─────────────┘  └─────────────┘  └─────────────┘

-- 原始宽表
CREATE TABLE `product` (
    `product_id`     BIGINT PRIMARY KEY,
    `name`           VARCHAR(100),
    `description`    TEXT,            -- 不常访问的大字段
    `spec_json`      JSON,            -- 不常访问
    `cover_image`    VARCHAR(500),
    `detail_images`  JSON,            -- 大字段
    `price`          DECIMAL(10,2),
    `stock`          INT,
    `created_at`     DATETIME
);

-- 垂直分表后
CREATE TABLE `product_base` (
    `product_id`  BIGINT PRIMARY KEY,
    `name`        VARCHAR(100),
    `price`       DECIMAL(10,2),
    `stock`       INT,
    `cover_image` VARCHAR(500),
    `created_at`  DATETIME
) ENGINE=InnoDB COMMENT='商品基础信息';

CREATE TABLE `product_detail` (
    `product_id`    BIGINT PRIMARY KEY,
    `description`   TEXT,
    `spec_json`     JSON,
    `detail_images` JSON,
    FOREIGN KEY (`product_id`) REFERENCES `product_base`(`product_id`)
) ENGINE=InnoDB COMMENT='商品扩展信息';

-- 订单表按 user_id 取模分 16 表
-- 分片键：user_id
-- 分片规则：user_id % 16

-- 路由逻辑伪代码
function getTableName(userId):
    suffix = userId % 16
    return "order_" + suffix

-- 每个分片表结构一致
CREATE TABLE `order_0` (
    `order_id`  BIGINT NOT NULL AUTO_INCREMENT,
    `user_id`   BIGINT NOT NULL,
    `amount`    DECIMAL(10,2) NOT NULL,
    `status`    TINYINT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`order_id`, `user_id`),  -- 复合主键保证分片键在索引中
    KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 其他分片表 order_1 ~ order_15 同上

-- 按月分表：order_202401, order_202402 ...
-- 路由函数
function getTableNameByTime(createTime):
    return "order_" + format(createTime, "YYYYMM")

-- 创建当月表
CREATE TABLE `order_202409` (
    `order_id`  BIGINT NOT NULL AUTO_INCREMENT,
    `user_id`   BIGINT NOT NULL,
    `amount`    DECIMAL(10,2) NOT NULL,
    `status`    TINYINT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`order_id`),
    KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 跨月查询：应用层需要并行查询多个分片
SELECT * FROM order_202408 WHERE user_id = 100 AND created_at >= '2024-08-01' AND created_at < '2024-09-01'
UNION ALL
SELECT * FROM order_202409 WHERE user_id = 100 AND created_at >= '2024-09-01' AND created_at < '2024-10-01';

┌─────────────────────────────────────────────┐
│       分片键 → 分片算法的映射               │
├──────────────┬──────────────────────────────┤
│ 取模分片      │ shard = key % N            │
│ 范围分片      │ shard = key / N            │
│ 一致性哈希    │ hash(key) on ring          │
│ 时间分片      │ shard = YYYYMM             │
│ 映射表分片    │ 查路由表获取 shard_id      │
└──────────────┴──────────────────────────────┘

-- 没有分片键的查询 — 需广播到所有分片再合并
SELECT COUNT(*) FROM order_0 WHERE status = 0;   -- 查询分片 0
SELECT COUNT(*) FROM order_1 WHERE status = 0;   -- 查询分片 1
-- ... 共 16 次查询，应用层汇总

-- 解决方案：
-- 1. 建立倒排索引表（如 ES）
-- 2. 存储汇总数据（日统计表）
-- 3. 中间件聚合（ShardingSphere 支持）

// 使用 Seata AT 模式实现分布式事务
@GlobalTransactional(name = "create-order", rollbackFor = Exception.class)
public void createOrder(CreateOrderRequest request) {
    // 步骤1：扣减库存（商品库）
    productService.deductStock(request.getProductId(), request.getQuantity());
    // 步骤2：创建订单（订单库）
    orderService.create(request);
    // 步骤3：扣减余额（用户库）
    userService.deductBalance(request.getUserId(), request.getAmount());
    // 任一失败则全局回滚
}

-- 方案1：雪花算法（Snowflake）
-- 结构：1bit(0) | 41bit(毫秒时间戳) | 10bit(机器ID) | 12bit(序列号)
-- 优点：趋势递增、高性能、无中心
-- 缺点：时钟回拨问题

-- 方案2：号段模式（Leaf）
CREATE TABLE `id_alloc` (
    `biz_tag`    VARCHAR(64) PRIMARY KEY COMMENT '业务标识',
    `max_id`     BIGINT NOT NULL DEFAULT 0 COMMENT '当前最大ID',
    `step`       INT NOT NULL DEFAULT 1000 COMMENT '步长',
    `version`    INT NOT NULL DEFAULT 0 COMMENT '乐观锁'
) ENGINE=InnoDB;

-- 更新号段
UPDATE `id_alloc` 
SET `max_id` = `max_id` + `step`, `version` = `version` + 1
WHERE `biz_tag` = 'order' AND `version` = 0;
-- 应用层获取 [max_id+1, max_id+step] 号段缓存到本地

应用层读写分离：

┌──────────┐   写入    ┌──────────┐
│  应用服务  │ ──────→ │  主库 Master │
│          │          └────┬─────┘
│ 数据源路由 │             │ 异步复制
│  (动态切换)│             ▼
│          │   读取    ┌──────────┐
│          │ ←────── │ 从库 Slave │
└──────────┘          └──────────┘

写入主库 → binlog → 网络传输 → 从库 relay log → SQL 线程回放
                                          ↑ 这里可能延迟数百 ms

-- 方案1：强制读主库（session 级别）
-- 对一致性要求高的请求，强制走主库
SET SESSION /* force_master */;
SELECT * FROM `order` WHERE `order_id` = 12345;  -- 走主库

-- 方案2：等待从库追上
-- 检查从库延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master 接近 0 表示已同步

-- 方案3：缓存写后数据
-- 写入时设置本地缓存，读请求优先查缓存

-- 方案4：半同步复制
-- 等待至少一个从库确认后再返回成功
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 超时回退到异步

// 业务代码中标记一致性操作
@Service
public class OrderService {

    // 创建订单后立即查询 — 必须走主库
    @Transactional
    public Order createAndGet(Order order) {
        insert(order);                          // 写入主库
        DataSourceContextHolder.set("master");  // 强制走主库
        return getById(order.getOrderId());     // 读主库
    }

    // 列表查询可以走从库
    @ReadOnly
    public List<Order> listOrders(Long userId) {
        return orderMapper.listByUserId(userId); // 走从库
    }
}

CREATE TABLE `user` (
    -- 整数类型：按需选择
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,        -- 20亿内
    `big_id`      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,     -- 极大值

    -- 字符串类型
    `username`    VARCHAR(32)  NOT NULL,           -- 可变短字符串
    `intro`       VARCHAR(255) DEFAULT '',         -- 短文本
    `bio`         TEXT,                            -- 长文本（64KB）
    `email`       VARCHAR(128) NOT NULL,

    -- 小数类型：严禁 FLOAT/DOUBLE
    `balance`     DECIMAL(10,2) NOT NULL DEFAULT 0.00,  -- 精确小数

    -- 枚举类型
    `status`      TINYINT UNSIGNED NOT NULL DEFAULT 1,  -- 0-禁用 1-正常
    `gender`      ENUM('male','female','unknown') NOT NULL DEFAULT 'unknown',

    -- 时间类型
    `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- JSON（MySQL 5.7+）
    `ext_info`    JSON DEFAULT NULL,               -- 动态扩展字段

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TINYINT vs INT vs BIGINT
-- TINYINT UNSIGNED: 0~255     —— 状态码、枚举值
-- SMALLINT UNSIGNED: 0~65535   —— 年龄、积分
-- INT UNSIGNED: 0~42亿        —— 大多数场景
-- BIGINT UNSIGNED: 极大       —— 订单ID、用户ID（分表时）

-- VARCHAR 长度选择
-- VARCHAR(32)  vs VARCHAR(255)
-- 虽然可变长，但 MySQL 内部会在内存中分配最大长度
-- 超过 255 时，行格式变化（需额外 2 字节）
-- 建议：VARCHAR(32) 够用绝不设 VARCHAR(255)

-- DECIMAL 精度
-- DECIMAL(10,2)      总长10位，小数2位 → 最大99999999.99
-- DECIMAL(18,4)      总长18位，小数4位 → 适合汇率等精度要求高的场景
-- 严禁 FLOAT/DOUBLE：浮点数不精确，会导致账目不平

-- DATETIME vs TIMESTAMP
-- DATETIME：'1000-01-01' ~ '9999-12-31'，占用 5~8 字节
-- TIMESTAMP：'1970-01-01' ~ '2038-01-19'，占用 4 字节
-- 建议统一用 DATETIME，避免 2038 年问题

1NF：字段不可再分（原子性）
2NF：非主键字段完全依赖于主键（消除部分依赖）
3NF：非主键字段不传递依赖于主键（消除传递依赖）

-- 范式设计：用户表和用户标签分别存储
CREATE TABLE `user` (
    `user_id` BIGINT PRIMARY KEY,
    `name`    VARCHAR(32)
);

CREATE TABLE `user_tag` (
    `user_id` BIGINT,
    `tag_name` VARCHAR(32),
    PRIMARY KEY (`user_id`, `tag_name`)
);

-- 查询用户的标签列表需要 JOIN
SELECT u.*, GROUP_CONCAT(ut.tag_name) AS tags
FROM user u
LEFT JOIN user_tag ut ON u.user_id = ut.user_id
WHERE u.user_id = 100;

-- 反范式设计：用户表冗余标签字段（JSON）
CREATE TABLE `user` (
    `user_id` BIGINT PRIMARY KEY,
    `name`    VARCHAR(32),
    `tags`    JSON DEFAULT NULL      -- 冗余标签 ["VIP","老用户"]
);

-- 直接查询，无需 JOIN
SELECT `user_id`, `name`, JSON_EXTRACT(`tags`, '$') AS tags
FROM `user`
WHERE `user_id` = 100;

范式化（3NF）
├─ 优点：更新一致、无冗余
├─ 缺点：查询需多表 JOIN
└─ 适用：写多读少、一致性要求高的金融场景

反范式
├─ 优点：查询快、无需 JOIN
├─ 缺点：更新不一致风险、冗余占用空间
└─ 适用：读多写少、性能敏感的报表/缓存场景

CREATE TABLE `order` (
    `order_id`   BIGINT PRIMARY KEY,
    `amount`     DECIMAL(10,2),
    `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '0-正常 1-删除',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '删除时间',
    `created_at` DATETIME NOT NULL
);

-- 软删除操作
UPDATE `order` SET `is_deleted` = 1, `deleted_at` = NOW() WHERE `order_id` = 100;

-- 查询时默认过滤
SELECT * FROM `order` WHERE `is_deleted` = 0;

-- 创建过滤索引（MySQL 8.0+ 支持部分索引）
CREATE INDEX `idx_active` ON `order` (`created_at`) WHERE `is_deleted` = 0;

CREATE TABLE `account` (
    `account_id` BIGINT PRIMARY KEY,
    `balance`    DECIMAL(10,2) NOT NULL,
    `version`    INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本号'
);

-- 更新时比较版本号
UPDATE `account` 
SET `balance` = `balance` - 100, `version` = `version` + 1
WHERE `account_id` = 100 AND `version` = 5;

-- 影响行数为 0 表示版本冲突，需重试

CREATE TABLE `audit_log` (
    `id`           BIGINT PRIMARY KEY AUTO_INCREMENT,
    `table_name`   VARCHAR(64)  NOT NULL COMMENT '操作表',
    `record_id`    BIGINT       NOT NULL COMMENT '记录ID',
    `action`       VARCHAR(16)  NOT NULL COMMENT 'INSERT/UPDATE/DELETE',
    `old_value`    JSON         DEFAULT NULL COMMENT '变更前',
    `new_value`    JSON         DEFAULT NULL COMMENT '变更后',
    `operator`     VARCHAR(64)  NOT NULL COMMENT '操作人',
    `operated_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_table_record` (`table_name`, `record_id`),
    KEY `idx_operated_at` (`operated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据变更审计日志';

-- 配合触发器自动记录
CREATE TRIGGER `trg_order_update` AFTER UPDATE ON `order` FOR EACH ROW
BEGIN
    INSERT INTO `audit_log` (`table_name`, `record_id`, `action`, `old_value`, `new_value`, `operator`)
    VALUES ('order', OLD.order_id, 'UPDATE', 
            JSON_OBJECT('status', OLD.status, 'amount', OLD.amount),
            JSON_OBJECT('status', NEW.status, 'amount', NEW.amount),
            @current_operator);
END;

-- 错误：查询所有列，无法利用覆盖索引
SELECT * FROM `order` WHERE `user_id` = 100;

-- 正确：只查询需要的列
SELECT `order_id`, `amount`, `status` FROM `order` WHERE `user_id` = 100;

-- order_id 是 VARCHAR 类型
-- 错误：传入整数会导致全表扫描
SELECT * FROM `order` WHERE `order_id` = 12345;  -- 隐式转换
-- MySQL 执行：CAST(order_id AS SIGNED) = 12345，索引失效

-- 正确：传入正确的字符串类型
SELECT * FROM `order` WHERE `order_id` = '12345';

-- 错误：函数包裹索引列，索引失效
SELECT * FROM `order` WHERE DATE(`created_at`) = '2024-09-16';
-- 全表扫描

-- 正确：使用范围查询，索引生效
SELECT * FROM `order` 
WHERE `created_at` >= '2024-09-16 00:00:00' 
  AND `created_at` <  '2024-09-17 00:00:00';

-- IN 优于 EXISTS 当外部表小、内部表大时
-- 假设 users 表 100 行，orders 表 1000 万行

-- 推荐：小表 users 驱动大表 orders
SELECT * FROM `user` 
WHERE `user_id` IN (SELECT `user_id` FROM `order` WHERE `status` = 1);
-- MySQL 会优化为：先查 order 得到 user_id 集合，再查 user

-- 不推荐：大表 orders 作为子查询驱动力
SELECT * FROM `order` 
WHERE EXISTS (SELECT 1 FROM `user` WHERE `user`.`user_id` = `order`.`user_id`);

-- 传统分页：OFFSET 越大越慢
SELECT * FROM `order` ORDER BY `id` LIMIT 10 OFFSET 1000000;
-- MySQL 需要读取 1,000,010 行后丢弃前 1,000,000 行

-- 优化方案1：游标分页（推荐）
SELECT * FROM `order` 
WHERE `id` > 1000000        -- 上次最后一条的 id
ORDER BY `id` 
LIMIT 10;

-- 优化方案2：子查询分页（MySQL）
SELECT * FROM `order` 
WHERE `id` >= (
    SELECT `id` FROM `order` ORDER BY `id` LIMIT 1 OFFSET 1000000
)
ORDER BY `id` 
LIMIT 10;

-- 优化方案3：覆盖索引子查询
SELECT o.*
FROM `order` o
JOIN (
    SELECT `id` FROM `order` ORDER BY `id` LIMIT 10 OFFSET 1000000
) tmp ON o.`id` = tmp.`id`
ORDER BY o.`id`;

-- 推荐写法：高选择性条件在前
SELECT * FROM `order` 
WHERE `user_id` = 100             -- 高选择性，筛选掉 99.99% 数据
  AND `status` = 1                -- 低选择性
  AND `amount` > 100;             -- 范围条件

-- 不推荐：低选择性条件在前
SELECT * FROM `order` 
WHERE `status` = 1                -- 低选择性
  AND `amount` > 100
  AND `user_id` = 100;

-- 原则1：关联字段必须有索引
-- 原则2：小表驱动大表
-- 原则3：避免超过 3 张表的 JOIN

-- 推荐：关联字段索引 + 小表驱动
-- orders 表 1 亿行，users 表 100 万行
-- 用 users 驱动 orders（小表驱动大表）
SELECT u.`username`, o.`order_id`, o.`amount`
FROM `user` u
INNER JOIN `order` o ON u.`user_id` = o.`user_id`   -- order.user_id 需有索引
WHERE u.`status` = 1;

-- 不推荐：用子查询模拟 JOIN 但不走索引
SELECT * FROM `order` 
WHERE `user_id` IN (SELECT `user_id` FROM `user` WHERE `status` = 1);
-- 如果子查询结果集过大，IN 可能不如 JOIN 高效

-- 错误：逐条插入（1000 条需 1000 次网络往返）
INSERT INTO `order` (`user_id`, `amount`) VALUES (1, 100);
INSERT INTO `order` (`user_id`, `amount`) VALUES (2, 200);
-- ... 重复 998 次

-- 正确：批量插入（1 次网络往返）
INSERT INTO `order` (`user_id`, `amount`) VALUES
(1, 100),
(2, 200),
-- ... 最多 1000 条一批
(1000, 999);

-- 错误：逐条更新（长事务 + 高锁开销）
-- 正确：批量更新 with CASE WHEN
UPDATE `order` SET `status` = CASE `order_id`
    WHEN 1 THEN 2
    WHEN 2 THEN 3
    WHEN 3 THEN 1
END
WHERE `order_id` IN (1, 2, 3);

-- 1. 开启慢查询日志（MySQL）
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;          -- 超过 1 秒
SET GLOBAL log_queries_not_using_indexes = 1;

-- 2. 分析慢查询
-- 使用 mysqldumpslow
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

-- 3. 使用 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM `order` WHERE `status` = 1 ORDER BY `created_at` DESC;

-- 输出关键指标解读：
-- type: ALL（全表扫描）→ 需加索引
-- possible_keys: 可用的索引
-- key: 实际使用的索引
-- rows: 扫描行数预估
-- Extra: Using filesort（需优化排序）

发现慢查询（监控告警）
       │
       ▼
记录慢 SQL（慢查询日志）
       │
       ▼
EXPLAIN 分析执行计划
       │
       ▼
定位问题（索引缺失/类型转换/扫描行数多）
       │
       ▼
优化方案（加索引/改 SQL/反范式/分库分表）
       │
       ▼
效果验证（对比执行时间与扫描行数）

┌──────────────────────────────────────┐
│           备份策略                    │
├────────────────┬─────────────────────┤
│ 全量备份        │ 每周日凌晨 3:00     │
│ 增量备份        │ 每天凌晨 3:00       │
│ binlog 备份     │ 实时（每 30 分钟）  │
│ 备份保留        │ 近 30 天           │
│ 异地备份        │ 跨机房/跨云        │
│ 恢复演练        │ 每月一次           │
└────────────────┴─────────────────────┘

# 全量备份脚本（使用 mysqldump）
#!/bin/bash
BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="backup_user"
DB_PASS="your_password"
DB_NAME="your_database"

mysqldump \
  -u${DB_USER} -p${DB_PASS} \
  --single-transaction \      # 不加锁
  --routines \                # 导出存储过程
  --triggers \                # 导出触发器
  --master-data=2 \           # 记录 binlog 位置
  --flush-logs \              # 刷新日志
  ${DB_NAME} | gzip > ${BACKUP_DIR}/full_${DATE}.sql.gz

# 只保留最近 30 天备份
find ${BACKUP_DIR} -name "full_*.sql.gz" -mtime +30 -delete

# 使用 XtraBackup 进行物理备份（适用于大库）
# 全量备份
xtrabackup --backup --target-dir=/data/backup/xtra/full/ \
  --user=root --password=root

# 增量备份
xtrabackup --backup --target-dir=/data/backup/xtra/inc1/ \
  --incremental-basedir=/data/backup/xtra/full/ \
  --user=root --password=root

                           ┌──────────────────┐
                           │     告警平台      │
                           │   (PagerDuty/    │
                           │    飞书/钉钉)     │
                           └────────┬─────────┘
                                    │
              ┌─────────────────────┼─────────────────────┐
              │                     │                     │
              ▼                     ▼                     ▼
     ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐
     │   Prometheus +   │  │     Grafana     │  │     ELK/Kibana  │
     │  MySQL Exporter  │  │  可视化看板      │  │  慢查询日志分析  │
     └────────┬─────────┘  └─────────────────┘  └─────────────────┘
              │
              ▼
        ┌──────────────────────────────────────┐
        │            监控指标                   │
        ├──────────────────────────────────────┤
        │ QPS / TPS                           │
        │ 连接数 / 活跃连接数                   │
        │ 慢查询数量（每分钟）                  │
        │ 主从延迟（Seconds_Behind_Master）     │
        │ 磁盘使用率 / 磁盘 IOPS                │
        │ InnoDB 行锁等待 / 死锁计数             │
        │ 缓存命中率（Innodb_buffer_pool_reads）│
        │ 临时表创建量                          │
        └──────────────────────────────────────┘

-- DDL 审核清单
-- ✅ 表名、字段名使用小写蛇形命名
-- ✅ 每张表必须有主键
-- ✅ 必须有 created_at / updated_at
-- ✅ 字符集统一 utf8mb4
-- ✅ 字段有 COMMENT 注释
-- ❌ 禁止在业务高峰期执行 DDL
-- ❌ 禁止使用外键约束（应用层保证）
-- ❌ 禁止直接 DROP 表（改为 RENAME + 延迟删除）

-- 安全的 DDL 发布流程
-- 步骤1：备份表
CREATE TABLE `order_bak_20240916` LIKE `order`;
INSERT INTO `order_bak_20240916` SELECT * FROM `order`;

-- 步骤2：使用 pt-online-schema-change 在线变更
pt-online-schema-change h=localhost,D=db,t=order \
  --alter "ADD COLUMN remark VARCHAR(500) DEFAULT ''" \
  --execute

-- 步骤3：灰度验证
-- 先验证一小部分流量，确认无误后全量

-- 步骤4：清理备份（保留 7 天）
-- DROP TABLE IF EXISTS `order_bak_20240916`;

-- 预估单表数据量
-- 行大小 = 各字段字节数之和 + 行开销（约 20 字节）
-- 场景：order 表 1 亿行，单行约 200 字节
-- 数据量 ≈ 1亿 × 200B / 1024³ ≈ 18.6 GB
-- 索引量 ≈ 数据量的 1.5~2 倍 ≈ 30 GB
-- 总空间 ≈ 50 GB（不含备份）

-- 磁盘空间监控
SELECT 
    table_schema,
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb,
    ROUND(SUM(data_free) / 1024 / 1024 / 1024, 2) AS free_gb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'performance_schema', 'sys')
GROUP BY table_schema;

-- 增长趋势预测
-- 日增 10 万行 × 30 天 = 月增 300 万行
-- 月增 300 万行 × 200B = 月增 600 MB 数据 + 900 MB 索引
-- 留 6 个月余量，预分配存储空间

-- 检查 InnoDB 状态
SHOW ENGINE INNODB STATUS\G

-- 查看当前活跃事务
SELECT * FROM information_schema.innodb_trx
WHERE trx_state = 'RUNNING'\G

-- 查看锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 检查表空间使用
SELECT 
    table_schema AS db,
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    ROUND(data_free / 1024 / 1024, 2) AS free_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY total_mb DESC
LIMIT 20;

-- 检查当前 QPS
SHOW GLOBAL STATUS LIKE 'Questions';
-- 等 1 秒后再次执行，差值即 QPS