索引与事务排障

特点:
1. 非叶子节点只存储键值和指针,不存储数据
2. 叶子节点存储所有数据,并形成有序链表
3. 叶子节点之间通过双向链表连接,便于范围查询
4. 树高度通常 2-4 层,即可支持千万级数据

优势:
- 单次磁盘 I/O 可读取一个页(16KB),减少 I/O 次数
- 范围查询效率高(叶子节点有序链表)
- 排序性能好(索引本身有序)

-- 主键索引: 强制唯一,聚簇索引
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL,
    UNIQUE KEY uk_email (email),      -- 唯一索引: 业务唯一约束
    KEY idx_username (username)        -- 普通索引: 加速查询
);

-- 选择建议:
-- 1. 主键: 自增整数(避免页分裂) 或 业务唯一键(如用户ID)
-- 2. 唯一索引: 业务唯一约束(email、手机号、订单号)
-- 3. 普通索引: 频繁查询条件(username、状态、创建时间)

-- × 错误: 所有字段都加索引
CREATE TABLE bad_example (
    id BIGINT PRIMARY KEY,
    col1 VARCHAR(50),
    col2 VARCHAR(50),
    col3 VARCHAR(50),
    col4 VARCHAR(50),
    KEY idx_col1 (col1),  -- 写入性能下降,维护成本高
    KEY idx_col2 (col2),
    KEY idx_col3 (col3),
    KEY idx_col4 (col4)
);

-- √ 正确: 只为查询条件加索引
CREATE TABLE good_example (
    id BIGINT PRIMARY KEY,
    col1 VARCHAR(50),
    col2 VARCHAR(50),
    col3 VARCHAR(50),
    col4 VARCHAR(50),
    KEY idx_query_conditions (col1, col2)  -- 复合查询条件
);

-- 创建联合索引
CREATE INDEX idx_status_created_at ON order_info (status, created_at);

-- √ 能用到索引的查询
SELECT * FROM order_info WHERE status = 1;                    -- 最左列
SELECT * FROM order_info WHERE status = 1 AND created_at > '2025-01-01';  -- 全部列
SELECT * FROM order_info WHERE status = 1 ORDER BY created_at; -- 排序也能用到

-- × 用不到索引的查询
SELECT * FROM order_info WHERE created_at > '2025-01-01';      -- 跳过最左列
SELECT * FROM order_info WHERE status + 1 = 1;                 -- 对索引列做运算

索引结构: (status, created_at)

索引键值:
(1, '2025-01-01')
(1, '2025-01-02')
(1, '2025-01-03')
(2, '2025-01-01')
(2, '2025-01-02')

查询 WHERE status = 1:
  → 快速定位到第一个 status=1 的节点
  → 顺序扫描后续 status=1 的节点

查询 WHERE created_at > '2025-01-01':
  → 无法快速定位(索引先按 status 排序)
  → 只能全索引扫描

-- × 错误: 低基数字段做前导列
CREATE INDEX idx_gender ON users (gender);  -- gender 只有 M/F/NULL 三种值

-- 查询: SELECT * FROM users WHERE gender = 'M';
-- 问题: 即使命中索引,也需要扫描约 50% 的行,不如全表扫描

-- √ 正确: 高基数字段做前导列
CREATE INDEX idx_gender_user_id ON users (gender, user_id);
-- 或者直接用高基数字段
CREATE INDEX idx_email ON users (email);  -- email 基数接近行数

-- 判断标准:
-- 高基数: DISTINCT 值数量 > 行数 * 10%  → 适合做索引
-- 低基数: DISTINCT 值数量 < 行数 * 10%  → 不适合单独做索引

-- 查看表的基数统计
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CARDINALITY,
    TABLE_ROWS,
    ROUND(CARDINALITY / TABLE_ROWS * 100, 2) AS selectivity_percent
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t ON s.TABLE_NAME = t.TABLE_NAME
WHERE s.TABLE_SCHEMA = 'your_database'
  AND s.TABLE_NAME = 'users'
  AND s.INDEX_NAME = 'PRIMARY'
ORDER BY selectivity_percent DESC;

-- 基数越高,选择性越好,索引效果越好

-- 表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status TINYINT,
    amount DECIMAL(10,2),
    created_at DATETIME,
    KEY idx_user_status (user_id, status)
);

-- × 需要回表
SELECT * FROM orders WHERE user_id = 1001 AND status = 1;
-- 执行流程:
-- 1. 通过 idx_user_status 定位到主键 id
-- 2. 回表查询完整行数据(额外 I/O)

-- √ 覆盖索引(无需回表)
SELECT id, user_id, status FROM orders 
WHERE user_id = 1001 AND status = 1;
-- 执行流程:
-- 1. 通过 idx_user_status 直接返回数据
-- 2. 无需回表,性能更好

-- √ 创建覆盖索引
CREATE INDEX idx_user_status_amount ON orders (user_id, status, amount);
SELECT user_id, status, amount FROM orders 
WHERE user_id = 1001 AND status = 1;  -- 无需回表

-- 索引的代价:
-- 1. 写入成本: INSERT/UPDATE/DELETE 需要维护索引
-- 2. 存储成本: 索引占用磁盘空间
-- 3. 优化器成本: 选择索引时需要评估更多选项

-- × 过度索引
CREATE TABLE over_indexed (
    id BIGINT PRIMARY KEY,
    a VARCHAR(50),
    b VARCHAR(50),
    c VARCHAR(50),
    d VARCHAR(50),
    KEY idx_a (a),
    KEY idx_b (b),
    KEY idx_c (c),
    KEY idx_d (d),
    KEY idx_ab (a, b),
    KEY idx_bc (b, c)
    -- 写入性能严重下降
);

-- √ 合理索引
CREATE TABLE well_indexed (
    id BIGINT PRIMARY KEY,
    a VARCHAR(50),
    b VARCHAR(50),
    c VARCHAR(50),
    d VARCHAR(50),
    KEY idx_query_pattern (a, b)  -- 根据查询模式设计
);

-- 建议: 单表索引数量控制在 5 个以内

-- 场景 1: 按状态查询 + 按创建时间排序
CREATE INDEX idx_status_created_at ON order_info (status, created_at);

-- 查询
SELECT id, status, created_at
FROM order_info
WHERE status = 1
ORDER BY created_at DESC
LIMIT 10;

-- 执行流程:
-- 1. 通过索引定位 status=1 的第一条记录
-- 2. 按索引顺序(已按 created_at 排序)直接返回前 10 条
-- 3. 无需 filesort,性能极佳

-- 场景 2: 如果查询模式不匹配
-- 查询
SELECT * FROM order_info WHERE created_at > '2025-01-01';
-- 问题: 跳过 status 列,索引失效,全表扫描

-- 解决: 创建另一个索引
CREATE INDEX idx_created_at ON order_info (created_at);

-- 步骤 1: 分析查询模式
-- 慢查询日志中提取 WHERE 条件
-- 常见查询: WHERE user_id = ? AND status = ? ORDER BY created_at

-- 步骤 2: 确定列顺序
-- 等值条件列 → 范围条件列 → 排序列
-- user_id(等值) → status(等值) → created_at(排序/范围)

-- 步骤 3: 创建索引
CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);

-- 验证索引效果
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 AND status = 1 
ORDER BY created_at DESC 
LIMIT 10;

-- 关注:
-- type: ref(使用索引查找)
-- key: idx_user_status_created(使用的索引)
-- Extra: Using index(覆盖索引,无 filesort)

-- 表结构
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    created_at DATETIME,
    KEY idx_created_at (created_at)
);

-- × 索引失效: 对列使用函数
SELECT * FROM users WHERE DATE(created_at) = '2025-01-01';
-- 原因: 索引存储的是原始值,函数运算后无法使用索引

-- √ 索引生效: 等价改写
SELECT * FROM users 
WHERE created_at >= '2025-01-01 00:00:00' 
  AND created_at < '2025-01-02 00:00:00';

-- × 索引失效: 隐式类型转换
SELECT * FROM users WHERE name = 123;  -- name 是 VARCHAR,传入数字
-- MySQL 会转换为: WHERE CAST(name AS SIGNED) = 123
-- 索引失效

-- √ 索引生效: 类型匹配
SELECT * FROM users WHERE name = '123';

-- × 索引失效: 计算运算
SELECT * FROM users WHERE id + 1 = 1001;
-- √ 索引生效: 等价改写
SELECT * FROM users WHERE id = 1000;

-- 表结构
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    KEY idx_name (name)
);

-- × 索引失效: 前导模糊
SELECT * FROM products WHERE name LIKE '%手机%';
SELECT * FROM products WHERE name LIKE '%手机';
-- 原因: 前导通配符导致无法利用索引有序性

-- √ 索引生效: 后导模糊
SELECT * FROM products WHERE name LIKE '手机%';
-- 原因: 可以定位到"手"开头的位置,顺序扫描

-- 如果必须前导模糊,考虑:
-- 1. 全文索引(FULLTEXT)
CREATE FULLTEXT INDEX idx_name_fulltext ON products(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机' IN BOOLEAN MODE);

-- 2. Elasticsearch 等搜索引擎
-- 3. 前缀索引(仅限特定场景)
CREATE INDEX idx_name_prefix ON products(name(20));

-- 表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status TINYINT,
    KEY idx_user_id (user_id),
    KEY idx_status (status)
);

-- × 索引失效: OR 连接不同索引列
SELECT * FROM orders WHERE user_id = 1001 OR status = 1;
-- MySQL 5.6 及之前: 无法同时使用两个索引
-- MySQL 5.7+: 可能使用 index merge,但性能不稳定

-- √ 优化方案 1: UNION ALL
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE status = 1 AND user_id != 1001;

-- √ 优化方案 2: 创建联合索引(如果查询频繁)
CREATE INDEX idx_user_status ON orders (user_id, status);
SELECT * FROM orders WHERE user_id = 1001 OR status = 1;
-- 注意: 仍然可能不使用联合索引,需要测试

-- √ OR 同一索引列(索引生效)
SELECT * FROM orders WHERE user_id = 1001 OR user_id = 1002;
-- 可使用 idx_user_id

-- 表结构
CREATE INDEX idx_abc ON t (a, b, c);

-- √ 索引生效
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3  -- 部分生效(a 列)

-- × 索引失效
WHERE b = 2           -- 跳过最左列
WHERE c = 3           -- 跳过最左列
WHERE b = 2 AND c = 3 -- 跳过最左列

--  范围查询后的列失效
WHERE a = 1 AND b > 2 AND c = 3  -- a,b 生效,c 失效
-- 原因: b 是范围查询,后续列 c 无法利用索引有序性

-- 场景 1: 扫描行数仍然很大
-- 表: 100 万行数据,gender 索引(低基数)
CREATE INDEX idx_gender ON users (gender);  -- M/F/NULL

