MYSQL 查询执行速度很慢

2022-01-04 00:00:00 sql php mysql yii database-performance

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

if (($handle = fopen($filePath, "r")) !== FALSE) {而 (($peopleData = fgetcsv($handle, 10240, ",")) !== FALSE) {if (count($peopleData) == $fieldsCount) {//在我内部检查用户是否已经存在(firstName & lastName & DOB)//如果没有，我检查电子邮件是否存在.如果存在，更新记录.//否则插入新记录.}}}

以下是运行的查询.(我用的是 Yii 框架)

SELECT *从`AdvanceBulkInsert``t`WHERE renameSource='24851_bulk_people_2016-02-25_LE CARVALHO 1.zip.csv'限制 1选择 cf.*、ctyp.typeName、cfv.id 作为 customId、cfv.customFieldId、cfv.relatedId、cfv.fieldValue、cfv.createdAt从`CustomField``cf`ctyp.id = cf.customTypeId 上的内部连接 CustomType ctyp左外连接 CustomValue cfv on cf.id = cfv.customFieldId和相关 ID = 0左连接 CustomFieldSubArea cfsa on cfsa.customFieldId = cf.idWHERE ((relatedTable = 'people' and enabled = '1')AND (onCreate = '1'))AND (cfsa.subarea='peoplebulkinsert')ORDER BY cf.sortOrder, cf.label选择 *来自`用户``t`WHERE `t`.`firstName`='Franck'AND `t`.`lastName`='ALLEGAERT 'AND `t`.`dateOfBirth`='1971-07-29'AND (userType NOT IN ("1"))限制 1

如果存在更新用户:

UPDATE `User` SET `id`='51394', `address1`='49 GRANDE RUE',`mobile`='', `name`=NULL, `firstName`='Franck',`lastName`='ALLEGAERT'，`用户名`=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哪里`用户`.`id`='51394'

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

表引擎类型是MYISAM.只有 email 列有索引.

我该如何优化以减少处理时间?

查询 2，耗时 0.4701 秒，这意味着对于 30 000 条记录，将耗时 14103 秒，即大约 235 分钟.大约 6 小时.

更新

如果不存在则创建表`用户`(`id` bigint(20) 非空，`address1` 文本整理 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 - 女性，2-男性，0 - 未知'，`zip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL，`countryCode` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL，`joinedDate` 日期时间默认为 NULL，`signUpDate` datetime NOT NULL COMMENT '用户注册日期'，`supporterDate` datetime NOT NULL COMMENT '用户获得支持者的日期'，`userType` tinyint(2) 非空，`signup` tinyint(2) NOT NULL DEFAULT '0' COMMENT '用户是否遵循注册过程 1 - 注册，0 - 不注册'，`isSysUser` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 - 系统用户，0 - 不是系统用户'，`dateOfBirth` 日期 DEFAULT NULL COMMENT '用户出生日期'，`reqruiteCount` int(11) DEFAULT '0' COMMENT '他要求的用户数'，`keywords` 文本 COLLATE utf8_unicode_ci COMMENT 'Kewords',`delStatus` tinyint(2) NOT NULL DEFAULT '0' COMMENT '0 - 活动，1 - 删除'，`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 '经度和纬度'，`isSignupConfirmed` tinyint(4) NOT NULL DEFAULT '0' COMMENT '用户是否已确认注册'，`profImage` tinytext COLLATE utf8_unicode_ci COMMENT '配置文件图像名称或 URL'，`totalDonations` float DEFAULT NULL COMMENT '用户捐款总额'，`isMcContact` tinyint(1) DEFAULT NULL COMMENT '1 - Mailchimp contact',`emailStatus` tinyint(2) DEFAULT NULL COMMENT '1-bounced, 2-blocked',`notes` 文本整理 utf8_unicode_ci，`addressInvalidatedAt` 日期时间默认 NULL，`createdAt` 日期时间非空，`updatedAt` 日期时间默认 NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;如果不存在，则创建表`AdvanceBulkInsert`(`id` int(11) 非空，`source` varchar(256) 非空，`renameSource` varchar(256) 默认为空，`countryCode` varchar(3) 非空，`userType` tinyint(2) 非空，`size` varchar(128) 非空，`errors` varchar(512) 非空，`status` char(1) NOT NULL COMMENT '1:Queued, 2:In Progress, 3:Error, 4:Finished, 5:Cancel',`createdAt` 日期时间非空，`createdBy` int(11) 非空) 引擎=MyISAM 默认字符集=latin1;如果不存在`CustomField`，则创建表(`id` int(11) 非空，`customTypeId` int(11) 非空，`fieldName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL，`relatedTable` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL，`defaultValue` 文本整理 utf8_unicode_ci，`sortOrder` int(11) NOT NULL DEFAULT '0',`已启用` 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` 文本整理 utf8_unicode_ci，`label` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL，`htmlOptions` 文本整理 utf8_unicode_ci) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;如果不存在则创建表`CustomFieldSubArea`(`id` int(11) 非空，`customFieldId` int(11) 非空，`subarea` varchar(256) COLLATE utf8_unicode_ci NOT NULL) ENGINE=MyISAM AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;如果不存在`CustomValue`，则创建表(`id` int(11) 非空，`customFieldId` int(11) 非空，`relatedId` int(11) 非空，`fieldValue` 文本整理 utf8_unicode_ci，`createdAt` 时间戳 NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM AUTO_INCREMENT=86866 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

完整的 PHP 代码在这里

解决方案

索引是你的朋友.

UPDATE User ... WHERE id = ... -- 迫切需要一个 ID 索引，可能是 PRIMARY KEY.

renameSource 类似.

SELECT *来自`用户``t`WHERE `t`.`firstName`='Franck'AND `t`.`lastName`='ALLEGAERT 'AND `t`.`dateOfBirth`='1971-07-29'AND (userType NOT IN ("1"))限制 1；

需要 INDEX(firstName, lastName, dateOfBirth);字段可以按任何顺序排列(在本例中).

查看每个查询以了解它需要什么，然后将该INDEX 添加到表中.阅读我关于构建索引的食谱.

I have developed a user bulk upload module. There are 2 situations, when I do a bulk upload of 20 000 records when database has zero records. Its taking about 5 hours. But when the database already has about 30 000 records the upload is very very slow. It takes about 11 hours to upload 20 000 records. I am just reading a CSV file via fgetcsv method.

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.
}}}

Below are the queries that run. (I am using Yii framework)

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

If exist update the user:

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'

If user don't exist, insert new record.

Table engine type is MYISAM. Only the email column has a index.

How can I optimize this to reduce the processing time?

Query 2, took 0.4701 seconds which means for 30 000 records it will take 14103 sec, which is about 235 minutes. approx 6 hours.

Update

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;

Entire PHP Code is here http://pastie.org/10737962

Update 2

Explain output of the Query

解决方案

Indexes are your friend.

UPDATE User ... WHERE id = ... -- Desperately needs an index on ID, probably PRIMARY KEY.

Similarly for 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;

Needs INDEX(firstName, lastName, dateOfBirth); the fields can be in any order (in this case).

Look at each query to see what it needs, then add that INDEX to the table. Read my Cookbook on building indexes.

相关文章