WooCommerce商城开发：高性能订单存储数据库模式

这是一系列深入探讨的第一部分，专门用于解释高性能订单存储数据库模式的实施。

与1 月份提出的版本相比，数据库模式的变化很小。我们在不同的地方添加和删除了几列，但整体表结构与第一个提案中描述的相同：

我们在此项目中添加了4 个表：

1. 主订单表
2. 订单地址表
3. 订单操作表
4. 订单元表

该解决方案应该可以显着加快数据写入和读取速度：以前，每个新订单都需要对表中的记录进行一次 INSERT posts + 对每条postmeta记录进行近 40 个 INSERT。新的数据结构最多需要 5 个 INSERT。 

对多订单搜索，我们不得不多次将postmeta表与posts表连接起来（例如，跨地址字段和客户电子邮件地址进行搜索）。现在，搜索需要更少的连接，并且连接的表与postmeta表相比要小得多。

目录  隐藏 

主订单表

代码

订单地址表

代码

订单操作表

代码

订单元数据表

代码

关于订单的注意事项

订购商品

主订单表

主订单表包含以前存储在表中的所有数据wp_posts，并为最常用的元值添加列。 

自上次提议以来，我们删除了post_id列，因为wc_order.id始终与相应的post.ID相匹配，因此post_id不再需要。

我们还添加了：

• type列以正确支持退款（这是一种订单类型，建模为父订单的子订单，就像以前的帖子一样）。 
• customer_note用于存储客户可以在结帐过程中留下的注释的列

