SQL实战练习指南：从基础查询到高级优化-二趣网

1. 为什么需要SQL练习题？

作为从业15年的数据库工程师，我见过太多初学者在掌握SQL语法后却无法灵活运用的案例。SQL练习题的价值在于：它能让你在安全环境中犯错误——这些错误如果发生在生产环境，代价可能是灾难性的。

去年我带过一个实习生，他能在笔试中完美写出各种JOIN语法，却在第一次处理真实订单数据时，因为漏写WHERE条件而误删了整张表。这正是缺乏实战训练的典型后果。

2. 练习环境搭建指南

2.1 推荐使用Docker快速部署

比起直接安装MySQL服务，我更推荐用Docker容器化部署练习环境。这样你可以随时销毁重建数据库，不用担心误操作影响本地环境：

docker run --name mysql-practice -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql:8.0

这个命令会启动一个MySQL 8.0容器，将root密码设为123456（生产环境请勿使用简单密码），并映射到本地的3306端口。

2.2 初始化练习数据库

下载我准备的练习数据库脚本（模拟电商业务场景）：

CREATE DATABASE practice_db; USE practice_db; CREATE TABLE users ( user_id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, registration_date DATE, vip_level ENUM('bronze','silver','gold','platinum') ); CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, order_date DATETIME, total_amount DECIMAL(10,2), FOREIGN KEY (user_id) REFERENCES users(user_id) ); -- 插入示例数据（约1000条记录） INSERT INTO users VALUES (1,'张三','2023-01-15','gold'), (2,'李四','2023-02-20','silver'), /* 更多数据... */;

提示：实际练习时建议数据量控制在1000-5000条，既能体现查询性能差异，又不会让等待时间过长。

3. 基础查询专项训练

3.1 单表查询核心要点

看似简单的SELECT语句藏着很多魔鬼细节：

-- 常见错误：忽略NULL值处理 SELECT AVG(total_amount) FROM orders; -- 包含NULL的记录会被忽略 -- 正确做法： SELECT AVG(IFNULL(total_amount,0)) FROM orders; -- 日期范围查询的坑 SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'; -- 会漏掉1月31日23:59:59的数据 -- 更严谨的写法： SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

3.2 多表连接实战技巧

JOIN操作是SQL的核心难点，特别注意这些场景：

-- 内连接 vs 左连接 SELECT u.username, o.order_id FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE o.order_id IS NULL; -- 找出从未下单的用户 -- 自连接的特殊应用（查找相同等级的用户对） SELECT a.user_id, b.user_id, a.vip_level FROM users a JOIN users b ON a.vip_level = b.vip_level AND a.user_id < b.user_id;

4. 复杂查询进阶训练

4.1 子查询性能优化

很多教科书不会告诉你：错误的子查询写法可能导致性能下降100倍：

-- 低效写法（对每行orders执行子查询） SELECT * FROM orders o WHERE o.total_amount > ( SELECT AVG(total_amount) FROM orders ); -- 高效写法（先计算再比较） SET @avg_amount = (SELECT AVG(total_amount) FROM orders); SELECT * FROM orders WHERE total_amount > @avg_amount;

4.2 窗口函数实战应用

MySQL 8.0+支持的窗口函数能解决很多复杂问题：

-- 计算每个用户的订单金额排名 SELECT user_id, order_id, total_amount, RANK() OVER (PARTITION BY user_id ORDER BY total_amount DESC) as amount_rank FROM orders; -- 滚动统计最近30天销售额 SELECT order_date, SUM(total_amount) OVER ( ORDER BY order_date RANGE BETWEEN INTERVAL 29 DAY PRECEDING AND CURRENT ROW ) as rolling_sum FROM orders;

5. 事务与锁机制深度练习

5.1 并发场景模拟

打开两个MySQL客户端会话，模拟经典的丢失更新问题：

-- 会话1 START TRANSACTION; SELECT balance FROM accounts WHERE user_id = 1; -- 假设读到1000 -- 会话2 START TRANSACTION; SELECT balance FROM accounts WHERE user_id = 1; -- 同样读到1000 UPDATE accounts SET balance = balance - 200 WHERE user_id = 1; -- 扣款200 COMMIT; -- 会话1 UPDATE accounts SET balance = balance - 300 WHERE user_id = 1; -- 基于旧值1000扣款300 COMMIT; -- 最终余额变成700（正确应为500）

