MySQL explain analysis Query

MySQL explain analysis Query

Brief description:

You can use the EXPLAIN or DESC keyword to simulate the optimizer to execute SQL query statements to know how MySQL processes your SQL statements. This helps you analyze the performance bottleneck of your query statement or table structure.

Run the explain command to obtain the following information:

1. Reading sequence of tables

2. Read operation type of the table

3. Which indexes can be used?

4. Which indexes are actually used?

5. Reference between tables

6. How many rows are queried by optimizer in each table

Why use explain:

Explain helps us analyze the select statement and let us know why the query efficiency is low, so as to improve our query and make the query optimizer better work.

How does the MySQL query optimizer work:

The MySQL query optimizer has several goals, but the primary goal is to use the index as much as possible and use the strictest index to eliminate as many data rows as possible.

The final goal is to submit a SELECT statement to search for data rows, rather than exclude data rows.

The reason the optimizer tries to exclude data rows is that the faster it can exclude data rows, the faster it can find data rows that match conditions.

If you can perform the strictest test first, the query can be executed faster.

Limitations of explain:

1. explain won't tell you about triggers, stored procedure information, or the impact of user-defined functions on queries.

2. explain does not consider cache.

# The ideal of life is for ideal life! #

This article permanently updates the link address: