MySQL cache之join buffer的优化
mysql的连接(join)处理只有nest loop一种算法,基本原理是先取第一个表的行,然后再依次
扫描第二个表中的所有行进行匹配,一旦匹配成功则返回结果集.
在连接的过程有几个很重要的因素,比如cpu,连接栏位上是否有索引等等,本文将只讨论join buffer
的影响.join_buffer_size在mysql官方文档只说明了用于连接的buffer大小。在这里有几个问题没有交待清楚
1,join buffer究竟是存放的什么数据?
2,为什么不使用标准的cache,而对于join操作要单独设置一个buffer?
再来看nest loop的处理过程,由于第二个表可能需要访问多次,可能造成的一个后果就是第二个表对的cache
会非常热,从而形成热块,在多个线程执行连接同时去访问相应的的cache时,带来的影响越大.
使用了一个专用的join buffer来存放第二个表以后,至少可以解决以下问题:1,由于join buffer是存放的基于每
thread的连接表信息,这样在连接的时候,只需要访问join buffer就可以了,不需要再去有并发机制保护的cache.
2,join buffer的代码路径更短,执行访问速度更快.
例,创建两个表
t1(a int,b varchar(100),c varchar(100));
t2(a int,b varchar(100),c varchar(100));
分别insert数据
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 9999 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 99999 |
+----------+
1 row in set (0.04 sec)
更改join_buffer_size的大小,观察执行结果。
mysql> set session join_buffer_size=128;
Query OK, 0 rows affected (0.00 sec)
mysql> select STRAIGHT_JOIN count(t2.a) from t1,t2 where t1.a=t2.a;
+-------------+
| count(t2.a) |
+-------------+
| 9999 |
+-------------+
1 row in set (43.92 sec)
mysql> set session join_buffer_size=268435456;
Query OK, 0 rows affected (0.00 sec)
mysql> select STRAIGHT_JOIN count(t2.a) from t1,t2 where t1.a=t2.a;
+-------------+
| count(t2.a) |
+-------------+
| 9999 |
+-------------+
1 row in set (32.19 sec)
可以看到在更改过后,由于join_buffer_size更大了,执行速度会加快.
对于join_buffer_size的优化,由于是每个线程单独分配,因此需要根据最大连接数,机器可用内存来分配,防止内存用尽或OOM
一般4-8M是一个比较合理的值,对于复杂的连接sql,可以在连接开始前手动指定join_buffer_size大小.