mysql - 将表连接到自身并仅返回匹配值的优化方式(也删除此行)

Question

我有这个 map 表:

CREATE TABLE `items_map` (
    `id` INT(10) UNSIGNED NOT NULL,
    `context` VARCHAR(50) NULL DEFAULT NULL,
    `md5_hash` CHAR(32) NOT NULL,
    INDEX `idx_map` (`context`, `id`),
    INDEX `idx_hash` (`md5_hash`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO `items_map` (`id`, `context`, `md5_hash`) VALUES
    (1, 'coupons', 'f46902f68e1f0f56bd97d2c209874c93'),
    (3, 'countries', 'f46902f68e1f0f56bd97d2c209874c93'),
    (1, 'coupons', 'ed63e3a460c4fdef7223dd796dbe6848'),
    (15, 'regions', 'ed63e3a460c4fdef7223dd796dbe6848'),
    (1, 'coupons', '06786a4bc64c718118c1510d06e334a8'),
    (16, 'regions', '06786a4bc64c718118c1510d06e334a8'),
    (1, 'coupons', '2e7536e21183723b99b77c48503e3122'),
    (17, 'regions', '2e7536e21183723b99b77c48503e3122'),
    (2, 'coupons', 'f4df7e36910612dfb39463c874fc27c4'),
    (46, 'countries', 'f4df7e36910612dfb39463c874fc27c4'),
    (2, 'coupons', 'd40faa1e775d0df3864456f10f2995a7'),
    (579, 'regions', 'd40faa1e775d0df3864456f10f2995a7'),
    (2, 'coupons', '3cc0b00ea1c092a8dcd95d225519e8a4'),
    (580, 'regions', '3cc0b00ea1c092a8dcd95d225519e8a4');

我只想返回基于所选优惠券 ID 的地区和国家/地区。

我可以使用的经过测试的选择查询:

EXPLAIN EXTENDED
SELECT m.context AS code, m.id AS value
FROM items_map AS m
INNER JOIN items_map AS m2 ON m2.context = 'coupons' AND m2.id = 1 AND m.md5_hash = m2.md5_hash
WHERE m.context != 'coupons';

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "filtered"  "Extra"
"1" "SIMPLE"    "m2"    "ref"   "idx_map,idx_hash"  "idx_map"   "157"   "const,const"   "7" "100,00"    "Using where"
"1" "SIMPLE"    "m" "ref"   "idx_map,idx_hash"  "idx_hash"  "96"    "test.m2.md5_hash"  "1" "100,00"    "Using where"

<小时/>

EXPLAIN EXTENDED
SELECT m2.context AS code, m2.id AS value
FROM items_map AS m
INNER JOIN items_map AS m2 ON m.md5_hash = m2.md5_hash AND m2.context != 'coupons'
WHERE m.context = 'coupons' AND m.id = 1;

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "filtered"  "Extra"
"1" "SIMPLE"    "m" "ref"   "idx_map,idx_hash"  "idx_map"   "157"   "const,const"   "7" "100,00"    "Using where"
"1" "SIMPLE"    "m2"    "ref"   "idx_map,idx_hash"  "idx_hash"  "96"    "test.m.md5_hash"   "1" "100,00"    "Using where"

两个查询都返回正确的值:

"code"  "value"
"countries" "3"
"regions"   "15"
"regions"   "16"
"regions"   "17"

删除我正在使用的查询:

DELETE 
FROM items_map
WHERE md5_hash IN (SELECT m3.md5_hash FROM (
SELECT m2.md5_hash
FROM items_map AS m
INNER JOIN items_map AS m2 ON m2.context = 'coupons' AND m2.id = 1 AND m.md5_hash = m2.md5_hash
WHERE m.context != 'coupons') AS m3);

问题/请求:

1- 有更好的方法来运行此查询吗？

2- 请显示一个基于“id”和“上下文”删除此 map 记录的查询。例如;如果我想删除所有 id = 1 且 context = coupons 的映射记录，并删除包含相同 md5_hash 值的映射行。

重要:请给出可在多个数据库引擎中使用的选项。

Answer 1

这两个查询的执行方式类似。你最大的优化是不是选择，而是ratger使用mysql的多表删除:

DELETE m2
FROM items_map m
JOIN items_map m2 ON m.md5_hash = m2.md5_hash
WHERE m.context = 'coupons'
AND m.id = 1

请注意，您应该从连接表 m2 中删除，因为连接表将包含匹配的 md5 行和 m 中的行，这正是您想要的。

<小时/>

对于适用于任何数据库的查询:

DELETE FROM items_map
WHERE id IN (
    SELECT m2.id
    FROM items_map m
    JOIN items_map m2 ON m.md5_hash = m2.md5_hash
    WHERE m.context = 'coupons'
    AND m.id = 1)