Varchar 上的索引是否会对性能产生影响?

2021-12-21 00:00:00 indexing performance sql mysql varchar

varchar 列上的索引是否会使查询运行速度变慢?我可以把它变成int.我不需要做 LIKE % 比较.

Does index on a varchar column make the query run slower? I can make that be int. and I don't need to do the LIKE % comparison.

推荐答案

varchar 列上的索引是否会使查询运行速度变慢?

Does index on a varchar column make the query run slower?

不，没有.
如果优化器决定使用索引，查询将运行得更快.该表上的 INSERTs/UPDATEs/DELETEs 会更慢，但不太可能引起注意.

No, it does not.
If the optimizer decides to use of the index, the query will run faster. INSERTs/UPDATEs/DELETEs on that table will be slower, but not likely enough to notice.

我不需要做 LIKE % 比较

I don't need to do the LIKE % comparison

注意使用:

LIKE '%whatever%'

...将不使用索引，但以下将:

...will not use an index, but the following will:

LIKE 'whatever%'

关键是字符串左侧的通配符意味着不能使用列上的索引.

The key is wildcarding the lefthand side of the string means that an index on the column can't be used.

还知道 MySQL 限制了为索引 - 对于 MyISAM(InnoDB 为 767 字节)表，它们最长可达 1000 字节.

Also know that MySQL limits the amount of space set aside for indexes - they can be up to 1000 bytes long for MyISAM (767 bytes for InnoDB) tables.

相关文章