长SQL查询-如何优化？

Question

我有一个运行大约24小时的sql查询。我想知道是否有优化它的方法。

查询为：

update r, t  set r.a1 = t.a2, r.b1 = t.b1  where r.c = t.c and r.d1 = t.d2 and r.e1 = t.e2 and r.f1 = t.f2;

表r有大约2,000,000行，定义如下：

Field  Type      Collation  Null  Key  Default Extra         
-----  --------  ---------  ----  ---  ------  --------------

id     int(11)   (NULL)     NO    PRI  (NULL)  auto_increment

a1     blob      (NULL)     YES        (NULL)                

e1     tinyblob  (NULL)     YES   MUL  (NULL)                

f1     int(11)   (NULL)     YES   MUL  (NULL)                

c      int(11)   (NULL)     YES   MUL  (NULL)                

b1     int(11)   (NULL)     YES   MUL  (NULL)                

d1     int(11)   (NULL)     YES   MUL  (NULL)                

表t有大约1,200,000行，定义如下：

Field  Type      Collation  Null  Key     Default  Extra         

-----  --------  ---------  ----  ------  -------  --------------

c      int(11)   (NULL)     NO    MUL     0                      

d2     int(11)   (NULL)     NO    MUL     0                      

e2     tinyblob  (NULL)     YES   MUL     (NULL)                 

f2     int(2)    (NULL)     NO    MUL     (NULL)                 

a2     blob      (NULL)     YES           (NULL)                 

b1     int(11)   (NULL)     YES           0                      

id     int(11)   (NULL)     NO    PRI     (NULL)   auto_increment

我想知道是否有优化查询的方法？

谢谢!

Answer 1

索引键的顺序应该与where语句的顺序相同。r表键：

c,d1,e1,f1

和t表键：

c,d2,e2,f2

在列b1上的r表中是否需要键

Answer 2

你的结构看起来还不错。2M行并不是很多。您的行可能很大(使用blobs)，但是您所做的比较只针对整数值。它应该更快。

尝试在您的表上运行ANALYZE、OPTIMIZE、CHECK、REPAIR命令，以确保正确构建索引。

完成后，您应该尝试在系统中进行更深入的调查。是什么降低了查询速度？它可以是：

• disk I/O
• memory limit (请尝试调整您的my.cnf，请参阅卓越的http://www.mysqlperformanceblog.com/ )
• CPU (似乎是

的问题

使用监控来获得有关sql缓存、内存使用等的数据。它将帮助您诊断问题。

Answer 3

尝尝这个

update r left join t on r.c = t.c and r.d1 = t.d2 and r.e1 = t.e2 and r.f1 = t.f2 set r.a1 = t.a2, r.b1 = t.b1

还要做一些更改

• 在表r中索引的make c，d1，e1列
• 在表t

中索引的c，d2，e2列