数据库学习教程

    架构与数据类型

    安装与连接

    # macOS（Homebrew）
brew install mysql && brew services start mysql

# Windows（winget）
winget install Oracle.MySQL

# Docker（推荐用于本地练习）
docker run -d --name mysql \
  -e MYSQL_ROOT_PASSWORD=secret \
  -e MYSQL_DATABASE=demo \
  -p 3306:3306 mysql:8.4
    mysql -h 127.0.0.1 -P 3306 -u root -p demo

# 在会话内查看版本与字符集
SELECT VERSION();
SHOW VARIABLES LIKE 'character_set_%';
    Tip

    生产环境务必为应用创建独立账号并授予最小权限，不要用 root 直连：

    CREATE USER 'app'@'%' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON demo.* TO 'app'@'%';
FLUSH PRIVILEGES;

    整体架构

    MySQL 分为 Server 层与存储引擎层：

    客户端 ──► 连接器/线程池 ──► 解析器 ──► 优化器 ──► 执行器
                                                  │
                                                  ▼
                                          ┌───────────────┐
                                          │  存储引擎层    │
                                          │  InnoDB / ...  │
                                          └───────────────┘
    层级组件职责
    Server 层连接器认证、权限、连接管理
    Server 层分析/优化器语法解析、生成执行计划
    Server 层执行器调用引擎接口执行计划
    Server 层查询缓存MySQL 8 已移除
    Server 层binlog归档日志，用于复制与数据恢复
    引擎层InnoDB事务、行锁、MVCC、崩溃恢复

    InnoDB 的关键后台结构：

    • Buffer Pool：内存中的数据页缓存，读写的主要战场，命中率决定性能。
    • redo log（WAL）：顺序写、循环用，保证崩溃持久性。
    • undo log：保存旧版本，支撑回滚与 MVCC。
    • binlog：Server 层逻辑日志，用于主从复制与数据恢复。
    Warning

    修改数据时 InnoDB 是「先写 redo log（预写），后刷数据页」——这就是 WAL（Write-Ahead Logging）。理解它才能明白为什么数据库能既保证持久性又保持高吞吐。

    常用数据类型

    整数

    类型字节范围（有符号）
    TINYINT1-128 ~ 127
    SMALLINT2±3.2 万
    INT4±21 亿
    BIGINT8±9.2×10¹⁸

    BIGINT UNSIGNED 可作自增主键，别用 INT 承担可能增长的业务量。

    字符串

    • CHAR(n)：定长，适合固定长度（如国家码）。
    • VARCHAR(n)：变长，最常用；n 建议不超过必要长度。
    • TEXT / LONGTEXT：大文本，避免放进频繁查询的表。
    • 字符集统一 utf8mb4，排序规则推荐 utf8mb4_0900_ai_ci（MySQL 8）。

    时间

    类型说明
    DATE日期
    DATETIME日期时间，范围大，不受时区影响
    TIMESTAMP时间戳，范围到 2038，受时区影响
    DATETIME(3)毫秒精度

    其他

    • DECIMAL(p,s)：精确小数，金额必用。
    • JSON：原生 JSON 类型，可建虚拟列索引。
    • ENUM：枚举，省空间但改值需 DDL，谨慎使用。
    CREATE TABLE products (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku        VARCHAR(64)   NOT NULL,
    title      VARCHAR(255)  NOT NULL,
    price      DECIMAL(12,2) NOT NULL,
    attrs      JSON,
    status     TINYINT       NOT NULL DEFAULT 0,
    created_at DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    UNIQUE KEY uk_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    Tip

    AUTO_INCREMENT 在主从或多主架构下可能冲突；分库分表场景应改用雪花 ID 或号段模式生成主键。

    小结

    • MySQL = Server 层（连接、解析、优化、执行）+ 可插拔存储引擎层（InnoDB）。
    • InnoDB 通过 Buffer Pool、redo/undo log 实现高性能与崩溃恢复。
    • 类型选择遵循「精确、够用、有默认值」：金额 DECIMAL、字符集 utf8mb4、时间统一约定时区。