출처: http://dev.mysql.com/doc/refman/5.6/en/create-view.html
Posted by Rafael Palacios on November 14, 2007
Simulating Materialized View
Materialized View와 유사하게 만들기.
Materialized views are not updated every time they are accessed; they behave as static tables hence are much faster than normal view. Materialized views are useful when the system performs lots of queries on the view while the original data changes infrequently.
Materialized view는 일반 뷰처럼 접근할 때 마다 업데이트 하지 않는다; 접근 할 때마다 갱신하지 않으므로 일반 뷰보다는 굉장히 빠르다(일반 뷰는 본래의 테이블(데이터)에 그 때 그 때 접근하는데 이 뷰는 일반 테이블처럼 저장소에 테이블을 딱 하니 만든다고 하네요). Materialized view는 본래 데이터가 잘 변하지 않는뷰에 대한 대량의 쿼리를 날릴 때 유용하게 사용된다.
Since MySQL currently doesn't support materialized views, here is simple way to simulate materialized views that consists on creating a static table and a robust updating script.
Since MySQL currently doesn't support materialized views, here is simple way to simulate materialized views that consists on creating a static table and a robust updating script.
MySQL은 현재 Materialized view를 지원하지 않는다, 하지만 간단한 방법으로 Materialized view와 유사하게 만들 수 있으므로 그 방법을 설명하겠다.
The tipical command for creating a normal view is:
The tipical command for creating a normal view is:
먼저 일반 뷰를 생성하는 전형적인 명령은 다음과 같다:
CREATE VIEW my_view AS SELECT <xxxxxx your select expression xxxxx>
In order to create the equivalent to a materialized view you create a table with the same name instead of a view.
Then you create the following MySQL command that can be run periodically, for example every night.
CREATE VIEW my_view AS SELECT <xxxxxx your select expression xxxxx>
In order to create the equivalent to a materialized view you create a table with the same name instead of a view.
Then you create the following MySQL command that can be run periodically, for example every night.
materialized view 와 유사하게 만들기 위해서 메일 밤 이런 식으로 주기적으로 아래 쿼리를 실행시켜야 한다(왜냐하면 Materialized view는 원본 테이블에 있는 내용이 변경이 되어도 Materialized view는 변경되지 않습니다).
CREATE TEMPORARY TABLE tmp_my_view SELECT <*** same select expression as before ***>
LOCK TABLE my_database.my_view WRITE;
DELETE FROM my_database.my_view;
INSERT INTO my_database.my_view SELECT * FROM tmp_my_view;
UNLOCK TABLES;
The previous MySQL script can be run in Unix/Linux system by adding the following command as a cron entry:
Unix나 리눅스에 해당 명령을 실행 시키기 위한 크론 명령은 다음과 같다:
mysql -u userid --password=XXXXX -D my_database < update_materialized_view.mysql >/dev/null 2>&1
mysql -u userid --password=XXXXX -D my_database < update_materialized_view.mysql >/dev/null 2>&1
Posted by Justin Rennell on July 2, 2009
I was able to simulate a materialized view off of a regular view by executing a simpler script than above.
With any regular view, "myRegularView":
나는 아래 와 같은 형태로 만들어서 쓰니깐 참고 하세요:
DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`;
CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;
Running this script at whatever interval is appropriate will create a new regular table with the contents of the view at the time of execution.
With any regular view, "myRegularView":
나는 아래 와 같은 형태로 만들어서 쓰니깐 참고 하세요:
DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`;
CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;
Running this script at whatever interval is appropriate will create a new regular table with the contents of the view at the time of execution.
Posted by Aaron Tavistock on July 9, 2009
> DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`;
> CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;
If you do this without locking the table you risk performing CRUD operations against the table while its missing, which will throw errors or show empty results when something is legitimately there. Obviously simply locking the table won't work, because a DROP TABLE will also drop the lock. Since RENAME TABLE locks all the tables involved and is typically a very quick operation, making a working table then using RENAME TABLE to swap the tables will work and remain functional throughout.
For example:
CREATE TABLE new_materialized_view SELECT * from regular_view;
RENAME TABLE materialized_view = old_materialized_view, new_materialized_view = materialized_view;
DROP TABLE IF EXISTS old_materialized_view;
> CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;
If you do this without locking the table you risk performing CRUD operations against the table while its missing, which will throw errors or show empty results when something is legitimately there. Obviously simply locking the table won't work, because a DROP TABLE will also drop the lock. Since RENAME TABLE locks all the tables involved and is typically a very quick operation, making a working table then using RENAME TABLE to swap the tables will work and remain functional throughout.
For example:
CREATE TABLE new_materialized_view SELECT * from regular_view;
RENAME TABLE materialized_view = old_materialized_view, new_materialized_view = materialized_view;
DROP TABLE IF EXISTS old_materialized_view;
다음검색