主页 / dba / 问题 / 52156
Accepted
Mike Purcell
Asked: 2013-10-25 13:08:53 +0800 CST

MySQL - 向低基数文本列添加索引？

  • 772

我正在处理将枚举列转换为文本列的项目（我无法更改）。该列的基数很低（7 个唯一值）。我会通过添加 10-15 char 索引来提高性能，还是基数足够低，索引会导致收益递减？

mysql performance

2 个回答

  1. Best Answer

    列的大小或数据类型无关紧要。重要的是独特的价值观。如果您只有 7 个唯一值，则意味着必须考虑 14.286% 的行。

    与其给 MySQL Query Optimizer 解决这个问题的压力，不如通过散列对表进行分区：

    CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    ...
    ...
    myenum INT NOT NULL,
    PRIMARY KEY (id)
)
PARTITION BY HASH( myenum )
PARTITIONS 7;

    不需要在任何索引中都有 myenum。如果任何 SELECT 查询的WHERE子句包含AND myenum = ....

    如果您必须增加唯一值的数量，则必须增加分区的数量。

    试试看 ！！！

    更新 2013-10-24 17:57

    正如我在评论中所说，您应该按基数最高的枚举进行分区。

    其他枚举呢？不要自己索引枚举！

    如果您的 SELECT 查询包括 WHERE enum2...AND enum3=...`` AND enum4=...`，您应该考虑创建枚举的复合索引。

    例如，如果您有 enum2、enum3 和 enum4，您可以制作如下复合索引：

    ALTER TABLE mytable ADD INDEX (enum2,enum3,enum4);
ALTER TABLE mytable ADD INDEX (enum3,enum4);

    你应该选择哪个顺序？

    • 基数（枚举2）>基数（枚举3）
    • 基数(enum3) > 基数(enum4)

    CAVEAT：再次强调，如果您按 分区enum1，则无需在enum1.

    • 4

  2. 由于两个原因，我几乎不能不同意接受的答案。

    首先，所有关于优化器不使用低基数索引的讨论都被夸大了。确实，优化器可能不喜欢它，而且优化器有时可能会选择忽略它，但我看到帖子暗示如果超过“x”% 的行与索引匹配，则不会使用它。这绝对不是真的。

    我坐在一张超过一百万行的桌子前。它有一个索引枚举列，以及许多其他索引，但我在下面显示了该索引。注意基数是 2。

    mysql> show indexes in xxxxxxx;
+---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xxxxxxx |          1 | target_xxx          |            1 | target_xxx          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

    那么，优化器是否使用该索引？

    mysql> explain select * from xxxxxxx where target_xxx = 'default';
+----+-------------+---------+------+---------------+------------+---------+-------+---------+-----------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows    | Extra                 |
+----+-------------+---------+------+---------------+------------+---------+-------+---------+-----------------------+
|  1 | SIMPLE      | xxxxxxx | ref  | target_xxx    | target_xxx | 1       | const | 1269015 | Using index condition |
+----+-------------+---------+------+---------------+------------+---------+-------+---------+-----------------------+
1 row in set (0.23 sec)

    是的，它确实。它对匹配的行使用索引，并且它还使用索引几乎立即告诉我，如果我在 where 子句中使用了在该列的表中的任何位置都找不到的值，则没有行匹配。

    低基数索引没有用或被使用的神话......严重需要被丢弃。

    给优化器选择。这不是你想要避免的事情。

    其次，如果您按照讨论对表进行分区，那么在其 where 子句中未引用该列的每个查询现在都需要处理所有 7 个分区（和 7 组索引）。除非此列有一些真正重要和有意义的东西，这意味着您将在大多数 where 子句中询问它，否则对它进行分区似乎不是一个特别好的计划。

    分区不是灵丹妙药。

    然而，它是另一种子弹——除非使用得当，否则它往往会指向你的脚。

    • 4

相关问题