1. MySQL安装前的准备工作

作为最流行的开源关系型数据库之一，MySQL在Web应用、企业系统等领域有着广泛应用。在开始安装前，我们需要做好以下准备工作：

• 操作系统兼容性检查：MySQL支持Windows、Linux和macOS三大主流平台。以Windows 10为例，官方要求至少4GB内存和2GB磁盘空间，但实际开发环境建议8GB以上内存。

• 版本选择策略：

  • 社区版(MySQL Community Server)：免费开源版本，适合个人开发者和小型项目
  • 企业版(MySQL Enterprise Edition)：提供商业支持和高可用性方案
  • 集群版(MySQL Cluster)：分布式数据库解决方案

提示：新手建议选择最新的稳定版(如8.0.x)，避免使用已停止维护的版本(如5.1)

• 安装包类型说明：
  • MSI安装包(Windows)：图形化安装向导，适合新手
  • ZIP压缩包：需要手动配置，灵活性更高
  • 源码编译：适合需要深度定制的场景

2. 详细安装步骤解析

2.1 Windows平台安装流程

1. 下载安装包：

   • 访问MySQL官网(https://dev.mysql.com/downloads/)
   • 选择"MySQL Community Server"
   • 下载适合的Windows版本(推荐MSI安装包)

2. 运行安装向导：

   # 示例安装命令(实际以图形界面操作为主) msiexec /i mysql-installer-community-8.0.xx.msi

3. 安装类型选择：

   • Developer Default：开发默认配置
   • Server only：仅安装服务器
   • Client only：仅客户端工具
   • Full：完整安装

4. 关键配置参数：

   • 认证方式：建议选择强密码加密(SHA256)
   • root密码：设置复杂密码并妥善保管
   • Windows服务：建议勾选"Configure MySQL Server as a Windows Service"

2.2 Linux平台安装方案

对于Linux用户，推荐使用包管理器安装：

Ubuntu/Debian:

sudo apt update sudo apt install mysql-server sudo mysql_secure_installation

CentOS/RHEL:

sudo yum install mysql-server sudo systemctl start mysqld sudo mysql_secure_installation

注意：Linux安装后需要运行安全脚本，设置root密码并移除匿名用户等不安全配置

3. 安装后关键配置

3.1 基础环境配置

1. 配置MySQL服务：

   • Windows：通过服务管理器设置自动启动
   • Linux：使用systemctl启用服务

     sudo systemctl enable mysqld

2. 环境变量设置：

   • 将MySQL的bin目录添加到系统PATH
   • Windows示例：

     C:\Program Files\MySQL\MySQL Server 8.0\bin

3. 防火墙配置：

   • 开放3306端口(默认MySQL端口)
   • Windows防火墙规则设置
   • Linux命令示例：

     sudo ufw allow 3306/tcp

3.2 配置文件优化

MySQL的核心配置文件(my.ini或my.cnf)需要根据硬件配置调整：

[mysqld] # 内存配置(8GB内存服务器示例) innodb_buffer_pool_size = 4G key_buffer_size = 256M # 连接数配置 max_connections = 200 thread_cache_size = 10 # 日志配置 slow_query_log = 1 long_query_time = 2

4. 常见问题解决方案

4.1 安装失败排查

1. 服务启动失败：

   • 检查错误日志(默认位置：/var/log/mysqld.log或MySQL安装目录/data)
   • 常见原因：端口冲突、权限问题、磁盘空间不足

2. 连接问题：

   • ERROR 1045：认证失败，检查用户名密码
   • ERROR 2003：无法连接，检查服务状态和防火墙

3. 密码重置方法：

   # 停止MySQL服务后启动到安全模式 mysqld_safe --skip-grant-tables & mysql -u root FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

4.2 性能优化建议

1. 基础优化检查：

   SHOW STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';

2. 索引优化：

   • 使用EXPLAIN分析查询
   • 避免全表扫描

3. 存储引擎选择：

   • InnoDB：事务支持，推荐默认使用
   • MyISAM：读密集型场景

5. 开发环境集成

5.1 连接MySQL的工具

1. 命令行客户端：

   mysql -u username -p

2. 图形化工具：

   • MySQL Workbench(官方工具)
   • Navicat
   • DBeaver

3. 编程语言连接：

   • Python示例(pymysql)：

     import pymysql conn = pymysql.connect(host='localhost', user='root', password='password', database='testdb')

5.2 数据库管理基础

1. 用户权限管理：

   CREATE USER 'devuser'@'%' IDENTIFIED BY 'password'; GRANT SELECT, INSERT ON dbname.* TO 'devuser'@'%';

2. 备份与恢复：

   # 备份 mysqldump -u root -p dbname > backup.sql # 恢复 mysql -u root -p dbname < backup.sql

3. 监控命令：

   SHOW PROCESSLIST; SHOW ENGINE INNODB STATUS;

6. 高级配置技巧

6.1 主从复制配置

1. 主服务器配置：

   [mysqld] server-id = 1 log_bin = mysql-bin binlog_format = ROW

2. 从服务器配置：

   [mysqld] server-id = 2 relay_log = mysql-relay-bin read_only = ON

3. 建立复制关系：

   CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

6.2 安全加固措施

1. SSL连接配置：

   CREATE USER 'secureuser'@'%' REQUIRE SSL;

2. 审计日志启用：

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

3. 定期维护任务：

   • 用户权限审计
   • 密码轮换
   • 日志清理

7. 版本升级指南

7.1 升级前准备

1. 备份策略：

   • 完整数据库备份
   • 配置文件备份
   • 用户权限导出

2. 兼容性检查：

   SELECT * FROM sys.schema_unused_indexes;

3. 测试环境验证：

   • 先在测试环境验证升级过程
   • 检查应用兼容性

7.2 实际升级步骤

1. Windows平台：

   • 运行新版本安装程序
   • 选择"Upgrade MySQL Server"

2. Linux平台：

   sudo apt upgrade mysql-server

3. 升级后操作：

   mysql_upgrade -u root -p

8. 云环境部署方案

8.1 主流云平台对比

8.2 自建与托管服务对比

1. 自建优势：

   • 完全控制配置
   • 成本较低(长期使用)
   • 特殊定制需求

2. 托管服务优势：

   • 自动备份恢复
   • 高可用性保障
   • 自动扩展能力

9. 监控与维护

9.1 性能监控工具

1. 内置工具：

   SHOW GLOBAL STATUS; SHOW ENGINE INNODB STATUS;

2. 第三方工具：

   • Prometheus + Grafana
   • Percona Monitoring and Management

3. 慢查询分析：

   SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2;

9.2 日常维护任务

1. 定期任务清单：

   • 检查磁盘空间
   • 验证备份可用性
   • 分析慢查询日志

2. 优化建议：

   ANALYZE TABLE tablename; OPTIMIZE TABLE tablename;

3. 安全审计：

   SELECT * FROM mysql.user; SHOW GRANTS FOR 'username'@'host';

10. 故障恢复策略

10.1 数据恢复方案

1. 时间点恢复：

   mysqlbinlog binlog.000123 | mysql -u root -p

2. 表空间恢复：

   ALTER TABLE tablename IMPORT TABLESPACE;

3. 崩溃恢复：

   mysqld --innodb_force_recovery=6

10.2 高可用架构

1. 主从复制：

   • 异步复制
   • 半同步复制

2. 组复制：

   [mysqld] plugin_load_add='group_replication.so' group_replication_start_on_boot=off

3. 集群方案：

   • MySQL InnoDB Cluster
   • Galera Cluster

11. 开发最佳实践

11.1 数据库设计规范

1. 命名约定：

   • 表名：小写字母，下划线分隔
   • 字段名：避免使用关键字

2. 数据类型选择：

   • 整数：根据范围选择TINYINT/SMALLINT/INT/BIGINT
   • 字符串：VARCHAR vs CHAR
   • 时间：DATETIME vs TIMESTAMP

3. 索引策略：

   • 最左前缀原则
   • 覆盖索引优化

11.2 SQL编写规范

1. 查询优化：

   -- 避免 SELECT * FROM users; -- 推荐 SELECT id, name FROM users WHERE status=1;

2. 事务处理：

   START TRANSACTION; -- 业务操作 COMMIT;

3. 预处理语句：

   cursor.execute("SELECT * FROM users WHERE id=%s", (user_id,))

12. 扩展学习资源

12.1 官方文档重点

1. 必读章节：

   • 安装与升级指南
   • 优化手册
   • 安全指南

2. 实用参考：

   • 数据类型参考
   • 函数和操作符
   • SQL语句语法

12.2 推荐书籍

1. 入门级：

   • 《MySQL必知必会》
   • 《高性能MySQL(基础篇)》

2. 进阶级：

   • 《高性能MySQL(完整版)》
   • 《MySQL技术内幕》

3. 专家级：

   • 《MySQL运维内参》
   • 《数据库系统实现》

13. 实际案例分享

13.1 电商系统配置案例

1. 硬件配置：

   • 16核CPU
   • 64GB内存
   • SSD存储

2. 参数优化：

   innodb_buffer_pool_size = 48G innodb_io_capacity = 2000

3. 分表策略：

   • 按用户ID哈希分表
   • 热点数据单独处理

13.2 物联网数据处理

1. 时序数据方案：

   • 压缩表存储
   • 分区表按时间范围

2. 批量插入优化：

   INSERT INTO readings VALUES (...),(...),(...);

3. 归档策略：

   • 热数据：MySQL
   • 温数据：归档表
   • 冷数据：对象存储

14. 未来版本特性

14.1 MySQL 8.0新功能

1. 窗口函数：

   SELECT name, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rank FROM employees;

2. CTE表达式：

   WITH dept_stats AS ( SELECT dept_id, AVG(salary) avg_sal FROM employees GROUP BY dept_id ) SELECT * FROM dept_stats;

3. JSON增强：

   SELECT JSON_EXTRACT(data, '$.price') FROM products;

14.2 技术演进趋势

1. 云原生支持：

   • Kubernetes Operator
   • 自动扩展

2. 内存优化：

   • 更快的缓存算法
   • 列式存储支持

3. AI集成：

   • 查询优化器改进
   • 自动索引建议

15. 替代方案对比

15.1 关系型数据库对比

15.2 使用场景建议

1. 选择MySQL：

   • Web应用
   • 中小型系统
   • 需要快速上手

2. 考虑其他方案：

   • 复杂分析：PostgreSQL
   • 企业级特性：Oracle
   • 超大规模：分布式数据库

16. 社区支持资源

16.1 问题解决渠道

1. 官方论坛：

   • MySQL官方社区
   • Stack Overflow

2. 中文资源：

   • 阿里云开发者社区
   • 腾讯云数据库专栏

3. 会议活动：

   • MySQL Conference
   • 各云厂商技术峰会

16.2 贡献指南

1. Bug报告：

   • 详细描述问题现象
   • 提供复现步骤
   • 包含环境信息

2. 代码贡献：

   • 遵循编码规范
   • 包含测试用例
   • 文档更新

3. 文档改进：

   • 修正错误
   • 补充示例
   • 多语言翻译

17. 认证与职业发展

17.1 MySQL认证体系

1. Oracle认证：

   • MySQL 5.7 Database Administrator
   • MySQL 8.0 Database Administrator

2. 考试要点：

   • 安装与配置
   • 安全管理
   • 性能优化

3. 备考资源：

   • 官方认证指南
   • 模拟试题
   • 实操练习

17.2 职业路径建议

1. DBA方向：

   • 初级：日常维护
   • 中级：性能优化
   • 高级：架构设计

2. 开发方向：

   • 数据库开发
   • 数据架构师
   • 全栈工程师

3. 云方向：

   • 云数据库专家
   • 解决方案架构师

18. 安全合规要求

18.1 数据保护措施

1. 加密方案：

   • 传输层SSL
   • 静态数据加密
   • 字段级加密

2. 访问控制：

   • 最小权限原则
   • 定期权限审计
   • 多因素认证

3. 审计日志：

   • 记录所有管理操作
   • 敏感操作双重确认
   • 日志集中管理

18.2 合规标准

1. 通用标准：

   • GDPR
   • PCI DSS
   • HIPAA

2. 配置检查：

   SELECT * FROM sys.ps_check_schema;

3. 安全工具：

   • MySQL Enterprise Audit
   • OpenSCAP基线检查

19. 容器化部署

19.1 Docker部署方案

1. 官方镜像使用：

   docker run --name mysql -e MYSQL_ROOT_PASSWORD=password -d mysql:8.0

2. 自定义配置：

   docker run -v /my/custom:/etc/mysql/conf.d ...

3. 持久化存储：

   docker run -v /my/datadir:/var/lib/mysql ...

19.2 Kubernetes集成

1. StatefulSet示例：

   apiVersion: apps/v1 kind: StatefulSet metadata: name: mysql spec: serviceName: mysql replicas: 3

2. 高可用方案：

   • 使用Operator管理
   • 主从拓扑配置

3. 备份恢复：

   • 定时快照
   • 逻辑备份到对象存储

20. 性能基准测试

20.1 测试方法论

1. 工具选择：

   • sysbench
   • mysqlslap
   • tpcc-mysql

2. 测试指标：

   • TPS(每秒事务数)
   • QPS(每秒查询数)
   • 响应时间

3. 测试场景：

   • 只读测试
   • 读写混合
   • 高并发压力

20.2 优化效果验证

1. 前后对比：

   • 配置变更前后性能差异
   • 索引添加效果验证

2. 长期监控：

   • 建立性能基线
   • 定期回归测试

3. 报告生成：

   sysbench --db-driver=mysql oltp_read_write run

21. 备份恢复进阶

21.1 物理备份方案

1. Percona XtraBackup：

   xtrabackup --backup --target-dir=/backup/

2. MySQL Enterprise Backup：

   • 热备份方案
   • 增量备份支持

3. LVM快照：

   lvcreate -L1G -s -n dbbackup /dev/vg/mysql

21.2 逻辑备份技巧

1. 选择性备份：

   mysqldump -u root -p --ignore-table=db.logs db > backup.sql

2. 并行导出：

   mydumper -u root -p -o /backup

3. 压缩备份：

   mysqldump -u root -p db | gzip > backup.sql.gz

22. 分区与分片

22.1 分区策略

1. 范围分区：

   CREATE TABLE logs ( id INT, log_date DATE ) PARTITION BY RANGE (YEAR(log_date)) ( PARTITION p0 VALUES LESS THAN (2020), PARTITION p1 VALUES LESS THAN (2021) );

2. 哈希分区：

   CREATE TABLE users ( id INT, name VARCHAR(30) ) PARTITION BY HASH(id) PARTITIONS 4;

3. 列表分区：

   CREATE TABLE sales ( region VARCHAR(10), amount DECIMAL(10,2) ) PARTITION BY LIST COLUMNS(region) ( PARTITION p_east VALUES IN ('NY', 'NJ'), PARTITION p_west VALUES IN ('CA', 'OR') );

22.2 分片方案

1. 应用层分片：

   • 根据业务规则路由
   • 需要应用代码支持

2. 中间件方案：

   • MyCAT
   • ShardingSphere

3. 云数据库方案：

   • Vitess
   • PolarDB-X

23. 数据迁移策略

23.1 同构迁移

1. mysqldump方案：

   # 源库导出 mysqldump -u root -p --single-transaction db > db.sql # 目标库导入 mysql -u root -p newdb < db.sql

2. CSV导出导入：

   -- 导出 SELECT * INTO OUTFILE '/tmp/data.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' FROM table; -- 导入 LOAD DATA INFILE '/tmp/data.csv' INTO TABLE table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

23.2 异构迁移

1. ETL工具：

   • Talend
   • Apache NiFi

2. 自定义脚本：

   • 使用Python连接不同数据库
   • 分批处理大数据量

3. 云服务方案：

   • AWS DMS
   • Alibaba Cloud DTS

24. 数据同步方案

24.1 实时同步

1. Binlog解析：

   • Canal
   • Maxwell

2. Debezium方案：

   docker run -it --name mysql-connector \ -e BOOTSTRAP_SERVERS=kafka:9092 \ -e GROUP_ID=1 \ -e CONFIG_STORAGE_TOPIC=my_connect_configs \ -e OFFSET_STORAGE_TOPIC=my_connect_offsets \ -e STATUS_STORAGE_TOPIC=my_connect_statuses \ -e CONNECT_KEY_CONVERTER=org.apache.kafka.connect.json.JsonConverter \ -e CONNECT_VALUE_CONVERTER=org.apache.kafka.connect.json.JsonConverter \ -e CONNECT_INTERNAL_KEY_CONVERTER=org.apache.kafka.connect.json.JsonConverter \ -e CONNECT_INTERNAL_VALUE_CONVERTER=org.apache.kafka.connect.json.JsonConverter \ -e CONNECT_REST_ADVERTISED_HOST_NAME=connect \ --link zookeeper:zookeeper \ --link kafka:kafka \ --link mysql:mysql \ -p 8083:8083 \ debezium/connect:1.2

24.2 批量同步

1. Sqoop方案：

   sqoop import \ --connect jdbc:mysql://mysql.example.com/db \ --username user -P \ --table customers \ --target-dir /data/customers

2. DataX配置：

   { "job": { "content": [{ "reader": { "name": "mysqlreader", "parameter": { "username": "root", "password": "password", "column": ["id", "name"], "connection": [{ "table": ["users"], "jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/db"] }] } }, "writer": {...} }] } }

