MySQL技能使用说明

快速参考

字符集陷阱

• utf8已损坏——仅3字节，无法存储表情符号；始终使用utf8mb4
• utf8mb4_unicode_ci用于不区分大小写的排序；utf8mb4_bin用于精确字节比较
• JOIN中的排序规则不匹配会严重影响性能——确保跨表使用一致的排序规则
• 连接字符集必须匹配：SET NAMES utf8mb4或连接字符串参数
• 在utf8mb4列上创建索引会更大——可能触及索引大小限制；考虑使用前缀索引

与PostgreSQL的索引差异

• 不支持部分索引——无法WHERE active = true在索引定义中
• MySQL 8.0.13之前不支持表达式索引——此前必须使用生成列
• TEXT/BLOB类型需要指定前缀长度：INDEX (description(100))——未指定长度会导致错误
• 不支持INCLUDE语法实现覆盖索引——需要将列直接加入索引：INDEX (a, b, c)来覆盖c列
• 仅InnoDB引擎自动为外键创建索引——使用前需确认存储引擎

UPSERT操作模式

• INSERT ... ON DUPLICATE KEY UPDATE——非标准SQL；需要唯一键冲突触发
• LAST_INSERT_ID()用于自增ID——没有PostgreSQL的RETURNING子句
• REPLACE INTO先删除后插入——会改变自增ID，触发DELETE级联操作
• 检查受影响的行：1表示插入，2表示更新（反直觉）

锁的陷阱

• SELECT ... FOR UPDATE锁定行——但间隙锁可能锁定超出预期的范围
• InnoDB使用临键锁——防止幻读但可能导致死锁
• 锁等待超时默认50秒——innodb_lock_wait_timeout用于调整
• FOR UPDATE SKIP LOCKED存在于MySQL 8+——队列模式
• InnoDB默认隔离级别是REPEATABLE READ，不像PostgreSQL是READ COMMITTED
• 死锁是预期内的——代码必须捕获并重试，不能直接失败

GROUP BY严格性

• sql_mode包含ONLY_FULL_GROUP_BY在MySQL 5.7+中默认启用
• 非聚合列必须在GROUP BY中——不像旧版MySQL的宽松模式
• ANY_VALUE(column)当你知道值相同时用于消除错误
• 检查旧数据库的sql_mode——行为可能不同

InnoDB与MyISAM对比

• 始终使用InnoDB——支持事务、行级锁、外键约束、崩溃恢复
• 某些系统表仍默认使用MyISAM——不要用于应用数据
• 检查存储引擎：SHOW TABLE STATUS——通过以下语句转换：ALTER TABLE ... ENGINE=InnoDB
• JOIN中混合引擎可以工作但会失去事务保证

查询特性差异

• LIMIT偏移量, 数量与PostgreSQL顺序不同LIMIT数量 OFFSET偏移量
• !=与<>两者都有效；建议使用<>以符合SQL标准
• 不支持事务性DDL——ALTER TABLE会立即提交，无法回滚
• 布尔类型是TINYINT(1)—真值/假值仅为1/0
• IFNULL(a, b)而非COALESCE适用于两个参数——尽管COALESCE也有效

连接管理

• wait_timeout会终止空闲连接——默认8小时；连接池可能无法察觉
• max_connections默认151——通常过低；每个连接都会占用内存
• 连接池：所有应用实例的总连接数不要超过max_connections
• SHOW PROCESSLIST查看活动连接——可通过以下命令终止长时间运行的连接KILL <id>

复制注意事项

• 基于语句的复制可能因非确定性函数而中断——例如UUID(), NOW()
• 基于行的复制更安全但消耗更多带宽——MySQL 8 中的默认设置
• 只读副本存在延迟——在依赖副本读取前需检查 Seconds_Behind_Master 切勿写入只读副本——通常配置为只读但需核实
• 性能

EXPLAIN ANALYZE

• 仅适用于 MySQL 8.0.18+ 版本——旧版本仅支持 EXPLAIN 而不显示实际执行时间MySQL 8 已移除查询缓存——勿依赖此功能；应在应用层实现缓存
• OPTIMIZE TABLE
• 用于整理碎片化表格——会锁定表；大型表建议使用 pt-online-schema-change 工具innodb_buffer_pool_size
• 对于专用数据库服务器，建议设置为内存的 70-80%—set to 70-80% of RAM for dedicated DB server