如何通过MySQL数据库表设计案例掌握高效的表设计技巧？

MySQL数据库表设计案例_表设计

背景介绍

在当今信息化社会中，数据是企业的重要资产之一，数据库作为数据存储和管理的核心工具，其设计质量直接影响系统的性能、稳定性和可扩展性，本文将通过一个电子商务网站的实例，详细讲解如何进行MySQL数据库表设计。

需求分析

电子商务网站是一种典型的互联网应用，常见的功能包括用户注册登录、商品浏览、购物车、订单管理等，以下是一些基本的需求说明：

用户信息管理：支持用户注册、登录、修改个人信息。

商品信息管理：支持商品的添加、删除、修改和查询。

订单管理：支持订单的创建、支付、取消和查询。

评论系统：用户可以对商品进行评论。

准备工作

在开始设计之前，我们需要创建一个名为shop的数据库，并选择该数据库作为操作对象，具体SQL语句如下：

CREATE DATABASE shop;
USE shop;

为了区分不同的表，我们将所有表名加上前缀sh_，用户表命名为sh_user。

表结构设计

1. 商品分类表 (sh_goods_category)

用于保存商品分类信息，支持多级分类嵌套。

字段名	类型	约束	备注
id	INT UNSIGNED	PRIMARY KEY AUTO_INCREMENT	主键
parent_id	INT UNSIGNED		上级分类ID
name	VARCHAR(100)	NOT NULL	分类名称
sort	INT UNSIGNED	NOT NULL DEFAULT 0	同级排序
is_show	TINYINT UNSIGNED	NOT NULL DEFAULT 1	是否显示
create_time	INT UNSIGNED	NOT NULL	创建时间
update_time	INT UNSIGNED	NOT NULL	更新时间

创建表SQL语句：

CREATE TABLE sh_goods_category (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    parent_id INT UNSIGNED,
    name VARCHAR(100) NOT NULL,
    sort INT UNSIGNED NOT NULL DEFAULT 0,
    is_show TINYINT UNSIGNED NOT NULL DEFAULT 1,
    create_time INT UNSIGNED NOT NULL,
    update_time INT UNSIGNED NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES sh_goods_category(id)
);

商品表 (`sh_goods`)

用于保存商品详细信息。

字段名	类型	约束	备注
id	INT UNSIGNED	PRIMARY KEY AUTO_INCREMENT	主键
category_id	INT UNSIGNED	NOT NULL	分类ID
name	VARCHAR(255)	NOT NULL	商品名称
subtitle	VARCHAR(255)		商品副标题
price	DECIMAL(10,2)	NOT NULL	价格
original_price	DECIMAL(10,2)	NOT NULL	原价
pic_url	VARCHAR(255)		图片URL
is_on_sale	TINYINT UNSIGNED	NOT NULL DEFAULT 1	是否上架
create_time	INT UNSIGNED	NOT NULL	创建时间
update_time	INT UNSIGNED	NOT NULL	更新时间

创建表SQL语句：

CREATE TABLE sh_goods (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    category_id INT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    subtitle VARCHAR(255),
    price DECIMAL(10,2) NOT NULL,
    original_price DECIMAL(10,2) NOT NULL,
    pic_url VARCHAR(255),
    is_on_sale TINYINT UNSIGNED NOT NULL DEFAULT 1,
    create_time INT UNSIGNED NOT NULL,
    update_time INT UNSIGNED NOT NULL,
    FOREIGN KEY (category_id) REFERENCES sh_goods_category(id)
);

3. 商品规格表 (sh_goods_spec)

用于保存商品的规格信息。

字段名	类型	约束	备注
id	INT UNSIGNED	PRIMARY KEY AUTO_INCREMENT	主键
goods_id	INT UNSIGNED	NOT NULL	商品ID
spec_name	VARCHAR(100)	NOT NULL	规格名称
value	VARCHAR(50)		规格值
create_time	INT UNSIGNED	NOT NULL	创建时间
update_time	INT UNSIGNED	NOT NULL	更新时间

创建表SQL语句：

CREATE TABLE sh_goods_spec (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_id INT UNSIGNED NOT NULL,
    spec_name VARCHAR(100) NOT NULL,
    value VARCHAR(50),
    create_time INT UNSIGNED NOT NULL,
    update_time INT UNSIGNED NOT NULL,
    FOREIGN KEY (goods_id) REFERENCES sh_goods(id)
);

4. 商品属性表 (sh_goods_attr)

用于保存商品的属性信息。

字段名	类型	约束	备注
id	INT UNSIGNED	PRIMARY KEY AUTO_INCREMENT	主键
goods_id	INT UNSIGNED	NOT NULL	商品ID
attr_key	VARCHAR(100)	NOT NULL	属性名
attr_value	VARCHAR(100)	NOT NULL	属性值
create_time	INT UNSIGNED	NOT NULL	创建时间
update_time	INT UNSIGNED	NOT NULL	更新时间

创建表SQL语句：

