如何通过实际案例掌握MySQL数据库的应用与优化？

MySQL数据库案例

背景介绍

电子商务网站是一种在互联网上开展电子商务的平台，常见的电子商务网站有淘宝、天猫、京东、亚马逊等，电子商务网站最基本的功能是在线购物，它分为前台和后台，后台面向网站的运营人员，用于录入数据，前台面向访问网站的用户，用于选购商品，下面通过表1列举电子商务网站中的一些常见需求。

表1 电子商务网站需求说明

序号	需求描述	备注
1	用户注册登录
2	商品分类浏览
3	商品详情查看
4	商品搜索
5	购物车管理
6	订单生成和管理
7	用户评论和评分
8	用户信息管理
9	管理员权限管理

数据库设计

在了解数据库设计的基本流程、规范以后，为了学以致用，本节将以电子商务网站为例，演示如何进行数据库设计，通过实际操作，加深对数据库设计的理解。

准备工作

在MySQL中创建一个shop数据库，用来保存电子商务网站中的数据，在创建数据库后，使用USE shop选择数据库，具体SQL语句如下。

 创建数据库
CREATE DATABASE shop;
 使用数据库
USE shop;

在对数据表命名时，为了更好地区分项目，为所有的数据表加上“sh_”前缀，商品表命名为“sh_goods”。

商品分类表

商品分类表用于保存分类名称、分类排序是否显示等信息，并要求支持如图2所示的多级分类嵌套。

图2 分类结构图

在图2中，商品分类是树形结构，父分类和子分类是一对多的关系，接下来通过表2展示商品分类表的具体结构。

表2 商品分类表（sh_goods_category）

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

小提示:

(1)对于is_show字段，其数据类型选择TINYINT而不是ENUM，这是考虑到需求将来有可能会发生变化，ENMU类型在增加枚举值时需要修改表结构，而TINYINT类型不需要修改表结构。

(2)如果父分类的is_show值为0，则该分类和它的子分类都不会显示，子分类必须满足自身is_show和父分类is_show都为1时才会显示。

(3)对于create_time和update_time字段，其数据类型选择INT而不是TIMESTAMP，这是因为应用程序中习惯使用时间戳来保存时间，直接保存为INT类型会更加方便。

下面根据表2所示的表结构，创建商品分类表，具体SQL语句如下。

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

商品表

商品表用于保存商品的基本信息，如商品名称、价格、库存数量、所属分类等，接下来通过表3展示商品表的具体结构。

表3 商品表（sh_goods）

字段名	类型	约束	备注
id	INT UNSIGNED	PRIMARY KEY	主键
category_id	INT UNSIGNED	FOREIGN KEY	所属分类ID
name	VARCHAR(255)	NOT NULL	商品名称
price	DECIMAL(10,2)		商品价格
stock	INT UNSIGNED		库存数量
sale	INT UNSIGNED		销量
create_time	INT		创建时间戳
update_time	INT		更新时间戳

根据表3所示的表结构，创建商品表，具体SQL语句如下。

CREATE TABLE sh_goods (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    category_id INT UNSIGNED,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2),
    stock INT UNSIGNED,
    sale INT UNSIGNED,
    create_time INT,
    update_time INT,
    FOREIGN KEY (category_id) REFERENCES sh_goods_category(id)
);

商品规格表

商品规格表用于保存商品的规格信息，如颜色、尺寸等，接下来通过表4展示商品规格表的具体结构。

表4 商品规格表（sh_goods_specification）

字段名	类型	约束	备注
id	INT UNSIGNED	PRIMARY KEY	主键
goods_id	INT UNSIGNED	FOREIGN KEY	所属商品ID
spec_name	VARCHAR(50)		规格名称
spec_value	VARCHAR(50)		规格值

根据表4所示的表结构，创建商品规格表，具体SQL语句如下。

CREATE TABLE sh_goods_specification (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_id INT UNSIGNED,
    spec_name VARCHAR(50),
    spec_value VARCHAR(50),
    FOREIGN KEY (goods_id) REFERENCES sh_goods(id)
);

商品属性表

商品属性表用于保存商品的详细属性信息，如品牌、产地等，接下来通过表5展示商品属性表的具体结构。

表5 商品属性表（sh_goods_attribute）

字段名	类型	约束	备注
id	INT UNSIGNED	PRIMARY KEY	主键
goods_id	INT UNSIGNED	FOREIGN KEY	所属商品ID
attribute_name	VARCHAR(100)		属性名称
attribute_value	TEXT		属性值

根据表5所示的表结构，创建商品属性表，具体SQL语句如下。

CREATE TABLE sh_goods_attribute (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_id INT UNSIGNED,
    attribute_name VARCHAR(100),
    attribute_value TEXT,
    FOREIGN KEY (goods_id) REFERENCES sh_goods(id)
);

用户表

用户表用于保存用户的基本信息，如用户名、密码、邮箱等，接下来通过表6展示用户表的具体结构。

表6 用户表（sh_user）

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

根据表6所示的表结构，创建用户表，具体SQL语句如下。

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

评论表

评论表用于保存用户对商品的评价信息，接下来通过表7展示评论表的具体结构。

表7 评论表（sh_comment）

字段名	类型	约束	备注
id	INT UNSIGNED	PRIMARY KEY	主键
user_id	INT UNSIGNED	FOREIGN KEY	所属用户ID
goods_id	INT UNSIGNED	FOREIGN KEY	所属商品ID
content	TEXT		评论内容
score	TINYINT		评分
create_time	INT		创建时间戳
update_time	INT		更新时间戳

