如何正确索引不使用以下内容的varchar列

Question

我一直在试验EXPLAIN，并注意到对于我们的查询，行计数很大，所以我尝试向VARCHAR列添加一个索引。首先，我添加了FULLTEXT，但结果只是使查询更长，所以我用EXPLAIN检查了它，它仍然显示了所有的行。但是，当我尝试在同一个表上执行EXPLAIN时，但是使用另一个索引列，即INT，行计数仅为1。

我也尝试过从FULLTEXT更改为一个普通的INDEX，但结果仍然是一样的。

即使对VARCHAR进行索引，VARCHAR也会导致高行计数吗？有什么办法来提高它的性能吗？

此外，该列并不是唯一的。

全文之前的查询时间：

'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,

全文后的查询时间：

'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,

用全文解释：

用索引解释：

使用索引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 

Answer 1

对于您的查询，我将添加以下索引：

ALTER TABLE pdf_packages ADD INDEX (private_reference_number, deleted_at);

这与WHERE子句中的两个条件相匹配，因此它应该在减少检查行数方面做得最好。索引可以帮助搜索VARCHAR列和INT列。

但是，如果您的查询搜索的值恰好发生在表中的大多数行上，优化器可能会推理使用索引不会有帮助，因此它只执行表扫描。

通过类比，这就是为什么在书的后面的索引中没有包含普通单词的原因。为什么要在索引中列出" the“这个词，然后列出每个页码的列表呢？只要把书的封面读一遍就更快了。

实际上，如果您搜索的值发生在大约20%或更多的行上，MySQL的优化器将跳过索引。这不是一个记录在案的阈值，只是我注意到的一些东西。

如果您认为优化器做了错误的选择，可以使用索引提示覆盖它。但在大多数情况下，它做出了正确的选择。

只有在使用MATCH() AGAINST()谓词时，才会使用全文索引。我在查询中没有看到这一点，所以不需要全文索引。

Answer 2

(重点是标题)

LIKE在测试VARCHARs时很有用。

x LIKE 'abc'   -- this turns in to  x = 'abc'
x LIKE 'abc%'  -- will _probably_ use INDEX(x)
x LIKE '%abc'  -- cannot use INDEX(x)
x = 123        -- cannot use INDEX(x) because not comparing to string

关于最后一个例子的一般规则是：“引导通配符防止使用索引。”

FULLTEXT只适用于MATCH(...) AGAINST(...)

(我同意比尔的回答，加上magicianiam的评论。)