-- 创建测试表 CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT, email VARCHAR(100), created_time DATETIME, INDEX idx_name (name), INDEX idx_age (age), INDEX idx_created_time (created_time) ); -- 雷区1.1：对索引列进行函数操作 -- 错误写法：索引失效 EXPLAIN SELECT * FROM user WHERE DATE(created_time) = '2023-01-01'; -- 正确写法：使用范围查询 EXPLAIN SELECT * FROM user WHERE created_time >= '2023-01-01 00:00:00' AND created_time '2023-01-02 00:00:00'; -- 雷区1.2：隐式类型转换 -- 错误写法：name是字符串，但用了数字，导致索引失效 EXPLAIN SELECT * FROM user WHERE name = 123; -- 正确写法：类型匹配 EXPLAIN SELECT * FROM user WHERE name = '123'; -- 雷区1.3：前导模糊查询 -- 错误写法：LIKE以%开头，索引失效 EXPLAIN SELECT * FROM user WHERE name LIKE '%三%'; -- 正确写法：非前导模糊查询，可以使用索引 EXPLAIN SELECT * FROM user WHERE name LIKE '苏%'; -- 雷区1.4：OR条件使用不当 -- 错误写法：age有索引，email无索引，导致整个查询无法使用索引 EXPLAIN SELECT * FROM user WHERE age = 25 OR email = 'test@example.com'; -- 正确写法：使用UNION优化OR查询 EXPLAIN SELECT * FROM user WHERE age = 25 UNION SELECT * FROM user WHERE email = 'test@example.com';
深度剖析
有些小伙伴在工作中可能会疑惑：为什么这些写法会导致索引失效？
函数操作破坏索引有序性
索引是按照列值的原始顺序存储的
对列使用函数后，MySQL无法利用索引的有序性
必须扫描所有索引项，计算函数值后再比较
隐式类型转换的本质
当类型不匹配时，MySQL会进行隐式转换
实际上相当于：CAST(name AS SIGNED) = 123
对索引列进行了函数操作，导致失效
前导模糊查询的B+树遍历
B+树索引按照前缀排序
LIKE '苏%'可以利用前缀匹配
LIKE '%三'无法确定前缀，必须全表扫描
避坑指南
避免对索引列进行函数操作
确保查询条件与索引列类型匹配
谨慎使用前导模糊查询
使用UNION优化复杂的OR查询
雷区二：事务隔离级别与幻读
事务隔离级别是MySQL中比较复杂的概念，理解不当会导致数据不一致和性能问题。
为什么事务隔离级别重要？
不同的隔离级别在数据一致性、性能、并发性之间做出不同权衡。
选择不当会出现脏读、不可重复读、幻读等问题。
示例场景
-- 查看当前事务隔离级别 SELECT @@transaction_isolation; -- 设置隔离级别为REPEATABLE-READ（默认） SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 场景：转账业务中的幻读问题 -- 会话1：事务A START TRANSACTION; SELECT COUNT(*) FROM account WHERE user_id = 1001; -- 返回2 -- 会话2：事务B START TRANSACTION; INSERT INTO account (user_id, balance) VALUES (1001, 500); COMMIT; -- 会话1：事务A继续 SELECT COUNT(*) FROM account WHERE user_id = 1001; -- 仍然返回2（可重复读） UPDATE account SET balance = balance + 100 WHERE user_id = 1001; -- 影响3行！ SELECT COUNT(*) FROM account WHERE user_id = 1001; -- 返回3，出现幻读！ COMMIT;
-- 创建测试表，假设有1000万数据 CREATE TABLE order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT, amount DECIMAL(10,2), status TINYINT, created_time DATETIME, INDEX idx_created_time (created_time) ); -- 雷区：传统的分页写法 -- 当offset达到500万时，性能急剧下降 EXPLAIN SELECT * FROM order ORDER BY created_time DESC LIMIT 5000000, 20; -- 优化方案1：游标分页（推荐） -- 第一页 SELECT * FROM order ORDER BY created_time DESC, id DESC LIMIT 20; -- 第二页：记住上一页最后一条记录的created_time和id SELECT * FROM order WHERE created_time '2023-06-01 10:00:00' OR (created_time = '2023-06-01 10:00:00' AND id1000000) ORDER BY created_time DESC, id DESC LIMIT 20; -- 优化方案2：子查询优化（适用于非游标场景） SELECT * FROM order WHERE id >= ( SELECT id FROM order ORDER BY created_time DESC LIMIT 5000000, 1 ) ORDER BY created_time DESC LIMIT 20;
深度剖析
有些小伙伴在工作中可能发现，为什么offset越大查询越慢？
传统分页的性能瓶颈：
大量无效IO：需要读取并跳过offset条记录
回表成本：对于非覆盖索引，需要回表查询完整数据
排序开销：大数据量的排序可能在磁盘进行
游标分页的优势：
直接定位到起始位置，无需跳过大量记录
利用索引的有序性，避免排序操作
性能稳定，不随数据量增长而下降
为了理解传统分页与游标分页的区别，我画了一个对比图：
避坑指南
优先使用游标分页（基于游标或时间戳）
如果必须使用传统分页，使用子查询优化
确保排序字段有索引
前端配合使用无限滚动或游标分页UI
雷区四：字符集与排序规则陷阱
字符集问题经常在系统国际化或多语言支持时暴露，处理不当会导致乱码、排序错误、索引失效。
为什么字符集如此重要？
不同的字符集支持不同的字符范围，排序规则影响字符串比较和排序结果。
示例场景
-- 查看字符集配置 SHOW VARIABLES LIKE'character_set%'; SHOW VARIABLES LIKE'collation%'; -- 雷区：UTF8不是真正的UTF-8 -- MySQL的utf8最多支持3字节，无法存储emoji等4字节字符 CREATE TABLE user_utf8 ( id INT PRIMARY KEY, name VARCHAR(50) CHARACTER SET utf8 ); -- 插入emoji表情失败 INSERT INTO user_utf8 VALUES (1, '张三😊'); -- 错误！ -- 正确：使用utf8mb4 CREATE TABLE user_utf8mb4 ( id INT PRIMARY KEY, name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ); -- 插入emoji成功 INSERT INTO user_utf8mb4 VALUES (1, '张三😊'); -- 成功！ -- 雷区：排序规则影响查询结果 CREATE TABLE product ( id INT PRIMARY KEY, name VARCHAR(100) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; -- 大小写不敏感查询 SELECT * FROM product WHERE name = 'apple'; -- 会匹配'Apple', 'APPLE' -- 如果需要大小写敏感，使用binary或特定collation SELECT * FROM product WHERE name = BINARY 'apple'; -- 只匹配'apple'
-- 创建带外键的表结构 CREATE TABLE department ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOTNULL ); CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOTNULL, department_id INT, FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE ); -- 雷区1：级联删除导致意外数据丢失 -- 删除部门时，所有相关员工也被删除，可能不是期望的行为 DELETE FROM department WHERE id = 1; -- 部门1的所有员工都被删除！ -- 雷区2：外键锁竞争 -- 会话1：删除部门 START TRANSACTION; DELETE FROM department WHERE id = 1; -- 持有部门1的锁 -- 会话2：在同一个部门插入员工（被阻塞） START TRANSACTION; INSERT INTO employee (name, department_id) VALUES ('新员工', 1); -- 等待锁 -- 雷区3：数据迁移困难 -- 导入数据时必须按正确顺序，否则外键约束失败
