（mysql）多个字段需要子查询，求优化

Question

问个问题，（MySQL）A表的字端有code_name,code_id,code_field,另一表B表有很多属性，例如国籍，籍贯等等，国籍子查询查法为select code_name form A表 where code_id=B表.gj_id and code_field = 'gj'。现在遇到的困难时子查询太慢了，而且除了国籍外还有籍贯等属性，如果都去用子查询的话，一次估计几分钟。这种情况下能不能用联表…… 请问有没有什么好的解决方案！ 文笔不行，可以直接看下面代码，看看怎么优化 select (select code_name from A where code_id = B.GJ and field_name = 'GJ') as GJ, -- 国籍 (select code_name from A where code_id = B.JG and field_name = 'JG') as JG, -- 籍贯 (select code_name from A where code_id = B.SYD and field_name = 'SYD') as SYD-- 生源地 FROM B; 麻烦了！！！ 以下为大致的表结构（不好发正式的表结构）A表 CREATE TABLE `NewTable` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键' , `GJ` int(11) NOT NULL , `JG` int(11) NOT NULL , `MZ` int(11) NOT NULL , PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=3 CHECKSUM=0 ROW_FORMAT=FIXED DELAY_KEY_WRITE=0 ; B表 CREATE TABLE `NewTable` ( `id` int(11) NOT NULL , `code_id` int(11) NOT NULL , `code_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `field_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci CHECKSUM=0 ROW_FORMAT=DYNAMIC DELAY_KEY_WRITE=0 ; A表图 B表图 如果用子查询 select (select code_name from B where code_id = A.GJ and field_name = 'GJ') as GJ (select code_name from B where code_id = A.MZ and field_name = 'MZ') as MZ (select code_name from B where code_id = A.JG and field_name = 'JG') as JG FROM A; 以上是大概的结构！现在是需要优化查询的语句…… 我的尝试 select B.code_name,C.code_name FROM A JOIN B ON B.code_id = A.GJ AND B.field_name = 'GJ' JOIN B as C ON C.code_id = A.MZ AND C.field_name = 'MZ' 这种嵌套join的写法，竟然比子查询还要漫长……这是什么原因呢，知道的也可以说一下哈

Answer 1

首先假设AB两个表里该加的索引都加了（如果B表加了联合索引应该没这么慢的），看你评论里说了没权限动他们的表，那就先不考虑这个问题。

先说下嵌套join为什么会更慢：语句用了多层join，一次join就是做一次笛卡尔积，数据量大的时候，几次笛卡尔积的量就是天文数字了。而用子查询，最坏的情况，计算量就是一次笛卡尔积。

解法的话，当然最好的办法是写个python脚本，把B表数据一次性加载到内存里，然后自行组装A表的数据，这样就省去了每次还要查询B表。

如果条件不允许的话，试试在子查询里加上limit 1，来减少一些全表扫描时的行数：

select 

(select code_name from B where code_id = A.GJ and field_name = 'GJ' limit 1) as GJ
(select code_name from B where code_id = A.MZ and field_name = 'MZ' limit 1) as MZ
(select code_name from B where code_id = A.JG and field_name = 'JG' limit 1) as JG

FROM

A;

Answer 2

explain 看下执行计划

Answer 3

B表建立联合索引idx_code_id_field_name(code_id,field_name)
如果没权限修改表，自己创建个B的临时表总可以吧，临时表带上索引再去关联A表