MySQL主从复制配置教程
0
7
2026年6月19日
索引优化实战
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
status TINYINT DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email),
INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE UNIQUE INDEX idx_username ON users(username);
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
EXPLAIN SELECT id, email FROM users WHERE email = 'user@example.com';
EXPLAIN SELECT * FROM users WHERE created_at >= '2024-01-01';JOIN查询优化
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;事务与锁机制
DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
stock INT UNSIGNED NOT NULL DEFAULT 0,
version INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO goods (name, stock, version) VALUES ('iPhone 15', 100, 1);
START TRANSACTION;
SELECT * FROM goods WHERE id = 1 FOR UPDATE;
UPDATE goods SET stock = stock - 1 WHERE id = 1;
COMMIT;
UPDATE goods
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 1;
SELECT ROW_COUNT();存储过程
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 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;触发器
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE users
SET order_count = order_count + 1
WHERE id = NEW.user_id;
END //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END //
DELIMITER ;最佳实践
使用合适的数据类型，避免NULL，优先使用INT UNSIGNED，使用utf8mb4字符集，使用InnoDB引擎，使用自增INT主键，大数据分页使用WHERE id > last_id LIMIT 20，批量操作每次1000条记录。
技术分享，欢迎评论区交流讨论。
在线咨询
上一个应该是我，我买了一年，实在没价值，...