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

当我删除 limit 1000 部分时，它会很快返回结果，但是 limit 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]+$')

造成了瓶颈。我建议删除该行并运行查询以查看性能提高了多少。如果这确实是瓶颈，您可以采取以下一些可能的措施:

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