主页 / dba / 问题 / 30836
Accepted
artfulrobot
Asked: 2012-12-21 05:22:44 +0800 CST

使用大量自连接优化 MySQL 查询

  • 772

我有一个entity_relationship描述实体之间关系的表。每个关系将涉及 2 个以上的实体。

在某些情况下，可以说一个实体是选区的一个组成部分。这存储在entity_relationship表中。

然后我打算将实体添加到基于此的关系中：如果实体Joe Bloggs是选区University of Life的组成部分，而University of Life本身是选区Made Up Universities的组成部分，那么我会将 Joe Bloggs 添加到第二个关系中作为隐含成分。

当所有隐含成分关系都被填充时（通过运行查询直到没有记录被添加）我将有一个快速的方法来找出谁链接到什么而不需要在那个阶段进行递归。

entity_relationship 表如下所示：

+-----------------+---------------+
| Field           | Type          |
+-----------------+---------------+
| entity_id       | int(10)       |
| relationship_id | int(10)       |
| type            | enum(...)     |
+-----------------+---------------+

并且有钥匙（目前有很多钥匙，正在努力优化！）

+------------+----------+--------------+-----------------+-------------+------+
| Non_unique | Key_name | Seq_in_index | Column_name     | Cardinality | Null |
+------------+----------+--------------+-----------------+-------------+------+
|          0 | PRIMARY  |            1 | entity_id       |      179429 |      |
|          0 | PRIMARY  |            2 | relationship_id |      179429 |      |
|          1 | r_t_e    |            1 | relationship_id |      179429 |      |
|          1 | r_t_e    |            2 | type            |      179429 | YES  |
|          1 | r_t_e    |            3 | entity_id       |      179429 |      |
|          1 | t_r_e    |            1 | type            |           8 | YES  |
|          1 | t_r_e    |            2 | relationship_id |      179429 |      |
|          1 | t_r_e    |            3 | entity_id       |      179429 |      |
|          1 | t_e_r    |            1 | type            |           6 | YES  |
|          1 | t_e_r    |            2 | entity_id       |      179429 |      |
|          1 | t_e_r    |            3 | relationship_id |      179429 |      |
+------------+----------+--------------+-----------------+-------------+------+

然后我正在尝试的查询是：

INSERT INTO entity_relationship  
SELECT lt.entity_id entity_id, 
       py.relationship_id relationship_id,
       'implied-constituent' `type` 
FROM entity_relationship lt,
     entity_relationship ly,
     entity_relationship pt,
     entity_relationship py
WHERE lt.type='constituent'
  AND lt.relationship_id = ly.relationship_id
  AND ly.type='constituency'
  AND ly.entity_id = pt.entity_id 
  AND pt.type='constituent' 
  AND pt.relationship_id = py.relationship_id 
  AND py.type='constituency';

问题是这需要 42 秒才能运行（即使查询导致插入零行）。EXPLAIN 的输出（在 SELECT 上）显示：

+--------+-----+----+-------+---+------------------------+-----+------------------------+
| s._type|table|type|key    |len|ref                     |rows |Extra                   |
+--------+-----+----+-------+---+------------------------+-----+------------------------+
| SIMPLE |lt   |ref |t_r_e  |2  |const                   |89714|Using where; Using index|
| SIMPLE |ly   |ref |r_t_e  |6  |lt.relationship_id,const|    1|Using where; Using index|
| SIMPLE |pt   |ref |PRIMARY|4  |ly.entity_id            |    1|Using where             |
| SIMPLE |py   |ref |r_t_e  |6  |pt.relationship_id,const|    1|Using where; Using index|
+--------+-----+----+-------+---+------------------------+-----+------------------------+

看起来不错——除了第三行没有说使用索引。

任何人都可以看到优化这个的方法吗？

mysql optimization

1 个回答

  1. Best Answer

    如何在 MySQL 中修复它

    从 EXPLAIN 语句的第 3 行可以看出，这里没有使用[edit:]覆盖索引。它需要加入 entity_id 和类型，并且没有可用的键。

    ALTER TABLE entity_relationship ADD KEY e_t_r 
   (entity_id, type, relationship_id);

    这使得密钥可用，但 MySQL 选择不使用它。它可以强制使用USE INDEX (e_t_r)：：

    SELECT lt.entity_id entity_id, 
       py.relationship_id relationship_id,
       'implied-constituent' `type` 
FROM entity_relationship lt,
     entity_relationship ly,
     entity_relationship pt USE INDEX (e_t_r),
     entity_relationship py
WHERE lt.type='constituent'
  AND lt.relationship_id = ly.relationship_id
  AND ly.type='constituency'
  AND ly.entity_id = pt.entity_id 
  AND pt.type='constituent' 
  AND pt.relationship_id = py.relationship_id 
  AND py.type='constituency';

    现在运行时间为 0.8 秒。（与 17.7s 比较而不强制它使用该索引。）这是 MySQL 5.1.63。

    如何在 MariaDb 中修复它

    好吧，你不必！

    MariaDb 在有或没有USE INDEX干预的情况下都非常快速地执行查询（0.25 秒，但它在不同的主机上，所以我希望这接近上面优化版本中的 0.8 秒）。

    此外，MariaDb 不使用新索引并且对 t_e_r 索引非常满意，包括将其用作覆盖索引（即使用索引获取整个数据）。

    我对 MariaDb 的印象越来越深刻，现在正在考虑转换。

    • 0

相关问题