下面的视图包含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;由于上面提到的视图经常被使用,所以应用程序的速度会受到很大影响。有什么方法可以加速它的性能吗?
发布于 2015-06-12 11:58:33
什么是实体化视图?实体化视图(MV)是查询的预计算(实体化)结果。与简单视图不同,实体化视图的结果存储在某个位置,通常存储在表中。实体化视图用于需要立即响应且实体化视图所基于的查询需要很长时间才能产生结果的情况。实例化视图必须偶尔刷新一次。这取决于要求、刷新实体化视图的频率以及其内容的实际程度。基本上,实体化视图可以立即刷新或延迟刷新,也可以完全刷新或刷新到某个时间点。MySQL本身并不提供物化视图。但是自己构建物化视图是很容易的。
请在此处查看更多信息http://www.fromdual.com/mysql-materialized-views
尽管这是你想要读到的
https://stackoverflow.com/questions/30795041
复制相似问题