MariaDB 数据库与表

数据库管理

数据库概述

数据库是表的集合，用于组织和管理相关的数据。在MariaDB中，每个数据库都有自己的表、索引、视图和权限设置。

创建数据库

使用CREATE DATABASE语句创建数据库：

sql

CREATE DATABASE database_name;

或指定字符集和校对规则：

sql

CREATE DATABASE database_name
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

查看数据库

使用以下命令查看所有数据库：

sql

SHOW DATABASES;

查看当前数据库：

sql

SELECT DATABASE();

选择数据库

使用USE语句选择要使用的数据库：

sql

USE database_name;

修改数据库

修改数据库的字符集和校对规则：

sql

ALTER DATABASE database_name
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

删除数据库

使用DROP DATABASE语句删除数据库：

sql

DROP DATABASE database_name;

注意：删除数据库会永久删除所有包含的数据和对象，操作不可逆。

表设计与优化

表设计原则

遵循第三范式（3NF）：
- 表中的每一列都应该直接依赖于主键
- 避免数据冗余
- 提高数据一致性
选择合适的数据类型：
- 使用最小的数据类型存储数据
- 优先使用整数类型存储数值
- 使用VARCHAR代替CHAR存储可变长度字符串
- 对日期和时间使用专用数据类型
设计合理的主键：
- 主键应该唯一标识表中的每一行
- 优先使用整数类型作为主键
- 考虑使用自增主键
- 避免使用复合主键（除非必要）
设计适当的索引：
- 为经常查询的列创建索引
- 为JOIN操作的连接列创建索引
- 为WHERE子句中频繁使用的列创建索引
- 避免过度索引

数据类型选择

数值类型

数据类型	存储大小	范围	适用场景
TINYINT	1字节	-128 到 127	小整数，如状态码、标志位
SMALLINT	2字节	-32768 到 32767	中等整数，如数量、年龄
MEDIUMINT	3字节	-8388608 到 8388607	较大整数，如ID
INT	4字节	-2147483648 到 2147483647	常用整数，如ID、数量
BIGINT	8字节	-9223372036854775808 到 9223372036854775807	极大整数，如大表ID、时间戳
DECIMAL(M,D)	可变	取决于M和D	精确小数，如金额、价格
FLOAT	4字节	单精度浮点数	近似小数，如科学计算
DOUBLE	8字节	双精度浮点数	高精度近似小数

字符串类型

数据类型	存储大小	适用场景
CHAR(N)	N字节	固定长度字符串，如密码哈希
VARCHAR(N)	可变，1-2字节前缀+实际长度	可变长度字符串，如姓名、地址
TEXT	可变，最大65,535字节	长文本，如描述、内容
MEDIUMTEXT	可变，最大16,777,215字节	中等长度文本，如文章内容
LONGTEXT	可变，最大4,294,967,295字节	超长文本，如大型文档
BLOB	可变，最大65,535字节	二进制数据，如图片缩略图
MEDIUMBLOB	可变，最大16,777,215字节	中等二进制数据
LONGBLOB	可变，最大4,294,967,295字节	大型二进制数据，如视频文件

日期和时间类型

数据类型	存储大小	格式	适用场景
DATE	3字节	YYYY-MM-DD	日期，如生日、订单日期
TIME	3字节	HH:MM:SS	时间，如事件时间、持续时间
DATETIME	8字节	YYYY-MM-DD HH:MM:SS	日期和时间，如创建时间、更新时间
TIMESTAMP	4字节	YYYY-MM-DD HH:MM:SS	时间戳，自动更新，如最后修改时间
YEAR	1字节	YYYY	年份，如发布年份

创建表

使用CREATE TABLE语句创建表：

sql

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

查看表结构

使用以下命令查看表结构：

sql

DESCRIBE table_name;

或：

sql

SHOW CREATE TABLE table_name;

修改表结构

使用ALTER TABLE语句修改表结构：

添加列

sql

ALTER TABLE users ADD COLUMN age INT;

修改列

sql

ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED;

删除列

sql

ALTER TABLE users DROP COLUMN age;

添加索引

sql

ALTER TABLE users ADD INDEX idx_status (status);

修改表名

sql

ALTER TABLE old_table_name RENAME TO new_table_name;

删除表

