重复时 MySQL 查询运行速度变慢

Question

以下查询(不是我写的)根据距离计算在包含 50,000 条记录的表中进行搜索。我第一次运行它(在 phpMyAdmin 中)时，它运行时间不到 0.25 秒。如果我立即再次运行它，则需要 30 多秒。我尝试添加 SQL_NO_CACHE 但没有任何区别。由于在生产中同一个查询可能会在短时间内多次运行，这是一个主要问题。

请注意，如果用户在搜索页面上选择额外的标准(关键字)，这需要在其中进行字符串搜索的连接表，问题就会消失；我假设首先完成文本搜索，从而减少距离计算。 (这就是主键上有 GROUP BY 的原因；这些扩展搜索可以产生 PK 的多个实例。)

另请注意，在我的本地测试系统上，无论我重新运行查询多少次，该查询的运行时间都不到 0.02 秒。

这是查询(从实际搜索页面生成的示例):

SELECT 
    `cc6177_clients`.*,
    ACOS(SIN(RADIANS(`b1e39c_client_lat`)) * SIN(RADIANS(51.0486151)) + COS(RADIANS(`b1e39c_client_lat`)) * COS(RADIANS(51.0486151)) * COS(RADIANS(`b1e39c_client_long`) - RADIANS(- 114.0708459))) * 3964
          AS `distance`
FROM
    `cc6177_clients`
WHERE
    (b1e39c_client_status = '1'
        AND b1e39c_client_profile_status = '1'
        AND b1e39c_client_type = 'provider')
GROUP BY `b1e39c_client_id`
HAVING (distance <= 50)
ORDER BY `b1e39c_client_company_name` ASC
LIMIT 8

这是 EXPLAIN 输出:

1   SIMPLE  cc6177_clients  ref PRIMARY,client_email,client_company_name,main_search    main_search 3   const,const,const   26564   Using index condition; Using where; Using filesort  

这是创建表:

CREATE TABLE `cc6177_clients` (
 `b1e39c_client_id` int(11) NOT NULL AUTO_INCREMENT,
 `b1e39c_client_type` enum('client','provider') COLLATE utf8_unicode_ci NOT NULL,
 `b1e39c_client_login_type` enum('normal','social') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'normal',
 `b1e39c_client_oauth_provider` enum('facebook','gplus') COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_oauth_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_access_token` text COLLATE utf8_unicode_ci,
 `b1e39c_client_referrer_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_nickname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `b1e39c_client_first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_picture` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_country_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_contact_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_lat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_long` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_postal_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_status` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '0=>Not Active,1=>Active',
 `b1e39c_client_activation_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'It is used for account activation or reset password request key',
 `b1e39c_client_verified` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT 'It is used to verify client email address(0=>not Verified,1=>Verified)',
 `b1e39c_client_registered_on` datetime NOT NULL,
 `b1e39c_client_login_failed_count` int(2) NOT NULL,
 `b1e39c_client_login_failed_time` datetime DEFAULT NULL,
 `b1e39c_client_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_last_login` datetime DEFAULT NULL,
 `b1e39c_client_view_status` enum('0','1') COLLATE utf8_unicode_ci NOT NULL,
 `b1e39c_client_delete_status` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '0=>client deleted, 1=> client active',
 `b1e39c_client_verify_email_link_exp` datetime DEFAULT NULL,
 `b1e39c_client_company_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_website_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_company_logo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Provider Company Logo',
 `b1e39c_client_desc` text COLLATE utf8_unicode_ci,
 `b1e39c_client_category_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `b1e39c_client_sub_category_id` int(11) DEFAULT NULL,
 `b1e39c_client_claim_option` enum('0','1','2') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '0=>default,1=>User Added By Admin,2=>claim then make main provider',
 `b1e39c_client_membership_id` int(11) DEFAULT NULL COMMENT 'client current membership table unique id',
 `b1e39c_client_cur_membership_id` int(11) DEFAULT NULL COMMENT 'client currency membership',
 `b1e39c_client_membership_type` enum('free','paid') COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'client membership type',
 `b1e39c_client_profile_status` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
 `b1e39c_client_profile_cover` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`b1e39c_client_id`),
 UNIQUE KEY `client_email` (`b1e39c_client_email`),
 KEY `client_company_name` (`b1e39c_client_company_name`),
 KEY `main_search` (`b1e39c_client_status`,`b1e39c_client_profile_status`,`b1e39c_client_type`)
) ENGINE=InnoDB AUTO_INCREMENT=55931 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

不要到处使用 VARCHAR(255)；使用合理的限制。 (这将有助于提高当前所需的临时表的性能。

在 WHERE 子句中使用“边界框”，加上 INDEX(lat), INDEX(long)。 (见下文。)

不要用常量前缀 (b1e39c_client_) 使 SQL 困惑。 (人类的东西。)

去掉GROUP BY；除了减慢查询速度之外，它对这个 查询没有任何帮助。 (这消除了不必要的数据传递。)

这些都无法解释为什么第一次运行明显快于下一次运行。

“边界框”类似于

 AND lat  BETWEEN .. AND ..
 AND long BETWEEN .. AND ..

填充的值距源纬度/经度 50 个单位。 (请注意，对于 long 测试，您需要除以 cosd(lat)。)

这会将查询速度提高一个数量级，从而使查询速度如此之快以至于您的原始问题变得没有实际意义。

没有边界框，这可能会有所帮助:

INDEX(status, profile_status, type,   -- in any order
      company_name)   -- last

如果你想追问原来的问题，看看你是否可以得到 EXPLAIN SELECT ... 来显示两个不同的输出。