MySQL如何在索引中使用字符集校对(collations)?

Question

MySQL如何在索引中使用字符集校对(collations)?

5

我想知道MySQL在生成索引时是否考虑排序规则(collation)，或者无论排序规则如何，索引都是相同的。只有在遍历索引时才会考虑排序规则。

为了我的目的，我想在一个字段上使用排序规则utf8_unicode_ci。我知道这个特定的排序规则有相对较高的性能惩罚，但对我来说使用它仍然很重要。

我在该字段上建立了一个索引，用于满足ORDER BY子句，快速按顺序检索行(避免文件排序)。但是，我不确定使用这个排序规则是否会影响从索引中读回行的速度，或者索引是否根据该排序规则以已经归一化的状态存储数据，从而使性能惩罚完全在生成索引而不是读取索引时发生。

- thomasrutter

你正在使用索引进行什么操作？按顺序排序吗？单键查找？范围查找？ - ʞɔıu

正在使用索引进行 ORDER BY 操作。谢谢。 - thomasrutter

2个回答

6

MySQL将使用列的排序规则来创建索引。因此，如果您创建了一个utf8_unicode_ci字段，那么索引也将按utf8_unicode_ci顺序生效。

请记住，使用索引并不总能完全避免性能影响，但对大多数实际目的而言，它足够好用。

许多数据库系统不会受到CPU限制，因此我怀疑您是否会注意到影响。

- Harrison Fisk

1

我认为，如果您想更改列排序规则，那么您也必须重新创建索引？ - Tuukka Mustonen

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ʞɔıu · Accepted Answer

我认为B树结构会有所不同，因为它需要以不同的方式比较列值。

看看这两个查询计划：

mysql> explain select * from sometable where keycol = '3';
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | pro   | ref  | PRIMARY       | PRIMARY | 66      | const |   34 | Using where; Using index | 
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+


mysql> explain select * from sometable where binary keycol = '3';
+----+-------------+-------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | pro   | index | NULL          | PRIMARY | 132     | NULL | 14417 | Using where; Using index | 
+----+-------------+-------+-------+---------------+---------+---------+------+-------+--------------------------+

如果我们更改比较的排序规则，突然间它就无法查找索引，必须扫描每一行。无论使用大小写敏感或不敏感的排序规则，索引中存储的实际值都将是相同的，例如，它仍将返回原始大小写的值。

因此，针对不区分大小写的排序规则进行查找可能会稍微低效一些。

但是，我怀疑您永远也察觉不到这种差异。请注意，MySQL默认情况下使所有内容不区分大小写，因此影响不会太可怕。

更新：

您可以看到类似的效果用于排序操作：

mysql> explain select * from sometable order by keycol collate latin1_general_cs;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra                       |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | pro   | index | NULL          | PRIMARY | 132     | NULL | 14417 | Using index; Using filesort | 
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+

mysql> explain select * from sometable order by keycol ;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | pro   | index | NULL          | PRIMARY | 132     | NULL | 14417 | Using index | 
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

注意额外的“filesort”阶段，需要执行查询。这意味着mysql正在将结果排队到临时缓冲区，并使用快速排序在额外的阶段中自行进行排序，抛弃索引顺序。使用原始排序规则，这一步是不必要的，因为mysql最初从索引中知道了顺序。