生产环境为什么尽量不用join

  公司的mysql规范里一定有对join使用的限制，要么是生产环境不能使用联表查询，要么是join不能超过3个表，而且还要经过leader审批后才能使用。今天就分析join语句的性能，以及为什么对join有这么多限制。

  先建立两张表，test1和test2，向test1中插入1000条数据，test2中插入100条数据。两张表的表结构相同。

  一，执行select * from test2 left join test1 on test1.m = test2.m

  第一行语句表示test2表是全表扫描，test1表用到了索引。

  这条语句将test2作为驱动表，test1作为被驱动表。遍历test2中表的行，然后到test1中去比对（用到了索引），比对成功的，返回客户端。

二，执行select * from test2 left join test1 on test1.n = test2.n

  由于n字段没有索引，explain显示两张表都需要全表扫描。“Using join buffer”的意思是两张表在对比的时候，将数据取到join buffer中，也就是内存中的一块区域，虽然时间复杂度没变，但在内存中比较可以提高速度。

如果explain中看到了"Using join buffer"，就说明需要优化join语句了。

  如果不用join语句，由业务自己开发，整体流程是：

  ● 将test2表的数据全部取出select * from test2；

  ●循环取出的数据，select * from test1 where m = test1.m

  可以看到，自己开发在时间复杂度上与使用join差别不大，但流程更清晰。

如果你能对join把握的很好，确实可以使用join，但是如果join的表过多，即使分析explain也很麻烦，也不利于后面的开发维护。

  使用join还会引发其他的问题吗？

  会。假设我们查询的表是一张非常大的表，而且不经常使用，而我们的join语句又写的不好，就需要对表做多次扫描。mysql在扫描到数据之后，会放进内存中，对于长时间不使用的会淘汰（LRU算法），大量的冷表查询，会导致mysql内存命中率降低，从而影响线上的其他业务，这是生产环境限制使用join的重要原因之一。 还有一个原因就是，如果未来要拆库拆表，再处理join语句就会比较麻烦。

现象:列表页因超时查不出来东西,使用postman模拟请花费40多秒,将sql语句单独提出来后查询速度非常慢,40多秒

先上结论:

在两个表关联字段上建立索引解决此问题，下面的内容比这句话爽多了，请继续看

表结构如下:

users(用户)表:id,name

integal_record(分数记录)表:id,user_id,integal_id

其中,integal_record表的user_id关联着users表的id,业务目的是查询每个员工阅读次数、评论次数和积分总和,查询语句如下:

(里面写死的值是我从mybatis里扣出来的)

integral_id=1代表着阅读,integral_id=2代表着评论

查询时间快半分钟是无法让人接受的，从表象的SQL语句来说,首先我们看到有很多SUM函数,因此我们来测试下SUM函数的计算时间，如下查询每个人阅读的累计次数:

可以看出来计算时间很长和SUM函数没什么关系,再看整个SQL除了一些SUM函数外就剩下users表左联integal_record表,我们把条件去掉，直接就写个左查询试试

amazing，我的users表里有13400条数据，关联的integral_record 表里也有13000条数据，仅仅做了左连接竟然花费了70秒，这肯定是超时的元凶；所以我们explain一下，看看mysql对这条数据的查询策略:

查看结果:

我们看到type字段的结果是All，也就是代表全表扫描，那么就好办了，建立索引即可，其中users表中的id属于主键,策略自增,有默认的索引,不在考虑范围内，我们仅需对integral_record表增加索引即可:

继续执行左联语句查看运行时间:

时间仍然很久，索引并没有起作用，这里排除一些逃避困难时的迷信想法:mysql有bug 或者navicat有bug，有网络问题 哈哈哈.....我们来看左联的关键属性:

执行时间从60多秒变成了0.375s,整个世界都安静了，再次explain一下

发现mtemp(integal)表的检索方式从all变成了ref，索引，快的一批，深呼吸....

希望继续遇到这种sql需要优化的问题来增加sql调优相关经验

1. 如果GROUP BY 的列没有索引,产生临时表.

2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.

3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.

4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.

5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.

6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.

1、执行FROM语句

2、执行ON过滤

3、添加外部行

4、执行where条件过滤

5、执行group by分组语句

6、执行having

7、select列表

8、执行distinct去重复数据

9、执行order by字句

10、执行limit字

现在有这么两章表 学生表和分数表，现在需要查出每个班在11点到14点考试的科目，最高分数的学生，以及班级，姓名，和分数

SELECT NAME,score FROM score_record INNER JOIN user_info ON user_info.id = score_record.user_id WHERE create_time BETWEEN '2019-01-01 11:00:00' AND '2019-01-01 14:00:00' GROUP BY classes ORDER BY score DESC 

---