MYSQL限制很慢

Question

我对15000条记录运行了这个查询

SELECT t.*, concat(t.address1, ', ',t.zip, ' ',t.city, ', ',c.countryName ) AS fullAddress
FROM `User` `t` INNER JOIN
     Country c
     ON t.countryCode = c.countryCode
WHERE (userType != -1 AND userType != 1 AND address1 IS NOT NULL AND zip IS NOT NULL AND city IS NOT NULL AND t.countryCode IS NOT NULL
      ) AND
       (concat( t.address1, ', ', t.zip, ' ', t.city, ', ', c.countryName ) regexp '^[0-9]+,? [^,]+, [0-9]+,? [^,]+, [a-zA-Z]+$')
LIMIT 1000

当我删除限制1000部分时，它会快速返回结果，但是对于限制1000部分，则需要很长时间。而phpmyadmin被卡住了

表结构如下

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 DEFAULT '4' COMMENT 'Type of user. 1 - Politician 2 - Supporter 3 - Prospects 4 - Non support 5 - Unknown 6 - Newsletter 7 - Petitioner',
  `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,
  `longLat` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Longitude and Latitude',
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;



ALTER TABLE `User`
  ADD PRIMARY KEY (`id`),
  ADD KEY `email` (`email`),
  ADD KEY `cindex` (`countryCode`);

如何改进此查询？

Answer 1

我认为以下几句话：

AND (concat( t.address1, ', ', t.zip, ' ', t.city, ', ', c.countryName ) regexp '^[0-9]+,? [^,]+, [0-9]+,? [^,]+, [a-zA-Z]+$')

造成了你的瓶颈。我建议删除该行并运行查询，以查看这在多大程度上提高了您的性能。如果这确实是瓶颈，以下是您可以采取的一些可能的操作：

• 验证insert上的数据，因此所有数据都是有效的。
• 如果数据库应接受无效地址，则可以创建IS_VALID_ADDRESS布尔列。
• 您可以在GENERATED列之上创建一个或两个列，并确保它是STORED。

Answer 2

尝试将索引添加到列中，这将提高结果。