文章/答案/技术大牛

发布

社区首页 >问答首页 >Mysql全文搜索与其他索引数据

问Mysql全文搜索与其他索引数据
EN

Database Administration用户

提问于 2020-02-19 16:42:17

回答 1查看 1.8K关注 0票数 1

在这个问题中获得一些mysql优化建议之后。能够将性能提高到可接受的水平(在使用EventType和event_log_domain_id组合执行查找时)。回顾一下我的数据库表大约有1100万条记录，这些是列(相关的)：

id| EventTime | EventType | EventName ... | event_log_domain_id

然而，现在，在EventType和event_log_domain_id的筛选(完全匹配)之上，我需要将EventName添加到mix中并执行通配符搜索。

在查看mysql文档和可用的选项之后，全文搜索选项看起来非常有希望，但是运行这个选项：

SELECT COUNT(*) FROM `access_logs` 
WHERE `access_logs`.`event_log_domain_id` IN (8, 59, 920, 1054, 2227)
  AND (MATCH( EventName ) AGAINST( 'business' ))
  AND `access_logs`.`EventType` IN (1, 5)

而这个：

SELECT  `access_logs`.* FROM `access_logs` 
WHERE `access_logs`.`event_log_domain_id` IN (8, 59, 920, 1054, 2227) 
  AND (MATCH( EventName ) AGAINST( 'business' ))
  AND `access_logs`.`EventType` IN (1, 5) 
ORDER BY id desc LIMIT 50

无论如何，全文索引似乎都是使用的，我已经确认，首先通过运行explain，其中的内容如下所示：

id| select_type | table       | type     | possible_keys                                                                                                 | key            | key_len | ref  | rows | extra
1 | SIMPLE      | access_logs | fulltext | IndexEventType, event_log_domain_id, EventTypeAndeventlogdomainid,eventlogdomainidAndEventType,IndexEventName | IndexEventName | 0       | null | 1    | Using where; Using filesort

执行上述任何一个查询大约需要95秒时间。如果我使用事件类型获取全文搜索，查询将在几百毫秒内运行。

当我自己运行全文搜索时，如下所示：

SELECT count(*) FROM `access_logs` 
WHERE (MATCH( EventName ) AGAINST( 'business' ))

大约在9秒内执行。这本身是相当高的，但考虑到行数，它是“可接受的”。

尝试过修改内部查询别名，如下所示：

SELECT
    access_logs.*
FROM
    inbound_access_logs access_logs
JOIN 
 (
   SELECT  `access_logs`.* FROM `access_logs` 
    WHERE `access_logs`.`event_log_domain_id` IN (8, 59, 920, 1054, 2227)
    AND`access_logs`.`EventType` IN (1, 5)
 ) AS filtered_query
ON access_logs.id = filtered_query.id
where access_logs.EventName like '%business%'

它在几秒钟内执行，但如果我对内部查询别名使用相同的方法，并将其用于全文搜索。

SELECT
    access_logs.*
FROM
    inbound_access_logs access_logs
JOIN 
 (
   SELECT  `access_logs`.* FROM `access_logs` 
    WHERE `access_logs`.`event_log_domain_id` IN (8, 59, 920, 1054, 2227)
    AND`access_logs`.`EventType` IN (1, 5)
 ) AS filtered_query
ON access_logs.id = filtered_query.id
where MATCH( access_logs.EventName ) AGAINST( 'business' )

再来8-9秒。我想也许我把类似的查询放在里面，但是我真的想优化它，这样我就不用去碰它了。

我正在考虑的另一个选择是创建一个新表，如下所示：

id | access_log_id | EventName

会在数据插入时写入此表。在这个表上，我将对EventName列进行全文索引，在进行查找时我将加入这个表，希望这将使查询优化器考虑“更好”，因为它是两个表，希望在相同的搜索中每个表使用一个索引。不知道这是不是个好主意。

我的问题是:如何使用我前面提到的其他列来优化查询，以便在EventName上进行筛选？我的方法似乎都不是很好

更新:我一直在试验内部查询别名，比如：

SELECT
    access_logs.*
FROM
    inbound_access_logs access_logs
JOIN 
 (
   SELECT  `access_logs`.* FROM `access_logs` 
    WHERE `access_logs`.`event_log_domain_id` IN (8, 59, 920, 1054, 2227)
    AND`access_logs`.`EventType` IN (1, 5)
 ) AS filtered_query
ON access_logs.id = filtered_query.id
where access_logs.EventName like '%business%'

