MySQL主从复制延迟怎么处理

工作中用到了这个技术，把学习和实践过程记录一下。代码都在本地跑通过了，环境是Python 3.11。

先搞清楚原理

要理解MySQL，先得搞清楚查询执行计划这个机制。直接看代码比看文字描述来得快：

-- 优化查询示例
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transactions_created_at
ON transactions (created_at DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transactions_status_created
ON transactions (status, created_at DESC);

SELECT id, name, status, created_at
FROM transactions
WHERE status = 'active'
  AND created_at < '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM transactions
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;

实际怎么用

写代码的时候，MySQL有几个常见写法。推荐用下面这种方式，代码更清晰，也更容易维护：

-- 事务与并发控制
BEGIN;

SELECT * FROM products WHERE id = 3 FOR UPDATE;

UPDATE products
SET updated_at = NOW()
WHERE id = 3;

-- 乐观锁
UPDATE products
SET version = version + 1, value = 'new_value'
WHERE id = 3 AND version = 5;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

COMMIT;

-- 死锁检测
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
WHERE blocked_locks.granted IS FALSE;

进阶一点的用法

MySQL在复杂场景下的用法和简单场景差别挺大。主要是查询执行计划这块需要额外处理，代码如下：

容易踩的坑

踩坑经验分享：MySQL在查询执行计划方面有几个暗坑。最常见的是参数默认值不符合预期，其次是并发场景下的竞态条件。建议加上防御性检查。

写在最后

MySQL这块先整理到这里。连接管理是最重要的部分，务必理解透彻。如果遇到问题，先检查配置和版本，大部分报错都是这两个原因。