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

数据源负载均衡与灾备