MySQL 对 varchar 字段的部分索引和优化分组

Question

我在使用 MySQL 进行组查询时遇到了一些问题。

问题

为什么查询不会在 varchar(255) 字段上使用 10 个字符的部分索引来优化分组依据，这是有原因的吗？

详情

我的设置:

CREATE TABLE `sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `ref_source` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `guid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `initial_path` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `referrer_host` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `campaign` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_sessions_on_user_id` (`user_id`),
  KEY `index_sessions_on_referrer_host` (`referrer_host`(10)),
  KEY `index_sessions_on_initial_path` (`initial_path`(10)),
  KEY `index_sessions_on_campaign` (`campaign`(10))
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

此处未显示许多列和索引，因为它们不会真正影响问题。

我想做的是运行查询以查看所有引用主机以及来自每个主机的 session 数。我没有一张大 table ，但它足够大，我对全表扫描并不感兴趣。我要运行的查询是:

SELECT COUNT(*) AS count_all, referrer_host AS referrer_host FROM `sessions` GROUP BY referrer_host;

解释给出:

+----+-------------+----------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+----------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | sessions | ALL  | NULL          | NULL | NULL    | NULL | 303049 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+--------+---------------------------------+

我在 referrer_host 上有一个部分索引，但它没有使用它。即使我尝试使用 USE INDEX 或 FORCE INDEX 它也无济于事。解释是一样的，性能也是一样。

如果我在 referrer_host 上添加完整索引，而不是 10 个字符的部分索引，即使不是立即，一切也会更好。 (350 毫秒对 10 秒)

我已经测试了大于该字段中最长条目的部分索引也无济于事。完整索引是唯一似乎有效的方法。

Answer 1

对于全索引，查询将查找扫描整个索引并返回每个唯一键指向的记录数。 table 没有动。

使用部分索引，引擎在查看记录之前不知道 referrer_host 的值。它必须扫描整个表格!

如果 referrer_host 的大部分值都小于 10 个字符，那么理论上，优化器可以使用索引，然后只检查超过 10 个字符的行。但是，因为这不是聚集索引，所以必须进行多次非顺序磁盘读取才能找到这些记录。它最终可能会更慢，因为表扫描至少是顺序读取。优化器不进行假设，而只是进行扫描。