1. SQL查询优化实战从基础到高级技巧我刚入行做DBA时遇到过这样一个场景电商大促期间商品列表页加载缓慢查询耗时超过5秒。通过分析发现问题出在一个简单的商品查询SQL上——开发人员写了全表扫描的查询语句。这个经历让我深刻认识到SQL优化不是纸上谈兵的理论而是直接影响业务性能的关键技能。1.1 基础查询优化原则索引是SQL优化的第一道防线。我常跟团队说没走索引的查询就像在图书馆里不查目录直接翻书架。举个例子电商系统中常见的用户订单查询-- 错误示范全表扫描 SELECT * FROM orders WHERE user_id 10086; -- 正确做法使用索引 ALTER TABLE orders ADD INDEX idx_user_id (user_id); EXPLAIN SELECT * FROM orders WHERE user_id 10086;执行计划中的type: ref表明使用了索引。但索引不是万能的我曾见过有人给所有字段都建索引结果写入性能下降50%。联合索引的字段顺序更有讲究-- 商品表的多条件查询 ALTER TABLE products ADD INDEX idx_category_status (category_id, status); -- 有效查询最左前缀匹配 SELECT * FROM products WHERE category_id 3 AND status onsale; -- 无效查询跳过第一个字段 SELECT * FROM products WHERE status onsale; -- 无法使用索引1.2 高级查询优化策略当数据量达到千万级时简单的索引优化可能不够。去年我们处理过一个报表系统查询超时问题最终通过子查询优化解决了-- 原始慢查询3.2秒 SELECT * FROM orders WHERE create_time 2023-01-01 ORDER BY total_amount DESC LIMIT 100; -- 优化后0.15秒 SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE create_time 2023-01-01 ORDER BY total_amount DESC LIMIT 100 ) AS tmp ON o.id tmp.id;这个案例的诀窍在于内层查询只排序主键外层再关联获取完整数据减少了排序的数据量。分页查询是另一个性能黑洞。常见的LIMIT 10000, 20会导致MySQL读取10020行再丢弃前10000行。我们的解决方案是-- 传统分页慢 SELECT * FROM products LIMIT 10000, 20; -- 优化分页快 SELECT * FROM products WHERE id 10000 LIMIT 20;2. 事务与并发控制的工程实践银行转账是最经典的事务案例但实际场景要复杂得多。我们曾处理过一个充值系统bug用户同时发起多笔充值由于并发控制不当导致余额异常。2.1 事务隔离级别实战不同的业务场景需要不同的隔离级别。在电商库存管理中我们采用READ COMMITTED而非默认的REPEATABLE READ-- 设置隔离级别 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 库存扣减事务 BEGIN; SELECT quantity FROM inventory WHERE product_id 123 FOR UPDATE; -- 检查库存充足后 UPDATE inventory SET quantity quantity - 1 WHERE product_id 123; COMMIT;FOR UPDATE是关键它给记录加排他锁防止其他事务同时修改。我们曾用JMeter压测发现不加锁在高并发下会导致超卖。2.2 死锁分析与解决死锁是并发控制的噩梦。去年双11我们的订单系统出现过这样一个死锁事务A: 1. 锁定了订单记录(id100) 2. 尝试锁定用户记录(id200) 事务B: 1. 锁定了用户记录(id200) 2. 尝试锁定订单记录(id100)解决方案是统一加锁顺序所有事务必须先锁用户记录再锁订单记录。我们还增加了死锁检测机制-- 设置死锁超时 SET innodb_lock_wait_timeout 5; -- 5秒超时3. 数据库恢复的工程化方案数据恢复不是理论而是每个DBA必须掌握的救命技能。我经历过最惊险的一次是主库SSD损坏靠着完善的备份策略才化险为夷。3.1 备份策略设计我们的生产环境采用全量增量备份组合每周日全量备份mysqldump每天增量备份binlog备份文件异地存储OSS本地# 全量备份 mysqldump -uroot -p --single-transaction --master-data2 dbname backup.sql # 增量备份定时同步binlog mysqlbinlog --read-from-remote-server --raw --stop-never hostname binlog.0001233.2 故障恢复演练每季度我们会模拟各种故障场景进行恢复演练包括误删表恢复-- 从binlog恢复 mysqlbinlog --start-position123456 /var/lib/mysql/binlog.000123 | mysql -uroot -p主从切换整库恢复最近一次演练发现10GB的数据库完整恢复需要18分钟达不到SLA要求。我们通过并行恢复优化到了7分钟。4. 高并发场景下的架构设计当QPS超过1万时单机MySQL就力不从心了。我们的社交平台项目通过分库分表支撑了百万级并发。4.1 分库分表实战用户表按uid范围分片// 分片算法 public String determineDataSource(long uid) { if(uid 0 uid 1000000) { return ds_0; } else if(uid 1000000 uid 2000000) { return ds_1; } // ... }全局ID生成采用雪花算法def next_id(): timestamp int(time.time() * 1000) return (timestamp 22) | (worker_id 12) | sequence4.2 读写分离优化对于读多写少的场景我们配置了1主3从架构-- 在从库执行 SET GLOBAL read_only ON;应用层通过中间件实现读写分离Bean public AbstractRoutingDataSource routingDataSource() { // 读请求路由到从库 if (isReadOperation()) { return slaveDataSource; } return masterDataSource; }5. 真实案例电商秒杀系统优化去年重构秒杀系统时我们遇到了库存超卖、系统崩溃等问题。最终方案包含以下关键点Redis预减库存def seckill(user_id, item_id): stock redis.decr(fitem:{item_id}:stock) if stock 0: return False # 异步生成订单 mq.send({user_id: user_id, item_id: item_id}) return TrueMQ异步下单KafkaListener(topics seckill_orders) public void handleOrder(OrderMessage message) { try { orderService.createOrder(message); } catch (Exception e) { // 恢复Redis库存 redisTemplate.opsForValue().increment( item:message.getItemId():stock); } }限流措施# Nginx层限流 limit_req_zone $binary_remote_addr zoneseckill:10m rate100r/s; location /seckill { limit_req zoneseckill burst50; proxy_pass http://backend; }这套方案在618大促中经受住了50万QPS的考验最终下单成功率达到99.99%。