Mysql中视图DDL中使用WHERE ...AND 和 JOIN...ON在查询时的效率比较

一、创建视图时使用WHERE...AND

1、视图DDL

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `readorderToAcceptView` AS SELECT
`cl`.`id` AS `order_id`,
`cl`.`status` AS `order_status`,
`cl`.`assign_time` AS `ordered_time`,
`cl`.`order_no` AS `order_no`,
`p`.`name` AS `name`,
`p`.`sex` AS `sex`,
`p`.`age` AS `age`,
`p`.`phone` AS `phone`,
concat( `p`.`province_name`, `p`.`city_name`, `p`.`district_name` ) AS `area`,
`c`.`patient_no` AS `patient_no`,
`c`.`capsule_no` AS `capsule_no`,
`c`.`inspection_subjects` AS `inspection_subjects`,
`c`.`medica_history` AS `medica_history`,
`c`.`symptom_complaint` AS `symptom_complaint`,
`c`.`remarks` AS `remarks`,
`c`.`expert_id` AS `expert_id`,
`c`.`m_id` AS `m_id`,
`cl`.`create_time` AS `inspected_time`,
`cl`.`expected_time` AS `expected_time`,
`cl`.`check_no` AS `check_no`,
`cl`.`note` AS `note`,
`imf`.`local_url` AS `local_url`,
`imf`.`server_url` AS `server_url`,
`imf`.`fsize` AS `fsize`,
`ept`.`username` AS `username`,
`ept`.`cellphone` AS `cellphone`,
`org`.`name` AS `organization_name` 
FROM
    (
        ( ( ( ( ( `patient` `p` JOIN `case` `c` ) JOIN `checklist` `cl` ) JOIN `imagefile` `imf` ) JOIN `operatingrecord` `opr` ) JOIN `expect` `ept` )
        JOIN `organization` `org` 
    ) 
WHERE
    (
        ( `cl`.`check_no` = `imf`.`check_no` ) 
        AND ( `cl`.`patient_id` = `p`.`id` ) 
        AND ( `p`.`id` = `c`.`patient_id` ) 
        AND ( `c`.`expert_id` = `ept`.`id` ) 
        AND ( `cl`.`case_id` = `c`.`id` ) 
        AND ( `c`.`inspection_organization_id` = `org`.`id` ) 
    ) 
GROUP BY
    `cl`.`check_no`

2、查询视图

SELECT * FROM readordertoacceptview;

3、耗时

       2.472s

二、创建视图时使用JOIN...ON

1、视图DDL

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `readorderToAcceptViewOpt` AS SELECT
`cl`.`id` AS `order_id`,
`cl`.`status` AS `order_status`,
`cl`.`assign_time` AS `ordered_time`,
`cl`.`order_no` AS `order_no`,
`p`.`name` AS `name`,
`p`.`sex` AS `sex`,
`p`.`age` AS `age`,
`p`.`phone` AS `phone`,
concat( `p`.`province_name`, `p`.`city_name`, `p`.`district_name` ) AS `area`,
`c`.`patient_no` AS `patient_no`,
`c`.`capsule_no` AS `capsule_no`,
`c`.`inspection_subjects` AS `inspection_subjects`,
`c`.`medica_history` AS `medica_history`,
`c`.`symptom_complaint` AS `symptom_complaint`,
`c`.`remarks` AS `remarks`,
`c`.`expert_id` AS `expert_id`,
`c`.`m_id` AS `m_id`,
`cl`.`create_time` AS `inspected_time`,
`cl`.`expected_time` AS `expected_time`,
`cl`.`check_no` AS `check_no`,
`cl`.`note` AS `note`,
`imf`.`local_url` AS `local_url`,
`imf`.`server_url` AS `server_url`,
`imf`.`fsize` AS `fsize`,
`ept`.`username` AS `username`,
`ept`.`cellphone` AS `cellphone`,
`org`.`name` AS `organization_name` 
FROM
    (
        (
            (
                (
                    ( `patient` `p` JOIN `case` `c` ON ( ( `p`.`id` = `c`.`patient_id` ) ) )
                    JOIN `checklist` `cl` ON ( ( ( `cl`.`patient_id` = `p`.`id` ) AND ( `cl`.`case_id` = `c`.`id` ) ) ) 
                )
                LEFT JOIN `imagefile` `imf` ON ( ( `cl`.`check_no` = `imf`.`check_no` ) ) 
            )
            JOIN `expect` `ept` ON ( ( `c`.`expert_id` = `ept`.`id` ) ) 
        )
        JOIN `organization` `org` ON ( ( `c`.`inspection_organization_id` = `org`.`id` ) ) 
    ) 
GROUP BY
    `cl`.`check_no`

2、查询视图

SELECT * FROM readorderToAcceptViewOpt;

3、耗时

    0.684s

 三、结语

        在其他环境一致的条件下，在创建视图时使用JOIN...ON 要比使用WHERE...AND的查询效率高。