5.2 锁排查实战

当遇到锁等待超时错误（Lock wait timeout exceeded）时，可以这样排查：

-- 查看当前锁信息 SELECT * FROM performance_schema.data_locks; SELECT * FROM sys.innodb_lock_waits; -- 模拟死锁场景 -- 会话1 START TRANSACTION; UPDATE orders SET total_amount = 100 WHERE order_id = 1; UPDATE users SET username = 'new1' WHERE user_id = 1; -- 会话2 START TRANSACTION; UPDATE users SET username = 'new2' WHERE user_id = 1; UPDATE orders SET total_amount = 200 WHERE order_id = 1; -- 这里会发生死锁

6. 性能优化专项

6.1 索引设计实战

通过EXPLAIN分析不同查询计划的差异：

-- 没有索引的情况 EXPLAIN SELECT * FROM orders WHERE user_id = 5; -- type: ALL（全表扫描） -- 添加索引后 ALTER TABLE orders ADD INDEX idx_user_id (user_id); EXPLAIN SELECT * FROM orders WHERE user_id = 5; -- type: ref（索引查找） -- 复合索引的最左前缀原则 ALTER TABLE orders ADD INDEX idx_date_amount (order_date, total_amount); EXPLAIN SELECT * FROM orders WHERE total_amount > 100; -- 无法使用复合索引

6.2 慢查询日志分析

启用慢查询日志捕获性能问题：

-- 在my.cnf中配置 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1 -- 使用mysqldumpslow工具分析 mysqldumpslow -s t /var/log/mysql/mysql-slow.log

7. 真实业务场景模拟题

7.1 电商促销活动分析

-- 找出消费金额前10%的高价值用户 WITH user_stats AS ( SELECT user_id, SUM(total_amount) as total_spent, NTILE(10) OVER (ORDER BY SUM(total_amount) DESC) as user_tier FROM orders GROUP BY user_id ) SELECT * FROM user_stats WHERE user_tier = 1; -- 计算每月复购率 WITH monthly_orders AS ( SELECT user_id, DATE_FORMAT(order_date, '%Y-%m') as month, COUNT(DISTINCT DATE_FORMAT(order_date, '%Y-%m-%d')) as order_days FROM orders GROUP BY user_id, month ) SELECT month, COUNT(DISTINCT user_id) as total_users, COUNT(DISTINCT CASE WHEN order_days > 1 THEN user_id END) as repeat_users, ROUND(COUNT(DISTINCT CASE WHEN order_days > 1 THEN user_id END) / COUNT(DISTINCT user_id) * 100, 2) as repeat_rate FROM monthly_orders GROUP BY month;

7.2 用户行为路径分析

-- 使用递归CTE分析用户下单路径 WITH RECURSIVE user_paths AS ( -- 初始查询：获取每个用户的第一笔订单 SELECT user_id, order_id, order_date, CAST(order_id AS CHAR(200)) AS path, 1 AS path_length FROM orders o1 WHERE order_date = ( SELECT MIN(order_date) FROM orders o2 WHERE o2.user_id = o1.user_id ) UNION ALL -- 递归部分：查找下一笔订单 SELECT o.user_id, o.order_id, o.order_date, CONCAT(up.path, '->', o.order_id), up.path_length + 1 FROM user_paths up JOIN orders o ON up.user_id = o.user_id WHERE o.order_date > up.order_date AND o.order_date = ( SELECT MIN(order_date) FROM orders o3 WHERE o3.user_id = up.user_id AND o3.order_date > up.order_date ) ) SELECT * FROM user_paths WHERE user_id = 123;

8. 常见错误与调试技巧

8.1 语法错误排查

当遇到"You have an error in your SQL syntax"错误时：

