Mysql technical documentation–optimization of slow mysql–workflow–step-by-step troubleshooting

Here is a good way to find slow SQL –by. Adan

Prometheus-Monitoring Mysql Advanced Usage (1) (Installation Configuration)_Yidancheng’s Blog-CSDN Blog

Adan:

Once you know the slow SQL statements, you can start troubleshooting and optimizing the SQL according to the optimization steps.

1. Read sql logic

First observe the writing of sql statements and organize the logic of sql.

Separate sql query levels.

2. Use explain to view the execution plan

Optimize according to the parameters in the execution plan:

(1) Optimize according to type

In MySQL, the explain command provides detailed information about the query execution plan, where the type column describes the query’s access method. In the type column, from high to low, common access method levels include:

system: This is the highest level access method. There is usually only one record, indicating that the table has only one row. For example, for operations that query global variables, MySQL may use the system level.
const: This means that the query matches only one row of data in the table, usually due to the use of a primary key or unique index for an exact match.
eq_ref: In join queries, this is one of the most efficient access methods, indicating that the association between tables at join time is based on a unique index.
ref: Indicates using a non-unique index to query and return multiple rows matching a certain value.
fulltext: This means using the full text index for querying.
ref_or_null: Similar to ref, but also includes rows that return NULL values.
index_merge: Indicates using multiple indexes to query and merging the results to obtain the final result.
unique_subquery: This means that a unique index is used in the subquery to query.
index_subquery: Similar to unique_subquery, but uses a non-unique index.
range: Indicates using the index range to query, such as using the BETWEEN or IN operator.
index: This means a full index scan, which traverses the entire index tree.
all: This is the lowest level access method, indicating a full table scan, that is, traversing the entire table.

Please note that these query access methods are not strictly in order from high to low, but are determined based on the characteristics of the query and the optimizer’s decisions. Different queries may use different access methods.

Notice:

At least it must be above the range to meet the conditions for a production environment.

Optimization measures – indexing

Note: Troubleshooting the causes of index failure

A few common reasons indexes may become invalid in MySQL include:

Smaller data: If the amount of data in the table is smaller, indexes may become less efficient because full table scans may be faster than using indexes for small tables.
Improper index selection: Choosing an inappropriate index type or not correctly creating an index suitable for the query may lead to index failure. For example, when a query involves performing functional operations on indexed columns, or using the OR operator to join multiple conditions, the index may not be effectively utilized.
String columns that are too long: If you use a long string column as an index and fail to truncate it appropriately or use a prefix index, it may also cause index failure.
Uneven data distribution: If the data in the table is unevenly distributed, some indexes may less cover the query conditions, causing index failure.
Outdated statistics: MySQL uses statistics to decide how to use indexes. If the statistics are out of date or inaccurate, the optimizer may not be able to correctly select an appropriate index, causing the index to become invalid.
The query uses function operations: When the query uses function operations, such as performing function operations on index columns or using functions as query conditions, the index may not be effectively utilized, resulting in index failure.
Implicit data type conversion: If the condition type in the query does not match the data type of the index column, MySQL will perform implicit data type conversion, causing the index to fail.
Check whether the columns added to the index are involved in the calculation.

In order to avoid index failure, indexes need to be designed and created correctly, and optimized according to the characteristics of the query. In addition, regularly updating statistics and avoiding the use of functional operations or implicit data type conversions can also help improve the efficiency and accuracy of indexes.

Create an index using sql statements

CREATE INDEX index name ON table name (corresponding to the column in the table);

It creates a normal index. In MySQL, you can use the following statements to create different types of indexes:

Normal Index: Created using the CREATE INDEX statement. For example: CREATE INDEX index_name ON table_name(column_name);
Unique Index: Created using the CREATE UNIQUE INDEX statement. For example: CREATE UNIQUE INDEX index_name ON table_name(column_name);
Primary Key Index: Use the ALTER TABLE statement to add a primary key. For example: ALTER TABLE table_name ADD PRIMARY KEY (column_name);
Full-Text Index: Use the ALTER TABLE statement to add a full-text index. For example: ALTER TABLE table_name ADD FULLTEXT(column_name);
Clustered Index: Clustered index is implemented in MySQL through primary key index, and clustered index is automatically created by creating primary key.

