从 A 更新中选择 count(*) B 超慢

Question

我有一个查询需要 2 分钟从表 A 中计数并使用计数结果更新表 B。

每当 Table_B 列 Start 中的数字与 Table_A（readstart/readend）中的范围匹配时，我应该更新 Table_B 中的 read_count。

   id | readstart | readend | read_count
    1 | 2999997   | 3000097  | 0   
    2 | 3000097   | 3000197  | 0   
    3 | 3000497   | 3000597  | 0   
    4 | 3001597   | 3001697  | 0   
    5 | 3001897   | 3001997  | 0   
    6 | 3005397   | 3005497  | 0   
    7 | 3005997   | 3006097  | 0  
    8 | 3006397   | 3006497  | 0   
    9 | 3006797   | 3006897  | 0  
    10| 3007497   | 3007597  | 0

这是我应该用计数结果更新的表：

    CREATE TABLE `rdc_test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `readstart` int(11) DEFAULT NULL,
    `readend` int(11) DEFAULT NULL,
    `read_count` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `readstart` (`readstart`),
     KEY `readend` (`readend`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

这是我想要计算匹配行的表：

   CREATE TABLE `1ips_chr1` (
   `strand` char(1) DEFAULT NULL,
   `chr` varchar(10) DEFAULT NULL,
   `start` int(11) DEFAULT NULL,
   `end` int(11) DEFAULT NULL,
   `name` varchar(255) DEFAULT NULL,
   `name2` varchar(255) DEFAULT NULL,
   `id` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`),
   KEY `start` (`start`),
   KEY `end` (`end`)
  ) ENGINE=MyISAM AUTO_INCREMENT=34994289 DEFAULT CHARSET=latin1;

我对 10 行进行了测试，结果很糟糕..2 分钟选择 count(*) 并更新 10 行。我在 Table_A 中有大约 350,000 行需要更新，在 table_B 中有 35,000,000 行。我知道平均每个计数应该返回 30~40 个结果。

这是我的超慢查询：

UPDATE rdc_test
SET rdc_test.read_count =
    ( 
        SELECT COUNT(start) as read_count 
        FROM 1ips_chr1
        WHERE 1ips_chr1.start >= rdc_test.readstart 
        AND 1ips_chr1.start <= rdc_test.readend
    )

Query OK, 10 rows affected (2 min 22.20 sec)
Rows matched: 10  Changed: 10  Warnings: 0

Answer 1

试试这个：

UPDATE rdc_test t1
    INNER JOIN 
    (
        SELECT r.id AS id, 
               COUNT(l.start) AS read_count
        FROM rdc_test r
            LEFT OUTER JOIN start1ips_chr1 l
                ON l.start >= r.readstart 
                AND l.start <= r.readend
        GROUP BY r.id
    ) t2
    ON t1.id = t2.id
SET t1.read_count =  t2.read_count

编辑：

由于需要更新的数据量，最好的方法是重新创建表而不是执行更新：

CREATE TABLE new_rdc_test AS
SELECT r.id AS id, 
       r.readstart AS readstart,
       r.readend AS readend,
       COUNT(l.start) AS read_count
FROM rdc_test r
    LEFT OUTER JOIN start1ips_chr1 l
        ON l.start >= r.readstart 
        AND l.start <= r.readend
GROUP BY r.id, r.readstart, r.readend

这个查询运行得足够快吗？

Answer 2

尝试将 COUNT(*) 带到应用程序级别（即，将其存储为 PHP/Java 中的变量），然后使用该值执行 UPDATE。 MySQL 不必为您更新的每条记录计算该计数。