【求助】二十多万条记录 视图查询优化问题

Question

现在有三个表，A只有8条记录，B和C各有二十多万条记录我要从这三个表中进行多表查询。起初，我将A/B/C通过inner join的方式，建立了视图，然后在视图上进行查询。但性能却强差人意，执行一次平均需要三五分钟，而且经常会由于无响应导致查询失败。目前，我个人愚见觉得join的方式有点不对，因为是笛卡尔乘积而且视图不支持索引，所以要遍历所有数据，也就是8*20W*20W次，想想都可怕。不过我想不到其他的办法，请各位朋友给点意见，指导一下可以吗？
如果哪里描述不祥，请提出来，我会持续增加信息。

Answer 1

SQL代码是什么，在连接字段上建立索引没有

Answer 2

你的SQL语句是什么？视图本身不支持索引，但你的表是支持索引的啊。

Answer 3

视图语句：
a表8条记录，b、c各20万条记录CREATE VIEW viewName AS selcet 需要的字段名 from ((`a` join `b` on((`a`.`code` = `b`.`code`))) join `c` on((`a`.`policyno` = `c`.`policyno`)))视图建立后，在视图上进行查询，语句大致如下：
select 需要的字段名 from viewName where 条件1=? and 条件2 in（1，2，3） and 条件3=？对于a,b,c这三个表，都是以id作为主键（即id无任何意义，只作为流水号）。好像作为了主键是有主键索引的吧？ 

Answer 4

CREATE VIEW viewName AS 
selcet 需要的字段名 
from (`a` join `b` on `a`.`code` = `b`.`code`) 
join `c` on `a`.`policyno` = `c`.`policyno`
where 条件1=? and 条件2 in（1，2，3） and 条件3=？先创建以下索引
b (code)
c (policyno)另外需要针对你的 条件1=? and 条件2 in（1，2，3） and 条件3=？进行分析。

Answer 5

不用建索引了，建了也没用。
重新根据应用设计表吧。如果实在没办法，考虑把a内容冗余如b，或者c，简化关联关系。

Answer 6

打开表的顺序只能是
a b c
a c b
b/c a c/b要么就不会出问题，
既然你出了问题，这个问题就不是简单的索引能解决的原则是越向右侧关联的结果集越小。 而你这个应用，显然不符合这个原则。再建索引也没啥意义。

Answer 7

selcet 需要的字段名 from ((`a` join `b` on((`a`.`code` = `b`.`code`))) join `c` on((`a`.`policyno` = `c`.`policyno`)))
这样单独执行快么？select 需要的字段名 
from (selcet 需要的字段名 
from ((`a` join `b` on((`a`.`code` = `b`.`code`))) join `c` on((`a`.`policyno` = `c`.`policyno`)))
)k
where 条件1=? and 条件2 in（1，2，3） and 条件3=？这样在code和policyno上加索引试试

Answer 8

视图有两种执行方式(组合查询和临时表查询) 估计您的SQL被优化器当成临时表查询(先按视图生成临时表,在从临时表中搜索满足where条件的记录)了哈

Answer 9

我explain后，显示的select_type是simple  这个是临时表吗？

Answer 10

不是可以这样察看执行计划select_options换成您的读取视图的语句EXPLAIN EXTENDED SELECT select_options;
show warnings;

Answer 11

那
select 需要的字段名 
from (selcet 需要的字段名 
from ((`a` join `b` on((`a`.`code` = `b`.`code`))) join `c` on((`a`.`policyno` = `c`.`policyno`)))
)k
where 条件1=? and 条件2 in（1，2，3） and 条件3=？这样执行快么 
慢的话 试试code和policyno上加索引试试

Answer 12

不行啊，还是非常慢
现在总结一下哈：
使用视图（或嵌套视图的sql语句）就会很慢，大概需要五六分钟，甚至有时候会失败。
但如果直接操作要三四分钟，快了几分钟。
假如A join B ON(a.code=b.code)的话，在A中建立索引跟没建立速度还是一样，但在B中不建立的话需要三四分钟，建立后只要五六秒。速度越来越快，差距越来越大。
现在就极度迷惑：
1.视图不是被数据库优化了吗？应该会快很多的啊，但为什么却极度缓慢？
2.为什么在B中建立索引会快那么多，而在其他地方建却没什么作用？（A中二十多万条记录，B中接近一百多条，不是说在记录数少的表中不适宜建索引的吗？）

Answer 13

贴你的EXPLAIN结果，否则别人无法帮你分析。或者有人有兴趣的话可以贴出自己的猜测问题说明越详细，回答也会越准确！参见如何提问。（提问的智慧）

Answer 14

如果楼主贴出explain 结果，会发现
20W
1
20W
这样的结果。楼主，提问题，不发关键性的东西，谁能帮上你。
上面几位都告诉你看下分析结果，愣是不贴，还问个屁