mysql - 提高MySQL选择和更新查询性能的有效方法

Question

我在 MySQL 数据库中有 2 个主表。

Table1 is -> Master
Table2 is -> Stock

主表有大约 500K(50 万)行，每一行都是唯一的。主表的每一列都有一个外键(示例如下)

Stock 表目前大约有 20K 行；不久的将来可以达到 300K。

问题

我的问题是我正在对这些表执行 SQL 查询，目前这需要未知的时间。所以想知道如何提高 MySQL 数据库的性能以加快 SQL 查询的执行时间。

SQL 查询执行以下操作:

搜索主表 -> 搜索股票表 -> 更新主表

我正在通过 PHP 文件执行 SQL 查询，我在浏览器中运行该文件来完成上述步骤。因为主表有超过 500K 条记录，所以我一次只调用 1,000 条记录，并执行上述步骤，再次迭代到下一批 1,000 条记录，直到主表末尾。

我通过使用 ---
来实现这一点for 循环 for 搜索表；总共约 5390 个周期
while 循环搜索库存并更新主表。

测试
出于测试目的，我将 Stock 表减少为仅 100 条记录，并保留 Master 表的所有 539K 记录以检查执行时间。大约需要140~150秒。

脚本
我在下面附上了我的脚本

<?php
set_time_limit(36000);

$dbhost='localhost';
$dbuser='root';
$dbpass='';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);

mysql_select_db("pmaster",$conn);

if (!$conn) {   die('Could not connect : '.mysql_error());  }

$trecssql = "select count(distinct `Id`) Total from `Master`";
$trecs = mysql_fetch_assoc(mysql_query($trecssql));

$trecs = $trecs['Total'];
$recspt = 1000; // Records Per Transactions 539000
$trecs = ceil($trecs/$recspt);
$startrow = 1;
$endrow = 1000;
//$trecs = 50; // Comment it to work as normal.

$start_time = microtime(true);

这是批处理的循环

for ($i=1; $i<=$trecs; $i++) {

这是获取批量主行的查询

    $mastersql = "select `Id`, 
                         `Attribute1`, `Attribute2`, 
                         `Attribute3`, `Attribute4`, `Attribute5` 
                   from `Master` 
               where Id between ".$startrow." and ".$endrow;
    $this_mastersql = mysql_query($mastersql);
    $updatesql = '';

这是处理批处理的循环

    while($master_rec = mysql_fetch_assoc($this_mastersql)) {

以下查询总结了主数据中每个项目的库存内容。

       $searchsql = "select min(Price) minprice, 
                            avg(Price) avgprice, 
                            max(Price) maxprice, 
                            Currency from `Stock`
                      where     Name1 = '".$master_rec['Attribute1']."'
                    AND     Name2 = '".$master_rec['Attribute2']."'
                        AND     Name3 = '".$master_rec['Attribute3']."'
                        AND     Name4 = '".$master_rec['Attribute4']."'
                        AND     Name5 = '".$master_rec['Attribute5']."';";

        $this_searchsql = mysql_query($searchsql);
        $search_rec = mysql_fetch_assoc($this_searchsql);

这是更新主表的查询。

        $updatesql .= "update `Master`
                set `MinP` = '".$search_rec['minprice']."',
                    `AvgP` = '".$search_rec['avgprice']."',
                                `MaxP` = '".$search_rec['maxprice']."',
                                `Currency` = '".$search_rec['currency']."'
                            where Id = '".$master_rec['Id']."';";
    }

    mysql_query($updatesql);

    $startrow = $endrow+1;
    $endrow = $startrow+999;
}



$end_time = microtime(true);

echo 'Updated <br /><br />';
echo "Scripts Execution time <br />";
echo "Time in Hours : Minutes : Seconds <br />"
     .gmdate("H:i:s", $time_elapsed = $end_time - $start_time);
echo "<br /> <br /> Time in Seconds ".$time_elapsed;

?>

表结构
附上Excel文件中主表的示例。
https://docs.google.com/open?id=0B8Oew7S4GzgiQk5tbUZKdXVEUms

