MySQL索引优化与EXPLAIN分析
0
3
2026年6月12日
数据库优化能带来数倍的性能提升，索引优化解决90%的慢查询问题。本文分享3个完整可运行的SQL脚本，涵盖MySQL核心优化技能。
完整索引优化实战
正确的索引设计是数据库性能的基石。
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建单列索引
CREATE INDEX idx_users_email ON users(email);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- 创建联合索引（最左前缀原则）
CREATE INDEX idx_status_created ON users(status, created_at);
-- 插入测试数据
INSERT INTO users (username, email, status) VALUES
('user1', 'user1@example.com', 1),
('user2', 'user2@example.com', 1),
('user3', 'user3@example.com', 0);
-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE email = 'user1@example.com';
-- 覆盖索引优化（避免回表）
EXPLAIN SELECT id, email FROM users WHERE email = 'user1@example.com';
-- 时间范围查询优化
EXPLAIN SELECT * FROM users
WHERE created_at >= '2024-01-01'
AND created_at < '2024-01-02';完整JOIN查询优化
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,
INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO orders (user_id, order_no, amount) VALUES
(1, 'ORD001', 99.00),
(1, 'ORD002', 199.00),
(2, 'ORD003', 299.00);
-- INNER JOIN查询
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, o.amount
FROM users u
STRAIGHT_JOIN orders o ON u.id = o.user_id;
-- 创建联合覆盖索引
CREATE INDEX idx_order_user_amount ON orders(user_id, amount);
-- GROUP BY优化
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);
-- 悲观锁（FOR UPDATE）
START TRANSACTION;
SELECT * FROM goods WHERE id = 1 FOR UPDATE;
UPDATE goods SET stock = stock - 1 WHERE id = 1;
COMMIT;
-- 查看更新后的数据
SELECT stock, version FROM goods WHERE id = 1;
-- 乐观锁（版本号）
UPDATE goods
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 1;
-- 检查影响行数
SELECT ROW_COUNT();
-- 查看最终库存
SELECT * FROM goods;MySQL最佳实践
- 使用合适的数据类型，避免NULL，优先INT UNSIGNED
- 使用utf8mb4字符集支持完整emoji
- InnoDB引擎支持事务和行锁
- 自增INT主键，不使用UUID
- 大数据分页：WHERE id > last_id LIMIT 20
- 批量操作：每次1000条记录
- 慢查询日志：long_query_time = 1秒
技术分享，欢迎评论区交流讨论。
在线咨询
上一个应该是我，我买了一年，实在没价值，...