先检查保留字是否被用作标识符（如order、group等），若是需要用反引号包裹：
```
SELECT * FROM `order`; -- 而不是 SELECT * FROM order;
```
检查字符串引号是否匹配（MySQL中单引号和双引号有区别）
使用MySQL的SQL模式验证：
```
SET sql_mode = 'STRICT_TRANS_TABLES';
```

8.2 隐式类型转换陷阱

-- 示例1：字符串与数字比较 SELECT * FROM users WHERE user_id = '123abc'; -- 会隐式转换为123 -- 示例2：日期格式不一致 SELECT * FROM orders WHERE order_date BETWEEN '2023/01/01' AND '2023/01/31'; -- 可能无法使用索引 -- 解决方案：始终使用标准格式 SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

9. 高级特性实战

9.1 JSON数据类型操作

MySQL 5.7+支持JSON类型，处理半结构化数据：

-- 创建包含JSON列的表 CREATE TABLE product_reviews ( review_id INT PRIMARY KEY AUTO_INCREMENT, product_id INT, review_data JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 插入JSON数据 INSERT INTO product_reviews VALUES (NULL, 101, '{ "rating": 5, "tags": ["durable", "good value"], "author": { "name": "王五", "vip": true } }', NOW()); -- 查询JSON字段 SELECT review_id, review_data->"$.rating" as rating, review_data->"$.author.name" as author_name FROM product_reviews WHERE review_data->"$.rating" >= 4; -- 更新JSON字段 UPDATE product_reviews SET review_data = JSON_SET(review_data, '$.rating', 4) WHERE review_id = 1;

9.2 生成列(Generated Columns)应用

-- 创建虚拟生成列 CREATE TABLE invoices ( invoice_id INT PRIMARY KEY AUTO_INCREMENT, subtotal DECIMAL(10,2), tax_rate DECIMAL(5,2), tax_amount DECIMAL(10,2) AS (subtotal * tax_rate), total DECIMAL(10,2) AS (subtotal + subtotal * tax_rate) STORED ); -- 虚拟列不占用存储空间，STORED列会实际存储 INSERT INTO invoices (invoice_id, subtotal, tax_rate) VALUES (1, 100.00, 0.08); SELECT * FROM invoices; /* invoice_id | subtotal | tax_rate | tax_amount | total -----------+----------+----------+------------+------- 1 | 100.00 | 0.08 | 8.00 | 108.00 */

10. 安全防护实践

10.1 SQL注入防御

永远不要拼接SQL字符串：

// 错误示范（Java） String sql = "SELECT * FROM users WHERE username = '" + username + "'"; // 正确做法：使用预编译语句 PreparedStatement stmt = conn.prepareStatement( "SELECT * FROM users WHERE username = ?"); stmt.setString(1, username);

在MySQL中也可以使用存储过程封装敏感操作：

DELIMITER // CREATE PROCEDURE get_user(IN p_username VARCHAR(50)) BEGIN SET @sql = CONCAT('SELECT * FROM users WHERE username = ?'); PREPARE stmt FROM @sql; EXECUTE stmt USING p_username; DEALLOCATE PREPARE stmt; END // DELIMITER ;

10.2 权限最小化原则

创建专门的练习账号：

CREATE USER 'practice_user'@'%' IDENTIFIED BY 'complex_password123!'; GRANT SELECT, INSERT, UPDATE ON practice_db.* TO 'practice_user'@'%'; REVOKE DROP, DELETE, ALTER ON practice_db.* FROM 'practice_user'@'%';

11. 性能监控与优化

11.1 执行计划深度解读

使用EXPLAIN FORMAT=JSON获取详细执行计划：

EXPLAIN FORMAT=JSON SELECT u.username, COUNT(o.order_id) as order_count FROM users u LEFT JOIN orders o ON u.user_id = o.user_id GROUP BY u.user_id HAVING order_count > 5;

关键指标解读：

query_cost：预估查询成本
access_type：访问类型（const/ref/range/index/ALL）
possible_keys：可能使用的索引
rows_examined_per_scan：每次扫描检查的行数

11.2 索引优化实战

使用不可见索引测试优化效果：

-- 添加不可见索引（优化器会忽略） ALTER TABLE orders ADD INDEX idx_test (user_id) INVISIBLE; -- 测试查询性能 EXPLAIN SELECT * FROM orders WHERE user_id = 100; -- 不使用新索引 -- 临时启用优化器使用该索引 SET SESSION optimizer_switch='use_invisible_indexes=on'; EXPLAIN SELECT * FROM orders WHERE user_id = 100; -- 使用新索引 -- 确认有效后设为可见 ALTER TABLE orders ALTER INDEX idx_test VISIBLE;

12. 备份与恢复策略

12.1 逻辑备份最佳实践

使用mysqldump时的重要参数：

# 完整备份（包含存储过程、触发器等） mysqldump --single-transaction --routines --triggers \ --events --hex-blob practice_db > backup.sql # 只备份结构 mysqldump --no-data practice_db > schema.sql # 只备份特定表数据 mysqldump practice_db users orders > tables_data.sql

12.2 时间点恢复(PITR)配置

启用二进制日志：

[mysqld] log-bin=mysql-bin binlog-format=ROW

执行恢复操作：

# 先恢复完整备份 mysql practice_db < full_backup.sql # 再重放binlog到指定时间点 mysqlbinlog --stop-datetime="2023-06-15 15:00:00" \ /var/lib/mysql/mysql-bin.000123 | mysql -u root -p

13. 分库分表实战

13.1 水平分片策略

使用应用层分片路由：

// 根据user_id决定访问哪个分片 int shardIndex = userId % 4; String jdbcUrl = "jdbc:mysql://shard" + shardIndex + ":3306/order_db";

13.2 全局唯一ID生成

雪花算法(Snowflake)实现示例：

CREATE TABLE sequence_table ( stub CHAR(1) PRIMARY KEY DEFAULT '', id BIGINT UNSIGNED NOT NULL ) ENGINE=InnoDB; -- 获取ID的存储过程 DELIMITER // CREATE PROCEDURE next_id(OUT next_id BIGINT) BEGIN REPLACE INTO sequence_table (stub, id) VALUES ('', LAST_INSERT_ID(id + 1)); SET next_id = LAST_INSERT_ID(); END // DELIMITER ;

14. 高可用架构

14.1 主从复制配置

设置GTID复制：

-- 主库配置 [mysqld] server-id = 1 log_bin = mysql-bin binlog_format = ROW gtid_mode = ON enforce_gtid_consistency = ON -- 从库配置 CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1; START SLAVE;

14.2 读写分离实现

使用MySQL Router自动路由：

[DEFAULT] logging_folder = /var/log/mysqlrouter [routing:read_write] bind_address = 0.0.0.0 bind_port = 6446 destinations = master:3306 routing_strategy = first-available [routing:read_only] bind_address = 0.0.0.0 bind_port = 6447 destinations = slave1:3306,slave2:3306 routing_strategy = round-robin

15. 云数据库优化

15.1 RDS参数组调优

关键参数调整建议：

innodb_buffer_pool_size：设置为可用内存的70-80%
innodb_io_capacity：SSD存储建议设置为2000-4000
table_open_cache：根据实际表数量调整

15.2 连接池配置

推荐使用HikariCP配置：

HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:mysql://localhost:3306/practice_db"); config.setUsername("user"); config.setPassword("password"); config.setMaximumPoolSize(10); config.setConnectionTimeout(30000); config.setIdleTimeout(600000); config.setMaxLifetime(1800000); config.addDataSourceProperty("cachePrepStmts", "true"); config.addDataSourceProperty("prepStmtCacheSize", "250"); config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); HikariDataSource ds = new HikariDataSource(config);

16. 数据迁移方案

16.1 全量迁移工具对比

工具	适用场景	优点	缺点
mysqldump	小型数据库(<10GB)	简单易用，兼容性好	单线程，大库耗时
mysqlpump	MySQL 5.7+	多线程导出	不兼容旧版本
mydumper	大型数据库	并行导出，压缩支持好	需要额外安装
AWS DMS	跨云迁移	支持持续同步	成本较高

16.2 增量迁移实施

使用binlog实现准实时同步：

# 使用canal监听binlog canal.instance.mysql.slaveId=1234 canal.instance.filter.regex=.*\\..* canal.instance.filter.black.regex=

17. 数据质量监控

17.1 数据校验方案

使用checksum验证数据一致性：

-- 计算表数据校验和 SELECT COUNT(*) as row_count, SUM(CRC32(CONCAT_WS(',',user_id,username))) as checksum FROM users;

17.2 异常数据检测

创建数据质量规则表：

CREATE TABLE data_quality_rules ( rule_id INT PRIMARY KEY, rule_name VARCHAR(100), check_sql TEXT, severity ENUM('critical','warning') ); INSERT INTO data_quality_rules VALUES (1, 'null_username', 'SELECT COUNT(*) FROM users WHERE username IS NULL', 'critical'), (2, 'future_dates', 'SELECT COUNT(*) FROM orders WHERE order_date > NOW()', 'warning');

18. 数据仓库集成

18.1 ETL流程设计

使用存储过程实现简单ETL：

DELIMITER // CREATE PROCEDURE etl_daily_sales() BEGIN -- 1. 创建临时表存储转换数据 CREATE TEMPORARY TABLE temp_daily_sales AS SELECT DATE(order_date) as sale_date, user_id, SUM(total_amount) as daily_total FROM orders WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) GROUP BY DATE(order_date), user_id; -- 2. 加载到数据仓库表 INSERT INTO dw_sales_fact (sale_date, user_id, amount) SELECT sale_date, user_id, daily_total FROM temp_daily_sales ON DUPLICATE KEY UPDATE amount = VALUES(amount); -- 3. 清理临时表 DROP TEMPORARY TABLE IF EXISTS temp_daily_sales; END // DELIMITER ;

18.2 星型模型示例

-- 事实表 CREATE TABLE fact_orders ( order_id INT, user_id INT, product_id INT, order_date DATE, quantity INT, amount DECIMAL(10,2), PRIMARY KEY (order_id) ); -- 维度表 CREATE TABLE dim_users ( user_id INT PRIMARY KEY, username VARCHAR(50), registration_date DATE, vip_level VARCHAR(20) ); CREATE TABLE dim_products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), category VARCHAR(50), price DECIMAL(10,2) );

