MySQL如何使用排序规则和索引？

我想知道在生成索引时MySQL是否考虑排序规则，或者无论排序规则如何都生成相同的索引，仅在以后遍历该索引时才考虑排序规则。

出于我的目的，我想在字段上使用排序规则utf8_unicode_ci。我知道这种特定的归类具有相对较高的性能损失，但是使用它对我来说仍然很重要。

我在该字段上有一个索引，该索引用于满足ORDER BY子句，可快速按顺序检索行（避免使用文件排序）。但是，我不确定使用此排序规则是否会影响从索引读回的行的速度，或者索引是否根据该排序规则将数据存储在已归一化的状态下，从而导致性能下降完全是在生成索引而不是将其读回。

MySQL将使用列的排序规则作为索引。因此，如果您创建utf8_unicode_ci字段，则索引也将有效地按utf8_unicode_ci顺序排列。

请记住，使用索引并不总是100％绕过性能影响，但是对于大多数实际目的而言，它会。

许多数据库系统不受CPU限制，所以我怀疑您会注意到这种影响。

我相信btree结构将有所不同，因为它必须以不同的方式比较列值。

查看以下两个查询计划：

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 | 
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

请注意执行查询所需的额外“文件排序”阶段。这意味着mysql将在一个临时缓冲区中对结果进行排队，并在一个额外的阶段中使用quicksort对它本身进行排序，从而丢弃索引顺序。使用原始排序规则，此步骤是不必要的，因为mysql最初知道索引的顺序。