后端与系统架构2135 字

非常牛逼，200毫秒干到10毫秒，利用MySQL JSON特性优化千万级订单表

更新于 2024-05-12·全库 565 / 611

《非常牛逼，200毫秒干到10毫秒，利用MySQL JSON特性优化千万级订单表》共 0.2 万字，通篇围绕场景、数据库、MySQL、数据集展开，归在「后端与系统架构」这一层。

覆盖场景数据库MySQL数据集需求

适合给 AI 系统搭底座、要管得住的人

目录骨架（8 节）

一、创建表
二、插入数据
三、查询数据
四、虚拟列
1. 提高查询性能
2. 灵活的数据结构
3. 简化数据存储
4. 数据传输和序列化

目录 · 9 节

场景描述
业务需求
查询示例
效率问题
解决方案一 : 反范式设计
弊端
解决方案二 : mysql5.7后Json特性
一、创建表
二、插入数据

场景描述

在一个电商平台中，为了生成一份详细的销售报告，需要从多个表中获取数据。这些表包括订单、客户、产品、库存、支付、物流、地区、分类、促销、和评价等信息。假设需要从以下10个表中获取数据：

orders（订单）

customers（客户）

products（产品）

inventory（库存）

payments（支付）

shipping（物流）

regions（地区）

categories（分类）

promotions（促销）

reviews（评价）

业务需求

生成一份报告，包含每个订单的详细信息，包括订单ID、客户姓名、产品名称、库存状态、支付方式、物流信息、客户地区、产品分类、促销活动、以及客户评价等。

查询示例

为了获取这些信息，我们需要进行多表关联查询。查询语句可能如下所示：

SELECT 
    o.order_id, 
    c.name AS customer_name, 
    p.name AS product_name, 
    i.stock_status, 
    pay.payment_method, 
    s.shipping_status, 
    r.region_name, 
    cat.category_name, 
    promo.promotion_name, 
    rev.review_content
FROM 
    orders o
JOIN 
    customers c ON o.customer_id = c.customer_id
JOIN 
    products p ON o.product_id = p.product_id
JOIN 
    inventory i ON p.product_id = i.product_id
JOIN 
    payments pay ON o.payment_id = pay.payment_id
JOIN 
    shipping s ON o.shipping_id = s.shipping_id
JOIN 
    regions r ON c.region_id = r.region_id
JOIN 
    categories cat ON p.category_id = cat.category_id
JOIN 
    promotions promo ON o.promotion_id = promo.promotion_id
JOIN 
    reviews rev ON o.review_id = rev.review_id;

效率问题

表的数量多：以上查询涉及10个表的关联，每个表都有自己的数据量，如果每个表的数据量较大，关联操作会非常耗时。
全表扫描：在没有索引的情况下，查询需要对每个表进行全表扫描，导致查询时间显著增加。
数据处理复杂：每个订单需要从多个表中获取关联信息，导致查询需要处理大量的数据和复杂的连接操作。
性能瓶颈：由于查询需要大量的计算资源和内存，当数据量非常大时，数据库服务器可能出现性能瓶颈，影响系统的整体性能。

解决方案一 : 反范式设计

一个版本，这个1.0呢，就是采用反范式设计，基于宽表，也就是我们典型的空间换时间，可以看到刚才我们处理慢的一个主要思路呢，就是一个数据表要查询多次才能获得完整的信息。那如果我们把这些数据都。综合到一个宽表里边儿，也就是我们反范式表是不是就可以了呢？

其实这个思路非常的好，我们可以比如说把所有的属性呢，以列的形式在这儿呢，都进行体现出来

弊端

在当前的这个宽表中呢，包含了所有可能会出现的属性，哪一个属性有数据，我们就提取哪一个，但是针对宽表呢，在我们日常工作中啊，并不太推荐使用，有两个原因 :

它的字段一多以后，字段的动态填充和减少是要锁表的，尤其在数据量一大的时候，比如现在我们针对某一个类型又有一个新的属性，你一旦添加列的时候，整个这个表就锁了。对于我们的维护非常的不方便.
数据查询的时候非常麻烦,难以基于动态列的方式来进行了提取数据
作为宽表还有一些不能解决的问题，难以体现出一对多的关系

