MYSQL查询执行非常慢。
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) {
//在里面我检查用户是否已经存在（名字、姓氏、出生日期）
//如果不存在，我检查电子邮件是否存在。如果存在，更新记录。
//否则插入新记录。
}}}
以下是运行的查询。 （我正在使用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 - 女性, 2-男性, 0 - 未知', `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 '用户注册日期', `supporterDate` datetime NOT NULL COMMENT '用户成为支持者的日期', `userType` tinyint(2) NOT NULL, `signup` tinyint(2) NOT NULL DEFAULT '0' COMMENT '用户是否遵循注册流程 1 - 注册, 0 - 未注册', `isSysUser` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 - 系统用户, 0 - 非系统用户', `dateOfBirth` date DEFAULT NULL COMMENT '用户出生日期', `reqruiteCount` int(11) DEFAULT '0' COMMENT '用户招募计数', `keywords` text COLLATE utf8_unicode_ci COMMENT '关键词', `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 - 正常, 1 - 取消订阅邮件', `isManual` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 - 正常, 1 - 手动添加', `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联系人', `emailStatus` tinyint(2) DEFAULT NULL COMMENT '1-退回, 2-封锁', `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
查询的解释输出
MYSQL查询的性能很慢
问题原因：
- 发送了过多的请求到数据库
- 没有正确使用索引
- 使用了不必要的LEFT JOIN
- 没有对数据进行排序
解决方法：
- 将所有的SELECT请求合并为一个大的请求
- 使用EXISTS IN或JOIN替换某些SELECT语句
- 确保正确使用索引
- 移除不必要的LEFT JOIN
- 缩小查询范围，减少重复查询
- 如果不需要排序，移除ORDER BY语句
以下是一些具体的解决方法：
- 为cf.customTypeId, cfv.customFieldId, cfsa.customFieldId, user.dateOfBirth, user.firstName, user.lastName添加索引
- 在不需要使用CustomFieldSubArea的情况下，使用JOIN替换LEFT JOIN
- 如果可以，将查询结果保存在变量中，避免重复查询
- 如果不需要排序，移除ORDER BY cf.sortOrder, cf.label语句，如果需要排序，为cf.sortOrder, cf.label添加索引
另外，给出了两个具体的问题解决方法：
- 给出了CustomValue::model()->getCustomData的代码，可以查看具体实现
- 建议使用EXPLAIN命令来分析查询语句，找出性能瓶颈部分
最后，作者提到通过修改查询语句，移除cf.*并添加cf.fieldName, cf.label, cf.required, cf.defaultValue, cf.listValues, cf.htmlOptions，查询性能有所改善，处理30000条记录的时间从原来的106分钟减少到了较短的时间。
MySQL查询执行非常慢的问题可能有以下原因和解决方法：
1. 数据库结构中定义索引，并只选择需要的列。
解决方法：在数据库表结构中定义索引，并且只选择需要的列。
2. 在SELECT查询中不要使用 *。
解决方法：避免在SELECT查询中使用 *，而是明确指定需要的列。
3. 不要将ID放在引号中，如 User.id='51394'，而是使用 User.id= 51394。
解决方法：避免将ID放在引号中，将其作为整数使用。
4. 如果将ID放在引号中，索引将不起作用。这种方法可以使查询性能提高20%。
解决方法：避免将ID放在引号中，而是将其作为整数使用，并应用索引。
5. 如果正在使用 ENGINE=MyISAM，则无法在数据库表之间定义索引，需要将数据库引擎更改为 ENGINE=InnoDB。并创建一些索引，如外键、全文索引。
解决方法：将数据库引擎更改为 ENGINE=InnoDB，并在数据库表之间创建一些索引，如外键和全文索引。
根据快速测试，将ID放在引号中与否似乎对查询时间没有明显影响。是否有相关引用来支持20%的速度提升？
我说的是不要将ID放在引号中，并且应用索引。
使用整数而不是字符串只有在定义主键时才会产生影响（这将应用索引）。您可以在此处阅读更多信息：stackoverflow.com/questions/1071180/…
在该问题和任何答案中都没有提到类型强制转换。根据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
MYSQL查询执行非常慢的问题通常是由于缺乏适当的索引引起的。以下是一些出现这个问题的原因和解决方法。
首先，我们可以看到在UPDATE User ... WHERE id = ...查询中，ID字段需要一个索引，可能是主键索引。同样，在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)来创建这个索引，字段的顺序可以任意排列。
对于像部分名称搜索或出生日期范围这样的情况，分别创建firstname、lastname和dateofbirth的索引会更有用。例如，firstName LIKE 'F%'在我的索引中表现不佳，但是出生日期范围查询将表现良好，因为该列在索引的最后。
添加userType字段到索引中可能会有所帮助。
总结起来，解决MYSQL查询执行缓慢的问题的方法是对每个查询进行分析，并根据需要在表中添加适当的索引。可以参考我的索引构建手册获取更多信息。通过优化索引，可以显著提高查询性能。