Column Name	Type	Description	Example values
id	bigint(20) unsigned	Unique ID for order.	1, 5, 143
status	varchar(20)	Status for the order.	‘wc-processing’, ‘wc-completed’, ‘wc-refunded’
currency	varchar(10)	Currency identifier in which payment was received.	‘GBP’, ‘USD’, ‘ZAR’
type	varchar(20)		‘shop_order’, ‘shop_order_refund’
tax_amount	decimal(26,8)	Total order tax amount	12.45, 5.0
total_amount	decimal(26,8)	Total order total, including tax	15.66, 10.0
customer_id	bigint(20) unsigned	Customer ID (0 in case of guest checkout)	1, 5, 12
billing_email	varchar(320)	Billing email for customer	‘john@doe.com’
date_created_gmt	datetime	Timestamp when order was created	‘2021-12-31 23:59:59.999999’, ‘2022-04-31 23:59:59’
date_updated_gmt	datetime	Timestamp when the order or any of its properties were last updated	‘2021-12-31 23:59:59.999999’, ‘2022-04-31 23:59:59’
parent_order_id	bigint(20) unsigned	Order ID of the parent, in case this order is a refund
payment_method	varchar(100)	Identifier of payment method	‘bacs’,
payment_method_title	text	Title of payment method used	‘Direct Bank Transfer’,
transaction_id	varchar(100)	Transaction ID provided by the payment gateway.	‘ch_1HbohKHMqnIERF7AtGikC32B’
ip_address	varchar(100)	IP address used to create the order	‘1.2.3.4’
user_agent	text	The user-agent string of the web client that was used to create the order.	‘Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.121 Safari/537.36’, ‘Scrapy/2.6 (+https://scrapy.org)’
customer_note	text	Stores the customer note added during the checkout flow.	‘Call me on my cell once you ship the widget please’

代码

CREATE TABLE $wpdb->prefix.'wc_orders' (
 
    id BIGINT(20) UNSIGNED,
    status VARCHAR(20) NULL,
    currency VARCHAR(10) NULL,
    type VARCHAR(20) NULL,
    tax_amount DECIMAL(26, 8) NULL,
    total_amount DECIMAL(26, 8) NULL,
    customer_id BIGINT(20) UNSIGNED NULL,
    billing_email VARCHAR(320) NULL,
    date_created_gmt DATETIME NULL,
    date_updated_gmt DATETIME NULL,
    parent_order_id BIGINT(20) UNSIGNED NULL,
    payment_method VARCHAR(100) NULL,
    payment_method_title TEXT NULL,
    transaction_id VARCHAR(100) NULL,
    ip_address VARCHAR(100) NULL,
    user_agent TEXT NULL,
    customer_note TEXT NULL,
 
    PRIMARY KEY (id),
    KEY status (status),
    KEY date_created (date_created_gmt),
    KEY customer_id_billing_email (customer_id, billing_email),
    KEY billing_email (billing_email),
    KEY type_status (type, status),
    KEY parent_order_id (parent_order_id),
    KEY date_updated (date_updated_gmt)
);

订单地址表

订单地址表用于存储最终客户的帐单地址和送货地址。

我们从表中省略了date_created_gmt，因为该信息将存储在主订单表中。

Column Name	Type	Description	Example Values
id	bigint(20) unsigned	Unique ID for order address record.	1, 5
order_id	bigint(20) unsigned	Order ID.	3, 8
address_type	varchar(20)	Type of address (billing, shipping, etc).	‘billing’, ‘shipping’
first_name	text	First name	‘John’
last_name	text	Last name	‘Doe’
company	text	Company name	‘John Doe Ltd.’
address_1	text	Address line 1	‘Oaktree rd.’
address_2	text	Address line 2
city	text	City	‘New York’
state	text	State	‘NY’
postcode	text	Postcode/ZIP Code	‘10019’, ‘E14 3QS’
country	text	Country code from i18n/countries.php	‘US’, ‘UK’
email	varchar(320)	Email	‘john@doe.com’
phone	varchar(100)	Phone number	‘1-500-2345’

代码

CREATE TABLE $wpdb->prefix.'wc_order_addresses' (
     
    id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT(20) UNSIGNED NOT NULL,
    address_type VARCHAR(20) NULL,
    first_name TEXT NULL,
    last_name TEXT NULL,
    company TEXT NULL,
    address_1 TEXT NULL,
    address_2 TEXT NULL,
    city TEXT NULL,
    state TEXT NULL,
    postcode TEXT NULL,
    country TEXT NULL,
    email VARCHAR(320) NULL,
    phone VARCHAR(100) NULL,
 
    KEY order_id (order_id),
    UNIQUE KEY address_type_order_id (address_type, order_id),
    KEY email (email),
    KEY phone (phone)
);

订单操作表

Order Operational Table 存储我们用来维护内部订单状态的字段和标志。我们将这些字段与核心订单表分开，因为这些字段将来可能会根据内部实施细节进行更改。

在操作表中，有一列被重命名（download_permissions_granted被重命名为download_permmission_granted，注意权限中缺少s），还有一列被添加到recorded_sales，因为它存储在大多数订单中，但在原始提案中被省略了。

Column Name	Detail	Description	Example
id	bigint(20) unsigned		1, 5
order_id	bigint(20) unsigned		3, 8
created_via	varchar(100)	The identifier for order creation source. WC core uses the following values: – admin  – checkout – rest-api – store-api	‘admin’, ‘checkout’
woocommerce_version	varchar(20)	WooCommerce version which was active when creating the order. Postmeta previously called `_order_version`	‘4.6.0’, ‘6.7.0’
prices_include_tax	tinyint(1)	For internal use. Whether prices included taxes when purchased.	0, 1
coupon_usages_are_counted	tinyint(1)	For internal use. Whether coupon usage is counted.	0, 1
download_permission_granted	tinyint(1)	Marks whether download permissions are granted to order or not.	0, 1
cart_hash	varchar(100)	Hash of cart contents, used when clearing cart when order payment is successful.	‘010ae06d098de5f270bd27cc69721576’
new_order_email_sent	tinyint(1)	Whether an email is sent for the order	0, 1
order_key	varchar(100)	Key used to allow anonymous order access for payment and related operations.	‘wc_order_jIe6po8pcXvZh’
order_stock_reduced	tinyint(1)	Whether the stock is reduced for order	0, 1
date_paid_gmt	datetime	Timestamp when the order was first moved into any of the paid statuses.	‘2021-12-31 23:59:59.999999’, ‘2022-04-31 23:59:59’
date_completed_gmt	datetime	Timestamp when the order was last moved into completed status.	‘2021-12-31 23:59:59.999999’, ‘2022-04-31 23:59:59’
shipping_tax_amount	decimal(26,8)	Total shipping tax	12.34
shipping_total_amount	decimal(26,8)	Total shipping amount, including tax	45.69
discount_tax_amount	decimal(26,8)	Total discount tax	54.23
discount_total_amount	decimal(26,8)	Total discount amount, including tax	12.23
recorded_sales	tinyint(1)	Stores information about whether sales were recorded.	0, 1

代码

CREATETABLE$wpdb->prefix.'wc_order_operational_data'(

    id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARYKEY,

    order_id BIGINT(20) UNSIGNED NULL,

    created_via VARCHAR(100) NULL,

    woocommerce_version VARCHAR(20) NULL,

    prices_include_tax TINYINT(1) NULL,

    coupon_usages_are_counted TINYINT(1) NULL,

    download_permission_granted TINYINT(1) NULL,

    cart_hash VARCHAR(100) NULL,

    new_order_email_sent TINYINT(1) NULL,

    order_key VARCHAR(100) NULL,

    order_stock_reduced TINYINT(1) NULL,

    date_paid_gmt DATETIME NULL,

    date_completed_gmt DATETIME NULL,

    shipping_tax_amount DECIMAL(26, 8) NULL,

    shipping_total_amount DECIMAL(26, 8) NULL,

    discount_tax_amount DECIMAL(26, 8) NULL,

    discount_total_amount DECIMAL(26, 8) NULL,

    recorded_sales TINYINT(1) NULL,

    UNIQUEKEYorder_id (order_id),

    UNIQUEKEYorder_key (order_key)

);

订单元数据表

该表在功能上与wp_postmeta表类似，充当扩展数据的数据存储（除非扩展创建自己的表）。

与之前的提议相反，我们决定省略date_created_gmt，因为这些将存储在父订单中。

Column Name	Type	Description	Example Values
id	bigint(20) unsigned	Unique ID for meta record.	1, 5
order_id	bigint(20) unsigned, indexed	Corresponding order ID.	3, 8
meta_key	varchar(255)	Name of the key.	‘_stripe_customer_id’
meta_value	text	Value of the record.	‘cus_ICD8cAbkdVKbZF’

代码

CREATETABLE$wpdb->prefix.'wc_orders_meta'(

    id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARYKEY,

    order_id BIGINT(20) UNSIGNED NULL,

    meta_key VARCHAR(255),

    meta_value TEXT NULL,

    KEYmeta_key_value (meta_key, meta_value(100)),

    KEYorder_id_meta_key_meta_value (order_id, meta_key, meta_value(100))

)

关于订单的注意事项

我们决定从项目范围中删除订单注释，因为我们认为它们不是大多数与订单相关的数据库操作的瓶颈。我们也不认为wp_comments当前存储它们的表通常会收到与订单备注竞争的大量写入流量。因为我们已经决定强制执行 posts 表中的订单 ID = 新表中的订单 ID 的不变性，所以我们不需要重新绑定订单注释以指向正确的订单。如果需要，我们计划根据进一步的性能测试和来自社区的反馈来处理订单备注。

订购商品

Order Items 已经有自己的专用表，它们超出了本项目的范围。

点击阅读 WooCommerce商城开发：高性能订单存储数据库模式 原文