解决方案二 : mysql5.7后Json特性

一、创建表

CREATE TABLE wide_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

二、插入数据

INSERT INTO wide_table (data) VALUES 
(JSON_OBJECT('order_id', 1, 'order_date', '2023-05-01', 'amount', 150.00, 'customer', JSON_OBJECT('customer_id', 101, 'customer_name', 'Alice', 'customer_address', '123 Maple St.'), 'product', JSON_OBJECT('product_id', 1001, 'product_name', 'Widget', 'product_price', 50.00), 'inventory', JSON_OBJECT('stock_status', 'In Stock'), 'payment', JSON_OBJECT('payment_method', 'Credit Card'), 'shipping', JSON_OBJECT('shipping_status', 'Shipped'), 'region', JSON_OBJECT('region_name', 'North'), 'category', JSON_OBJECT('category_name', 'Electronics'), 'promotion', JSON_OBJECT('promotion_name', 'Spring Sale'), 'review', JSON_OBJECT('review_content', 'Great product!'))),
(JSON_OBJECT('order_id', 2, 'order_date', '2023-05-02', 'amount', 200.00, 'customer', JSON_OBJECT('customer_id', 102, 'customer_name', 'Bob', 'customer_address', '456 Oak St.'), 'product', JSON_OBJECT('product_id', 1002, 'product_name', 'Gizmo', 'product_price', 75.00), 'inventory', JSON_OBJECT('stock_status', 'In Stock'), 'payment', JSON_OBJECT('payment_method', 'PayPal'), 'shipping', JSON_OBJECT('shipping_status', 'Delivered'), 'region', JSON_OBJECT('region_name', 'South'), 'category', JSON_OBJECT('category_name', 'Gadgets'), 'promotion', JSON_OBJECT('promotion_name', 'Summer Sale'), 'review', JSON_OBJECT('review_content', 'Excellent!'))),
(JSON_OBJECT('order_id', 3, 'order_date', '2023-05-03', 'amount', 250.00, 'customer', JSON_OBJECT('customer_id', 101, 'customer_name', 'Alice', 'customer_address', '123 Maple St.'), 'product', JSON_OBJECT('product_id', 1003, 'product_name', 'Thingamajig', 'product_price', 100.00), 'inventory', JSON_OBJECT('stock_status', 'Out of Stock'), 'payment', JSON_OBJECT('payment_method', 'Credit Card'), 'shipping', JSON_OBJECT('shipping_status', 'Processing'), 'region', JSON_OBJECT('region_name', 'North'), 'category', JSON_OBJECT('category_name', 'Tools'), 'promotion', JSON_OBJECT('promotion_name', 'Winter Sale'), 'review', JSON_OBJECT('review_content', 'Very useful!'))),
(JSON_OBJECT('order_id', 4, 'order_date', '2023-05-04', 'amount', 300.00, 'customer', JSON_OBJECT('customer_id', 103, 'customer_name', 'Charlie', 'customer_address', '789 Birch St.'), 'product', JSON_OBJECT('product_id', 1004, 'product_name', 'Doodad', 'product_price', 125.00), 'inventory', JSON_OBJECT('stock_status', 'In Stock'), 'payment', JSON_OBJECT('payment_method', 'Debit Card'), 'shipping', JSON_OBJECT('shipping_status', 'Shipped'), 'region', JSON_OBJECT('region_name', 'East'), 'category', JSON_OBJECT('category_name', 'Home Appliances'), 'promotion', JSON_OBJECT('promotion_name', 'Autumn Sale'), 'review', JSON_OBJECT('review_content', 'Works great!'))),
(JSON_OBJECT('order_id', 5, 'order_date', '2023-05-05', 'amount', 175.00, 'customer', JSON_OBJECT('customer_id', 104, 'customer_name', 'Dave', 'customer_address', '321 Pine St.'), 'product', JSON_OBJECT('product_id', 1005, 'product_name', 'Whatsit', 'product_price', 60.00), 'inventory', JSON_OBJECT('stock_status', 'In Stock'), 'payment', JSON_OBJECT('payment_method', 'Credit Card'), 'shipping', JSON_OBJECT('shipping_status', 'Shipped'), 'region', JSON_OBJECT('region_name', 'West'), 'category', JSON_OBJECT('category_name', 'Kitchenware'), 'promotion', JSON_OBJECT('promotion_name', 'Holiday Sale'), 'review', JSON_OBJECT('review_content', 'Very practical!'))),
(JSON_OBJECT('order_id', 6, 'order_date', '2023-05-06', 'amount', 220.00, 'customer', JSON_OBJECT('customer_id', 105, 'customer_name', 'Eve', 'customer_address', '654 Elm St.'), 'product', JSON_OBJECT('product_id', 1006, 'product_name', 'Contraption', 'product_price', 85.00), 'inventory', JSON_OBJECT('stock_status', 'Out of Stock'), 'payment', JSON_OBJECT('payment_method', 'PayPal'), 'shipping', JSON_OBJECT('shipping_status', 'Processing'), 'region', JSON_OBJECT('region_name', 'Central'), 'category', JSON_OBJECT('category_name', 'Electronics'), 'promotion', JSON_OBJECT('promotion_name', 'Spring Sale'), 'review', JSON_OBJECT('review_content', 'Highly recommended!'))),
(JSON_OBJECT('order_id', 7, 'order_date', '2023-05-07', 'amount', 190.00, 'customer', JSON_OBJECT('customer_id', 106, 'customer_name', 'Frank', 'customer_address', '987 Cedar St.'), 'product', JSON_OBJECT('product_id', 1007, 'product_name', 'Gadget', 'product_price', 70.00), 'inventory', JSON_OBJECT('stock_status', 'In Stock'), 'payment', JSON_OBJECT('payment_method', 'Debit Card'), 'shipping', JSON_OBJECT('shipping_status', 'Delivered'), 'region', JSON_OBJECT('region_name', 'North'), 'category', JSON_OBJECT('category_name', 'Gadgets'), 'promotion', JSON_OBJECT('promotion_name', 'Summer Sale'), 'review', JSON_OBJECT('review_content', 'Fantastic!'))),
(JSON_OBJECT('order_id', 8, 'order_date', '2023-05-08', 'amount', 210.00, 'customer', JSON_OBJECT('customer_id', 107, 'customer_name', 'Grace', 'customer_address', '456 Spruce St.'), 'product', JSON_OBJECT('product_id', 1008, 'product_name', 'Widget Pro', 'product_price', 80.00), 'inventory', JSON_OBJECT('stock_status', 'In Stock'), 'payment', JSON_OBJECT('payment_method', 'Credit Card'), 'shipping', JSON_OBJECT('shipping_status', 'Shipped'), 'region', JSON_OBJECT('region_name', 'South'), 'category', JSON_OBJECT('category_name', 'Electronics'), 'promotion', JSON_OBJECT('promotion_name', 'Spring Sale'), 'review', JSON_OBJECT('review_content', 'Very satisfied!'))),
(JSON_OBJECT('order_id', 9, 'order_date', '2023-05-09', 'amount', 230.00, 'customer', JSON_OBJECT('customer_id', 108, 'customer_name', 'Henry', 'customer_address', '789 Fir St.'), 'product', JSON_OBJECT('product_id', 1009, 'product_name', 'Gizmo Max', 'product_price', 90.00), 'inventory', JSON_OBJECT('stock_status', 'Out of Stock'), 'payment', JSON_OBJECT('payment_method', 'PayPal'), 'shipping', JSON_OBJECT('shipping_status', 'Processing'), 'region', JSON_OBJECT('region_name', 'East'), 'category', JSON_OBJECT('category_name', 'Gadgets'), 'promotion', JSON_OBJECT('promotion_name', 'Summer Sale'), 'review', JSON_OBJECT('review_content', 'Good value!')));

充值接口被薅羊毛，老板让我做接口加密

Restful API设计最佳实践

同模块推荐

资料里没有你的场景？

把你手上的数据和想解决的那个环节发上来，我们按具体情况回，不复述方法论。

发布需求