19. 时序数据处理

19.1 时间序列聚合

使用时间桶函数：

-- 按5分钟粒度聚合数据 SELECT FROM_UNIXTIME( FLOOR(UNIX_TIMESTAMP(create_time)/300)*300 ) as time_bucket, COUNT(*) as event_count, AVG(value) as avg_value FROM sensor_data GROUP BY time_bucket ORDER BY time_bucket;

19.2 移动平均计算

SELECT date, temperature, AVG(temperature) OVER ( ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) as moving_avg FROM weather_data;

20. 空间数据处理

20.1 地理坐标存储

使用空间数据类型：

CREATE TABLE locations ( location_id INT PRIMARY KEY, place_name VARCHAR(100), position POINT SRID 4326, SPATIAL INDEX(position) ); INSERT INTO locations VALUES (1, 'Eiffel Tower', ST_GeomFromText('POINT(48.8584 2.2945)', 4326)), (2, 'Statue of Liberty', ST_GeomFromText('POINT(40.6892 -74.0445)', 4326)); -- 计算两点距离（单位：米） SELECT ST_Distance_Sphere( (SELECT position FROM locations WHERE location_id = 1), (SELECT position FROM locations WHERE location_id = 2) ) as distance_meters;

20.2 地理围栏查询

-- 查找半径1公里内的地点 SET @center = ST_GeomFromText('POINT(40.7128 -74.0060)', 4326); SELECT place_name, ST_Distance_Sphere(position, @center) as distance FROM locations WHERE ST_Distance_Sphere(position, @center) <= 1000 ORDER BY distance;

