MySQL优化，“喜欢"与“=" [英] MySQL Optimization, "like" vs "="

问题描述

我有一个包含如下列的表格:

I have a table with columns like this:

| Country.Number | CountryName |
| US.01          | USA     |
| US.02          | USA     |

我想将其修改为:

| Country | Number | CountryName |
| US      | 01     | USA     |
| US      | 02     | USA     |

关于优化，如果我使用:

Regarding optimization, is there a difference in performance if I use:

select * from mytable where country.number like "US.%"

或

select * from mytable where country = "US"

推荐答案

在这种特殊情况下，性能差异很可能很小，因为 mysql 使用 "US.%" 上的索引.在搜索类似 "%.US"(通配符在前面)之类的内容时，主要会感觉到性能下降.因为它会在不使用索引的情况下进行表格扫描.

The performance difference will most likely be miniscule in this particular case, as mysql uses an index on "US.%". The performance degradation is mostly felt when searching for something like "%.US" (the wildcard is in front). As it then does a tablescan without using indices.

EDIT:你可以这样看:

MySql 在内部存储 varchar 索引，就像树一样，第一个符号是根并分支到每个下一个字母.

MySql internally stores varchar indices like trees with first symbol being the root and branching to each next letter.

因此，当搜索 ="US" 时，它会查找 U，然后向下搜索 S，然后再向下搜索一步以确保这就是价值的终结.这是三个步骤.

So when searching for = "US" it looks for U, then goes one step down for S and then another to make sure that is the end of the value. That's three steps.

搜索 LIKE "US.%" 它再次查找 U，然后是 S，然后是 .然后停止搜索并显示结果 - 这也是三个步骤，因为它不关心值是否在那里终止.

Searching for LIKE "US.%" it looks again for U, then S, then . and then stops searching and displays the results - that's also three steps only as it cares not whether the value terminated there.

EDIT2:我绝不提倡这种数据库非规范化，我只是想引起您的注意，这件事可能不像乍一看那么简单.

EDIT2: I'm in no way promoting such database denormalization, I just wanted to attract your attention that this matter may not be as straightforward as it seems at first glance.

这篇关于MySQL优化，“喜欢"与“="的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！