MySQL亿级数据量查询优化方案

背景

最近业务上有一个需求，涉及到两张表的联查，条件也只有三个。是不是听起来很简单？但这两张表一张是2.5亿，一张是4.5亿。加起来将近7亿的数据。之前我自己了解的是MySQL单表数据建议存储量也就是2000w，所以听到这两张表的大小时也很震惊。但凡事都得有个解决方案，在使用了索引等工具，把这次的过程分享一下。

优化方向

首先我们得先有一个方向，按照性价比从高到低分别是索引的建立和优化->SQL语句的优化->表结构的优化->硬件层次的优化。索引是最简单而且效率最高的一种手段，我们一般在优化SQL的时候都会用到，再下来是SQL语句本身，只展示出需要的字段，以及SQL语句本身是否走了索引，索引是否失效等等，关于SQL优化理论可以看我这篇文章

MySQL理论

再往下是表结构的优化，首先是数据类型的选择，例如年龄，很多人用的是int类型存储，但完全可以使用tinyint来代替，将其从4字节变为1字节，同理还有char和varchar，varchar是可变字符，用多少占多少，而char是固定占用设置的空间，对于text的话，尽可能不去使用，再深度一点的就是对于数据存储的方式，水平分库分表等等……最后是硬件层次的优化，这个是成本最高的，往服务器中多加一点CPU，多分配一点资源。以上就是我们优化SQL的几种方向，这次我们仅使用前两种方式来优化这7亿的数据

索引

我们都知道索引的强大，将无序的数据变为有序，首先我们需要给外键加一个索引，也就是这两张表的连接条件的那两个字段，如果有等值匹配的条件字段也可以加，但如果是模糊查询的话就要注意了，因为左LIKE和全模糊会导致索引失效，如果想要模糊查询，可以考虑用搜索引擎实现。时间字段也是可以加索引的以及order by的字段，加完索引后我们可以使用explain执行计划来看一下是否用到了索引

SQL语句调优

对于SQL语句来说，我们只返回我们想要的字段就可以，尽量避免返回无用字段，例如使用SELECT *，另外也不要在SQL语句中进行函数运算，这也会导致索引失效。不要连接过多的表，阿里巴巴的编程规范约定的表是不能超过3张以上。在连接的时候也使用小表去连接大表，这样可以减少表连接的创建次数，避免浪费数据库资源，加快查询速度。