sql-server - Django 模型选择 : IntegerField vs CharField

Question

TL;DR:我有一个包含数百万个实例的表，我想知道应该如何为其建立索引。

我有一个使用 SQL Server 作为数据库后端的 Django 项目。

在生产环境中拥有大约 1400 万个实例的模型后，我意识到我遇到了性能问题:

class UserEvent(models.Model)

    A_EVENT = 'A'
    B_EVENT = 'B'

    types = (
        (A_EVENT, 'Event A'),
        (B_EVENT, 'Event B')
    )

    event_type = models.CharField(max_length=1, choices=types)

    contract = models.ForeignKey(Contract)

    # field_x = (...)
    # field_y = (...)

我使用了很多基于该字段的查询，并且效率非常低，因为该字段没有索引。仅使用此字段过滤模型大约需要 7 秒，而通过索引外键查询不会带来性能问题:

UserEvent.objects.filter(event_type=UserEvent.B_EVENT).count()
# elapsed time: 0:00:06.921287

UserEvent.objects.filter(contract_id=62).count()
# elapsed time: 0:00:00.344261

当我意识到这一点时，我也向自己提出了一个问题:“这个字段不应该是 SmallIntegerField 吗？因为我只有一小部分选择，基于整数字段的查询比基于 text/varchar 的查询更有效查询。”

所以，据我了解，我有两个选择*:

*I realize that a third option may exist, since indexing fields with low cardinality may not cause severe improvements, but since my values have a [1%-99%] distribution (and I'm looking for the 1% part), indexing this field seems to be a valid option.

• A) 只需为此字段建立索引，并将其保留为 CharField。

  A_EVENT = 'A'
  B_EVENT = 'B'
  
  types = (
      (A_EVENT, 'Event A'),
      (B_EVENT, 'Event B')
  )
  
  event_type = models.CharField(max_length=1, choices=types, db_index=True)
  

• B) 执行迁移以将此字段转换为 SmallIntegerField(我不希望它成为 BooleanField，因为可能可以向该字段添加更多选项) ，然后对该字段建立索引。

  A_EVENT = 1
  B_EVENT = 2
  
  types = (
      (A_EVENT, 'Event A'),
      (B_EVENT, 'Event B')
  )
  
  event_type = models.SmallIntegerField(choices=types, db_index=True)
  

选项A

优点:简单

缺点: CharField基于索引的效率低于基于整数的索引

选项B

优点:基于整数的索引比 CharField 更有效。基于索引

缺点:我必须执行复杂的操作:

1. 架构迁移以创建新的 SmallIntegerField
2. 数据迁移将数百万个实例从旧字段复制(并转换)到新字段。
3. 更新项目代码以使用新字段或执行另一次架构迁移以将新字段重命名为前一个字段。
4. 删除旧字段。

<小时/>

总结一下，这里真正的问题是:

将字段迁移到 SmallIntegerField 所获得的性能提升值得冒这个风险吗？

我倾向于尝试选项 A，并检查性能改进是否足够。

<小时/>

我还向 StackOverflow 提出了这个问题，因为出现了一个更通用的问题:

• 在任何情况下，在 Django 选择中使用 CharFields 是否比使用 Boolean/Integer/SmallIntegerField 更好？

这种情况的产生是因为在定义项目模型时我受到Django documentation code snippet的启发。 :

YEAR_IN_SCHOOL_CHOICES = (
     ('FR', 'Freshman'),
     ('SO', 'Sophomore'),
     ('JR', 'Junior'),
     ('SR', 'Senior'),
)

year_in_school = models.CharField(max_length=2,
                                  choices=YEAR_IN_SCHOOL_CHOICES,
                                  default=FRESHMAN)

为什么他们可以使用整数而使用字符，因为它只是一种永远不应该显示的值表示形式？

Answer 1

计数查询速度。

UserEvent.objects.filter(event_type=UserEvent.B_EVENT).count()
# elapsed time: 0:00:06.921287

不幸的是，当表有大量条目时，这种性质的查询在数据库中总是很慢。

Mysql通过查看索引provided the indexed columns are numeric来优化count查询。因此，如果您使用的是 mysql，但显然您不是，那么这是使用 SmallIntegeField 而不是 Charfield 的一个很好的理由。您的里程因其他数据库而异。我不是 SQL Server 方面的专家，但我的理解是它是 particularly poor at using indexes关于 COUNT(*) 次查询。

分区

您也许可以通过对数据进行分区来提高涉及 event_type 的查询的整体性能。由于当前索引的基数很差，因此规划器通常最好进行全表扫描。如果数据已分区，则只需要扫描该特定分区。

Char 或 Smallint

char(2)和small int哪个占用更多空间？答案是，这取决于您的字符集。如果字符集只需要每个字符一个字节，那么小整数和 char(2) 将占用相同的空间量。由于该字段的基数非常低，因此在这种情况下使用 char 或smallint 不会产生任何显着差异。