使用DROP TABLE语句删除表：

sql

DROP TABLE table_name;

复制表

复制表结构

sql

CREATE TABLE new_table LIKE old_table;

复制表结构和数据

sql

CREATE TABLE new_table SELECT * FROM old_table;

或：

sql

INSERT INTO new_table SELECT * FROM old_table;

表操作

插入数据

使用INSERT INTO语句插入数据：

插入单行

sql

INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'password123');

插入多行

sql

INSERT INTO users (username, email, password) 
VALUES 
  ('john_doe', 'john@example.com', 'password123'),
  ('jane_smith', 'jane@example.com', 'password456'),
  ('bob_johnson', 'bob@example.com', 'password789');

从其他表插入

sql

INSERT INTO new_users (username, email) 
SELECT username, email FROM old_users WHERE status = 1;

查询数据

使用SELECT语句查询数据：

基本查询

sql

SELECT id, username, email FROM users;

条件查询

sql

SELECT * FROM users WHERE status = 1 AND created_at > '2023-01-01';

排序

sql

SELECT * FROM users ORDER BY created_at DESC;

分页

sql

SELECT * FROM users LIMIT 10 OFFSET 20;

聚合查询

sql

SELECT status, COUNT(*) as user_count FROM users GROUP BY status;

更新数据

使用UPDATE语句更新数据：

sql

UPDATE users SET status = 0 WHERE id = 1;

或更新多行：

sql

UPDATE users SET status = 0 WHERE last_login < '2023-01-01';

删除数据

使用DELETE语句删除数据：

sql

DELETE FROM users WHERE id = 1;

或删除多行：

sql

DELETE FROM users WHERE status = 0 AND created_at < '2023-01-01';

截断表

使用TRUNCATE TABLE语句删除表中所有数据：

sql

TRUNCATE TABLE users;

注意：TRUNCATE TABLE比DELETE更快，但会重置自增计数器，且不能回滚。

表优化

优化表

使用OPTIMIZE TABLE语句优化表：

sql

OPTIMIZE TABLE users;

作用：

回收未使用的空间
优化数据文件和索引结构
提高查询性能

检查表

使用CHECK TABLE语句检查表的完整性：

sql

CHECK TABLE users;

修复表

使用REPAIR TABLE语句修复损坏的表：

sql

REPAIR TABLE users;

注意：主要用于MyISAM和Aria存储引擎，InnoDB通常不需要修复。

分析表

使用ANALYZE TABLE语句更新表的统计信息：

sql

ANALYZE TABLE users;

作用：

更新表的统计信息
帮助查询优化器生成更好的执行计划

分区表

分区表概述

分区表是将一个大表分成多个小表，每个分区存储表的一部分数据。分区表可以提高查询性能，便于管理和维护。

分区类型

范围分区

根据列值的范围进行分区：

sql

CREATE TABLE orders (
  id INT AUTO_INCREMENT,
  order_date DATE,
  total_amount DECIMAL(10,2),
  PRIMARY KEY (id, order_date)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN MAXVALUE
);

列表分区

根据列值的列表进行分区：

sql

CREATE TABLE sales (
  id INT AUTO_INCREMENT,
  region VARCHAR(20),
  amount DECIMAL(10,2),
  PRIMARY KEY (id, region)
) ENGINE=InnoDB
PARTITION BY LIST COLUMNS (region) (
  PARTITION p_north VALUES IN ('North', 'Northeast'),
  PARTITION p_south VALUES IN ('South', 'Southeast'),
  PARTITION p_west VALUES IN ('West', 'Southwest'),
  PARTITION p_central VALUES IN ('Central', 'Midwest')
);

哈希分区

根据列值的哈希值进行分区：

sql

