DevOps炼成记：后端工程师 MySQL 生存指南

-- 5.6引入的Online DDL（部分操作不锁表）
ALTER TABLE users ADD COLUMN age INT, ALGORITHM=INPLACE, LOCK=NONE;

-- 客户端连接（8.0推荐方式）
mysql -h localhost -u root -p --ssl-mode=REQUIRED  -- 8.0废弃--ssl-verify-server-cert

-- 查看版本与状态
SELECT VERSION();                           -- 查看版本
SHOW STATUS LIKE 'Threads_connected';       -- 当前连接数
SHOW VARIABLES LIKE 'max_connections';      -- 最大连接数

-- 8.0+ 使用Performance Schema查看更详细连接信息
SELECT * FROM performance_schema.threads WHERE NAME LIKE '%sql%';

-- 创建数据库（完整语法，包含所有选项）
CREATE DATABASE IF NOT EXISTS devops_db
    DEFAULT CHARACTER SET utf8mb4           -- 8.0默认，5.7需显式指定
    DEFAULT COLLATE utf8mb4_unicode_ci      -- 排序规则
    ENCRYPTION='Y'                          -- 8.0+ 透明数据加密(TDE)
    DEFAULT TABLESPACE ts_devops;            -- 8.0+ 通用表空间

-- 查看数据库
SHOW DATABASES;
SHOW CREATE DATABASE devops_db;

-- 修改数据库（8.0+支持更多选项）
ALTER DATABASE devops_db 
    CHARACTER SET utf8mb4 
    READ ONLY = 0;                          -- 8.0.22+ 只读模式控制

-- 删除数据库（危险操作！）
DROP DATABASE IF NOT EXISTS devops_db;      -- 5.5+ 语法
-- 8.0+ 安全建议：先设置为只读，确认无连接后再删除

-- 整数类型（所有版本支持）
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

-- 字符串类型
CHAR(255), VARCHAR(65535)                   -- 最大长度受行大小限制
TEXT, MEDIUMTEXT, LONGTEXT                  -- 最大4GB(8.0.14+)
ENUM, SET                                   

-- JSON类型（5.7.8+）
CREATE TABLE events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    data JSON CHECK (JSON_VALID(data)),     -- 8.0.16+ 原生CHECK约束
    INDEX idx_data ((CAST(data->>'$.name' AS CHAR(50))))  -- 虚拟列索引
);

-- 空间数据（5.7+）
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    location POINT NOT NULL SRID 4326,      -- 5.7.5+ 指定空间参考系
    SPATIAL INDEX idx_location (location)   -- 5.7.5+ InnoDB空间索引
);

