mysql - 为什么使用 CHAR/VARCHAR 索引时 MySQL 查询的性能如此糟糕？

Question

首先，我将描述问题域的简化版本。

有表strings:

CREATE TABLE strings (
  value CHAR(3) COLLATE utf8_unicode_ci NOT NULL,
  INDEX(value)
) ENGINE=InnoDB;

如您所见，它有一个 CHAR(3) 列的非唯一索引。

该表是使用以下脚本填充的:

CREATE TABLE a_variants (
  letter CHAR(1) COLLATE utf8_unicode_ci  NOT NULL
) ENGINE=MEMORY;

INSERT INTO a_variants VALUES -- 60 variants of letter 'A'
  ('A'),('a'),('À'),('Á'),('Â'),('Ã'),('Ä'),('Å'),('à'),('á'),('â'),('ã'),
  ('ä'),('å'),('Ā'),('ā'),('Ă'),('ă'),('Ą'),('ą'),('Ǎ'),('ǎ'),('Ǟ'),('ǟ'),
  ('Ǡ'),('ǡ'),('Ǻ'),('ǻ'),('Ȁ'),('ȁ'),('Ȃ'),('ȃ'),('Ȧ'),('ȧ'),('Ḁ'),('ḁ'),
  ('Ạ'),('ạ'),('Ả'),('ả'),('Ấ'),('ấ'),('Ầ'),('ầ'),('Ẩ'),('ẩ'),('Ẫ'),('ẫ'),
  ('Ậ'),('ậ'),('Ắ'),('ắ'),('Ằ'),('ằ'),('Ẳ'),('ẳ'),('Ẵ'),('ẵ'),('Ặ'),('ặ');

INSERT INTO strings
  SELECT CONCAT(a.letter, b.letter, c.letter) -- 60^3 variants of string 'AAA'
    FROM a_variants a, a_variants b, a_variants c
  UNION ALL SELECT 'BBB'; -- one variant of string 'BBB'

因此，它包含 216000 个无法区分的(根据 utf8_unicode_ci 排序规则)字符串“AAA”的变体和字符串“BBB”的一个变体:

SELECT value, COUNT(*) FROM strings GROUP BY value;

+-------+----------+
| value | COUNT(*) |
+-------+----------+
| AAA   |   216000 |
| BBB   |        1 |
+-------+----------+

由于 value 被索引，我希望以下两个查询具有相似的性能:

SELECT SQL_NO_CACHE COUNT(*) FROM strings WHERE value = 'AAA';
SELECT SQL_NO_CACHE COUNT(*) FROM strings WHERE value = 'BBB';

但实际上第一个比第二个慢 300 倍!见:

+----------+------------+---------------------------------------------------------------+
| Query_ID | Duration   | Query                                                         |
+----------+------------+---------------------------------------------------------------+
|        1 | 0.11749275 | SELECT SQL_NO_CACHE COUNT(*) FROM strings WHERE value = 'AAA' |
|        2 | 0.00033325 | SELECT SQL_NO_CACHE COUNT(*) FROM strings WHERE value = 'BBB' |
|        3 | 0.11718050 | SELECT SQL_NO_CACHE COUNT(*) FROM strings WHERE value = 'AAA' |
+----------+------------+---------------------------------------------------------------+

-- 为了确定，我在这里运行了两次“AAA”查询。

如果我更改索引列的大小或将其类型更改为 VARCHAR，性能问题仍然会出现。同时，在类似的情况下，但当非唯一索引不是 CHAR/VARCHAR(例如 INT)时，查询速度与预期一样快.

那么，问题是为什么在使用 CHAR/VARCHAR 索引时 MySQL 查询的性能如此糟糕？

我有强烈的感觉，MySQL 对索引键匹配的所有值进行全线性扫描。但是，当它只能返回匹配行的计数时，为什么要这样做呢？我是否遗漏了什么，而那是真正需要的？或者这是 MySQL 优化器的一个可悲的缺点？

Answer 1

很明显，问题在于查询正在进行索引扫描。另一种方法是进行两次索引查找，查找相同的第一个和最后一个值，然后使用索引中的元信息进行计算。根据您的观察，MySQL 可以做到这两点。

这个答案的其余部分是猜测。

性能“仅”慢 300 倍，而不是慢 200,000 倍的原因是读取索引的开销。实际上，与所需的其他操作相比，扫描条目的速度相当快。

在比较方面，数字和字符串之间存在根本区别。引擎可以只查看两个数字的位表示并识别它们是相同还是不同。不幸的是，对于字符串，您需要考虑编码/排序规则。我认为这就是它需要查看值的原因。

如果您有 216,000 个 完全 相同字符串的副本，那么 MySQL 将能够使用索引中的元数据进行计数。换句话说，索引器足够聪明，可以使用元数据进行精确相等比较。但是，它不够聪明，无法考虑编码。