第7章：性能与存储调优

-- 配置慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 查看最耗时的SQL
SELECT 
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000,2) AS total_time_s,
    ROUND((SUM_TIMER_WAIT/COUNT_STAR)/1000000000,2) AS avg_time_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_time_s DESC
LIMIT 10;

-- 分析执行计划
EXPLAIN SELECT * FROM users WHERE age > 30 AND city='Beijing';

-- 获取实际执行时间（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30 AND city='Beijing';

-- 复合索引 (a, b, c)
WHERE a = 1              -- ✅ 使用索引
WHERE a = 1 AND b = 2    -- ✅ 使用索引  
WHERE b = 2              -- ❌ 不使用索引

-- 查询字段都在索引中，避免回表
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = 'Tom';  -- 覆盖索引

-- 计算索引选择性
SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;
-- 选择性 > 0.1 的列适合建索引

-- 慢查询：OFFSET 扫描过多
SELECT * FROM users ORDER BY id LIMIT 100000, 10;

-- 优化：基于主键分页
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;

-- MySQL 5.6+ 自动优化
EXPLAIN SELECT * FROM users WHERE name LIKE 'T%' AND age > 20;
-- 显示：Extra: Using index condition

-- 计算 Buffer Pool 命中率
SELECT 
  (1 - (SUM(CASE WHEN VARIABLE_NAME='Innodb_buffer_pool_reads' 
                 THEN VARIABLE_VALUE END) /
        SUM(CASE WHEN VARIABLE_NAME='Innodb_buffer_pool_read_requests' 
                 THEN VARIABLE_VALUE END))) * 100 AS buffer_hit_rate
FROM performance_schema.global_status;

-- 查看 Buffer Pool 状态
SHOW ENGINE INNODB STATUS\G

-- 关键参数
innodb_buffer_pool_size = 8G          -- 设置为内存的70-80%
innodb_buffer_pool_instances = 8      -- 提高并发性能
innodb_io_capacity = 2000             -- SSD推荐值
innodb_flush_log_at_trx_commit = 2    -- 平衡性能与安全

max_connections = 2000        -- 最大连接数
wait_timeout = 600           -- 连接超时
thread_cache_size = 64       -- 线程缓存

innodb_thread_concurrency = 0    -- 工作线程数（0=自动）
innodb_read_io_threads = 4       -- 读线程数
innodb_write_io_threads = 4      -- 写线程数

-- 查看当前锁等待（MySQL 8.0+）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 查看事务状态
SELECT * FROM information_schema.innodb_trx;

-- 水平分片示例
CREATE TABLE users_0 (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP,
    INDEX idx_username (username)
) ENGINE=InnoDB;

-- 分片规则：user_id % 16

UPDATE goods SET stock = stock - 1 WHERE id = 100;

-- 按商品ID分片（id % 8）
CREATE TABLE goods_stock_0 (
    id BIGINT PRIMARY KEY,
    stock INT,
    version INT,
    updated_at TIMESTAMP
) ENGINE=InnoDB;

UPDATE goods 
SET stock = stock - 1, version = version + 1 
WHERE id = 100 AND version = @v;

-- 创建预计算统计表
CREATE TABLE daily_stats (
    stat_date DATE,
    user_count INT,
    order_count INT,
    total_amount DECIMAL(15,2),
    PRIMARY KEY (stat_date)
) ENGINE=InnoDB;

-- 定时任务增量更新
INSERT INTO daily_stats (stat_date, user_count, order_count, total_amount)
SELECT 
    DATE(created_at) as stat_date,
    COUNT(DISTINCT user_id) as user_count,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders 
WHERE created_at >= CURDATE()
GROUP BY DATE(created_at)
ON DUPLICATE KEY UPDATE
    user_count = VALUES(user_count),
    order_count = VALUES(order_count),
    total_amount = VALUES(total_amount);

-- 分批处理大事务
SET @batch_size = 1000;
SET @offset = 0;

WHILE @offset < @total_count DO
    START TRANSACTION;
    
    UPDATE large_table 
    SET status = 'processed' 
    WHERE id BETWEEN @offset AND @offset + @batch_size - 1;
    
    COMMIT;
    SET @offset = @offset + @batch_size;
END WHILE;

-- 使用消息队列异步处理
INSERT INTO task_queue (task_type, data, status) 
VALUES ('batch_update', @data, 'pending');

-- 按用户ID分片
CREATE TABLE orders_0 (
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT,
    order_no VARCHAR(32),
    status TINYINT,
    amount DECIMAL(10,2),
    created_at TIMESTAMP,
    PRIMARY KEY (id, user_id),
    INDEX idx_user_time (user_id, created_at)
) ENGINE=InnoDB;

-- 复合索引优化查询
CREATE INDEX idx_status_time ON orders(status, created_at);
CREATE INDEX idx_user_status ON orders(user_id, status);

CREATE TABLE access_logs (
    id BIGINT AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(50),
    created_at DATETIME,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- 查看状态
SHOW STATUS;

-- 查看变量
SHOW VARIABLES;

-- 查看进程
SHOW PROCESSLIST;

-- 参数组配置
-- 实例类：db.r5.2xlarge (8 vCPU, 64 GB RAM)
-- 存储：gp3 1000 GB, 3000 IOPS
-- 参数组：mysql8.0-optimized

-- 关键参数
innodb_buffer_pool_size = 48G
innodb_log_file_size = 1G
innodb_io_capacity = 3000
innodb_io_capacity_max = 6000
max_connections = 2000

-- 实例规格：mysql.n2.4xlarge (16 vCPU, 64 GB RAM)
-- 存储：ESSD 1000 GB, 3000 IOPS
-- 参数模板：mysql8.0-high-performance

-- 关键参数
innodb_buffer_pool_size = 48G
innodb_log_file_size = 1G
innodb_io_capacity = 3000
innodb_io_capacity_max = 6000
max_connections = 2000

-- 性能监控查询
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME IN (
    'Questions',
    'Uptime',
    'Threads_connected',
    'Threads_running',
    'Innodb_buffer_pool_hit_rate',
    'Innodb_rows_read',
    'Innodb_rows_inserted',
    'Innodb_rows_updated',
    'Innodb_rows_deleted'
);