CREATE TABLE IF NOT EXISTS users (
    -- 自增列（8.0推荐语法）
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- 业务字段
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    age TINYINT UNSIGNED CHECK (age >= 18),  -- 8.0.16+ 原生CHECK
    
    -- 枚举类型
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    
    -- JSON字段（5.7.8+）
    profile JSON,
    
    -- 虚拟生成列（5.7.6+）
    display_name VARCHAR(100) AS (CONCAT(username, ' (', email, ')')) VIRTUAL,
    
    -- 存储生成列（5.7.6+）
    profile_name VARCHAR(50) AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name'))) STORED,
    
    -- 时间戳（5.6.5+ 支持自动更新）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 5.6.5+
    
    -- 表级约束
    CONSTRAINT uk_email UNIQUE KEY (email),
    CONSTRAINT chk_phone CHECK (phone REGEXP '^[0-9]{11}$'),  -- 8.0.16+
    
    -- 索引定义（8.0+ 支持降序索引）
    INDEX idx_username (username ASC),
    INDEX idx_created_desc (created_at DESC)                  -- 8.0+ 降序索引
    
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci
  ROW_FORMAT=DYNAMIC                                         -- 5.7推荐，支持大字段
  PARTITION BY RANGE (YEAR(created_at)) (                    -- 分区示例
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
  );

-- 添加列（8.0+ 支持表达式默认值）
ALTER TABLE users 
ADD COLUMN uuid CHAR(36) DEFAULT (UUID()) FIRST,           -- 8.0.13+ 表达式默认
ADD COLUMN score DECIMAL(10,2) DEFAULT 0.00 AFTER username;

-- 修改列（Online DDL）
ALTER TABLE users 
MODIFY COLUMN username VARCHAR(100) NOT NULL 
COMMENT '登录用户名', 
ALGORITHM=INPLACE, LOCK=NONE;                              -- 5.6+ Online DDL

-- 重命名列（8.0+ 语法）
ALTER TABLE users RENAME COLUMN username TO login_name;

-- 添加索引（8.0+ 不可见索引）
CREATE INDEX idx_phone ON users(phone) INVISIBLE;          -- 8.0+ 不可见索引，测试用

-- 删除索引
DROP INDEX idx_phone ON users;

-- 重命名表
RENAME TABLE users TO app_users, logs TO app_logs;         -- 原子操作

-- 8.0+ 原子DDL示例（失败自动回滚）
ALTER TABLE users ADD COLUMN test INT, DROP COLUMN non_existent;  -- 整体失败

-- 插入数据（所有版本）
INSERT INTO users (username, email) VALUES ('admin', 'admin@devops.com');

-- 8.0+ 插入并返回（类似PostgreSQL）
INSERT INTO users (username, email) 
VALUES ('test', 'test@devops.com')
RETURNING id, created_at;                                  -- 8.0.19+

-- 批量插入（优化技巧：单语句多值）
INSERT INTO users (username, email) VALUES 
    ('user1', 'u1@devops.com'),
    ('user2', 'u2@devops.com'),
    ('user3', 'u3@devops.com')
ON DUPLICATE KEY UPDATE                                    --  upsert操作
    updated_at = CURRENT_TIMESTAMP;

-- 8.0+ 新增VALUES()别名（更清晰）
INSERT INTO users (id, username) VALUES (1, 'a'), (2, 'b') AS new
ON DUPLICATE KEY UPDATE username = new.username;

-- 从查询插入
INSERT INTO users_archive SELECT * FROM users WHERE status = 'inactive';

-- 替换插入（先删除后插入，慎用）
REPLACE INTO users (id, username) VALUES (1, 'new_name');

-- 更新操作（LIMIT限制，避免全表更新）
UPDATE users 
SET status = 'inactive', updated_at = NOW() 
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 1000;                                                -- 分批处理

-- 多表更新（8.0+ 支持CTE）
WITH inactive_users AS (
    SELECT id FROM users WHERE last_login < '2023-01-01'
)
UPDATE users u
JOIN inactive_users iu ON u.id = iu.id
SET u.status = 'archived';

-- 删除操作（安全删除）
DELETE FROM users WHERE id = 1 LIMIT 1;

-- 8.0+ 删除并返回
DELETE FROM users WHERE status = 'banned' RETURNING id, username;  -- 8.0.1+

-- 清空表（TRUNCATE更快，但无回滚）
TRUNCATE TABLE temp_logs;

SELECT DISTINCT username, email 
FROM users 
WHERE created_at > '2024-01-01' 
    AND status IN ('active', 'pending')
    AND email LIKE '%@devops.com%'
ORDER BY created_at DESC 
LIMIT 10 OFFSET 20;                                        -- 分页

-- JSON提取
SELECT 
    id,
    JSON_EXTRACT(profile, '$.age') as age,                 -- 返回JSON格式
    profile->>'$.age' as age_text,                         -- 返回文本（->>操作符5.7.13+）
    JSON_UNQUOTE(JSON_EXTRACT(profile, '$.hobbies[0]')) as first_hobby
FROM users
WHERE JSON_CONTAINS(profile, '"gaming"', '$.hobbies');     -- JSON包含查询

-- JSON聚合（5.7.22+）
SELECT 
    status,
    JSON_ARRAYAGG(username) as user_list,                  -- 聚合为JSON数组
    JSON_OBJECT('count', COUNT(*), 'avg_age', AVG(age)) as stats
FROM users
GROUP BY status;

-- 排名函数
SELECT 
    username,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,    -- 连续排名
    RANK() OVER (ORDER BY score DESC) as rank_num,         -- 跳跃排名
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank, -- 密集排名
    NTILE(4) OVER (ORDER BY score DESC) as quartile        -- 四分位数
FROM game_scores;

-- 分组窗口函数（Partition By）
SELECT 
    department,
    username,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

-- 累计计算（Frame子句）
SELECT 
    date,
    daily_amount,
    SUM(daily_amount) OVER (
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_sum,                                   -- 累计和
    
    AVG(daily_amount) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as 7_day_avg                                         -- 移动平均
FROM sales;

-- 首尾值取值
SELECT 
    username,
    salary,
    FIRST_VALUE(salary) OVER (ORDER BY salary) as lowest,
    LAST_VALUE(salary) OVER (
        ORDER BY salary 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as highest,
    LAG(salary, 1) OVER (ORDER BY salary) as prev_salary,  -- 前一行
    LEAD(salary, 1) OVER (ORDER BY salary) as next_salary  -- 后一行
FROM employees;

-- 非递归CTE（替代子查询，更清晰）
WITH active_users AS (
    SELECT id, username, department_id 
    FROM users 
    WHERE status = 'active'
),
dept_stats AS (
    SELECT department_id, COUNT(*) as cnt
    FROM active_users
    GROUP BY department_id
)
SELECT au.*, ds.cnt
FROM active_users au
JOIN dept_stats ds ON au.department_id = ds.department_id;

-- 递归CTE（组织架构、路径查询）
WITH RECURSIVE org_tree AS (
    -- 锚定成员：根节点
    SELECT id, name, manager_id, 0 as level, 
           CAST(name AS CHAR(200)) as path
    FROM departments
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归成员
    SELECT d.id, d.name, d.manager_id, ot.level + 1,
           CONCAT(ot.path, ' > ', d.name)
    FROM departments d
    JOIN org_tree ot ON d.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY path;

-- 递归查询限制（默认1000层）
SET SESSION cte_max_recursion_depth = 2000;

-- 所有JOIN类型
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
RIGHT JOIN shipments s ON o.id = s.order_id          -- 右连接较少使用
CROSS JOIN status_codes sc;                          -- 笛卡尔积

-- 8.0+ LATERAL派生表（类似相关子查询，但更高效）
SELECT u.id, u.username, recent_orders.*
FROM users u,
LATERAL (
    SELECT order_id, amount 
    FROM orders 
    WHERE user_id = u.id 
    ORDER BY created_at DESC 
    LIMIT 3
) AS recent_orders;

-- 集合操作
SELECT username FROM users_2023
UNION DISTINCT                                          -- 去重并集
SELECT username FROM users_2024;

SELECT username FROM users_2023
UNION ALL                                               -- 不去重（更快）
SELECT username FROM users_2024;

-- 8.0+ EXCEPT（差集）和 INTERSECT（交集）
SELECT username FROM users_2023
EXCEPT                                                  -- 8.0.31+
SELECT username FROM users_2024;                        -- 只在2023不在2024

-- B-Tree索引（默认）
CREATE INDEX idx_name ON users(username);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 前缀索引（长文本优化）
CREATE INDEX idx_content ON articles(content(100));     -- 前100字符

-- 复合索引（最左前缀原则）
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

-- 降序索引（8.0+）
CREATE INDEX idx_created ON events(created_at DESC, id DESC);

-- 不可见索引（8.0+，安全测试）
CREATE INDEX idx_test ON users(phone) INVISIBLE;
ALTER TABLE users ALTER INDEX idx_test VISIBLE;         -- 重新可见

-- 函数索引（8.0.13+）
CREATE INDEX idx_func ON users((UPPER(username)));      -- 大写搜索优化
CREATE INDEX idx_json ON users((CAST(data->>"$.zip" AS UNSIGNED)));  -- JSON索引

-- 空间索引（5.7.5+）
CREATE SPATIAL INDEX idx_loc ON places(location);

-- 全文索引（5.6+ InnoDB支持）
CREATE FULLTEXT INDEX idx_content ON articles(title, content)
    WITH PARSER ngram;                                  -- 8.0.24+ 中文分词

-- 查看执行计划（8.0+ ANALYZE提供实际执行统计）
EXPLAIN ANALYZE                                         -- 8.0.18+
SELECT * FROM users WHERE email = 'test@devops.com';

-- 传统EXPLAIN（所有版本）
EXPLAIN FORMAT=JSON                                     -- JSON格式（5.6+）
SELECT u.*, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- 优化器提示（Optimizer Hints，5.6+）
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM big_table;  -- 5.7.8+ 查询超时
SELECT /*+ INDEX(users idx_email) */ * FROM users WHERE email = 'a';
SELECT /*+ JOIN_ORDER(users, orders) */ * FROM users u JOIN orders o;

-- 直方图统计（8.0+）
ANALYZE TABLE users UPDATE HISTOGRAM ON status, age;    -- 8.0+ 列统计
SELECT * FROM information_schema.COLUMN_STATISTICS;

-- 查看隔离级别（5.7+ 变量名变更）
SELECT @@transaction_isolation;                         -- 8.0推荐
SELECT @@tx_isolation;                                  -- 5.7及之前

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

-- 事务控制（所有版本支持）
START TRANSACTION;                                      -- 或 BEGIN
-- 或 BEGIN WORK;

SAVEPOINT sp1;                                          -- 设置保存点

INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

SAVEPOINT sp2;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 回滚到保存点
ROLLBACK TO SAVEPOINT sp2;                              -- 部分回滚

-- 提交或回滚
COMMIT;                                                 -- 或 COMMIT WORK;
-- 或 ROLLBACK;

-- 显式锁定（谨慎使用）
SELECT * FROM users WHERE id = 1 FOR UPDATE;            -- 排他锁(X锁)
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;    -- 共享锁(S锁，8.0可简写为FOR SHARE)

-- 8.0+ NOWAIT和SKIP LOCKED
SELECT * FROM users WHERE status = 'pending' 
LIMIT 1 FOR UPDATE SKIP LOCKED;                         -- 跳过已锁行，避免等待

SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;     -- 立即返回错误，不等待

-- 8.0+ 元数据锁监控
SELECT * FROM performance_schema.metadata_locks;        -- 查看MDL锁
SELECT * FROM performance_schema.table_handles;         -- 表级锁情况

-- 创建用户（5.7与8.0差异）
-- 5.7方式（仍兼容但已废弃）
GRANT ALL ON *.* TO 'dev'@'%' IDENTIFIED BY 'password'; 

-- 8.0推荐方式（先创建用户，再授权）
CREATE USER 'dev'@'%' IDENTIFIED WITH caching_sha2_password  -- 8.0默认认证插件
    BY 'SecurePass123!'
    PASSWORD EXPIRE INTERVAL 90 DAY                        -- 密码过期策略
    FAILED_LOGIN_ATTEMPTS 3                                -- 8.0.19+ 失败锁定
    PASSWORD_LOCK_TIME 2;                                  -- 锁定2天

-- 修改用户（8.0+）
ALTER USER 'dev'@'%' 
    IDENTIFIED BY 'NewPass456!'
    ACCOUNT LOCK;                                          -- 锁定账户

-- 删除用户
DROP USER IF EXISTS 'dev'@'%';

-- 查看用户
SELECT user, host, plugin, password_expired 
FROM mysql.user;

-- 5.7方式：直接授权给用户
GRANT SELECT, INSERT ON devops_db.* TO 'backend'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 8.0+ 角色管理（RBAC）
-- 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 给角色授权
GRANT SELECT ON devops_db.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON devops_db.* TO 'app_write';
GRANT ALL ON devops_db.* TO 'app_admin';

-- 授权角色给用户
GRANT 'app_write' TO 'developer'@'%';
SET DEFAULT ROLE 'app_write' TO 'developer'@'%';          -- 设置默认角色

-- 激活角色（会话级）
SET ROLE 'app_admin';

-- 查看角色权限
SHOW GRANTS FOR 'app_write';
SHOW GRANTS FOR 'developer'@'%' USING 'app_write';

-- 回收权限
REVOKE DELETE ON devops_db.* FROM 'app_write';
DROP ROLE 'app_read';

-- SSL连接要求（5.7+）
REQUIRE SSL;                                            -- 强制SSL
REQUIRE X509;                                           -- 需要客户端证书
REQUIRE CIPHER 'AES256-SHA';

-- 5.7+ 密码策略插件
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;

-- 8.0+ 连接加密（废弃mysql_ssl_rsa_setup，使用组件）
INSTALL COMPONENT 'file://component_mysqlx';

-- 审计日志（企业版）或开源替代
-- 8.0.11+ 支持资源组（限制CPU）
CREATE RESOURCE GROUP batch_group
    TYPE = USER
    VCPU = 2-3                                          -- 限制CPU核心
    THREAD_PRIORITY = 10
    ENABLE;

SET RESOURCE GROUP batch_group FOR THREAD 123;          -- 绑定线程

-- 经典备份（所有版本）
mysqldump -u root -p --single-transaction                 -- 一致性备份(InnoDB)
    --master-data=2                                     -- 记录binlog位置（5.x）
    --source-data=2                                     -- 8.0.26+ 新名称
    --routines --triggers --events                       -- 包含存储过程等
    --databases devops_db > backup.sql

-- 8.0+ 备份锁（更轻量）
LOCK INSTANCE FOR BACKUP;                               -- 8.0+ 备份锁，允许DML
-- 执行物理文件复制...
UNLOCK INSTANCE;

-- 仅备份结构
mysqldump --no-data devops_db > schema.sql;

-- 仅备份数据
mysqldump --no-create-info devops_db > data.sql;

-- 按条件备份
mysqldump --where "created_at > '2024-01-01'" devops_db users > recent_users.sql

-- 8.0.17+ 克隆插件（本地或远程克隆）
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

-- 本地克隆
CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql_backup';

-- 远程克隆（从捐赠者实例克隆）
CLONE INSTANCE FROM 'donor'@'192.168.1.100':3306
    IDENTIFIED BY 'password'
    DATA DIRECTORY = '/var/lib/mysql_clone';

-- 查看克隆状态
SELECT * FROM performance_schema.clone_status;
SELECT * FROM performance_schema.clone_progress;

-- 恢复流程
-- 1. 恢复全量备份
mysql -u root -p < backup.sql

-- 2. 应用binlog（5.7+使用mysqlbinlog，8.0支持更精细控制）
mysqlbinlog --start-datetime="2024-03-13 10:00:00" \
            --stop-datetime="2024-03-13 12:00:00" \
            --database=devops_db \
            mysql-bin.000123 | mysql -u root -p

-- 8.0+ 基于GTID的恢复
mysqlbinlog --include-gtids='uuid:1-100' mysql-bin.* | mysql -u root -p

-- 主库配置（5.5+ 基础复制）
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- 5.6+ GTID复制（推荐）
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_AUTO_POSITION=1;                              -- GTID自动定位

-- 8.0+ 新语法（CHANGE REPLICATION SOURCE TO）
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='master_host',
    SOURCE_USER='repl',
    SOURCE_PASSWORD='password',
    SOURCE_AUTO_POSITION=1,
    SOURCE_SSL=1;                                        -- 强制SSL复制

-- 启动/停止复制
START SLAVE;                                            -- 5.7及之前
START REPLICA;                                          -- 8.0.22+ 新术语

STOP SLAVE SQL_THREAD;                                  -- 仅停止SQL线程

-- 查看复制状态
SHOW SLAVE STATUS\G                                     -- 传统方式
SHOW REPLICA STATUS\G                                   -- 8.0.22+

-- 复制过滤（5.7+ 在线修改）
CHANGE REPLICATION FILTER 
    REPLICATE_DO_DB = (devops_db),
    REPLICATE_IGNORE_TABLE = (devops_db.logs);

-- 8.0+ 多线程复制优化（基于writeset）
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;
SET GLOBAL slave_parallel_workers = 8;                  -- 5.6+ 并行worker

-- 8.0+ 组复制（MGR）自动故障转移
-- 配置组复制（单主模式）
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 加入已有组
START GROUP_REPLICATION;

-- 查看组成员
SELECT * FROM performance_schema.replication_group_members;

-- 8.0+ InnoDB ClusterSet（跨地域容灾）
-- 使用MySQL Shell配置
-- cluster.createClusterSet('myClusterSet')

-- 开启监控（8.0默认开启大部分）
UPDATE performance_schema.setup_consumers SET ENABLED='YES';
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES';

-- 查看慢查询（不使用慢日志表方式）
SELECT * FROM performance_schema.events_statements_history_long 
WHERE TIMER_WAIT > 1*10^12;                            -- 超过1秒

-- 5.7+ SYS Schema封装查询
-- 查看慢查询TOP10
SELECT * FROM sys.statements_with_full_table_scans 
ORDER BY rows_examined DESC LIMIT 10;

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

-- 查看连接和内存
SELECT * FROM sys.session;
SELECT * FROM sys.memory_by_thread_by_current_bytes;

-- EXPLAIN ANALYZE（实际执行分析）
EXPLAIN ANALYZE 
SELECT /*+ SET_VAR(optimizer_switch='derived_condition_pushdown=on') */ *
FROM (SELECT * FROM orders WHERE amount > 100) AS derived
JOIN users ON derived.user_id = users.id;

-- 查看资源组使用
SELECT * FROM information_schema.resource_groups;

-- 查看直方图使用效率
SELECT * FROM information_schema.COLUMN_STATISTICS;

-- 查看不可见索引状态（8.0+）
SELECT INDEX_NAME, IS_VISIBLE 
FROM information_schema.STATISTICS 
WHERE TABLE_NAME = 'users';

-- 升级前检查（5.7→8.0）
-- 1. 使用官方升级检查工具（mysqlsh）
-- mysqlsh -- util.checkForServerUpgrade()

-- 2. 手动检查不兼容项
-- 检查废弃变量
SHOW VARIABLES LIKE 'tx_isolation';                     -- 8.0已移除，使用transaction_isolation

-- 检查认证插件
SELECT user, host, plugin FROM mysql.user 
WHERE plugin IN ('mysql_old_password', 'sha256_password');  -- 需要更新

-- 3. 8.0升级后自动执行
-- mysql_upgrade已废弃，8.0在启动时自动升级数据字典

-- 使用逻辑复制升级（5.5/5.6→5.7→8.0）
-- 1. 建立5.7从库，从5.5/5.6复制
-- 2. 验证5.7运行正常
-- 3. 建立8.0从库，从5.7复制（需设置兼容模式）
SET GLOBAL binlog_row_image = FULL;                     -- 8.0要求

-- 4. 业务切换到8.0（通过VIP或DNS）
-- 5. 观察后下线旧版本

-- 8.0克隆升级（同版本小版本升级）
CLONE INSTANCE FROM 'old_instance'@'host':3306 
    IDENTIFIED BY 'password';

# docker-compose.yml (8.0+示例)
version: '3.8'
services:
  mysql:
    image: mysql:8.0.36
    environment:
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/db_root_password
      MYSQL_DATABASE: devops_db
    command:
      - --default-authentication-plugin=mysql_native_password  # 兼容旧客户端
      - --innodb-dedicated-server=on                           # 自动内存配置
      - --performance-schema=on
    volumes:
      - mysql_data:/var/lib/mysql
      - ./config/my.cnf:/etc/mysql/conf.d/custom.cnf
    secrets:
      - db_root_password

-- 8.0+ SET PERSIST（持久化到mysqld-auto.cnf）
SET PERSIST max_connections = 500;                      -- 重启后仍有效
SET PERSIST_ONLY back_log = 1024;                       -- 只写入文件，不立即生效

-- 查看持久化配置
SELECT * FROM performance_schema.persisted_variables;

-- 重置持久化配置
RESET PERSIST max_connections;
RESET PERSIST;                                          -- 清除所有

新项目？
├─ 是 → MySQL 8.0.36+ 或 8.4 LTS
└─ 否 → 当前版本 < 5.7？
    ├─ 是 → 优先升级至5.7，验证后→8.0
    └─ 否 → 5.7维护模式，规划8.0升级

# my.cnf 版本差异化配置

# 5.5/5.6 基础配置
[mysqld]
innodb_file_per_table = 1           # 独立表空间（5.5+重要）
innodb_flush_method = O_DIRECT      # 避免双缓冲
query_cache_type = 0                # 5.6建议关闭QC

# 5.7 优化配置
gtid_mode = ON                      # 开启GTID
enforce_gtid_consistency = ON
binlog_row_image = FULL             # 为升级8.0做准备
log_timestamps = SYSTEM             # 日志时间带时区
innodb_buffer_pool_dump_at_shutdown = 1  # 快速预热

# 8.0+ 现代配置
default_authentication_plugin = caching_sha2_password
local_infile = 0                    # 安全：禁用LOAD DATA LOCAL
require_secure_transport = ON       # 强制SSL/TLS
information_schema_stats_expiry = 0 # 实时统计（影响性能，谨慎）
binlog_expire_logs_seconds = 604800 # 替代expire_logs_days

-- ❌ 绝对禁止或谨慎的操作

-- 1. 无WHERE条件的UPDATE/DELETE（所有版本灾难）
UPDATE users SET status = 'active';                     -- 全表更新！
-- 安全做法：先SELECT确认，或SET sql_safe_updates=1（5.6+）

-- 2. 大表DDL不加ALGORITHM（5.5/5.6锁表噩梦）
ALTER TABLE big_table ADD COLUMN...;                    -- 锁表数小时
-- 正确做法（5.6+）：ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE;

-- 3. 8.0前使用GROUP BY不严格（ONLY_FULL_GROUP_BY）
-- 5.7+ 默认开启，旧代码可能报错

-- 4. 忽略排序规则导致的索引失效（8.0 utf8mb4_0900_ai_ci）
WHERE name = 'Admin' COLLATE utf8mb4_unicode_ci;        -- 可能不走索引

-- 5. 使用SELECT * 在JSON/生成列多的表（性能杀手）
-- 特别是8.0+的丰富数据类型，应明确列名