学习进度条

每日总结（数据库专题：索引优化与事务）

今日学习时间：1小时
今日代码量：50行
今日博客：1篇（MySQL索引优化实战指南）

---

1. 索引创建与使用（MySQL示例）

-- 创建复合索引
CREATE INDEX idx_name_age ON employees(last_name, age);

-- 查看索引使用情况
EXPLAIN SELECT * FROM employees 
WHERE last_name = 'Smith' AND age > 30;

-- 强制使用索引（不推荐常规使用）
SELECT * FROM employees 
FORCE INDEX (idx_name_age) 
WHERE age > 25;

关键结论：

• 最左前缀原则：WHERE age > 25 无法使用上述复合索引
• 索引覆盖：SELECT last_name FROM employees WHERE last_name LIKE 'A%' 可直接使用索引

---

2. 事务隔离级别（SQL标准）

-- 查看当前隔离级别（MySQL）
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 事务示例（银行转账）
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

隔离级别对比：

级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✔️	✔️	✔️
READ COMMITTED	✖️	✔️	✔️
REPEATABLE READ	✖️	✖️	✔️
SERIALIZABLE	✖️	✖️	✖️

---

3. 死锁模拟与解决

-- 会话1
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1;
-- 等待会话2
UPDATE products SET stock = stock + 1 WHERE id = 2;

-- 会话2（并发执行）
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 2;
UPDATE products SET stock = stock + 1 WHERE id = 1;

-- 解决方案：统一获取锁的顺序
UPDATE products SET stock = stock - 1 WHERE id IN (1,2) ORDER BY id;

---

4. 数据库连接池配置（Spring Boot）

# application.yml
spring:
  datasource:
    hikari:
      maximum-pool-size: 10
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

监控SQL执行：

// 开启慢查询日志
@Bean
public HikariConfig hikariConfig() {
    HikariConfig config = new HikariConfig();
    config.setConnectionInitSql("SET SESSION long_query_time=1");
    return config;
}

---

5. 常见优化技巧

-- 避免全表扫描（添加索引后）
ALTER TABLE orders ADD INDEX idx_customer (customer_id);

-- 分页优化（避免OFFSET）
SELECT * FROM orders 
WHERE id > 1000  -- 上次查询的最大ID
ORDER BY id LIMIT 20;

-- 使用EXISTS代替IN
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM inventory 
    WHERE product_id = p.id AND quantity > 0
);

---

实践案例：

// JPA批量插入优化
@Transactional
public void batchInsert(List<Employee> employees) {
    for (int i = 0; i < employees.size(); i++) {
        entityManager.persist(employees.get(i));
        if (i % 50 == 0) { // 每50条flush一次
            entityManager.flush();
            entityManager.clear();
        }
    }
}

---

关键收获：

1. 理解B+树索引结构与最左前缀原则
2. 掌握四种隔离级别的应用场景
3. 学会识别和避免死锁场景
4. 实现高效的批量数据操作

遇到的问题：

• 复合索引失效（解决：调整查询条件顺序）
• 事务超时（解决：调整@Transactional(timeout=60)）