mysql - 请帮助我进行表/嵌套 JOIN 优化 : Way too slow!

Question

我的数据库中有大约 250 个表，每个表恰好有 439340 行。

mysql> SHOW CREATE TABLE data.b50d1 ;
+-------+--------------------------------------------------------------------------------------------
CREATE TABLE `b50d1` (
      `pTime` int(10) unsigned NOT NULL,
      `Slope` double NOT NULL,
      `STD` double NOT NULL,
      PRIMARY KEY (`pTime`),
      KEY `Slope` (`Slope`) USING BTREE
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=43940 MAX_ROWS=43940 PACK_KEYS=1 ROW_FORMAT=FIXED |
+-------+--------------------------------------------------------------------------------------------

如您所见，每个表中有三列:

• pTime:POSIX 时间戳。此列(及其所有值)在每个表中完全相同。这是我的 PRIMARY KEY
• 坡度
• 性病

Slope 和 STD 列具有“signed double”值，这些值在行与行和表与表之间都不同。

这是其中一个表中的一个小示例:

mysql> SELECT * FROM data.b50d1 limit 10;
+------------+------------+-------------+
| pTime      | Slope      | STD         |
+------------+------------+-------------+
| 1104537600 | 6.38733032 | -1.13387667 |
| 1104537900 | 5.58733032 | -0.93810617 |
| 1104538200 | 5.30135747 | -0.51912757 |
| 1104538500 |  5.4678733 | -0.54460575 |
| 1104538800 | 5.58190045 | -0.46369055 |
| 1104539100 | 5.50226244 | -0.46712018 |
| 1104714000 | 5.31221719 | -0.25210485 |
| 1104714300 | 4.72941176 |  0.00321249 |
| 1104714600 | 5.19638009 |  0.64116376 |
| 1104714900 | 5.12941176 |  0.39599099 |
+------------+------------+-------------+

我使用这些表运行存储过程。此过程包括以下步骤:

第 1 步)创建临时表 MainList...

步骤 2) INSERT SELECT 语句的结果到表中。生成的数据集是原始表的过滤组合。

STEP 3) 带有嵌套 JOIN 的 SELECT 语句遍历 TEMPORARY 表 (MainList) 的每个 MainList.STD 值，并返回原始表中的第一行 匹配某些特定条件(如下例)。

第 4 步)JOIN 结果到 MainList 并将它们输出给用户。

