mysql - 加快 MySQL 中的 View 速度

Question

以下 View 包含 2000 行(不算多)，但获取值大约需要 4 秒。

CREATE 
ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER
VIEW `saving_account_ledger_view_new1` AS
SELECT 
    (CASE
        WHEN
            ((`mt`.`Tr_Type` = 'DJV')
                OR (`mt`.`Tr_Type` = 'DCA')
                OR (`mt`.`Tr_Type` = 'DBK'))
        THEN
            'Deposit'
        ELSE (CASE
            WHEN
                ((`mt`.`Tr_Type` = 'WJV')
                    OR (`mt`.`Tr_Type` = 'WCA')
                    OR (`mt`.`Tr_Type` = 'WBK'))
            THEN
                'Withdraw'
        END)
    END) AS `Particulars`,
    `mt`.`SBAc_No` AS `SBAc_No`,
    `mt`.`Tr_Date` AS `Tr_Date`,
    `mt`.`Tr_No` AS `Tr_No`,
    SUM((CASE
        WHEN (`mt`.`Tr_Type` = 'DCA') THEN `mt`.`Pri_Amt`
        WHEN (`mt`.`Tr_Type` = 'DJV') THEN `mt`.`Pri_Amt`
        WHEN (`mt`.`Tr_Type` = 'DBK') THEN `mt`.`Pri_Amt`
        ELSE 0
    END)) AS `Deposit`,
    SUM((CASE
        WHEN (`mt`.`Tr_Type` = 'WCA') THEN `mt`.`Pri_Amt`
        WHEN (`mt`.`Tr_Type` = 'WJV') THEN `mt`.`Pri_Amt`
        WHEN (`mt`.`Tr_Type` = 'WBK') THEN `mt`.`Pri_Amt`
        ELSE 0
    END)) AS `Withdraw`,
    (IFNULL((SELECT 
                    SUM((CASE
                            WHEN
                                ((`mt2`.`Tr_Type` = 'DJV')
                                    OR (`mt2`.`Tr_Type` = 'DCA')
                                    OR (`mt2`.`Tr_Type` = 'DBK'))
                            THEN
                                `mt2`.`Pri_Amt`
                            ELSE 0
                        END))
                FROM
                    `sb_loan_trans` `mt2`
                WHERE
                    ((`mt2`.`Tr_Date` <= `mt`.`Tr_Date`)
                        AND (`mt2`.`SBAc_No` = `mt`.`SBAc_No`)
                        AND (`mt2`.`Tr_No` < `mt`.`Tr_No`))),
            0) - IFNULL((SELECT 
                    SUM((CASE
                            WHEN
                                ((`mt2`.`Tr_Type` = 'WJV')
                                    OR (`mt2`.`Tr_Type` = 'WCA')
                                    OR (`mt2`.`Tr_Type` = 'WBK'))
                            THEN
                                `mt2`.`Pri_Amt`
                            ELSE 0
                        END))
                FROM
                    `sb_loan_trans` `mt2`
                WHERE
                    ((`mt2`.`Tr_Date` <= `mt`.`Tr_Date`)
                        AND (`mt2`.`SBAc_No` = `mt`.`SBAc_No`)
                        AND (`mt2`.`Tr_No` < `mt`.`Tr_No`))),
            0)) AS `Balance`
FROM
    `sb_loan_trans` `mt`
GROUP BY `mt`.`Tr_Date` , `mt`.`Tr_No` , `mt`.`SBAc_No` , `mt`.`Tr_Type`

表结构如下:-

CREATE TABLE `sb_loan_trans` (
`Tr_No` bigint(20) NOT NULL,
`SBAc_No` bigint(20) NOT NULL,
`Tr_Date` datetime NOT NULL,
`Tr_Type` char(10) NOT NULL,
`Pri_Amt` double NOT NULL,
`Int_Amt` double NOT NULL DEFAULT '0',
`Penal_Int_Amt` double NOT NULL DEFAULT '0',
`FA_Pri` bigint(20) NOT NULL DEFAULT '0',
`FA_Int` bigint(20) NOT NULL DEFAULT '0',
`Fa_Penal_Int` bigint(20) NOT NULL DEFAULT '0',
`Recept_No` varchar(10) DEFAULT '0',
`Disurb_Code` int(11) DEFAULT '0',
`CREATEDID` char(10) DEFAULT NULL,
`CREATEDDATETIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`UPDATEDID` char(10) DEFAULT NULL,
`UPDATEDDATETIME` datetime DEFAULT NULL,
PRIMARY KEY (`Tr_No`),
KEY `IDX_SB_LN1` (`Tr_Date`,`Tr_No`,`SBAc_No`,`Tr_Type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

由于上述 View 被频繁使用，应用程序的速度受到很大影响。有什么办法可以加快其性能吗？

Answer 1

WHAT IS A MATERIALIZED VIEW? A Materialized View (MV) is the pre-calculated (materialized) result of a query. Unlike a simple VIEW the result of a Materialized View is stored somewhere, generally in a table. Materialized Views are used when immediate response is needed and the query where the Materialized View bases on would take to long to produce a result. Materialized Views have to be refreshed once in a while. It depends on the requirements how often a Materialized View is refreshed and how actual its content is. Basically a Materialized View can be refreshed immediately or deferred, it can be refreshed fully or to a certain point in time. MySQL does not provide Materialized Views by itself. But it is easy to build Materialized Views yourself.

在这里查看更多内容 http://www.fromdual.com/mysql-materialized-views

虽然这就是你想读的内容