logo标签列表

MYSQL查询执行速度非常慢

phpmysqlsqlyiidatabase-performance
19

我开发了一个用户批量上传模块。有两种情况，当数据库没有记录时，我批量上传 20,000 条记录，需要约 5 小时。但当数据库已有大约 30,000 条记录时，上传非常非常慢。上传 20,000 条记录需要约 11 小时。我只是通过 fgetcsv 方法读取 CSV 文件。

if (($handle = fopen($filePath, "r")) !== FALSE) {
            while (($peopleData = fgetcsv($handle, 10240, ",")) !== FALSE) {
                if (count($peopleData) == $fieldsCount) {

//inside i check if user already exist (firstName & lastName & DOB)
//if not, i check if email exist. if exist, update the records.
//other wise insert a new record.
}}}

以下是正在运行的查询。(我使用Yii框架)
SELECT * 
FROM `AdvanceBulkInsert` `t` 
WHERE renameSource='24851_bulk_people_2016-02-25_LE CARVALHO 1.zip.csv' 
LIMIT 1

SELECT cf.*, ctyp.typeName, cfv.id as customId, cfv.customFieldId, 
       cfv.relatedId, cfv.fieldValue, cfv.createdAt 
FROM `CustomField` `cf` 
    INNER JOIN CustomType ctyp on ctyp.id = cf.customTypeId 
    LEFT OUTER JOIN CustomValue cfv on cf.id = cfv.customFieldId 
                and relatedId = 0 
    LEFT JOIN CustomFieldSubArea cfsa on cfsa.customFieldId = cf.id 
WHERE ((relatedTable = 'people' and enabled = '1') 
  AND (onCreate = '1')) 
  AND (cfsa.subarea='peoplebulkinsert') 
ORDER BY cf.sortOrder, cf.label

SELECT * 
FROM `User` `t` 
WHERE `t`.`firstName`='Franck' 
  AND `t`.`lastName`='ALLEGAERT ' 
  AND `t`.`dateOfBirth`='1971-07-29' 
  AND (userType NOT IN ("1")) 
LIMIT 1

如果存在则更新用户：
UPDATE `User` SET `id`='51394', `address1`='49 GRANDE RUE', 
                  `mobile`='', `name`=NULL, `firstName`='Franck', 
                  `lastName`='ALLEGAERT ', `username`=NULL, 
                  `password`=NULL, `email`=NULL, `gender`=0, 
                  `zip`='60310', `countryCode`='DZ', 
                  `joinedDate`='2016-02-23 10:44:18', 
                  `signUpDate`='0000-00-00 00:00:00', 
                  `supporterDate`='2016-02-25 13:26:37', `userType`=3, 
                  `signup`=0, `isSysUser`=0, `dateOfBirth`='1971-07-29', 
                  `reqruiteCount`=0, `keywords`='70,71,72,73,74,75', 
                  `delStatus`=0, `city`='AMY', `isUnsubEmail`=0, 
                  `isManual`=1, `isSignupConfirmed`=0, `profImage`=NULL, 
                  `totalDonations`=NULL, `isMcContact`=NULL, 
                  `emailStatus`=NULL, `notes`=NULL, 
                  `addressInvalidatedAt`=NULL, 
                  `createdAt`='2016-02-23 10:44:18', 
                  `updatedAt`='2016-02-25 13:26:37', `longLat`=NULL 
WHERE `User`.`id`='51394'

如果用户不存在，则插入新记录。

表引擎类型为MYISAM。只有电子邮件列具有索引。

如何优化以减少处理时间？

查询2，耗时0.4701秒，这意味着对于30,000条记录，需要14103秒，大约235分钟，即6个小时左右。

更新

