-- 启用 AI 查询优化器
SET GLOBAL optimizer_switch = 'ai_optimization=on';

-- 使用 AI 优化提示
SELECT /*+ AI_OPTIMIZE */ * FROM users WHERE age > 25;

-- 分析查询计划（AI 增强版）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- 查看 AI 优化建议
SELECT * FROM sys.ai_query_recommendations
WHERE query_hash = MD5('YOUR_QUERY');

-- 自动应用优化建议
CALL sys.apply_ai_optimizations('YOUR_QUERY_HASH');

-- 启用查询缓存（MySQL 9.x 智能缓存）
SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 67108864; -- 64MB

-- 监控缓存命中率
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- 查看缓存统计
SHOW STATUS LIKE 'Qcache%';

-- InnoDB 缓冲池大小（建议设置为可用内存的 60-80%）
SET GLOBAL innodb_buffer_pool_size = 4294967296;  -- 4GB

-- 缓冲池实例数量（多核CPU建议设置多个实例）
SET GLOBAL innodb_buffer_pool_instances = 4;

-- 日志文件大小
SET GLOBAL innodb_log_file_size = 1073741824;  -- 1GB

-- 刷新日志频率（平衡性能和数据安全）
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
-- 1: 每次事务刷新（最安全，默认）
-- 2: 每秒刷新（性能好，崩溃可能丢失1秒数据）
-- 0: 不立即刷新（最快，崩溃可能丢失大量数据）

-- 最大连接数
SET GLOBAL max_connections = 500;

-- 连接超时时间
SET GLOBAL wait_timeout = 600;  -- 10分钟
SET GLOBAL interactive_timeout = 600;

-- 线程缓存大小
SET GLOBAL thread_cache_size = 50;

-- 查询缓存大小
SET GLOBAL query_cache_size = 671>;  -- 64MB
SET GLOBAL query_cache_limit = 1048576;  -- 单个查询最大1MB

-- 临时表大小
SET GLOBAL tmp_table_size = 134217728;  -- 128MB
SET GLOBAL max_heap_table_size = 134217728;

-- 排序缓冲大小
SET GLOBAL sort_buffer_size = 4194304;  -- 4MB

-- 读取缓冲大小
SET GLOBAL read_buffer_size = 2097152;  -- 2MB
SET GLOBAL read_rnd_buffer_size = 4194304;  -- 4MB

-- 查看配置
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%size%';

-- 开启慢查询日志
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';

-- 查看慢查询
SELECT * FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 10;

-- 使用 mysqldumpslow 工具分析
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- -s: 排序方式 (t=时间, c=次数)
-- -t: 显示前N条

-- EXPLAIN 分析查询计划
EXPLAIN SELECT * FROM users WHERE age > 25;

-- 关键字段解读：
-- type: 连接类型
--   system > const > eq_ref > ref > range > index > ALL
--   const/eq_ref/ref/range 都是可接受的
--   ALL 表示全表扫描，需要优化

-- key: 实际使用的索引
-- rows: 预计扫描的行数（越小越好）
-- Extra: 额外信息
--   Using index: 使用覆盖索引
--   Using filesort: 需要额外排序
--   Using temporary: 使用临时表

-- EXPLAIN ANALYZE（实际执行并分析）
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- ❌ 使用过大的数据类型
CREATE TABLE t1 (
    id INT,           -- 4字节
    phone VARCHAR(20)
);

-- ✅ 使用合适的数据类型
CREATE TABLE t2 (
    id INT UNSIGNED,     -- 无符号，支持更大范围
    status TINYINT,        -- 1字节，范围 -128~127
    age TINYINT UNSIGNED, -- 0-255
    views BIGINT UNSIGNED,  -- 大访问量
    price DECIMAL(10,2)  -- 精确价格
);

-- 使用 ENUM 代替字符串
CREATE TABLE orders (
    status ENUM('pending', 'processing', 'completed', 'cancelled')
);

-- 使用 NOT NULL
-- NULL 需要额外空间存储，且影响索引效率

-- 垂直拆分：将大表拆分为多个小表
-- 原始表：users (id, name, email, avatar, bio, ...) 字段很多

-- 拆分后：
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    avatar VARCHAR(255),
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 水平拆分：按数据范围或哈希分表
-- 按月份分表（适用于日志、订单等）
CREATE TABLE orders_202401 (
    LIKE orders
);

CREATE TABLE orders_202402 (
    LIKE orders
);