21. 全文检索实现

21.1 全文索引配置

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title, body); -- 自然语言搜索 SELECT * FROM articles WHERE MATCH(title, body) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE); -- 布尔模式搜索 SELECT * FROM articles WHERE MATCH(title, body) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

21.2 同义词扩展搜索

配置同义词库：

[mysqld] ft_stopword_file = '' ft_min_word_len = 2 ft_wordlist_charset = utf8 ft_wordlist_file = /etc/mysql/synonyms.txt

同义词文件示例：

# synonyms.txt DB database optimize optimize tuning

22. 性能基准测试

22.1 sysbench压力测试

# 准备测试数据 sysbench oltp_read_write \ --db-driver=mysql \ --mysql-host=localhost \ --mysql-port=3306 \ --mysql-user=root \ --mysql-password=123456 \ --mysql-db=test_db \ --tables=10 \ --table-size=100000 \ prepare # 执行测试 sysbench oltp_read_write \ --threads=16 \ --time=300 \ --report-interval=10 \ run

22.2 自定义基准测试

创建专用测试存储过程：

DELIMITER // CREATE PROCEDURE run_benchmark(IN iterations INT) BEGIN DECLARE i INT DEFAULT 0; DECLARE start_time BIGINT; DECLARE end_time BIGINT; CREATE TEMPORARY TABLE IF NOT EXISTS benchmark_results ( test_name VARCHAR(50), exec_time_ms BIGINT, exec_count INT ); SET start_time = UNIX_TIMESTAMP(UTC_TIMESTAMP(6)) * 1000000; WHILE i < iterations DO -- 测试查询1：简单主键查询 SELECT * FROM users WHERE user_id = FLOOR(1 + RAND() * 10000); -- 测试查询2：复杂连接查询 SELECT u.username, COUNT(o.order_id) FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE u.registration_date > '2023-01-01' GROUP BY u.user_id HAVING COUNT(o.order_id) > 5; SET i = i + 1; END WHILE; SET end_time = UNIX_TIMESTAMP(UTC_TIMESTAMP(6)) * 1000000; INSERT INTO benchmark_results VALUES ('mixed_workload', (end_time - start_time)/1000, iterations); END // DELIMITER ;

