如约数科

综合实战：电商订单查询系统

引言本文通过电商订单系统的实战案例，综合运用前面学到的SQL知识，实现各种复杂的业务查询。一、表结构设计 1.1 核心表 -- 用户表 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100), phone VARCHAR(20), register_date DATE, level INT DEFAULT 1 -- 用户等级 ); -- 商品表 CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL, category VARCHAR(50), price DECIMAL(10,2), stock INT DEFAULT 0, sales INT DEFAULT 0 ); -- 订单表 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(50) UNIQUE, user_id INT, total_amount DECIMAL(10,2), status VARCHAR(20), -- pending, paid, shipped, completed, cancelled created_at DATETIME, paid_at DATETIME, FOREIGN KEY (user_id) REFERENCES users(id) ); -- 订单明细表 CREATE TABLE order_items ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT, product_id INT, quantity INT, price DECIMAL(10,2), -- 下单时价格 FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) ); 二、基础查询实战 2.1 用户订单查询 -- 查询用户的所有订单 SELECT u.username, o.order_no, o.total_amount, o.status, DATE_FORMAT(o.created_at, '%Y-%m-%d %H:%i') AS order_time FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.id = 1 ORDER BY o.created_at DESC; 2.2 订单详情查询 -- 查询订单明细（包含商品信息） SELECT o.order_no, p.name AS product_name, oi.quantity, oi.price, oi.quantity * oi.price AS subtotal FROM orders o INNER JOIN order_items oi ON o.id = oi.order_id INNER JOIN products p ON oi.product_id = p.id WHERE o.order_no = 'ORD20241121000001'; 三、统计分析实战 3.1 用户消费统计 -- 统计每个用户的消费情况 SELECT u.id, u.username, u.level, COUNT(o.id) AS order_count, IFNULL(SUM(o.total_amount), 0) AS total_spent, ROUND(AVG(o.total_amount), 2) AS avg_order_value, MAX(o.created_at) AS last_order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username, u.level ORDER BY total_spent DESC; 3.2 商品销售排行 -- 销量Top 10商品 SELECT p.name, p.category, SUM(oi.quantity) AS total_sales, SUM(oi.quantity * oi.price) AS total_revenue FROM products p INNER JOIN order_items oi ON p.id = oi.product_id INNER JOIN orders o ON oi.order_id = o.id WHERE o.status = 'completed' GROUP BY p.id, p.name, p.category ORDER BY total_sales DESC LIMIT 10; 3.3 按月销售统计 -- 每月销售额趋势 SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS order_count, COUNT(DISTINCT user_id) AS customer_count, SUM(total_amount) AS total_sales, ROUND(AVG(total_amount), 2) AS avg_order_value FROM orders WHERE status = 'completed' AND created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH) GROUP BY DATE_FORMAT(created_at, '%Y-%m') ORDER BY month; 四、高级查询实战 4.1 用户购买行为分析 -- 分析用户首次购买、最近购买、购买频次 SELECT u.username, u.register_date, MIN(o.created_at) AS first_order_date, MAX(o.created_at) AS last_order_date, COUNT(o.id) AS order_count, TIMESTAMPDIFF(DAY, u.register_date, MIN(o.created_at)) AS days_to_first_order, TIMESTAMPDIFF(DAY, MIN(o.created_at), MAX(o.created_at)) AS active_days FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username, u.register_date HAVING order_count > 0; 4.2 复购率分析 -- 计算复购率（购买2次及以上的用户占比） SELECT COUNT(DISTINCT user_id) AS total_customers, COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) AS repeat_customers, ROUND(COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2) AS repeat_rate FROM ( SELECT user_id, COUNT(*) AS order_count FROM orders WHERE status = 'completed' GROUP BY user_id ) t; 4.3 客户分层（RFM模型） -- RFM分析：Recency（最近购买）、Frequency（购买频次）、Monetary（消费金额） SELECT user_id, username, DATEDIFF(NOW(), last_order_date) AS recency, order_count AS frequency, total_amount AS monetary, CASE WHEN DATEDIFF(NOW(), last_order_date) <= 30 AND order_count >= 5 AND total_amount >= 10000 THEN 'VIP客户' WHEN DATEDIFF(NOW(), last_order_date) <= 90 AND order_count >= 3 THEN '活跃客户' WHEN DATEDIFF(NOW(), last_order_date) > 180 THEN '流失客户' ELSE '普通客户' END AS customer_segment FROM ( SELECT u.id AS user_id, u.username, MAX(o.created_at) AS last_order_date, COUNT(o.id) AS order_count, SUM(o.total_amount) AS total_amount FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username ) t ORDER BY monetary DESC; 五、复杂业务场景 5.1 购物车转化率 -- 统计下单未支付的订单（购物车） SELECT COUNT(*) AS cart_count, SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count, ROUND(SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS conversion_rate FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY); 5.2 商品关联分析 -- 查找经常一起购买的商品 SELECT p1.name AS product1, p2.name AS product2, COUNT(*) AS co_purchase_count FROM order_items oi1 INNER JOIN order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id INNER JOIN products p1 ON oi1.product_id = p1.id INNER JOIN products p2 ON oi2.product_id = p2.id GROUP BY p1.id, p1.name, p2.id, p2.name ORDER BY co_purchase_count DESC LIMIT 10; 5.3 库存预警 -- 库存不足且销售较好的商品 SELECT name, category, stock, sales, CASE WHEN stock = 0 THEN '缺货' WHEN stock < sales * 0.1 THEN '严重不足' WHEN stock < sales * 0.3 THEN '库存紧张' ELSE '正常' END AS stock_status FROM products WHERE stock < sales * 0.3 ORDER BY stock_status, sales DESC; 六、性能优化实战 6.1 创建索引 -- 为常用查询字段创建索引 CREATE INDEX idx_user_id ON orders(user_id); CREATE INDEX idx_status ON orders(status); CREATE INDEX idx_created_at ON orders(created_at); CREATE INDEX idx_order_id ON order_items(order_id); CREATE INDEX idx_product_id ON order_items(product_id); -- 联合索引 CREATE INDEX idx_status_user_id ON orders(status, user_id); CREATE INDEX idx_status_created_at ON orders(status, created_at); 6.2 创建视图简化查询 -- 用户订单统计视图 CREATE VIEW v_user_order_stats AS SELECT u.id AS user_id, u.username, u.level, COUNT(o.id) AS order_count, IFNULL(SUM(o.total_amount), 0) AS total_spent, MAX(o.created_at) AS last_order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username, u.level; -- 使用视图 SELECT * FROM v_user_order_stats WHERE total_spent > 10000; 七、总结知识点回顾本案例综合运用了： ...

