mysql - mysql数据库表中的pkey

Question

从这个 SQL 语句中，我试图了解 pkey 的使用，我们已经将复合键作为主键，有人可以解释一下吗？

CREATE TABLE categories_products (
    category_id int unsigned not null,
    product_id int unsigned not null,
    PRIMARY KEY (category_id, product_id),
    KEY pkey (product_id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE
);

Answer 1

通过扩展解释，并从你的例子出发:

首先，让我们设置环境:

mysql>
mysql> create table categories_products (
    ->   category_id int unsigned not null,
    ->   product_id int unsigned not null,
    ->   somefield varchar(25),
    ->   primary key (category_id, product_id)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> insert into categories_products(category_id, product_id) values (1, 1), (1, 2), (2, 2);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

我删除了外键，因为它们对此没有任何影响，而且我添加了一个额外的字段，因为它使结果更容易解释。之后我会对此进行扩展。

首先我们尝试查询特定类别:

mysql> explain select * from categories_products where category_id = 1;
+----+-------------+---------------------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table               | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------------------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | categories_products | ref  | PRIMARY       | PRIMARY | 4       | const |    2 |       |
+----+-------------+---------------------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

我们可以从结果中看到它确实使用了 PRIMARY 键，并且只检查了 2 行，因为索引知道这就是匹配 的所有行>where 约束。

我们的下一个测试查询产品和类别:

mysql> explain select * from categories_products where category_id = 1 and product_id = 1;
+----+-------------+---------------------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table               | type  | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+---------------------+-------+---------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | categories_products | const | PRIMARY       | PRIMARY | 8       | const,const |    1 |       |
+----+-------------+---------------------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)

我们可以再次看到正在使用 PRIMARY 键，但这次更好，它只返回一行。

接下来，让我们试着只看一个产品:

mysql> explain select * from categories_products where product_id = 1;
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | categories_products | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

这一次，查询无法找到合适的索引来使用，因此不得不使用 where 来缩小结果范围。这比它能够执行索引查找的效率要低。

为什么会这样？为什么优化器可以使用 category_id 而不是 product_id 即使它们都在复合索引中？因为 MySQL 从左到右读取该索引。考虑一个大型复合索引 (f1, f2, f3, f4)。这隐含地允许您访问这些附加索引，(f1)、(f1, f2)、(f1, f2, f3)。

现在让我们在其中添加您的 pkey 索引，看看会发生什么。

mysql> create index pkey on categories_products(product_id);
explain select * from categories_products where product_id = 1;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> explain select * from categories_products where product_id = 1;
+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | categories_products | ref  | pkey          | pkey | 4       | const |    1 |       |
+----+-------------+---------------------+------+---------------+------+---------+-------+------+-------+

好了，现在也可以使用索引执行与以前相同的查询。

现在来谈谈我添加额外字段的原因。当主键覆盖整个表时，就像在这种情况下所做的那样，任何查询都将从索引而不是表中读取整个结果，这可能会在解释结果中产生一些误导性的条目。例如，如果我删除该附加字段和 pkey 索引，然后重新运行查询，结果如下:

mysql> explain select * from categories_products where product_id = 1;
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table               | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | categories_products | index | NULL          | PRIMARY | 8       | NULL |    3 | Using where; Using index |
+----+-------------+---------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

现在乍一看似乎正在使用 PRIMARY 索引，但仔细检查我们发现它仍在检查整个表(3 行)，并使用 where 限制结果，如以及索引。

无论如何，这就是为什么您需要 product_id 上的索引的原因