使用自聯接的表優化 MariaDB 查詢

我有一個查詢，我認為它花費的時間太長（因為我還沒有看到它完成！）但我不知道為什麼。

查詢（由難以更改但並非不可能更改的應用程序生成）是：

SELECT t1.id id1, t2.id id2, 5 weight
FROM ar1 t1 
INNER JOIN ar1 t2 ON (t2.contact_type = 'Individual' and t1.first_name = t2.first_name) 
WHERE t1.first_name IS NOT NULL
AND t1.first_name <> ''
AND t1.contact_type = 'Individual'
AND t1.id < t2.id GROUP BY id1, id2, weight

還有一個有 100k 行的表。

**$$ EDIT: added: $$**查詢的目的是提供可能重複的唯一記錄對的列表。可能是因為該表也有其他欄位，但此查詢僅查看名字欄位。

CREATE TABLE `ar1` (                                                                            
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,                       
 `contact_type` varchar(64) DEFAULT NULL,
 `first_name` varchar(64) DEFAULT NULL,       
 PRIMARY KEY (`id`),                                                                           
 KEY `k1` (`first_name`,`contact_type`),                                                       
 KEY `k2` (`contact_type`,`first_name`)                                                        
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci                              

表狀態：

Name            | ar1
Engine          | InnoDB
Version         | 10
Row_format      | Dynamic
Rows            | 102463
Avg_row_length  | 46
Data_length     | 4734976
Max_data_length | 0
Index_length    | 5275648
Data_free       | 0
Auto_increment  | <null>
Create_time     | 2020-04-21 09:22:1
Update_time     | <null>
Check_time      | <null>
Collation       | utf8mb4_unicode_ci
Checksum        | <null>
Create_options  |

解釋說：

***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | t1
type          | ref
possible_keys | PRIMARY,k1,k2
key           | k2
key_len       | 259
ref           | const
rows          | 51232
Extra         | Using where; Using index; Using temporary; Using filesort
***************************[ 2. row ]***************************
id            | 1
select_type   | SIMPLE
table         | t2
type          | ref
possible_keys | PRIMARY,k1,k2
key           | k1
key_len       | 518
ref           | od_civicrm.t1.first_name,const
rows          | 5
Extra         | Using where; Using index
2 rows in set

筆記：

• 表中只有 8647 個不同的名字。
• contact_type適用Individual於 99.9% 的行。
• 也不需要對常量 ( weight) 進行分組。但是忽略它也不能神奇地解決它。
• 我嘗試了各種索引組合。
• 我在 MariaDB 10.1
• 我嘗試過的伺服器並不龐大 - 但在我看來這不是一個巨大的記錄集？

結果ANALYZE可以下載

到底是什麼意圖？它似乎傳遞了ids相同的所有對個人first_name。

它看起來像一個“分組”最大值，但不完全是。

weight似乎無關緊要，因為它是常數“5”。

INDEX(contact_type, first_name, id)

可能會加快速度，即使 99.9% 的 contact_types 是所需的值。加速是由於索引被“覆蓋”。列的順序很重要。

它可能會返回一百萬行。這本身就需要時間。並可能扼殺客戶。

只是在尋找騙子？

也許你想要

SELECT GROUP_CONCAT(id), first_name
   FROM t
   WHERE first_name != ''
     AND contact_type = 'Individual'
   GROUP BY first_name
   HAVING COUNT(*) > 1;

或者，SELECT MIN(id), MAX(id), first_name ...，除了當有超過 2 個時它不會給你所有的 id。

最佳：

INDEX(contact_type, first_name, id)

好的，我已經將它減少到不到 2 秒（從 ~200 秒，所以提高了 100 倍）。

SELECT d1.id, c2.id, 5 weight
 FROM
 (
   SELECT MIN(id) id, first_name
   FROM civicrm_contact c1
   WHERE first_name <> '' AND contact_type = 'Individual'
   GROUP BY c1.first_name
   HAVING COUNT(*) > 1
 ) d1
 INNER JOIN civicrm_contact c2 ON c2.first_name = d1.first_name 
    AND c2.id > d1.id AND c2.contact_type = 'Individual'

• Notefirst_name <> ''可以代替，IS NOT NULL AND ...因為與上下文中的NULL結果進行比較，這將解析為布爾值 false。NULL``WHERE
• 一個關鍵的first_name, contact_type幫助，但只是非常微不足道。

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