mysql - 如何加快 MySQL 中 'where MATCH AGAINST and DATETIME' 搜索的速度？

Question

我有一个相当大的数据库，我想在其中搜索/过滤 MEDIUMTEXT(标签)、DATETIME(创建时间)和 BIT(包含)列。

假设数据库如下所示:

+------+-----------------------+--------------------------+---------+
|  id  |      created_time     |           tags           | include |
|(INT) |       (DATETIME)      |       (MEDIUMTEXT)       |  (BIT)  |
+------+-----------------------+--------------------------+---------+
|  1   | '2017-02-20 08:58:06' |         'client 1'       |    1    |
|  2   | '2017-03-01 18:12:00' | 'client 1 and client 2'  |    0    |
|  3   | '2017-03-02 02:52:35' | 'client 3 plus client 1' |    0    |
|  4   | '2017-03-03 12:41:58' |         'client 1'       |    1    |
|  5   | '2017-03-05 18:03:12' | 'client 2, client 3'     |    1    |
|  6   | '2017-03-06 20:25:45' | 'client 1 and client 3'  |    0    |
|  7   | '2017-03-08 22:51:22' |         'client 1'       |    1    |
+------+-----------------------+--------------------------+---------+

我已对 DATETIME 和 BIT 列建立了索引，并且在 MEDIUMTEXT 列上使用了 FULLTEXT 索引。

如果我运行此语句:

select statement 1
------------------
SELECT COUNT(*)
FROM database
WHERE (MATCH(tags) AGAINST('"client 1"' IN BOOLEAN MODE))
AND created_time >= '2017-03-01 12:00:00'
AND include = 0;

需要 14 秒。运行并返回 6700 行。

但是，如果我运行:

select statement 2
------------------
SELECT COUNT(*)
FROM database
WHERE (MATCH(tags) AGAINST('"client 1"' IN BOOLEAN MODE));

需要 0.4 秒。运行并返回 145000 行，如果我运行:

select statement 3
------------------
SELECT COUNT(*)
FROM database
WHERE created_time >= '2017-03-01 12:00:00'
AND include = 0;

需要 0.5 秒。运行并返回 25000 行。

现在我的问题是，如何让“select statements 1”运行得更快？我是否需要先运行“select 语句 2”，然后对结果运行“select 语句 3”？如果是这样，怎么办？有人有 UNION 的经验吗？我可以在这里使用它吗？或者有没有办法可以在 INDEX 和 FULLTEXT 上创建多列索引？

<小时/>

添加了有关实际表格(而不是上面的示例)的信息，特别感谢@rick-james

Query 1:
SELECT  SQL_NO_CACHE count(*)
    FROM  Twitter_tweet
    WHERE  created_time >= '2017-01-01 23:00:00'
      AND  MATCH(tags) AGAINST('\"dkpol\"' IN BOOLEAN MODE);
Query 2:
SELECT  SQL_NO_CACHE count(*)
    FROM  Twitter_tweet
    WHERE  MATCH(tags) AGAINST('\"dkpol\"' IN BOOLEAN MODE);
Query 3:
SELECT  SQL_NO_CACHE count(*)
    FROM  Twitter_tweet
    WHERE  created_time >= '2017-01-01 23:00:00';

解释 3 个查询:

+----+-------------+---------------+----------+----------------------------------------------------+--------------------+---------+-------+--------+----------+-----------------------------------+
| id | select_type | table         | type     | possible_keys                                      | key                | key_len | ref   | rows   | filtered | Extra                             |
+----+-------------+---------------+----------+----------------------------------------------------+--------------------+---------+-------+--------+----------+-----------------------------------+
| 1  | SIMPLE      | Twitter_tweet | fulltext | created_time_INDEX,SELECT_tags_INDEX,tags_FULLTEXT | tags_FULLTEXT      | 0       | const | 1      | 50.00    | Using where; Ft_hints: no_ranking |
+----+-------------+---------------+----------+----------------------------------------------------+--------------------+---------+-------+--------+----------+-----------------------------------+
| 2  | SIMPLE      |               |          |                                                    |                    |         |       |        |          | Select tables optimized away      |
+----+-------------+---------------+----------+----------------------------------------------------+--------------------+---------+-------+--------+----------+-----------------------------------+
| 3  | SIMPLE      | Twitter_tweet | range    | created_time_INDEX,SELECT_tags_INDEX               | created_time_INDEX | 6       |       | 572286 | 100.00   | Using where; Using index           |
+----+-------------+---------------+----------+----------------------------------------------------+--------------------+---------+-------+--------+----------+-----------------------------------+

显示创建表:

