9. mysql 学习笔记 - 数据库设计与优化

千度麒麟
70 阅读6分钟

1. 数据库设计与优化

1.1 数据库设计

范式与反范式

范式 是指数据库设计时遵循的规范规则，目的是消除冗余、提高数据一致性。通常分为 1NF、2NF、3NF、BCNF 等。

  • 第一范式 (1NF) ：确保每个字段只能包含单一值，即每个列都是原子性的。

  • 第二范式 (2NF) ：基于 1NF，消除部分依赖，即每个非主属性完全依赖于主键。

  • 第三范式 (3NF) ：基于 2NF，消除传递依赖，即非主属性直接依赖于主键，而不是依赖于其他非主属性。

  • BCNF（博茨-科得范式） ：在 3NF 基础上，所有的决定性属性都必须是超键。

反范式设计

在某些情况下，出于性能的考虑，可能会使用 反范式（Denormalization）。反范式设计通过冗余数据来优化查询速度，但牺牲了一定的数据一致性。

例如，在一个电商系统中，为了减少 JOIN 操作，可以将订单表和用户表中的一些字段冗余存储。

表设计

  • 选择合理的表结构：确保每个表有意义并且设计合理。

  • 分表与分库：对于非常大的数据量，可以选择 水平分表（将数据分散到多个表中）或 垂直分表（将表拆分为多个功能模块）。如：

    • 水平分表：按照时间、地理位置等字段进行拆分。

    • 垂直分表：根据表中的业务模块将其拆分，避免过多字段导致数据冗余。

字段类型

字段类型的选择：合理选择字段类型是数据库设计的基础。常见的字段类型：

  • 数值类型：使用 INT、BIGINT、DECIMAL 等。选择合适的数值类型以节省存储空间。

  • 日期与时间类型：DATE、DATETIME、TIMESTAMP 用于存储日期和时间。DATETIME 有较大的存储空间，TIMESTAMP 存储的是从 1970 年 1 月 1 日到某个时间点的秒数，适合存储时间戳。

  • 字符串类型：选择合适的长度，例如 VARCHAR、CHAR、TEXT 等。VARCHAR 根据实际内容存储长度，CHAR 固定长度，适合存储长度固定的字段。

  • 枚举与集合类型：如 ENUM、SET，适用于字段值取固定集合的情况。

1.2 索引优化

索引的创建与使用

索引是加速查询的关键工具。创建索引能显著提升查询性能，但过多的索引也会影响插入、更新和删除操作。

常见索引类型：

  • 普通索引（INDEX） ：提升查询速度。

  • 唯一索引（UNIQUE） ：确保索引字段的唯一性。

  • 主键索引（PRIMARY KEY） ：唯一且非空的索引，用于唯一标识每行数据。

  • 全文索引（FULLTEXT） ：对文本数据进行全文搜索。

创建索引的例子：

CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(name, age);

索引优化

  • 选择合适的索引类型：如有需要，可以创建复合索引，避免多个单列索引。

  • 避免过多索引：每增加一个索引，会增加写操作的开销。对于高频更新的表，不宜创建过多索引。

  • 索引覆盖：通过复合索引覆盖查询的所有字段，可以避免回表查询，提高性能。

SELECT name, age FROM users WHERE name = 'Alice';

可以通过以下方式创建复合索引：

CREATE INDEX idx_name_age ON users(name, age);

全表扫描与索引扫描

  • 全表扫描（Full Table Scan） ：当没有索引或索引无法有效利用时，MySQL 会对全表进行扫描，导致查询速度慢。

  • 索引扫描（Index Scan） ：如果查询条件能够利用索引，MySQL 会通过索引进行快速查找。

1.3 SQL 执行计划与优化

EXPLAIN 语句

EXPLAIN 语句用于分析查询执行计划，帮助你理解 MySQL 如何执行查询并找出性能瓶颈。它会展示查询的各个执行步骤，例如表的访问类型、使用的索引、扫描的行数等。

image.png

  • id：查询的标识符。多个查询时用于标识查询的顺序。

  • select_type：查询类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）。

  • table：查询涉及的表。

  • type：访问类型，常见值有：

    • ALL（全表扫描）

    • index（索引扫描）

    • range（范围扫描）

    • ref（索引查找）

  • key：使用的索引。

  • rows：MySQL 估算需要扫描的行数。

查询优化

  • 优化 SELECT 语句：

    • 避免使用 SELECT *，只查询必要的字段。

    • 使用 LIMIT 限制返回的数据行数。

    • 适当使用 JOIN 优化数据查询，避免不必要的子查询。

  • 避免不必要的子查询：子查询可能导致查询性能下降，尤其是当子查询结果集较大时。使用 JOIN 通常能更高效地处理查询。

  • 避免使用 OR：OR 会导致索引失效，特别是在多个条件存在时，尽量使用 IN 替代 OR。

不推荐：

SELECT * FROM users WHERE name = 'Alice' OR age = 30;

推荐：

SELECT * FROM users WHERE name = 'Alice' UNION SELECT * FROM users WHERE age = 30;

image.png

数据库设计与优化案例

案例 1: 设计一个用户信息表

假设你需要设计一个存储用户基本信息的表，字段包括用户ID、姓名、年龄、性别、电子邮件地址和注册时间。

表设计

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,   -- 用户ID，主键
    name VARCHAR(100) NOT NULL,          -- 用户姓名
    age INT NOT NULL,                    -- 用户年龄
    sex ENUM('male', 'female') NOT NULL, -- 用户性别
    email VARCHAR(255) UNIQUE NOT NULL,  -- 用户电子邮件
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 注册时间
);

优化索引

  • email 列使用 UNIQUE 索引，确保邮箱唯一性。

  • name 列可以考虑创建索引以加速基于姓名的查询。

CREATE INDEX idx_name ON users(name);

案例 2: 大数据量情况下的分表设计

假设某电商平台的订单表 orders 存储了大量订单数据，超过千万条记录。为了提高查询效率，可以按 订单时间 对订单表进行水平分表。

分表设计

按年份将 orders 表拆分成多个子表：

CREATE TABLE orders_2021 (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATETIME,
    total_amount DECIMAL(10, 2)
);

CREATE TABLE orders_2022 (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATETIME,
    total_amount DECIMAL(10, 2)
);

查询订单时，通过选择相应的表来提高性能：

SELECT * FROM orders_2021 WHERE user_id = 123;

案例 3: 查询优化 - 使用复合索引

假设你有一个 users 表，查询经常涉及 name 和 age 两个字段。

为 name 和 age 创建复合索引：

CREATE INDEX idx_name_age ON users(name, age);

查询时直接使用索引：

SELECT * FROM users WHERE name = 'kim';
avatar
文章
阅读
粉丝