mysql 完全锁定在一个简单的查询上

Question

我有这个问题:

SELECT
    t1.artist_id AS artist_id,
    t2.name AS track_name
FROM
    media.recording t1,
    media.track_name t2
WHERE
    t1.name = t2.id
GROUP BY
    t1.artist_id,
    t2.name COLLATE utf8_general_ci

所有字符串字段都是 utf8_bin，但我需要不区分大小写的不同艺术家 ID 及其轨道名称，因为有很多重复项，例如:Metallica - 悲伤但真实，Metallica - 悲伤但真实，Metallica - 悲伤但真实我只需要一个版本。

表“recording”有 1100 万行，“track_name”有 550 万行。数据库是Mysql，表是MyISAM。

问题是，当我运行这个查询时，整个 mysql 都被锁定了。它会将此服务器上所有数据库中的所有表锁定大约 15 分钟。所有查询都等待所有数据库中所有表的表锁定。 myisam 不应该只锁定查询正在使用的表(在我的例子中是 recording 和 track_name)吗？

EXPLAIN EXTENDED 显示:

    +----+-------------+-------+--------+--------------------+---------+---------+---------------+----------+----------+---------------------------------+
| id | select_type | table | type   | possible_keys      | key     | key_len | ref           | rows     | filtered | Extra                           |
+----+-------------+-------+--------+--------------------+---------+---------+---------------+----------+----------+---------------------------------+
|  1 | SIMPLE      | t1    | ALL    | recording_idx_name | NULL    | NULL    | NULL          | 10805478 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY            | PRIMARY | 4       | media.t1.name |        1 |   100.00 |                                 |
+----+-------------+-------+--------+--------------------+---------+---------+---------------+----------+----------+---------------------------------+

我认为硬件不是问题。服务器有 4 个 CPU，8 个内核(32 个内核，64 个线程)和 64GB RAM。它在 RAID 10 中有 6 个 SSD 磁盘。

我的.cnf

max_connections          = 768
key_buffer               = 6G
table_cache              = 15360
read_buffer_size         = 2M
read_rnd_buffer_size     = 2M
sort_buffer_size         = 1M
tmp_table_size           = 128M
max_heap_table_size      = 128M
max_allowed_packet       = 16M
bulk_insert_buffer_size  = 16M
myisam_sort_buffer_size  = 128M
thread_cache_size        = 64
join_buffer_size         = 1M

请帮忙:)谢谢!

-帕克西尔

Answer 1

尝试distinct:

SELECT DISTINCT
    t1.artist_id AS artist_id,
    t2.name COLLATE utf8_general_ci AS track_name
FROM
    media.recording t1,
    media.track_name t2
WHERE
    t1.name = t2.id

MySQL 的弱点是group by...