Materialised view (MV), bir veya birden fazla tablodan belirli kriterlerle seçilmiş veriler ile olurşturulmuş tablodur. Bu tabloyu veritabanındaki diğer tablolar gibi listede görebilirsiniz. Asıl kullanılış amaçları, sorgulama yaparken mümkün olduğunca performans düşüşlerini önlemek içindir. İsteğe bağlı olarak yenilenirler ama içlerinde bulunan bilgi çoğunlukla eski olacaktır. MVs'leri de normal tablolar gibi sorgulayabilirsiniz. Not: Eğer materialised view içindeki alanlar için "benzersizlik" özelliği kullanılmış ise, bu sorgulamanın yanlış sıralama yaratmasına sebep olabilir, bu nedenle "benzersizlik" özelliğinin kullanılmaması tavsiye edilir.


Test tabloları


Materialised viewler ile çalışırken, user ve activity isimlerindeki tabloları kullanacağız.


User tablosu


CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username_unique` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO user
(username, password)
VALUES
('one_username', 'one_password'),
('two_username', 'two_password'),
('three_username', 'three_password'),
('four_username', 'four_password'),
('five_username', 'five_password');

mysql> SELECT * FROM user;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 1 | one_username | one_password |
| 2 | two_username | two_password |
| 3 | three_username | three_password |
| 4 | four_username | four_password |
| 5 | five_username | five_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

Activity tablosu


CREATE TABLE `activity` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`timestamp` datetime NOT NULL,
`url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`user_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_activity_1_idx` (`user_id`),
CONSTRAINT `fk_activity_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO activity
(ip, timestamp, url, user_id)
VALUES
('1.1.1.1', '2015-04-01 18:10:59', '/url_one', 1),
('1.1.1.1', '2015-04-02 10:06:11', '/url_two', 1),
('2.2.2.2', '2015-04-01 21:00:00', '/url_one', 2),
('2.2.2.2', '2015-04-03 23:01:40', '/url_one', 2),
('3.3.3.3', '2015-04-02 02:23:30', '/url_one', 3),
('4.4.4.4', '2015-04-01 03:00:14', '/url_two', 4),
('5.5.5.5', '2015-04-01 13:30:33', '/url_one', 5);

mysql> SELECT * FROM activity;
+----+---------+---------------------+----------+---------+
| id | ip | timestamp | url | user_id |
+----+---------+---------------------+----------+---------+
| 1 | 1.1.1.1 | 2015-04-01 18:10:59 | /url_one | 1 |
| 2 | 1.1.1.1 | 2015-04-02 10:06:11 | /url_two | 1 |
| 3 | 2.2.2.2 | 2015-04-01 21:00:00 | /url_one | 2 |
| 4 | 2.2.2.2 | 2015-04-03 23:01:40 | /url_one | 2 |
| 5 | 3.3.3.3 | 2015-04-02 02:23:30 | /url_one | 3 |
| 6 | 4.4.4.4 | 2015-04-01 03:00:14 | /url_two | 4 |
| 7 | 5.5.5.5 | 2015-04-01 13:30:33 | /url_one | 5 |
+----+---------+---------------------+----------+---------+
7 rows in set (0.00 sec)

Tek bir tablo üzerinden bir materialised view yaratıp sonrada sorgulayalım


Asıl tablo versiyonu


mysql> SELECT * FROM user ORDER BY username;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 1 | one_username | one_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
+----+----------------+----------------+
5 rows in set (0.04 sec)

Materialised view versiyonu


# MV tablo yaratalım.
CREATE TABLE `user_mv` (
`id` int(10) unsigned NOT NULL,
`username` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(40) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

# MV'yi sıralanmış bilgilerle dolduralım.
INSERT INTO user_mv
SELECT
id,
username,
password
FROM user
ORDER BY username;

# Görüdüğü gibi ORDER BY kullanılmamış.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 1 | one_username | one_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

Join ile bağlanmış iki tablo üzerinden bir materialised view yaratıp sonrada sorgulayalım


Asıl tablo versiyonu


mysql> SELECT
-> user.username,
-> activity.timestamp,
-> activity.url
-> FROM user
-> INNER JOIN activity ON activity.user_id = user.id
-> ORDER BY
-> user.username ASC,
-> activity.timestamp DESC;
+----------------+---------------------+----------+
| username | timestamp | url |
+----------------+---------------------+----------+
| five_username | 2015-04-01 13:30:33 | /url_one |
| four_username | 2015-04-01 03:00:14 | /url_two |
| one_username | 2015-04-02 10:06:11 | /url_two |
| one_username | 2015-04-01 18:10:59 | /url_one |
| three_username | 2015-04-02 02:23:30 | /url_one |
| two_username | 2015-04-03 23:01:40 | /url_one |
| two_username | 2015-04-01 21:00:00 | /url_one |
+----------------+---------------------+----------+
7 rows in set (0.00 sec)

Materialised view versiyonu


# MV tablo yaratalım.
CREATE TABLE `user_activity_mv` (
`username` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`timestamp` datetime NOT NULL,
`url` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

# MV'yi sıralanmış bilgilerle dolduralım.
INSERT INTO user_activity_mv
SELECT
user.username,
activity.timestamp,
activity.url
FROM user
INNER JOIN activity ON activity.user_id = user.id
ORDER BY
user.username ASC,
activity.timestamp DESC;

# Görüdüğü gibi ORDER BY kullanılmamış.
mysql> SELECT * FROM user_activity_mv;
+----------------+---------------------+----------+
| username | timestamp | url |
+----------------+---------------------+----------+
| five_username | 2015-04-01 13:30:33 | /url_one |
| four_username | 2015-04-01 03:00:14 | /url_two |
| one_username | 2015-04-02 10:06:11 | /url_two |
| one_username | 2015-04-01 18:10:59 | /url_one |
| three_username | 2015-04-02 02:23:30 | /url_one |
| two_username | 2015-04-03 23:01:40 | /url_one |
| two_username | 2015-04-01 21:00:00 | /url_one |
+----------------+---------------------+----------+
7 rows in set (0.00 sec)

Materialised viewleri yenileme


Materialised viewlerin nasıl veya ne sıklıkla yenileneceği size kalmış. İsteğe bağlı olarak stored procedures, triggers, cron jobs veya manuel yenileme tercih edilebilir.


Stored procedure


# MV'nin mevcut durumu.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 1 | one_username | one_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

# Farlı bir ORDER BY kullanıp, stored procedure ile MV'yi sıralanmış bilgilerle dolduralım.
DROP PROCEDURE IF EXISTS user_mv_sp;

DELIMITER $$

CREATE PROCEDURE user_mv_sp()
BEGIN
TRUNCATE TABLE user_mv;

INSERT INTO user_mv
SELECT
id,
username,
password
FROM user
ORDER BY id DESC;
END;
$$

DELIMITER ;

# Stored procedure'yi çalıştır.
mysql> CALL user_mv_sp();
Query OK, 5 rows affected (0.12 sec)

# MV'nin yeni durumuna bakalım.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
| 1 | one_username | one_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

Triggers (after insert, delete and update)


# MV'nin mevcut durumu.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
| 1 | one_username | one_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

# Tablo "user" üzerinde "After Insert" triggeri yaratalım.
DROP TRIGGER IF EXISTS after_insert;

DELIMITER $$

CREATE TRIGGER after_insert
AFTER INSERT ON user
FOR EACH ROW
BEGIN
INSERT INTO user_mv
(username, password)
VALUES
(NEW.id, NEW.username, NEW.password);
END;
$$

DELIMITER ;

# Tablo "user" içine bir kayıt girebilim.
INSERT INTO user (username, password) VALUES ('six_username', 'six_password');

# MV'nin yeni durumuna bakalım.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
| 1 | one_username | one_password |
| 6 | six_username | six_password |
+----+----------------+----------------+
6 rows in set (0.00 sec)

# Tablo "user" üzerinde "After Delete" triggeri yaratalım.
DROP TRIGGER IF EXISTS after_delete;

DELIMITER $$

CREATE TRIGGER after_delete
AFTER DELETE ON user
FOR EACH ROW
BEGIN
DELETE FROM user_mv
WHERE id = OLD.id;
END;
$$

DELIMITER ;

# Tablo "user" içinden bir kayıt silelim.
DELETE FROM user WHERE id = 6;

# MV'nin yeni durumuna bakalım.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
| 1 | one_username | one_password |
+----+----------------+----------------+
5 rows in set (0.00 sec)

# Tablo "user" üzerinde "After Update" triggeri yaratalım.
DROP TRIGGER IF EXISTS after_update;

DELIMITER $$

CREATE TRIGGER after_update
AFTER UPDATE ON user
FOR EACH ROW
BEGIN
UPDATE user_mv
SET
username = NEW.username,
password = NEW.password
WHERE
id = OLD.id;
END;
$$

DELIMITER ;

# Tablo "user" içindeki bir kaydı yenileyelim.
UPDATE user SET password = 'one_passwords' WHERE id = 1;

# MV'nin yeni durumuna bakalım.
mysql> SELECT * FROM user_mv;
+----+----------------+----------------+
| id | username | password |
+----+----------------+----------------+
| 5 | five_username | five_password |
| 4 | four_username | four_password |
| 3 | three_username | three_password |
| 2 | two_username | two_password |
| 1 | one_username | one_passwords |
+----+----------------+----------------+
5 rows in set (0.00 sec)