logo标签列表

有人能加速我的MySQL存储过程吗？

mysqlsql
3
我正在对金融数据进行模拟，每次都使用不同的参数尽可能快地触发此存储过程。
速度在这里非常重要。
该过程的操作如下：
1. 找到变量STD的值，该值与STD输入值两侧的X个值相同（这给出了围绕输入STD的范围的边界，长度为X）。
2. 从此范围内的变量中编制一个符合一组条件的列表。
3. 使用另一组条件处理此列表以创建代表开放价格、订单类型和关闭价格的最终列表。
这是唯一的非过渡表的压缩（rar）表转储。
这是该表的SHOW CREATE TABLE信息：
| b50d1 | CREATE TABLE `b50d1` (
  `pTime` int(10) unsigned NOT NULL,
  `Slope` float(8,4) unsigned NOT NULL DEFAULT '0.0000',
  `STD` float(8,4) unsigned NOT NULL DEFAULT '0.0000',
  `Slope_Pos` int(1) unsigned NOT NULL DEFAULT '2',
  `STD_Pos` int(1) unsigned NOT NULL DEFAULT '2',
  PRIMARY KEY (`pTime`),
  UNIQUE KEY `ID1` (`pTime`,`STD`,`STD_Pos`) USING BTREE,
  UNIQUE KEY `ID2` (`pTime`,`Slope`,`Slope_Pos`),
  KEY `STD` (`STD`) USING BTREE,
  KEY `Slope` (`Slope`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=439340 PACK_KEYS=1
/*!50100 PARTITION BY KEY (pTime)
PARTITIONS 10 */ |

如果您不想下载转储文件，这里有一个表格的小样例：
mysql> select * from b50d1 limit 10;
+------------+---------+--------+-----------+---------+
| pTime      | Slope   | STD    | Slope_Pos | STD_Pos |
+------------+---------+--------+-----------+---------+
| 1107309300 |  1.6326 | 1.3924 |         0 |       1 |
| 1107314400 |  6.8959 | 0.2243 |         1 |       1 |
| 1107323100 | 18.1991 | 1.4685 |         1 |       0 |
| 1107335400 |  2.5014 | 0.4736 |         0 |       0 |
| 1107362100 |  4.2878 | 0.8558 |         0 |       1 |
| 1107363300 |  6.9629 | 1.4130 |         0 |       0 |
| 1107363900 |  8.1032 | 0.2860 |         0 |       0 |
| 1107367500 | 16.6244 | 0.6159 |         0 |       0 |
| 1107368400 | 19.3792 | 1.1875 |         0 |       0 |
| 1107369300 | 21.9457 | 0.9426 |         0 |       0 |
+------------+---------+--------+-----------+---------+

这是我的代码:

参数:

t1 varchar(15),inp1 float,inp2 int,inp3 float,inp4 int,inp9 int,inp10 int

过程:

BEGIN
DROP TABLE IF EXISTS MainList;
DROP TABLE IF EXISTS SearchListA;
DROP TABLE IF EXISTS List1;
DROP TABLE IF EXISTS List2;


CREATE TABLE MainList(
  `pTime` int unsigned NOT NULL,
  `STD` double unsigned NOT NULL,
    `STD_Pos`   int unsigned NOT NULL,
  PRIMARY KEY (`pTime` ASC),
    INDEX (`STD` ASC) USING BTREE,
    INDEX `ID1` (`pTime` ASC, `STD` ASC) USING BTREE,
    INDEX `ID2` (`pTime` ASC, `STD` ASC, `STD_Pos` ASC) USING BTREE
    ) ENGINE = MEMORY;  


CREATE TABLE SearchListA(
  `pTime`  int unsigned  NOT NULL ,
  `STD` double unsigned NOT NULL,
    `STD_Pos`   int unsigned NOT NULL,
  `SearchEnd`  int unsigned NOT NULL,
    PRIMARY KEY (`pTime` ASC),
    INDEX (`STD` ASC),
    INDEX `ID1` (`pTime`,`STD` ASC) USING BTREE,
    INDEX `ID2` (`pTime` ASC, `STD` ASC, `STD_Pos` ASC) USING BTREE
    ) ENGINE = MEMORY;

CREATE TABLE List1(
  `pTime` int unsigned NOT NULL,
  `STD` double unsigned NOT NULL DEFAULT 0,
    `STD_Pos`   int unsigned NOT NULL DEFAULT 2,
  PRIMARY KEY (`pTime` ASC),
    INDEX (`STD`,`STD_Pos` ASC) USING BTREE
    ) ENGINE = MEMORY;  

CREATE TABLE List2(
  `pTime` int unsigned NOT NULL,
  `Slope` double unsigned NOT NULL DEFAULT 0,
    `Slope_Pos`     int unsigned NOT NULL DEFAULT 2,
  PRIMARY KEY (`pTime` ASC),
    INDEX `ID1` (`Slope`,`Slope_Pos` ASC) USING BTREE
    ) ENGINE = MEMORY;  



SET @s1 = CONCAT('INSERT INTO List1(pTime,STD,STD_Pos)  SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 USE INDEX (STD)   WHERE   t1.STD < ',   ABS(inp1),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by STD DESC limit ', inp2);
PREPARE stmt FROM @s1;
EXECUTE stmt;

SET @lim = inp2+(inp2-(SELECT count(*) FROM List1));
SET @s2 = CONCAT('INSERT INTO List1(pTime,STD,STD_Pos)  SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 USE INDEX (STD)   WHERE  t1.STD >=',   ABS(inp1),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by STD ASC limit ?');
PREPARE stmt FROM @s2;
EXECUTE stmt USING @lim;

##########################################
SET @s3 = CONCAT('INSERT INTO List2(pTime,Slope,Slope_Pos)  SELECT t1.pTime, t1.Slope, t1.Slope_Pos FROM ',t1,' AS t1 USE INDEX (Slope) WHERE t1.Slope < ',ABS(inp3),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by Slope DESC limit ', inp4);
PREPARE stmt FROM @s3;
EXECUTE stmt;

SET @lim = inp4+(inp4-(SELECT count(*) FROM List2));
SET @s4 = CONCAT('INSERT INTO List2(pTime,Slope,Slope_Pos)  SELECT t1.pTime, t1.Slope, t1.Slope_Pos FROM ',t1,' AS t1 USE INDEX (Slope) WHERE t1.Slope >=',ABS(inp3),' AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' order by Slope ASC limit ?');
PREPARE stmt FROM @s4;
EXECUTE stmt USING @lim;

#########################################


#########################################
SET @minSL1 = (SELECT MIN(Slope) FROM List2);
SET @maxSL1 = (SELECT MAX(Slope) FROM List2);

SET @minSD1 = (SELECT MIN(STD) FROM List1);
SET @maxSD1 = (SELECT MAX(STD) FROM List1);


SET @s = CONCAT('INSERT INTO MainList(pTime,STD,STD_Pos) SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 ',
' WHERE t1.Slope >= ', @minSL1 ,
' AND t1.Slope <= ', @maxSL1 ,
' AND t1.STD  >= ', @minSD1 ,
' AND t1.STD  <= ', @maxSD1,
' AND ((t1.Slope_Pos <> t1.STD_Pos) AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' ) ORDER BY t1.pTime'
);

PREPARE stmt FROM @s;
EXECUTE stmt;

INSERT INTO SearchListA (pTime,STD,STD_Pos,SearchEnd)
SELECT sql_no_cache M1.pTime,M1.STD,M1.STD_Pos,M2.pTime 
FROM MainList as M1
JOIN MainList as M2
ON(M2.pTime = (
SELECT M3.pTime FROM MainList as M3 WHERE M3.pTime>M1.pTime ORDER BY M3.pTime ASC  limit 1)
);



SET @q = CONCAT('
SELECT 
m.pTime as OpenTime,
CASE WHEN m.STD_Pos = 0 THEN 1 ELSE -1 END As Type,
min( big.pTime ) as CloseTime
FROM   
    SearchListA m
    JOIN ',t1,' as big ON (
        big.pTime >  m.pTime
        AND big.pTime <= LEAST(m.SearchEnd,m.pTime+172800)
        AND m.STD < big.STD AND m.STD_Pos <> big.STD_Pos
    )
GROUP BY m.pTime
');


PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END

示例函数调用：

CALL `data`.`JustMain`( "b50d1",1.5,5000,6.43,5000,1121126400,1278892800)

我目前可以在不到一秒的时间内运行此过程，但速度越快越好！

如果有人需要更多信息，我会包含在内。

+1 表示“STD”的值。 - Rudie
1个回答
1

这里只是一个开始：

建议1：从表中删除重复索引。减少要维护的索引可以加快加载表的速度，无论存储引擎如何。

MainList表有两个以相同两列（ID1和ID2）开头的索引。

MainList表有三个以相同一列（主键、ID1和ID2）开头的索引。

如果MainList的主键是pTime，则表中没有其他行会共享相同的pTime。请确保这是您的意图。

如果您知道MainList中有多行应该具有相同的pTime（即具有多个STD的pTime），则将PRIMARY更改为（pTime ASC，STD ASC）。

由于您的查询涉及pTime、STD、STD_Pos，并且如果此元组是唯一的，则这三列可以成为主键（pTime ASC，STD ASC，STD_Pos ASC）

为了确保安全，MainList应该像这样：

CREATE TABLE MainList(
  `pTime` int unsigned NOT NULL,
  `STD` double unsigned NOT NULL,
  `STD_Pos` int unsigned NOT NULL,
  INDEX `NDX1` (`STD` ASC, `STD_Pos` ASC) USING BTREE,
  INDEX `NDX2` (`pTime` ASC, `STD` ASC, `STD_Pos` ASC) USING BTREE
) ENGINE = MEMORY;

是的，没有主键。让MySQL查询优化器从这里决定。如果你非常确定每一行的pTime都是唯一的，那么表格可以像这样：

CREATE TABLE MainList(
  `pTime` int unsigned NOT NULL,
  `STD` double unsigned NOT NULL,
  `STD_Pos` int unsigned NOT NULL,
  INDEX `NDX1` (`STD` ASC, `STD_Pos` ASC) USING BTREE,
  INDEX `NDX2` (`pTime` ASC, `STD` ASC, `STD_Pos` ASC) USING BTREE,
  UNIQUE INDEX pTime (pTime)
) ENGINE = MEMORY;

建议 #2：在加载期间禁用非唯一索引
这就是mysqldump创建备份的方式，专为尽可能快地将数据加载回mysql而设计。
在加载具有其他非唯一索引的任何表之前，请执行以下操作（以示例说明）：
SET @s1 = 'ALTER TABLE List1 DISABLE KEYS';
    PREPARE stmt FROM @s1;
    EXECUTE stmt;
SET @s1 = CONCAT('INSERT INTO List1(pTime,STD,STD_Pos)  SELECT t1.pTime, t1.STD, t1.STD_Pos FROM ',t1,' AS t1 USE INDEX (STD)   WHERE   t1.STD < ',   ABS(inp1),' AND t1.pTime >= ', inp9,
    ' AND t1.pTime <= ', inp10,' order by STD DESC limit ', inp2);
    PREPARE stmt FROM @s1;
    EXECUTE stmt;
SET @s1 = 'ALTER TABLE List1 ENABLE KEYS';
    PREPARE stmt FROM @s1;
    EXECUTE stmt;

这样做可以为List1构建非唯一索引。PRIMARY立即加载到List1中。 List1的非唯一索引将在之后以线性方式加载。您也可以对List2执行此操作。

如果数据负载较小，则第二个建议可能帮助不大，但可以随着数据负载增加而实现快速加载。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接