What are the common optimization types of mysql

This article mainly explains "what are the common optimization types of mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the common optimization types of mysql".

1. Redefine the order of associated tables.

The association in the data table does not always follow the order specified in the query, and determining the association order is an important function of the optimizer.

2. Convert the outer connection into the inner connection.

Not all outerjoin statements must be executed as external connections. Many factors, such as where conditions and library table structures, may equate external joins with internal joins. MySQL can recognize this and rewrite the query to adjust the association order.

3. Use the equivalent transformation rule.

MySQL can use some equivalent transformations to simplify and standardize expressions. It can merge and reduce some comparisons, and it can also remove some permanent and untenable judgments. For example, (5=5anda > 5) will be rewritten to a > 5. Similarly, if there is (a5andb=canda=5).

4. Optimize count (), min () and max ()

Whether the index and column are empty usually helps MySQL optimize this expression. For example, to find the minimum value of a column, you only need to query the leftmost record of the B-tree index, and MySQL can directly get the first row of records of the index.

Estimate and convert to a constant expression.

5. Overwrite index scan.

When the columns in the index contain the columns needed in all the queries, MySQL can use the index to return the desired data without querying the corresponding data rows.

6. Optimize the subquery.

In some cases, MySQL can convert subqueries into a more efficient form, thereby reducing access to data by multiple queries.

Thank you for your reading, these are the contents of "what are the common optimization types of mysql". After the study of this article, I believe you have a deeper understanding of what the common optimization types of mysql are, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!