mysql - 使用多个索引优化 MySQL 查询

Question

我有一个疑问:

SELECT items.*
FROM items
INNER JOIN other on other.some_key = items.some_key
WHERE
  items.partition_id = 7 AND
  items.created_date > '2016-01-01 00:00:00' AND
  (
    other.name like '%user query%' OR
    match (fulltext_candidate) against ('+user* +query*' IN BOOLEAN MODE) OR
    items.some_varchar like '%user query%'
  )
ORDER BY items.created_date
LIMIT 20 OFFSET 0;

我添加了多个索引

ALTER TABLE `items` ADD INDEX `partition_id` (`partition_id`)
ALTER TABLE `items` ADD FULLTEXT `fti` (`fulltext_candidate`)
ALTER TABLE `items` ADD INDEX `created_date` (`created_date`)
ALTER TABLE `items` ADD INDEX `some_varchar` (`some_varchar`)
ALTER TABLE `other` ADD INDEX `name` (`name`)

查询速度很慢。慢查询日志显示查询扫描了 items 表的所有行。我相信 MySQL 在查询中只使用一个索引。如果我执行简化查询，即只在 WHERE 子句中包含一列，则查询速度会非常快。

如何使用全文索引和普通索引构建单个索引来优化此查询？

Answer 1

这对于评论来说太大了。

这看起来很奇怪。

inner join other on other.some_key = items.some_key

我假设 some_key 已被索引，但有一个非常奇怪的名称？如果是，并且没有命中指定的查询索引，那么我认为它可能正在执行全表扫描，因为这是获取数据的最快方法。我需要更多地了解您的数据等才能提供帮助。举个例子。在小表中，数据库可能会对它们进行全表扫描，因为它是几千字节的数据，并且您可以通过多种方式在查询中访问它。优化器查看索引并调用它可以过滤结果集的数量，如果该数字较低，则不会使用它。