OceanBase教程FG050-OceanBase数据库设计规范与标准

本文详细介绍OceanBase数据库的设计规范与标准，帮助读者掌握OceanBase的数据库设计原则、规范和最佳实践。风哥教程参考OceanBase官方文档OceanBase8数据库设计、OceanBase8最佳实践等内容。

数据库设计规范与标准是确保数据库系统高效、稳定、可维护的重要基础，通过本文的学习，读者将掌握OceanBase的数据库设计原则、命名规范、表结构设计、索引设计、数据类型选择等方面的规范和标准，确保数据库设计的质量。

本文将详细介绍OceanBase的数据库设计规范、命名标准、表结构设计规范、索引设计规范以及最佳实践。

目录大纲

Part01-基础概念与理论知识
Part02-生产环境规划与建议
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
Part05-风哥经验总结与分享
- 5.1 数据库设计最佳实践
- 5.2 性能优化最佳实践

Part01-基础概念与理论知识

1.1 数据库设计概述

数据库设计是指根据业务需求和系统特点，设计数据库的结构和组织方式，它具有以下作用：

数据组织：合理组织和存储数据
性能优化：提高数据库的性能
数据完整性：确保数据的完整性和一致性
可维护性：提高数据库的可维护性
可扩展性：支持业务的扩展和变化

数据库设计的层次：

概念设计：抽象出业务实体及其关系
逻辑设计：设计数据库的逻辑结构
物理设计：设计数据库的物理存储结构
实施设计：实施数据库设计并进行优化

1.2 数据库设计原则

数据库设计的基本原则：

范式原则：遵循数据库设计范式，减少数据冗余
性能原则：优化数据库性能，提高查询效率
可扩展性原则：支持业务的扩展和变化
可维护性原则：提高数据库的可维护性
安全性原则：确保数据的安全性和完整性

OceanBase数据库设计的特殊原则：

分布式原则：考虑分布式环境下的数据分布
高可用原则：确保数据的高可用性
分区原则：合理设计分区策略

，风哥提示：。

兼容性原则：保持与MySQL的兼容性

1.3 数据库设计流程

数据库设计的基本流程：

需求分析：分析业务需求，确定数据模型
概念设计：设计概念模型，包括实体、属性和关系
逻辑设计：将概念模型转换为逻辑模型，设计表结构和关系
物理设计：设计物理存储结构，包括索引、分区等
实施与测试：实施数据库设计并进行测试
优化与维护：根据实际使用情况进行优化和维护

Part02-生产环境规划与建议

2.1 数据库设计规划

数据库设计规划：

业务需求分析：分析业务需求，确定数据模型
数据量估计：估计数据量大小，设计合理的存储结构
性能需求分析：分析性能需求，设计合理的索引和分区策略
扩展性规划：规划系统的扩展性，支持业务的增长
安全规划：规划数据安全措施，确保数据的安全性

2.2 命名规范建议

，学习交流加群风哥微信: itpux-com。

命名规范建议：

数据库命名：
- 使用小写字母和下划线
- 以业务名称或项目名称命名
- 长度不超过32个字符
表命名：
- 使用小写字母和下划线
- 以业务模块名称为前缀
- 使用复数形式
- 长度不超过32个字符
列命名：
- 使用小写字母和下划线
- 使用描述性名称
- 避免使用保留字
- 长度不超过32个字符
索引命名：
- 使用小写字母和下划线
- 以”idx_”为前缀
- 包含列名
- 长度不超过32个字符
存储过程/函数命名：
- 使用小写字母和下划线
- 以”sp_”或”func_”为前缀
- 包含功能描述
- 长度不超过32个字符
，学习交流加群风哥QQ113257174。

2.3 性能优化建议

性能优化建议：

表结构优化：
- 合理选择数据类型
- 避免使用NULL值
- 合理设置字段长度
- 使用合适的存储引擎
索引优化：
- 合理创建索引
- 避免过度索引
- 使用复合索引
- 定期维护索引
查询优化：
- 优化SQL语句
- 使用绑定变量
- 避免全表扫描
- 合理使用子查询
分区优化：
- 合理设计分区策略
- 选择合适的分区键
- 定期维护分区

，更多视频教程www.fgedu.net.cn。

Part03-生产环境项目实施方案