CREATE TABLE sh_goods_attr (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_id INT UNSIGNED NOT NULL,
    attr_key VARCHAR(100) NOT NULL,
    attr_value VARCHAR(100) NOT NULL,
    create_time INT UNSIGNED NOT NULL,
    update_time INT UNSIGNED NOT NULL,
    FOREIGN KEY (goods_id) REFERENCES sh_goods(id)
);

用户表 (`sh_user`)

用于保存用户的基本信息。

字段名	类型	约束	备注
id	INT UNSIGNED	PRIMARY KEY AUTO_INCREMENT	主键
user_name	VARCHAR(50)	NOT NULL	用户名
passwd	VARCHAR(50)	NOT NULL	密码
email	VARCHAR(100)		邮箱
address	VARCHAR(255)		地址
create_time	INT UNSIGNED	NOT NULL	创建时间
update_time	INT UNSIGNED	NOT NULL	更新时间

创建表SQL语句：

CREATE TABLE sh_user (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(50) NOT NULL,
    passwd VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    address VARCHAR(255),
    create_time INT UNSIGNED NOT NULL,
    update_time INT UNSIGNED NOT NULL,
);

6. 评论表 (sh_comment)

用于保存用户对商品的评论信息。

字段名	类型	约束	备注
id	INT UNSIGNED	PRIMARY KEY AUTO_INCREMENT	主键
goods_id	INT UNSIGNED	NOT NULL	商品ID
user_id	INT UNSIGNED	NOT NULL	用户ID
content	TEXT	NOT NULL	评论内容
create_time	INT UNSIGNED	NOT NULL	创建时间
update_time	INT UNSIGNED	NOT NULL	更新时间

创建表SQL语句：

CREATE TABLE sh_comment (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    content TEXT NOT NULL,
    create_time INT UNSIGNED NOT NULL,
    update_time INT UNSIGNED NOT NULL,
    FOREIGN KEY (goods_id) REFERENCES sh_goods(id),
    FOREIGN KEY (user_id) REFERENCES sh_user(id)
);

SQL设计与性能优化技巧：索引与查询优化

索引设计原则：

主键索引：每个表都应该有一个主键，InnoDB引擎会自动为具有AUTO_INCREMENT属性的主键生成索引，如用户表的主键id。

唯一索引：确保某些列的值在整个表中是唯一的，例如用户名、邮箱等，如用户表中的user_name和email字段可以设置唯一索引。

普通索引：经常用于搜索条件的列应该创建普通索引，例如商品表中的category_id和is_on_sale字段，对于经常需要排序的列，也应考虑建立索引，如商品分类表中的sort字段。

复合索引：如果经常同时根据多个列进行查询，可以考虑建立复合索引，在评论表中，如果经常根据goods_id和user_id进行查询，可以建立一个包含这两个字段的复合索引。

覆盖索引：选择索引列时，尽量让索引包含所有查询所需的列，这样可以避免回表查询，提高查询效率，在商品表中，如果经常需要查询商品的id、name和price，可以在这三个字段上建立复合索引。

避免冗余索引：不要为相同的查询条件创建多个重复的索引，这会增加维护成本并降低写入性能，在商品表中，如果已经为category_id建立了索引，就不需要再为category_id和其他字段的组合建立索引。

查询优化技巧：

使用合适的数据类型：选择合适的数据类型可以减少存储空间，提高查询效率，对于整数类型的数据，可以使用INT而不是BIGINT；对于字符串类型的数据，可以根据实际长度选择合适的VARCHAR长度，如商品名称通常不会超过255个字符，因此可以使用VARCHAR(255)。

规范化设计：通过范式化减少数据冗余和依赖，提高数据的一致性，但过度的范式化可能导致频繁的JOIN操作，影响查询性能，需要在范式化和反范式化之间找到平衡点，商品分类表采用了树形结构，避免了过多的JOIN操作。

**避免SELECT尽量只选择需要的列，避免使用SELECT，以减少数据传输量，在查询商品信息时，如果只需要商品的id和name，可以使用SELECT id, name FROM sh_goods。

使用LIMIT限制返回结果集：当查询结果非常大时，使用LIMIT限制返回的记录数，以提高查询速度，在分页查询中，可以使用LIMIT来限制每页显示的记录数，如SELECT * FROM sh_goods ORDER BY create_time DESC LIMIT 10。

合理使用缓存：对于不经常变化的数据，可以使用缓存技术，如Memcached或Redis，减少数据库的压力，对于热门商品的销量排行榜，可以将结果缓存到Redis中，每次查询时直接从缓存中读取，而不是每次都查询数据库，这样可以大大减轻数据库的负担，提高系统的响应速度，还可以设置合理的过期时间，确保数据的实时性和准确性，可以设置缓存的过期时间为3600秒（即1小时），这样即使数据发生变化，也能在一个小时后自动更新缓存，具体的缓存策略还需要根据实际情况进行调整和优化，以达到最佳的效果。