MySQL 集群上的三表 JOIN 查詢變得異常緩慢

我有一個由腳本呼叫到我們的 MySQL 集群的選擇查詢，該查詢從一兩秒內執行到每個查詢花費一分鐘以上（並且它必須執行這個查詢 24000 次）。自上次執行以來的幾個月裡，程式碼方面沒有任何變化。我不是一個合適的 DBA，只是一個長期的 MySQL 使用者，我在另一個執行緒上讀到它可能是索引超出 key_cache 大小限制的問題？但這適用於 ndbcluster 表類型嗎？如果是這樣，我該怎麼說？

問題查詢的解釋輸出是：

EXPLAIN SELECT userid from users left outer join routes on routes.user_id = users.id left outer join aliases on aliases.route_id = routes.id where aliases.name='xxxx' and domain_id=8;

+----+-------------+---------+------+-----------------+----------+---------+------------------+--------+-----------------------------------+ 
| id | select_type | table   | type | possible_keys   | key      | key_len | ref              | rows   | Extra                            |
+----+-------------+---------+------+-----------------+----------+---------+------------------+--------+-----------------------------------+ 
|  1 | SIMPLE      | users   | ALL  | PRIMARY         | NULL     | NULL    | NULL             | 177802 |                                  | 
|  1 | SIMPLE      | routes  | ref  | PRIMARY,user_id | user_id  | 4       | vnames.users.id  |      1 | Using where                       | 
|  1 | SIMPLE      | aliases | ref  | route_id        | route_id | 4       | vnames.routes.id |      1 | Using where with pushed condition |
+----+-------------+---------+------+-----------------+----------+---------+------------------+--------+-----------------------------------+

三個表的 Show Indexes 的輸出是：

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| aliases |          0 | PRIMARY  |            1 | id          | A         |      377197 |     NULL | NULL   |      | BTREE      |         |
| aliases |          1 | route_id |            1 | route_id    | A         |      377197 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| routes |          0 | PRIMARY  |            1 | id          | A         |      208442 |     NULL | NULL   |      | BTREE      |         |
| routes |          1 | user_id  |            1 | user_id     | A         |      208442 |     NULL | NULL   |      | BTREE      |         |
| routes |          1 | address  |            1 | address     | A         |      208442 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          0 | PRIMARY  |            1 | id          | A         |      177803 |     NULL | NULL   |      | BTREE      |         |
| users |          1 | userid   |            1 | userid      | A         |      177803 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

任何幫助/建議將不勝感激。

我懷疑這個查詢由於表中的捲而死了。執行返回所有使用者標識 24000 次的查詢最終會導致性能問題。

在某些時候，您的表會增長到無法放入記憶體的程度（訪問速度非常快），最終您會從磁碟讀取數據（對於大多數查詢來說查詢速度非常慢。這是一個突然的切換。我寫的一個查詢來自發生這種情況時不到 5 分鐘。如果您可以壓縮表，這可能會解決一段時間的情況。添加更多記憶體可能會有所幫助。（即使您的數據庫沒有足夠的緩衝區空間，O/S 也可能為您緩衝數據。）

這就是我格式化查詢以理解它的方式。

SELECT userid 
FROM   users 
LEFT OUTER JOIN routes 
ON     routes.user_id = users.id 
LEFT OUTER JOIN aliases 
ON     aliases.route_id = routes.id 
WHERE  aliases.name = 'xxxx' 
AND    domain_id = 8;

如果您正在搜尋與 aliases.name 匹配的別名，那麼為該列建立索引會有所幫助。

基於 LEFT OUTER JOINS，您應該獲取所有使用者或 domain_id = 8 的所有使用者的使用者 ID。當我閱讀此查詢時，它的編寫方式很長：

SELECT userid 
FROM   users;

要麼

SELECT userid 
from   users
WHERE  domain_id = 8;

但是，我希望您在別名和域匹配時需要使用者 ID。

SELECT userid 
FROM   users 
JOIN   routes 
ON     routes.user_id = users.id 
JOIN   aliases 
ON     aliases.route_id = routes.id 
WHERE  aliases.name = 'xxxx' 
AND    domain_id = 8;

如果您正在尋找與 aliases.name 值匹配的使用者 ID，那麼索引 aliases.name 會有所幫助。

引用自：https://dba.stackexchange.com/questions/143284