使用多个 JOIN 查询 1k 条目最多需要 10 秒
这是该结构的简化版本（省略了一些常规 varchar cols）：
CREATE TABLE `car` (
`reg_plate` varchar(16) NOT NULL default '',
`type` text NOT NULL,
`client` int(11) default NULL,
PRIMARY KEY (`reg_plate`)
)
这是我尝试运行的查询：
SELECT * FROM (
SELECT
car.*,
tire.id as tire,
client.name as client_name
FROM
car
LEFT JOIN client ON car.client = client.id
LEFT JOIN tire ON tire.reg_plate = reg_plate
GROUP BY car.reg_plate
) t1
嵌套查询是必要的，因为框架有时会添加 WHERE / SORT 子句（假设有名为 <代码>client_name 或轮胎）。 car 和 tire 表都有大约。 1,5K 条目。 client 不超过 500，并且由于某种原因，它仍然需要长达 10 秒才能完成（更糟糕的是，框架运行它两次，首先检查有多少行，然后实际限制为请求的页面）
我感觉这个查询效率非常低，我只是不知道如何优化它。
提前致谢。
Here's a simplified version of the structure (left out some regular varchar cols):
CREATE TABLE `car` (
`reg_plate` varchar(16) NOT NULL default '',
`type` text NOT NULL,
`client` int(11) default NULL,
PRIMARY KEY (`reg_plate`)
)
And here's the query I'm trying to run:
SELECT * FROM (
SELECT
car.*,
tire.id as tire,
client.name as client_name
FROM
car
LEFT JOIN client ON car.client = client.id
LEFT JOIN tire ON tire.reg_plate = reg_plate
GROUP BY car.reg_plate
) t1
The nested query is necessary due to the framework sometimes adding WHERE / SORT clauses (which assume there are columns named client_name or tire).
Both the car and the tire tables have approx. 1,5K entries. client has no more than 500, and for some reason it still takes up to 10 seconds to complete (worse, the framework runs it twice, first to check how much rows there are, then to actually limit to the requested page)
I'm getting a feeling that this query is very inefficient, I just don't know how to optimize it.
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，阅读 MySQL 的 EXPLAIN 语法。
您可能需要在连接子句中的每一列上以及您的框架在 WHERE 和 SORT 子句中使用的每一列上建立索引。有时多列索引比单列索引更好。
您的框架可能不需要嵌套查询。取消嵌套并创建视图或将参数传递给存储过程可能会给您带来更好的性能。
为了获得有关 SO 的更好建议，请始终在问题中包含 DDL 和示例数据（作为 INSERT 语句）。您可能还应该包含有关性能问题的 EXPLAIN 输出。
First, read up on MySQL's EXPLAIN syntax.
You probably need indexes on every column in the join clauses, and on every column that your framework uses in WHERE and SORT clauses. Sometimes multi-column indexes are better than single-column indexes.
Your framework probably doesn't require nested queries. Unnesting and creating a view or passing parameters to a stored procedure might give you better performance.
For better suggestions on SO, always include DDL and sample data (as INSERT statements) in your questions. You should probably include EXPLAIN output on performance questions, too.