3.1 数据库设计实施

数据库设计实施步骤：

需求分析：
- 收集业务需求
- 分析数据模型
- 确定数据关系
概念设计：
- 设计实体关系图
- 确定实体属性
- 定义实体关系
逻辑设计：
- 设计表结构
- 定义字段类型
- 设置约束条件
物理设计：
- 设计索引
- 设计分区策略
- 配置存储参数
实施与测试：
- 创建数据库和表
- 插入测试数据
- 执行性能测试
，更多学习教程公众号风哥教程itpux_com。

3.2 表结构设计实施

表结构设计实施步骤：

表结构设计：
- 确定表名和字段名
- 选择数据类型
- 设置字段长度
- 设置约束条件
主键设计：
- 选择合适的主键类型
- 设置主键自增
- 确保主键的唯一性
外键设计：
- 设置外键关系
- 定义外键约束
- 确保引用完整性
索引设计：
- 创建必要的索引
- 选择合适的索引类型
- 优化索引结构
存储参数设置：
- 设置表的存储引擎
- 配置表的压缩方式
- 设置表的分区策略

3.3 索引设计实施

索引设计实施步骤：

索引需求分析：
- 分析查询需求
- 确定需要索引的列
- 评估索引的必要性
索引类型选择：
- 选择合适的索引类型
- 考虑索引的存储开销
- 评估索引的性能影响
索引创建：
- 创建单列索引
- 创建复合索引
- 设置索引的存储参数
索引维护：
- 定期重建索引
- 优化索引结构
- 删除不必要的索引
索引监控：
- 监控索引使用情况
- 分析索引性能
- 调整索引策略

Part04-生产案例与实战讲解

4.1 数据库设计实战

数据库设计实战示例：

