项目实战：电商库全流程

CREATE DATABASE IF NOT EXISTS shop2 DEFAULT CHARACTER SET utf8mb4;
USE shop2;
-- 从第 4 章复制五张表的 DDL，但删掉所有 KEY idx_* 行，只留 PRIMARY KEY 和 UNIQUE KEY

DELIMITER //
CREATE PROCEDURE gen_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  -- 用户 10 万
  WHILE i < 100000 DO
    INSERT INTO users (username, email, phone)
    VALUES (CONCAT('user', i), CONCAT('u', i, '@ex.com'),
            CONCAT('138', LPAD(i, 8, '0')));
    SET i = i + 1;
    IF i % 5000 = 0 THEN COMMIT; END IF;
  END WHILE;
 
  -- 分类与商品
  INSERT INTO categories (name, parent_id) VALUES
    ('数码',0),('手机',1),('电脑',1),('图书',0),('技术书',4);
  SET i = 0;
  WHILE i < 50000 DO
    INSERT INTO products (category_id, name, price, stock, status)
    VALUES (2 + FLOOR(RAND()*3), CONCAT('商品', i),
            ROUND(10 + RAND()*9990, 2), FLOOR(RAND()*1000),
            IF(RAND() < 0.9, 1, 2));
    SET i = i + 1;
    IF i % 5000 = 0 THEN COMMIT; END IF;
  END WHILE;
 
  -- 订单 100 万，时间散布一年
  SET i = 0;
  WHILE i < 1000000 DO
    INSERT INTO orders (order_no, user_id, status, total_amount, created_at)
    VALUES (CONCAT('SO', LPAD(i, 12, '0')),
            1 + FLOOR(RAND()*100000),
            FLOOR(RAND()*5),
            ROUND(10 + RAND()*20000, 2),
            NOW() - INTERVAL FLOOR(RAND()*365) DAY
                  - INTERVAL FLOOR(RAND()*86400) SECOND);
    SET i = i + 1;
    IF i % 5000 = 0 THEN COMMIT; END IF;
  END WHILE;
 
  -- 明细：每单 1~5 条
  INSERT INTO order_items (order_id, product_id, quantity, price)
  SELECT o.id, 1 + FLOOR(RAND()*50000), 1 + FLOOR(RAND()*3),
         ROUND(10 + RAND()*9990, 2)
  FROM orders o
  JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) x
  WHERE RAND() < 0.8;
  COMMIT;
END //
DELIMITER ;
 
SET autocommit = 0;
CALL gen_data();
COMMIT;
SET autocommit = 1;
 
ANALYZE TABLE users, products, orders, order_items;

-- 缩小版造数：把订单表放大到约 700 行
INSERT INTO orders (order_no, user_id, status, total_amount, created_at)
SELECT CONCAT(o.order_no, '-', d1.n, d2.n),
       o.user_id, o.status, o.total_amount, o.created_at
FROM orders o,
     (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d1,
     (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
      UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d2;
 
-- 先拆掉索引，让问题暴露：type=ALL + Using filesort
ALTER TABLE orders DROP INDEX idx_user_created;
ANALYZE TABLE orders;
EXPLAIN SELECT id, order_no, status, total_amount, created_at
FROM orders
WHERE user_id = 1 AND status IN (1,2,3)
ORDER BY created_at DESC
LIMIT 20;
 
-- 等值列 user_id 在前，排序列 created_at 在后
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
ANALYZE TABLE orders;
EXPLAIN SELECT id, order_no, status, total_amount, created_at
FROM orders
WHERE user_id = 1 AND status IN (1,2,3)
ORDER BY created_at DESC
LIMIT 20;

SELECT o.order_no, o.status, o.total_amount,
       oi.quantity, oi.price, p.name
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.order_no = 'SO000000004396';

ALTER TABLE order_items ADD INDEX idx_order (order_id);

-- 先看看结果对不对
SELECT id, name, price
FROM products
WHERE category_id = 2 AND status = 1 AND price BETWEEN 100 AND 6000
ORDER BY price
LIMIT 20;
 
EXPLAIN SELECT id, name, price
FROM products
WHERE category_id = 2 AND status = 1 AND price BETWEEN 100 AND 6000
ORDER BY price
LIMIT 20;
 
ALTER TABLE products ADD INDEX idx_cat_status_price (category_id, status, price);
ANALYZE TABLE products;
 
EXPLAIN SELECT id, name, price
FROM products
WHERE category_id = 2 AND status = 1 AND price BETWEEN 100 AND 6000
ORDER BY price
LIMIT 20;

SELECT DATE(created_at) AS d,
       COUNT(*) AS orders_cnt,
       SUM(CASE WHEN status IN (1,2,3) THEN total_amount ELSE 0 END) AS gmv,
       ROUND(AVG(status = 4), 4) AS cancel_rate
FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-04-01'
GROUP BY DATE(created_at)
ORDER BY d;
 
-- 反面教材：函数包住列，索引用不上
EXPLAIN SELECT COUNT(*) FROM orders WHERE DATE(created_at) = '2026-02-01';
 
ALTER TABLE orders ADD INDEX idx_created (created_at);
EXPLAIN SELECT COUNT(*) FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-04-01';

SELECT o.created_at, o.user_id, oi.quantity
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE oi.product_id = 12345
ORDER BY o.created_at DESC
LIMIT 10;

SELECT * FROM sys.schema_redundant_indexes;   -- 有没有把 (user_id) 和 (user_id, created_at) 都建了？
SELECT * FROM sys.schema_unused_indexes;      -- 跑一段时间后回头清理