CREATE TABLE IF NOT EXISTS `Attribute1` (
  `Id` int(3) NOT NULL,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Attribute2` (
  `Id` int(10) NOT NULL,
  `Name` decimal(3,2) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Attribute3` (
  `Id` int(3) NOT NULL,
  `Name` varchar(5) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Attribute4` (
  `Id` int(3) NOT NULL,
  `Name` varchar(20) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Attribute5` (
  `Id` int(3) NOT NULL,
  `Name` varchar(100) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Master` (
  `Id` int(20) NOT NULL AUTO_INCREMENT,
  `Attribute1` varchar(50) DEFAULT NULL,
  `Attribute2` decimal(3,2) DEFAULT NULL,
  `Attribute3` varchar(5) DEFAULT NULL,
  `Attribute4` varchar(20) DEFAULT NULL,
  `Attribute5` varchar(100) DEFAULT NULL,
  `MinP` decimal(10,2) DEFAULT NULL,
  `AvgP` decimal(10,2) DEFAULT NULL,
  `MaxP` decimal(10,2) DEFAULT NULL,
  `Currency` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `Attribute1` (`Attribute1`),
  KEY `Attribute2` (`Attribute2`),
  KEY `Attribute3` (`Attribute3`),
  KEY `Attribute4` (`Attribute4`),
  KEY `Attribute5` (`Attribute5`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Constraints for table `Master`
--
ALTER TABLE `Master`
  ADD CONSTRAINT `Master_ibfk_1` FOREIGN KEY (`Attribute1`) REFERENCES 
      `Attribute1` (`Name`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `Master_ibfk_2` FOREIGN KEY (`Attribute2`) REFERENCES 
      `Attribute2` (`Name`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `Master_ibfk_3` FOREIGN KEY (`Attribute3`) REFERENCES 
      `Attribute3` (`Name`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `Master_ibfk_4` FOREIGN KEY (`Attribute4`) REFERENCES 
      `Attribute4` (`Name`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `Master_ibfk_5` FOREIGN KEY (`Attribute5`) REFERENCES 
      `Attribute5` (`Name`) ON DELETE CASCADE ON UPDATE CASCADE;


CREATE  TABLE  `pmaster`.`Stock` (  
 `Id` int( 10  )  NOT  NULL  AUTO_INCREMENT ,
 `Name1` varchar( 10  )  NOT  NULL ,
 `Name2` decimal( 5, 2  )  NOT  NULL ,
 `Name3` varchar( 5  )  NOT  NULL ,
 `Name4` varchar( 5  )  NOT  NULL ,
 `Name5` varchar( 40  )  NOT  NULL ,
 `OtherFields1` varchar( 20  )  NOT  NULL ,
 `OtherFields2` varchar( 25  )  NOT  NULL ,
 `SoOn` varchar( 15  )  NOT  NULL ,
 `Price` decimal( 15, 2  )  NOT  NULL ,
 `Currency` varchar( 5  )  NOT  NULL ,
 PRIMARY  KEY (  `id`  ) ,
) ENGINE  = InnoDB  DEFAULT CHARSET  = latin1;

目前，Stock 表中仅 100 条记录就需要约 140 到 150 秒，随着记录的增加，所需时间会成倍增加，而 20K 条记录则需要很长时间，还没有尝试总共需要多少时间，但最后当脚本运行超过 3 小时时，我不得不退出，因为主表中没有一条记录被更新。

这意味着它可能只完成了 1% 到 2%，或者可能更少。

friend 们有什么想法可以如何以更快的方式完成此操作。
如果您需要这些数据库的数据，请告诉我将在此处生成并上传。

Answer 1

您应该考虑删除包含 1000 条记录的批处理。您应该考虑使用此查询一次，而不是两次查询。此查询将为您需要在主表中执行的每个更新生成一行。

SELECT m.Id, 
       min(s.Price) minprice, 
       avg(s.Price) avgprice, 
       max(s.Price) maxprice, 
       s.Currency 
  FROM Stock s
 INNER JOIN Master m
     ON (    s.Name1 = m.Attribute1
         AND s.Name2 = m.Attribute2
         AND s.Name3 = m.Attribute3
         AND s.Name4 = m.Attribute4
         AND s.Name5 = m.Attribute5)
  GROUP BY m.Id, s.Currency

此查询为 Stock 表中与 Master 表中的属性匹配的每组行生成一行。

如果您在 Stock 表中的 Name 项上放置一个复合索引，并在 Master 表中的 Attribute 项上放置另一个复合索引，则此查询应该相当高效。