— 1. 创建数据库
CREATE DATABASE fgedudb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
— 2. 切换到数据库
USE fgedudb;
— 3. 创建用户表
CREATE TABLE fgedu_users (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘用户ID’,
username VARCHAR(50) NOT NULL UNIQUE COMMENT ‘用户名’,
email VARCHAR(100) NOT NULL UNIQUE COMMENT ‘邮箱’,
password VARCHAR(100) NOT NULL COMMENT ‘密码’,
balance DECIMAL(10,2) DEFAULT 0.00 COMMENT ‘余额’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’用户表’;
— 4. 创建订单表
CREATE TABLE fgedu_orders (
order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘订单ID’,
user_id INT NOT NULL COMMENT ‘用户ID’,
amount DECIMAL(10,2) NOT NULL COMMENT ‘订单金额’,
status VARCHAR(20) NOT NULL COMMENT ‘订单状态’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
FOREIGN KEY (user_id) REFERENCES fgedu_users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’订单表’;
— 5. 创建产品表
CREATE TABLE fgedu_products (
product_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘产品ID’,
product_name VARCHAR(255) NOT NULL COMMENT ‘产品名称’,
price DECIMAL(10,2) NOT NULL COMMENT ‘产品价格’,
stock INT NOT NULL COMMENT ‘库存数量’,
description TEXT COMMENT ‘产品描述’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’产品表’;
— 6. 创建订单详情表
CREATE TABLE fgedu_order_items (
item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘订单详情ID’,
order_id INT NOT NULL COMMENT ‘订单ID’,
product_id INT NOT NULL COMMENT ‘产品ID’,
quantity INT NOT NULL COMMENT ‘数量’,
price DECIMAL(10,2) NOT NULL COMMENT ‘单价’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
FOREIGN KEY (order_id) REFERENCES fgedu_orders(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES fgedu_products(product_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’订单详情表’;
— 7. 创建索引
CREATE INDEX idx_user_created_at ON fgedu_users(created_at);
CREATE INDEX idx_order_user_id ON fgedu_orders(user_id);
CREATE INDEX idx_order_status ON fgedu_orders(status);
CREATE INDEX idx_product_name ON fgedu_products(product_name);
CREATE INDEX idx_order_item_order_id ON fgedu_order_items(order_id);
— 8. 查看表结构
SHOW CREATE TABLE fgedu_users;
SHOW CREATE TABLE fgedu_orders;
SHOW CREATE TABLE fgedu_products;
SHOW CREATE TABLE fgedu_order_items;

— 创建数据库
Query OK, 1 row affected (0.01 sec)

— 切换到数据库
Database changed

— 创建用户表
Query OK, 0 rows affected (0.01 sec)

— 创建订单表
Query OK, 0 rows affected (0.01 sec)

— 创建产品表
Query OK, 0 rows affected (0.01 sec)

— 创建订单详情表
Query OK, 0 rows affected (0.01 sec)

— 查看表结构
+————+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+————————+
| Table | Create Table | Encoding | Collation |
+————+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+————————+
| fgedu_users | CREATE TABLE `fgedu_users` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘用户ID’,
`username` varchar(50) NOT NULL COMMENT ‘用户名’,
`email` varchar(100) NOT NULL COMMENT ‘邮箱’,
`password` varchar(100) NOT NULL COMMENT ‘密码’,
`balance` decimal(10,2) DEFAULT ‘0.00’ COMMENT ‘余额’,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`),
KEY `idx_user_created_at` (`created_at`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘lz4_1.0’ REPLICA_NUM = 3 BLOCK_SIZE =
16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘用户表’ | utf8mb4 |
utf8mb4_general_ci |
+————+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+————————+

+————-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+————————+
| Table | Create Table | Encoding | Collation |
+————-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+————————+
| fgedu_orders | CREATE TABLE `fgedu_orders` (
`order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘订单ID’,
`user_id` int(11) NOT NULL COMMENT ‘用户ID’,
`amount` decimal(10,2) NOT NULL COMMENT ‘订单金额’,
`status` varchar(20) NOT NULL COMMENT ‘订单状态’,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (`order_id`),
KEY `idx_order_user_id` (`user_id`),
KEY `idx_order_status` (`status`),
CONSTRAINT `fgedu_orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `fgedu_users` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘lz4_1.0’ REPLICA_NUM = 3 BLOCK_SIZE =
16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘订单表’ | utf8mb4 |
utf8mb4_general_ci |
+————-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+————————+

+—————+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+———————-+————————+
| Table | Create Table | Encoding | Collation |
+—————+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+———————-+————————+
| fgedu_products | CREATE TABLE `fgedu_products` (
`product_id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘产品ID’,
`product_name` varchar(255) NOT NULL COMMENT ‘产品名称’,
`price` decimal(10,2) NOT NULL COMMENT ‘产品价格’,
`stock` int(11) NOT NULL COMMENT ‘库存数量’,
`description` text COMMENT ‘产品描述’,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (`product_id`),
KEY `idx_product_name` (`product_name`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘lz4_1.0’ REPLICA_NUM = 3 BLOCK_SIZE =
16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘产品表’ | utf8mb4 |
utf8mb4_general_ci |
+—————+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+———————-+————————+

+——————-+—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+————————+
| Table | Create Table | Encoding | Collation |
+——————-+—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+————————+
| fgedu_order_items | CREATE TABLE `fgedu_order_items` (
`item_id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘订单详情ID’,
`order_id` int(11) NOT NULL COMMENT ‘订单ID’,
`product_id` int(11) NOT NULL COMMENT ‘产品ID’,
`quantity` int(11) NOT NULL COMMENT ‘数量’,
`price` decimal(10,2) NOT NULL COMMENT ‘单价’,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
PRIMARY KEY (`item_id`),
KEY `idx_order_item_order_id` (`order_id`),
CONSTRAINT `fgedu_order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `fgedu_orders` (`order_id`) ON
DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fgedu_order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `fgedu_products`
(`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘lz4_1.0’ REPLICA_NUM = 3 BLOCK_SIZE =
16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘订单详情表’ | utf8mb4 |
utf8mb4_general_ci |
+——————-+—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+———————-+————————+

4.2 表结构设计实战

表结构设计实战示例：

— 1. 设计用户表
CREATE TABLE fgedu_users (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘用户ID’,
username VARCHAR(50) NOT NULL UNIQUE COMMENT ‘用户名’,
email VARCHAR(100) NOT NULL UNIQUE COMMENT ‘邮箱’,
password VARCHAR(100) NOT NULL COMMENT ‘密码’,
nickname VARCHAR(50) COMMENT ‘昵称’,
avatar VARCHAR(255) COMMENT ‘头像’,
gender TINYINT COMMENT ‘性别：0-未知，1-男，2-女’,
birthday DATE COMMENT ‘生日’,
phone VARCHAR(20) COMMENT ‘手机号’,
address VARCHAR(255) COMMENT ‘地址’,
balance DECIMAL(10,2) DEFAULT 0.00 COMMENT ‘余额’,
status TINYINT DEFAULT 1 COMMENT ‘状态：0-禁用，1-启用’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’用户表’;
— 2. 设计产品表
CREATE TABLE fgedu_products (
product_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘产品ID’,
product_name VARCHAR(255) NOT NULL COMMENT ‘产品名称’,
category_id INT NOT NULL COMMENT ‘分类ID’,
brand_id INT NOT NULL COMMENT ‘品牌ID’,
price DECIMAL(10,2) NOT NULL COMMENT ‘产品价格’,
original_price DECIMAL(10,2) COMMENT ‘原价’,
stock INT NOT NULL COMMENT ‘库存数量’,
sales INT DEFAULT 0 COMMENT ‘销量’,
rating DECIMAL(2,1) DEFAULT 5.0 COMMENT ‘评分’,
description TEXT COMMENT ‘产品描述’,
images TEXT COMMENT ‘产品图片’,
status TINYINT DEFAULT 1 COMMENT ‘状态：0-下架，1-上架’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’产品表’;
— 3. 设计分类表
CREATE TABLE fgedu_categories (
category_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘分类ID’,
category_name VARCHAR(100) NOT NULL COMMENT ‘分类名称’,
parent_id INT DEFAULT 0 COMMENT ‘父分类ID’,
level TINYINT DEFAULT 1 COMMENT ‘分类级别’,
sort_order INT DEFAULT 0 COMMENT ‘排序’,
status TINYINT DEFAULT 1 COMMENT ‘状态：0-禁用，1-启用’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’分类表’;
— 4. 设计品牌表
CREATE TABLE fgedu_brands (
brand_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘品牌ID’,
brand_name VARCHAR(100) NOT NULL COMMENT ‘品牌名称’,
logo VARCHAR(255) COMMENT ‘品牌 logo’,
description TEXT COMMENT ‘品牌描述’,
status TINYINT DEFAULT 1 COMMENT ‘状态：0-禁用，1-启用’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’品牌表’;
— 5. 设计订单表
CREATE TABLE fgedu_orders (
order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘订单ID’,
order_no VARCHAR(32) NOT NULL UNIQUE COMMENT ‘订单号’,
user_id INT NOT NULL COMMENT ‘用户ID’,
total_amount DECIMAL(10,2) NOT NULL COMMENT ‘总金额’,
actual_amount DECIMAL(10,2) NOT NULL COMMENT ‘实际支付金额’,
payment_method VARCHAR(20) COMMENT ‘支付方式’,
payment_time DATETIME COMMENT ‘支付时间’,
status VARCHAR(20) NOT NULL COMMENT ‘订单状态’,
shipping_address VARCHAR(255) NOT NULL COMMENT ‘收货地址’,
shipping_phone VARCHAR(20) NOT NULL COMMENT ‘收货电话’,
shipping_name VARCHAR(50) NOT NULL COMMENT ‘收货人姓名’,
tracking_no VARCHAR(50) COMMENT ‘物流单号’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
FOREIGN KEY (user_id) REFERENCES fgedu_users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’订单表’;
— 6. 设计订单详情表
CREATE TABLE fgedu_order_items (
item_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘订单详情ID’,
order_id INT NOT NULL COMMENT ‘订单ID’,
product_id INT NOT NULL COMMENT ‘产品ID’,
product_name VARCHAR(255) NOT NULL COMMENT ‘产品名称’,
product_image VARCHAR(255) NOT NULL COMMENT ‘产品图片’,
price DECIMAL(10,2) NOT NULL COMMENT ‘单价’,
quantity INT NOT NULL COMMENT ‘数量’,
subtotal DECIMAL(10,2) NOT NULL COMMENT ‘小计’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
FOREIGN KEY (order_id) REFERENCES fgedu_orders(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES fgedu_products(product_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’订单详情表’;
— 7. 创建索引
CREATE INDEX idx_user_username ON fgedu_users(username);
CREATE INDEX idx_user_email ON fgedu_users(email);
CREATE INDEX idx_user_phone ON fgedu_users(phone);
CREATE INDEX idx_product_category ON fgedu_products(category_id);
CREATE INDEX idx_product_brand ON fgedu_products(brand_id);
CREATE INDEX idx_product_name ON fgedu_products(product_name);
CREATE INDEX idx_category_parent ON fgedu_categories(parent_id);
CREATE INDEX idx_order_user ON fgedu_orders(user_id);
CREATE INDEX idx_order_no ON fgedu_orders(order_no);
CREATE INDEX idx_order_status ON fgedu_orders(status);
CREATE INDEX idx_order_item_order ON fgedu_order_items(order_id);
— 8. 查看表结构
SHOW TABLES;
DESCRIBE fgedu_users;
DESCRIBE fgedu_products;
DESCRIBE fgedu_categories;
DESCRIBE fgedu_brands;
DESCRIBE fgedu_orders;
DESCRIBE fgedu_order_items;

— 设计用户表
Query OK, 0 rows affected (0.01 sec)

— 设计产品表
Query OK, 0 rows affected (0.01 sec)

— 设计分类表
Query OK, 0 rows affected (0.01 sec)

— 设计品牌表
Query OK, 0 rows affected (0.01 sec)

— 设计订单表
Query OK, 0 rows affected (0.01 sec)

— 设计订单详情表
Query OK, 0 rows affected (0.01 sec)

— 创建索引
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

+—————-+————–+——+—–+——————-+———————————————–+
| Field | Type | Null | Key | Default | Extra |
+—————-+————–+——+—–+——————-+———————————————–+
| product_id | int(11) | NO | PRI | NULL | auto_increment |
| product_name | varchar(255) | NO | MUL | NULL | |
| category_id | int(11) | NO | MUL | NULL | |
| brand_id | int(11) | NO | MUL | NULL | |
| price | decimal(10,2) | NO | | NULL | |
| original_price | decimal(10,2) | YES | | NULL | |
| stock | int(11) | NO | | NULL | |
| sales | int(11) | YES | | 0 | |
| rating | decimal(2,1) | YES | | 5.0 | |
| description | text | YES | | NULL | |
| images | text | YES | | NULL | |
| status | tinyint(4) | YES | | 1 | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+—————-+————–+——+—–+——————-+———————————————–+

+————-+————–+——+—–+——————-+———————————————–+
| Field | Type | Null | Key | Default | Extra |
+————-+————–+——+—–+——————-+———————————————–+
| category_id | int(11) | NO | PRI | NULL | auto_increment |
| category_name | varchar(100) | NO | | NULL | |
| parent_id | int(11) | YES | MUL | 0 | |
| level | tinyint(4) | YES | | 1 | |
| sort_order | int(11) | YES | | 0 | |
| status | tinyint(4) | YES | | 1 | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+————-+————–+——+—–+——————-+———————————————–+

4.3 索引设计实战

索引设计实战示例：

— 1. 分析查询需求
— 查看慢查询日志
SHOW VARIABLES LIKE ‘%slow_query%’;
— 分析慢查询
SELECT * FROM mysql.slow_log WHERE sql_text LIKE ‘%fgedu_users%’ ORDER BY start_time DESC LIMIT 10;
— 2. 设计索引
— 为用户表创建索引
CREATE INDEX idx_user_username ON fgedu_users(username);
CREATE INDEX idx_user_email ON fgedu_users(email);
CREATE INDEX idx_user_phone ON fgedu_users(phone);
CREATE INDEX idx_user_created_at ON fgedu_users(created_at);
— 为产品表创建索引
CREATE INDEX idx_product_name ON fgedu_products(product_name);
CREATE INDEX idx_product_category ON fgedu_products(category_id);
CREATE INDEX idx_product_brand ON fgedu_products(brand_id);
CREATE INDEX idx_product_price ON fgedu_products(price);
CREATE INDEX idx_product_status ON fgedu_products(status);
— 为订单表创建索引
CREATE INDEX idx_order_user_id ON fgedu_orders(user_id);
CREATE INDEX idx_order_order_no ON fgedu_orders(order_no);
CREATE INDEX idx_order_status ON fgedu_orders(status);
CREATE INDEX idx_order_created_at ON fgedu_orders(created_at);
CREATE INDEX idx_order_payment_time ON fgedu_orders(payment_time);
— 为订单详情表创建索引
CREATE INDEX idx_order_item_order_id ON fgedu_order_items(order_id);
CREATE INDEX idx_order_item_product_id ON fgedu_order_items(product_id);
— 3. 优化复合索引
— 创建复合索引
CREATE INDEX idx_user_email_status ON fgedu_users(email, status);
CREATE INDEX idx_product_category_status ON fgedu_products(category_id, status);
CREATE INDEX idx_order_user_status ON fgedu_orders(user_id, status);
— 4. 查看索引
SHOW INDEX FROM fgedu_users;
SHOW INDEX FROM fgedu_products;
SHOW INDEX FROM fgedu_orders;
SHOW INDEX FROM fgedu_order_items;
— 5. 测试索引效果
— 测试用户表索引
EXPLAIN SELECT * FROM fgedu_users WHERE username = ‘test’;
EXPLAIN SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’;
EXPLAIN SELECT * FROM fgedu_users WHERE phone = ‘13800138000’;
EXPLAIN SELECT * FROM fgedu_users WHERE created_at > ‘2026-01-01’;
EXPLAIN SELECT * FROM fgedu_users WHERE email = ‘test@fgedu.net.cn’ AND status = 1;
— 测试产品表索引
EXPLAIN SELECT * FROM fgedu_products WHERE product_name LIKE ‘test%’;
EXPLAIN SELECT * FROM fgedu_products WHERE category_id = 1;
EXPLAIN SELECT * FROM fgedu_products WHERE brand_id = 1;
EXPLAIN SELECT * FROM fgedu_products WHERE price > 100;
EXPLAIN SELECT * FROM fgedu_products WHERE status = 1;
EXPLAIN SELECT * FROM fgedu_products WHERE category_id = 1 AND status = 1;
— 测试订单表索引
EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 1;
EXPLAIN SELECT * FROM fgedu_orders WHERE order_no = ‘202604090001’;
EXPLAIN SELECT * FROM fgedu_orders WHERE status = ‘pending’;
EXPLAIN SELECT * FROM fgedu_orders WHERE created_at > ‘2026-04-01’;
EXPLAIN SELECT * FROM fgedu_orders WHERE payment_time > ‘2026-04-01’;
EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 1 AND status = ‘pending’;
— 测试订单详情表索引
EXPLAIN SELECT * FROM fgedu_order_items WHERE order_id = 1;
EXPLAIN SELECT * FROM fgedu_order_items WHERE product_id = 1;
— 6. 维护索引
— 重建索引
ALTER TABLE fgedu_users DROP INDEX idx_user_username, ADD INDEX idx_user_username(username);
ALTER TABLE fgedu_products DROP INDEX idx_product_name, ADD INDEX idx_product_name(product_name);
ALTER TABLE fgedu_orders DROP INDEX idx_order_order_no, ADD INDEX idx_order_order_no(order_no);
— 优化表
OPTIMIZE TABLE fgedu_users;
OPTIMIZE TABLE fgedu_products;
OPTIMIZE TABLE fgedu_orders;
OPTIMIZE TABLE fgedu_order_items;

— 分析慢查询
+———————+—————————+—————–+—————+————-+——————-+———-+—————-+———–+—————+————————-+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id |
insert_id | server_id | sql_text |
+———————+—————————+—————–+—————+————-+——————-+———-+—————-+———–+—————+————————-+
| 2026-04-09 10:00:00 | root[root] @ localhost [] | 1.234567 | 0.000000 | 1 | 10000 | fgedudb | 0 | 0 |
1 | SELECT * FROM fgedu_users WHERE username = ‘test’ |
| 2026-04-09 10:01:00 | root[root] @ localhost [] | 0.987654 | 0.000000 | 10 | 10000 | fgedudb | 0 | 0 |
1 | SELECT * FROM fgedu_products WHERE category_id = 1 |
+———————+—————————+—————–+—————+————-+——————-+———-+—————-+———–+—————+————————-+

— 设计索引
— 为用户表创建索引
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

— 为产品表创建索引
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

— 为订单表创建索引
Qu

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html