MySQL数据库开发那些你得知道但又不一定全懂的基础知识和技巧

说到MySQL开发，很多朋友会用基本的增删改查，但有些细节和技巧，知道了能让你事半功倍，也更能避免踩坑，这些内容散落在MySQL官方手册、一些经典书籍比如《高性能MySQL》以及很多开发者的经验总结里,我帮你梳理一下。

关于字段类型的选择，别小看它

你是不是习惯性地用VARCHAR(255)来存所有文本？或者用INT来存所有数字？这里有点讲究。

• 数字类型的选择：如果只是存状态码，比如0,1,2，用TINYINT就足够了，它只占1字节，而INT占4字节，数据量大了，节省的空间很可观，存IP地址也是个经典例子，很多人用VARCHAR(15)，但其实MySQL提供了INET_ATON()和INET_NTOA()函数，可以把IP地址转换成INT UNSIGNED类型存储（占4字节），查询和存储效率都更高，这个技巧在《高性能MySQL》里有提到。
• 时间类型的选择：DATETIME和TIMESTAMP都存时间，但区别很大。TIMESTAMP占4字节，带时区转换，存的是UTC时间，检索时会根据当前会话的时区设置显示，而DATETIME占8字节，存的就是字面值的时间，与时区无关，如果你的应用要跨时区，TIMESTAMP可能更合适；如果就是要记录一个固定的时间点（比如用户生日），DATETIME更稳妥,官方手册对这两种类型有详细对比。
• 不要用字符类型存数字：这是性能优化的一个基本原则，用VARCHAR存数字，比如商品编号‘10086’，排序的时候会按字符规则排（‘1’, ‘10’, ‘2’），而不是数值大小（1,2,10）,而且无法利用数字索引的优势。

写SQL语句，养成好习惯

• 永远指定字段名：INSERT INTO table VALUES (...)这种写法很危险，一旦表结构发生变化（比如增加了字段），这个语句就可能报错，老老实实写INSERT INTO table (col1, col2, ...) VALUES (...)，虽然多打几个字，但安全得多,这是无数开发者踩坑后的经验。
• LIMIT 1的妙用：当你查询的结果预期只有一行时（比如根据唯一主键或唯一索引查询），在SELECT语句最后加上LIMIT 1，这会告诉数据库引擎：“我只要一条”，引擎找到一条后就会停止搜索，能提高一点效率,在根据用户输入查询时尤其有用。
• 小心OR引起的索引失效：比如WHERE status = 1 OR name = 'abc'，如果status和name上分别有索引，MySQL通常只能使用其中一个，或者干脆进行全表扫描，一个常见的优化技巧是改用UNION：SELECT ... WHERE status = 1 UNION SELECT ... WHERE name = 'abc'，这样两个索引都可能被用上,这个优化方法在数据库性能相关的文章中被广泛讨论。

索引，用对了是神器，用错了是负担

• 前缀索引：有时候要索引的字段特别长，比如一篇文章的标题，整个建索引会很占空间，可以只对字段的前N个字符建立索引，比如ALTER TABLE articles ADD INDEX idx_title (title(20));，这个N的选取很重要，要保证前缀的辨识度足够高,官方手册有详细说明。
• 覆盖索引是性能加速器：如果一个索引包含了查询所需要的所有字段，那么MySQL就可以直接从索引中获取数据，不需要再回表查询数据行，这速度会快很多，比如表有id, name, age，索引是(name, age)，查询SELECT name, age FROM users WHERE name = 'John'，这个查询就用到了覆盖索引，规划索引时可以有意考虑这一点。《高性能MySQL》中强调了覆盖索引的重要性。
• 索引不是越多越好：索引虽然加快查询，但会降低写入（INSERT/UPDATE/DELETE）速度，因为每次写数据都要更新索引，索引也占用磁盘空间，需要根据实际查询需求来建立,维护不必要的索引是一种浪费。

事务和锁，理解它们才能写好并发程序

• 默认隔离级别不一定是万能的：MySQL默认的隔离级别是“可重复读”（REPEATABLE-READ），在这个级别下，一个事务内多次读取同一范围的数据，结果是一致的，但这可能会带来“幻读”问题（两次查询中间，别的事务插入了新数据），大部分互联网应用其实用“读已提交”（READ-COMMITTED）级别就够了，它能减少很多锁的竞争，但你要能接受不可重复读的现象，设置隔离级别需要根据业务场景权衡,这个在数据库理论教材和官方手册都有深入探讨。
• 尽量让事务短小：事务开启后，会持有锁等资源，如果在一个事务里进行复杂的计算、或者调用外部接口，会导致事务时间很长，增加锁冲突的概率，影响数据库并发性能，好的做法是，在事务内部只做数据库操作,尽快提交或回滚。

一些有用的函数和操作

• ON DUPLICATE KEY UPDATE：实现“存在则更新，不存在则插入”的神奇语法，比如导入数据时，如果唯一键冲突，就更新其他字段的值，一句SQL搞定，避免了先查询再判断的繁琐，这是MySQL的扩展语法,非常好用。
• EXPLAIN命令是你的最佳朋友：任何时候，对一条感觉慢的SQL语句，就在它前面加上EXPLAIN，看看MySQL的执行计划，它会告诉你是否使用了索引、使用了哪个索引、扫描了多少行等等，这是SQL性能调优的第一步，也是最重要的一步，官方手册对EXPLAIN的输出有逐项解释。

这些点都是MySQL开发中非常实际的基础知识和技巧，理解了它们，能让你写出更健壮、更高效的代码。