求一条多表联查SQL语句的优化。

[chchgao]

CSDN的朋友们，大家好！


我在工作中遇到一个问题，恳求大家的帮助。


先说说表的情况。我有个查询，涉及到这几个表格：

1）patient 表， 记录共 707832 条。
2）case_event 表， 记录共 2975352 条。
3）case_main 表， 记录共 2975056 条。
4）orders 表， 记录共 3043243 条。


我有一个几个表联查的语句。是这样的：

SELECT pa_nhi FROM patient WHERE pa_pno IN (SELECT cs_pno FROM case_main WHERE cs_serial IN (SELECT ce_cs_serial FROM case_event WHERE ce_serial IN (SELECT or_event_serial FROM orders WHERE or_accession_no = '809862')));

能达到目的，但是速度上不是很快。根据7次查询平均值（\timing测算出来的）是1494.57ms


根据同事的建议，我把它改成了另外一种写法：

SELECT pa_nhi FROM patient, case_main, case_event, orders WHERE or_event_serial = ce_serial AND ce_cs_serial = cs_serial AND cs_pno = pa_pno AND or_accession_no = '809862';

速度上有所提高，但是并不显著。根据7次查询平均值是1461.86ms

两者相差32.71ms。

现在这几个表格是已经固定的了，从改进表结构的角度，下手比较难。

请问大家，还有没有比较好的建议，针对这种几个表联查的情况，可以显著提高速度的？



恳请大家提些意见，谢谢大家！

[WWWWA]

将IN->INNER JOIN，在连接字段上建立索引试试

[WWWWA]

将IN->INNER JOIN，在连接字段上建立索引试试

[ACMAIN_CHM]

SELECT pa_nhi FROM patient, case_main, case_event, orders WHERE or_event_serial = ce_serial AND ce_cs_serial = cs_serial AND cs_pno = pa_pno AND or_accession_no = '809862';

这个应该可以了。 贴出你的 explain select 及相关表的确 show index from xx 以供分析。

[chchgao]

[Quote=引用 1 楼 的回复:]

将IN->INNER JOIN，在连接字段上建立索引试试
[/Quote]


朋友你好。谢谢你的帮助！！

我把我的查询语句改成了如下的形式，请帮忙看看这个是不是你的本意？

SELECT pa_nhi FROM patient INNER JOIN case_main ON cs_pno = pa_pno INNER JOIN case_event ON ce_cs_serial = cs_serial INNER JOIN orders ON or_event_serial = ce_serial AND or_accession_no = '1773819-MR';

经过测试，按照你的建议修改以后，查询速度又有了一定的提高。7次查询平均速度是： 1463.29ms

比我的那个SQL速度快了 31.28ms，但是速度和第二个SQL相近。


在表上做索引的那个，我需要跟同事商量一下。谢谢你的建议。如果是真的可以做了索引的话，我会把测试结果发回来的。再次谢谢。

[chchgao]

[Quote=引用 3 楼 的回复:]

SELECT pa_nhi FROM patient, case_main, case_event, orders WHERE or_event_serial = ce_serial AND ce_cs_serial = cs_serial AND cs_pno = pa_pno AND or_accession_no = '809862';

这个应该可以了。 贴出你的 explain s……
[/Quote]



好的。谢谢您的建议。。。请稍等。我先下楼一下。等一下贴过来。

[chchgao]

[Quote=引用 3 楼 的回复:]

SELECT pa_nhi FROM patient, case_main, case_event, orders WHERE or_event_serial = ce_serial AND ce_cs_serial = cs_serial AND cs_pno = pa_pno AND or_accession_no = '809862';

这个应该可以了。 贴出你的 explain s……
[/Quote]


回来了，先贴表结构：

1) orders 表格。
Table "public.orders"
Column | Type | Modifiers
--------------------------+-----------------------------+----------------------------------------------------------------------
or_serial | integer | not null default nextval(('"orders_or_serial_seq"'::text)::regclass)
or_event_serial | integer |
or_status | character(1) |
or_status_change | timestamp without time zone |
or_staff | integer |
or_machine | integer |
or_body_part | character(2) |
or_ex_type | character(2) |
or_rf_serial | integer |
or_group_type | character(1) |
or_report_serial | integer |
or_amended_report | integer |
or_assigned | character(1) |
or_link_serial | integer |
or_dor | timestamp without time zone |
or_dor_staff | integer |
or_start | timestamp without time zone |
or_end | timestamp without time zone |
or_start_staff | integer |
or_accession_no | character(256) |
or_ready_to_report | timestamp without time zone |
or_ready_to_report_staff | integer |
or_typed_by | timestamp without time zone |
or_reported_by | timestamp without time zone |
or_verified_by | timestamp without time zone |
Indexes:
"orders_pkey" PRIMARY KEY, btree (or_serial)
"ix_or2" btree (or_event_serial)
"ix_or3" btree (or_report_serial)
"ix_or4" btree (or_status_change)




