如何在Mysql中创建MUL键的索引？

Question

我们需要在MUL - Key中已经存在的"source path" column上创建索引。例如，它有/src/com/Vendor/DTP/Emp/Grd1/Sal/2016/Jan/31-01/Joseph，我们需要像'%Sal/2016/Jan%'一样进行搜索，它有近1000万条记录。请提出任何改进性能的想法。

    | Field      | Type     | Null | Key | Default | Extra          |
    +------------+----------+------+-----+---------+----------------+
    | Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
    | Name       | char(35) | NO   |     |         |                |
    | Country    | char(3)  | NO   | UNI |         |                |
    | source Path| char(20) | YES  | MUL |         |                |
    | Population | int(11)  | NO   |     | 0       |          

Answer 1

不幸的是，以%开头的搜索不能使用索引(这与在复合索引中没有太多关系)。

不过，您也有一些选择：

• 路径中的值似乎有实际意义。理想的解决方案是使用元数据，例如月份、名称，无论"SAL“代表什么，并将其存储在自己的列或属性表中，然后查询该元数据。这显然只有在非常特殊的情况下才有可能，其中你有每个路径所需的元数据，所以在这里可能不是一个选项。
• 你可以添加一个包含源路径的所有子路径的“搜索表”(例如(id, subpath))，e.g.'/src/com/Vendor/DTP/Emp/Grd1/Sal/2016/Jan/31-01/Joseph‘'/com/Vendor/DTP/Emp/Grd1/Sal/2016/Jan/31-01/Joseph’'/Vendor/DTP/Emp/Grd1/Sal/2016/Jan/31-01/Joseph‘...’/Sal/2016/Jan/31-01/约瑟夫‘...’/31-01/约瑟夫‘’/约瑟夫‘’

因此，在您的示例中有11行。现在可以对其使用索引，例如在

..。where exists (select * from子路径s where s.subpath like '/Sal/2016/Jan%‘s.subpath s.id = outerquery.id)

这依赖于知道你的搜索词的开始。如果您的示例%Sal/2016/Jan中的Sal实际上应该包括单词结尾，例如/NoSal/2016/Jan，那么您将不得不修改您的输入术语来删除第一个单词，因此%Sal/2016/Jan%将要求您搜索/2016/Jan% (带有索引)，然后重新检查结果集，如果它也符合%Sal/2016/Jan% (参见全文选项的示例，它有相同的“问题”，只查找单词的开头)。

您必须维护搜索表，这通常是在触发器中完成的(当您在原始表中插入、更新或删除值时更新子路径表)。

由于这是一个新表，您不能(直接)将其与另一个索引组合在一起，例如，如果country = 'A'已经删除了99.99%的行，则无法优化where country = 'A' and subpath like 'Sal/2016/Jan%'。如果force index).

• You实际上使用了索引(因为优化器可以尝试不同的方法)，那么您可能必须检查查询的explain，然后可能重新组织您的查询(例如，使用join或for可以使用fulltext search。在userinput中，您必须生成一个查询，如下所示

select * from (select * from table where match(source Path) about ('+SAL +2016 +Jan‘在布尔模式下))子查询where source path like’%Sal/2016/JAN%‘全文搜索将不关心单词的顺序，因此如果结果集确实是正确的路径，则必须重新检查结果集，但全文搜索将使用(全文)索引来加速。它将只查找单词的开头，因此类似于“搜索表”选项，如果Sal可以是单词的结尾，则必须将其从全文搜索中删除。默认情况下，只有至少包含3或4个字母的单词(取决于您的引擎)才会被添加到索引中，因此您必须将ft_min_word_len或innodb_ft_min_token_size的值设置为适合您的要求。

搜索表方法可能是最方便的解决方案，因为它的使用与您当前的搜索非常相似:您可以直接在一个地方添加用户输入(不必解释它来创建against (...)表达式)，也可以在其他情况下轻松使用它(例如，在join table2 on concat(table2.Year,'/',table2.Month,'%') like ...之类的情况下)；但是您必须设置触发器(或者以其他方式维护表)，这比仅仅添加全文索引要复杂一些。