Meta Trader 資料庫優化

http://netkiller.github.io/journal/metatrader.html

Mr. Neo Chen (陳景峯), netkiller, BG7NYT


中國廣東省深圳市龍華新區民治街道溪山美地
518131
+86 13113668890


版權聲明

轉載請與作者聯繫,轉載時請務必標明文章原始出處和作者信息及本聲明。

文檔出處:
http://netkiller.github.io
http://netkiller.sourceforge.net

微信掃瞄二維碼進入 Netkiller 微信訂閲號

QQ群:128659835 請註明“讀者”

2014-06-20

摘要

目錄

1. 怎樣優化Meta Trades 資料庫

1.1. MT4_USERS 表

監控MT4用戶變化,隨時將狀態與我們的用戶表同步,下面是當交易賬號被刪除後,會將我們用戶資料庫的記錄狀態改為已刪除狀態

CREATE DEFINER=`dba`@`192.168.6.20` TRIGGER `MT4_USERS_AFTER_DELETE` AFTER DELETE ON `MT4_USERS` FOR EACH ROW BEGIN
	UPDATE `yourdb`.members SET `status` = 'Deleted' WHERE username = OLD.LOGIN;
END			
			

交易賬號狀態監控

CREATE DEFINER=`dba`@`192.168.6.20` TRIGGER `MT4_USERS_AFTER_UPDATE` AFTER UPDATE ON `MT4_USERS` FOR EACH ROW BEGIN
	IF OLD.ENABLE = '0' THEN
		UPDATE `yourdb`.members SET `status` = 'Disabled' WHERE username = OLD.LOGIN;
	ELSE
		UPDATE `yourdb`.members SET `status` = 'Enabled' WHERE username = OLD.LOGIN;
	END IF;
END
			

你也可以監控MT4的開戶

CREATE TRIGGER `MT4_USERS_AFTER_INSERT` AFTER INSERT ON `MT4_USERS` FOR EACH ROW BEGIN
	/*這裡寫入你的INSERT語句即可*/
END
			

1.2. MT4_TRADES 表的優化方案

我將TICKET主鍵去除,然後對MT4_TRADES表進行分區處理,因為我們所有的查詢都會涉及LOGIN欄位,所以我使用LOGIN欄位分區,是之每次查詢都能精準地定位到該分區,同時將資料庫存儲引擎改為InnoDB。

