MySQL InnerJoin是否有限制? [英] MySQL Is there a limit to InnerJoin?
问题描述
我有这个查询来收集有关单个订单的信息，并且它变得非常复杂.
I have this query for gathering the information about a single order and its become quite complex.
我没有要测试的数据，所以我问，如果有人在小型和大型数据集中具有相关经验，是否可以限制或应该在单个查询中进行多少个联接?明智的做法是将大型查询分成较小的部分，还是没有明显的不同?
I don't have any data to test with so i'm asking, if anyone has experience with this in small and large data sets, is there a limit to how many joins you can or should make in a single query? Would it be advisable to split the large queries into smaller parts or does this not make a significant difference?
而且，在每个INNER JOIN之后都有一个WHERE子句是否合法?
Also, is it legal to have a WHERE clause after each INNER JOIN?
感谢您的建议.
这是查询:
# Order: Get Order
function getOrder($order_id) {
$sql = "SELECT (order.id, order.created, o_status.status,
/* payment info */
order.total, p_status.status,
/* ordered by */
cust_title.title, cust.forename, cust.surname,
customer.phone, customer.email,
cust.door_name, cust.street1,
cust.street2, cust.town,
cust.city, cust.postcode,
/* deliver to */
recip_title.title, recipient.forename, recipient.surname,
recipient.door_name, recipient.street1,
recipient.street2, recipient.town,
recipient.city, recipient.postcode,
/* deliver info */
shipping.name, order.memo,
/* meta data */
order.last_update)
FROM tbl_order AS order
INNER JOIN tbl_order_st AS o_status
ON order.order_status_id = o_status.id
INNER JOIN tbl_payment_st AS p_status
ON order.payment_status_id = p_status.id
INNER JOIN (SELECT (cust_title.title, cust.forename, cust.surname,
customer.phone, customer.email,
/* ordered by */ cust.door_name, cust.street1,
cust.street2, cust.town,
cust.city, cust.postcode)
FROM tbl_customer AS customer
INNER JOIN tbl_contact AS cust
ON customer.contact_id = cust.id
INNER JOIN tbl_contact_title AS cust_title
ON cust.contact_title_id = cust_title.id
WHERE order.customer_id = customer.id)
ON order.customer_id = customer.id
INNER JOIN (SELECT (recip_title.title, recipient.forename, recipient.surname,
/* deliver to */ recipient.door_name, recipient.street1,
recipient.street2, recipient.town,
recipient.city, recipient.postcode)
FROM tbl_contact AS recipient
INNER JOIN tbl_contact_title AS recip_title
ON recipient.contact_title_id = recip_title.id
WHERE order.contact_id = recipient.id)
ON order.contact_id = recipient.id
INNER JOIN tbl_shipping_opt AS shipping
ON order.shipping_option_id = shipping.id
WHERE order.id = '?';";
dbQuery($sql, array((int)$order_id));
$rows = dbRowsAffected();
if ($rows == 1)
return dbFetchAll();
else
return null;
}
由于有人请求此查询的模式，所以它是:
Since someone requested the schema for this query, here it is:
# TBL_CONTACT_TITLE
DROP TABLE IF EXISTS tbl_contact_title;
CREATE TABLE tbl_contact_title(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
title CHAR(3)
) ENGINE = InnoDB;
INSERT INTO tbl_contact_title
(title)
VALUES ('MR'),
('MRS'),
('MS');
# TBL_CONTACT
DROP TABLE IF EXISTS tbl_contact;
CREATE TABLE tbl_contact(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
contact_title_id INT,
FOREIGN KEY(contact_title_id) REFERENCES tbl_contact_title(id) ON DELETE SET NULL,
forename VARCHAR(50),
surname VARCHAR(50),
door_name VARCHAR(25),
street1 VARCHAR(40),
street2 VARCHAR(40),
town VARCHAR(40),
city VARCHAR(40),
postcode VARCHAR(10),
currency_id INT,
FOREIGN KEY(currency_id) REFERENCES tbl_currency(id) ON DELETE SET NULL
) ENGINE = InnoDB;
# TBL_CUSTOMER
DROP TABLE IF EXISTS tbl_customer;
CREATE TABLE tbl_customer(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
contact_id INT,
FOREIGN KEY(contact_id) REFERENCES tbl_contact(id) ON DELETE SET NULL,
birthday DATE,
is_male TINYINT,
phone VARCHAR(20),
email VARCHAR(50) NOT NULL
) ENGINE = InnoDB, AUTO_INCREMENT = 1000;
# TBL_ORDER_ST
DROP TABLE IF EXISTS tbl_order_st;
CREATE TABLE tbl_order_st(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
status VARCHAR(25)
) ENGINE = InnoDB;
INSERT INTO tbl_order_st
(status)
VALUES
('NEW'),
('PROCESSING'),
('SHIPPED'),
('COMPLETED'),
('CANCELLED');
# TBL_SHIPPING_OPT
DROP TABLE IF EXISTS tbl_shipping_opt;
CREATE TABLE tbl_shipping_opt(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(50),
description VARCHAR(255),
cost DECIMAL(6,3)
) ENGINE = InnoDB;
INSERT INTO tbl_shipping_opt
(name, description, cost)
VALUES
('UK Premier', 'U.K. Mainland upto 30KG, Next Working Day', 8.00),
('Europe Standard', 'Most European Destinations* upto 30KG, 2 to 5 Working Days *please check before purchase', 15.00);
# TBL_PAYMENT_ST
DROP TABLE IF EXISTS tbl_payment_st;
CREATE TABLE tbl_payment_st(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
status VARCHAR(25)
) ENGINE = InnoDB;
INSERT INTO tbl_payment_st
(status)
VALUES
('UNPAID'),
('PAID');
# TBL_ORDER
DROP TABLE IF EXISTS tbl_order;
CREATE TABLE tbl_order(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
customer_id INT,
FOREIGN KEY(customer_id) REFERENCES tbl_customer(id) ON DELETE SET NULL,
contact_id INT,
FOREIGN KEY(contact_id) REFERENCES tbl_contact(id) ON DELETE SET NULL,
created DATETIME,
last_update TIMESTAMP,
memo VARCHAR(255),
order_status_id INT,
FOREIGN KEY(order_status_id) REFERENCES tbl_order_st(id),
shipping_option_id INT,
FOREIGN KEY(shipping_option_id) REFERENCES tbl_shipping_opt(id),
coupon_id INT,
FOREIGN KEY(coupon_id) REFERENCES tbl_coupon(id) ON DELETE SET NULL,
total DECIMAL(9,3),
payment_status_id INT,
FOREIGN KEY(payment_status_id) REFERENCES tbl_payment_st(id)
) ENGINE = InnoDB, AUTO_INCREMENT = 1000;
推荐答案
您没有MySQL的JOIN限制附近的任何地方.您的加入数量还不错.但是，由于在派生表上没有索引，因此在执行过程中联接派生表(您的内部子查询)可能会导致性能问题.在没有索引的派生表上执行联接可能很慢.
You don't have anywhere near the limit of JOINs for MySQL. Your number of joins isn't bad. However, joining on a derived table (your inner subquery) as you're doing can cause performance issues, since derived tables don't have indexes. Performing a join on a derived table without indexes can be slow.
您应该考虑使用索引来创建一个真正的临时表，或者找出避免子查询的方法.
You should consider making a real temporary table with indexes for joining, or figure out a way to avoid the subquery.
MySQL中的JOIN基本上就像对每个连接的行进行查找(查找).因此，如果您要加入许多记录，MySQL将必须执行许多查找.问题在于，要连接的表数少于要连接的行数.
A JOIN in MySQL is basically like doing a lookup (seek) for each joined row. So, MySQL will have to perform many lookups if you are joining many records. It's less about how many tables you join than the number of rows that you join that can be a problem.
无论如何，MySQL在放弃并只读取整个表之前只会执行很多次查找.在决定哪种价格便宜方面，它做得很好.
Anyway, MySQL will only perform so many seeks before it will give up and just read the whole table. It does a pretty good job at deciding which will be less expensive.
也许您能做的最好的事情就是通过使用ANALYZE TABLE更新索引统计信息来帮助其猜测.
Perhaps the best thing you can do is help it guess by updating the index statistics with ANALYZE TABLE.
每个SELECT可以有一个WHERE子句.因此，您的内部子查询将具有WHERE子句，而外部查询将具有WHERE子句，这些将在JOIN之后应用(至少从逻辑上讲，尽管MySQL通常会首先应用它们以提高性能).
You can have one WHERE clause per SELECT. So your inner subquery will have a WHERE clause and your outer query will have a WHERE clause, and these get applied after the JOIN (at least logically, though MySQL will generally apply them first for performance).
此外，所有这些都是假设您知道如何正确使用索引.
Also, all of this is assuming you know how to properly use indexes.
这篇关于MySQL InnerJoin是否有限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
