使用 select count(*) 更新查询速度较慢

Question

我必须计算 table2 中的数字在 table2.a 和 table2.b 范围内的数字之间出现的次数

，即我们想知道有多少次这样的情况： a <开始< b

我运行了以下查询：

UPDATE table2
 SET occurrence =  
       (SELECT COUNT(*) FROM table1 WHERE start BETWEEN table2.a AND table2.b);



table2 
ID   a   b    occurrence
1    1   10
2    1   20
3    1   25
4    2   30


table1 
ID start col1 col2 col3
1   1
2   7
3  10
4  21
5  25
6  27
7  30

table2 作为

• a、b 上的 3 个索引，出现
• 1567 行（因此我们将在 table2 上 SELECT COUNT(*) 1567 次..）
• ID 列作为 PK

table1 作为

• 上的 1 个索引
• 开始42,000,000 行
• 列开始是“按列开始排序”
• ID 列为 PK

==>花了2.5个小时才完成了2/3。我需要加快速度...有什么建议吗？ :)

Answer 1

您可以尝试将 id 列添加到表 1 的索引中：

CREATE INDEX start_index ON table1 (start,id);

并将查询重写为

UPDATE table2
 SET occurrence =  
       (SELECT COUNT(id) FROM table1 WHERE start BETWEEN table2.a AND table2.b);

这称为“覆盖索引”： http://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

->对表1的整个查询可以通过索引中的数据来服务->无需对实际记录进行额外的页面查找。

Answer 2

使用存储过程。将 COUNT 的结果保存在局部变量中，然后使用它来运行 UPDATE 查询。

Answer 3

我会这样做

// use one expensive join
create table tmp
select table2.id, count(*) as occurrence 
from table1
inner join table1
on table1.start between table2.a and table2.b
group by table1.id;

update table2, tmp
set table2.occurrence=tmp.occurrence 
where table2.id=tmp.id;

Answer 4

我认为 count(*) 使数据库读取数据行，而在您的情况下它只需要读取索引。尝试：

UPDATE table2
 SET occurrence =  
       (SELECT COUNT(1) FROM table1 WHERE start BETWEEN table2.a AND table2.b);