MYSQL查询耗时4小时

Question

大家下午好。我来找你是希望你能为我遇到的 MYSQL 优化问题提供一些指导。首先，一些系统规范。

• MYSQL版本:5.2.47 CE
• WampServer 2.2 版

计算机:

• 三星 QX410(笔记本电脑)
• Windows 7
• 英特尔 i5(2.67 Ghz)
• 4GB 内存

我有两个表:

1. “Delta_Shares”包含股票交易数据，并包含两列注释。 “Ticker”是 Varchar(45)，“Date_Filed”是 Date。该表有大约 300 万行(所有行都是唯一的)。我在 (Ticker, Date_Filed) 上的“DeltaSharesTickerDateFiled”表上有一个索引。

2. “Stock_Data”包含两列注释。 “Ticker”是 Varchar(45)，“Value_Date”是 Date。该表有大约 1900 万行(所有行都是唯一的)。我在 (Ticker, Value_Date) 的“StockDataIndex”表上有一个索引。

我试图通过查找 Stock_Data 表中的信息来更新“Delta_Shares”表。 以下查询需要 4 个多小时才能运行。

update delta_shares A, stock_data B
set A.price_at_file = B.stock_close
where A.ticker = B.ticker
    and A.date_filed = B.value_Date;

过多的运行时间是大量行、糟糕的索引、糟糕的机器、糟糕的 SQL 编写还是以上所有原因的自然结果？请让我知道是否有任何其他信息有用(我对 MYSQL 不是很熟悉，尽管这个问题使我在优化的道路上走得很远)。我非常感谢任何想法或建议。

---

更新为“EXPLAIN SELECT”

1(id)  SIMPLE(seltype)  A(table)   ALL(type)  DeltaSharesTickerDateFiled(possible_keys) ... 3038011(rows)   

1(id)  SIMPLE(seltype)  B(table)  ref(type)  StockDataIndex(possible_keys)  StockDataIndex(key)  52(key_len) 13ffeb2013.A.ticker,13ffeb2013.A.date_filed(ref) 1(rows)   Using where

---

更新了表格描述。Stock_Data表:

idstock_data    int(11)         NO  PRI     auto_increment
ticker          varchar(45)     YES MUL     
value_date      date            YES         
stock_close     decimal(10,2)   YES 

Delta_Shares 表:

iddelta_shares          int(11) NO  PRI     auto_increment
cik                     int(11) YES MUL     
ticker              varchar(45) YES MUL     
date_filed_identify     int(11) YES         
Price_At_File       decimal(10,2)   YES         
delta_shares        int(11) YES         
date_filed                date  YES         
marketcomparable            varchar(45)      YES            
market_comparable_price     decimal(10,2)    YES            
industrycomparable          varchar(45)      YES            
industry_comparable_price   decimal(10,2)    YES                    

来自 Delta_Shares 的指数:

delta_shares    0   PRIMARY 1   iddelta_shares  A   3095057             BTREE       
delta_shares    1   DeltaIndex  1   cik A   18          YES BTREE       
delta_shares    1   DeltaIndex  2   date_filed_identify A   20633           YES BTREE       
delta_shares    1   DeltaSharesAllIndex 1   cik A   18          YES BTREE       
delta_shares    1   DeltaSharesAllIndex 2   ticker  A   619011          YES BTREE       
delta_shares    1   DeltaSharesAllIndex 3   date_filed_identify A   3095057         YES BTREE       
delta_shares    1   DeltaSharesTickerDateFiled  1   ticker  A   11813           YES BTREE       
delta_shares    1   DeltaSharesTickerDateFiled  2   date_filed  A   3095057         YES BTREE       

来自 Stock_Data 的索引:

stock_data  0   PRIMARY 1   idstock_data    A   18683114                BTREE       
stock_data  1   StockDataIndex  1   ticker  A   14676           YES BTREE       
stock_data  1   StockDataIndex  2   value_date  A   18683114            YES BTREE       

Answer 1

您可以制定一些基准来查看瓶颈所在。例如，尝试将字段更新为常量值并查看需要多长时间(显然，您需要制作数据库的副本来执行此操作)。然后尝试一个不更新的选择查询，但只选择要更新的值和它们将更新到的值。

像这样的基准通常会告诉您是否在浪费时间尝试优化，或者是否有很大的改进空间。

至于内存，这里有一个粗略的概念:

varchar 字段是 2 个字节加上实际长度，datetime 字段是 8 个字节。因此，让我们做一个非常自由的猜测，即 Stock_Data 表中的 varchar 字段平均约为 42 字节。使用每行加起来最多 50 个字节的日期时间字段。

50 字节 x 2000 万行 = .93 GB

因此，如果此过程是您机器中唯一进行的事情，那么我认为内存不是问题，因为您可以轻松地将查询正在处理的两个表中的所有数据同时放入内存中。但如果还有其他事情发生，那么它可能是一个因素。