根据表7所示的表结构，创建评论表，具体SQL语句如下。

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

SQL查询示例与分析：电商数据库实战指南

**1. 需求分析

电子商务网站是一种在互联网上开展电子商务的平台，常见的电子商务网站有淘宝、天猫、京东、亚马逊等，电子商务网站最基本的功能是在线购物，它分为前台和后台，后台面向网站的运营人员，用于录人数据，前台面向访问网站的用户，用于选购商品，下面通过表1列举电子商务网站中的一些常见需求。

表1电子商务网站需求说明如下：

用户注册登录：允许用户创建账户并登录网站，用户信息应包括用户名、密码、邮箱等基本信息，还需考虑密码加密存储以提高安全性，为了保证用户体验，可以添加第三方登录功能（如微信、QQ登录），此功能可以通过用户表（sh_user）来实现，用户表包含以下关键字段：用户ID（主键）、用户名、密码、邮箱、注册时间、最后登录时间等，密码需要进行加密处理后再存储到数据库中，为了提高查询效率，可以在用户名字段上建立索引，还可以考虑使用Redis缓存常用数据，以减少数据库的压力，对于第三方登录，可以通过关联表来存储第三方账号信息，当用户登录时，系统会根据用户名或第三方账号信息验证身份，成功后返回相应的用户信息，为了确保安全，建议定期检查并更新密码策略，防止弱密码的使用，可以通过设置验证码来防止自动化攻击，还可以记录用户的登录历史，以便后续分析和监控异常行为，通过这些措施，可以有效保障用户账户的安全性和系统的稳定运行。

商品分类浏览：提供商品分类的功能，让用户可以根据分类浏览商品，分类数据应包括分类ID、分类名称、上级分类ID等信息，为了支持多级分类，可以使用递归查询的方式获取分类树状结构，此功能可以通过商品分类表（sh_goods_category）来实现，商品分类表包含以下关键字段：分类ID（主键）、分类名称、上级分类ID、排序字段、显示状态、创建时间和更新时间等，上级分类ID用于表示当前分类的父级分类，从而实现多级分类结构，排序字段用于控制同级分类的显示顺序，显示状态用于控制分类是否显示在前端页面，创建时间和更新时间用于记录分类的新增和修改时间，为了提高查询效率，可以在分类ID和上级分类ID字段上建立索引，还可以考虑使用缓存技术，将常用的分类数据存储在内存中，以加快访问速度，对于多级分类的递归查询，可以使用递归算法或队列方式来实现，通过这些优化措施，可以提升分类浏览的性能和用户体验。

商品详情查看：展示商品的详细信息，包括商品名称、图片、价格、库存、销量等，还需要显示商品的规格和属性信息，此功能可以通过商品表（sh_goods）、商品规格表（sh_goods_specification）和商品属性表（sh_goods_attribute）来实现，商品表包含以下关键字段：商品ID（主键）、分类ID（外键）、商品名称、价格、库存、销量、创建时间和更新时间等，分类ID用于关联商品分类表，以获取商品的分类信息，价格字段可以使用DECIMAL类型来存储，以确保精度，库存字段可以使用UNSIGNED类型来存储正整数，销量字段也使用UNSIGNED类型来存储销售数量，创建时间和更新时间用于记录商品的新增和修改时间，为了提高查询效率，可以在商品ID和分类ID字段上建立索引，对于商品的图片，可以使用单独的表来存储图片URL或路径，以便于管理和维护，还可以考虑使用CDN加速静态资源的加载速度，通过这些设计和优化措施，可以确保商品详情页面的信息准确无误且加载迅速。

商品搜索：提供商品搜索功能，支持关键词搜索、筛选和排序等功能，为了提高搜索效率，可以使用全文索引或者专门的搜索引擎服务（如Elasticsearch），此功能涉及到多个表的联合查询，包括商品表（sh_goods）、商品分类表（sh_goods_category）等，需要在商品表中建立全文索引，以便快速检索商品名称和其他文本字段，可以使用LIKE操作符进行简单的模糊匹配搜索，但是LIKE操作符在大数据集上的效率较低，因此可以考虑使用全文索引引擎（如MyISAM或InnoDB），还可以结合分类表进行筛选搜索，例如只搜索特定分类下的商品，还可以根据价格区间、销量等条件进行排序和筛选，为了进一步提升性能，可以引入缓存机制，将热门搜索结果存储在内存中，以减少数据库的压力，也可以使用分页技术来限制每次返回的结果数量，避免一次性加载过多数据导致页面卡顿，通过这些技术和策略的应用，可以大大提高商品搜索的效率和用户体验。

购物车管理：允许用户将商品添加到购物车中，并在购物车内进行增删改查操作，购物车数据应包括用户ID、商品ID、数量等信息，此功能可以通过购物车表（sh_cart）来实现，购物车表包含以下关键字段：购物车ID（主键）、用户ID（外键）、商品ID（外键）、数量、添加时间等，用户ID用于关联用户表，以获取用户信息；商品ID用于关联商品表，以获取商品信息，数量字段用于记录用户想要购买的商品数量，添加时间用于记录用户添加商品到购物车的时间，为了提高查询效率，可以在购物车ID、用户ID和商品ID字段上建立索引，还可以考虑使用Redis缓存购物车数据，以提高读写性能，当用户打开购物车页面时，系统可以从缓存中读取数据；当用户关闭页面或手动清空购物车时，系统可以将数据同步到数据库中，通过这些设计和优化措施，可以确保购物车功能的高效运行和用户体验的良好感受。