我遇到了一个严重的 MySQL 性能瓶颈，我无法理解和解决。以下是表结构、索引和记录数(请耐心等待，只有两个表):

mysql> desc elggobjects_entity;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| guid        | bigint(20) unsigned | NO   | PRI | NULL    |       |
| title       | text                | NO   | MUL | NULL    |       |
| description | text                | NO   |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show index from elggobjects_entity;
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| elggobjects_entity |          0 | PRIMARY  |            1 | guid        | A         |      613637 |     NULL | NULL   |      | BTREE      |         |
| elggobjects_entity |          1 | title    |            1 | title       | NULL      |         131 |     NULL | NULL   |      | FULLTEXT   |         |
| elggobjects_entity |          1 | title    |            2 | description | NULL      |         131 |     NULL | NULL   |      | FULLTEXT   |         |
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

mysql> select count(*) from elggobjects_entity;
+----------+
| count(*) |
+----------+
|   613637 |
+----------+
1 row in set (0.00 sec)

mysql> desc elggentity_relationships;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(11)             | NO   | PRI | NULL    | auto_increment |
| guid_one     | bigint(20) unsigned | NO   | MUL | NULL    |                |
| relationship | varchar(50)         | NO   | MUL | NULL    |                |
| guid_two     | bigint(20) unsigned | NO   | MUL | NULL    |                |
| time_created | int(11)             | NO   |     | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> show index from elggentity_relationships;
+--------------------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                    | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| elggentity_relationships |          0 | PRIMARY      |            1 | id           | A         |    11408236 |     NULL | NULL   |      | BTREE      |         |
| elggentity_relationships |          0 | guid_one     |            1 | guid_one     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| elggentity_relationships |          0 | guid_one     |            2 | relationship | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| elggentity_relationships |          0 | guid_one     |            3 | guid_two     | A         |    11408236 |     NULL | NULL   |      | BTREE      |         |
| elggentity_relationships |          1 | relationship |            1 | relationship | A         |    11408236 |     NULL | NULL   |      | BTREE      |         |
| elggentity_relationships |          1 | guid_two     |            1 | guid_two     | A         |    11408236 |     NULL | NULL   |      | BTREE      |         |
+--------------------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)

mysql> select count(*) from elggentity_relationships;
+----------+
| count(*) |
+----------+
| 11408236 |
+----------+
1 row in set (0.00 sec)

现在我想对这两个表使用 INNER JOIN 并执行全文搜索。

查询:

SELECT
        count(DISTINCT o.guid) as total
FROM
        elggobjects_entity o
INNER JOIN
        elggentity_relationships r on (r.relationship="image" AND r.guid_one = o.guid)
WHERE
        ((MATCH (o.title, o.description) AGAINST ('scelerisque' )))

这给了我 6 分钟(!)的响应时间。

另一方面这一个

SELECT
        count(DISTINCT o.guid) as total
FROM
        elggobjects_entity o
INNER JOIN
        elggentity_relationships r on (r.relationship="image" AND r.guid_one = o.guid)
WHERE
        ((o.title like "%scelerisque%") OR (o.description like "%scelerisque%"))

在 0.02 秒内返回相同的计数值。

这怎么可能？我在这里错过了什么？ (MySQL 信息:mysql Ver 14.14 Distrib 5.1.49，适用于使用 readline 6.1 的 debian-linux-gnu (x86_64))

编辑

解释第一个查询(使用 match .. against)给出:

+----+-------------+-------+----------+-----------------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type     | possible_keys         | key          | key_len | ref   | rows | Extra       |
+----+-------------+-------+----------+-----------------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | r     | ref      | guid_one,relationship | relationship | 152     | const | 6145 | Using where |
|  1 | SIMPLE      | o     | fulltext | PRIMARY,title         | title        | 0       |       |    1 | Using where |
+----+-------------+-------+----------+-----------------------+--------------+---------+-------+------+-------------+

而第二个查询(使用 LIKE "%..%"):

+----+-------------+-------+--------+-----------------------+--------------+---------+---------------------+------+-------------+
| id | select_type | table | type   | possible_keys         | key          | key_len | ref                 | rows | Extra       |
+----+-------------+-------+--------+-----------------------+--------------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | r     | ref    | guid_one,relationship | relationship | 152     | const               | 6145 | Using where |
|  1 | SIMPLE      | o     | eq_ref | PRIMARY               | PRIMARY      | 8       | elgg1710.r.guid_one |    1 | Using where |
+----+-------------+-------+--------+-----------------------+--------------+---------+---------------------+------+-------------+

最佳答案

结合您的经验和 EXPLAIN 的结果，在这种特殊情况下，全文索引似乎没有您预期的那么有用。这取决于数据库中的特定数据、数据库结构或/和特定查询。

通常数据库引擎对每个表只使用一个索引。所以当表有多个索引时，查询优化器会尝试使用更好的一个。但是优化器并不总是足够聪明。

EXPLAIN 的输出显示数据库查询优化器决定为relationship 和title 使用索引。关系过滤器将表 elggentity_relationships 减少到 6145 行。标题过滤器将表 elggobjects_entity 减少到 72697 行。然后 MySQL 需要在不使用任何索引的情况下连接这些表(6145 x 72697 = 446723065 次过滤操作)，因为索引已经用于过滤。在这种情况下，这可能太多了。 MySQL 甚至可以通过尝试在内存中保留足够的可用空间来决定将中间计算保留在硬盘中。

现在让我们看一下另一个查询。它使用 relationship 和 PRIMARY KEY(表 elggobjects_entity 的)作为其索引。关系过滤器将表 elggentity_relationships 减少到 6145 行。通过在 PRIMARY KEY 索引上连接这些表，结果仅获得 3957 行。这对于最后一个过滤器(即 LIKE "%scelerisque%")来说并不多，即使索引根本不用于此目的。

如您所见，速度在很大程度上取决于为查询选择的索引。因此，在这种特殊情况下，PRIMARY KEY 索引比全文 title 索引更有用，因为 PRIMARY KEY 对结果缩减的影响比标题。

MySQL 并不总是很聪明地设置正确的索引。我们可以手动执行此操作，方法是使用 IGNORE INDEX (index_name)、FORCE INDEX (index_name) 等子句

但在您的情况下，问题是如果我们在查询中使用 MATCH() AGAINST()，则需要全文索引，因为 MATCH() AGAINST() 没有全文索引根本无法工作。所以这就是MySQL为查询选择了错误索引的主要原因。

更新

好的，我做了一些调查。

首先，您可以尝试强制 MySQL 在表 elggentity_relationships 上使用 guid_one 索引而不是 relationship:USE INDEX (guid_one )。

但为了获得更好的性能，我认为您可以尝试为两列(guid_one、membership)的组合创建一个索引。当前索引 guid_one 非常相似，但用于 3 列，而不是 2 列。在此查询中，仅使用了 2 列。在我看来，创建索引后 MySQL 应该自动使用正确的索引。如果没有，强制 MySQL 使用它。

注意:创建索引后，不要忘记从查询中删除旧的 USE INDEX 指令，因为这可能会阻止查询使用新创建的索引。 :)

关于mysql - 'WHERE column LIKE "%expression %"' 如何在 MySQL 中比 MATCH(column) AGAINST ("expression") 表现得更好？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6580808/