mysql - 按字符串或整数搜索/索引

Question

好吧，这可能是一个新手问题，但我不知道如何在搜索中最好地表达它，所以我只是问。下面哪个选项更有效以及为什么。发生更改的列是一个将被大量用作搜索参数的列，我将为它建立索引。

选项 A，在“交易”表中使用“categoryName”:

Table Name: Categories
+--------------+
| categoryName |
+--------------+
| Gas          |
+--------------+
| Coffee       |
+--------------+
| Restaurants  |
+--------------+

Table Name: Transactions
+--------+--------------+-------------+---------+----------+--------+
| userID | categoryName | description | date    | budgeted | actual |
+--------+--------------+-------------+---------+----------+--------+
| 0      | Gas          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 0      | Coffee       | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 0      | Restaurant   | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 0      | Restaurant   | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 0      | Restaurant   | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 1      | Gas          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 1      | Coffee       | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 1      | Restaurant   | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 2      | Coffee       | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+
| 2      | Coffee       | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+--------------+-------------+---------+----------+--------+

选项B，为每个categoryName分配一个categoryID并在Transactions表中使用categoryID

Table Name: Categories
+-----------+--------------+
|categoryID | categoryName |
+-----------+--------------+
| 1         | Gas          |
+-----------+--------------+
| 2         | Coffee       |
+-----------+--------------+
| 3         | Restaurants  |
+-----------+--------------+

Table Name: Transactions
+--------+------------+-------------+---------+----------+--------+
| userID | categoryID | description | date    | budgeted | actual |
+--------+------------+-------------+---------+----------+--------+
| 0      | 1          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 0      | 2          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 0      | 3          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 0      | 3          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 0      | 3          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 1      | 1          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 1      | 2          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 1      | 3          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 2      | 2          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+
| 2      | 2          | blurb       | 12-1-13 | 5.00     | 5.00   |
+--------+------------+-------------+---------+----------+--------+

Answer 1

选项B就是答案。想象一下如果您必须更改其中一个值会发生什么。 天然气变成燃料。在选项 A 中，另一个表将有一堆带有 Gas 的列，这是毫无意义的。

使用整数键比使用 varchar 更高效。