索引的本质：为什么索引能加速查询？

引言索引是数据库性能优化的关键。理解索引的本质，才能正确使用索引。一、没有索引的查询 1.1 全表扫描 -- 查询id=1000的用户（无索引） SELECT * FROM users WHERE id = 1000; 执行过程：从第1行开始逐行检查id是否等于1000 找到后返回如果表有100万行，最坏情况需要扫描100万行时间复杂度：O(n) 1.2 问题查询慢（全表扫描）资源消耗大（CPU、磁盘IO）并发性能差二、索引的本质 2.1 从二分查找说起 # 在有序数组中查找 def binary_search(arr, target): left, right = 0, len(arr) - 1 while left <= right: mid = (left + right) // 2 if arr[mid] == target: return mid elif arr[mid] < target: left = mid + 1 else: right = mid - 1 return -1 时间复杂度：O(log n) 关键：数据有序 + 快速定位 2.2 索引的原理索引是一种排序的数据结构，存储列值和行位置的映射。索引结构（简化示例）： +-------+-----------+ | id值 | 行位置 | +-------+-----------+ | 1 | 0x1000 | | 10 | 0x1050 | | 100 | 0x1100 | | 1000 | 0x2000 | <-- 通过索引快速定位 +-------+-----------+ 查询过程： ...

B+树详解：MySQL索引的数据结构

引言 MySQL的InnoDB引擎使用B+树作为索引的数据结构。理解B+树是掌握索引优化的基础。一、从二叉树到B+树的演进 1.1 二叉查找树（BST） 10 / \ 5 15 / \ / \ 3 7 12 20 问题：可能退化为链表，查询O(n) 1.2 平衡二叉树（AVL）通过旋转保持平衡，查询O(log n)。问题：每个节点只存1个元素树高度大：100万数据需要20层磁盘IO次数多（每层一次IO） 1.3 B树 [10, 20, 30] / | | \ [1,5] [12,15] [22,25] [35,40] 特点：多路：每个节点存多个元素所有节点都存数据降低树高度问题：非叶子节点也存数据，占用空间范围查询需要中序遍历 1.4 B+树非叶子节点（只存索引）： [10, 20, 30] / | | \ / | | \ 叶子节点（存数据+索引）： [1,5,8] → [10,12,15] → [20,22,25] → [30,35,40] ↑ 双向链表 ↑ 特点： ...