-- 使用分区表（MySQL 原生支持）
CREATE TABLE orders_partitioned (
    id INT,
    created_at DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = myapp

-- 从库配置
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1

-- 从库设置
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

-- 推荐使用连接池（推荐 HikariCP、Druid）
-- 连接池配置建议：

-- 最小空闲连接数
minimum-idle = 5

-- 最大连接数
maximum-pool-size = 20

-- 空闲连接超时（毫秒）
idle-timeout = 300000

-- 连接最大生命周期（毫秒）
max-lifetime = 1800000

-- 连接超时（毫秒）
connection-timeout = 30000

-- 优化策略 1：缩短主键长度
-- ❌ 长主键导致树高度增加
CREATE TABLE users_bad (
    id VARCHAR(36) PRIMARY KEY,  -- UUID，36 字节
    name VARCHAR(100)
);
-- 每页存储键数：16KB / 36 字节 ≈ 455 个
-- 3 层树容量：455² ≈ 20 万行（容量大幅下降）

-- ✅ 短主键优化
CREATE TABLE users_good (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 8 字节
    name VARCHAR(100)
);
-- 每页存储键数：16KB / 8 字节 ≈ 2048 个
-- 3 层树容量：2048² ≈ 419 万行

-- 查看表的 B+ 树高度估算
SELECT
    table_name,
    table_rows,
    round(data_length / 16384, 0) AS data_pages,
    round(index_length / 16384, 0) AS index_pages,
    CASE
        WHEN table_rows < 1365 THEN '2 层'
        WHEN table_rows < 1870000 THEN '3 层'
        ELSE '4 层+'
    END AS estimated_height
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY table_rows DESC;

-- 页分裂性能影响：
-- - 直接插入：1 次 IO（写 1 个页）
-- - 页分裂：3-5 次 IO（分配新页 + 移动数据 + 更新指针）
-- - 连锁分裂：可能向上传播到根节点

-- 优化策略 2：使用自增主键，避免随机插入
-- ❌ 随机主键导致频繁页分裂
CREATE TABLE orders_bad (
    id VARCHAR(36) PRIMARY KEY,  -- UUID 随机值
    amount DECIMAL(10, 2)
);
-- 插入顺序：uuid_abc, uuid_xyz, uuid_def ...
-- 每次插入可能触发页分裂

-- ✅ 自增主键顺序插入
CREATE TABLE orders_good (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10, 2)
);
-- 插入顺序：1, 2, 3, 4, 5...
-- 顺序写入，几乎不触发页分裂

-- 必须使用 UUID 时的优化方案
CREATE TABLE orders_uuid (
    id BINARY(16) PRIMARY KEY,  -- 压缩 UUID
    amount DECIMAL(10, 2),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_time (created_at)  -- 时间索引用于范围查询
);
-- 使用 UUID_TO_BIN(uuid, 1) 优化时间局部性
INSERT INTO orders_uuid (id, amount)
VALUES (UUID_TO_BIN(UUID(), 1), 100.00);

-- 优化策略 3：提高数据页利用率

-- 1. 批量插入，减少页分裂
-- ❌ 单条插入，每批 1000 条
INSERT INTO orders (amount) VALUES (100);  -- 执行 1000 次

-- ✅ 批量插入，每批 1000 条
INSERT INTO orders (amount)
VALUES (100), (100), ..., (100);  -- 1000 个值

-- 2. 定期优化表，整理碎片
OPTIMIZE TABLE orders;
-- 重建表，回收空间，提高页利用率

-- 3. 监控页利用率
SELECT
    table_name,
    round(data_length / 1024 / 1024, 2) AS data_mb,
    round(data_free / 1024 / 1024, 2) AS free_mb,
    round((data_free / (data_length + data_free)) * 100, 2) AS fragmentation_pct
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY fragmentation_pct DESC;

-- 碎片率 > 20% 时建议优化

-- 优化策略 4：使用覆盖索引减少回表

-- 场景：查询用户订单统计
-- ❌ 需要回表
SELECT customer_id, amount, created_at
FROM orders WHERE customer_id = 100;
-- 执行过程：
-- 1. 在 idx_customer 中找到 customer_id=100 的记录
-- 2. 获取主键 id
-- 3. 回表到聚簇索引获取 amount, created_at
-- 假设命中 100 条记录，需要回表 100 次

-- ✅ 覆盖索引，无需回表
CREATE INDEX idx_cover ON orders(customer_id, amount, created_at);
SELECT customer_id, amount, created_at
FROM orders WHERE customer_id = 100;
-- 执行过程：
-- 1. 在 idx_cover 中直接获取所有数据
-- 2. 无需回表，IO 减少 50-90%

-- 覆盖索引设计原则：
-- 1. WHERE 条件列放在最左
-- 2. SELECT 列包含在索引中
-- 3. 考虑索引大小与收益比

-- 验证覆盖索引
EXPLAIN SELECT customer_id, amount FROM orders WHERE customer_id = 100;
-- Extra: Using index（表示使用覆盖索引）

-- 查看当前锁情况
SELECT * FROM performance_schema.data_locks;

-- 设置事务隔离级别（支持新的隔离级别）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 乐观锁（改进的版本控制）
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;

-- 查看行锁等待
SELECT * FROM performance_schema.events_waits_current
WHERE EVENT_NAME LIKE '%lock%';

-- 实时性能监控
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

-- 查看慢查询（AI 分析）
SELECT * FROM sys.statements_with_temp_tables
ORDER BY tmp_tables DESC;

-- 表空间使用情况
SELECT * FROM information_schema.innodb_tablespaces;

-- AI 性能诊断报告
CALL sys.generate_performance_report('your_database');