How to deal with subquery by optimizer in Mysql
This article is about how to deal with sub-queries by optimizer in Mysql. I think it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.
According to the type and location of the subquery, the mysql optimizer will adopt different processing strategies for the subquery in the query statement, including rewriting to join, rewriting to semi-join and materializing.
Scalar quantum query (Scalar Subquery): the scalar quantum query in the query statement returns only one row of data at a time, which can be optimized and buffered by the optimizer during execution.
In subquery (unique) (IN Subquery (Unique)): the subquery in the query statement returns a unique dataset, so the optimizer can safely convert this subquery to inner join for processing. You should be able to see this scenario happen by executing show warnings after explain.
In subquery (non-unique) (IN Subquery (Non-Unique)): when a subquery in a query statement returns a non-unique dataset, the optimizer cannot safely convert the subquery into an inner join, but can only adopt other processing strategies, such as converting a subquery to a semi-join (Semi-join), while in mysql, the strategy for performing a semi-join includes FirstMatch,Materializelookup,Materializescan,DuplicatesWeedout,Loosescan, etc., and the specific processing details of each strategy You can refer to the relevant documentation, and I won't repeat it here.
NOT in subquery (NOT in subquery): for the NOT in subquery in the query statement, the mysql optimizer can adopt two strategies: materialization and transformation to exists.
Derived Table: the mysql optimizer does not materialize subqueries in the from clause of the query statement. Instead, it is usually merge, similar to the way a view merges its definition with the predicate of a query statement.
CTEs and CTEs and Views: views, as stored queries, can simplify applications and achieve data security, while CTEs is similar to views, except that its life is limited to the relevant query statements themselves. There are two main strategies for mysql optimizer to deal with CTEs and view, that is, merge processing (Merge) and materialization processing (Materialize). For CTEs and view, mysql optimizer first tries to merge them with the main query, and when some scenarios cause them to be unable to merge with the resident query, it will consider solidifying the view or CTEs results through temporary tables for other parts of the main query. At this point, show warings can see the emergence of these scenes after explain.
This is how the optimizer handles subqueries in Mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.