索引类型：主键索引、唯一索引、普通索引、全文索引

引言 MySQL支持多种索引类型，每种有不同的特点和适用场景。一、主键索引（PRIMARY KEY） 1.1 特点唯一且非空聚簇索引：数据和索引存在一起每个表只能有一个主键 1.2 创建 -- 方式1：建表时指定 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) ); -- 方式2：后续添加 ALTER TABLE users ADD PRIMARY KEY (id); 1.3 聚簇索引（Clustered Index） InnoDB的主键索引是聚簇索引：叶子节点存储完整的数据行表数据按主键顺序存储 B+树结构： [10, 20] / | \ [1→row] [10→row] [20→row] ← 叶子节点存完整数据优势：主键查询快（一次IO获取完整数据）范围查询快（数据有序）劣势：主键不能太长（影响所有二级索引）插入乱序会导致页分裂二、唯一索引（UNIQUE INDEX） 2.1 特点值必须唯一允许NULL（但只能有一个NULL）可以有多个唯一索引 2.2 创建 -- 方式1：建表时 CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(100) UNIQUE ); -- 方式2：单独创建 CREATE UNIQUE INDEX idx_email ON users(email); -- 方式3：ALTER TABLE ALTER TABLE users ADD UNIQUE INDEX idx_email (email); 2.3 使用场景 -- 用户名、邮箱、手机号等唯一字段 CREATE UNIQUE INDEX idx_username ON users(username); CREATE UNIQUE INDEX idx_phone ON users(phone); 三、普通索引（NORMAL INDEX） 3.1 特点最基本的索引值可以重复也称为二级索引（Secondary Index） 3.2 创建 -- 方式1：CREATE INDEX CREATE INDEX idx_name ON users(name); -- 方式2：ALTER TABLE ALTER TABLE users ADD INDEX idx_age (age); -- 方式3：建表时 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), INDEX idx_name (name) ); 3.3 二级索引（非聚簇索引）存储结构： ...

创建索引的最佳实践

一、何时创建索引 1.1 适合创建索引的场景 -- 1. WHERE条件列 SELECT * FROM orders WHERE user_id = 123; CREATE INDEX idx_user_id ON orders(user_id); -- 2. ORDER BY排序列 SELECT * FROM products ORDER BY price DESC; CREATE INDEX idx_price ON products(price); -- 3. GROUP BY分组列 SELECT category, COUNT(*) FROM products GROUP BY category; CREATE INDEX idx_category ON products(category); -- 4. JOIN连接列 SELECT * FROM orders o JOIN users u ON o.user_id = u.id; CREATE INDEX idx_user_id ON orders(user_id); -- 5. DISTINCT去重列 SELECT DISTINCT category FROM products; CREATE INDEX idx_category ON products(category); 1.2 不适合创建索引的场景 -- 1. 小表（< 1000行） -- 全表扫描更快 -- 2. 频繁更新的列 -- 维护索引代价高 -- 3. 区分度低的列（选择性 < 0.01） SELECT * FROM users WHERE gender = '男'; -- 50%数据 -- 不如全表扫描 -- 4. 不在WHERE/ORDER BY/JOIN中使用的列 -- 纯粹浪费空间二、选择索引列的原则 2.1 选择性高的列 -- 计算列的选择性 SELECT COUNT(DISTINCT column) / COUNT(*) AS selectivity FROM table_name; -- 选择性 > 0.1：适合索引 -- 选择性 < 0.01：不适合索引示例： ...

索引失效的场景分析

