终身学习、乐于分享、共同成长！

前言

电商系统已经成为了当今社会中不可或缺的一部分。顾客们可以在不出门的情况下选购自己需要的商品，并且享受到快捷、方便的购物体验。而对于电商平台来说，一个高效的订单查询系统也是至关重要的。因为在交易过程中，顾客们可能会遇到各种问题，比如商品未及时送达、退换货等问题。那么一个能够提供快速、准确的订单查询服务的电商平台，将有助于提升用户体验和满意度，并有助于提高平台的口碑和业绩。

一个经典的订单系统表关系常常如下图：

订单查询这个功能具有广泛的适用性，从上图的表关系可以看出其实很多业务场景的表关系都能与之相匹配，搞懂这个订单查询的优化很容易能移植到别的业务查询优化中。

准备表结构与数据

首先来准备一下案例的表结构和数据。

客户表create table customers ( id bigint auto_increment comment 主键ID primary key, customer_namevarchar(64) null comment 客户名称, customer_type varchar(20) null comment 客户类型 ) comment 客户表;产品表create table products ( id bigint auto_increment comment 商品ID primary key, product_name varchar(64) null comment 商品名称, price decimal(19, 2) null comment 产品价格 ) comment 商品表;订单表create table orders ( id bigintauto_incrementcomment 主键ID primary key, order_no varchar(20) null comment 订单编号, order_date datetime null comment 下单时间, customer_id bigint null comment 关联客户ID ) comment 订单表;订单项目表create table order_items ( id bigint auto_increment comment 主键ID primary key, product_id bigint null comment 关联产品ID, order_id bigint null comment 关联订单主键ID, num int null comment 购买数量 ) comment 订单项目表;初始化案例数据

查询需求

按订单编号、订单日期、客户名称分组查询统计订单的总金额，原始查询SQL如下：

SELECT o.order_no, o.order_date, c.customer_name, SUM(p.price * oi.num) AS total_amount FROM orders o JOIN customers c on o.customer_id = c.id JOIN order_items oi on o.id = oi.order_id JOIN products p onp.id = oi.product_idWHERE o.order_date BETWEEN 2023-03-01 00:00:00 AND 2023-03-20 22:59:59 AND c.customer_type = S级客户 GROUP BY o.order_no, o.order_date, c.customer_name ORDER BY total_amount DESC LIMIT 10

优化步骤

第一步：创建索引

-- 在订单表上创建客户ID与订单日期组合索引 CREATE INDEX idx_customer_id_order_date ON orders(customer_id, order_date); -- 在客户表上创建客户类型索引 CREATE INDEX idx_customer_type ON customers(customer_type); -- 在订单项目表上创建订单id索引以及产品id索引 CREATE INDEXidx_order_idON order_items(order_id); CREATE INDEX idx_product_id ON order_items(product_id);

第二步：优化查询语句

将WHERE子句中的范围过滤条件放到连接条件中

SELECT o.order_no, o.order_date, c.customer_name, SUM(p.price * oi.num) AS total_amount FROMorders oJOIN customers c on o.customer_id = c.id AND c.customer_type = S级客户 JOIN order_items oi ono.id = oi.order_idJOIN products p on p.id = oi.product_id WHERE o.order_date BETWEEN 2023-03-01 00:00:00 AND 2023-03-20 22:59:59 GROUP BY o.order_no, o.order_date, c.customer_name ORDER BY total_amount DESC LIMIT 10;

第三步：减少表数量

通过子查询将products表的结果筛选出来，将其结果用于JOIN，这样就可以减少一个JOIN，并将查询的复杂度降低到一个子查询中。

SELECTo.order_no, o.order_date, c.customer_name,SUM(p.price * oi.num) AS total_amount FROM orders o JOIN customers c on o.customer_id = c.id ANDc.customer_type =S级客户 JOIN order_items oi on o.id = oi.order_id JOIN (SELECT id, price FROM products) p onoi.product_id = p.idWHERE o.order_date BETWEEN 2023-03-01 00:00:00 AND 2023-03-20 22:59:59 GROUP BYo.order_no, o.order_date, c.customer_nameORDER BY total_amount DESC LIMIT 10;

第四步：使用内连试图将复杂的子查询转换为更简单的查询

SELECTo.order_no, o.order_date, c.customer_name, total_amountFROM (SELECT oi.order_id, SUM(p.price * oi.num) AS total_amount FROM order_items oi JOIN products p ON oi.product_id = p.id GROUP BY oi.order_id) op JOIN orders o ONop.order_id = o.idAND o.order_date BETWEEN 2023-03-01 00:00:00 AND 2023-03-20 22:59:59 JOIN customers c ONo.customer_id = c.idAND c.customer_type = S级客户 ORDER BY total_amount DESC LIMIT 10;

这个查询使用了内连试图op它从order_items表与products表中获取每个订单的总金额，然后将其与orders表和customers表连接起来，以获取每个订单的其他信息和客户信息。

同时，它将WHERE子句中的日期范围和客户类型过滤条件放在连接条件中，从而减少了WHERE子句中的过滤条件，并将其转移到连接条件中，从而减少了表的扫描行数。