如何优化由SQLAlchemy生成的查询? [英] How can I optimize this query produced by SQLAlchemy?
问题描述
我有一个由SQLAlchemy ORM生成的查询.它应该检索特定课程的stream_items以及它们的所有部分-资源，内容文本块等，以及发布它们的用户.但是，此查询似乎非常慢，在我们的生产数据库上花费了几分钟，数据库中有20,000名左右的用户，该课程中有25个左右的stream_items，每个stream_item有几个内容文本块.请注意，数据库中除用户外，其他记录很少，因为我们导入了很多用户，但内容却很少.
I have a query generated by SQLAlchemy ORM. It is supposed to retrieve stream_items for a specific course, along with all of their parts - resources, content text blocks, etc., and the users who posted them. However, this query appears to be extremely slow, taking minutes on our production database with 20,000 or so users in the database, 25 or so stream_items for the course, and a couple content text blocks per stream_item. Note that there are very few of any other records besides users in the database because we imported a bunch of users but very little content.
请注意，每个对象ID都是franklin_object表中的外键.
Note that every object id is a foreign key into the franklin_object table.
我尝试查看查询，并确定了几个麻烦的位(查看EXPLAIN输出)
I've tried looking at the query, and have identified several troubling bits (looking at the EXPLAIN output)
- 其中一项查询是使用临时；使用文件排序".
- 用户表被击中两次，没有索引
- 内容文本块表被击中两次，没有索引
但是，我真的不知道该怎么办，尤其是后两个问题.
However, I really don't know what to do about these, especially the latter two issues.
这是查询:
SELECT stream_item.id AS stream_item_id,
franklin_object.id AS franklin_object_id,
franklin_object.type AS franklin_object_type,
franklin_object.uuid AS franklin_object_uuid,
stream_item.parent_id AS stream_item_parent_id,
stream_item.shown_at AS stream_item_shown_at,
stream_item.author_id AS stream_item_author_id,
stream_item.stream_sort_at AS stream_item_stream_sort_at,
stream_item.PUBLIC AS stream_item_public,
stream_item.created_at AS stream_item_created_at,
stream_item.updated_at AS stream_item_updated_at,
anon_1.content_text_block_text AS anon_1_content_text_block_text,
anon_2.resource_id AS anon_2_resource_id,
anon_2.franklin_object_id AS anon_2_franklin_object_id,
anon_2.franklin_object_type AS anon_2_franklin_object_type,
anon_2.franklin_object_uuid AS anon_2_franklin_object_uuid,
anon_2.resource_top_parent_resource AS anon_2_resource_top_parent_resource,
anon_2.resource_top_parent_id AS anon_2_resource_top_parent_id,
anon_2.resource_title AS anon_2_resource_title,
anon_2.resource_url AS anon_2_resource_url,
anon_2.resource_image AS anon_2_resource_image,
anon_2.resource_created_at AS anon_2_resource_created_at,
anon_2.resource_updated_at AS anon_2_resource_updated_at,
franklin_object_1.id AS franklin_object_1_id,
franklin_object_1.type AS franklin_object_1_type,
franklin_object_1.uuid AS franklin_object_1_uuid,
anon_1.content_text_block_id AS anon_1_content_text_block_id,
anon_1.franklin_object_id AS anon_1_franklin_object_id,
anon_1.franklin_object_type AS anon_1_franklin_object_type,
anon_1.franklin_object_uuid AS anon_1_franklin_object_uuid,
anon_1.content_text_block_position AS anon_1_content_text_block_position,
anon_1.content_text_block_franklin_object_id AS anon_1_content_text_block_franklin_object_id,
anon_1.content_text_block_created_at AS anon_1_content_text_block_created_at,
anon_1.content_text_block_updated_at AS anon_1_content_text_block_updated_at,
anon_3.user_password AS anon_3_user_password,
anon_3.user_auth_token AS anon_3_user_auth_token,
anon_3.user_id AS anon_3_user_id,
anon_3.franklin_object_id AS anon_3_franklin_object_id,
anon_3.franklin_object_type AS anon_3_franklin_object_type,
anon_3.franklin_object_uuid AS anon_3_franklin_object_uuid,
anon_3.user_email AS anon_3_user_email,
anon_3.user_auth_token_expiration AS anon_3_user_auth_token_expiration,
anon_3.user_active AS anon_3_user_active,
anon_3.user_activation_token AS anon_3_user_activation_token,
anon_3.user_first_name AS anon_3_user_first_name,
anon_3.user_last_name AS anon_3_user_last_name,
anon_3.user_image AS anon_3_user_image,
anon_3.user_bio AS anon_3_user_bio,
anon_3.user_aspirations AS anon_3_user_aspirations,
anon_3.user_website AS anon_3_user_website,
anon_3.user_resume AS anon_3_user_resume,
anon_3.user_resume_name AS anon_3_user_resume_name,
anon_3.user_primary_role AS anon_3_user_primary_role,
anon_3.user_institution_id AS anon_3_user_institution_id,
anon_3.user_birth_date AS anon_3_user_birth_date,
anon_3.user_gender AS anon_3_user_gender,
anon_3.user_graduation_year AS anon_3_user_graduation_year,
anon_3.user_complete AS anon_3_user_complete,
anon_3.user_masthead_y_position AS anon_3_user_masthead_y_position,
anon_3.user_masthead AS anon_3_user_masthead,
anon_3.user_fb_access_token AS anon_3_user_fb_access_token,
anon_3.user_fb_user_id AS anon_3_user_fb_user_id,
anon_3.user_location AS anon_3_user_location,
anon_3.user_created_at AS anon_3_user_created_at,
anon_3.user_updated_at AS anon_3_user_updated_at,
anon_4.content_text_block_text AS anon_4_content_text_block_text,
anon_4.content_text_block_id AS anon_4_content_text_block_id,
anon_4.franklin_object_id AS anon_4_franklin_object_id,
anon_4.franklin_object_type AS anon_4_franklin_object_type,
anon_4.franklin_object_uuid AS anon_4_franklin_object_uuid,
anon_4.content_text_block_position AS anon_4_content_text_block_position,
anon_4.content_text_block_franklin_object_id AS anon_4_content_text_block_franklin_object_id,
anon_4.content_text_block_created_at AS anon_4_content_text_block_created_at,
anon_4.content_text_block_updated_at AS anon_4_content_text_block_updated_at,
anon_5.user_password AS anon_5_user_password,
anon_5.user_auth_token AS anon_5_user_auth_token,
anon_5.user_id AS anon_5_user_id,
anon_5.franklin_object_id AS anon_5_franklin_object_id,
anon_5.franklin_object_type AS anon_5_franklin_object_type,
anon_5.franklin_object_uuid AS anon_5_franklin_object_uuid,
anon_5.user_email AS anon_5_user_email,
anon_5.user_auth_token_expiration AS anon_5_user_auth_token_expiration,
anon_5.user_active AS anon_5_user_active,
anon_5.user_activation_token AS anon_5_user_activation_token,
anon_5.user_first_name AS anon_5_user_first_name,
anon_5.user_last_name AS anon_5_user_last_name,
anon_5.user_image AS anon_5_user_image,
anon_5.user_bio AS anon_5_user_bio,
anon_5.user_aspirations AS anon_5_user_aspirations,
anon_5.user_website AS anon_5_user_website,
anon_5.user_resume AS anon_5_user_resume,
anon_5.user_resume_name AS anon_5_user_resume_name,
anon_5.user_primary_role AS anon_5_user_primary_role,
anon_5.user_institution_id AS anon_5_user_institution_id,
anon_5.user_birth_date AS anon_5_user_birth_date,
anon_5.user_gender AS anon_5_user_gender,
anon_5.user_graduation_year AS anon_5_user_graduation_year,
anon_5.user_complete AS anon_5_user_complete,
anon_5.user_masthead_y_position AS anon_5_user_masthead_y_position,
anon_5.user_masthead AS anon_5_user_masthead,
anon_5.user_fb_access_token AS anon_5_user_fb_access_token,
anon_5.user_fb_user_id AS anon_5_user_fb_user_id,
anon_5.user_location AS anon_5_user_location,
anon_5.user_created_at AS anon_5_user_created_at,
anon_5.user_updated_at AS anon_5_user_updated_at,
anon_6.stream_item_id AS anon_6_stream_item_id,
anon_6.franklin_object_id AS anon_6_franklin_object_id,
anon_6.franklin_object_type AS anon_6_franklin_object_type,
anon_6.franklin_object_uuid AS anon_6_franklin_object_uuid,
anon_6.stream_item_parent_id AS anon_6_stream_item_parent_id,
anon_6.stream_item_shown_at AS anon_6_stream_item_shown_at,
anon_6.stream_item_author_id AS anon_6_stream_item_author_id,
anon_6.stream_item_stream_sort_at AS anon_6_stream_item_stream_sort_at,
anon_6.stream_item_public AS anon_6_stream_item_public,
anon_6.stream_item_created_at AS anon_6_stream_item_created_at,
anon_6.stream_item_updated_at AS anon_6_stream_item_updated_at
FROM franklin_object
INNER JOIN stream_item
ON franklin_object.id = stream_item.id
INNER JOIN (SELECT franklin_object.id AS franklin_object_id,
franklin_object.type AS franklin_object_type,
franklin_object.uuid AS franklin_object_uuid,
content_text_block.id AS content_text_block_id,
content_text_block.text AS content_text_block_text,
content_text_block.position AS content_text_block_position,
content_text_block.franklin_object_id AS content_text_block_franklin_object_id,
content_text_block.created_at AS content_text_block_created_at,
content_text_block.updated_at AS content_text_block_updated_at
FROM franklin_object
INNER JOIN content_text_block
ON franklin_object.id = content_text_block.id) AS anon_1
ON stream_item.id = anon_1.content_text_block_franklin_object_id
LEFT OUTER JOIN contents_resources AS contents_resources_1
ON anon_1.content_text_block_id = contents_resources_1.content_id
LEFT OUTER JOIN (SELECT franklin_object.id AS franklin_object_id,
franklin_object.type AS franklin_object_type,
franklin_object.uuid AS franklin_object_uuid,
resource.id AS resource_id,
resource.top_parent_resource AS resource_top_parent_resource,
resource.top_parent_id AS resource_top_parent_id,
resource.title AS resource_title,
resource.url AS resource_url,
resource.image AS resource_image,
resource.created_at AS resource_created_at,
resource.updated_at AS resource_updated_at
FROM franklin_object
INNER JOIN resource
ON franklin_object.id = resource.id) AS anon_2
ON anon_2.resource_id = contents_resources_1.resource_id
LEFT OUTER JOIN contents_franklin_objects AS contents_franklin_objects_1
ON anon_1.content_text_block_id = contents_franklin_objects_1.content_id
LEFT OUTER JOIN franklin_object AS franklin_object_1
ON franklin_object_1.id = contents_franklin_objects_1.franklin_object_id
LEFT OUTER JOIN likers AS likers_1
ON stream_item.id = likers_1.post_id
LEFT OUTER JOIN (SELECT franklin_object.id AS franklin_object_id,
franklin_object.type AS franklin_object_type,
franklin_object.uuid AS franklin_object_uuid,
USER.id AS user_id,
USER.email AS user_email,
USER.password AS user_password,
USER.auth_token AS user_auth_token,
USER.auth_token_expiration AS user_auth_token_expiration,
USER.active AS user_active,
USER.activation_token AS user_activation_token,
USER.first_name AS user_first_name,
USER.last_name AS user_last_name,
USER.image AS user_image,
USER.bio AS user_bio,
USER.aspirations AS user_aspirations,
USER.website AS user_website,
USER.resume AS user_resume,
USER.resume_name AS user_resume_name,
USER.primary_role AS user_primary_role,
USER.institution_id AS user_institution_id,
USER.birth_date AS user_birth_date,
USER.gender AS user_gender,
USER.graduation_year AS user_graduation_year,
USER.complete AS user_complete,
USER.masthead_y_position AS user_masthead_y_position,
USER.masthead AS user_masthead,
USER.fb_access_token AS user_fb_access_token,
USER.fb_user_id AS user_fb_user_id,
USER.location AS user_location,
USER.created_at AS user_created_at,
USER.updated_at AS user_updated_at
FROM franklin_object
INNER JOIN USER
ON franklin_object.id = USER.id) AS anon_3
ON anon_3.user_id = likers_1.user_id
LEFT OUTER JOIN contents_franklin_objects AS contents_franklin_objects_2
ON franklin_object.id = contents_franklin_objects_2.franklin_object_id
LEFT OUTER JOIN (SELECT franklin_object.id AS franklin_object_id,
franklin_object.type AS franklin_object_type,
franklin_object.uuid AS franklin_object_uuid,
content_text_block.id AS content_text_block_id,
content_text_block.text AS content_text_block_text,
content_text_block.position AS content_text_block_position,
content_text_block.franklin_object_id AS content_text_block_franklin_object_id,
content_text_block.created_at AS content_text_block_created_at,
content_text_block.updated_at AS content_text_block_updated_at
FROM franklin_object
INNER JOIN content_text_block
ON franklin_object.id = content_text_block.id) AS anon_4
ON anon_4.content_text_block_id = contents_franklin_objects_2.content_id
LEFT OUTER JOIN (SELECT franklin_object.id AS franklin_object_id,
franklin_object.type AS franklin_object_type,
franklin_object.uuid AS franklin_object_uuid,
stream_item.id AS stream_item_id,
stream_item.parent_id AS stream_item_parent_id,
stream_item.shown_at AS stream_item_shown_at,
stream_item.author_id AS stream_item_author_id,
stream_item.stream_sort_at AS stream_item_stream_sort_at,
stream_item.PUBLIC AS stream_item_public,
stream_item.created_at AS stream_item_created_at,
stream_item.updated_at AS stream_item_updated_at
FROM franklin_object
INNER JOIN stream_item
ON franklin_object.id = stream_item.id) AS anon_6
ON anon_6.stream_item_parent_id = franklin_object.id
LEFT OUTER JOIN likers AS likers_2
ON anon_6.stream_item_id = likers_2.post_id
LEFT OUTER JOIN (SELECT franklin_object.id AS franklin_object_id,
franklin_object.type AS franklin_object_type,
franklin_object.uuid AS franklin_object_uuid,
USER.id AS user_id,
USER.email AS user_email,
USER.password AS user_password,
USER.auth_token AS user_auth_token,
USER.auth_token_expiration AS user_auth_token_expiration,
USER.active AS user_active,
USER.activation_token AS user_activation_token,
USER.first_name AS user_first_name,
USER.last_name AS user_last_name,
USER.image AS user_image,
USER.bio AS user_bio,
USER.aspirations AS user_aspirations,
USER.website AS user_website,
USER.resume AS user_resume,
USER.resume_name AS user_resume_name,
USER.primary_role AS user_primary_role,
USER.institution_id AS user_institution_id,
USER.birth_date AS user_birth_date,
USER.gender AS user_gender,
USER.graduation_year AS user_graduation_year,
USER.complete AS user_complete,
USER.masthead_y_position AS user_masthead_y_position,
USER.masthead AS user_masthead,
USER.fb_access_token AS user_fb_access_token,
USER.fb_user_id AS user_fb_user_id,
USER.location AS user_location,
USER.created_at AS user_created_at,
USER.updated_at AS user_updated_at
FROM franklin_object
INNER JOIN USER
ON franklin_object.id = USER.id) AS anon_5
ON anon_5.user_id = likers_2.user_id
WHERE stream_item.parent_id = 11
ORDER BY stream_item.stream_sort_at DESC,
anon_1.content_text_block_position,
anon_6.stream_item_stream_sort_at DESC
EXPLAIN输出:
And the EXPLAIN output:
ID SELECT_TYPE TABLE POSSIBLY_KEYS KEY KEY_LEN REF ROWS EXTRA
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 599 Using temporary; Using filesort
1 PRIMARY stream_item eq_ref PRIMARY,parent_id PRIMARY 4 anon_1.content_text_block_franklin_object_id 1 Using where
1 PRIMARY contents_resources_1 ref content_id content_id 5 anon_1.content_text_block_id 2
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 7
1 PRIMARY contents_franklin_objects_1 ref content_id content_id 5 anon_1.content_text_block_id 1
1 PRIMARY franklin_object eq_ref PRIMARY PRIMARY 4 franklin.stream_item.id 1 Using where
1 PRIMARY franklin_object_1 eq_ref PRIMARY PRIMARY 4 franklin.contents_franklin_objects_1.franklin_object_id 1
1 PRIMARY likers_1 ref post_id post_id 5 franklin.stream_item.id 1
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 136
1 PRIMARY contents_franklin_objects_2 ref franklin_object_id franklin_object_id 5 franklin.stream_item.id 1
1 PRIMARY <derived5> ALL NULL NULL NULL NULL 599
1 PRIMARY <derived6> ALL NULL NULL NULL NULL 608
1 PRIMARY likers_2 ref post_id post_id 5 anon_6.stream_item_id 1
1 PRIMARY <derived7> ALL NULL NULL NULL NULL 136
7 DERIVED user ALL PRIMARY NULL NULL NULL 133
7 DERIVED franklin_object eq_ref PRIMARY PRIMARY 4 franklin.user.id 1
6 DERIVED stream_item ALL PRIMARY NULL NULL NULL 709
6 DERIVED franklin_object eq_ref PRIMARY PRIMARY 4 franklin.stream_item.id 1
5 DERIVED content_text_block ALL PRIMARY NULL NULL NULL 666
5 DERIVED franklin_object eq_ref PRIMARY PRIMARY 4 franklin.content_text_block.id 1
4 DERIVED user ALL PRIMARY NULL NULL NULL 133
4 DERIVED franklin_object eq_ref PRIMARY PRIMARY 4 franklin.user.id 1
3 DERIVED resource ALL PRIMARY NULL NULL NULL 7
3 DERIVED franklin_object eq_ref PRIMARY PRIMARY 4 franklin.resource.id 1
2 DERIVED content_text_block ALL PRIMARY NULL NULL NULL 666
2 DERIVED franklin_object eq_ref PRIMARY PRIMARY 4 franklin.content_text_block.id 1
如何将ALL查询减少到更快的速度?我还有什么其他方法可以加快速度?
How do I reduce the ALL queries to something faster? What are other ways I can speed this up?
franklin_objects设置反模式的方式吗?它的工作方式是franklin_object表具有两列:id和type.那么每种类型都是一个表，其主键是franklin_object的外键.
Is the way franklin_objects are set up an antipattern? The way it works is that the franklin_object table has two columns: id and type. Then each type is a table, with a primary key that is a foreign key into franklin_object.
生成sql的代码大致如下:
The code that generates the sql is something along the lines of:
stream_item_query = StreamItem.query.options(db.joinedload('stream_items'),db.joinedload('contents_included_in'),db.joinedload('contents.resources'),db.joinedload('contents.objects'),db.subqueryload('likers'))
stream_items = stream_item_query.filter(StreamItem.parent_id == community_id).order_by(db.desc(StreamItem.stream_sort_at)).all()
推荐答案
哇，这有点伤了我的大脑.试图弄清楚查询在做什么，所有表是什么以及关系是乏味的.如果您有类似的经历，那么请以第一个提示为可能，您可能会在此单个查询中尝试执行过多操作.
Wow, this one hurt my brain a little. Trying to figure out what the query is doing, what all the tables are, and the relationships was tedious. If you have a similar experience, let that be the first hint that you're probably trying to do too much in this single query.
我的建议是重新考虑您的整个方法.
My suggestion is to rethink your entire approach.
SQLAlchemy是一个非常不错的工具，我不会猛击它(或您选择的mysql)，但是与大多数ORM工具一样，您需要考虑使用它们的成本.这个franklin_object表格业务就是一个例子.这是反模式吗?是和否.从纯粹的面向对象角度来看，这是有道理的.您可以通过在该表中查找id来确定要查询的表.从关系查询的角度来看，它的作用很小.我可以从您的查询中删除franklin_object的每个实例，并且除了... franklin_object中的列之外，什么都不会丢失.如果那是一个可行的选择，我会立即这样做.
SQLAlchemy is a pretty nice tool, and I'm not going to bash it (or your choice of mysql), but as with most ORM tools you need to consider the costs with their use. One example is this franklin_object table business. Is this an anti-pattern? Yes and No. It makes sense from a purely OO perspective. You can determine which tables to query by looking up an id in this table. From a relational querying perspective, it serves very little purpose. I could remove every instance of franklin_object from your query and lose nothing but...the columns from franklin_object. If that's a viable option, I would do that right away.
让我们进一步检查与franklin_object的链接.查看子查询，它们都具有相同的形式:
Let's examine this linking with franklin_object further. Looking at the sub-queries, they all have the same form:
SELECT franklin_object.id AS franklin_object_id,
franklin_object.type AS franklin_object_type,
franklin_object.uuid AS franklin_object_uuid,
linked_table.id AS linked_table_id,
linked_table.col2 AS col2 --and more
FROM franklin_object
INNER JOIN linked_table
ON franklin_object.id = linked_table.id) AS anon_n
关于如何优化查询的这一部分，没有多少信息可供数据库继续使用，而与统计信息无关.也许如果通过在where子句中指定type来限制franklin_object，则查询会更好.可能是.
There isn't much information for the database to go on as far as how to optimize this part of the query, regardless of statistics. Perhaps if franklin_object were limited by specifying the type in a where clause the query would be better. Maybe.
这对于USER表尤其有问题，因为该表有很多记录(可以这么说).由于查询的是大多数列，并且优化器无法准确确定要检索的行数，因此执行全表扫描是有意义的.就您而言，两次.
This is especially problematic with the USER table, as this table has a lot of records (so you say). Since you are querying most of the columns, and the optimizer can't accurately figure out how many rows will be retrieved, it makes sense that a full table scan be performed. In your case, twice.
另一方面是所涉及的联接的绝对数量.如果我们除去所有franklin_object引用，仍然有11个联接.如果您的数据模型更具关系性，那不是很糟糕，但事实并非如此.生成的查询对数据库没有太多帮助，无法确定执行查询的最佳方法，因此它做得不好.也许您可以通过提示等来减轻这种情况，但是从长远来看，我敢打赌会刺伤您.
Another aspect is the sheer number of joins involved. If we take out all the franklin_object references, there are still 11 joins. That's not terrible, if your data model was more relational, but it isn't. The generated query doesn't give much help to the database to figure out the best way to perform the query, and so it doesn't do a good job. Maybe you could mitigate this with hints and so forth, but I bet this will bite you in the long-run.
您使用的是ORM工具，因此确实使用.一次完成这么大的查询不会对您有任何帮助.可以将其拆分一些以提高性能.执行惰性检索，以避免庞大，复杂的查询.我想说，尝试看看一切进展，懒惰地做所有事情.性能可能还可以，我会说更好.不是很好，可能甚至不能接受，但是比在数据库搅动时喝咖啡更好.
You're using an ORM tool, so really use it. You don't gain anything by having such a large query done all at once. It could be split up a bit for performance. Perform lazy retrieves to avoid huge, complicated queries. I would say try, just to see how it goes, to do everything lazily. Performance will likely be ok, I'd say better. Not great, probably not even acceptable, but better than being able to get coffee while the database is churning.
然后，开始将事情拼接成更简化的块.将逻辑上有意义的对象联系在一起，例如resource和contents_resources.另一个示例是stream_item，likers和user之间的连接重复.进行一个查询，然后让SQLAlchemy执行其操作.
Then, start piecing things together into more streamlined chunks. Tie together objects which logically make sense, such as resource and contents_resources. Another example, the connection between stream_item, likers and user is duplicated. Make that one query and let SQLAlchemy do its thing.
作为最后的手段，可以实现某种缓存机制.也许在某处对表进行非规范化.在变化缓慢，读取繁重的系统上，您可以将这些表输入到另一个结构中，在该结构中查询既简单又快速.也就是说，要预先进行处理并将其存储在单个表中.
As a last resort, some kind of caching mechanism could be implemented. Perhaps denormalize the tables somewhere. On a slow-changing, read-heavy system you could have these tables feed into another structure where the queries are straight-forward and fast. That is, to do the processing up-front and store it in a single table.
祝你好运
这篇关于如何优化由SQLAlchemy生成的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
