1. MySQL CRUD操作的核心价值在数据库操作中CRUDCreate, Read, Update, Delete构成了最基础也最重要的四大操作。作为关系型数据库的代表MySQL的CRUD操作看似简单但其中蕴含着大量值得深入探讨的技术细节和优化空间。我见过太多开发者在面试时能流畅说出CRUD的定义但在实际工作中却频繁犯下低级错误。比如在百万级数据表上不加索引就执行全表扫描的SELECT或者在大事务中执行大量UPDATE导致锁表现象。这些问题的根源都在于对基础操作的理解不够深入。2. 环境准备与基础配置2.1 MySQL安装与配置工欲善其事必先利其器。在进行CRUD操作前我们需要确保MySQL环境正确配置。以MySQL 8.0为例安装后有几个关键配置需要特别注意[mysqld] # 设置默认字符集 character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci # 事务隔离级别默认REPEATABLE-READ transaction-isolationREAD-COMMITTED # 最大连接数 max_connections200 # 查询缓存大小MySQL 8.0已移除查询缓存 # query_cache_size0注意MySQL 8.0已经移除了查询缓存功能这是很多从老版本迁移过来的开发者容易忽略的点。2.2 创建测试数据库我们创建一个简单的电商数据库作为示例CREATE DATABASE ecommerce DEFAULT CHARACTER SET utf8mb4; USE ecommerce; CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_name (name), INDEX idx_price (price) ) ENGINEInnoDB;这个表设计包含了几个关键点使用utf8mb4字符集支持完整Unicode设置自增主键添加了created_at和updated_at时间戳为常用查询字段建立了索引3. CREATE操作的艺术3.1 基础插入语句最基本的INSERT语句大家都熟悉INSERT INTO products (name, price, stock) VALUES (iPhone 13, 6999.00, 100);但实际生产环境中我们更常使用批量插入INSERT INTO products (name, price, stock) VALUES (MacBook Pro, 12999.00, 50), (AirPods Pro, 1999.00, 200), (iPad Air, 4799.00, 80);批量插入相比单条插入有显著性能优势。在我的测试中插入1000条记录单条插入约12秒批量插入每次100条约0.8秒3.2 高级插入技巧3.2.1 INSERT IGNORE当遇到重复键时忽略错误INSERT IGNORE INTO products (id, name, price) VALUES (1, iPhone 13, 6999.00);3.2.2 REPLACE当遇到重复键时替换整行REPLACE INTO products (id, name, price) VALUES (1, iPhone 13 Pro, 7999.00);3.2.3 ON DUPLICATE KEY UPDATE最实用的存在则更新操作INSERT INTO products (id, name, price) VALUES (1, iPhone 13, 6999.00) ON DUPLICATE KEY UPDATE name VALUES(name), price VALUES(price), updated_at NOW();经验分享在数据同步场景中ON DUPLICATE KEY UPDATE比先查询再决定INSERT或UPDATE效率高得多。3.3 从其他表导入数据INSERT INTO products (name, price) SELECT product_name, product_price FROM old_products WHERE category electronics;4. READ操作的艺术4.1 基础查询-- 查询所有列 SELECT * FROM products; -- 查询特定列 SELECT name, price FROM products; -- 带条件的查询 SELECT * FROM products WHERE price 5000;4.2 查询优化要点4.2.1 避免SELECT *-- 不推荐 SELECT * FROM products; -- 推荐 SELECT id, name, price FROM products;只查询需要的列可以减少网络传输量和内存占用特别是在宽表列多的表情况下差异明显。4.2.2 正确使用索引-- 使用索引 SELECT * FROM products WHERE name iPhone 13; -- 未使用索引LIKE以通配符开头 SELECT * FROM products WHERE name LIKE %Phone%; -- 使用索引LIKE不以通配符开头 SELECT * FROM products WHERE name LIKE iPhone%;4.2.3 分页优化常见但低效的分页写法SELECT * FROM products LIMIT 10000, 20;优化方案SELECT * FROM products WHERE id 10000 LIMIT 20;4.3 高级查询技巧4.3.1 窗口函数MySQL 8.0SELECT id, name, price, RANK() OVER (ORDER BY price DESC) as price_rank, price - LAG(price, 1) OVER (ORDER BY price) as price_diff FROM products;4.3.2 公用表表达式(CTE)WITH expensive_products AS ( SELECT * FROM products WHERE price 5000 ) SELECT * FROM expensive_products ORDER BY price DESC;4.3.3 JSON处理MySQL 5.7SELECT id, JSON_EXTRACT(attributes, $.color) as color, JSON_EXTRACT(attributes, $.weight) as weight FROM products WHERE JSON_CONTAINS(attributes, red, $.color);5. UPDATE操作的艺术5.1 基础更新UPDATE products SET price 7499.00 WHERE id 1;5.2 批量更新技巧-- 基于条件的批量更新 UPDATE products SET stock stock - 10 WHERE price 5000; -- 使用CASE语句的复杂更新 UPDATE products SET price CASE WHEN name LIKE iPhone% THEN price * 0.9 WHEN name LIKE Mac% THEN price * 0.85 ELSE price * 0.95 END;5.3 更新优化建议总是带上WHERE条件避免全表更新大批量更新时考虑分批次执行在事务中执行相关更新操作更新前先EXPLAIN查看执行计划血泪教训我曾经在生产环境执行过一个没有WHERE条件的UPDATE导致全表所有记录被更新花了3小时才从备份恢复。6. DELETE操作的艺术6.1 基础删除DELETE FROM products WHERE id 1;6.2 批量删除优化对于大表删除建议-- 低效做法 DELETE FROM large_table WHERE create_time 2020-01-01; -- 高效做法分批次删除 DELETE FROM large_table WHERE create_time 2020-01-01 LIMIT 1000; -- 然后循环执行直到影响行数为06.3 替代DELETE的方案6.3.1 使用软删除ALTER TABLE products ADD COLUMN is_deleted TINYINT DEFAULT 0; -- 删除操作变为更新 UPDATE products SET is_deleted 1 WHERE id 1; -- 查询时排除已删除的 SELECT * FROM products WHERE is_deleted 0;6.3.2 使用归档表-- 将要删除的数据移到归档表 INSERT INTO products_archive SELECT * FROM products WHERE create_time 2020-01-01; -- 然后从主表删除 DELETE FROM products WHERE create_time 2020-01-01;7. 事务与锁机制7.1 基础事务START TRANSACTION; UPDATE accounts SET balance balance - 1000 WHERE user_id 1; UPDATE accounts SET balance balance 1000 WHERE user_id 2; COMMIT; -- 或者出错时 ROLLBACK;7.2 事务隔离级别MySQL默认使用REPEATABLE-READ隔离级别但某些场景可能需要调整-- 查看当前隔离级别 SELECT transaction_isolation; -- 设置会话级别隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;7.3 锁的注意事项尽量使用索引列作为条件避免锁表长事务会导致锁持有时间过长死锁可以通过SHOW ENGINE INNODB STATUS分析8. 性能监控与优化8.1 慢查询日志[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 18.2 EXPLAIN分析EXPLAIN SELECT * FROM products WHERE name LIKE iPhone%;关键指标type最好达到ref或range级别possible_keys可能使用的索引key实际使用的索引rows预估扫描行数8.3 索引优化建议为WHERE、JOIN、ORDER BY的列建立索引遵循最左前缀原则避免过度索引索引也占用空间并影响写入性能定期使用ANALYZE TABLE更新统计信息9. 常见问题解决方案9.1 连接数过多-- 查看当前连接 SHOW PROCESSLIST; -- 查看最大连接数 SHOW VARIABLES LIKE max_connections; -- 临时增加连接数 SET GLOBAL max_connections 500;9.2 主键冲突-- 查看自增值 SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA ecommerce AND TABLE_NAME products; -- 重置自增值 ALTER TABLE products AUTO_INCREMENT 1000;9.3 数据恢复从binlog恢复数据mysqlbinlog --start-datetime2023-01-01 00:00:00 \ --stop-datetime2023-01-01 12:00:00 \ /var/lib/mysql/mysql-bin.000123 | mysql -u root -p10. 最佳实践总结总是为表设置主键为常用查询条件创建适当索引避免在WHERE条件中对字段进行函数操作大批量操作时分批次进行生产环境操作前先在测试环境验证重要操作前先备份数据使用EXPLAIN分析复杂查询监控慢查询并及时优化合理设置事务隔离级别定期维护数据库ANALYZE TABLE, OPTIMIZE TABLE