SELECT * FROM users WHERE gender = 'M';
-- 执行计划: type=ref, key=idx_gender, rows=500000
-- 问题: 即使使用索引,仍需扫描 50 万行
-- 优化: 删除低基数索引,使用全表扫描反而更快

-- 场景 2: 回表次数太多
-- 表: 100 万行数据
CREATE INDEX idx_status ON orders (status);

SELECT * FROM orders WHERE status = 1;  -- 假设 status=1 有 10 万行
-- 执行流程:
-- 1. 通过索引定位到 10 万个主键
-- 2. 回表查询 10 万次(随机 I/O)
-- 3. 性能比全表扫描还差

-- 优化: 使用覆盖索引
CREATE INDEX idx_status_user_amount ON orders (status, user_id, amount);
SELECT status, user_id, amount FROM orders WHERE status = 1;  -- 无需回表

-- 场景 3: 排序没有借助索引
CREATE INDEX idx_user_id ON orders (user_id);

SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 10;
-- 执行计划 Extra: Using filesort
-- 问题: 索引中无 created_at,需要额外排序

-- 优化: 创建联合索引
CREATE INDEX idx_user_created ON orders (user_id, created_at);
-- 执行计划 Extra: 无 filesort

-- 场景 4: 需要临时表
SELECT status, COUNT(*) FROM orders GROUP BY status;
-- 执行计划 Extra: Using temporary
-- 优化: 创建索引
CREATE INDEX idx_status ON orders (status);
-- 执行计划 Extra: Using index(覆盖索引,无需临时表)

-- MySQL 8.0+ 支持 EXPLAIN ANALYZE,显示实际执行时间
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 1;

-- 输出示例:
-> Filter: (orders.status = 1)  (cost=10000 rows=100000) (actual time=0.1..100 rows=100000 loops=1)
    -> Table scan on orders  (cost=10000 rows=1000000) (actual time=0.1..50 rows=1000000 loops=1)

-- 关注:
-- 1. estimated rows vs actual rows: 估算是否准确
-- 2. actual time: 实际执行时间
-- 3. 是否全表扫描(应该用索引的地方)

-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- MySQL 5.7: SELECT @@tx_isolation;

-- 设置隔离级别(会话级别)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置隔离级别(全局级别)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 示例: 读已提交 vs 可重复读
-- 时间线对比:

-- 会话 A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 返回 1000

-- 会话 B
START TRANSACTION;
UPDATE accounts SET balance = 800 WHERE id = 1;
COMMIT;

-- 会话 A(读已提交)
SELECT balance FROM accounts WHERE id = 1;  -- 返回 800(读到已提交的数据)

-- 会话 A(可重复读)
SELECT balance FROM accounts WHERE id = 1;  -- 返回 1000(读到事务开始时的快照)

-- 1. 读未提交(RU)
-- 实现: 不加锁,直接读取最新数据(包括未提交的)
-- 问题: 脏读,几乎不使用

-- 2. 读已提交(RC)
-- 实现: MVCC,每次查询生成新的 Read View
-- Read View: 记录当前活跃事务 ID,判断数据可见性
-- 优点: 避免脏读
-- 问题: 不可重复读(两次查询结果不同)

-- 3. 可重复读(RR) - MySQL 默认
-- 实现: MVCC,事务开始时生成 Read View,整个事务期间复用
-- 优点: 避免脏读、不可重复读
-- 额外机制: 间隙锁(Gap Lock)防止幻读

-- 4. 串行化(Serializable)
-- 实现: 所有读操作加共享锁,写操作加排他锁
-- 优点: 完全避免并发问题
-- 缺点: 并发性能极差,很少使用

-- 场景: 长事务持有锁,阻塞其他事务
-- 事务 A(长事务)
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 执行复杂业务逻辑,耗时 30 秒...
-- 事务未提交,持续持有 id=1 的行锁

-- 事务 B(被阻塞)
START TRANSACTION;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- 等待锁,超时失败
-- ERROR 1205 (HY000): Lock wait timeout exceeded

-- 排查: 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_TRX WHERE trx_id = '阻塞事务ID';

-- 排查: 查看长事务
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- 长事务产生大量 undo log
-- 事务 A
START TRANSACTION;
INSERT INTO large_table SELECT * FROM source_table;  -- 插入 100 万行
-- 执行复杂逻辑...
ROLLBACK;  -- 需要回滚 100 万行,可能耗时数分钟

-- undo log 空间不足
-- ERROR 1114 (HY000): The table 'large_table' is full
-- (实际是 undo log 空间不足)

-- 监控 undo log 使用情况
SHOW ENGINE INNODB STATUS\G
-- 关注:
-- History list length: 清理线程待处理的 undo log 数量
-- 如果持续增长,说明有长事务未提交

-- 长事务导致 MVCC 版本链过长
-- 事务 A(长事务)
START TRANSACTION;  -- trx_id = 100

-- 事务 B(更新同一行)
UPDATE users SET name = 'B' WHERE id = 1;  -- 产生新版本,记录 trx_id = 101
COMMIT;

-- 事务 C(更新同一行)
UPDATE users SET name = 'C' WHERE id = 1;  -- 产生新版本,记录 trx_id = 102
COMMIT;

-- 事务 D(更新同一行)
UPDATE users SET name = 'D' WHERE id = 1;  -- 产生新版本,记录 trx_id = 103
COMMIT;

-- 事务 A(查询)
SELECT * FROM users WHERE id = 1;
-- 需要遍历版本链: D → C → B → A(原始版本)
-- 性能下降,版本链越长越慢

-- 监控:
-- History list length 越长,查询性能越差

-- 场景: 长事务增大死锁概率
-- 事务 A(长事务)
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 持有 id=1 的锁
-- 执行复杂逻辑,耗时 10 秒...

-- 事务 B(另一个事务)
START TRANSACTION;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;  -- 持有 id=2 的锁
UPDATE accounts SET balance = balance - 50 WHERE id = 1;  -- 等待 id=1 的锁

-- 事务 A(继续执行)
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待 id=2 的锁
-- 死锁! MySQL 检测到死锁,回滚其中一个事务
-- ERROR 1213 (40001): Deadlock found when trying to get lock

-- 避免死锁:
-- 1. 事务尽量短小
-- 2. 按固定顺序访问资源(id 升序)
-- 3. 减少锁持有时间

SELECT * FROM order_info WHERE id = 1001 FOR UPDATE;

-- 表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(50),
    status TINYINT,
    amount DECIMAL(10,2),
    KEY idx_order_no (order_no)
);

-- × 危险: 无索引条件
SELECT * FROM orders WHERE status = 1 FOR UPDATE;
-- 问题: status 无索引,锁住所有行(全表扫描 + 全表锁)

-- × 危险: 索引条件但不精准
SELECT * FROM orders WHERE order_no LIKE 'ORD%' FOR UPDATE;
-- 问题: 锁住所有匹配的行,可能很多

-- √ 安全: 精准条件
SELECT * FROM orders WHERE id = 1001 FOR UPDATE;
SELECT * FROM orders WHERE order_no = 'ORD20250101001' FOR UPDATE;

-- × 危险: 长事务 + FOR UPDATE
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 加锁

-- 执行远程调用(耗时 5 秒)
CALL external_service();

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 问题: 锁持有时间 = 查询 + 远程调用 + 更新 = 5+ 秒
-- 期间其他事务全部阻塞

-- √ 优化: 缩短锁持有时间
-- 1. 先执行远程调用
CALL external_service();

-- 2. 最后再加锁和更新
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- 锁持有时间: 仅更新操作,毫秒级

-- × 危险: 未命中索引,锁全表
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100),
    stock INT,
    KEY idx_name (name)
);

-- 事务 A
START TRANSACTION;
SELECT * FROM products WHERE name = '不存在的商品' FOR UPDATE;
-- 虽然 name 有索引,但未命中,锁住所有间隙(间隙锁)

-- 事务 B
INSERT INTO products VALUES (1, '新商品', 100);
-- 被阻塞! 因为间隙锁阻止插入

-- √ 优化: 使用主键或唯一索引
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- 锁范围精确,仅锁住 id=1 的行

-- √ 推荐用法:
-- 1. 必须使用主键或唯一索引
SELECT * FROM orders WHERE id = 1001 FOR UPDATE;

-- 2. 事务尽量短小
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;  -- 快速提交

-- 3. 考虑乐观锁替代
-- 不使用 FOR UPDATE
SELECT version, stock FROM products WHERE id = 1;
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = ?;  -- 乐观锁

-- 4. 使用 NOWAIT 或 SKIP LOCKED(MySQL 8.0+)
SELECT * FROM orders WHERE id = 1001 FOR UPDATE NOWAIT;
-- 如果行被锁住,立即报错,不等待

SELECT * FROM orders WHERE status = 'pending' FOR UPDATE SKIP LOCKED;
-- 跳过被锁住的行,只锁未被锁住的行

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询

-- 查看配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 使用 mysqldumpslow 分析慢日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- -s t: 按查询时间排序
-- -t 10: 显示前 10 条

// Spring Boot 配置: 打印慢 SQL
spring:
  datasource:
    hikari:
      leak-detection-threshold: 60000  # 连接泄漏检测
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        generate_statistics: true

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql.BasicBinder: TRACE
    org.hibernate.stat: DEBUG

// 或使用 P6Spy 监控
// pom.xml
<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.9.1</version>
</dependency>

// spy.properties
driverlist=com.mysql.cj.jdbc.Driver
logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat
appender=com.p6spy.engine.spy.appender.StdoutLogger
outagedetection=true
outagedetectioninterval=1  # 超过 1 秒打印堆栈

-- 使用 performance_schema(MySQL 5.7+)
-- 开启性能监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement/%';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements%';

-- 查询慢 SQL
SELECT 
    DIGEST_TEXT AS sql_text,
    COUNT_STAR AS exec_count,
    AVG_TIMER_WAIT / 1000000000 AS avg_latency_ms,
    MAX_TIMER_WAIT / 1000000000 AS max_latency_ms,
    SUM_ROWS_EXAMINED AS total_rows_examined,
    SUM_ROWS_SENT AS total_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- 使用 sys schema(MySQL 5.7+)
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

-- 关键字段解读:

-- 1. id: 查询标识符
-- 相同 id: 从上往下执行
-- 不同 id: 子查询,id 越大越先执行

-- 2. select_type: 查询类型
-- SIMPLE: 简单查询(无子查询、UNION)
-- PRIMARY: 最外层查询
-- SUBQUERY: 子查询
-- DERIVED: 派生表(FROM 子句中的子查询)

-- 3. type: 访问类型(性能从好到坏)
-- system: 单行系统表
-- const: 主键或唯一索引常量查询
-- eq_ref: 主键或唯一索引关联查询
-- ref: 非唯一索引等值查询
-- range: 索引范围扫描
-- index: 索引全扫描
-- ALL: 全表扫描(最差)

-- 4. possible_keys: 可能使用的索引
-- 显示查询涉及的字段上有哪些索引

-- 5. key: 实际使用的索引
-- NULL: 未使用索引

-- 6. key_len: 使用的索引长度
-- 越短越好(但也要足够)

-- 7. rows: 预估扫描行数
-- 越少越好

-- 8. Extra: 额外信息
-- Using index: 覆盖索引
-- Using where: 服务器层过滤
-- Using filesort: 文件排序(需优化)
-- Using temporary: 使用临时表(需优化)

-- 示例 1: 全表扫描
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2025-01-01';
-- type: ALL
-- key: NULL
-- rows: 1000000
-- Extra: Using where

-- 问题: 对索引列做函数运算,索引失效
-- 优化:
EXPLAIN SELECT * FROM orders 
WHERE created_at >= '2025-01-01 00:00:00' 
  AND created_at < '2025-01-02 00:00:00';
-- type: range
-- key: idx_created_at
-- rows: 1000
-- Extra: Using index condition

-- 示例 2: 索引选择错误
CREATE INDEX idx_user_id ON orders (user_id);
CREATE INDEX idx_status ON orders (status);

EXPLAIN SELECT * FROM orders WHERE user_id = 1001 OR status = 1;
-- type: index_merge
-- key: idx_user_id, idx_status
-- rows: 500000
-- Extra: Using union(idx_user_id, idx_status); Using where

-- 问题: 扫描行数过多,性能不稳定
-- 优化:
EXPLAIN 
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE status = 1 AND user_id != 1001;
-- 两个查询都使用索引,性能更稳定

-- 示例 3: filesort
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC;
-- type: ref
-- key: idx_user_id
-- Extra: Using filesort

-- 问题: 排序未使用索引
-- 优化: 创建联合索引
CREATE INDEX idx_user_created ON orders (user_id, created_at);
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC;
-- type: ref
-- key: idx_user_created
-- Extra: 无 filesort

-- MySQL 8.0+: 使用 EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1001;

-- 输出示例:
-> Index lookup on orders using idx_user_id (user_id=1001)  (cost=10 rows=100) (actual time=0.1..1 rows=100 loops=1)
-- 显示实际执行时间、行数、循环次数

-- 慢 SQL
SELECT * FROM orders WHERE user_id = 1001 AND status = 1;

-- EXPLAIN
-- type: ALL
-- key: NULL
-- rows: 1000000

-- 解决: 创建索引
CREATE INDEX idx_user_status ON orders (user_id, status);

-- EXPLAIN
-- type: ref
-- key: idx_user_status
-- rows: 10

-- 慢 SQL
SELECT * FROM orders WHERE user_id + 1 = 1001;

-- EXPLAIN
-- type: ALL
-- key: NULL

-- 解决: 改写条件
SELECT * FROM orders WHERE user_id = 1000;

-- EXPLAIN
-- type: ref
-- key: idx_user_id

-- 慢 SQL
SELECT * FROM large_table LIMIT 1000000, 10;

-- 问题: 偏移量过大,需要扫描前 1000010 行

-- 解决方案 1: 使用覆盖索引 + 延迟关联
SELECT t.* FROM large_table t
INNER JOIN (SELECT id FROM large_table LIMIT 1000000, 10) tmp
ON t.id = tmp.id;

-- 解决方案 2: 记录上次查询的最大 ID
SELECT * FROM large_table WHERE id > 1000000 LIMIT 10;

-- 慢 SQL
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

-- EXPLAIN
-- Extra: Using temporary; Using filesort

-- 解决: 创建索引
CREATE INDEX idx_user_id ON orders (user_id);

-- EXPLAIN
-- Extra: Using index

-- 慢 SQL(本身不慢,但在等待锁)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 排查:
-- 1. 查看当前锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 2. 查看持锁事务
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_id IN (
    SELECT requesting_trx_id FROM information_schema.INNODB_LOCK_WAITS
);

-- 3. 查看持锁 SQL
SELECT * FROM performance_schema.events_statements_current 
WHERE THREAD_ID IN (
    SELECT THREAD_ID FROM performance_schema.threads 
    WHERE PROCESSLIST_ID = 持锁事务的trx_mysql_thread_id
);

-- 1. 当前 SQL 的执行计划
EXPLAIN SELECT * FROM orders WHERE id = 1001;

-- 2. 当前会话是否在等待锁
SELECT * FROM information_schema.INNODB_LOCK_WAITS 
WHERE requesting_trx_id = 当前事务ID;

-- 或使用 sys schema
SELECT * FROM sys.innodb_lock_waits;

-- 3. 其他事务是否持有锁且长时间未提交
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- 4. 查看完整锁等待链
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    b.trx_started AS blocking_started
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- 5. 终止长事务
KILL 持锁事务的trx_mysql_thread_id;

SHOW ENGINE INNODB STATUS\G

-- 关注 LATEST DETECTED DEADLOCK 部分:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-01-01 12:00:00 0x7f8b8c0b4700
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 140236892501760, query id 100 localhost root updating
UPDATE accounts SET balance = balance - 100 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `test`.`accounts` 
trx id 12345 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 140236892501760, query id 101 localhost root updating
UPDATE accounts SET balance = balance + 50 WHERE id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `test`.`accounts` 
trx id 12346 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

-- 分析:
-- 事务 1 等待 id=1 的锁
-- 事务 2 持有 id=1 的锁,等待 id=2 的锁
-- 形成死锁

SHOW FULL PROCESSLIST;

-- 输出示例:
Id: 10
User: root
Host: localhost:54321
db: test
Command: Query
Time: 30
State: Waiting for table metadata lock
Info: UPDATE orders SET status = 1 WHERE id = 1001

-- 关注:
-- Time: 执行时间
-- State: 状态
--   - Waiting for table metadata lock: 等待元数据锁
--   - Waiting for table level lock: 等待表锁
--   - Sending data: 查询执行中
--   - Statistics: 计算统计信息
--   - Locked: 被锁住(MyISAM)

SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_lock_structs
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- 关注:
-- trx_state: RUNNING, LOCK WAIT, ROLLING BACK
-- running_seconds: 运行时间
-- trx_rows_locked: 锁定的行数
-- trx_query: 正在执行的 SQL

-- MySQL 5.7+ 使用 sys schema
SELECT * FROM sys.innodb_lock_waits;

-- 输出:
-- wait_age: 等待时长
-- locked_table: 被锁表
-- locked_index: 被锁索引
-- locked_type: 锁类型
-- waiting_query: 等待的查询
-- blocking_query: 阻塞的查询

-- MySQL 8.0+ 使用 performance_schema.data_locks
SELECT 
    OBJECT_NAME,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    THREAD_ID,
    PROCESSLIST_ID
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = 'test' AND OBJECT_NAME = 'orders';

-- 查看长事务
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
WHERE trx_state = 'RUNNING'
ORDER BY trx_started ASC
LIMIT 10;

-- 如果有事务运行超过 30 秒,重点关注
-- 可能是长事务导致锁等待

-- 查看持锁事务的 SQL
SELECT 
    t.trx_id,
    t.trx_mysql_thread_id,
    t.trx_query AS current_query,
    e.SQL_TEXT AS last_executed_sql,
    e.TIMER_WAIT / 1000000000000 AS execution_time_sec
FROM information_schema.INNODB_TRX t
LEFT JOIN performance_schema.events_statements_current e 
    ON e.THREAD_ID = (
        SELECT THREAD_ID 
        FROM performance_schema.threads 
        WHERE PROCESSLIST_ID = t.trx_mysql_thread_id
    )
ORDER BY t.trx_started ASC;

-- 注意: trx_query 可能为空(事务空闲中)
-- 需要查看 last_executed_sql

-- 查看等待事务的执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 FOR UPDATE;

-- 如果 type=ALL,说明未命中索引,锁全表
-- 如果 type=ref,说明命中索引,锁范围较小

-- 查看事务运行时间
SELECT 
    trx_id,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
    trx_rows_modified,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- running_seconds > 30: 长事务
-- trx_rows_modified > 10000: 批量更新
-- 都可能导致锁等待

-- 查看当前执行的 SQL
SELECT 
    PROCESSLIST_ID,
    PROCESSLIST_USER,
    PROCESSLIST_DB,
    PROCESSLIST_STATE,
    PROCESSLIST_INFO
FROM performance_schema.threads
WHERE PROCESSLIST_COMMAND = 'Query'
  AND PROCESSLIST_INFO LIKE '%FOR UPDATE%';

-- 如果有大量 FOR UPDATE,检查是否必要
-- 可考虑乐观锁替代

-- 死锁场景 1: 更新顺序不一致
-- 事务 A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 持有 id=1 的锁
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待 id=2 的锁

-- 事务 B
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- 持有 id=2 的锁
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- 等待 id=1 的锁

-- 死锁! A 等 B,B 等 A

-- 解决: 按固定顺序更新(如 id 升序)
-- 事务 A 和 B 都先更新 id=1,再更新 id=2

-- 代码示例: 转账逻辑
// × 错误: 更新顺序不一致
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    jdbcTemplate.update("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromId);
    jdbcTemplate.update("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toId);
}

// 转账 A->B: 先锁 A,再锁 B
// 转账 B->A: 先锁 B,再锁 A
// 并发时死锁!

// √ 正确: 按固定顺序更新
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    Long first = Math.min(fromId, toId);
    Long second = Math.max(fromId, toId);
    jdbcTemplate.update("UPDATE accounts SET balance = balance - ? WHERE id = ?", 
        first.equals(fromId) ? amount : amount.negate(), first);
    jdbcTemplate.update("UPDATE accounts SET balance = balance + ? WHERE id = ?", 
        first.equals(fromId) ? amount : amount.negate(), second);
}

-- 死锁场景 2: 间隙锁冲突
-- 事务 A
SELECT * FROM orders WHERE id > 100 FOR UPDATE;  -- 锁住 id>100 的所有间隙

-- 事务 B
INSERT INTO orders VALUES (101, ...);  -- 等待间隙锁

-- 事务 A
INSERT INTO orders VALUES (102, ...);  -- 等待事务 B 的插入意向锁

-- 死锁!

-- 解决: 缩小锁范围
SELECT * FROM orders WHERE id = 101 FOR UPDATE;

-- 1. 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS\G
-- 搜索 "LATEST DETECTED DEADLOCK"

-- 2. 查看死锁涉及的事务
-- 从 LATEST DETECTED DEADLOCK 中提取事务 ID

-- 3. 查看死锁涉及的 SQL
-- 从 LATEST DETECTED DEADLOCK 中提取 SQL

-- 4. 分析死锁原因:
-- - 更新顺序不一致?
-- - 锁范围过大?
-- - 索引失效导致全表锁?
-- - 长事务?

-- 5. 解决方案:
-- - 统一资源访问顺序
-- - 精准查询条件,命中索引
-- - 缩短事务时间
-- - 使用乐观锁替代悲观锁

-- 1. 索引设计要跟随查询场景
-- × 错误: 所有字段都加索引
CREATE INDEX idx_all ON orders (user_id, status, created_at, amount);

-- √ 正确: 根据查询模式设计
-- 查询 1: WHERE user_id = ? AND status = ?
-- 查询 2: WHERE user_id = ? ORDER BY created_at
CREATE INDEX idx_user_status ON orders (user_id, status);
CREATE INDEX idx_user_created ON orders (user_id, created_at);

-- 2. 优先使用覆盖索引
-- × 错误: 需要回表
SELECT * FROM orders WHERE user_id = 1001;

-- √ 正确: 覆盖索引
SELECT id, user_id, status FROM orders WHERE user_id = 1001;

-- 3. 避免索引冗余
-- × 错误: 索引冗余
CREATE INDEX idx_user_id ON orders (user_id);
CREATE INDEX idx_user_status ON orders (user_id, status);  -- idx_user_id 冗余

-- √ 正确: 只保留联合索引
CREATE INDEX idx_user_status ON orders (user_id, status);

-- 4. 定期分析索引使用情况
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    ROWS_READ,
    ROWS_INSERTED,
    ROWS_UPDATED,
    ROWS_DELETED
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
  AND INDEX_NAME IS NOT NULL
ORDER BY ROWS_READ DESC;

-- 1. 事务尽量短小
-- × 错误: 事务包含远程调用
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 远程调用第三方支付接口(耗时 5 秒)
COMMIT;

-- √ 正确: 先调用远程接口,最后再开事务
-- 远程调用第三方支付接口
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 2. 避免事务嵌套
-- × 错误: 事务嵌套
START TRANSACTION;
START TRANSACTION;  -- 不生效,仍在第一个事务中
COMMIT;
COMMIT;

-- √ 正确: 使用传播行为(Spring)
@Transactional(propagation = Propagation.REQUIRES_NEW)

-- 3. 只读操作不加事务(或加只读事务)
-- × 错误: 写事务包含读操作
START TRANSACTION;
SELECT * FROM orders WHERE id = 1001;  -- 读操作
UPDATE orders SET status = 1 WHERE id = 1001;
COMMIT;

-- √ 正确: 读写分离
SELECT * FROM orders WHERE id = 1001;  -- 无事务
START TRANSACTION;
UPDATE orders SET status = 1 WHERE id = 1001;
COMMIT;

-- √ 或使用只读事务(如果需要一致性读)
START TRANSACTION READ ONLY;
SELECT * FROM orders WHERE id = 1001;
COMMIT;

-- 4. 设置合理的隔离级别
-- 大多数场景: READ COMMITTED
-- 需要一致性读: REPEATABLE READ
-- 极端场景: SERIALIZABLE(谨慎使用)

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 1. 更新必须命中索引
-- × 错误: 未命中索引,锁全表
UPDATE orders SET status = 1 WHERE DATE(created_at) = '2025-01-01';

-- √ 正确: 命中索引,锁范围小
UPDATE orders SET status = 1 
WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02';

-- 2. 批量更新分批执行
-- × 错误: 大批量更新
UPDATE orders SET status = 1 WHERE status = 0;  -- 更新 100 万行

-- √ 正确: 分批更新
UPDATE orders SET status = 1 WHERE status = 0 LIMIT 1000;
-- 循环执行,每次 1000 行

-- 3. 避免 SELECT FOR UPDATE
-- × 错误: 悲观锁
SELECT * FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- √ 正确: 乐观锁
SELECT id, stock, version FROM products WHERE id = 1;
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = ?;
-- 检查影响行数,为 0 则重试

-- × 错误认知: 所有查询字段都加索引
CREATE INDEX idx_a ON t (a);
CREATE INDEX idx_b ON t (b);
CREATE INDEX idx_c ON t (c);
CREATE INDEX idx_d ON t (d);

-- 实际问题:
-- 1. 写入性能下降: 每次 INSERT/UPDATE/DELETE 都要维护索引
-- 2. 存储成本增加: 索引占用磁盘空间
-- 3. 优化器困惑: 需要评估更多索引选项
-- 4. 维护成本增加: 索引越多,调优越复杂

-- √ 正确做法:
-- 1. 分析慢查询日志,只为慢查询加索引
-- 2. 使用覆盖索引,减少索引数量
-- 3. 定期检查索引使用情况,删除无用索引
SELECT 
    INDEX_NAME,
    COUNT(*) AS usage_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
GROUP BY INDEX_NAME
ORDER BY usage_count ASC;

-- usage_count = 0: 未使用的索引,考虑删除

-- × 错误认知: 锁等待是因为隔离级别太低
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 实际问题:
-- 1. 隔离级别提高,并发性能下降
-- 2. 可能并未解决根本问题(长事务、锁范围过大)

-- √ 正确做法:
-- 1. 先排查锁等待原因(长事务? 锁范围过大?)
-- 2. 优化事务设计(缩短事务时间)
-- 3. 优化查询(命中索引,缩小锁范围)
-- 4. 最后才考虑调整隔离级别

-- × 错误认知: SHOW PROCESSLIST 能看到所有问题
SHOW PROCESSLIST;
-- 只能看到当前正在执行的 SQL,看不到锁等待关系

-- √ 正确做法: 使用 sys.innodb_lock_waits
SELECT 
    wait_age_secs,
    locked_table,
    waiting_query,
    blocking_query
FROM sys.innodb_lock_waits;

-- 能看到:
-- 1. 谁在等待(waiting_query)
-- 2. 谁阻塞(blocking_query)
-- 3. 等待多久(wait_age_secs)
-- 4. 锁的表和索引(locked_table)

-- × 错误认知: EXPLAIN 显示使用索引,就认为没问题
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;
-- type: ref, key: idx_user_id
-- 认为没问题,但实际可能:
-- 1. 索引选择错误(优化器选择错误)
-- 2. 等待锁(事务问题,不是索引问题)
-- 3. 磁盘 I/O 问题(数据不在缓存)

-- √ 正确做法: 结合多维度排查
-- 1. EXPLAIN: 查看执行计划
-- 2. EXPLAIN ANALYZE: 查看实际执行时间(MySQL 8.0+)
-- 3. 慢查询日志: 确认是否真的慢
-- 4. SHOW ENGINE INNODB STATUS: 查看锁等待
-- 5. performance_schema: 查看资源消耗

-- 慢 SQL
SELECT * FROM orders 
WHERE user_id = 1001 
  AND status IN (1, 2, 3) 
ORDER BY created_at DESC 
LIMIT 20;

-- 表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status TINYINT,
    amount DECIMAL(10,2),
    created_at DATETIME,
    KEY idx_user_id (user_id),
    KEY idx_status (status)
);

-- 排查步骤:
-- 1. EXPLAIN
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 AND status IN (1, 2, 3) 
ORDER BY created_at DESC LIMIT 20;

-- 结果:
-- type: ref
-- key: idx_user_id
-- rows: 100000
-- Extra: Using where; Using filesort

-- 问题: 
-- - 扫描行数太多(10 万行)
-- - filesort(排序未使用索引)

-- 2. 分析
-- user_id 索引命中,但 status 条件和排序未用到
-- 需要创建联合索引

-- 3. 优化方案 1: 创建联合索引
CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);

-- EXPLAIN
-- type: range
-- key: idx_user_status_created
-- rows: 100
-- Extra: Using index condition

-- 优化后: 扫描行数从 10 万降到 100,无 filesort

-- 4. 优化方案 2: 覆盖索引(如果只需要部分字段)
SELECT id, user_id, status, created_at FROM orders 
WHERE user_id = 1001 AND status IN (1, 2, 3) 
ORDER BY created_at DESC LIMIT 20;

-- Extra: Using index
-- 无需回表,性能更佳

-- 报错 SQL
UPDATE orders SET status = 2 WHERE id = 1001;
-- ERROR 1205 (HY000): Lock wait timeout exceeded

-- 排查步骤:
-- 1. 查看当前事务
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- 结果:
-- trx_id=1001, running_seconds=120, trx_query=NULL

-- 问题: 有长事务运行 120 秒

-- 2. 查看锁等待
SELECT * FROM sys.innodb_lock_waits;

-- 结果:
-- waiting_query: UPDATE orders SET status = 2 WHERE id = 1001
-- blocking_query: NULL(事务空闲)

-- 3. 查看阻塞事务的最后 SQL
SELECT 
    t.trx_id,
    e.SQL_TEXT AS last_sql,
    e.TIMER_WAIT / 1000000000000 AS execution_time_sec
FROM information_schema.INNODB_TRX t
LEFT JOIN performance_schema.events_statements_current e 
    ON e.THREAD_ID = (
        SELECT THREAD_ID 
        FROM performance_schema.threads 
        WHERE PROCESSLIST_ID = t.trx_mysql_thread_id
    )
WHERE t.trx_id = 1001;

-- 结果:
-- last_sql: SELECT * FROM orders WHERE user_id = 1001 FOR UPDATE

-- 4. 分析
-- 长事务持有 id=1001 的锁,但未提交
-- 可能是代码逻辑问题(事务未正确关闭)

-- 5. 解决
-- 方案 1: 终止长事务
KILL 持锁事务的trx_mysql_thread_id;

-- 方案 2: 检查代码,确保事务正确关闭
-- 可能是:
-- - try-catch 中未回滚
-- - 事务方法抛出未捕获异常
-- - 事务超时设置过长

-- 代码示例:
@Transactional(timeout = 30)  // 设置超时时间
public void updateOrder(Long orderId) {
    Order order = orderMapper.selectById(orderId);
    order.setStatus(2);
    orderMapper.updateById(order);
}

-- 死锁场景
-- 事务 A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 事务 B
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

-- 死锁! A 等 B,B 等 A

-- 排查步骤:
-- 1. 查看死锁日志
SHOW ENGINE INNODB STATUS\G
-- 搜索 "LATEST DETECTED DEADLOCK"

-- 2. 分析
-- 两个事务更新顺序不一致:
-- A: 先 id=1,再 id=2
-- B: 先 id=2,再 id=1

-- 3. 解决: 统一更新顺序
-- 代码示例:
@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    // 按 id 升序更新
    Long first = Math.min(fromId, toId);
    Long second = Math.max(fromId, toId);
    
    // 先更新 id 小的
    Account firstAccount = accountMapper.selectById(first);
    if (first.equals(fromId)) {
        firstAccount.setBalance(firstAccount.getBalance().subtract(amount));
    } else {
        firstAccount.setBalance(firstAccount.getBalance().add(amount));
    }
    accountMapper.updateById(firstAccount);
    
    // 再更新 id 大的
    Account secondAccount = accountMapper.selectById(second);
    if (second.equals(fromId)) {
        secondAccount.setBalance(secondAccount.getBalance().subtract(amount));
    } else {
        secondAccount.setBalance(secondAccount.getBalance().add(amount));
    }
    accountMapper.updateById(secondAccount);
}

-- 优化后: 所有转账都按 id 升序更新,不会死锁