MySQL 查询 where 语句慢

Question

我有一个数据库，我在其中存储日语词典:单词、读物、标签、类型、其他语言的含义(这里英语最重要，但也有其他一些)等等。

现在，我想使用 Datatables js 插件创建一个界面，以便用户可以查看表格并使用一些过滤选项(例如，仅显示动词，​​或查找包含“狗”的条目)。然而，我正在努力处理使用过滤时可能非常慢的查询......我已经加快了很多速度，但它仍然不是很好。

这是我的基本查询:

select
v.id,
(
    select group_concat(distinct vke.kanji_element separator '; ') from vocabulary_kanji_element as vke
    where vke.vocabulary_id = v.id
) kanji_notation,
(
    select group_concat(distinct vre.reading_element separator '; ') from vocabulary_reading_element as vre
    where vre.vocabulary_id = v.id
) reading_notation,
(
    select group_concat(distinct vsg.gloss separator '; ') from vocabulary_sense_gloss as vsg
    join vocabulary_sense as vs on vsg.sense_id = vs.id
    join language as l on vsg.language_id = l.id and l.language_code = 'eng'
    where vs.vocabulary_id = v.id
) meanings,
(
    select group_concat(distinct pos.name_code separator '; ') from vocabulary_sense as vs
    join vocabulary_sense_has_pos as vshp on vshp.sense_id = vs.id
    join part_of_speech as pos on pos.id = vshp.pos_id
    where vs.vocabulary_id = v.id
) pos
from vocabulary as v
join vocabulary_sense as vs on vs.vocabulary_id = v.id
join vocabulary_sense_gloss as vsg on vsg.sense_id = vs.id
join vocabulary_kanji_element as vke on vke.vocabulary_id = v.id
join vocabulary_reading_element as vre on vre.vocabulary_id = v.id
join language as l on l.id = vsg.language_id and l.language_code = 'eng'
join vocabulary_sense_has_pos as vshp on vshp.sense_id = vs.id
join part_of_speech as pos on pos.id = vshp.pos_id
where
-- pos.name_code = 'n' and
(vsg.gloss like '%eat%' OR vke.kanji_element like '%eat%' OR vre.reading_element like '%eat%')
group by v.id
order by v.id desc
-- limit 3900, 25

输出是这样的:

|id    | kanji_notation | reading_notation | meanings  | pos  |
---------------------------------------------------------------
|117312|    お手; 御手   |        おて      | hand; arm |n; int|

现在(在我的本地机器上工作)，如果没有 WHERE 语句，但有限制，它工作得很快 - 大约 0,140 秒。但是当文本过滤打开时，执行时间会增加到 6.5 秒，而且通常会更长。首先过滤 part_of_speech，大约 5.5 秒。 3 秒可以，但 6 秒太长了。

表 vocabulary_sense_gloss 中有 1 155 897 条记录，所以我认为这不是很多。

CREATE TABLE `vocabulary_sense_gloss` (
    `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    `sense_id` MEDIUMINT(8) UNSIGNED NOT NULL,
    `gloss` VARCHAR(255) NOT NULL,
    `language_id` MEDIUMINT(8) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `vocabulary_sense_gloss_vocabulary_sense_id` (`sense_id`),
    INDEX `vocabulary_sense_gloss_language_id` (`language_id`),
    FULLTEXT INDEX `vocabulary_sense_gloss_gloss` (`gloss`),
    CONSTRAINT `vocabulary_sense_gloss_language_id` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`),
    CONSTRAINT `vocabulary_sense_gloss_vocabulary_sense_id` FOREIGN KEY (`sense_id`) REFERENCES `vocabulary_sense` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

请问有什么办法可以优化吗？或者也许我应该更改我的数据库？我试图使用全文搜索，但它并没有快多少，而且似乎只适用于完整的术语，所以它没有用。使用“eat%”而不是“%eat%”的类似情况:它不会返回我想要的结果。

我试图将 vocabulary_sense_gloss 分成两个表 - 一个只有英文术语，另一个包含其他术语。由于用户通常会使用英语，所以它会使事情变得更快，但我不确定这是否是一个好方法。

此外，我试图将 VARCHAR 更改为 CHAR。它似乎加快了执行时间，尽管表大小增加了很多。

Answer 1

这个WHERE子句的性能极差。

(vsg.gloss like '%eat%' OR 
 vke.kanji_element like '%eat%' OR
 vre.reading_element like '%eat%')

为什么？首先:column LIKE '%constant%' 要求查询引擎检查column 的每个可能值。由于常量搜索词中的前导 %，它不可能使用索引。

其次:OR 子句意味着查询计划器必须扫描结果三次。

您打算如何改进它？这并不容易。您需要了解如何使用 column LIKE 'constant%' 搜索词从常量中消除前导 %。

一旦你这样做了，你就可以用这样的构造来击败你庞大的连接结果集的三重扫描

     ...
     WHERE v.id IN
           (SELECT sense_id AS id
              FROM vocabulary_sense_gloss 
             WHERE gloss LIKE 'eat%'
                                               UNION
            SELECT vocabulary_id AS id
              FROM vocabulary_kanji_element 
             WHERE kanji_element LIKE 'eat%'
                                               UNION
            SELECT vocabulary_id AS id
              FROM vocabulary_reading_element
             WHERE reading_element LIKE 'eat%'
           )

这将直接提取相关单词的 id 编号，而不是从多路 JOIN 的结果中提取。为了加快速度，您的 vocabulary_sense_gloss 需要在 (vocabulary_sense_gloss, sense_id) 上建立索引。其他两个表将需要类似的索引。