有两个表table1,table2,建表语句如下:
create table table1 (
a int,
b int
);
CREATE INDEX ia ON table1(a);
create table table2 (
a int,
b int
);
CREATE INDEX ia ON table2(a);
还有一个视图,创建语句如下:
create view testview1(a,b) as
select a,b from table1
union all
select a,b from table2;
查询表的执行计划如下:
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "table1" "ref" "ia" "ia" "5" "const" "1" \N
查询视图时的执行计划如下:
select * from testview1 where a=1;
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "" "ref" "" "" "386" "const" "10" "Using index condition"
"2" "DERIVED" "cpehg_service_detail_cache" "ALL" \N \N \N \N "42515" \N
"3" "UNION" "cpehg_service_detail_his" "ALL" \N \N \N \N "5940737" \N
\N "UNION RESULT" "" "ALL" \N \N \N \N \N "Using temporary"
可以看到查单个表的时候走索引了,但是查询视图的时候没有走索引,请问这个该如何优化?
谢谢!