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

Question

我正在对财务数据进行模拟，每次使用不同的参数尽可能快地触发此存储过程。

速度在这里至关重要。

该程序的作用是这样的：

1. 求变量STD的值，该值与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 浮点型,inp2 int,inp3 浮点数,inp4 int,inp9 int,inp10 整数

过程：

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 的主键是 pTime，则表中的任何其他行都不会共享相同的 pTime。确保这是您的意图。

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

由于您的查询涉及 pTime、STD、STD_Pos，并且如果此元组是唯一的，则这三列可以是 PRIMARY KEY (pTime ASC,STD ASC,< code>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;  

没错，没有 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 的 NonUnique 索引将随后以线性方式加载。您也可以对 List2 执行此操作。

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