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