MySQL对InnerJoin有限制吗？
MySQL对InnerJoin有限制吗？
提问于 2012-04-14 22:45:00
我有这个查询来收集关于单个订单的信息，它变得相当复杂。
我没有任何数据可以测试，所以我想问的是，如果任何人在大大小小的数据集上都有这方面的经验，那么在一个查询中可以或应该进行多少连接有限制吗？将大型查询拆分成较小的部分是否可取，或者这不会产生显着差异？
另外，在每个INNER JOIN后面有一个WHERE子句合法吗
谢谢你的建议。
下面是查询：
# 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;
}由于有人请求了此查询的模式，因此如下所示：
# 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;回答 4
Stack Overflow用户
回答已采纳
发布于 2012-04-14 23:29:28
您没有任何接近MySQL连接限制的连接。您的joins数量还不错。但是，在派生表(您的内部子查询)上进行连接可能会导致性能问题，因为派生表没有索引。在没有索引的派生表上执行联接可能会很慢。
您应该考虑使用索引创建一个真正的临时表以进行连接，或者想出一种方法来避免子查询。
MySQL中的JOIN基本上类似于对每个连接的行执行查找(seek)。因此，如果您要连接许多记录，MySQL将不得不执行许多查找。问题不在于你连接了多少个表，而在于你连接了多少行。
无论如何，MySQL只会执行那么多次查找，然后就会放弃，只读整个表。它在决定哪种方法更便宜方面做得很好。
也许您能做的最好的事情是通过使用ANALYZE表更新索引统计信息来帮助它进行猜测。
每个SELECT可以有一个WHERE子句。因此，内部子查询将具有WHERE子句，外部查询将具有WHERE子句，这些子句将在连接之后应用(至少在逻辑上是如此，尽管出于性能考虑，MySQL通常会首先应用它们)。
此外，所有这些都假设您知道如何正确使用索引。
Stack Overflow用户
发布于 2012-04-14 23:13:41
嗯，我曾经尝试过查看连接数量的限制，如果我没记错的话，我在mysql 5.0.4上测试了一个包含100个表的连接。
我收到了以下错误：
表太多。MySQL在一个join中只能使用61个表。
我认为MySql 4的限制是31个表。
一句忠告如果你打算在一个查询中使用那么多的表，那么是时候开始考虑简化你的查询设计了。
Stack Overflow用户
发布于 2012-06-27 07:15:34
我也在搜索这个，然后我找到了另一个解决方案，添加了" and“作为连接的一部分。
INNER JOIN kopplaMedlemIntresse
ON intressen.id = kopplaMedlemIntresse.intresse_id AND kopplaMedlemIntresse.medlem_id = 3页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/10154466
复制相关文章
相似问题
腾讯云开发者
