Skip to content

Latest commit

 

History

History
261 lines (195 loc) · 11.5 KB

dev-guide-bookshop-schema-design.md

File metadata and controls

261 lines (195 loc) · 11.5 KB
Raw
title summary aliases
Bookshop 应用
Bookshop 应用设计、数据导入、连接数据库等操作。
/zh/tidb/dev/bookshop-schema-design

Bookshop 应用

Bookshop 是一个虚拟的在线书店应用，你可以在 Bookshop 当中便捷地购买到各种类别的书，也可以对你看过的书进行点评。

为了方便你阅读应用开发指南中的内容，我们将以 Bookshop 应用的数据表结构和数据为基础来编写示例 SQL。本章节将为你介绍如何导入该应用的表结构和数据，以及其数据表结构的定义。

导入表结构和数据

你可以通过 TiUP 或通过 TiDB Cloud Import 两种方式导入 Bookshop 应用的表结构和数据。

方法一：通过 tiup demo 命令行

如果你使用 TiUP 部署 TiDB 集群或者你可以直接连接到你的 TiDB 服务器，你可以通过如下命令快速生成并导入 Bookshop 应用的示例数据：

tiup demo bookshop prepare

该命令默认会连接到 127.0.0.1 地址上的 4000 端口，使用 root 用户名进行无密码登录，默认在名为 bookshop 的数据库中创建表结构。

配置连接信息

你可以通过如下参数修改默认的连接信息：

参数 简写 默认值 解释
--host -H 127.0.0.1 数据库地址
--port -P 4000 数据库端口
--user -U root 数据库用户
--password -p 无 数据库用户密码
--db -D bookshop 数据库名称

例如，你想要连接到 TiDB Cloud 上的数据库，你可以如下命令指定连接信息进行连接：

tiup demo bookshop prepare -U <username> -H <endpoint> -P 4000 -p <password>

设置数据量

另外，你还可以通过如下参数指定各个数据库表生成的数据量：

参数 默认值 解释
--users 10000 指定在 users 表生成的数据行数
--authors 20000 指定在 authors 表生成的数据行数
--books 20000 指定在 books 表生成的数据行数
--orders 300000 指定在 orders 表生成的数据行数
--ratings 300000 指定在 ratings 表生成的数据行数

例如，以下命令通过 --users 参数指定生成 20 万行用户信息，通过 --books 参数指定生成 50 万行书籍的基本信息，通过 --authors 参数指定生成 10 万的作者信息，通过 --ratings 参数指定生成 100 万的评分记录，通过 --orders 参数指定生成 100 万的订单记录。

tiup demo bookshop prepare --users=200000 --books=500000 --authors=100000 --ratings=1000000 --orders=1000000 --drop-tables

通过 --drop-tables 参数你可以删除原有的表结构，更多的参数说明你可以通过命令 tiup demo bookshop --help 进行了解。

方法二：通过 TiDB Cloud Import 功能

在 TiDB Cloud 的集群详情页面，你可以通过切换到 Import 标签页，点击 Import Data 按钮进入到 Data Import 页面。在该页面当中，按照以下步骤将 Bookshop 示例数据从 AWS S3 中导入到你的 TiDB Cloud 集群：

  1. 选择 Data Format 为 SQL File。

  2. 将以下 Bucket URI 和 Role ARN 复制到页面上对应的输入框当中：

    Bucket URI:

    s3://developer.pingcap.com/bookshop/

    Role ARN:

    arn:aws:iam::494090988690:role/s3-tidb-cloud-developer-access

  3. 点击 Next 按钮切换到 Choose the tables to be imported 步骤确认将要导入的文件的信息。

  4. 点击 Next 按钮切换到 Preview 步骤确认将要导入的示例数据是否正确。

    在这个示例数据当中，预先生成了 20 万的用户信息、50 万条书籍信息、10 万条作者信息、100 万条评分记录以及 100 万条订单信息。

  5. 点击 Start Import 按钮开始导入数据，等待 TiDB Cloud 完成数据导入。

你可以通过 TiDB Cloud Migration Overview 文档获取更多有关 TiDB Cloud 数据导入和迁移的信息。

查看数据导入情况

导入完成后，你可以通过下面的 SQL 语句查看各个表的数据量信息：

SELECT
    CONCAT(table_schema,'.',table_name) AS 'Table Name',
    table_rows AS 'Number of Rows',
    CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size',
    CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size',
    CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS 'Total'
FROM
    information_schema.TABLES
WHERE table_schema LIKE 'bookshop';

运行结果为：