2）case_event 表格

Table "public.case_event"
Column | Type | Modifiers
-----------------------+-----------------------------+----------------------------------------------------------------
ce_serial | integer | not null default nextval('case_event_ce_serial_seq'::regclass)
ce_cs_serial | integer |
ce_type | character(1) |
ce_start | timestamp without time zone |
ce_end | timestamp without time zone |
ce_pat_location | character(20) |
ce_pat_type | character(3) |
ce_description | character varying(40) |
ce_status | character(1) |
ce_pat_condition | character(2) |
ce_site | character(4) |
ce_urgency | character(1) |
ce_confirm_receipt | character(1) |
ce_staff | integer |
ce_dor | timestamp without time zone |
ce_ac_key | integer |
ce_ac_type | character(1) |
ce_film_dest | character(1) |
ce_film_cd_serial | integer |
ce_extra_info | character varying(20) |
ce_film_delivery | character(1) |
ce_film_delivery_date | timestamp without time zone |
ce_consent | timestamp without time zone |
ce_consent_staff | integer |
ce_lmp | character(1) |
ce_lmp_date | date |
ce_attend_ltr_printed | timestamp without time zone |
ce_attend_ltr_staff | integer |
ce_external_case_id | character varying(20) |
ce_ur | character(16) |
ce_hospital | character(2) |
ce_rrs_serial | integer | default 0
Indexes:
"case_event_pkey" PRIMARY KEY, btree (ce_serial)
"ix_ce2" btree (ce_cs_serial)
"ix_ce3" btree (ce_start)




3）case_main 表格

Table "public.case_main"
Column | Type | Modifiers
--------------------+-----------------------------+---------------------------------------------------------------
cs_serial | integer | not null default nextval('case_main_cs_serial_seq'::regclass)
cs_pno | integer |
cs_rf_type | character(2) |
cs_description | character varying(40) |
cs_confidentiality | character(1) |
cs_notify_who | character(1) |
cs_notify_when | character(1) |
cs_end_date | date |
cs_status | character(1) |
cs_status_change | timestamp without time zone |
cs_staff | integer |
cs_type | character(2) |
cs_referrer | integer |
cs_rf_date | date |
Indexes:
"case_main_pkey" PRIMARY KEY, btree (cs_serial)
"ix_cs2" btree (cs_pno)




4）patient 表格

Table "public.patient"
Column | Type | Modifiers
----------------------+---------------+--------------------------------------------------------------------
pa_pno | integer | not null default nextval(('"patient_pa_pno_seq"'::text)::regclass)
pa_nhi | character(12) |
pa_title | character(1) |
pa_surname | character(30) |
pa_firstname | character(30) |
pa_middlename | character(30) |
pa_sex | character(1) |
pa_dob | date |
pa_dod | date |
pa_domicile | character(4) |
pa_address_serial | integer |
pa_residence | character(1) |
pa_ethnicity1 | character(2) |
pa_ethnicity2 | character(2) |
pa_ethnicity3 | character(2) |
pa_gp | integer |
pa_occupation | character(20) |
pa_impaired | character(2) |
pa_merged | integer |
pa_religion | character(3) |
pa_dor | date |
pa_alt_ref | character(15) |
pa_marital_status | character(1) |
pa_residency_date | date |
pa_birth_country | character(4) |
pa_entry_date | date |
pa_preferred_name | character(20) |
pa_gp_ad_serial | integer |
pa_vip | character(1) |
pa_consent_printed | character(1) |
pa_pacs_id | character(20) |
pa_consent | character(1) |
pa_preferred_surname | character(30) |
Indexes:
"patient_pkey" PRIMARY KEY, btree (pa_pno)
"ix_pa2" UNIQUE, btree (pa_nhi)
"ix_pa3" btree (pa_surname, pa_firstname)
"ix_pa4" btree (pa_surname, pa_middlename)
"ix_pa5" btree (pa_dob, pa_surname)
"ix_pa6" btree (pa_merged)



要去开会了Explain等一下奉上。。。对不起。