文章/答案/技术大牛

发布

问mysql数据库表中的pkey
EN

Stack Overflow用户

提问于 2015-05-21 04:51:04

回答 1查看 435关注 0票数 3

从这个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
);

mysql

sql

database

回答 1

Stack Overflow用户

回答已采纳

发布于 2015-05-21 05:27:03

通过扩展解释，并从您的例子出发：

首先，让我们设置环境：

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)

我们再一次看到主键正在被使用，但这一次更好的是，它只返回一行。

接下来，让我们来看看一个产品：

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)

现在乍一看，主索引似乎正在被使用，但仔细观察，我们发现它仍然在检查整个表(3行)，并限制了where和索引的结果。

无论如何，这就是为什么您需要在product_id上建立索引。

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/30364846

复制

相似问题

问mysql数据库表中的pkey
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql数据库表中的pkeyEN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysql数据库表中的pkey
EN