mysql - MySQL中的View性能

我已经在一个表上创建了一个视图，如下所示：

CREATE OR REPLACE VIEW V_BLA AS
SELECT
    CONVERT(UIT_DNR USING latin1) AS internNr,
    UIT_SNR AS schuldnr,
    TRIM((SELECT SCH_OMS FROM BUSCHU WHERE SCH_DNR = UIT_DNR AND SCH_VNR = UIT_SNR)) AS omschrijving,
    CASE
        WHEN (SELECT TABDATA FROM BDTABE WHERE TABNR = 9000 AND VOLGNR = 1 AND TABTAAL = 0) = 1 THEN -- ALGEMEEN BESTAND
            (SELECT CONCAT(TRIM(SC_FNAAM), ' ', TRIM(SC_VNAAM)) FROM SDSCIN WHERE SC_NR = LEFT(UIT_SCHULDEISER, 4))
        ELSE
            (SELECT CONCAT(TRIM(SC_FNAAM), ' ', TRIM(SC_VNAAM)) FROM BUSCIN WHERE SC_NR = 100000 - UIT_SCHULDEISER)
    END AS schuldeiser,
    UIT_DATUM AS datum,
    UIT_VLGNR AS volgnummer,
    UIT_BEDRAG AS bedrag,
    CONVERT(IF(
        UIT_COBE_DAT = 0,
        NULL,
        CONCAT(
            RIGHT(UIT_COBE_DAT, 4), '-',
            IF(UIT_COBE_DAT < 10000000,
                CONCAT(MID(UIT_COBE_DAT, 2, 2), '-0', LEFT(UIT_COBE_DAT, 1)),
                CONCAT(MID(UIT_COBE_DAT, 3, 2), '-', LEFT(UIT_COBE_DAT, 2))
            ))), DATE) AS datumBetaallijst,
    TRIM(UIT_MEDEDELING) AS mededeling
FROM BUUITG;

问题是性能真的很差。当我尝试通过internNr选择一些行时，最多需要10秒才能生成输出。我创建了必要的索引：


BDTABE：在BDTABE（TABNR，VOLGNR）上创建索引BDTABE_TABNR_VOLGNR；
SDSCIN：在SDSCIN（SC_NR）上创建索引SDSCIN_SC_NR；
BUSCIN：在BUSCIN（SC_NR）上创建索引BUSCIN_SC_NR；


BUUITG上的UIT_DNR上的额外索引没有任何改善。

我做选择时的解释计划：

EXPLAIN SELECT * FROM V_BLA WHERE interNr = 'something';

+---+--------------------+------------+-----+------------------------+------------------------+----+-----------------------------------------------------------+--------+--------+-----------------------------------------------------+
| 1 |      PRIMARY       | <derived2> | ref |      <auto_key0>       |      <auto_key0>       | 22 |                           const                           |   10   | 100.00 |                     Using where                     |
+---+--------------------+------------+-----+------------------------+------------------------+----+-----------------------------------------------------------+--------+--------+-----------------------------------------------------+
| 2 | DERIVED            | BUUITG     | ALL |                        |                        |    |                                                           | 208498 | 100.00 |                                                     |
| 6 | DEPENDENT SUBQUERY | BUSCIN     | ref | BUSCIN_SC_NR           | BUSCIN_SC_NR           |  4 | func                                                      |      1 | 100.00 | Using index condition                               |
| 5 | DEPENDENT SUBQUERY |            |     |                        |                        |    |                                                           |        |        | Impossible WHERE noticed after reading const tables |
| 4 | SUBQUERY           | BDTABE     | ref | BDTABE_TABNR_VOLGNR    | BDTABE_TABNR_VOLGNR    | 14 | const,const                                               |      1 | 100.00 | Using where                                         |
| 3 | DEPENDENT SUBQUERY | BUSCHU     | ref | BUSCHU_SCH_VNR_SCH_DNR | BUSCHU_SCH_VNR_SCH_DNR | 12 | export_winsoc.BUUITG.UIT_SNR,export_winsoc.BUUITG.UIT_DNR |      1 | 100.00 |                                                     |
+---+--------------------+------------+-----+------------------------+------------------------+----+-----------------------------------------------------------+--------+--------+-----------------------------------------------------+

最佳答案

正如评论中已经指出的那样，我会将选择列表中的子查询作为简单联接或派生表移至from子句。我使用左联接，但可以根据您的要求和数据随意将它们更改为内部联接。请也检查联接条件，因为在某些情况下我不得不猜测哪些表位于哪个表中。

CREATE OR REPLACE VIEW V_BLA AS
SELECT
    CONVERT(UIT_DNR USING latin1) AS internNr,
    UIT_SNR AS schuldnr,
    TRIM(BUSCHU.SCH_OMS ) AS omschrijving,
    CASE
        WHEN BDTABE.TABDATA  = 1 THEN -- ALGEMEEN BESTAND
            CONCAT(TRIM(SDSCIN.SC_FNAAM), ' ', TRIM(SDSCIN.SC_VNAAM))
        ELSE
            CONCAT(TRIM(BUSCIN.SC_FNAAM), ' ', TRIM(BUSCIN.SC_VNAAM)
    END AS schuldeiser,
    UIT_DATUM AS datum,
    UIT_VLGNR AS volgnummer,
    UIT_BEDRAG AS bedrag,
    CONVERT(IF(
        UIT_COBE_DAT = 0,
        NULL,
        CONCAT(
            RIGHT(UIT_COBE_DAT, 4), '-',
            IF(UIT_COBE_DAT < 10000000,
                CONCAT(MID(UIT_COBE_DAT, 2, 2), '-0', LEFT(UIT_COBE_DAT, 1)),
                CONCAT(MID(UIT_COBE_DAT, 3, 2), '-', LEFT(UIT_COBE_DAT, 2))
            ))), DATE) AS datumBetaallijst,
    TRIM(UIT_MEDEDELING) AS mededeling
FROM BUUITG
LEFT JOIN BUSCHU ON BUSCHU.SCH_DNR=BUUITG.UIT_DNR AND BUSCHU.SCH_VNR=BUUITG.UIT_VNR
LEFT JOIN BDTABE ON BDTABE.TABNR = 9000 AND BDTABE.VOLGNR = 1 AND BDTABE.TABTAAL = 0 --did not find any common fields between the 2 tables
LEFT JOIN SDSCIN ON SDSCIN.SC_NR = LEFT(BUUITGUIT_SCHULDEISER, 4)
LEFT JOIN BUSCIN ON BUSCIN.SC_NR = 100000 - BUUITG.UIT_SCHULDEISER