不同 mysql 版本、percona、MariaDB 上的慢查詢響應

LEFT JOIN 應該比 NOT IN 查詢更快。這是我在網上讀到的。但實際上，這似乎是相反的。我在這裡有以下查詢，

INSERT IGNORE INTO callrequests (contact, call_status, campaign_id, created_date, active_call) 
SELECT contact, '1', '7', '2013-10-20 13:40:51', '0' FROM contacts c WHERE phonebook_id = '62' and c.`contact` NOT IN (SELECT contact FROM dnc_contacts d WHERE group_id = '3') ON DUPLICATE KEY UPDATE call_status = if( call_status = '2', VALUES(call_status), call_status);

執行大約需要 7 秒。但是，如果我使用相同的查詢刪除 NOT IN，插入 LEFT JOIN，

INSERT IGNORE callrequests (contact, call_status, campaign_id, created_date, active_call) SELECT c.contact, '1', '7', '2013-10-20 13:40:51', '0' FROM contacts c 
LEFT JOIN `dnc_contacts` as B on c.`contact` <> B.`contact` WHERE c.phonebook_id = '62' and B.`group_id` = '3' ON DUPLICATE KEY UPDATE call_status = if( call_status = '2', VALUES(call_status), call_status);

顯示創建表呼叫請求

CREATE TABLE `callrequests` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `contact` varchar(45) NOT NULL,
 `call_status` int(11) NOT NULL,
 `campaign_id` int(11) NOT NULL,
 `created_date` timestamp NULL DEFAULT NULL,
 `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `active_call` tinyint(1) DEFAULT NULL,
 `uuid` varchar(60) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`,`campaign_id`),
 UNIQUE KEY `unique_contact` (`campaign_id`,`contact`),
 KEY `fk_callrequest_campaign1_idx` (`campaign_id`),
 KEY `active_calls` (`active_call`),
 KEY `idx_call_status` (`call_status`),
 CONSTRAINT `fk_callrequest_campaign1` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16384 DEFAULT CHARSET=latin1;

顯示創建表聯繫人

CREATE TABLE `contacts` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `contact` varchar(45) NOT NULL,
 `phonebook_id` int(11) NOT NULL,
 `created_date` timestamp NULL DEFAULT NULL,
 `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '                     ',
 `status` tinyint(1) NOT NULL DEFAULT '1',
 `admin` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`,`phonebook_id`) USING BTREE,
 KEY `phonebook_id_contact_INDEX` (`contact`,`status`),
 KEY `contact_INDEX` (`contact`),
 KEY `fk_contact_phonebook1_idx` (`phonebook_id`),
 CONSTRAINT `fk_contact_phonebook1` FOREIGN KEY (`phonebook_id`) REFERENCES `phonebooks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2484174 DEFAULT CHARSET=latin1;

顯示創建表 dnc_contacts

CREATE TABLE `dnc_contacts` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `group_id` int(11) NOT NULL,
 `contact` varchar(45) NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1',
 `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 `admin` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`,`group_id`) USING BTREE,
 KEY `group_id_satus_contact_INDEX` (`status`),
 KEY `fk_dnc_contact_dnc_group1_idx` (`group_id`),
 CONSTRAINT `fk_dnc_contact_dnc_group1` FOREIGN KEY (`group_id`) REFERENCES `dnc_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32573 DEFAULT CHARSET=latin1;

這需要很長時間才能完成。現在有趣的部分是第一個查詢在伺服器版本：5.6.13-rel61.0 Percona 上執行非常快（7 秒），如果我在伺服器版本上執行相同的第一個查詢：5.5.5-10.0.4-MariaDB 需要很長一段時間。所有伺服器配置都相同。伺服器執行 32 GB RAM，配備 AMD Opteron 8 核心和 RAID 1 SSD。MySQL 配置文件在所有實例上都相同

port = 1979
socket = /opt/percona/tmp/mysql.sock
skip-external-locking
default-storage-engine = InnoDB
tmpdir = /tmp/
innodb_data_home_dir = /opt/percona/data
innodb_log_group_home_dir = /opt/percona/data
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP
max-allowed-packet             = 16M
max-connect-errors             = 1000000
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 400
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 1024
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 1G
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 15G
innodb_thread_concurrency      = 0
interactive_timeout = 60
wait_timeout = 60
connect_timeout = 5
innodb_lock_wait_timeout = 15

似乎無法弄清楚發生了什麼。試圖調整一些參數，但沒有運氣。有沒有辦法可以改進查詢或查看伺服器配置？

謝謝。

dnc_contacts 將受益於 INDEX(group_id, contact) 和/或 INDEX(contact, group_id)

這兩個查詢在邏輯上不等價。

FROM contacts c LEFT JOIN `dnc_contacts` as B on c.`contact` <> B.`contact` 

這並不像你認為的那樣。此表達式將 c 中的每一行連接到 b 中的每一行，除非 contact 相等。每個表中的 1000 行意味著大約 999000 行正在生成和比較。

FROM contacts c LEFT JOIN `dnc_contacts` as B on c.`contact` = B.`contact` and B.group_id = 3
WHERE b.contact is null AND c.phonebook_id = '62'

“查找 C 中與 B 中的行匹配的聯繫人值匹配的所有行，如果存在 B.group_id 3，但如果您在匹配行中實際找到 b.contact 的值，則從結果。”

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