MySQL视图与触发器应用
0
1
2026年7月6日
主从复制配置
-- 主库配置
-- my.cnf: server-id=1, log-bin=mysql-bin
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
-- 从库配置
-- my.cnf: server-id=2, relay-log=relay-bin
CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=156;
START SLAVE;
SHOW SLAVE STATUS\GJOIN查询优化
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
order_no VARCHAR(32) NOT NULL,
amount DECIMAL(10,2) NOT NULL DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_user_amount (user_id, amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SELECT u.id, u.username, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;
SELECT u.id, u.username, SUM(o.amount) as total
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
EXPLAIN SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;存储过程
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN p_user_id INT)
BEGIN
SELECT o.*, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.user_id = p_user_id;
END //
CREATE PROCEDURE CreateOrder(
IN p_user_id INT,
IN p_amount DECIMAL(10,2),
OUT p_order_id INT
)
BEGIN
INSERT INTO orders (user_id, order_no, amount)
VALUES (p_user_id, CONCAT('ORD', UNIX_TIMESTAMP()), p_amount);
SET p_order_id = LAST_INSERT_ID();
END //
DELIMITER ;
CALL GetUserOrders(1);
CALL CreateOrder(1, 99.99, @order_id);
SELECT @order_id;表设计规范
-- 推荐的表设计
CREATE TABLE example (
id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID',
title VARCHAR(100) NOT NULL DEFAULT '' COMMENT '标题',
content TEXT COMMENT '内容',
status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态',
sort INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '排序',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_user_id (user_id),
KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='示例表';视图应用
CREATE VIEW v_user_order_summary AS
SELECT
u.id,
u.username,
COUNT(o.id) as order_count,
SUM(o.amount) as total_amount,
MAX(o.created_at) as last_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
SELECT * FROM v_user_order_summary WHERE order_count > 0;分页优化
-- 普通分页（越往后越慢）
SELECT * FROM orders ORDER BY id LIMIT 10000, 10;
-- 优化：使用WHERE条件
SELECT * FROM orders
WHERE id > 10000
ORDER BY id LIMIT 10;
-- 优化：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY id LIMIT 10000, 10
) t ON o.id = t.id;技术分享，欢迎评论区交流讨论。
在线咨询
上一个应该是我，我买了一年，实在没价值，...