程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 基於存儲引擎MEMORY加觸發器的應用場景

基於存儲引擎MEMORY加觸發器的應用場景

編輯：關於MYSQL數據庫

需求解讀：某限時特賣B2C網站技術總負責人想采用一種簡單、開發成本極低且成熟可靠，能滿足前期針對特賣信息快速處理的支持，且要求不采用第三方MemCache或者編寫程序操作內存的方案，考慮到其業務特點：特賣期限高峰，不超過5000個特賣信息，且需要頻繁、快速修改的數據全為數值類型，且每條記錄可控制在100字節以內，為此建議其前期考慮使用MySQL支持的Memory引擎解決響應速度，外加觸發器的模式解決數據的安全性。基於此需求而編寫此篇技術文章，以幫助更多開發者，了解Memory引擎及觸發器的功效，多一種解決方案。

　　一.知識點：

　　1.存儲引擎MEMORY知識點

　　1.1 MEMORY支持的特性

引擎限制

內存表

事務

不支持

鎖類型

表級別

多版本控制

不支持

HASH索引

支持

全文索引

不支持

B樹索引

支持

數據緩存

N/A

索引緩存

N/A

族索引

不支持

數據加密

支持

數據庫集群

不支持

數據壓縮

不支持

外鍵

布支持

備份/基於時間點恢復

支持

復制

不支持

統計信息更新

支持

查詢緩存

支持

　　1.2 MEMORY引擎的存儲特性

　　MEMORY引擎的數據及索引數據都存儲於內存中，為此文件系統只會有一個單獨的表定義文件，例如: MEMMORY引擎的表：t_memory，在數據庫目錄下只有：t_memory.frm文件，正是由於其所有數據都存儲於內存中，除表定義信息有對應的實體文件存儲於磁盤上外，只要MySQLd服務不存在，則表中的數據全部丟失。

　　對MEMORY引擎表做DELETE刪除數據的操作，並不是真正刪除，而是標記為刪除狀態，只有新記錄INSERT且寫入同一張表才可重用，另外重建、重置、刪除表、MySQLd服務重啟，才會釋放掉被刪除數據所占的內存。

　　1.3 數據類型的支持

　　1>.除BLOB、TEXT及二者的變種外，其他類型都支持;

　　2>.采用固定長度字段類型，即VARCHAR(50) 等同於CHAR(50);

　　3>.支持創建自增序列的字段;

　　4>.字段允許為NULL，同時索引也允許包含為NULL類型的字段;

　　1.4 索引的支持

　　支持B-tree和HASH索引，每張MEMORY引擎表支持最多創建32個索引，每個索引允許最多包含16個字段，索引最大長度：500字節;

　　1.5 存儲空間范圍

　　MEMORY存儲引擎擁有的存儲空間，取決於設置全局變量：max_heap_table_size的值為多大。

　　二.觸發器的知識點

　　2.1 語法

　　當通過SQL(INSERT、UPDATE、DELETE)語句使表中的數據發生變化，能捕獲到此變化的程序，即是我們常用到的觸發器，是一種類似於存儲過程，而具有特殊作用的程序。

　　2.1.1創建

　　CREATE [DEFINER = { user | CURRENT_USER }]

　　TRIGGER trigger_name trigger_time trigger_event

　　ON tbl_name FOR EACH ROW trigger_stmt

　　2.1.2 刪除

　　DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

　　2.2 限制

　　1>.一個數據庫中不能有同名的觸發器程序；

　　2>.同一表上，對同一個事件的響應處理不能有二個及以上的觸發器；

　　3>.觸發器無法顯示調用執行，也無法像函數或存儲過程一樣顯示地傳遞參數；

　　4>.通過關鍵字OLD.column_name獲得的值不能通過SET命令修改，但是關鍵字NEW獲得的值能通過SET NEW.column_name=VALUE方式修改；

　　5>.觸發器的處理部分不能含有事務的關鍵字，例如：ROLLBACK等；

　　6>.創建了觸發器的表，若支持事務，則觸發器也會受事務執行成功還是失敗的影響，且觸發器程序執行成功還是失敗，也會影響事務的執行是成功還是失敗;若不支持事務，則也無法支持事務的回滾操作；

　　三.模擬的業務場景

　　1.對某表中部分符合規定要求的數據UPDATE業務操作量大，且要求響應時間短；

　　2.確保數據的安全性；

　　3.為滿足上述要求，且不增加額外的開發成本，需要從非MEMORY引擎表中讀取數據到MEMORY引擎表中；