CREATE CLUSTERED INDEX idx_clustered_index_name
ON table_name (column1, column2, ...);

For your specific example, CREATE INDEX idx_orders_date ON orders(orderdate); creates a normal index named idx_orders_date for orders The orderdate column on the table.

To create an index, you use the CREATE INDEX statement, specifying the index name, table name, and column names to be indexed. Make sure you consider query needs and the distribution of your data when building indexes to get the best query performance.

Reconstruct sql statement

scene one:

Original:

change:

As shown in the figure, even though the index was created in the previous SQL, the index was still invalid because it participated in the function calculation. So here we use the method of changing the writing of the SQL statement to take out the column.

Also pay attention to using clustered indexes to avoid table backs.

Avoid table return

Use a covering index -> directly bind the columns of the required data to the index when creating the index, so that the corresponding data can be directly found when the pointer is searched to avoid table backing.

In the execution plan, you can see in the possible_keys attribute that there are related indexes, and then you can see the specific index used in the key attribute.

(2) Find problems based on Extra

In EXPLAIN query results, the attribute Extra provides additional details about other relevant information in the execution plan. It can provide information about specific operations or optimization options that occurred during query execution. The Extra attribute is usually used to describe some important details or special situations in the execution plan.

Here are some common Extra attributes and their meanings:

Using index: Indicates that the query uses a covering index, that is, the query can return the required data only through the index without further access to the table itself.
Using where: Indicates that the query uses the WHERE clause for conditional filtering.
Using temporary: Indicates that the query needs to create a temporary table to handle certain operations in the query, such as sorting or grouping.
Using filesort: Indicates that the query needs to sort the results, but the sorting cannot be completed through the index, so a file sorting algorithm needs to be used.
Using join buffer (Block Nested Loop): Indicates that the query uses the join buffer (BNL) algorithm to process the join operation.
Using index condition: Indicates that the query uses index condition push optimization, that is, conditional filtering is performed on the index.

These are just some of the common Extra properties, there are actually other possible property values, depending on the database management system and the complexity of the query. Understanding the Extra attribute can help you analyze a query’s execution plan and identify potential performance issues or optimization opportunities.

Optimization ideas for attribute values:

Based on the information from the Extra attribute, you may consider the following optimizations:

Using index: If the query uses a covering index, this is usually a good sign. Make sure there are appropriate indexes on the corresponding columns and check whether the index coverage meets the query needs.
Using where: The WHERE clause is used for conditional filtering, which helps reduce the size of the returned result set. Make sure there are appropriate indexes on relevant columns to improve the efficiency of conditional filtering.
Using temporary: Creating temporary tables may have a performance impact. Consider optimizing the query or adjusting the query structure, such as using more efficient statements or rewriting the query to avoid the creation of temporary tables.
Using filesort: File sort operations can consume significant computing resources. Try to avoid file sorting by optimizing queries or adding appropriate indexes. Consider adjusting your query’s ORDER BY statement so that you can use the index to complete the sorting operation.
Using join buffer (Block Nested Loop): Uses the join buffer (BNL) algorithm for join operations, but may cause performance degradation for large data sets. In case of poor performance, you can try using other join algorithms or adjust the join conditions in the query.
Using index condition: Index condition push optimization can improve query performance. Make sure the conditions in the query match the columns of the index and that the index is selective.

Additionally, you can consider the following general optimization methods to improve query performance:

Ensure that appropriate indexes exist and create or adjust indexes based on query needs.
Partition or shard the table to reduce the amount of data queried.
Optimize query statements to avoid unnecessary complexity and repeated operations.
Use batch operations or combine multiple queries to reduce the number of database accesses.
Monitor database performance and make adjustments and optimizations as needed.

The best optimization strategy depends on the specific database, table structure and query mode. Therefore, before any optimization is performed, it is recommended to conduct benchmark testing and performance analysis to understand the specific bottlenecks and problems, and then take corresponding optimization measures in a targeted manner.