MariaDB TABLE结构详解与实战应用

MariaDB TABLE 基础结构

表的概念与作用

在 MariaDB 数据库中，表是数据存储的基本结构单元。它类似于一个二维表格，由行（记录）和列（字段）组成。每一行代表一个具体的实体或事件，而每一列则表示该实体或事件的某个属性。例如，在一个员工信息表中，每一行可能代表一个员工，而列则可以是员工的姓名、年龄、职位等属性。表的作用是有序地组织和管理数据，使得数据的存储、查询和操作变得更加高效和便捷。通过合理设计表结构，可以确保数据的完整性、一致性和安全性，同时也能提升数据库的性能。

表的创建语法

在 MariaDB 中，使用 CREATE TABLE 语句来创建表。其基本语法如下：

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
   ...
    [table_constraint]
);

• IF NOT EXISTS 子句是可选的，用于在表不存在时才创建表，避免创建已存在表时的错误。
• table_name 是要创建的表的名称，必须在数据库中是唯一的。
• 每一个 column 定义包含列名、数据类型以及可选的约束条件。
• table_constraint 用于定义表级别的约束，例如主键约束、外键约束等。

数据类型

1. 数值类型
   • 整数类型：
     • TINYINT：占用 1 字节，范围是 -128 到 127（有符号）或 0 到 255（无符号）。例如，用于存储小型计数器或状态码。
     • SMALLINT：占用 2 字节，有符号范围是 -32768 到 32767，无符号范围是 0 到 65535。常用于存储相对较小的整数，如商品数量。
     • MEDIUMINT：占用 3 字节，有符号范围是 -8388608 到 8388607，无符号范围是 0 到 16777215。
     • INT（或 INTEGER）：占用 4 字节，有符号范围是 -2147483648 到 2147483647，无符号范围是 0 到 4294967295。是最常用的整数类型，适用于大多数整数存储需求。
     • BIGINT：占用 8 字节，有符号范围是 -9223372036854775808 到 9223372036854775807，无符号范围是 0 到 18446744073709551615。适用于需要存储极大整数的场景，如处理天文数据或大型事务 ID。
   • 浮点数类型：
     • FLOAT：单精度浮点数，通常占用 4 字节。其精度大约为 7 位有效数字。例如，FLOAT(7,2) 表示总共有 7 位数字，其中 2 位是小数部分。适用于对精度要求不是特别高的科学计算或统计数据。
     • DOUBLE：双精度浮点数，通常占用 8 字节，精度大约为 15 位有效数字。DOUBLE(15,5) 表示总共有 15 位数字，5 位是小数部分。适用于需要更高精度的浮点数运算。
   • 定点数类型：DECIMAL（或 NUMERIC），用于存储精确的小数。例如，DECIMAL(10,2) 表示总共有 10 位数字，其中 2 位是小数部分。它在金融领域等对精度要求极高的场景中广泛应用，因为浮点数在表示某些小数时可能存在精度丢失问题，而 DECIMAL 类型可以保证精确存储。
2. 日期和时间类型
   • DATE：用于存储日期，格式为 YYYY - MM - DD，例如 2023 - 10 - 05。占用 3 字节，适用于只需要记录日期的场景，如员工入职日期、订单日期等。
   • TIME：用于存储时间，格式为 HH:MM:SS，例如 14:30:00。占用 3 字节，可用于记录事件发生的具体时间，如会议开始时间、交易时间点等。
   • DATETIME：用于存储日期和时间，格式为 YYYY - MM - DD HH:MM:SS，例如 2023 - 10 - 05 14:30:00。占用 8 字节，能完整记录一个事件的日期和时间信息，常用于记录系统日志、操作记录等。
   • TIMESTAMP：也用于存储日期和时间，格式与 DATETIME 相同，但它的取值范围较小，从 1970 - 01 - 01 00:00:00 到 2038 - 01 - 19 03:14:07。占用 4 字节，它的一个特点是会自动根据服务器时区进行转换，并且在插入或更新行时，如果没有显式指定值，它会自动设置为当前时间。常用于记录数据的变更时间。
   • YEAR：用于存储年份，占用 1 字节，取值范围是 1901 到 2155。例如，YEAR(4) 以 4 位数字表示年份，如 2023；YEAR(2) 以 2 位数字表示年份，00 - 69 表示 2000 - 2069，70 - 99 表示 1970 - 1999。
