两张3百多万的表关联查询速度优化

现象：表A数据量310多万，表B数据量320多万，A left join B执行时间超10分钟无结果
优化经过：
1、网上有人说tmp_table_size的值太小，查看tmp_table_size

show variables like '%tmp%';  

 ---218KB确实有些小
调整tmp_table_size大小：

set global tmp_table_size=512*1024*1024;

2、测试，超10分钟无结果
3、查看join_buffer_size的值

show variables like '%join%'; 

—忘记具体是多少了，不大，然后调整成了8M

set global tmp_table_size=8*1024*1024;

4、测试，依然超10分钟无结果
5、查看执行计划

explain select a.handno from A left join B on a.handno=b.handno

type：index
possible_keys：NULL
key_len：82
rows分别是：3100000 、3200000
6、尝试inner join，查看执行计划

explain select a.handno from A inner join B on a.handno=b.handno

type分别是：index eq_ref
possible_keys：NULL PRIMARY
key_len分别是：82 62
rows分别是：3100000 、1
执行语句2分钟左右可以查询出结果，那说明按着这个执行计划就可以出结果，inner join可以很快查询出结果，而left join却不行，突然想到两个表调换下位置关联
7、查看执行计划

explain select b.handno from B left join A on b.handno=a.handno

type分别是：index eq_ref
possible_keys：NULL PRIMARY
key_len分别是：82 62
rows分别是：3200000 、1
8、换一下位置就可以，可能是跟表结构有关系，查看两张表的建表语句，发现有区别的地方有两个：一是表的存储索引不同A是INNODB，B是MyISAM，二是表A的建表语句里有参数Row_Format=DYNAMIC
9、重新创建表A，存储引擎设为MyISAM,去掉Row_Format=DYNAMIC，再次查看执行计划，执行计划如下：type分别是：index eq_ref
possible_keys：NULL PRIMARY
key_len分别是：82 62
rows分别是：3100000 、1
10、但是MyISAM存储引擎不适合大数据量的表，调整表的存储引擎为INNODB，再次查看执行计划，执行计划无变化，可以在2分钟左右查询出结果

alter table A engine=InnoDB;
alter table B engine=InnoDB;

注：执行计划详解参考
https://blog.csdn.net/weixin_41558728/article/details/81704916

另：记录一下，mysql的format:
在mysql中， 若一张表里面不存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫静态表，即该表的row_format是fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。
若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话，那么张这个表其实也叫动态表，即该表的row_format是dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。
所以，做搜索查询量大的表一般都以空间来换取时间，设计成静态表。
row_format还有其他一些值：
DEFAULT
FIXED
DYNAMIC
COMPRESSED
REDUNDANT
COMPACT

修改行格式：
ALTER TABLE table_name ROW_FORMAT = DEFAULT

修改过程导致：
fixed—>dynamic: 这会导致CHAR变成VARCHAR
dynamic—>fixed: 这会导致VARCHAR变成CHAR