1. MySQL增删改查基础概念与场景解析MySQL作为最流行的开源关系型数据库其核心操作可以概括为CRUDCreate, Read, Update, Delete。在实际开发中约80%的数据库操作都围绕着这四项基本功能展开。不同于教科书式的概念讲解我们先从一个真实场景切入假设你正在开发一个电商平台的商品管理系统。当新品上架时需要在products表中插入记录Create用户浏览商品时需要查询商品信息Read价格调整时要更新数据Update商品下架时则要删除记录Delete。这些操作看似简单但每个环节都藏着新手容易踩的坑。注意虽然增删改查语法简单但在生产环境中需要考虑事务完整性、并发控制和性能优化这些我们会在后续章节详细展开。2. 环境准备与基础配置2.1 MySQL安装最佳实践根据2023年Stack Overflow开发者调查MySQL在数据库使用率中排名第二仅次于PostgreSQL。对于初学者推荐使用MySQL Community Server 8.0版本它在Windows/macOS/Linux上都有完善的安装包。Windows用户常遇到的坑是安装时默认字符集是latin1应该手动选择utf8mb4服务端口3306可能被占用安装前建议用netstat -ano检查忘记设置root密码虽然8.0后默认需要设置macOS用户通过Homebrew安装更高效brew install mysql brew services start mysql mysql_secure_installation2.2 基础表结构设计我们以用户管理系统为例创建第一个练习表CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;关键设计要点使用InnoDB引擎支持事务utf8mb4字符集支持emoji等特殊字符为常用查询字段添加索引设置自增主键3. 增删改查操作详解3.1 插入数据Create基础语法INSERT INTO users (username, email) VALUES (dev_user, devexample.com);批量插入的高效写法INSERT INTO users (username, email) VALUES (user1, user1test.com), (user2, user2test.com), (user3, user3test.com);实战技巧使用INSERT IGNORE可以跳过重复记录ON DUPLICATE KEY UPDATE则可以实现存在即更新的效果。3.2 查询数据Read基础查询SELECT * FROM users WHERE id 1;复杂查询示例SELECT u.username, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.created_at 2023-01-01 GROUP BY u.id HAVING order_count 5 ORDER BY order_count DESC LIMIT 10;性能优化要点避免SELECT *只查询需要的字段合理使用索引EXPLAIN分析执行计划大数据量分页用WHERE id ? LIMIT ?替代LIMIT ?, ?3.3 更新数据Update基础更新UPDATE users SET email new_emailexample.com WHERE id 1;带条件的复杂更新UPDATE products SET stock stock - 1, updated_at NOW() WHERE id 100 AND stock 0;致命陷阱忘记加WHERE条件会导致全表更新建议开启--safe-updates模式。3.4 删除数据Delete基础删除DELETE FROM users WHERE id 1;软删除实践推荐ALTER TABLE users ADD COLUMN is_deleted TINYINT DEFAULT 0; -- 删除操作改为更新 UPDATE users SET is_deleted 1 WHERE id 1; -- 查询时排除已删除 SELECT * FROM users WHERE is_deleted 0;4. 高级技巧与性能优化4.1 事务处理实战转账场景的典型事务START TRANSACTION; UPDATE accounts SET balance balance - 100 WHERE user_id 1 AND balance 100; UPDATE accounts SET balance balance 100 WHERE user_id 2; COMMIT; -- 出错时执行 ROLLBACK4.2 索引优化策略常见索引失效场景对字段使用函数WHERE YEAR(create_time) 2023隐式类型转换WHERE phone 13800138000phone是varchar类型前导模糊查询WHERE name LIKE %张4.3 存储过程示例创建分页查询存储过程DELIMITER // CREATE PROCEDURE sp_user_pagination( IN page INT, IN page_size INT ) BEGIN DECLARE offset_val INT; SET offset_val (page - 1) * page_size; SELECT * FROM users WHERE is_deleted 0 ORDER BY id LIMIT offset_val, page_size; END // DELIMITER ;5. 常见问题排查5.1 连接数爆满问题现象出现Too many connections错误 解决方案-- 查看当前连接数 SHOW STATUS LIKE Threads_connected; -- 临时增加最大连接数 SET GLOBAL max_connections 200; -- 长期方案优化连接池配置 -- 在应用端设置合理的连接池大小如HikariCP5.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 # 使用pt-query-digest pt-query-digest /var/log/mysql/mysql-slow.log5.3 数据备份策略物理备份与逻辑备份对比类型工具优点缺点逻辑备份mysqldump可单表恢复兼容性好大库恢复慢物理备份Percona XtraBackup速度快热备份需要更多存储空间云数据库AWS RDS Snapshot全托管自动化厂商锁定备份脚本示例# 每日全量备份 mysqldump -uroot -p --single-transaction --routines \ --triggers --all-databases full_backup_$(date %F).sql # 配合binlog实现增量备份 mysqlbinlog --start-datetime2023-08-01 00:00:00 \ /var/lib/mysql/mysql-bin.000123 incr_backup.sql6. 现代开发中的增删改查实践6.1 ORM框架的使用以Spring Boot MyBatis为例// Mapper接口 public interface UserMapper { Insert(INSERT INTO users(username, email) VALUES(#{username}, #{email})) Options(useGeneratedKeys true, keyProperty id) int insert(User user); Select(SELECT * FROM users WHERE id #{id}) User getById(Long id); Update(UPDATE users SET email#{email} WHERE id#{id}) int updateEmail(Param(id) Long id, Param(email) String email); Delete(DELETE FROM users WHERE id #{id}) int deleteById(Long id); }6.2 分库分表场景当单表数据超过500万行时考虑分表策略水平分表按ID范围或哈希分到多个结构相同的表垂直分表将大字段拆分到单独的表使用ShardingSphere等中间件6.3 与NoSQL的协作模式混合架构示例MySQL存储核心交易数据强一致性Redis缓存热点数据高性能读取Elasticsearch实现复杂搜索缓存更新策略// 伪代码示例 public User getUserWithCache(Long id) { // 1. 先查缓存 User user redis.get(user: id); if (user ! null) { return user; } // 2. 查数据库 user userMapper.selectById(id); if (user ! null) { // 3. 写入缓存 redis.setex(user: id, 3600, user); } return user; }7. 安全注意事项7.1 SQL注入防御错误示范// 拼接SQL语句危险 String sql SELECT * FROM users WHERE username username ;正确做法使用预编译语句PreparedStatement stmt conn.prepareStatement( SELECT * FROM users WHERE username ?); stmt.setString(1, username);MyBatis中使用#{}而非${}select idgetByUsername resultTypeUser SELECT * FROM users WHERE username #{username} /select7.2 权限管理原则最小权限示例-- 创建应用账号 CREATE USER app_user192.168.1.% IDENTIFIED BY complex_password; -- 只授予必要权限 GRANT SELECT, INSERT, UPDATE ON db_name.users TO app_user192.168.1.%; GRANT SELECT ON db_name.products TO app_user192.168.1.%;7.3 敏感数据保护加密存储方案-- 使用AES_ENCRYPT函数 INSERT INTO users (username, password) VALUES (admin, AES_ENCRYPT(mypassword, encryption_key)); -- 查询时解密 SELECT username, AES_DECRYPT(password, encryption_key) FROM users;8. 监控与性能调优8.1 关键指标监控必备监控项QPS/TPSSHOW GLOBAL STATUS LIKE Questions连接数SHOW STATUS LIKE Threads_%缓存命中率SHOW STATUS LIKE Innodb_buffer_pool_%慢查询比例SHOW STATUS LIKE Slow_queries8.2 EXPLAIN深度解析分析示例EXPLAIN FORMATJSON SELECT u.* FROM users u JOIN orders o ON u.id o.user_id WHERE o.amount 100;关键指标解读type列从优到差 system const eq_ref ref range index ALLpossible_keys可能使用的索引rows预估扫描行数ExtraUsing filesort/Using temporary需要优化8.3 参数调优指南关键参数调整# InnoDB缓冲池建议物理内存的50-70% innodb_buffer_pool_size 4G # 连接数相关 max_connections 200 thread_cache_size 50 # 日志配置 innodb_log_file_size 256M sync_binlog 19. 版本特性与升级策略9.1 MySQL 8.0新特性实用新功能窗口函数SELECT username, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank FROM employees;通用表表达式(CTE)WITH dept_stats AS ( SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id ) SELECT * FROM dept_stats WHERE avg_salary 10000;9.2 升级注意事项从5.7升级到8.0的步骤备份所有数据检查兼容性mysqlcheck -u root -p --all-databases --check-upgrade安装新版本但不启动运行升级程序mysql_upgrade -u root -p验证SELECT version;10. 开发实战经验分享10.1 分页查询优化传统分页问题-- 越往后越慢 SELECT * FROM large_table LIMIT 1000000, 10;优化方案使用索引覆盖SELECT * FROM large_table WHERE id (SELECT id FROM large_table ORDER BY id LIMIT 1000000, 1) LIMIT 10;记录上次查询的最大ID-- 第一页 SELECT * FROM large_table ORDER BY id LIMIT 10; -- 后续页假设上页最后ID是12345 SELECT * FROM large_table WHERE id 12345 ORDER BY id LIMIT 10;10.2 批量操作技巧批量插入优化对比-- 低效方式1000次网络往返 for(int i0; i1000; i) { INSERT INTO test VALUES(i); } -- 高效方式1次网络往返 INSERT INTO test VALUES (1),(2),(3),...,(1000);使用LOAD DATA导入LOAD DATA INFILE /tmp/data.csv INTO TABLE test FIELDS TERMINATED BY , LINES TERMINATED BY \n;10.3 在线DDL实践传统ALTER TABLE会锁表使用Online DDL-- 8.0默认支持instant算法添加列 ALTER TABLE users ADD COLUMN last_login DATETIME, ALGORITHMINSTANT; -- 大表修改使用inplace算法 ALTER TABLE big_table ADD INDEX idx_name (name), ALGORITHMINPLACE, LOCKNONE;工具推荐pt-online-schema-changegh-ost11. 不同场景下的设计模式11.1 电商系统设计典型表结构-- 商品表 CREATE TABLE products ( id BIGINT PRIMARY KEY, name VARCHAR(200), price DECIMAL(10,2), stock INT, -- 其他字段... FULLTEXT INDEX idx_name_desc (name, description) ); -- 订单表分表示例 CREATE TABLE orders_2023 ( id BIGINT PRIMARY KEY, user_id INT, amount DECIMAL(12,2), status TINYINT, INDEX idx_user (user_id), INDEX idx_ctime (create_time) ) PARTITION BY RANGE (YEAR(create_time)) ( PARTITION p2023 VALUES LESS THAN (2024), PARTITION pmax VALUES LESS THAN MAXVALUE );11.2 社交网络设计关系型设计-- 用户关系表 CREATE TABLE user_relations ( user_id INT, friend_id INT, relation_type ENUM(friend, follow), PRIMARY KEY (user_id, friend_id), INDEX reverse_idx (friend_id, user_id) ); -- 动态信息表 CREATE TABLE feeds ( id BIGINT PRIMARY KEY, user_id INT, content TEXT, location POINT SRID 4326, -- 地理位置 SPATIAL INDEX(location), FULLTEXT INDEX(content) );11.3 物联网数据存储时序数据处理-- 设备数据表按时间分区 CREATE TABLE device_data ( device_id INT, metric_time DATETIME(6), -- 微秒精度 metric_name VARCHAR(50), metric_value DOUBLE, PRIMARY KEY (device_id, metric_time, metric_name) ) PARTITION BY RANGE (TO_DAYS(metric_time)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS(2023-02-01)), PARTITION p202302 VALUES LESS THAN (TO_DAYS(2023-03-01)), -- 其他月份... );12. 故障恢复与数据修复12.1 误删除数据恢复使用binlog恢复步骤定位操作时间点mysqlbinlog --start-datetime2023-08-01 14:00:00 \ --stop-datetime2023-08-01 15:00:00 \ /var/lib/mysql/binlog.000123 /tmp/operations.sql编辑SQL文件删除错误语句重放binlogmysql -uroot -p /tmp/operations.sql12.2 数据不一致修复使用pt-table-checksum检测pt-table-checksum --replicatetest.checksums \ --ignore-databasesmysql,sys \ hlocalhost,uroot,ppassword使用pt-table-sync修复pt-table-sync --replicatetest.checksums \ hmaster,uroot,ppassword \ hslave,uroot,ppassword \ --print12.3 主从同步故障处理常见问题排查检查复制状态SHOW SLAVE STATUS\G跳过错误谨慎使用STOP SLAVE; SET GLOBAL sql_slave_skip_counter 1; START SLAVE;重建复制# 在主库 mysqldump --master-data2 --single-transaction \ -uroot -p db_name db_dump.sql # 在从库 mysql -uroot -p db_name db_dump.sql CHANGE MASTER TO ...; START SLAVE;13. 云数据库实践13.1 AWS RDS最佳实践连接池配置// HikariCP配置示例 HikariConfig config new HikariConfig(); config.setJdbcUrl(jdbc:mysql://rds-endpoint:3306/db); config.setUsername(app_user); config.setPassword(password); config.setMaximumPoolSize(20); config.setConnectionTimeout(30000); config.addDataSourceProperty(cachePrepStmts, true); config.addDataSourceProperty(prepStmtCacheSize, 250); config.addDataSourceProperty(prepStmtCacheSqlLimit, 2048);13.2 阿里云RDS性能优化只读实例使用// Spring Boot多数据源配置 Bean Primary ConfigurationProperties(spring.datasource.master) public DataSource masterDataSource() { return DataSourceBuilder.create().build(); } Bean ConfigurationProperties(spring.datasource.slave) public DataSource slaveDataSource() { return DataSourceBuilder.create().build(); }13.3 跨地域同步方案使用AWS DMS服务创建复制实例配置源和目标端点创建并启动复制任务监控CDC变更数据捕获14. 未来趋势与扩展阅读14.1 MySQL与NewSQLTiDB兼容MySQL协议的优势水平扩展能力强一致性分布式事务实时HTAP能力迁移注意事项语法兼容性检查事务隔离级别调整分布式JOIN优化14.2 向量搜索扩展MySQL 8.0的向量搜索-- 添加向量列 ALTER TABLE products ADD COLUMN embedding VECTOR(1536); -- 向量相似度搜索 SELECT id, name, VECTOR_DISTANCE(embedding, CAST([0.1, 0.2, ..., 0.5] AS VECTOR(1536)) ) AS distance FROM products ORDER BY distance LIMIT 10;14.3 推荐学习路径进阶学习资源书籍《高性能MySQL》第4版《MySQL技术内幕InnoDB存储引擎》在线课程MySQL官方认证课程极客时间《MySQL实战45讲》社区Percona博客PlanetMySQL阿里云数据库技术月刊 1. MySQL增删改查基础概念与场景解析MySQL作为最流行的开源关系型数据库其核心操作可以概括为CRUDCreate, Read, Update, Delete。在实际开发中约80%的数据库操作都围绕着这四项基本功能展开。不同于教科书式的概念讲解我们先从一个真实场景切入假设你正在开发一个电商平台的商品管理系统。当新品上架时需要在products表中插入记录Create用户浏览商品时需要查询商品信息Read价格调整时要更新数据Update商品下架时则要删除记录Delete。这些操作看似简单但每个环节都藏着新手容易踩的坑。注意虽然增删改查语法简单但在生产环境中需要考虑事务完整性、并发控制和性能优化这些我们会在后续章节详细展开。2. 环境准备与基础配置2.1 MySQL安装最佳实践根据2023年Stack Overflow开发者调查MySQL在数据库使用率中排名第二仅次于PostgreSQL。对于初学者推荐使用MySQL Community Server 8.0版本它在Windows/macOS/Linux上都有完善的安装包。Windows用户常遇到的坑是安装时默认字符集是latin1应该手动选择utf8mb4服务端口3306可能被占用安装前建议用netstat -ano检查忘记设置root密码虽然8.0后默认需要设置macOS用户通过Homebrew安装更高效brew install mysql brew services start mysql mysql_secure_installation2.2 基础表结构设计我们以用户管理系统为例创建第一个练习表CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;关键设计要点使用InnoDB引擎支持事务utf8mb4字符集支持emoji等特殊字符为常用查询字段添加索引设置自增主键3. 增删改查操作详解3.1 插入数据Create基础语法INSERT INTO users (username, email) VALUES (dev_user, devexample.com);批量插入的高效写法INSERT INTO users (username, email) VALUES (user1, user1test.com), (user2, user2test.com), (user3, user3test.com);实战技巧使用INSERT IGNORE可以跳过重复记录ON DUPLICATE KEY UPDATE则可以实现存在即更新的效果。3.2 查询数据Read基础查询SELECT * FROM users WHERE id 1;复杂查询示例SELECT u.username, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.created_at 2023-01-01 GROUP BY u.id HAVING order_count 5 ORDER BY order_count DESC LIMIT 10;性能优化要点避免SELECT *只查询需要的字段合理使用索引EXPLAIN分析执行计划大数据量分页用WHERE id ? LIMIT ?替代LIMIT ?, ?3.3 更新数据Update基础更新UPDATE users SET email new_emailexample.com WHERE id 1;带条件的复杂更新UPDATE products SET stock stock - 1, updated_at NOW() WHERE id 100 AND stock 0;致命陷阱忘记加WHERE条件会导致全表更新建议开启--safe-updates模式。3.4 删除数据Delete基础删除DELETE FROM users WHERE id 1;软删除实践推荐ALTER TABLE users ADD COLUMN is_deleted TINYINT DEFAULT 0; -- 删除操作改为更新 UPDATE users SET is_deleted 1 WHERE id 1; -- 查询时排除已删除 SELECT * FROM users WHERE is_deleted 0;4. 高级技巧与性能优化4.1 事务处理实战转账场景的典型事务START TRANSACTION; UPDATE accounts SET balance balance - 100 WHERE user_id 1 AND balance 100; UPDATE accounts SET balance balance 100 WHERE user_id 2; COMMIT; -- 出错时执行 ROLLBACK4.2 索引优化策略常见索引失效场景对字段使用函数WHERE YEAR(create_time) 2023隐式类型转换WHERE phone 13800138000phone是varchar类型前导模糊查询WHERE name LIKE %张4.3 存储过程示例创建分页查询存储过程DELIMITER // CREATE PROCEDURE sp_user_pagination( IN page INT, IN page_size INT ) BEGIN DECLARE offset_val INT; SET offset_val (page - 1) * page_size; SELECT * FROM users WHERE is_deleted 0 ORDER BY id LIMIT offset_val, page_size; END // DELIMITER ;5. 常见问题排查5.1 连接数爆满问题现象出现Too many connections错误 解决方案-- 查看当前连接数 SHOW STATUS LIKE Threads_connected; -- 临时增加最大连接数 SET GLOBAL max_connections 200; -- 长期方案优化连接池配置 -- 在应用端设置合理的连接池大小如HikariCP5.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 # 使用pt-query-digest pt-query-digest /var/log/mysql/mysql-slow.log5.3 数据备份策略物理备份与逻辑备份对比类型工具优点缺点逻辑备份mysqldump可单表恢复兼容性好大库恢复慢物理备份Percona XtraBackup速度快热备份需要更多存储空间云数据库AWS RDS Snapshot全托管自动化厂商锁定备份脚本示例# 每日全量备份 mysqldump -uroot -p --single-transaction --routines \ --triggers --all-databases full_backup_$(date %F).sql # 配合binlog实现增量备份 mysqlbinlog --start-datetime2023-08-01 00:00:00 \ /var/lib/mysql/mysql-bin.000123 incr_backup.sql6. 现代开发中的增删改查实践6.1 ORM框架的使用以Spring Boot MyBatis为例// Mapper接口 public interface UserMapper { Insert(INSERT INTO users(username, email) VALUES(#{username}, #{email})) Options(useGeneratedKeys true, keyProperty id) int insert(User user); Select(SELECT * FROM users WHERE id #{id}) User getById(Long id); Update(UPDATE users SET email#{email} WHERE id#{id}) int updateEmail(Param(id) Long id, Param(email) String email); Delete(DELETE FROM users WHERE id #{id}) int deleteById(Long id); }6.2 分库分表场景当单表数据超过500万行时考虑分表策略水平分表按ID范围或哈希分到多个结构相同的表垂直分表将大字段拆分到单独的表使用ShardingSphere等中间件6.3 与NoSQL的协作模式混合架构示例MySQL存储核心交易数据强一致性Redis缓存热点数据高性能读取Elasticsearch实现复杂搜索缓存更新策略// 伪代码示例 public User getUserWithCache(Long id) { // 1. 先查缓存 User user redis.get(user: id); if (user ! null) { return user; } // 2. 查数据库 user userMapper.selectById(id); if (user ! null) { // 3. 写入缓存 redis.setex(user: id, 3600, user); } return user; }7. 安全注意事项7.1 SQL注入防御错误示范// 拼接SQL语句危险 String sql SELECT * FROM users WHERE username username ;正确做法使用预编译语句PreparedStatement stmt conn.prepareStatement( SELECT * FROM users WHERE username ?); stmt.setString(1, username);MyBatis中使用#{}而非${}select idgetByUsername resultTypeUser SELECT * FROM users WHERE username #{username} /select7.2 权限管理原则最小权限示例-- 创建应用账号 CREATE USER app_user192.168.1.% IDENTIFIED BY complex_password; -- 只授予必要权限 GRANT SELECT, INSERT, UPDATE ON db_name.users TO app_user192.168.1.%; GRANT SELECT ON db_name.products TO app_user192.168.1.%;7.3 敏感数据保护加密存储方案-- 使用AES_ENCRYPT函数 INSERT INTO users (username, password) VALUES (admin, AES_ENCRYPT(mypassword, encryption_key)); -- 查询时解密 SELECT username, AES_DECRYPT(password, encryption_key) FROM users;8. 监控与性能调优8.1 关键指标监控必备监控项QPS/TPSSHOW GLOBAL STATUS LIKE Questions连接数SHOW STATUS LIKE Threads_%缓存命中率SHOW STATUS LIKE Innodb_buffer_pool_%慢查询比例SHOW STATUS LIKE Slow_queries8.2 EXPLAIN深度解析分析示例EXPLAIN FORMATJSON SELECT u.* FROM users u JOIN orders o ON u.id o.user_id WHERE o.amount 100;关键指标解读type列从优到差 system const eq_ref ref range index ALLpossible_keys可能使用的索引rows预估扫描行数ExtraUsing filesort/Using temporary需要优化8.3 参数调优指南关键参数调整# InnoDB缓冲池建议物理内存的50-70% innodb_buffer_pool_size 4G # 连接数相关 max_connections 200 thread_cache_size 50 # 日志配置 innodb_log_file_size 256M sync_binlog 19. 版本特性与升级策略9.1 MySQL 8.0新特性实用新功能窗口函数SELECT username, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank FROM employees;通用表表达式(CTE)WITH dept_stats AS ( SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id ) SELECT * FROM dept_stats WHERE avg_salary 10000;9.2 升级注意事项从5.7升级到8.0的步骤备份所有数据检查兼容性mysqlcheck -u root -p --all-databases --check-upgrade安装新版本但不启动运行升级程序mysql_upgrade -u root -p验证SELECT version;10. 开发实战经验分享10.1 分页查询优化传统分页问题-- 越往后越慢 SELECT * FROM large_table LIMIT 1000000, 10;优化方案使用索引覆盖SELECT * FROM large_table WHERE id (SELECT id FROM large_table ORDER BY id LIMIT 1000000, 1) LIMIT 10;记录上次查询的最大ID-- 第一页 SELECT * FROM large_table ORDER BY id LIMIT 10; -- 后续页假设上页最后ID是12345 SELECT * FROM large_table WHERE id 12345 ORDER BY id LIMIT 10;10.2 批量操作技巧批量插入优化对比-- 低效方式1000次网络往返 for(int i0; i1000; i) { INSERT INTO test VALUES(i); } -- 高效方式1次网络往返 INSERT INTO test VALUES (1),(2),(3),...,(1000);使用LOAD DATA导入LOAD DATA INFILE /tmp/data.csv INTO TABLE test FIELDS TERMINATED BY , LINES TERMINATED BY \n;10.3 在线DDL实践传统ALTER TABLE会锁表使用Online DDL-- 8.0默认支持instant算法添加列 ALTER TABLE users ADD COLUMN last_login DATETIME, ALGORITHMINSTANT; -- 大表修改使用inplace算法 ALTER TABLE big_table ADD INDEX idx_name (name), ALGORITHMINPLACE, LOCKNONE;工具推荐pt-online-schema-changegh-ost11. 不同场景下的设计模式11.1 电商系统设计典型表结构-- 商品表 CREATE TABLE products ( id BIGINT PRIMARY KEY, name VARCHAR(200), price DECIMAL(10,2), stock INT, -- 其他字段... FULLTEXT INDEX idx_name_desc (name, description) ); -- 订单表分表示例 CREATE TABLE orders_2023 ( id BIGINT PRIMARY KEY, user_id INT, amount DECIMAL(12,2), status TINYINT, INDEX idx_user (user_id), INDEX idx_ctime (create_time) ) PARTITION BY RANGE (YEAR(create_time)) ( PARTITION p2023 VALUES LESS THAN (2024), PARTITION pmax VALUES LESS THAN MAXVALUE );11.2 社交网络设计关系型设计-- 用户关系表 CREATE TABLE user_relations ( user_id INT, friend_id INT, relation_type ENUM(friend, follow), PRIMARY KEY (user_id, friend_id), INDEX reverse_idx (friend_id, user_id) ); -- 动态信息表 CREATE TABLE feeds ( id BIGINT PRIMARY KEY, user_id INT, content TEXT, location POINT SRID 4326, -- 地理位置 SPATIAL INDEX(location), FULLTEXT INDEX(content) );11.3 物联网数据存储时序数据处理-- 设备数据表按时间分区 CREATE TABLE device_data ( device_id INT, metric_time DATETIME(6), -- 微秒精度 metric_name VARCHAR(50), metric_value DOUBLE, PRIMARY KEY (device_id, metric_time, metric_name) ) PARTITION BY RANGE (TO_DAYS(metric_time)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS(2023-02-01)), PARTITION p202302 VALUES LESS THAN (TO_DAYS(2023-03-01)), -- 其他月份... );12. 故障恢复与数据修复12.1 误删除数据恢复使用binlog恢复步骤定位操作时间点mysqlbinlog --start-datetime2023-08-01 14:00:00 \ --stop-datetime2023-08-01 15:00:00 \ /var/lib/mysql/binlog.000123 /tmp/operations.sql编辑SQL文件删除错误语句重放binlogmysql -uroot -p /tmp/operations.sql12.2 数据不一致修复使用pt-table-checksum检测pt-table-checksum --replicatetest.checksums \ --ignore-databasesmysql,sys \ hlocalhost,uroot,ppassword使用pt-table-sync修复pt-table-sync --replicatetest.checksums \ hmaster,uroot,ppassword \ hslave,uroot,ppassword \ --print12.3 主从同步故障处理常见问题排查检查复制状态SHOW SLAVE STATUS\G跳过错误谨慎使用STOP SLAVE; SET GLOBAL sql_slave_skip_counter 1; START SLAVE;重建复制# 在主库 mysqldump --master-data2 --single-transaction \ -uroot -p db_name db_dump.sql # 在从库 mysql -uroot -p db_name db_dump.sql CHANGE MASTER TO ...; START SLAVE;13. 云数据库实践13.1 AWS RDS最佳实践连接池配置// HikariCP配置示例 HikariConfig config new HikariConfig(); config.setJdbcUrl(jdbc:mysql://rds-endpoint:3306/db); config.setUsername(app_user); config.setPassword(password); config.setMaximumPoolSize(20); config.setConnectionTimeout(30000); config.addDataSourceProperty(cachePrepStmts, true); config.addDataSourceProperty(prepStmtCacheSize, 250); config.addDataSourceProperty(prepStmtCacheSqlLimit, 2048);13.2 阿里云RDS性能优化只读实例使用// Spring Boot多数据源配置 Bean Primary ConfigurationProperties(spring.datasource.master) public DataSource masterDataSource() { return DataSourceBuilder.create().build(); } Bean ConfigurationProperties(spring.datasource.slave) public DataSource slaveDataSource() { return DataSourceBuilder.create().build(); }13.3 跨地域同步方案使用AWS DMS服务创建复制实例配置源和目标端点创建并启动复制任务监控CDC变更数据捕获14. 未来趋势与扩展阅读14.1 MySQL与NewSQLTiDB兼容MySQL协议的优势水平扩展能力强一致性分布式事务实时HTAP能力迁移注意事项语法兼容性检查事务隔离级别调整分布式JOIN优化14.2 向量搜索扩展MySQL 8.0的向量搜索-- 添加向量列 ALTER TABLE products ADD COLUMN embedding VECTOR(1536); -- 向量相似度搜索 SELECT id, name, VECTOR_DISTANCE(embedding, CAST([0.1, 0.2, ..., 0.5] AS VECTOR(1536)) ) AS distance FROM products ORDER BY distance LIMIT 10;14.3 推荐学习路径进阶学习资源书籍《高性能MySQL》第4版《MySQL技术内幕InnoDB存储引擎》在线课程MySQL官方认证课程极客时间《MySQL实战45讲》社区Percona博客PlanetMySQL阿里云数据库技术月刊