引言创建了索引不一定会使用。理解索引失效的场景，才能写出高效的SQL。一、违反最左前缀原则 1.1 联合索引规则 -- 索引 CREATE INDEX idx_a_b_c ON table(a, b, c); -- ✅ 使用索引 WHERE a = 1; WHERE a = 1 AND b = 2; WHERE a = 1 AND b = 2 AND c = 3; -- ❌ 不使用索引 WHERE b = 2; -- 缺少a WHERE c = 3; -- 缺少a和b WHERE b = 2 AND c = 3; -- 缺少a 1.2 跳跃列 -- 索引 CREATE INDEX idx_a_b_c ON table(a, b, c); -- ⚠️ 部分使用索引 WHERE a = 1 AND c = 3; -- 只使用a，c失效二、索引列上使用函数 2.1 函数导致失效 -- ❌ 失效：YEAR函数 SELECT * FROM orders WHERE YEAR(created_at) = 2024; -- ✅ 改进：使用范围查询 SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31 23:59:59'; 2.2 常见函数失效案例 -- ❌ 失效 WHERE DATE(created_at) = '2024-11-21'; WHERE LEFT(name, 3) = 'abc'; WHERE UPPER(email) = 'USER@EXAMPLE.COM'; WHERE price * 0.8 > 100; -- ✅ 改进 WHERE created_at >= '2024-11-21' AND created_at < '2024-11-22'; WHERE name LIKE 'abc%'; WHERE email = 'user@example.com'; -- 应用层转小写 WHERE price > 125; -- 100 / 0.8 三、隐式类型转换 3.1 字符串与数字 -- 表结构 CREATE TABLE users ( id INT, phone VARCHAR(20) -- 字符串类型 ); CREATE INDEX idx_phone ON users(phone); -- ❌ 失效：数字查询字符串列 SELECT * FROM users WHERE phone = 13800138000; -- 数字 -- MySQL转换为：WHERE CAST(phone AS SIGNED) = 13800138000 -- 索引失效 -- ✅ 使用索引 SELECT * FROM users WHERE phone = '13800138000'; -- 字符串 3.2 反向情况 -- 表结构 CREATE TABLE users ( id INT, age INT -- 数字类型 ); CREATE INDEX idx_age ON users(age); -- ✅ 使用索引：字符串查询数字列 SELECT * FROM users WHERE age = '20'; -- MySQL转换为：WHERE age = CAST('20' AS SIGNED) -- 索引仍然有效规则：字符串列查询时必须用字符串类型。 ...

联合索引：最左前缀原则

一、联合索引基础 1.1 什么是联合索引 -- 单列索引 CREATE INDEX idx_a ON table(a); CREATE INDEX idx_b ON table(b); -- 联合索引（多列组合） CREATE INDEX idx_a_b ON table(a, b); CREATE INDEX idx_a_b_c ON table(a, b, c); 1.2 存储结构联合索引 idx_a_b_c(a, b, c)的B+树： [(1,1,1), (1,1,2), (1,2,1), (2,1,1)] ↓ 先按a排序 [(1,1,1), (1,1,2), (1,2,1)] ← a相同 ↓ 再按b排序 [(1,1,1), (1,1,2)] ← a、b相同 ↓ 最后按c排序二、最左前缀原则 2.1 核心规则 CREATE INDEX idx_a_b_c ON table(a, b, c); -- ✅ 可以使用索引 WHERE a = 1; -- 使用(a) WHERE a = 1 AND b = 2; -- 使用(a,b) WHERE a = 1 AND b = 2 AND c = 3; -- 使用(a,b,c) WHERE a = 1 AND c = 3; -- 使用(a)，c失效 -- ❌ 不能使用索引 WHERE b = 2; -- 缺少a WHERE c = 3; -- 缺少a WHERE b = 2 AND c = 3; -- 缺少a 2.2 原理联合索引按从左到右的顺序排序： ...

覆盖索引：减少回表查询