时间取决于EventName和其他条件。从1秒到60秒不等。这是令人沮丧的，获得一些更接近的运行时会更好。

我正在使用m3.media机器在AWS RDS上运行，不确定这是否有区别。最好知道我还能尝试什么，或者如何更有把握地构建。因为一旦我运行了查询，它就会留在数据库缓存中，并在第二次运行时执行得很快。

更新1

显示表create语句：

'CREATE TABLE `access_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `EventTime` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `EventSize` int(10) unsigned NOT NULL DEFAULT ''0'',
  `EventScore` double DEFAULT NULL,
  `EventCustomer` varchar(255) DEFAULT NULL,
  `event_log_domain_id` smallint(6) DEFAULT NULL,
  `EventIdentifier` varchar(255) DEFAULT NULL,
  `EventType` tinyint(3) unsigned NOT NULL DEFAULT ''0'',
  `EventName` varchar(255) DEFAULT NULL,
  `utc` tinyint(1) DEFAULT ''0'',
  `job_queue_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `EventTime` (`EventTime`),
  KEY `IndexIdentifier` (`EventIdentifier`),
  KEY `IndexType` (`EventType`),
  KEY `IndexUserType` (`EventIdentifier`,`EventType`),
  KEY `event_log_domain_id` (`event_log_domain_id`),
  KEY `job_queue_id` (`job_queue_id`),
  KEY `EventTypeAndeventogdomainid` (`EventType`,`event_log_domain_id`),
  KEY `eventogdomainidAndEventType` (`event_log_domain_id`,`EventType`),
  FULLTEXT KEY `IndexEventName` (`EventName`)
) ENGINE=InnoDB AUTO_INCREMENT=123163064 DEFAULT CHARSET=latin1'

我们真正用这个页面做什么。

我们已经构建了一个对这些事件进行过滤的页面，有5个过滤器：

EventName (通配符搜索%search_string%，并不总是提供)
EventCustomer (前向匹配search_string%，并不总是提供)
EventIdentifier (前向匹配search_string%，并不总是提供)
EventType (完全匹配，一个或多个ids，但并不总是提供)
event_log_domain_id (完全匹配，总是提供一个或多个ids )

可以提供多个筛选器，因此可以为所有日志域提供名称和客户。或者只是特定日志域等的事件类型。

我们在页面上有一个分页，所以我们确实需要执行一个计数来获得行计数。

然后，我们获得50/100记录在当时，所以我们也限制。我们还提供默认顺序，或者提供按少数列(EventCustomer、EventIdentifier、EventType和EventTime)排序的能力。

mysql

performance

full-text-search

回答 1

Database Administration用户

发布于 2020-02-25 06:16:22

据我所知，需要首先执行FULLTEXT。也就是说，您的JOIN尝试会使情况变得更糟。

access_logs.*可能有一些TEXT列，对吗？(请提供SHOW CREATE TABLE，这样我就能猜到这么多了。)这可是个很大的问题。在完成所有筛选之前，最好只获取id，然后再执行一个额外的JOIN来获取*。id是轻量级的，*可能很大。如果你把大部分行都扔了，那就太浪费了。

试试看：

SELECT  g.*
    FROM ( SELECT  id
            FROM  access_logs
            WHERE  MATCH(EventName) AGAINST('business' IN BOOLEAN MODE) 
         ) AS f
    JOIN  access_logs AS g USING(id)
    WHERE  g.domain_id` IN (8, 59, 920, 1054, 2227)
      AND  g.EventType IN (1, 5)

LIMIT版本需要更多的关注。首先筛选到50个ids，然后获取其余的列。

SELECT i.*
    FROM ( SELECT  g.id
            FROM ( SELECT  id
                    FROM  access_logs
                    WHERE  MATCH(EventName) AGAINST('business' IN BOOLEAN MODE) 
                 ) AS f
            JOIN  access_logs AS g USING(id)
            WHERE  g.domain_id` IN (8, 59, 920, 1054, 2227)
              AND  g.EventType IN (1, 5) 
            ORDER BY id DESC
            LIMIT 50    -- Only here, not at the outer layer
             ) AS h
    JOIN access_logs AS i  USING(id)
    ORDER BY id DESC   -- Yes, this is needed a second time

票数 0

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/260052

复制

相似问题

问Mysql全文搜索与其他索引数据
EN

更新1

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Mysql全文搜索与其他索引数据EN

更新1

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Mysql全文搜索与其他索引数据
EN