3. 字符串类型
   • 固定长度字符串：CHAR，用于存储固定长度的字符串。例如，CHAR(10) 表示无论实际存储的字符串长度是多少，都会占用 10 个字符的空间。如果存储的字符串长度小于指定长度，会在右侧填充空格。它的优点是存储和检索速度快，因为长度固定，适合存储如邮政编码、身份证号码等长度固定的信息。
   • 可变长度字符串：
     • VARCHAR：用于存储可变长度的字符串。例如，VARCHAR(100) 表示最大可以存储 100 个字符的字符串，但实际占用空间根据存储的字符串长度而定。它不会填充空格，节省存储空间，但在处理大数据量时，由于其长度可变，可能会导致存储碎片化。常用于存储文本长度不确定的信息，如文章标题、用户评论等。
     • TEXT：用于存储大量文本数据。它有不同的类型，如 TINYTEXT（最大 255 字节）、TEXT（最大 65535 字节）、MEDIUMTEXT（最大 16777215 字节）和 LONGTEXT（最大 4294967295 字节）。TEXT 类型不适合作为索引字段，因为其数据量较大，索引效率较低。常用于存储文章内容、产品描述等长文本信息。
   • 二进制字符串：
     • BINARY 和 VARBINARY：与 CHAR 和 VARCHAR 类似，不过它们存储的是二进制数据而不是字符数据。BINARY 是固定长度的二进制字符串，VARBINARY 是可变长度的二进制字符串。常用于存储图片、音频等二进制文件数据。
     • BLOB（Binary Large Object）：与 TEXT 类型类似，但用于存储二进制大对象。有 TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等不同类型，分别对应不同的最大存储容量，用于存储大型二进制数据，如图片、视频等。

约束条件

1. 主键约束（PRIMARY KEY）
   • 主键是表中的一列或多列的组合，其值能唯一标识表中的每一行。在 MariaDB 中，一个表只能有一个主键。主键约束确保了数据的唯一性和实体完整性。例如，在一个用户表中，可以将用户 ID 设为主键，这样每个用户都有唯一的标识。
   • 创建表时定义主键的语法：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

• 也可以在表级定义主键：

CREATE TABLE users (
    user_id INT,
    username VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (user_id)
);

2. 唯一约束（UNIQUE）
   • 唯一约束确保指定列或列组合的值在表中是唯一的，但与主键不同的是，唯一约束允许列中有一个 NULL 值（主键不允许 NULL 值）。例如，在用户表中，邮箱地址通常需要唯一，可以添加唯一约束。
   • 列级定义唯一约束：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

• 表级定义唯一约束：

CREATE TABLE users (
    user_id INT,
    username VARCHAR(50),
    email VARCHAR(100),
    UNIQUE (email)
);

3. 非空约束（NOT NULL）
   • 非空约束确保列中不能存储 NULL 值。例如，在订单表中，订单金额字段通常不能为空，因为没有金额的订单是不合理的。
   • 定义非空约束：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_amount DECIMAL(10,2) NOT NULL,
    order_date DATE
);

4. 外键约束（FOREIGN KEY）
   • 外键用于建立两个表之间的关联关系。它是一个表中的一列或多列，其值必须匹配另一个表中主键或唯一键的值。例如，在订单表和用户表中，订单表中的用户 ID 字段可以是外键，关联到用户表的用户 ID 主键，以确保订单所属用户是存在的。
   • 创建外键的语法：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_amount DECIMAL(10,2),
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

