面试题：MariaDB中如何优化复杂查询涉及的TABLE结构

Question

假设在MariaDB中有一个电商数据库，包含商品表products（product_id, product_name, price, category_id等字段）、订单表orders（order_id, customer_id, order_date等字段）和订单详情表order_items（order_item_id, order_id, product_id, quantity等字段）。现在要查询每个客户购买的各类商品的总金额，并按客户ID和商品类别ID排序。请描述如何对这些TABLE结构进行优化，以提高该复杂查询的执行效率，并给出优化后的SQL查询语句。

Answer 1

表结构优化

1. 添加索引：
   • 在products表的product_id和category_id字段上添加索引。因为查询涉及到按商品类别统计金额，category_id索引有助于快速定位商品类别相关记录。

   CREATE INDEX idx_product_id ON products(product_id);
   CREATE INDEX idx_category_id ON products(category_id);
   

   • 在orders表的customer_id和order_id字段上添加索引。customer_id索引用于快速定位每个客户的订单，order_id索引有助于连接orders表和order_items表。

   CREATE INDEX idx_customer_id ON orders(customer_id);
   CREATE INDEX idx_order_id_orders ON orders(order_id);
   

   • 在order_items表的order_id、product_id字段上添加索引。order_id索引用于连接orders表和order_items表，product_id索引用于连接order_items表和products表。

   CREATE INDEX idx_order_id_order_items ON order_items(order_id);
   CREATE INDEX idx_product_id_order_items ON order_items(product_id);
   

优化后的SQL查询语句

SELECT 
    o.customer_id,
    p.category_id,
    SUM(oi.quantity * p.price) AS total_amount
FROM 
    orders o
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id
GROUP BY 
    o.customer_id, p.category_id
ORDER BY 
    o.customer_id, p.category_id;