以下是程序本身:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetTimeList`(t1 varchar(7),t2 varchar(7),t3 varchar(7),inp1 float,inp2 float,inp3 float,inp4 float,inp5 float,inp6 float,inp7 float,inp8 float,inp9 float,inp10 float)
    READS SQL DATA
BEGIN
DROP TABLE IF EXISTS MainList;

CREATE TEMPORARY TABLE MainList(
  `pTime` int unsigned NOT NULL,
  `STD` double NOT NULL,
  PRIMARY KEY (`pTime`),
    KEY (`STD`) USING BTREE
    ) ENGINE = MEMORY;  

SET @s = CONCAT('INSERT INTO MainList(pTime,STD)  SELECT DISTINCT t1.pTime, t1.STD FROM ',t1,' AS t1 JOIN (',t2,' as t2 ,',t3,' as t3 )',
' ON (( t1.Slope >= ', inp1,
' AND t1.Slope <= ', inp2,
' AND t1.STD  >= ', inp3,
' AND t1.STD  <= ', inp4,
' AND t2.Slope  >= ', inp5,
' AND t2.Slope  <= ', inp6,
' AND t3.Slope  >= ', inp7,
' AND t3.Slope  <= ', inp8,
' ) OR ( t1.Slope <= ', 0-inp1,
' AND t1.Slope >= ', 0-inp2,
' AND t1.STD  <= ', 0-inp3,
' AND t1.STD  >= ', 0-inp4,
' AND t2.Slope  <= ', 0-inp5,
' AND t2.Slope  >= ', 0-inp6,
' AND t3.Slope  <= ', 0-inp7,
' AND t3.Slope  >= ', 0-inp8,
' ) ) AND ((t1.Slope < 0 XOR t1.STD < 0) AND t1.pTime = t2.pTime AND t2.pTime = t3.pTime AND t1.pTime >= ', inp9,
' AND t1.pTime <= ', inp10,' ) ORDER BY t1.pTime'
);

PREPARE stmt FROM @s;
EXECUTE stmt;

SET @q= CONCAT('SELECT m.pTime as OpenTime, CASE WHEN m.STD < 0 THEN 1 ELSE -1 END As Type, mu.pTime As CloseTime from MainList m LEFT JOIN ',t1,' mu ON mu.pTime = ( SELECT DISTINCT md.pTime FROM ',t1,' md WHERE md.pTime>m.pTime',' AND md.pTime <= ', inp10,
                            ' AND SIGN (md.STD)!= SIGN (m.STD) AND ABS(md.STD) >= ABS(m.STD) ORDER BY md.pTime LIMIT 1 )');


PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE MainList;
END

为了便于测试，我将上述过程分解为两个单独的查询。以下是带有“EXPLAIN EXTENDED”语句的查询(临时表是预先生成的):

第一个查询

---

INSERT INTO MainList(pTime,STD)
SELECT
  t1.pTime,
  t1.STD
 FROM
  b50d1 AS t1
 JOIN(b75d1 AS t2, b100d1 AS t3)ON(
  (
          t1.Slope >= 2.3169
          AND t1.Slope <= 7.0031
          AND t1.STD >= - 2.068
          AND t1.STD <= - 0.972
          AND t2.Slope >= 0.3179
          AND t2.Slope <= 5.7221
          AND t3.Slope >= 2.6466
          AND t3.Slope <= 35.7534
  )
  OR(
          t1.Slope <= - 2.3169
          AND t1.Slope >= - 7.0031
          AND t1.STD <= 2.068
          AND t1.STD >= 0.972
          AND t2.Slope <= - 0.3179
          AND t2.Slope >= - 5.7221
          AND t3.Slope <= - 2.6466
          AND t3.Slope >= - 35.7534
  )
 )
 AND(
  (t1.Slope < 0 XOR t1.STD < 0)
  AND t1.pTime = t2.pTime
  AND t2.pTime = t3.pTime
  AND t1.pTime >= 1104710000
  AND t1.pTime <= 1367700000
 )
 ORDER BY
  t1.pTime;

解释扩展:

+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------+-----------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows   | filtered | Extra                       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | ALL    | PRIMARY,Slope | NULL    | NULL    | NULL          | 439340 |    25.79 | Using where; Using filesort |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY,Slope | PRIMARY | 4       | data.t1.pTime |      1 |   100.00 | Using where                 |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | PRIMARY | 4       | data.t1.pTime |      1 |   100.00 | Using where                 |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------+-----------------------------+

第二个查询

---

SELECT
    m.pTime AS OpenTime,
    CASE WHEN m.STD < 0 THEN 1 ELSE - 1 END AS Type,
  mu.pTime AS CloseTime;
FROM
    MainList m
LEFT JOIN b50d1 mu ON mu.pTime =(
    SELECT DISTINCT
        md.pTime
    FROM
        b50d1 md
    WHERE
        md.pTime > m.pTime
    AND md.pTime <= 1367700000
    AND SIGN(md.STD)!= SIGN(m.STD)
    AND ABS(md.STD)>= ABS(m.STD)
    ORDER BY
        md.pTime
    LIMIT 1
);

解释扩展:

+----+--------------------+-------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+-------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY            | m     | ALL    | NULL          | NULL    | NULL    | NULL |     16 |   100.00 |             |
|  1 | PRIMARY            | mu    | eq_ref | PRIMARY       | PRIMARY | 4       | func |      1 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | md    | range  | PRIMARY       | PRIMARY | 4       | NULL | 439338 |   100.00 | Using where |
+----+--------------------+-------+--------+---------------+---------+---------+------+--------+----------+-------------+

查询有效并返回正确的结果，但它比我需要的慢 数量级。我认识到 type: ALL 语句出现在两个 EXPLAIN 语句中表明我的索引可能不是最优的。

过去一周我才使用 MYSQL，现在我开始觉得自己头疼了。非常感谢您的帮助。

我用 CREATE TABLE 和 INSERT 语句创建了一个 SQL 文件，这样任何愿意帮助我的人都可以在“测试”中创建我的表的较小版本数据库: slowtables.SQL

为了完整起见，这里是 my.ini 设置文件 - 也许它是一个瓶颈？

[client]
pipe
socket=mysql
[mysql]
default-character-set=latin1
[mysqld]
skip-networking
enable-named-pipe
socket=mysql
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
character-set-server=latin1
default-storage-engine=MYISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=189M
table_cache=256
tmp_table_size=192M
key_buffer_size=594M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K

Answer 1

我在这里看到两个可能的改进，它们更多地与 MySQL 的优化器(或其弱点)有关。在第二个查询中，给定 LIMIT 1，子查询中的 DISTINCT 是多余的。ORDER BY LIMIT 1 查询应该通过搜索索引来完成，直到找到与其他条件匹配的记录。 (你真的需要 LEFT JOIN 吗？？)

在第一个查询中，MySQL 显然无法将 OR 优化为 UNION。但是，如果您手动执行此操作，它可能会为 UNION 查询的两半选择更好的计划。

HTH。我可以稍后再看。