如何正確索引不會使用 LIKE 的 varchar 列

我一直在嘗試EXPLAIN並註意到我們的查詢的行數很大，所以我嘗試向 VARCHAR 列添加索引。首先我添加了FULLTEXT，但它最終只是使查詢更長，所以我檢查了它，EXPLAIN它仍然顯示所有行。但是當我嘗試EXPLAIN在同一張表上執行但使用另一個索引列是 INT 時，行數僅為 1。

我也嘗試從正常更改FULLTEXT為正常INDEX，但仍然顯示相同的結果。

即使您對它進行索引，VARCHAR 是否總是會導致高行數？有什麼方法可以提高它的性能嗎？

此外，該列不是唯一的。

FULLTEXT 之前的查詢時間：

'query' => 'select `id` from `pdf_packages` where `private_reference_number` = ? and `pdf_packages`.`deleted_at` is null limit 1',
   'bindings' => 
   array (
     0 => '558376067',
   ),
   'time' => 64.42,

FULLTEXT 後的查詢時間：

'query' => 'select `id` from `pdf_packages` where `private_reference_number` = ? and `pdf_packages`.`deleted_at` is null limit 1',
   'bindings' => 
   array (
     0 => '292314725',
   ),
   'time' => 129.89,

用全文解釋：

用索引解釋：

用 INDEX INT 列解釋：

我已經刪除了“private_reference_number”列的索引，所以你不會在下面看到它。

顯示創建表：

CREATE TABLE `pdf_packages` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `organiser_id` int(11) NOT NULL,
 `pro_event_id` int(11) NOT NULL,
 `pro_order_id` int(11) NOT NULL,
 `pro_order_item_id` int(11) NOT NULL,
 `package_id` int(11) NOT NULL,
 `package_item_item_id` int(11) NOT NULL,
 `private_reference_number` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `reference_index` int(11) NOT NULL,
 `pro_attendee_id` int(11) DEFAULT NULL,
 `member_card_id` int(11) DEFAULT NULL,
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 `deleted_at` timestamp NULL DEFAULT NULL,
 `is_printed` int(11) NOT NULL DEFAULT '0',
 `has_arrived` tinyint(4) NOT NULL DEFAULT '0',
 `arrival_time` datetime DEFAULT NULL,
 `seat_details` longtext COLLATE utf8_unicode_ci,
 `svg_seat_details` longtext COLLATE utf8_unicode_ci,
 `is_shared` tinyint(1) NOT NULL DEFAULT '0',
 `share_from_id` int(11) DEFAULT NULL,
 `owner_id` int(11) DEFAULT NULL,
 `gates` text COLLATE utf8_unicode_ci,
 `pro_order_edit_history_id` int(11) DEFAULT NULL,
 `hard_ticket_sent` tinyint(4) NOT NULL DEFAULT '0',
 `hard_ticket_sent_on` datetime DEFAULT NULL,
 `sent_by` int(11) DEFAULT NULL,
 `pdf_sent` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `from_access` tinyint(1) NOT NULL DEFAULT '0',
 `pro_promo_code_id` int(11) DEFAULT NULL,
 `printed_at` datetime DEFAULT NULL,
 `bundle_index` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `pdf_packages_organiser_id_pro_event_id_pro_order_id_index` (`organiser_id`,`pro_event_id`,`pro_order_id`),
 KEY `pdf_packages_pro_order_item_id_package_id_index` (`pro_order_item_id`,`package_id`),
 KEY `pdf_packages_package_item_item_id_pro_attendee_id_index` (`package_item_item_id`,`pro_attendee_id`),
 KEY `pdf_packages_member_card_id_share_from_id_index` (`member_card_id`,`share_from_id`),
 KEY `pdf_packages_pro_order_edit_history_id_index` (`pro_order_edit_history_id`),
 KEY `pdf_packages_pro_attendee_id_index` (`pro_attendee_id`),
 KEY `pdf_packages_pro_order_item_id_index` (`pro_order_item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=184980 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

鑑於您的查詢，我將添加此索引：

ALTER TABLE pdf_packages ADD INDEX (private_reference_number, deleted_at);

這與您在 WHERE 子句中的兩個條件相匹配，因此它應該在減少檢查的行數方面做得最好。索引可以幫助搜尋 VARCHAR 列和 INT 列。

但是，如果您的查詢搜尋的值恰好出現在表中的大多數行上，優化器可能會認為使用索引沒有幫助，所以它只是進行表掃描。

以此類推，這就是為什麼書後的索引中不包含常用詞的原因。為什麼要在索引中列出單詞“the”，然後是每個頁碼的列表？只看書的封面會更快。

實際上，如果您搜尋的值出現在大約 20% 或更多的行上，MySQL 的優化器會跳過索引。這不是記錄的門檻值，只是我注意到的。

如果您認為優化器做出了錯誤的選擇，您可以使用索引提示來覆蓋它。但大多數時候，它會做出正確的選擇。

MATCH() AGAINST()僅當您使用謂詞時才會使用 FULLTEXT 索引。我在您的查詢中沒有看到這一點，因此您不需要 FULLTEXT 索引。

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