php 执行mysql查询慢_php – MYSQL查询执行速度非常慢

我开发了一个用户批量上传模块。有两种情况，当数据库有零记录时，我批量上传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;

更新2

解释查询的输出