（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表