Use Navicat's Explain Plan function to quickly view SQL execution paths. The key steps include: 1. Click the "Explanation" button before executing the SELECT statement or use the shortcut key Ctrl Shift E; 2. Observe fields such as Id, Select_type, Table, Type, Possible_keys, Key, Rows, and Extra in the execution plan; 3. Focus on type and rows metrics. ALL or index should be avoided. The smaller the rows value, the better; 4. Analyze the Extra field prompts, such as Using filesort or Using temporary, which may affect performance; 5. Take optimization measures based on the problem, such as adding indexes, reducing query fields, optimizing join operations, and rewriting SQL statements.

Analyzing query performance is an important part of database optimization, and the Explain Plan function provided by Navicat can help you quickly see the SQL execution path. The key is to understand the various indicators in the execution plan and find out potential performance bottlenecks.

View basic operations of execution plan

After opening a database connection in Navicat, before executing a SELECT statement, click the "Explanation" button (or use the shortcut key Ctrl Shift E) to see the execution plan of this SQL.

This plan usually displays multiple fields in a tabular form, such as:

• Id : The unique identifier of the operation
• Select_type : query type, such as simple query, subquery, etc.
• Table : The data table involved
• Type : Connection type, such as index, ref, ALL, etc.
• Possible_keys and Key : Indexes that may be used and indexes that are actually used
• Rows : The number of rows that are expected to be scanned
• Extra : Extra information, such as Using filesort, Using temporary, etc.

Together, these fields form a "roadmap" for the query, helping you determine whether SQL is efficient.

Focus on key metrics: type and rows

In the execution plan, the two fields worth paying attention to are type and rows .

• Type represents access type, common ones are:
  • system / const : optimal, indicating direct hitting the primary key or unique index
  • eq_ref : used for primary key or unique index matching when joining multiple tables
  • ref : non-unique index search
  • range : range scan, such as WHERE id > 100
  • index : Scan the entire index tree
  • ALL : Full table scanning should be avoided as much as possible

Generally speaking, as long as it is not ALL or index, it is considered an acceptable type. If you find that type is ALL, it means that the appropriate index is not used, and you need to consider adding or adjusting the index.

• rows indicates the number of rows that MySQL expects to scan. The smaller the value, the better. If you see rows with thousands or even tens of thousands, you need to consider optimization.

For example:
If you query an order table with million-level data, and the execution plan shows that type is ALL and rows is 1000000, it means that it is sweeping the entire table and is very inefficient. At this time, you should check whether you have indexed the query condition field.

Common tips in Extra fields

Extra fields often contain some important clues, such as:

• Using filesort : MySQL requires additional sorting, usually order by does not use index
• Using temporary tables are used, which usually appear in group by or distinct operations, and may also affect performance.
• Using where : means that WHERE conditional filtering is used
• Using index : hit the overlay index, very efficient

The appearance of Using filesort or Using temporary is not necessarily a bad thing, but you should pay attention if you find that they appear in large table operations. This type of operation can be reduced by adding an index to the sorted field, or overwriting SQL.

Actual optimization suggestions

When you discover problems through Navicat's execution plan, you can try the following ways to optimize:

• Add index to fields that are often used as query conditions
• Try to avoid select * and only check the required fields, which helps hit overwrite indexes
• For join operations, make sure that the associated fields have indexes
• Avoid function operations on fields in the where clause, which can cause index failure
• Split complex queries to reduce the amount of data processed at one time

For example:
If you have a like query written like WHERE name LIKE '%張%' , this wildcard starter cannot use the index, which will lead to full table scanning. You can consider whether it can be changed to prefix matching (such as %張) or use full text indexing.

Basically that's it. Although Navicat's execution planning function is simple, combined with these key points, it is enough to cope with most daily query optimization needs.

The above is the detailed content of How to analyze query performance using Navicat's explain plan?. For more information, please follow other related articles on the PHP Chinese website!