23. 故障模拟与恢复

23.1 数据页损坏恢复

模拟InnoDB页损坏：

# 找到表空间文件 SELECT @@datadir; ls /var/lib/mysql/practice_db # 使用dd命令破坏部分数据（仅用于测试！） dd if=/dev/zero of=/var/lib/mysql/practice_db/users.ibd bs=1024 count=10 conv=notrunc

恢复步骤：

使用innodb_force_recovery启动MySQL
导出受影响表数据
重建表结构
导入数据

23.2 主从同步中断处理

常见错误处理：

-- 查看从库状态 SHOW SLAVE STATUS\G -- 跳过指定错误 STOP SLAVE; SET GLOBAL sql_slave_skip_counter = 1; START SLAVE; -- 重置复制（谨慎使用） RESET SLAVE ALL; CHANGE MASTER TO ...; START SLAVE;

24. 监控与告警配置

24.1 关键指标监控

使用Performance Schema采集数据：

-- 查询执行时间最长的SQL SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10; -- 监控锁等待 SELECT * FROM sys.innodb_lock_waits; -- 表空间使用情况 SELECT * FROM sys.schema_table_statistics;

24.2 告警规则示例

使用Prometheus监控MySQL：

groups: - name: mysql-alerts rules: - alert: HighCPUUsage expr: rate(mysql_global_status_cpu_time_total[1m]) > 0.8 for: 5m labels: severity: warning annotations: summary: "High MySQL CPU usage ({{ $value }}%)" - alert: ReplicationLag expr: mysql_slave_status_seconds_behind_master > 30 for: 10m labels: severity: critical annotations: summary: "MySQL replication lag ({{ $value }} seconds)"

25. 版本升级策略

25.1 原地升级步骤

备份所有数据库

检查不兼容语法：

SELECT * FROM sys.schema_incompatibilities WHERE version > '8.0';

停止MySQL服务
安装新版本软件包
启动升级过程：
```
mysqld --upgrade=FORCE
```
验证系统表更新

25.2 逻辑升级方案

使用主从切换方式：

搭建新版本从库
配置复制到新版本实例
验证数据一致性
切换应用连接
逐步下线旧版本

26. 安全加固措施

26.1 密码策略配置