• 外键约束有助于维护数据的参照完整性，防止在子表（如订单表）中插入无效的外键值（不存在于父表（如用户表）中的用户 ID）。同时，在更新或删除父表中的记录时，也可以通过设置外键的级联操作来自动更新或删除子表中的相关记录，例如 ON UPDATE CASCADE ON DELETE CASCADE，表示当父表记录更新或删除时，子表中相关记录也相应更新或删除。

MariaDB TABLE 高级结构与特性

分区表

1. 分区表的概念与优势
   • 分区表是将一个大表按照某种规则分成多个较小的部分，这些部分称为分区。每个分区实际上是一个独立的物理对象，可以独立进行管理和维护。例如，一个包含多年销售数据的大表，可以按年份进行分区，每个年份的数据存储在一个单独的分区中。
   • 分区表的优势在于：
     • 性能提升：当查询只涉及部分分区的数据时，数据库可以只访问相关分区，减少 I/O 操作，提高查询性能。例如，查询某一年的销售数据，只需要访问对应年份的分区。
     • 管理方便：对于大表的维护操作，如备份、恢复、删除等，可以按分区进行，降低操作的复杂度和风险。例如，删除某一年的销售数据，只需删除对应的分区，而不需要对整个表进行操作。
     • 数据归档：可以方便地将旧数据转移到低速存储设备上，通过分区管理，不影响新数据的访问性能。
2. 分区类型
   • 范围分区：按照列的值的范围进行分区。例如，按日期范围分区，将不同时间段的数据划分到不同分区。

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

• 列表分区：按照列的值的列表进行分区。例如，按地区列表分区，不同地区的数据划分到不同分区。

CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(50),
    region VARCHAR(20)
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN ('North'),
    PARTITION p_south VALUES IN ('South'),
    PARTITION p_east VALUES IN ('East'),
    PARTITION p_west VALUES IN ('West')
);

• 哈希分区：通过对列值进行哈希运算，根据哈希结果将数据均匀分布到各个分区。适用于数据分布比较均匀，且没有明显的范围或列表特征的情况。

CREATE TABLE transactions (
    transaction_id INT,
    transaction_amount DECIMAL(10,2),
    transaction_date DATE
)
PARTITION BY HASH (transaction_id)
PARTITIONS 4;

• 键分区：与哈希分区类似，但使用的是数据库提供的键函数，而不是用户自定义的哈希函数。

CREATE TABLE logs (
    log_id INT,
    log_message TEXT,
    log_date TIMESTAMP
)
PARTITION BY KEY (log_id)
PARTITIONS 8;

临时表

1. 临时表的概念与用途
   • 临时表是一种特殊的表，它只在当前会话（连接）中存在，当会话结束时，临时表会自动被删除。临时表在处理复杂查询时非常有用，例如在进行多步计算或存储中间结果时。
   • 用途包括：
     • 存储中间结果：在复杂查询中，将中间计算结果存储在临时表中，然后再基于临时表进行进一步的查询和处理，避免重复计算，提高查询效率。
     • 隔离数据：在会话中临时存储一些数据，这些数据不会影响其他会话，保证数据的隔离性。
2. 创建与使用临时表

CREATE TEMPORARY TABLE temp_customers (
    customer_id INT,
    customer_name VARCHAR(50),
    email VARCHAR(100)
);

-- 插入数据到临时表
INSERT INTO temp_customers (customer_id, customer_name, email)
SELECT customer_id, customer_name, email
FROM customers
WHERE region = 'North';

-- 在临时表上进行查询
SELECT * FROM temp_customers;

视图

1. 视图的概念与作用
   • 视图是一个虚拟表，它并不实际存储数据，而是基于一个或多个实际表的查询结果。视图提供了一种逻辑上的表结构，允许用户以一种简单、统一的方式访问复杂的查询结果。
   • 作用包括：
     • 简化复杂查询：将复杂的多表连接查询封装成一个视图，用户只需要查询视图，而不需要编写复杂的 SQL 语句。例如，在一个包含订单表、用户表和产品表的数据库中，要查询每个订单的用户信息和产品信息，可以创建一个视图来简化查询。
     • 数据安全：通过视图可以限制用户对实际表中某些列的访问。例如，只允许用户查看订单表中的订单金额和订单日期，而不允许查看用户的敏感信息。
     • 数据独立性：当实际表的结构发生变化时，只需要修改视图的定义，而不会影响依赖视图的应用程序。