CREATE TABLE IF NOT EXISTS `MT4_TRADES` (
  `TICKET` int(11) NOT NULL,
  `LOGIN` int(11) NOT NULL,
  `SYMBOL` char(16) NOT NULL,
  `DIGITS` int(11) NOT NULL,
  `CMD` int(11) NOT NULL,
  `VOLUME` int(11) NOT NULL,
  `OPEN_TIME` datetime NOT NULL,
  `OPEN_PRICE` double NOT NULL,
  `SL` double NOT NULL,
  `TP` double NOT NULL,
  `CLOSE_TIME` datetime NOT NULL,
  `EXPIRATION` datetime NOT NULL,
  `CONV_RATE1` double NOT NULL,
  `CONV_RATE2` double NOT NULL,
  `COMMISSION` double NOT NULL,
  `COMMISSION_AGENT` double NOT NULL,
  `SWAPS` double NOT NULL,
  `CLOSE_PRICE` double NOT NULL,
  `PROFIT` double NOT NULL,
  `TAXES` double NOT NULL,
  `COMMENT` char(32) NOT NULL,
  `INTERNAL_ID` int(11) NOT NULL,
  `MARGIN_RATE` double NOT NULL,
  `TIMESTAMP` int(11) NOT NULL,
  `MODIFY_TIME` datetime NOT NULL,
  KEY `INDEX_LOGIN` (`LOGIN`),
  KEY `INDEX_CMD` (`CMD`),
  KEY `INDEX_OPENTIME` (`OPEN_TIME`),
  KEY `INDEX_CLOSETIME` (`CLOSE_TIME`),
  KEY `COMMENT` (`LOGIN`,`COMMENT`),
  KEY `TICKET` (`TICKET`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (LOGIN)
PARTITIONS 16 */;
			

存款時間的相關問題,我們使用網絡支付,成功後寫入資料庫,但這個時間並不是MT4的存款時間,與客戶端查看到的時候有誤差,我們通過訂單號反向查找,通過觸發器實時將存款時間寫會我們的資金賬號表。


DELIMITER //
CREATE DEFINER=`dba`@`192.168.6.20` PROCEDURE `MT4_TRADES_COMMENT`(IN `opentime` DATETIME, IN `comment` VARCHAR(32))
    COMMENT '比對MT4訂單好於Account訂單號'
BEGIN
	DECLARE orderNumber VARCHAR(25) DEFAULT NULL;
	DECLARE orderType VARCHAR(7) DEFAULT NULL;
	
	SELECT right(`comment`, 7) into orderType;
	SELECT SUBSTRING_INDEX(`comment`, ':', 1) into orderNumber;

	IF orderType = 'DEPOSIT' THEN
		UPDATE yourdb.accounts SET paystatus = '成功', deposit_time = opentime, mtime = now() where `order` = orderNumber;
	END IF;
	UPDATE yourdb.withdrawing SET deposit_time = opentime, mtime = now() where `order` = orderNumber; -- operator = 'TRIGGER', `status` = 'Completed'
END//
DELIMITER ;

SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `MT4_TRADES_AFTER_INSERT` AFTER INSERT ON `MT4_TRADES` FOR EACH ROW BEGIN
	call MT4_TRADES_COMMENT(NEW.OPEN_TIME, NEW.COMMENT);
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;			
			

			
SELECT TICKET,LOGIN,VOLUME
FROM example_real.MT4_TRADES
WHERE CMD IN ('0','1') AND SYMBOL IN ('GOLD','SILVER') AND CLOSE_TIME<>'1970-01-01 00:00:00' AND CLOSE_TIME>='2014-7-31 15:00:00' AND CLOSE_TIME<='2014-7-31 15:01:00' AND (
SELECT `status`
FROM db_example.members
WHERE username=example_real.MT4_TRADES.LOGIN)='Succeed'
ORDER BY CLOSE_TIME DESC;

+----------+-----------+--------+
| TICKET   | LOGIN     | VOLUME |
+----------+-----------+--------+
| 19971796 | 257024039 |      5 |
| 19972242 | 257024039 |      5 |
| 19964259 | 257010431 |      5 |
| 19973136 | 257019937 |    150 |
| 19970852 | 257021723 |      5 |
| 19971608 | 257024039 |      5 |
| 19973095 | 257015829 |      5 |
| 19973580 | 257022352 |      5 |
| 19971575 | 257024039 |      5 |
| 19973396 | 257020913 |      5 |
| 19971437 | 257024039 |      5 |
| 19970942 | 280084741 |     10 |
| 19973532 | 257025545 |      5 |
| 19971348 | 257024224 |     25 |
| 19973555 | 280104671 |     10 |
| 19970986 | 257025679 |      5 |
| 19971193 | 257004879 |      5 |
| 19969919 | 257023636 |     25 |
| 19971425 | 280084741 |     10 |
| 19971501 | 257027591 |      5 |
+----------+-----------+--------+
20 rows in set (6.11 sec)


SELECT TICKET,LOGIN,VOLUME
FROM (
SELECT TICKET,LOGIN,VOLUME,CLOSE_TIME
FROM example_real.MT4_TRADES
WHERE CMD IN ('0','1') AND SYMBOL IN ('GOLD','SILVER') AND CLOSE_TIME<>'1970-01-01 00:00:00' AND CLOSE_TIME>='2014-7-31 15:00:00' AND CLOSE_TIME<='2014-7-31 15:01:00') AS tab1, db_example.members tab2
WHERE tab1.LOGIN = tab2.username AND tab2.`status`= 'Succeed' ORDER BY tab1.CLOSE_TIME DESC;

+----------+-----------+--------+
| TICKET   | LOGIN     | VOLUME |
+----------+-----------+--------+
| 19973555 | 280104671 |     10 |
| 19971425 | 280084741 |     10 |
| 19970942 | 280084741 |     10 |
| 19971193 | 257004879 |      5 |
| 19964259 | 257010431 |      5 |
| 19973095 | 257015829 |      5 |
| 19973136 | 257019937 |    150 |
| 19973396 | 257020913 |      5 |
| 19970852 | 257021723 |      5 |
| 19973580 | 257022352 |      5 |
| 19969919 | 257023636 |     25 |
| 19971437 | 257024039 |      5 |
| 19971575 | 257024039 |      5 |
| 19971608 | 257024039 |      5 |
| 19972242 | 257024039 |      5 |
| 19971796 | 257024039 |      5 |
| 19971348 | 257024224 |     25 |
| 19973532 | 257025545 |      5 |
| 19970986 | 257025679 |      5 |
| 19971501 | 257027591 |      5 |
+----------+-----------+--------+
20 rows in set (0.31 sec)			
			
			

1.3. MT4_PRICES 表

MT4_PRICES 表結構

CREATE TABLE `MT4_PRICES` (
	`SYMBOL` CHAR(16) NOT NULL,
	`TIME` DATETIME NOT NULL,
	`BID` DOUBLE NOT NULL,
	`ASK` DOUBLE NOT NULL,
	`LOW` DOUBLE NOT NULL,
	`HIGH` DOUBLE NOT NULL,
	`DIRECTION` INT(11) NOT NULL,
	`DIGITS` INT(11) NOT NULL,
	`SPREAD` INT(11) NOT NULL,
	`MODIFY_TIME` DATETIME NOT NULL,
	PRIMARY KEY (`SYMBOL`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
			
			

記錄價格波動,用於繪製行情圖,我們使用觸發器記錄每次價格的變化。

CREATE TABLE IF NOT EXISTS `MT4_PRICES_GOLD_HISTORY` (
  `SYMBOL` char(16) NOT NULL,
  `TIME` datetime NOT NULL,
  `BID` double NOT NULL,
  `ASK` double NOT NULL,
  `LOW` double NOT NULL,
  `HIGH` double NOT NULL,
  `DIRECTION` int(11) NOT NULL,
  `DIGITS` int(11) NOT NULL,
  `SPREAD` int(11) NOT NULL,
  `MODIFY_TIME` datetime NOT NULL,
  UNIQUE KEY `TIME` (`TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `MT4_PRICES_SILVER_HISTORY` (
  `SYMBOL` char(16) NOT NULL,
  `TIME` datetime NOT NULL,
  `BID` double NOT NULL,
  `ASK` double NOT NULL,
  `LOW` double NOT NULL,
  `HIGH` double NOT NULL,
  `DIRECTION` int(11) NOT NULL,
  `DIGITS` int(11) NOT NULL,
  `SPREAD` int(11) NOT NULL,
  `MODIFY_TIME` datetime NOT NULL,
  UNIQUE KEY `TIME` (`TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `t_AfterUpdate_on_MT4_PRICES` AFTER UPDATE ON `MT4_PRICES` FOR EACH ROW BEGIN 
     if NEW.SYMBOL='GOLD' AND NEW.TIME > '1970-01-01 00:00:00' then
       insert into MT4_PRICES_GOLD_HISTORY select * from MT4_PRICES where SYMBOL='GOLD';
     
     elseif NEW.SYMBOL='SILVER' AND NEW.TIME > '1970-01-01 00:00:00' then
       insert into MT4_PRICES_SILVER_HISTORY select * from MT4_PRICES where SYMBOL='SILVER';
     end if;

END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;
			

上面是黃金與白銀數據,下面是外匯的例子

DROP TRIGGER IF EXISTS `t_After_Update_on_MT4PRICES`;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `t_After_Update_on_MT4PRICES` AFTER UPDATE ON `MT4_PRICES` FOR EACH ROW BEGIN     
     if NEW.SYMBOL='AUDJPY' then
       insert into MT4_PRICES_AUDJPY_HISTORY select * from MT4_PRICES where SYMBOL='AUDJPY';
     elseif NEW.SYMBOL='AUDUSD' then
       insert into MT4_PRICES_AUDUSD_HISTORY select * from MT4_PRICES where SYMBOL='AUDUSD';
     elseif NEW.SYMBOL='CHFJPY' then
       insert into MT4_PRICES_CHFJPY_HISTORY select * from MT4_PRICES where SYMBOL='CHFJPY';
     elseif NEW.SYMBOL='EURAUD' then
       insert into MT4_PRICES_EURAUD_HISTORY select * from MT4_PRICES where SYMBOL='EURAUD';
     elseif NEW.SYMBOL='EURCHF' then
       insert into MT4_PRICES_EURCHF_HISTORY select * from MT4_PRICES where SYMBOL='EURCHF';
     elseif NEW.SYMBOL='EURGBP' then
       insert into MT4_PRICES_EURGBP_HISTORY select * from MT4_PRICES where SYMBOL='EURGBP';       
     elseif NEW.SYMBOL='EURJPY' then
       insert into MT4_PRICES_EURJPY_HISTORY select * from MT4_PRICES where SYMBOL='EURJPY';
     elseif NEW.SYMBOL='EURUSD' then
       insert into MT4_PRICES_EURUSD_HISTORY select * from MT4_PRICES where SYMBOL='EURUSD';
     elseif NEW.SYMBOL='GBPCHF' then
       insert into MT4_PRICES_GBPCHF_HISTORY select * from MT4_PRICES where SYMBOL='GBPCHF';       
     elseif NEW.SYMBOL='GBPJPY' then
       insert into MT4_PRICES_GBPJPY_HISTORY select * from MT4_PRICES where SYMBOL='GBPJPY';
     elseif NEW.SYMBOL='GBPUSD' then
       insert into MT4_PRICES_GBPUSD_HISTORY select * from MT4_PRICES where SYMBOL='GBPUSD';
     elseif NEW.SYMBOL='NZDJPY' then
       insert into MT4_PRICES_NZDJPY_HISTORY select * from MT4_PRICES where SYMBOL='NZDJPY';       
     elseif NEW.SYMBOL='NZDUSD' then
       insert into MT4_PRICES_NZDUSD_HISTORY select * from MT4_PRICES where SYMBOL='NZDUSD';         
     elseif NEW.SYMBOL='USDCAD' then
       insert into MT4_PRICES_USDCAD_HISTORY select * from MT4_PRICES where SYMBOL='USDCAD';        
     elseif NEW.SYMBOL='USDCHF' then
       insert into MT4_PRICES_USDCHF_HISTORY select * from MT4_PRICES where SYMBOL='USDCHF'; 
     elseif NEW.SYMBOL='USDJPY' then
       insert into MT4_PRICES_USDJPY_HISTORY select * from MT4_PRICES where SYMBOL='USDJPY'; 
     end if;

END//
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;
/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;			
			

1.4. MT4_DAILY 表實現月結單

MT4_DAILY 表存儲日結單數據,我們可以隨時查詢他們,但是當數據量達到一定程度。運算量就會影響到查詢性能,我們需要實現月結單功能,已避免每次查詢都運算一次。

2. 數據源(Data Feeds)

格式

symbol  time(GMT) bid ask \CR \LF		
		
例 1. telnet 例子
telnet 211.223.211.114 16013

SILVER 1414129227 17.21 17.25
GOLD 1414129230 1229.92 1230.42
SILVER 1414129230 17.21 17.25
GOLD 1414129231 1229.98 1230.48
ABCXYZ 1414129231 15.463 15.663
ABCXYZ 1414129235 15.275 15.475
ABCXYZ 1414129239 15.027 15.227
GOLD 1414129242 1230.02 1230.52
ABCXYZ 1414129244 14.741 14.941
ABCXYZ 1414129248 14.442 14.642
ABCXYZ 1414129252 14.157 14.357
ABCXYZ 1414129256 13.912 14.112
ABCXYZ 1414129260 13.728 13.928
SILVER 1414129260 17.21 17.25
ABCXYZ 1414129264 13.622 13.822
ABCXYZ 1414129268 13.604 13.804
ABCXYZ 1414129272 13.674 13.874
ABCXYZ 1414129277 13.827 14.027
GOLD 1414129277 1230.08 1230.58
GOLD 1414129278 1230.02 1230.52
ABCXYZ 1414129281 14.049 14.249
GOLD 1414129281 1230.03 1230.53
GOLD 1414129283 1230.02 1230.52
ABCXYZ 1414129285 4.321 4.521
			

數據源負載均衡與災備