1、创建视图时，一定不能在索引上面使用函数，不然索引就不起效了
2、使用了左右连接时，以原来没有数据的一端为WHERE条件时，查询会慢很多
左连接有两种，一种是使得left join, 还有一种是使用(+)的语法方式
|
|
连接 |
(+) |
left join |
备注 |
|
select * from myview |
0.156 |
0.25 |
18.172 |
(+)优 |
|
select count(*) from myview |
2.313 |
2.516 |
2.297 |
|
|
select * from myview where sampleid is null |
0.953 |
0.235 |
18.344 |
(+)优 |
|
select count(*) from myview where sampleid is null |
0.797 |
30.406 |
4.453 |
left join优 |
|
select * from myview where applydate >= '20130529' |
0.282 |
0.219 |
0.281 |
|
|
select count(*) from myview where applydate >= '20130529' |
0.5 |
0.453 |
0.61 |
|
|
select * from myview where auditingdate >= '20130529' |
1.172 |
26.5 |
1.86 |
left join优 |
|
select count(*) from myview where auditingdate >= '20130529' |
1.047 |
27.656 |
0.297 |
left join优 |
|
select * from myview where patientName like '%%李%%' |
0.265 |
0.235 |
0.203 |
|
|
select count(*) from myview where patientName like '%%李%%' |
11.921 |
2.297 |
1.828 |
为什么连接会这么慢？ |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
奇怪，11月10号测试结果怎么和这个不一样了？