CREATE TABLE `Twitter_tweet` (
   `post_id` bigint(20) unsigned NOT NULL,
   `from_user_id` bigint(20) unsigned NOT NULL,
   `from_user_username` tinytext,
   `from_user_fullname` tinytext,
   `message` mediumtext,
   `created_time` datetime DEFAULT NULL,
   `quoted_post_id` bigint(20) unsigned DEFAULT NULL,
   `quoted_user_id` bigint(20) unsigned DEFAULT NULL,
   `quoted_user_username` tinytext,
   `quoted_user_fullname` tinytext,
   `to_post_id` bigint(20) unsigned DEFAULT NULL,
   `to_user_id` bigint(20) unsigned DEFAULT NULL,
   `to_user_username` tinytext,
   `truncated` bit(1) DEFAULT NULL,
   `is_retweet` bit(1) DEFAULT NULL,
   `retweeting_post_id` bigint(20) unsigned DEFAULT NULL,
   `retweeting_user_id` bigint(20) unsigned DEFAULT NULL,
   `retweeting_user_username` tinytext,
   `retweeting_user_fullname` tinytext,
   `tags` text,
   `mentions_user_id` text,
   `mentions_user_username` text,
   `mentions_user_fullname` text,
   `post_urls` text,
   `count_favourite` int(11) DEFAULT NULL,
   `count_retweet` int(11) DEFAULT NULL,
   `lang` tinytext,
   `location_longitude` float(13,10) DEFAULT NULL,
   `location_latitude` float(13,10) DEFAULT NULL,
   `place_id` tinytext,
   `place_fullname` tinytext,
   `source` tinytext,
   `fetchtime` datetime DEFAULT NULL,
   PRIMARY KEY (`post_id`),
   UNIQUE KEY `post_id_UNIQUE` (`post_id`),
   KEY `from_user_id_INDEX` (`from_user_id`),
   KEY `quoted_user_id_INDEX` (`quoted_user_id`),
   KEY `to_user_id_INDEX` (`to_user_id`),
   KEY `retweeting_user_id_INDEX` (`retweeting_user_id`),
   KEY `created_time_INDEX` (`created_time`),
   KEY `retweeting_post_id_INDEX` (`retweeting_post_id`),
   KEY `post_all_id_INDEX` (`post_id`,`retweeting_post_id`,`to_post_id`,`quoted_post_id`),
   KEY `quoted_post_id_INDEX` (`quoted_post_id`),
   KEY `to_post_id_INDEX` (`to_post_id`),
   KEY `is_retweet_INDEX` (`is_retweet`),
   KEY `SELECT_tags_INDEX` (`created_time`,`is_retweet`,`post_id`),
   FULLTEXT KEY `tags_FULLTEXT` (`tags`),
   FULLTEXT KEY `mentions_user_id_FULLTEXT` (`mentions_user_id`),
   FULLTEXT KEY `message_FULLTEXT` (`message`),
   FULLTEXT KEY `content_select` (`tags`,`message`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

计时时，做两件事:

• 关闭查询缓存(或我们SELECT SQL_NO_CACHE...)
• 运行查询两次。

运行查询时，会发生以下情况:

1. 检查 QC 以查看最近是否运行了完全相同相同的查询；如果是，则返回该运行的结果。这通常需要大约 1 毫秒。 (这不是您给出的示例中发生的情况。)

2. 执行查询。现在有多个子案例:

   • 如果“缓冲池”处于“冷”状态，则可能会涉及大量 I/O。 I/O 速度慢。这可以解释你跑了14秒的原因。
   • 如果所需的数据缓存在 RAM 中，那么它会运行得更快。这可能解释了为什么其他两次运行要快得多。

如果在进行这些补偿后，您仍然遇到问题，请针对这些情况提供 SHOW CREATE TABLE 和 EXPLAIN SELECT ...。 (可能还涉及其他因素。)

架构批判

提高性能(某些)的一种方法是缩小数据。

• langtinytext，--有一个5个字符的标准
• BIGINT 占用 8 个字节。 4 字节的INT 足以供世界上一半的人使用。 (但首先要验证您的 AUTO_INCRMENTs 没有烧毁大量 ID。)
• 出于微妙的原因，VARCHAR(255) 比 TINYTEXT 更好，尽管它们看起来相当。只要可行，请使用小于 255 的值。
• FLOAT(13,10) 有一些问题；我建议 DECIMAL(8,6)/(9,6) 足以区分两个相邻的高音扬声器(GPS 并不那么精确)。
• PRIMARY KEY 是一个UNIQUE key ；摆脱多余的UNIQUE。
• 使用 INDEX(a, b)，您不再需要 INDEX(a)。 (至少2例)

批量

您将如何处理结果集中的 6700 或 25000 行？我问这个问题是因为返回大量行的工作是性能问题的一部分。如果您的下一步是进一步削减输出，那么在 SQL 中进行削减可能会更好。

分析

查看第二组查询:

1. FT + 日期范围。首先进行 FT 搜索，然后按日期进一步过滤。
2. FT，计算结果，退出。请注意，所有这些都是在 EXPLAIN 中完成的，因此“选择已优化的表”——并且 EXPLAIN 时间与 SELECT 相同> 时间。
3. 扫描一个索引估计 572K 行——完全在索引中完成。这是无法改进的。然而，情况可能会变得更糟——例如添加看似无害的 AND include = 0。在这种情况下，它将无法仅使用索引，而是必须在索引和数据之间来回切换——成本要高得多。这种情况的解决办法是:INDEX(include,created_time)，它运行得更快。

COUNT(*) 可能很便宜——不需要返回大量数据，通常可以在索引等内完成。

SELECT col1, col2 比 SELECT * 更快 - 特别是因为 TEXT 列。