-- 安装密码验证组件 INSTALL COMPONENT 'file://component_validate_password'; -- 设置密码策略 SET GLOBAL validate_password.policy = STRONG; SET GLOBAL validate_password.length = 12; SET GLOBAL validate_password.mixed_case_count = 1; SET GLOBAL validate_password.number_count = 1; SET GLOBAL validate_password.special_char_count = 1;

26.2 审计日志配置

启用企业版审计插件或使用开源替代方案：

[mysqld] plugin-load-add=audit_log.so audit_log_format=JSON audit_log_policy=ALL audit_log_rotate_on_size=100000000

27. 资源限制管理

27.1 连接数控制

-- 设置最大连接数 SET GLOBAL max_connections = 200; -- 按用户限制资源 CREATE USER 'app_user'@'%' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 500 MAX_CONNECTIONS_PER_HOUR 100;

27.2 内存使用限制

[mysqld] # 限制内存表大小 tmp_table_size = 32M max_heap_table_size = 32M # 每个连接的内存限制 thread_stack = 256K sort_buffer_size = 4M join_buffer_size = 4M

28. 分布式事务实践

28.1 XA事务示例

-- 协调者 XA START 'transaction1'; UPDATE account SET balance = balance - 100 WHERE user_id = 1; XA END 'transaction1'; XA PREPARE 'transaction1'; -- 参与者1 XA START 'transaction1'; UPDATE inventory SET stock = stock - 1 WHERE product_id = 101; XA END 'transaction1'; XA PREPARE 'transaction1'; -- 参与者2 XA START 'transaction1'; INSERT INTO orders (user_id, product_id) VALUES (1, 101); XA END 'transaction1'; XA PREPARE 'transaction1'; -- 协调者提交 XA COMMIT 'transaction1';

28.2 最终一致性方案

使用本地消息表：

CREATE TABLE outbox_messages ( message_id BIGINT PRIMARY KEY AUTO_INCREMENT, topic VARCHAR(100), payload JSON, status ENUM('pending','sent','failed'), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_status (status) ); -- 在业务事务中包含消息插入 START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE user_id = 1; INSERT INTO outbox_messages VALUES (NULL, 'payment', '{"userId":1,"amount":100}', 'pending', NOW()); COMMIT; -- 单独进程轮询发送消息 SELECT * FROM outbox_messages WHERE status = 'pending' LIMIT 100 FOR UPDATE;

29. 数据脱敏方案

29.1 动态数据掩码

-- 创建视图实现动态脱敏 CREATE VIEW masked_customers AS SELECT customer_id, CONCAT(LEFT(name, 1), '***') as name, CONCAT('****-****-****-', RIGHT(credit_card,4)) as credit_card, email FROM customers; -- 使用函数实现列级脱敏 CREATE FUNCTION mask_string(input VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC BEGIN RETURN CONCAT(LEFT(input, 1), REPEAT('*', CHAR_LENGTH(input)-1)); END; SELECT customer_id, mask_string(name) as name, mask_string(phone) as phone FROM customers;

29.2 静态数据脱敏

使用批量更新：

-- 姓名脱敏 UPDATE customers SET name = CONCAT(LEFT(name, 1), REPEAT('*', CHAR_LENGTH(name)-1)); -- 邮箱脱敏 UPDATE customers SET email = CONCAT( LEFT(SUBSTRING_INDEX(email, '@', 1), 2), '***@', SUBSTRING_INDEX(email, '@', -1) ); -- 电话号码脱敏 UPDATE customers SET phone = CONCAT( LEFT(phone, 3), '****', RIGHT(phone, 4) );

30. 数据归档策略

30.1 分区表归档

-- 创建按时间分区的表 CREATE TABLE orders ( order_id INT, order_date DATETIME, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY (order_id, order_date) ) PARTITION BY RANGE (TO_DAYS(order_date)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')), PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')), PARTITION pmax VALUES LESS THAN MAXVALUE ); -- 归档旧分区数据 ALTER TABLE orders EXCHANGE PARTITION p202301 WITH TABLE orders_archive_202301;

30.2 使用pt-archiver工具

# 归档3个月前的订单数据 pt-archiver \ --source h=localhost,D=practice_db,t=orders \ --dest h=localhost,D=archive_db,t

企业官网建设流程全解析