25. 数据仓库集成

25.1 与Hadoop集成

1. Sqoop导入：

   sqoop import \ --connect jdbc:mysql://localhost/db \ --table sales \ --warehouse-dir /user/hive/warehouse

2. Hive外部表：

   CREATE EXTERNAL TABLE mysql_sales ( id INT, amount DOUBLE ) STORED BY 'org.apache.hadoop.hive.mysql.storagehandler.MySQLStorageHandler' TBLPROPERTIES ( "mysql.host" = "localhost", "mysql.database" = "db", "mysql.table" = "sales" );

25.2 与数据湖集成

1. Delta Lake同步：

   # 使用Spark读取MySQL df = spark.read \ .format("jdbc") \ .option("url", "jdbc:mysql://localhost:3306/db") \ .option("dbtable", "sales") \ .option("user", "root") \ .option("password", "password") \ .load() # 写入Delta Lake df.write.format("delta").save("/delta/sales")

2. 数据湖house架构：

   • MySQL作为OLTP系统
   • 定期同步到数据湖
   • 使用Presto/Trino联合查询

26. 机器学习集成

26.1 数据准备

1. Python连接MySQL：

   import pandas as pd from sqlalchemy import create_engine engine = create_engine('mysql+pymysql://user:password@localhost/db') df = pd.read_sql('SELECT * FROM customers', engine)

