假设我有一个用户表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_idbad_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 |
+----+-------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+

10-04 10:57