CREATE TABLE IF NOT EXISTS `User` (
  `id` bigint(20) NOT NULL,
  `address1` text COLLATE utf8_unicode_ci,
  `mobile` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `firstName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '1 - female, 2-male, 0 - unknown',
  `zip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `countryCode` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `joinedDate` datetime DEFAULT NULL,
  `signUpDate` datetime NOT NULL COMMENT 'User signed up date',
  `supporterDate` datetime NOT NULL COMMENT 'Date which user get supporter',
  `userType` tinyint(2) NOT NULL,
  `signup` tinyint(2) NOT NULL DEFAULT '0' COMMENT 'whether user followed signup process 1 - signup, 0 - not signup',
  `isSysUser` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 - system user, 0 - not a system user',
  `dateOfBirth` date DEFAULT NULL COMMENT 'User date of birth',
  `reqruiteCount` int(11) DEFAULT '0' COMMENT 'User count that he has reqruited',
  `keywords` text COLLATE utf8_unicode_ci COMMENT 'Kewords',
  `delStatus` tinyint(2) NOT NULL DEFAULT '0' COMMENT '0 - active, 1 - deleted',
  `city` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `isUnsubEmail` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 - ok, 1 - Unsubscribed form email',
  `isManual` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 - ok, 1 - Manualy add',
  `longLat` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Longitude and Latitude',
  `isSignupConfirmed` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Whether user has confirmed signup ',
  `profImage` tinytext COLLATE utf8_unicode_ci COMMENT 'Profile image name or URL',
  `totalDonations` float DEFAULT NULL COMMENT 'Total donations made by the user',
  `isMcContact` tinyint(1) DEFAULT NULL COMMENT '1 - Mailchimp contact',
  `emailStatus` tinyint(2) DEFAULT NULL COMMENT '1-bounced, 2-blocked',
  `notes` text COLLATE utf8_unicode_ci,
  `addressInvalidatedAt` datetime DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `AdvanceBulkInsert` (
  `id` int(11) NOT NULL,
  `source` varchar(256) NOT NULL,
  `renameSource` varchar(256) DEFAULT NULL,
  `countryCode` varchar(3) NOT NULL,
  `userType` tinyint(2) NOT NULL,
  `size` varchar(128) NOT NULL,
  `errors` varchar(512) NOT NULL,
  `status` char(1) NOT NULL COMMENT '1:Queued, 2:In Progress, 3:Error, 4:Finished, 5:Cancel',
  `createdAt` datetime NOT NULL,
  `createdBy` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `CustomField` (
  `id` int(11) NOT NULL,
  `customTypeId` int(11) NOT NULL,
  `fieldName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `relatedTable` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `defaultValue` text COLLATE utf8_unicode_ci,
  `sortOrder` int(11) NOT NULL DEFAULT '0',
  `enabled` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `listItemTag` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `required` char(1) COLLATE utf8_unicode_ci DEFAULT '0',
  `onCreate` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `onEdit` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `onView` char(1) COLLATE utf8_unicode_ci DEFAULT '1',
  `listValues` text COLLATE utf8_unicode_ci,
  `label` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `htmlOptions` text COLLATE utf8_unicode_ci
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `CustomFieldSubArea` (
  `id` int(11) NOT NULL,
  `customFieldId` int(11) NOT NULL,
  `subarea` varchar(256) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `CustomValue` (
  `id` int(11) NOT NULL,
  `customFieldId` int(11) NOT NULL,
  `relatedId` int(11) NOT NULL,
  `fieldValue` text COLLATE utf8_unicode_ci,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM AUTO_INCREMENT=86866 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

完整的PHP代码在这里http://pastie.org/10737962

更新2

解释查询的输出结果

enter image description here

2
发布一些完整的代码，这样更有意义。同时确保在连接点上建立了索引。 - Dave
1
你知道哪一部分花费了时间吗？是更新还是其中一个查询？ - sagi
1
如果您发表问题，我们就能够真正读到它，这会有所帮助。 - RiggsFolly
2
就像我说的那样，在连接点上添加索引！ - Dave
@sagi，我正在尝试理解哪个部分需要时间。有没有办法查看它？ - dev1234
显示剩余8条评论
8个回答
15

索引是您的好朋友。

UPDATE User ... WHERE id = ... -- 需要在ID上急需建立一个索引，可能是PRIMARY KEY。

同样适用于renameSource。

SELECT * 
FROM `User` `t` 
WHERE `t`.`firstName`='Franck' 
  AND `t`.`lastName`='ALLEGAERT ' 
  AND `t`.`dateOfBirth`='1971-07-29' 
  AND (userType NOT IN ("1")) 
LIMIT 1;

需要 INDEX(firstName, lastName, dateOfBirth)；字段顺序可以是任意的（在这种情况下）。

查看每个查询以了解其所需内容，然后将该INDEX添加到表中。请阅读我的索引构建手册。

对于名字、姓氏和出生日期，分别建立索引在部分名称搜索或出生日期范围等情况下会更加有用。 - Sergiy Tytarenko
“firstName LIKE 'F%'” 不适合我的索引。然而，出生日期范围很好，因为该列在索引中是最后一列。 - Rick James
将 userType 添加到索引上可能会有帮助。 - Rick James
11

尝试以下措施来提高查询性能：

  • 在数据库结构中定义索引，并获取您想要的列。
  • 不要在选择查询中使用 * 。
  • 不要将ID放入引号中，例如 User.id='51394' ，而应该使用 User.id= 51394。
  • 如果您将ID放在引号中，则索引无法起作用。这种方法可以使查询性能提高20%。
  • 如果您正在使用 ENGINE=MyISAM ，则无法在数据库表之间定义索引，请将数据库引擎更改为 ENGINE=InnoDB 。并创建一些索引，如外键索引、全文索引。
2
我进行了一些快速测试，发现在查询时间上，将ID放在引号中或不放似乎没有明显的影响。你有支持20%速度提升的参考资料吗？ - Matt Raines
我说不要加引号输入ID，然后应用索引。 - Mukul Medatwal
@MattRaines：如果您定义了主键（将应用索引），则使用整数而不是字符串只会产生差异。您可以在此处阅读更多信息：https://dev59.com/jXNA5IYBdhLWcg3wKaYx - Victor Marchuk
该问题或任何答案中都没有提到类型强制转换。根据EXPLAIN，使用引号对查询是否使用主键没有影响。mysql> explain SELECT * FROM foo WHERE id = '1'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: foo type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index - Matt Raines
5
如果我理解正确的话，对于 SELECT * FROM AdvanceBulkInsert 的所有结果......你会运行一个请求 SELECT cf.*，并对于所有的 SELECT cf.*，你会运行 SELECT * FROM User 我认为问题在于您向数据库发送了过多的请求。
我认为您应该将所有的选择请求合并在一个大请求中。
为此： 然后对合并选择的所有结果进行更新。
您还应该逐个计时查询以找出哪些查询需要最长时间，并且您还应该使用 ANALYSE 来找出请求中哪些部分需要时间。 编辑： 现在我看到了你的代码：
一些建议：

  • 你是否为 cf.customTypeId、cfv.customFieldId、cfsa.customFieldId、user.dateOfBirth、user.firstName 和 user.lastName 创建了索引？

  • 如果你有一个使用了 CustomFieldSubArea 的 WHERE 子句，你不需要进行 LEFT JOIN CustomFieldSubArea，只需进行 simple JOIN CustomFieldSubArea 即可。

  • 您将使用 relatedId = 0 多次运行查询2，也许可以将结果保存在变量中？

  • 如果您不需要排序的数据，请删除 "ORDER BY cf.sortOrder, cf.label"。否则，在 cf.sortOrder、cf.label 上添加索引。

你能给我们 CustomValue::model()->getCustomData 的代码吗？另外，你能对查询2进行 EXPLAIN 吗？（https://dev.mysql.com/doc/refman/5.7/en/using-explain.html） - sab
这是CustomValue::model()->getCustomData的代码：http://pastie.org/10738382 - dev1234
查询2的解释已在问题中更新。顺便说一下，当我删除了cf.*并添加了cf.fieldName、cf.label、cf.required、cf.defaultValue、cf.listValues、cf.htmlOptions时，效果有所改善。现在处理来自CSV文件的30,000个用户记录只需要106分钟。 - dev1234
3
当您需要找出查询为什么需要很长时间时，您需要检查单个部分。正如您在问题中所示Explain statement可以帮助您。通常最重要的列是：
  • select_type - 这应该总是简单的查询/子查询。相关子查询会带来很多麻烦。幸运的是您没有使用任何
  • possible keys - 此选择将搜索哪些键
  • rows - 键/缓存和其他技术确定了多少候选行。较小的数字更好
  • Extra - “using”告诉您如何找到行，这是最有用的信息

查询分析

我本来会发布第一个和第三个查询的分析结果，但它们都是相当简单的查询。以下是使您困扰的查询的详细信息：

EXPLAIN SELECT cf.*, ctyp.typeName, cfv.id as customId, cfv.customFieldId, 
   cfv.relatedId, cfv.fieldValue, cfv.createdAt 
FROM `CustomField` `cf` 
    INNER JOIN CustomType ctyp on ctyp.id = cf.customTypeId 
    LEFT OUTER JOIN CustomValue cfv on cf.id = cfv.customFieldId 
                and relatedId = 0 
    LEFT JOIN CustomFieldSubArea cfsa on cfsa.customFieldId = cf.id 
WHERE ((relatedTable = 'people' and enabled = '1') 
  AND (onCreate = '1')) 
  AND (cfsa.subarea='peoplebulkinsert') 
ORDER BY cf.sortOrder, cf.label
  • 使用INNER JOIN连接CustomType ctyp和cf表，条件为ctyp.id = cf.customTypeId
  • 使用LEFT OUTER JOIN连接CustomValue cfv和cf表，条件为cf.id = cfv.customFieldId并且relatedId = 0
  • 使用LEFT JOIN连接CustomFieldSubArea cfsa和cf表，条件为cfsa.customFieldId = cf.id
  • WHERE语句中的条件为((relatedTable = 'people' and enabled = '1') AND (onCreate = '1')) AND (cfsa.subarea='peoplebulkinsert')
  • 按照cf.sortOrder和cf.label排序

解决方案

让我来解释一下上面的列表。加粗的列必须有索引。连接表是昂贵的操作，否则需要遍历两个表的所有行。如果在可连接的列上创建索引，数据库引擎会找到更快、更好的方式来执行操作。这应该是任何数据库的常见做法。

< p >斜体列不一定需要索引，但如果您有大量的行（20,000是大量），您还应该在用于搜索的列上添加索引，这可能不会对处理速度产生太大影响，但值得额外花费一点时间。

因此，您需要向这些列添加索引

  • CustomType - id
  • CustomField - customTypeId，id，relatedTable，enabled，onCreate，sortOrder，label
  • CustomValue - customFieldId
  • CustomFieldSubArea - customFieldId，subarea

要验证结果，请在添加索引后再次运行explain语句（以及可能的其他select / insert / update查询）。额外的列应该显示类似“Using Index”的内容，possible_keys列应列出使用的键（甚至每个联接查询中的两个或更多个）。

副笔：您的代码中有一些拼写错误，您应该修复它们以防其他人需要处理您的代码：作为表列的“reqruiteCount”和您引用的代码中的数组索引“fileUplaod”。

为了获得更好的性能，在适用的情况下使用复合索引，而不是大量的单独索引。MySQL 几乎从不在单个查询中使用两个索引。（这是因为使用两个索引会效率低下。） - Rick James
1

对于我的工作，我每天都需要添加一个包含524列和1万条记录的CSV文件。当我尝试使用php解析并添加记录时，情况非常糟糕。

因此，我建议您查看关于LOAD DATA LOCAL INFILE的文档。

我将自己的代码复制/粘贴为示例，但请根据您的需求进行调整。

$dataload = 'LOAD DATA LOCAL INFILE "'.$filename.'"
                REPLACE
                INTO TABLE '.$this->csvTable.' CHARACTER SET "utf8"
                FIELDS TERMINATED BY "\t"
                IGNORE 1 LINES
            ';

$result = (bool)$this->db->query($dataload);

当 $filename 是你的CSV的本地路径时（你可以使用 dirname(__FILE__) 获取它），请使用此命令。

这个SQL命令非常快（只需1或2秒即可添加/更新所有CSV）

编辑：阅读文档，但是当然你需要在用户表上拥有唯一索引才能使 "replace" 生效。因此，您不需要检查用户是否存在。您也不需要使用php解析CSV文件。

0

请确保您尝试减少查询并使用 SQL 在线编译器检查时间段，然后将其包含在项目中。

0

您似乎有每个记录进行3次查询的可能性（概率？）。这3个查询将需要3次访问数据库（如果您使用yii将记录存储在yii对象中，那么可能会使事情变得更慢）。

您能否在名字/姓氏/出生日期和电子邮件地址上添加唯一键？

如果可以，那么您只需执行INSERT....ON DUPLICATE KEY UPDATE。这将将每个记录减少到单个查询，大大加快速度。

但是，此语法的最大优点是您可以一次插入/更新多条记录（我通常坚持大约250条），因此对数据库的访问次数更少。

您可以编写一个类，只需将记录传递给该类，当记录数量达到您选择的数量时，它就会执行插入。还可以在析构函数中添加调用以插入任何最终记录。

另一种选择是将所有内容读入临时表，然后将其用作源连接到用户表以进行更新/插入。这将需要一些索引方面的努力，但是向临时表的批量加载很快，并且使用有用的索引进行更新也很快。将其用作插入的源也应该很快（如果排除已更新的记录）。

另一个问题似乎是关于你的查询，但不确定你在哪里执行它。它似乎只需要执行一次，在这种情况下可能并不重要。你没有给出CustomType表的结构，但它与Customfield连接，并且字段customTypeId没有索引。因此，该连接将会很慢。同样，在CustomValue和CustomFieldSubArea连接中，基于customFieldId进行连接，而这两个字段都没有索引（希望是唯一索引，如果这些字段不唯一，你将返回大量记录-每个可能组合的1行）。
SELECT cf.*, ctyp.typeName, cfv.id as customId, cfv.customFieldId, 
       cfv.relatedId, cfv.fieldValue, cfv.createdAt 
FROM `CustomField` `cf` 
    INNER JOIN CustomType ctyp on ctyp.id = cf.customTypeId 
    LEFT OUTER JOIN CustomValue cfv on cf.id = cfv.customFieldId 
                and relatedId = 0 
    LEFT JOIN CustomFieldSubArea cfsa on cfsa.customFieldId = cf.id 
WHERE ((relatedTable = 'people' and enabled = '1') 
  AND (onCreate = '1')) 
  AND (cfsa.subarea='peoplebulkinsert') 
ORDER BY cf.sortOrder, cf.label
0

始终在一个事务中进行批量导入

        $transaction = Yii::app()->db->beginTransaction();
        $curRow = 0;
        try
        {
            while (($peopleData = fgetcsv($handle, 10240, ",")) !== FALSE) {
            $curRow++;
            //process $peopleData
            //insert row
            //best to use INSERT ... ON DUPLICATE  KEY UPDATE
            // a = 1
            // b = 2;
            if ($curRow % 5000 == 0) {
               $transaction->commit();
               $transaction->beginTransaction();
            }
        }
        catch (Exception $ex)
        {
            $transaction->rollBack();
            $result = $e->getMessage();                    
        }
        //don't forget the remainder.
        $transaction->commit();

我曾经看到通过简单使用这种技术，导入程序的速度提高了500％。我还见过一个导入过程，每行执行600个查询（包括select、insert、update和show table structure）。这种技术将该过程加速了30％。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接