2. 特征工程：

   # 直接从SQL中计算特征 query = """ SELECT customer_id, COUNT(*) as purchase_count, AVG(amount) as avg_spend FROM transactions GROUP BY customer_id """ features = pd.read_sql(query, engine)

26.2 模型应用

1. 预测结果写回：

   predictions.to_sql('customer_churn_predictions', engine, if_exists='replace')

2. MySQL机器学习插件：

   INSTALL PLUGIN ml SONAME 'ml.so'; CREATE TABLE house_prices ( size INT, bedrooms INT, price DOUBLE ); -- 训练模型 CALL ml_train('house_prices', 'price', 'linear_regression', @model); -- 使用模型预测 CALL ml_predict(@model, JSON_OBJECT('size', 2000, 'bedrooms', 3), @result);

27. 地理空间数据处理

27.1 空间数据类型

1. 基础类型：

   • POINT
   • LINESTRING
   • POLYGON

2. 创建空间表：

   CREATE TABLE locations ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), position POINT SRID 4326, SPATIAL INDEX(position) );

3. 插入空间数据：

   INSERT INTO locations (name, position) VALUES ('Office', ST_GeomFromText('POINT(116.404 39.915)'));

27.2 空间查询

1. 距离查询：

   SELECT name FROM locations WHERE ST_Distance_Sphere(position, ST_GeomFromText('POINT(116.404 39.915)')) < 1000;

