CREATE VIEW [Star].[VStar]
WITH SCHEMABINDING
AS
SELECT A.[UserId]
,A.[StarNo]
,[Declaration]
,[CreatedDataTime]
,[RelatedAlbumId]
,[Approved]
,[Mobile]
,[Gender]
,[MobileCity]
,[Score]
,[WebVote]
,[WapVote]
,[MessageVote]
FROM [Star].[Star] AS A INNER JOIN
[Star].[StarScore] AS B ON A.[UserId]=B.[UserId]
CREATE UNIQUE CLUSTERED INDEX [PK_VStar] ON [Star].[VStar]
(
[UserId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [UserData]
CREATE UNIQUE NONCLUSTERED INDEX [IX_Star_StarNo] ON [Star].[VStar]
(
[StarNo] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [UserData]
以上为SQLSERVER里面的视图,并包含创建了索引提高查询的效率,那么Mysql里对视图的性能如何保证?Mysql视图有物化概念吗?如果需要创建Group by的视图,如何提高性能?
create view name as select ... group by ..; 就是要优化后面的sql来提高group by.
CREATE TABLE `Star` (
`UserId` int(11) NOT NULL,
`StarNo` int(11) NOT NULL,
`Declaration` varchar(200) CHARACTER SET utf8 NOT NULL,
`CreatedDataTime` datetime NOT NULL,
`RelatedAlbumId` int(11) NOT NULL,
`Approved` char(10) COLLATE utf8_bin NOT NULL,
`Mobile` bigint(20) NOT NULL,
`Gender` tinyint(3) unsigned NOT NULL,
`MobileCity` varchar(50) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`UserId`),
KEY `IX_Star_Approved_UserId` (`Approved`,`UserId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CREATE TABLE `StarScore` (
`UserId` int(11) NOT NULL,
`StarNo` int(11) NOT NULL,
`Score` int(11) NOT NULL,
`WebVote` int(11) NOT NULL,
`WapVote` int(11) NOT NULL,
`MessageVote` int(11) NOT NULL,
PRIMARY KEY (`UserId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
建如下视图:DROP VIEW IF EXISTS `VStar`$$CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `VStar` AS
SELECT
`A`.`UserId` AS `UserId`,
`A`.`StarNo` AS `StarNo`,
`A`.`Declaration` AS `Declaration`,
`A`.`CreatedDataTime` AS `CreatedDataTime`,
`A`.`RelatedAlbumId` AS `RelatedAlbumId`,
`A`.`Approved` AS `Approved`,
`A`.`Mobile` AS `Mobile`,
`A`.`Gender` AS `Gender`,
`A`.`MobileCity` AS `MobileCity`,
`B`.`Score` AS `Score`,
`B`.`WebVote` AS `WebVote`,
`B`.`WapVote` AS `WapVote`,
`B`.`MessageVote` AS `MessageVote`
FROM (`Star` `A`
JOIN `StarScore` `B`
ON ((`A`.`UserId` = `B`.`UserId`)))$$如果执行一下查询:SELECT UserId,StarNo FROM VStar
WHERE Approved LIKE '1%'
ORDER BY Score DESC
Using where; Using temporary; Using filesort:不能避免排序?
大家有兴趣可以研究下:http://code.google.com/p/flexviews/