　　4.對MEMORY引擎表中的數據，只有UPDATE業務操作；

　　5.通過觸發器的方式，更新InnoDB引擎表中的數據；

　測試過程如下：

　　1>.創建用於測試2張表，存儲引擎分別為：MEMORY、InnoDB

CREATE TABLE `t_memory` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(40) NOT NULL,
  PRIMARY KEY(`id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8  COLLATE 'utf8_general_ci';

CREATE TABLE `t_innodb` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(40) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE 'utf8_general_ci';

　　2>.添加幾條測試數據

INSERT INTO t_innodb(username) value('a'),('asdfasdf'),('q235423573sdf'),('afdhfhswqertqw');
INSERT INTO t_memory SELECT * from t_innodb;

　　3>.創建基於MEMORY引擎的表上的更新觸發器

DELIMITER &&
CREATE TRIGGER tri_memory_update AFTER UPDATE ON t_memory FOR EACH ROW
BEGIN
UPDATE t_innodb SET username=NEW.username WHERE username=OLD.username;
END &&
DELIMITER ;

　　4>.對比2張表中的數據

root@localhost : test 03:58:25> select * from t_memory;
+----+----------------+
| id | username       |
+----+----------------+
|  1 | a              |
|  2 | asdfasdf       |
|  3 | q235423573sdf  |
|  4 | afdhfhswqertqw |
+----+----------------+
4 rows in set (0.00 sec)

root@localhost : test 03:58:32> select * from t_innodb;
+----+----------------+
| id | username       |
+----+----------------+
|  1 | a              |
|  4 | afdhfhswqertqw |
|  2 | asdfasdf       |
|  3 | q235423573sdf  |
+----+----------------+
4 rows in set (0.00 sec)

　　5>.對MEMORY引擎表執行更新模擬操作

UPDATE t_memory SET username='769067806dfgh' WHERE ID=1;

　　6>.更新之後的數據對比

root@localhost : test 03:58:25> select * from t_memory;
+----+----------------+
| id | username       |
+----+----------------+
|  1 | 769067806dfgh  |
|  2 | asdfasdf       |
|  3 | q235423573sdf  |
|  4 | afdhfhswqertqw |
+----+----------------+
4 rows in set (0.00 sec)
root@localhost : test 03:58:32> select * from t_innodb;
+----+----------------+
| id | username       |
+----+----------------+
|  1 | 769067806dfgh  |
|  4 | afdhfhswqertqw |
|  2 | asdfasdf       |
|  3 | q235423573sdf  |
+----+----------------+
4 rows in set (0.00 sec)

　　7>.對於MySQLd服務直接KILL掉操作系統級別的進程及保護進程(備注：先KILL掉保護進程)

　　8>.重新啟動MySQLd服務，然後查看對比2張表中的數據

root@localhost : test 04:10:05> select * from t_innodb;
+----+----------------+
| id | username       |
+----+----------------+
|  1 | 769067806dfgh  |
|  4 | afdhfhswqertqw |
|  2 | asdfasdf       |
|  3 | q235423573sdf  |
+----+----------------+
4 rows in set (0.02 sec)
root@localhost : test 04:10:12> select * from t_memory;
Empty set (0.00 sec)

　　若是采用MEMORY引擎表支持在線業務，另外再使用觸發器或者類似觸發器的其他程序完成數據同步到實體表，那麼從MEMORY引擎特點、業務等角度提出以下建議：

　　1>.MEMORY引擎存儲表中，只使用數值類型、日期類型的字段且為TIMESTAMP類型，不要使用字符串類型；

　　2>.刪除MEMORY引擎表中的數據之後，使用語句ALTER TABLE tablename ENGINE=MEMORY釋放掉被刪除數據而占用的內存；

　　3>.只使用MEMORY引擎表支持UPDATE操作業務；

　　4>.對MEMORY引擎表的數據操作，最好是根據主鍵去完成，以便最快速度完成操作，而不影響其他線程的操作；

　　5>.不要是用MEMORY引擎表支持數據量過大的業務，比如數據量1G以上；

　　6>.要定期清理MEMORY引擎表中不需要的數據，以便騰出內存；

　　7>.參數max_heap_table_size的值要設置合理，考慮系統資源及數據量可能有多大；

　　8>.MEMORY引擎不支持事務，為此使用觸發器完成數據同步的工作，要考慮響應操作的時間點(BEFOR/AFTER)，由於操作可能會很頻繁，實體表建議使用支持事務的引擎，比如：InnoDB引擎；