mysql–外键识别和非识别关系之间的性能差异

我只是在我的数据库中添加了一些外键,而且通常我的所有外键都没有识别,因为我从未打扰过它们,因为我从来不知道差别,而且我的数据库似乎对我来说似乎总是运行良好.

现在我已经决定,我将正确地建立这个数据库,并使外键识别和识别.我很好奇在做加入时是否与他们有任何性能差异？

谢谢

解决方法:

是的,通过在识别关系上创建外键,可以为连接带来一些性能优势.但它取决于查询(因为优化方法总是如此).

例如,查询给定作者的书籍：

SELECT a.author_name, b.book_name
FROM Authors AS a
JOIN AuthorBooks AS ab ON a.author_id = ab.author_id
JOIN Books AS b ON b.book_id = ab.book_id
WHERE a.author_id = 12345;


在这种情况下,我们希望与AuthorBooks的连接使用索引.它会使用哪个索引？这取决于我们如何定义该表中的索引.

这两个实体表非常简单.

CREATE TABLE Authors (
author_id INT AUTO_INCREMENT PRIMARY KEY,
author_name VARCHAR(50)
);
CREATE TABLE Books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
book_name VARCHAR(50)
);


但是开发人员设计多对多表有两种常见方式.一个主键具有自动增量ID：

CREATE TABLE AuthorBooks (
id INT AUTO_INCREMENT PRIMARY KEY,
author_id INT NOT NULL,
book_id INT NOT NULL,
FOREIGN KEY (author_id) REFERENCES Authors (author_id)
FOREIGN KEY (book_id) REFERENCES Books (book_id)
);


另一个没有id.主键是两个外键的组合,这使得它们都与它们各自引用的实体表具有识别关系.

CREATE TABLE AuthorBooks (
author_id INT NOT NULL,
book_id INT NOT NULL,
PRIMARY KEY (author_id, book_id),
FOREIGN KEY (author_id) REFERENCES Authors (author_id)
FOREIGN KEY (book_id) REFERENCES Books (book_id)
);


在性能方面有什么不同？

首先,请记住MySQL如何为外键实现索引：如果没有索引,外键将隐式创建一个.如果列上已有索引,则外键将使用它.甚至包含可作为最左列的外键列的索引也可以使用,并且不需要为外键创建新索引.

在第一个AuthorBooks表设计中,当MySQL从作者到AuthorBooks的连接时,它在author_id外键的索引中查找一个条目.但是要执行第二次连接,该索引条目必须获取它引用的行,以获取book_id值,然后将其用于连接到Books表.因此,连接最终需要额外的表查找.

在第二个AuthorBooks表设计中,author_id由表的PRIMARY KEY索引.因此,当连接执行对author_id的查找时,它可以访问匹配的book_id,而无需对表进行额外查找.然后book_id可用于第二次连接.这消除了查询找到的每一行的步骤.

这对性能来说是一个很大的好处.我简单地通过使多对多表使用这样的覆盖索引来优化一些查询 – 无论是通过使用主键还是在两个外键上创建额外的两列索引 – 这导致最多六个订单性能提升幅度.