2. 创建与使用视图

-- 创建视图
CREATE VIEW order_info AS
SELECT o.order_id, u.username, p.product_name, o.order_amount
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id;

-- 使用视图
SELECT * FROM order_info;

MariaDB TABLE 实战应用

示例数据库设计

假设我们要设计一个图书馆管理系统的数据库，其中涉及以下实体和关系：

1. 图书（Books）：包含图书 ID、书名、作者、出版年份、ISBN 等信息。

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    publication_year INT,
    isbn VARCHAR(20) UNIQUE
);

2. 读者（Readers）：包含读者 ID、姓名、联系方式等信息。

CREATE TABLE readers (
    reader_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    contact_info VARCHAR(100)
);

3. 借阅记录（BorrowRecords）：记录读者借阅图书的信息，包括借阅记录 ID、图书 ID、读者 ID、借阅日期、归还日期等。这里图书 ID 和读者 ID 作为外键分别关联到图书表和读者表。

CREATE TABLE borrow_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    reader_id INT,
    borrow_date DATE NOT NULL,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
);

数据操作示例

1. 插入数据
   • 插入图书数据：

INSERT INTO books (title, author, publication_year, isbn)
VALUES ('Database Systems', 'Raghu Ramakrishnan', 2003, '978 - 0072465631');

• 插入读者数据：

INSERT INTO readers (name, contact_info)
VALUES ('John Doe', 'john.doe@example.com');

• 插入借阅记录数据：

-- 假设 book_id 为 1，reader_id 为 1
INSERT INTO borrow_records (book_id, reader_id, borrow_date)
VALUES (1, 1, '2023 - 10 - 01');

2. 查询数据
   • 查询所有图书信息：

SELECT * FROM books;

• 查询某个读者借阅的所有图书：

SELECT b.title
FROM books b
JOIN borrow_records br ON b.book_id = br.book_id
JOIN readers r ON br.reader_id = r.reader_id
WHERE r.name = 'John Doe';

3. 更新数据
   • 更新图书的出版年份：

UPDATE books
SET publication_year = 2004
WHERE title = 'Database Systems';

4. 删除数据
   • 删除某个借阅记录：

DELETE FROM borrow_records
WHERE record_id = 1;

性能优化实战

1. 索引优化
   • 在借阅记录表的 book_id 和 reader_id 列上创建索引，以加快连接查询的速度。

CREATE INDEX idx_book_id ON borrow_records(book_id);
CREATE INDEX idx_reader_id ON borrow_records(reader_id);

2. 查询优化
   • 对于复杂查询，可以使用 EXPLAIN 关键字来分析查询计划，找出性能瓶颈。例如：

EXPLAIN SELECT b.title
FROM books b
JOIN borrow_records br ON b.book_id = br.book_id
JOIN readers r ON br.reader_id = r.reader_id
WHERE r.name = 'John Doe';

• 根据 EXPLAIN 的结果，可以调整查询语句，如优化连接顺序、添加合适的索引等，以提高查询性能。

3. 分区优化
   • 如果借阅记录数据量很大，可以按借阅日期进行范围分区，例如按月份分区，提高查询特定时间段借阅记录的性能。

CREATE TABLE borrow_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    reader_id INT,
    borrow_date DATE NOT NULL,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
)
PARTITION BY RANGE (YEAR(borrow_date) * 100 + MONTH(borrow_date)) (
    PARTITION p0 VALUES LESS THAN (202301),
    PARTITION p1 VALUES LESS THAN (202302),
   ...
    PARTITION p11 VALUES LESS THAN (202401)
);

通过以上对 MariaDB TABLE 结构的详细讲解和实战应用示例，希望读者能对 MariaDB 表结构有更深入的理解，并能在实际项目中灵活运用，构建高效、稳定的数据库应用系统。