2. 包含查询：

   SELECT name FROM locations WHERE ST_Within(position, ST_GeomFromText('POLYGON((...))'));

3. 空间连接：

   SELECT a.name, b.name FROM locations a, regions b WHERE ST_Within(a.position, b.geometry);

28. 全文检索实现

28.1 全文索引配置

1. 创建全文索引：

   CREATE TABLE articles ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), content TEXT, FULLTEXT(title, content) ) ENGINE=InnoDB;

2. 自然语言搜索：

   SELECT * FROM articles WHERE MATCH(title, content) AGAINST('数据库' IN NATURAL LANGUAGE MODE);

3. 布尔搜索：

   SELECT * FROM articles WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

28.2 高级搜索功能

1. 相关性排序：

   SELECT id, title, MATCH(title, content) AGAINST('数据库') AS score FROM articles WHERE MATCH(title, content) AGAINST('数据库') ORDER BY score DESC;

2. 同义词扩展：

   -- 需要配置同义词文件 SELECT * FROM articles WHERE MATCH(title, content) AGAINST('DB' WITH QUERY EXPANSION);

3. N-gram分词：

   [mysqld] ngram_token_size=2

29. 时序数据处理

29.1 时序表设计

1. 分区表方案：

   CREATE TABLE metrics ( ts TIMESTAMP, device_id INT, value FLOAT, PRIMARY KEY (device_id, ts) ) PARTITION BY RANGE (UNIX_TIMESTAMP(ts)) ( PARTITION p202301 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-01')), PARTITION p202302 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-01')) );

