MySQL优化那些隐藏的技巧和原理，很多人其实还没搞明白呢

根据多位资深数据库开发者和技术博主，如“数据库内核杂谈”、“老叶茶馆”等长期分享的MySQL实践经验总结）

MySQL优化这个话题，很多人一上来就想着改配置参数，比如innodb_buffer_pool_size要调多大，query_cache_size开不开，但其实，绝大多数性能问题，根本轮不到去动这些高级参数，很多隐藏的技巧和原理，就藏在最基础的SQL编写和表结构设计里，但恰恰是这些，很多人没搞明白,或者知其然不知其所以然。

第一个隐藏技巧：理解索引的真正工作原理，而不仅仅是“建个索引”。

很多人知道查询慢要建索引，但为什么建了索引有时候还慢？这里有个关键原理：索引的最左前缀匹配原则，比如你有一个联合索引是 (a, b, c)，它不光光是为了查询WHERE a=1 AND b=2 AND c=3准备的，它相当于同时拥有了三个索引：(a)、(a, b)、(a, b, c)。

WHERE a=1 可以用到这个索引。WHERE a=1 AND b=2 也可以，如果你跳过a，直接查 WHERE b=2 AND c=3，这个联合索引就完全失效了，因为索引的排列顺序是先按a排序，a相同再按b排，b相同再按c排，你直接从b开始查，数据库没办法利用这个有序的结构，只能全表扫描，这就好比电话簿是按姓氏、然后名字排序的，你没法直接快速找到所有叫“小明”的人。

另一个隐藏点是索引下推，这是MySQL 5.6引入的一个很重要的优化，还是上面那个索引 (a, b, c)，假设你执行 WHERE a=1 AND c=3，在旧版本中，数据库会先用索引找到所有a=1的记录，然后根据主键ID一条条回表，到真实数据行里再去判断c=3，但有了索引下推，数据库在索引层面就会先判断c=3这个条件（因为c也在索引里），只有同时满足a=1和c=3的记录，才会回表，这大大减少了不必要的回表操作，提升了性能，很多人感觉不到这个变化,但它在底层默默地帮了很多忙。

*第二个隐藏技巧：COUNT() 其实很快，别瞎优化。**

很多人被网上的一些文章误导，觉得COUNT(*)慢，于是用COUNT(1)或者COUNT(主键)来代替，觉得这样会更快，这其实是个误解,原理在于：

• COUNT(*)：SQL标准定义是统计行数，MySQL对它做了特别优化，它会忽略所有列，直接读取行数计数,效率很高。
• COUNT(1)：和COUNT(*)的执行计划几乎一模一样，性能上没有本质区别，里面的“1”不代表列，只是代表一个常量值,数据库同样忽略具体列内容。
• COUNT(列名)：这个就不同了！它要判断指定的列是否为NULL，不为NULL才计数，这需要去读取每一行的这个列的值，如果这个列上没有索引，还需要做全表扫描，效率自然比COUNT(*)低。

*当你需要统计表的总行数时，放心大胆地用`COUNT()`，它是最优选择。** 真正的瓶颈在于大数据量的计数，这时候应该考虑用额外的统计表或者缓存来优化,而不是纠结于COUNT的写法。

第三个隐藏技巧：JOIN查询的驱动表选择，不是你想的那样。

写JOIN语句的时候，哪个表放在前面（作为驱动表），哪个表放在后面（被驱动表），是有讲究的，一个常见的误区是“小表驱动大表”这个说法，这个说法大体没错，但这里的“小”更准确的指的是经过WHERE条件过滤后，结果集小的表。

表A有100万条数据，但你的查询条件WHERE A.type='active'过滤后只剩下100条，表B有1万条数据，没有过滤条件，应该让A表作为驱动表，因为数据库会先循环这100条结果，再去1万条数据的B表里进行关联查询（如果B表的关联字段有索引，会非常快），反之，如果用B表驱动A表，就要循环1万次，每次去100万条数据的A表里查（即使有索引，1万次查找也比100次慢）。

MySQL的查询优化器通常会帮你做出正确的选择，但如果你发现它的选择不对（可以通过EXPLAIN命令查看执行计划），就可以使用STRAIGHT_JOIN来强制指定驱动表的顺序，理解这个原理,能让你在优化复杂关联查询时更有方向。

第四个隐藏技巧：分页查询的大偏移量优化，别再用LIMIT offset, size了。

当数据量很大时，翻页到后面，比如LIMIT 100000, 20，会非常慢，很多人不知道为什么，原理是：这个语句并不是直接跳到第100000条记录开始取20条，它是先取出100020条完整的数据行，然后在服务器端抛弃前面的100000条，返回最后的20条，这个“取”和“弃”的过程,成本极高。

一个有效的优化技巧是延迟关联，先通过覆盖索引快速定位到需要的主键ID,再根据这些ID回表查询需要的列。

比如原SQL是： SELECT * FROM articles ORDER BY created_time DESC LIMIT 100000, 20;

优化后可以写成： SELECT * FROM articles INNER JOIN ( SELECT id FROM articles ORDER BY created_time DESC LIMIT 100000, 20 ) AS tmp USING(id);

子查询SELECT id只操作索引（覆盖索引），速度很快，拿到20个目标ID后，再通过主键ID快速回表取出完整行，这个技巧在偏移量非常大时,性能提升是数量级的。

第五个隐藏技巧：字段类型选择的影响比你想象的大。

选择一个错误的字段类型,可能是性能问题的万恶之源。

• 用VARCHAR存储IP地址：IP地址本质是32位无符号整数，用VARCHAR(15)存储不仅占用更多空间，查询效率也低，应该用INT UNSIGNED，然后用INET_ATON()和INET_NTOA()函数进行转换,查询和排序效率会高很多。
• 用CHAR存储不定长字符串：CHAR是定长的，比如你定义CHAR(100)，即使用来存“abc”，它也会占用100个字符的空间（尾部用空格填充），而VARCHAR是变长的，只占用实际长度+1或2个字节的存储空间，定长字段在更新时不容易产生碎片，但如果不必要的长，会浪费大量存储空间和内存，导致一次读入内存的数据页变少，间接影响性能，除非存储像MD5哈希值这种长度完全固定的字符串,否则优先考虑VARCHAR。
• 枚举（ENUM）和集合（SET）类型的妙用：对于一些状态、类型等取值固定的字段，使用ENUM或SET类型，在数据库内部是用整数存储的，非常节省空间，查询效率也比VARCHAR高，但缺点是修改可选值需要ALTER TABLE,不够灵活。

MySQL优化不是一个神秘的黑盒子，它建立在扎实的基础知识上，与其盲目地去调整那些复杂的配置参数，不如先花时间把索引原理、SQL写法、表结构设计这些基本功打牢，理解了这些隐藏在最底层的技巧和原理,你才能从根本上解决大多数性能问题。