一、什么是覆盖索引 1.1 回表查询 -- 表结构 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), age INT, email VARCHAR(100) ); CREATE INDEX idx_name ON users(name); -- 查询 SELECT * FROM users WHERE name = '张三'; 查询过程：在idx_name索引找到name=‘张三’，获取id=10 回表：到主键索引查找id=10的完整数据返回结果 IO次数：2次（索引1次 + 回表1次） 1.2 覆盖索引 -- 查询 SELECT id, name FROM users WHERE name = '张三'; 查询过程：在idx_name索引找到name=‘张三’ 索引中已包含id和name 直接返回，无需回表 IO次数：1次（只查索引）覆盖索引：查询的列都在索引中，无需回表。二、覆盖索引的优势 2.1 减少IO -- 无覆盖索引 SELECT * FROM users WHERE name = '张三'; -- IO：索引 + 回表 = 2次 -- 覆盖索引 SELECT id, name FROM users WHERE name = '张三'; -- IO：索引 = 1次 2.2 提升性能 -- 测试 -- 100万数据，查询10000次 -- SELECT *（需要回表） -- 耗时：5秒 -- SELECT id, name（覆盖索引） -- 耗时：0.5秒 -- 性能提升：10倍三、如何实现覆盖索引 3.1 主键索引天然覆盖 -- 主键索引是聚簇索引，包含完整数据 SELECT * FROM users WHERE id = 10; -- 覆盖索引 3.2 设计联合索引 -- 查询 SELECT id, name, age FROM users WHERE name = '张三'; -- 索引设计 CREATE INDEX idx_name_age ON users(name, age); -- 索引包含：name, age, id（主键自动包含） -- 覆盖查询所需列 3.3 添加冗余列 -- 查询 SELECT user_id, order_no, status FROM orders WHERE user_id = 123; -- 索引设计 CREATE INDEX idx_user_no_status ON orders(user_id, order_no, status); -- order_no和status可能不在WHERE中，但在SELECT中 -- 添加到索引实现覆盖四、EXPLAIN中的覆盖索引 4.1 Using index EXPLAIN SELECT id, name FROM users WHERE name = '张三'; 结果： ...

索引下推：ICP优化

一、什么是索引下推（ICP） 1.1 问题场景 CREATE INDEX idx_a_b ON table(a, b); SELECT * FROM table WHERE a = 1 AND b LIKE '%abc%'; 传统执行（无ICP）：使用索引找到a=1的所有记录回表获取完整数据在Server层过滤b LIKE ‘%abc%’ 问题：大量无效回表。 1.2 ICP优化 ICP执行（MySQL 5.6+）：使用索引找到a=1的记录在索引中直接过滤b LIKE ‘%abc%’ 只回表符合条件的记录优势：减少回表次数。二、ICP原理 2.1 传统流程 vs ICP流程传统：索引层：找到a=1的10条记录 ↓ 回表10次存储引擎：获取10条完整数据 ↓ Server层：过滤b，最终2条符合 ICP：索引层：找到a=1的10条记录 ↓ 在索引中过滤b 索引层：10条中2条符合b条件 ↓ 只回表2次存储引擎：获取2条完整数据 2.2 关键下推：将Server层的过滤条件下推到存储引擎层。三、ICP的适用条件 3.1 启用条件 -- 查看ICP状态 SHOW VARIABLES LIKE 'optimizer_switch'; -- index_condition_pushdown=on -- 启用ICP SET optimizer_switch='index_condition_pushdown=on'; 3.2 使用场景 -- ✅ 可以使用ICP CREATE INDEX idx_a_b_c ON table(a, b, c); WHERE a = 1 AND b > 10 AND c = 3; -- a精确匹配 → 使用索引 -- b范围查询 → 使用索引 -- c过滤条件 → ICP下推到索引层 3.3 不适用场景 -- ❌ 主键索引（聚簇索引） -- 已经包含完整数据，无需下推 -- ❌ 覆盖索引 -- 不需要回表，无需下推 -- ❌ 全表扫描 -- 没有使用索引四、EXPLAIN中的ICP 4.1 识别标志 EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status LIKE '%pending%'; 结果： ...

EXPLAIN执行计划详解（上）：基础字段

一、EXPLAIN基础 1.1 什么是EXPLAIN EXPLAIN SELECT * FROM users WHERE id = 10; 作用：查看SQL执行计划不实际执行查询分析索引使用情况优化查询性能 1.2 基本用法 -- 方式1：EXPLAIN EXPLAIN SELECT ...; -- 方式2：DESCRIBE（同义词） DESCRIBE SELECT ...; -- 方式3：查看实际执行（MySQL 8.0.18+） EXPLAIN ANALYZE SELECT ...; 二、id - 查询标识 2.1 含义查询的执行顺序标识。 EXPLAIN SELECT * FROM users WHERE id = 10; 结果： id: 1 ← 简单查询 2.2 子查询 EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); 结果： id | table ----+-------- 1 | users 2 | orders ← 先执行id=2（子查询）规则： id大的先执行 id相同，从上往下执行三、select_type - 查询类型 3.1 SIMPLE 简单查询，无子查询或UNION。 ...