2. 压缩存储：

   ALTER TABLE metrics COMPRESSION="zlib";

3. 降采样查询：

   SELECT DATE_FORMAT(ts, '%Y-%m-%d %H:00:00') AS hour, AVG(value) AS avg_value FROM metrics GROUP BY hour;

29.2 时序函数

1. 窗口函数：

   SELECT ts, value, AVG(value) OVER (PARTITION BY device_id ORDER BY ts RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW) AS hourly_avg FROM metrics;

2. 时间序列补全：

   WITH time_series AS ( SELECT '2023-01-01 00:00:00' + INTERVAL seq HOUR AS ts FROM ( SELECT 0 AS seq UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) numbers ) SELECT t.ts, COALESCE(m.value, 0) AS value FROM time_series t LEFT JOIN metrics m ON t.ts = m.ts;

30. 物联网场景实践

30.1 设备数据存储

1. 表结构设计：

   CREATE TABLE device_data ( device_id VARCHAR(32), timestamp TIMESTAMP(6), metric_name VARCHAR(64), metric_value DOUBLE, PRIMARY KEY (device_id, timestamp, metric_name) ) PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp)) ( PARTITION p202301 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-01')) );

2. 批量插入优化：

   INSERT INTO device_data VALUES ('device1', '2023-01-01 00:00:00', 'temp', 23.5), ('device1', '2023-01-01 00:01:00', 'temp', 23.6), ('device2', '2023-01-01 00:00:00', 'humidity', 45.0);

30.2 实时数据处理

1. 物化视图：

   CREATE TABLE device_stats ( device_id VARCHAR(32), day DATE, min_temp DOUBLE, max_temp DOUBLE, PRIMARY KEY (device_id, day) ); -- 定时刷新 INSERT INTO device_stats SELECT device_id, DATE(timestamp), MIN(CASE WHEN metric_name = 'temp' THEN metric_value END), MAX(CASE WHEN metric_name = 'temp' THEN metric_value END) FROM device_data WHERE timestamp >= CURRENT_DATE GROUP BY device_id, DATE(timestamp) ON DUPLICATE KEY UPDATE min_temp = VALUES(min_temp), max_temp = VALUES(max_temp);

2. 事件触发：

   CREATE TRIGGER check_alert AFTER INSERT ON device_data FOR EACH ROW BEGIN IF NEW.metric_name = 'temp' AND NEW.metric_value > 30 THEN INSERT INTO alerts(device_id, alert_time, alert_type) VALUES (NEW.device_id, NEW.timestamp, 'high_temp'); END IF; END;

31. 金融级应用实践

31.1 事务处理优化

1. 隔离级别选择：

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2. 死锁处理：

   [mysqld] innodb_deadlock_detect=ON innodb_lock_wait_timeout=50

3. 大事务拆分：

   • 分批处理
   • 应用层补偿机制

31.2 数据一致性保障

1. XA事务：

   XA START 'transaction_id'; -- SQL操作 XA END 'transaction_id'; XA PREPARE 'transaction_id'; XA COMMIT 'transaction_id';

2. 双写校验：

   -- 关键操作记录校验值 INSERT INTO transactions VALUES (..., MD5(CONCAT(account_from, account_to, amount)));

3. 对账机制：

   • 定时全量核对
   • 差异自动修复

32. 游戏行业实践

32.1 玩家数据存储

1. JSON字段应用：

   CREATE TABLE player_data ( player_id BIGINT PRIMARY KEY, basic_info JSON, inventory JSON, achievements JSON, INDEX ((CAST(basic_info->'$.level' AS UNSIGNED))) );

2. 在线状态管理：

   CREATE TABLE online_players ( player_id BIGINT PRIMARY KEY, login_time TIMESTAMP, last