mysql - 如何设计一个简单有效的MySQL黑名单表

假设我有一个用户表my_users，其中有一个主键id。另外，我想设计一个简单的黑名单表（在mysql中），其声明如下：

CREATE TABLE IF NOT EXISTS black_list (
  user_id INT NOT NULL,
  bad_string VARCHAR(100) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES my_users(id),
  PRIMARY KEY (user_id, bad_string));

对black_list中任何行的解释是，id为user_id的用户希望将字符串bad_string列入黑名单。显然，user_id不能是唯一的，因为一个用户可能有多个黑名单字符串。另一方面，bad_string不能是唯一的，因为可能有多个用户将同一字符串列入黑名单。但是，该对（user_id，bad_string）应该是唯一的，因为用户多次黑名单相同的字符串是没有意义的。
在最坏的情况下，当我们通过用户id（SELECT * FROM black_list WHERE user_id = X）选择黑名单时，mysql将不得不扫描整个black_list表。
我的问题是：对于SELECT表中的行数，是否可以在次线性时间内运行上述black_list语句？如果是的话，我怎么能做到呢？

最佳答案

您断言SELECT * FROM black_list WHERE user_id = X将必须扫描整个黑名单表是不正确的。
在这个sql fiddle中，您可以看到它使用了一个索引：

+----+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type |   table    | type | possible_keys |   key   | key_len |  ref  | rows | filtered |    Extra    |
+----+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | black_list | ref  | PRIMARY       | PRIMARY |       4 | const |    1 |   100.00 | Using index |
+----+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+