CREATE TABLE users (
  id INT AUTO_INCREMENT,
  username VARCHAR(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB
PARTITION BY HASH (id) PARTITIONS 4;

键分区

根据主键或唯一键的哈希值进行分区：

sql

CREATE TABLE products (
  id INT AUTO_INCREMENT,
  name VARCHAR(100),
  PRIMARY KEY (id)
) ENGINE=InnoDB
PARTITION BY KEY () PARTITIONS 8;

分区表管理

查看分区

sql

SHOW CREATE TABLE orders;

或：

sql

SELECT * FROM information_schema.partitions 
WHERE table_schema = 'database_name' AND table_name = 'orders';

添加分区

sql

ALTER TABLE orders ADD PARTITION (
  PARTITION p2025 VALUES LESS THAN (2026)
);

删除分区

sql

ALTER TABLE orders DROP PARTITION p2021;

合并分区

sql

ALTER TABLE orders REORGANIZE PARTITION p2022, p2023 INTO (
  PARTITION p2022_2023 VALUES LESS THAN (2024)
);

常见问题

如何选择合适的数据类型？

数值：使用最小的整数类型，精确小数使用DECIMAL
字符串：可变长度使用VARCHAR，固定长度使用CHAR
日期和时间：根据精度需求选择DATE、TIME、DATETIME或TIMESTAMP
二进制数据：根据大小选择BLOB、MEDIUMBLOB或LONGBLOB

如何设计合理的主键？

优先使用整数类型
考虑使用自增主键
避免使用复合主键（除非必要）
主键应该稳定，不经常变化
主键应该唯一标识表中的每一行

什么时候使用分区表？

表大小超过10GB
查询经常访问表的一部分数据
需要快速删除历史数据
需要提高查询性能
需要更好的管理和维护

如何优化大表？

使用分区表
合理设计索引
优化查询语句
考虑分库分表
定期清理无用数据
使用合适的存储引擎

如何备份表？

使用mysqldump命令备份表：mysqldump -u user -p database_name table_name > table_name.sql
使用SELECT INTO OUTFILE导出表数据：SELECT * INTO OUTFILE '/path/to/file.csv' FROM table_name
使用物理备份工具（如mariabackup）

最佳实践

合理设计表结构：
- 遵循第三范式
- 选择合适的数据类型
- 设计合理的主键和索引
优化插入性能：
- 使用批量插入
- 关闭自动提交（对于大量插入）
- 禁用外键检查（临时）
- 禁用唯一性检查（临时）
优化查询性能：
- 只查询需要的列
- 使用索引优化查询
- 避免在WHERE子句中使用函数
- 优化JOIN操作
定期维护表：
- 定期优化表
- 更新表统计信息
- 检查和修复表（如果需要）
- 监控表大小和增长趋势
使用分区表：
- 对于大表，考虑使用分区表
- 选择合适的分区类型
- 定期管理分区
备份表数据：
- 定期备份表数据
- 测试备份恢复
- 存储备份在安全的位置
监控表性能：
- 监控慢查询
- 分析查询执行计划
- 监控表锁和等待
- 监控表空间使用情况

结论

数据库和表是MariaDB的核心组件，合理设计和管理数据库和表对于提高数据库性能、可靠性和可维护性至关重要。通过选择合适的数据类型、设计合理的表结构、优化查询和定期维护，可以确保MariaDB在生产环境中稳定高效地运行。

MariaDB 数据库与表 ​

数据库管理 ​

数据库概述 ​

创建数据库 ​

查看数据库 ​

选择数据库 ​

修改数据库 ​

删除数据库 ​

表设计与优化 ​

表设计原则 ​

数据类型选择 ​

数值类型 ​

字符串类型 ​

日期和时间类型 ​

创建表 ​

查看表结构 ​

修改表结构 ​

添加列 ​

修改列 ​

删除列 ​

添加索引 ​

修改表名 ​

删除表 ​

复制表 ​

复制表结构 ​

复制表结构和数据 ​

表操作 ​

插入数据 ​

插入单行 ​

插入多行 ​

从其他表插入 ​

查询数据 ​

基本查询 ​

条件查询 ​

排序 ​

分页 ​

聚合查询 ​

更新数据 ​

删除数据 ​

截断表 ​

表优化 ​

优化表 ​

检查表 ​

修复表 ​

分析表 ​

分区表 ​

分区表概述 ​

分区类型 ​

范围分区 ​

列表分区 ​

哈希分区 ​

键分区 ​

分区表管理 ​

查看分区 ​

添加分区 ​

删除分区 ​

合并分区 ​

常见问题 ​

如何选择合适的数据类型？ ​

如何设计合理的主键？ ​

什么时候使用分区表？ ​

如何优化大表？ ​

如何备份表？ ​

最佳实践 ​

结论 ​