# 场景：复杂查询优化
MariaDB [fgedudb]> CREATE TEMPORARY TABLE fgedu_temp_order_stats (
-> user_id INT,
-> order_count INT,
-> total_amount DECIMAL(10,2),
-> last_order_date DATETIME
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
# 插入统计数据
MariaDB [fgedudb]> INSERT INTO fgedu_temp_order_stats
-> SELECT
-> user_id,
-> COUNT(*) AS order_count,
-> SUM(total_amount) AS total_amount,
-> MAX(created_at) AS last_order_date
-> FROM fgedu_orders
-> GROUP BY user_id;
Query OK, 1000 rows affected (0.05 sec)
Records: 1000 Duplicates: 0 Warnings: 0
# 分析用户购买行为
MariaDB [fgedudb]> SELECT
-> CASE
-> WHEN order_count = 1 THEN ‘新用户’
-> WHEN order_count BETWEEN 2 AND 5 THEN ‘活跃用户’
-> ELSE ‘忠诚用户’
-> END AS user_type,
-> COUNT(*) AS user_count,
-> AVG(total_amount) AS avg_amount,
-> MAX(total_amount) AS max_amount
-> FROM fgedu_temp_order_stats
-> GROUP BY user_type;
+———–+————+————-+————+
| user_type | user_count | avg_amount | max_amount |
+———–+————+————-+————+
| 新用户 | 500 | 1000.00 | 5000.00 |
| 活跃用户 | 300 | 3000.00 | 15000.00 |
| 忠诚用户 | 200 | 8000.00 | 50000.00 |
+———–+————+————-+————+
# 场景：数据导入中间表
MariaDB [fgedudb]> CREATE TEMPORARY TABLE fgedu_temp_import (
-> id INT,
-> name VARCHAR(50),
-> email VARCHAR(100),
-> age INT
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
# 导入数据
MariaDB [fgedudb]> LOAD DATA INFILE ‘/mariadb/data/users.csv’ INTO TABLE fgedu_temp_import
-> FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
-> LINES TERMINATED BY ‘\n’
-> IGNORE 1 ROWS;
Query OK, 10000 rows affected (0.10 sec)
Records: 10000 Duplicates: 0 Warnings: 0
# 数据清洗
MariaDB [fgedudb]> DELETE FROM fgedu_temp_import WHERE age < 18;
Query OK, 500 rows affected (0.01 sec)
# 导入到正式表
MariaDB [fgedudb]> INSERT INTO fgedu_users (id, name, email, age)
-> SELECT id, name, email, age FROM fgedu_temp_import
-> ON DUPLICATE KEY UPDATE
-> name = VALUES(name),
-> email = VALUES(email),
-> age = VALUES(age);
Query OK, 10000 rows affected (0.15 sec)
Records: 10000 Duplicates: 0 Warnings: 0

# 场景：会话管理
MariaDB [fgedudb]> CREATE TABLE fgedu_session_cache (
-> session_id VARCHAR(32) PRIMARY KEY,
-> user_id INT,
-> data TEXT,
-> created_at DATETIME,
-> last_accessed DATETIME
-> ) ENGINE=MEMORY;
Query OK, 0 rows affected (0.01 sec)
# 插入会话数据
MariaDB [fgedudb]> INSERT INTO fgedu_session_cache (session_id, user_id, data, created_at, last_accessed)
-> VALUES
-> (‘session1’, 1, ‘{“theme”: “dark”, “language”: “zh-CN”}’, NOW(), NOW()),
-> (‘session2’, 2, ‘{“theme”: “light”, “language”: “en-US”}’, NOW(), NOW());
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
# 查询会话数据
MariaDB [fgedudb]> SELECT * FROM fgedu_session_cache WHERE session_id = ‘session1’;
+————+———+——————————-+———————+———————+
| session_id | user_id | data | created_at | last_accessed |
+————+———+——————————-+———————+———————+
| session1 | 1 | {“theme”: “dark”, “language”: “zh-CN”} | 2023-01-01 12:00:00 | 2023-01-01 12:00:00 |
+————+———+——————————-+———————+———————+
# 更新会话数据
MariaDB [fgedudb]> UPDATE fgedu_session_cache
-> SET data = ‘{“theme”: “dark”, “language”: “zh-CN”, “notifications”: true}’,
-> last_accessed = NOW()
-> WHERE session_id = ‘session1’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 清理过期会话
MariaDB [fgedudb]> DELETE FROM fgedu_session_cache
-> WHERE last_accessed < DATE_SUB(NOW(), INTERVAL 30 MINUTE);
Query OK, 0 rows affected (0.00 sec)
# 场景：缓存热点数据
MariaDB [fgedudb]> CREATE TABLE fgedu_product_cache (
-> product_id INT PRIMARY KEY,
-> name VARCHAR(100),
-> price DECIMAL(10,2),
-> stock INT,
-> last_updated DATETIME
-> ) ENGINE=MEMORY;
Query OK, 0 rows affected (0.01 sec)
# 加载热点数据
MariaDB [fgedudb]> INSERT INTO fgedu_product_cache (product_id, name, price, stock, last_updated)
-> SELECT id, name, price, stock, NOW()
-> FROM fgedu_products
-> WHERE is_hot = 1;
Query OK, 100 rows affected (0.01 sec)
Records: 100 Duplicates: 0 Warnings: 0
# 查询热点数据
MariaDB [fgedudb]> SELECT * FROM fgedu_product_cache WHERE product_id = 1;
+————+———-+——-+——-+———————+
| product_id | name | price | stock | last_updated |
+————+———-+——-+——-+———————+
| 1 | Product 1 | 99.99 | 100 | 2023-01-01 12:00:00 |
+————+———-+——-+——-+———————+
# 定期更新缓存
MariaDB [fgedudb]> UPDATE fgedu_product_cache p
-> JOIN fgedu_products f ON p.product_id = f.id
-> SET p.price = f.price,
-> p.stock = f.stock,
-> p.last_updated = NOW();
Query OK, 10 rows affected (0.00 sec)
Rows matched: 100 Changed: 10 Warnings: 0

# 场景：秒杀系统优化
# 创建内存表存储秒杀商品
MariaDB [fgedudb]> CREATE TABLE fgedu_seckill_products (
-> product_id INT PRIMARY KEY,
-> name VARCHAR(100),
-> price DECIMAL(10,2),
-> original_price DECIMAL(10,2),
-> stock INT,
-> start_time DATETIME,
-> end_time DATETIME
-> ) ENGINE=MEMORY;
Query OK, 0 rows affected (0.01 sec)
# 插入秒杀商品
MariaDB [fgedudb]> INSERT INTO fgedu_seckill_products (product_id, name, price, original_price, stock, start_time, end_time)
-> VALUES
-> (1, ‘iPhone 14’, 4999.99, 5999.99, 100, ‘2023-01-01 10:00:00’, ‘2023-01-01 11:00:00’),
-> (2, ‘AirPods Pro’, 1299.99, 1999.99, 200, ‘2023-01-01 10:00:00’, ‘2023-01-01 11:00:00’);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
# 创建秒杀订单临时表
MariaDB [fgedudb]> CREATE TEMPORARY TABLE fgedu_seckill_orders (
-> order_id VARCHAR(32) PRIMARY KEY,
-> user_id INT,
-> product_id INT,
-> quantity INT,
-> price DECIMAL(10,2),
-> created_at DATETIME
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
# 秒杀处理存储过程
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE PROCEDURE fgedu_process_seckill (
-> IN p_user_id INT,
-> IN p_product_id INT,
-> IN p_quantity INT,
-> OUT p_result VARCHAR(50),
-> OUT p_order_id VARCHAR(32)
-> )
BEGIN
DECLARE v_stock INT;
DECLARE v_price DECIMAL(10,2);
DECLARE v_now DATETIME;
DECLARE v_order_id VARCHAR(32);
SET v_now = NOW();
SET v_order_id = CONCAT(‘SK’, DATE_FORMAT(v_now, ‘%Y%m%d%H%i%s’), LPAD(FLOOR(RAND() * 10000), 4, ‘0’));
— 检查秒杀时间
SELECT stock, price INTO v_stock, v_price
FROM fgedu_seckill_products
WHERE product_id = p_product_id
AND start_time <= v_now
AND end_time >= v_now;
IF v_stock IS NULL THEN
SET p_result = ‘秒杀活动未开始或已结束’;
SET p_order_id = NULL;
RETURN;
END IF;
— 检查库存
IF v_stock < p_quantity THEN
SET p_result = ‘库存不足’;
SET p_order_id = NULL;
RETURN;
END IF;
— 减库存
UPDATE fgedu_seckill_products
SET stock = stock – p_quantity
WHERE product_id = p_product_id
AND stock >= p_quantity;
IF ROW_COUNT() = 0 THEN
SET p_result = ‘库存不足’;
SET p_order_id = NULL;
RETURN;
END IF;
— 创建订单
INSERT INTO fgedu_seckill_orders (order_id, user_id, product_id, quantity, price, created_at)
VALUES (v_order_id, p_user_id, p_product_id, p_quantity, v_price, v_now);
SET p_result = ‘秒杀成功’;
SET p_order_id = v_order_id;
END //
DELIMITER;
Query OK, 0 rows affected (0.00 sec)
# 测试秒杀
MariaDB [fgedudb]> CALL fgedu_process_seckill(1, 1, 1, @result, @order_id);
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT @result, @order_id;
+———–+————————+
| @result | @order_id |
+———–+————————+
| 秒杀成功 | SK202301011200001234 |
+———–+————————+
# 查看库存
MariaDB [fgedudb]> SELECT product_id, name, stock FROM fgedu_seckill_products;
+————+———+——-+
| product_id | name | stock |
+————+———+——-+
| 1 | iPhone 14 | 99 |
| 2 | AirPods Pro | 200 |
+————+———+——-+
# 查看订单
MariaDB [fgedudb]> SELECT * FROM fgedu_seckill_orders;
+————————+———+————+———-+——-+———————+
| order_id | user_id | product_id | quantity | price | created_at |
+————————+———+————+———-+——-+———————+
| SK202301011200001234 | 1 | 1 | 1 | 4999.99 | 2023-01-01 12:00:00 |
+————————+———+————+———-+——-+———————+
# 高并发测试脚本
#!/bin/bash
# seckill_test.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
for i in {1..100}
do
mysql -u fgedu -pfgedu fgedudb -e “CALL fgedu_process_seckill($i, 1, 1, @result, @order_id);
SELECT @result, @order_id;
”
sleep 0.01
done
# 执行高并发测试
$ bash seckill_test.sh
# 查看最终库存
MariaDB [fgedudb]> SELECT product_id, name, stock FROM fgedu_seckill_products;
+————+———+——-+
| product_id | name | stock |
+————+———+——-+
| 1 | iPhone 14 | 0 |
| 2 | AirPods Pro | 200 |
+————+———+——-+
# 查看订单数量
MariaDB [fgedudb]> SELECT COUNT(*) FROM fgedu_seckill_orders;
+———-+
| COUNT(*) |
+———-+
| 100 |
+———-+