代码之家 › 专栏 › 技术社区 › thomasrutter

MySQL如何将排序规则与索引结合使用?

indexing mysql

thomasrutter · 技术社区 · 17 年前

我想知道MySQL在生成索引时是否考虑了排序规则,或者无论排序规则如何,索引的生成都是一样的,只有在以后遍历该索引时才会考虑排序规则。

出于我的目的,我想在字段上使用排序规则utf8_unicode_ci。我知道这种特殊的排序规则会带来相对较高的性能损失,但使用它对我来说仍然很重要。

2 回复 | 直到 17 年前

ÊÉÄ±u 17 年前

我相信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 | 
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

Harrison Fisk 17 年前

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