+-----------------------+----------------+-----------+------------+---------+
| Table Name            | Number of Rows | Data Size | Index Size | Total   |
+-----------------------+----------------+-----------+------------+---------+
| bookshop.orders       |        1000000 | 0.0373G   | 0.0075G    | 0.0447G |
| bookshop.book_authors |        1000000 | 0.0149G   | 0.0149G    | 0.0298G |
| bookshop.ratings      |        4000000 | 0.1192G   | 0.1192G    | 0.2384G |
| bookshop.authors      |         100000 | 0.0043G   | 0.0000G    | 0.0043G |
| bookshop.users        |         195348 | 0.0048G   | 0.0021G    | 0.0069G |
| bookshop.books        |        1000000 | 0.0546G   | 0.0000G    | 0.0546G |
+-----------------------+----------------+-----------+------------+---------+
6 rows in set (0.03 sec)

数据表详解

以下将详细介绍 Bookshop 应用程序的数据库表结构：

books 表

该表用于存储书籍的基本信息。

字段名 类型 含义
id bigint(20) 书籍的唯一标识
title varchar(100) 书籍名称
type enum 书籍类型（如：杂志、动漫、教辅等）
stock bigint(20) 库存
price decimal(15,2) 价格
published_at datetime 出版时间

authors 表

该表用于存储作者的基本信息。

字段名 类型 含义
id bigint(20) 作者的唯一标识
name varchar(100) 姓名
gender tinyint(1) 生理性别 (0: 女, 1: 男，NULL: 未知)
birth_year smallint(6) 生年
death_year smallint(6) 卒年

users 表

该表用于存储使用 Bookshop 应用程序的用户。

字段名 类型 含义
id bigint(20) 用户的唯一标识
balance decimal(15,2) 余额
nickname varchar(100) 昵称

ratings 表

该表用于存储用户对书籍的评分记录。

字段名 类型 含义
book_id bigint 书籍的唯一标识（关联至 books）
user_id bigint 用户的唯一标识（关联至 users）
score tinyint 用户评分 (1-5)
rated_at datetime 评分时间

book_authors 表

一个作者可能会编写多本书，一本书可能需要多个作者同时编写，该表用于存储书籍与作者之间的对应关系。

字段名 类型 含义
book_id bigint(20) 书籍的唯一标识（关联至 books）
author_id bigint(20) 作者的唯一标识（关联至 authors）

orders 表

该表用于存储用户购买书籍的订单信息。

字段名 类型 含义
id bigint(20) 订单的唯一标识
book_id bigint(20) 书籍的唯一标识（关联至 books）
user_id bigint(20) 用户唯一标识（关联至 users）
quantity tinyint(4) 购买数量
ordered_at datetime 购买时间

数据库初始化 dbinit.sql 脚本

如果你希望手动创建 Bookshop 应用的数据库表结构，你可以运行以下 SQL 语句：

CREATE DATABASE IF NOT EXISTS `bookshop`;

DROP TABLE IF EXISTS `bookshop`.`books`;
CREATE TABLE `bookshop`.`books` (
  `id` bigint(20) AUTO_RANDOM NOT NULL,
  `title` varchar(100) NOT NULL,
  `type` enum('Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports') NOT NULL,
  `published_at` datetime NOT NULL,
  `stock` int(11) DEFAULT '0',
  `price` decimal(15,2) DEFAULT '0.0',
  PRIMARY KEY (`id`) CLUSTERED
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS `bookshop`.`authors`;
CREATE TABLE `bookshop`.`authors` (
  `id` bigint(20) AUTO_RANDOM NOT NULL,
  `name` varchar(100) NOT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `birth_year` smallint(6) DEFAULT NULL,
  `death_year` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`) CLUSTERED
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS `bookshop`.`book_authors`;
CREATE TABLE `bookshop`.`book_authors` (
  `book_id` bigint(20) NOT NULL,
  `author_id` bigint(20) NOT NULL,
  PRIMARY KEY (`book_id`,`author_id`) CLUSTERED
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS `bookshop`.`ratings`;
CREATE TABLE `bookshop`.`ratings` (
  `book_id` bigint NOT NULL,
  `user_id` bigint NOT NULL,
  `score` tinyint NOT NULL,
  `rated_at` datetime NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (`book_id`,`user_id`) CLUSTERED,
  UNIQUE KEY `uniq_book_user_idx` (`book_id`,`user_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
ALTER TABLE `bookshop`.`ratings` SET TIFLASH REPLICA 1;

DROP TABLE IF EXISTS `bookshop`.`users`;
CREATE TABLE `bookshop`.`users` (
  `id` bigint AUTO_RANDOM NOT NULL,
  `balance` decimal(15,2) DEFAULT '0.0',
  `nickname` varchar(100) UNIQUE NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS `bookshop`.`orders`;
CREATE TABLE `bookshop`.`orders` (
  `id` bigint(20) AUTO_RANDOM NOT NULL,
  `book_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `quality` tinyint(4) NOT NULL,
  `ordered_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) CLUSTERED,
  KEY `orders_book_id_idx` (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin