-- 查看Buffer Pool配置
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

-- Buffer Pool大小（建议设为物理内存的70-80%）
SET GLOBAL innodb_buffer_pool_size = 8G;

-- Buffer Pool实例数
SET GLOBAL innodb_buffer_pool_instances = 4;

-- 查看Buffer Pool状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Buffer Pool命中率
SELECT 
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 
    AS 'Buffer Pool Hit Rate%'
FROM 
    (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS reads,
    (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests 
     FROM performance_schema.global_status 
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS requests;

-- 查看事务隔离级别
SELECT @@transaction_isolation;
SELECT @@tx_isolation;

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

-- 查看正在运行的事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁等待
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 查看锁信息
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 共享锁(S锁)
SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁(X锁)
SELECT * FROM table_name WHERE id = 1 FOR UPDATE;

-- 意向锁
-- 意向共享锁(IS)
-- 意向排他锁(IX)
-- 共享和排他意向锁(SIX)

-- 查看当前锁
SHOW ENGINE INNODB STATUS\G
-- 查看Lock section

-- 查看锁等待
SELECT 
    waiting_trx_id,
    waiting_pid,
    blocking_trx_id,
    blocking_pid
FROM sys.innodb_lock_waits;

-- 查看最近死锁
SHOW ENGINE INNODB STATUS\G

-- 或者
SELECT * FROM performance_schema.events_statements_history 
WHERE object_schema = 'mysql' 
AND object_name = 'innodb'
LIMIT 50;

-- 启用InnoDB监控
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 查看死锁日志
-- MySQL 8.0+
SELECT * FROM performance_schema.data_lock_waits;

-- 手动触发死锁检测
SET GLOBAL innodb_lock_wait_timeout = 50;

-- 四种隔离级别
-- 1. READ UNCOMMITTED (读未提交)
-- 2. READ COMMITTED (读已提交)
-- 3. REPEATABLE READ (可重复读，InnoDB默认)
-- 4. SERIALIZABLE (串行化)

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

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

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

-- 测试隔离级别影响
-- 会话1
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;

-- 会话2（取决于隔离级别）
SELECT * FROM users WHERE id = 1;

-- 查看表索引
SHOW INDEX FROM table_name;

-- 分析索引使用
EXPLAIN SELECT * FROM table_name WHERE column = value;

-- 查看索引统计信息
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'database_name'
AND TABLE_NAME = 'table_name';

-- 更新索引统计信息
ANALYZE TABLE table_name;

-- 创建优化索引
CREATE INDEX idx_name ON users(last_name, first_name);

-- 查看索引大小
SELECT 
    INDEX_NAME,
    ROUND(STAT_VALUE * @@innodb_page_size / 1024 / 1024, 2) AS 'Index Size (MB)'
FROM mysql.innodb_index_stats
WHERE TABLE_NAME = 'users'
AND STAT_NAME = 'size';

-- 索引使用情况
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'database_name'
ORDER BY SUM_TIMER_WAIT DESC;

-- 查看Redo Log配置
SHOW VARIABLES LIKE 'innodb_log%';

-- 查看Redo Log状态
SHOW STATUS LIKE 'Innodb_log%';

-- Redo Log写入情况
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME LIKE 'Innodb_log%';

-- 优化配置
-- 增大log file size
-- 增加log files in group

-- 查看Redo Log写入量
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE,
    ROUND(VARIABLE_VALUE / 1024 / 1024, 2) AS 'MB'
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
    'Innodb_os_log_written',
    'Innodb_os_log_fsyncs'
);

-- 查看Redo Log的checkpoint
SHOW ENGINE INNODB STATUS\G
-- 查找Log部分

-- 查看Undo配置
SHOW VARIABLES LIKE 'innodb_undo%';

-- 查看Undo表空间
SELECT * FROM information_schema.INNODB_TABLESPACES 
WHERE SPACE_TYPE = 'Undo';

-- 查看Undo使用情况
SHOW STATUS LIKE 'Innodb%undo%';

-- 监控Undo Log
SELECT 
    SPACE_ID,
    ROUND(SUM(FILE_SIZE) / 1024 / 1024 / 1024, 2) AS 'Size (GB)'
FROM information_schema.INNODB_DATAFILES
WHERE TABLESPACE_NAME LIKE '%undo%'
GROUP BY SPACE_ID;

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看慢查询
SELECT * FROM mysql.slow_log;

-- 查看慢查询统计
SELECT 
    DIGEST_TEXT,
    COUNT(*) as 'exec_count',
    SUM(TIMER_WAIT) / 1000000000000 as 'total_time',
    AVG(TIMER_WAIT) / 1000000000000 as 'avg_time'
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'database_name'
ORDER BY total_time DESC
LIMIT 10;

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE age > 30;

-- 详细执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 30;

-- 实际执行统计
SELECT 
    SQL_TEXT,
    TRUNCATE(TIMER_WAIT / 1000000000000, 6) AS 'TIME (s)',
    TRUNCATE(LOCK_TIME / 1000000000000, 6) AS 'LOCK_TIME (s)',
    ROWS_EXAMINED,
    ROWS_SENT
FROM performance_schema.events_statements_history_long
ORDER BY TIMER_WAIT DESC
LIMIT 20;

-- 索引使用情况
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    SUM_TIMER_FETCH / 1000000000000 AS 'FETCH_TIME (s)'
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'database_name'
ORDER BY SUM_TIMER_FETCH DESC;

-- 配置双主复制
-- 主1
CHANGE MASTER TO
  MASTER_HOST='192.168.1.101',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='binlog.000001',
  MASTER_LOG_POS=154;

START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G

-- 配置主从延迟监控
SELECT 
    SUBSTRING_INDEX(host, ':', 1) AS host,
    REPLICATION_LAG_IN_SECONDS AS lag
FROM performance_schema.replication_applier_status_by_coordinator;

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

-- 主要检查项:
-- 1. SEMAPHORES (信号量)
-- 2. LATEST DETECTED DEADLOCK (最近的死锁)
-- 3. TRANSACTIONS (事务)
-- 4. FILE I/O (文件IO)
-- 5. BUFFER POOL AND MEMORY (缓冲池)
-- 6. ROW OPERATIONS (行操作)

-- 查看连接和线程
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看锁等待
SELECT 
    waiting_trx_id,
    waiting_query,
    blocking_trx_id,
    blocking_query
FROM sys.innodb_lock_waits;

-- 查看表锁
SELECT * FROM performance_schema.metadata_locks;

-- 查看InnoDB表统计
SHOW TABLE STATUS FROM database_name;

-- 问题1: Buffer Pool太小
-- 解决: 增大innodb_buffer_pool_size

-- 问题2: 死锁频繁
-- 解决: 检查索引，优化事务大小
SELECT * FROM information_schema.INNODB_LOCKS;

-- 问题3: Redo Log过小
-- 解决: 增大innodb_log_file_size
-- 注意: 需要停止MySQL，备份ib_logfile

-- 问题4: Undo Log占空间
-- 解决: 定期purge，合理设置innodb_undo_tablespaces