数据库执行计划设计：实时分析场景最佳实践-冉冉博客

作为冉冉博客的技术分享系列，今天深入探讨数据库执行计划设计。这些技巧在我博客运营过程中都经过实战验证，希望能帮到你。

一、MySQL 索引设计与优化策略

索引是 MySQL 性能优化的核心。合理的索引设计能让查询速度提升 10-100 倍，但设计不当反而会拖慢写入性能。冉冉博客在大量实际项目中总结出这套索引优化方法论。

1.1 B-Tree 与 Hash 索引的选择

-- 创建复合索引，遵循最左前缀原则
CREATE INDEX idx_orders ON orders(customer_id, status, created_at);

-- 以下查询都能使用索引
SELECT * FROM orders WHERE customer_id = 100;
SELECT * FROM orders WHERE customer_id = 100 AND status = 'paid';

-- 以下查询无法使用索引
SELECT * FROM orders WHERE status = 'paid';

1.2 覆盖索引避免回表

-- 覆盖索引，无需回表
CREATE INDEX idx_user_email_covered ON users(email, name);
SELECT email, name FROM users WHERE email = 'test' + '@' + 'example.com';

-- EXPLAIN 分析
EXPLAIN SELECT email, name FROM users WHERE email = 'test@example.com';
-- 看到 Using index condition 表示使用了覆盖索引

二、慢查询分析与执行计划

优化 MySQL 性能的第一步是找到慢查询。MySQL 提供了慢查询日志和 EXPLAIN 命令，帮助我们定位性能瓶颈。

2.1 开启并分析慢查询日志

-- 配置文件 my.cnf 开启慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

-- 实时查看当前正在执行的慢查询
SHOW FULL PROCESSLIST;

-- 使用 mysqldumpslow 统计
mysqldumpslow -t 10 /var/log/mysql/slow.log

2.2 EXPLAIN 深入理解查询执行计划

EXPLAIN SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id
ORDER BY order_count DESC;

-- type: ALL = 全表扫描（最差），range = 范围扫描（可接受），ref = 索引查找（好）
-- key: 实际使用的索引
-- rows: 扫描的行数，越少越好
-- Extra: Using filesort = 额外排序（慢），Using index = 覆盖索引（快）

三、表结构设计最佳实践

好的表结构设计是性能的基石。数据类型选择、范式化与反范式化的平衡、分区表策略都会直接影响查询性能。

3.1 数据类型选择原则

-- 整型选择
TINYINT UNSIGNED  -- 0-255，1字节，适合状态码
SMALLINT          -- -32768~32767，2字节
INT / BIGINT      -- 根据实际数据量选择

-- VARCHAR vs CHAR
-- CHAR(32) 固定32字符，适合定长数据如MD5哈希值
-- VARCHAR(255) 变长，但不要在变长字段上建太长索引
-- 索引最大长度767字节（InnoDB），UTF8MB4 下 VARCHAR(191)是索引安全上限

-- 日期类型推荐 DATETIME（范围更大）

3.2 分区表实战

CREATE TABLE access_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100),
    created_at DATETIME,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查询特定分区的数据（MySQL 自动只扫描对应分区）
SELECT * FROM access_logs 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

四、事务隔离与锁机制

理解 MySQL 的事务隔离级别和锁机制是编写正确并发应用的前提。隔离级别越高，数据一致性越好，但并发性能越差。

4.1 四种隔离级别详解

-- 查看当前会话隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- READ UNCOMMITTED - 可能脏读
-- READ COMMITTED - 可能不可重复读（Oracle默认）
-- REPEATABLE READ - MySQL默认，可能幻读
-- SERIALIZABLE - 最高，但性能最差

4.2 行锁、表锁与死锁处理

-- 查看当前锁状态
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 避免死锁：按固定顺序访问表
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE orders SET status = 'paid' WHERE id = 100;
COMMIT;

-- 减少事务大小，避免长事务
BEGIN;
UPDATE orders SET status = 'shipped' WHERE created_at < '2023-01-01' LIMIT 1000;
COMMIT;

五、主从复制与高可用架构

对于高可用架构，MySQL 主从复制是标配。从库提供读写分离，主库故障时可以快速切换。

5.1 GTID 模式的配置与应用

-- 主库配置（my.cnf）
server-id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW

-- 从库配置
server-id = 2
gtid_mode = ON
relay_log = /var/lib/mysql/mysql-relay-bin

-- 配置复制
CHANGE MASTER TO
    MASTER_HOST = 'master_host',
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'repl_password',
    MASTER_AUTO_POSITION = 1;

START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUSG

5.2 读写分离与故障切换

-- ProxySQL 配置：SELECT 读从库，其他走主库
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, 'master.db.com', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'slave1.db.com', 3306);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT', 1, 1);

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

以上就是数据库执行计划设计的完整分享。这些技巧都来自冉冉博客的实战经验，希望能帮到你。有问题欢迎留言交流！

文章版权归作者所有，未经允许请勿转载。

THE END