mysql - 谁能加速我的 MySQL 存储过程？

Question

我正在对财务数据运行模拟，每次都以不同的参数尽可能快地触发这个存储过程。

速度在这里非常重要。

程序的作用是这样的:

1. Find the value of variable STD that is X values away from the value of input STD on both sides of STD. (This gives the boundaries of a range around the input STD, X values in length).

2. Compiles a list from variables in this range that match a set of conditions.

3. Processes this list with another set of conditions to create a final list that represents Open Prices, Type of Order, and Close Prices.

这是一个compressed (rar) table dump对于唯一的非过渡表。

这是该表的 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)

我目前可以在不到一秒的时间内运行这个过程，但越快越好!

如果有人需要更多信息，我会提供。

Answer 1

这只是一个开始:

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

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

MainList 表具有三个以同一列开头的索引:Primary Key、ID1 和 ID2。

如果 MainList 的 PRIMARY KEY 是 pTime，则表中没有其他行将共享相同的 pTime。确保这是您的意图。

如果您知道 MainList 中的不止一行应该具有相同的 pTime(即具有多个 STD 的 pTime)，则将 PRIMARY 更改为 (pTime ASC, STD升序)。

由于您有涉及 pTime、STD、STD_Pos 的查询，并且如果此元组是唯一的，则这三列可以是 PRIMARY KEY (pTime ASC,STD升序,STD_Pos 升序)

为了安全起见，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;  

没错，没有PRIMARY KEY。让 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 执行此操作。

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