Wondershare EdrawMind
心智圖資源庫 Slow SQL processing encyclopedia
- 34
Slow SQL processing encyclopedia
MySQL processing encyclopedia ~ all the knowledge you want is here. Slow SQL may cause damage to the smooth operation of services and is the biggest hidden danger that often causes application jitter in daily development.
編輯於2023-10-11 14:05:10
- 推薦給您
- 大綱
Slow SQL Processing Guide
question
Slow SQL may cause damage to the smooth operation of services and is the biggest hidden danger that often causes application jitter in daily development.
Troubleshooting and solving slow SQL
The hidden dangers of slow SQL
Impact on smooth operation of services
Common problems in daily development
Ways to avoid slow SQL
Optimize SQL queries
Split complex SQL
Identify complex SQL
Identify complex structures such as subqueries, related queries, and aggregate functions in SQL
Identify multi-table joins, sorting, grouping and other operations in SQL
Break down complex SQL
Break complex SQL into multiple simple single-table queries
Reduce operations such as multi-table joins, sorting, and grouping
Optimize single table query
Use index
Reduce data volume
Optimize query conditions
Merge single table query results
Use operations such as UNION and UNION ALL to merge single table query results
Avoid using JOIN operations to combine results
Optimize merge results
Reduce the amount of data in merged results
Optimize sorting, grouping and other operations of merged results;
Analyze the problem and use EXPLAIN
Use the EXPLAIN command
View execution plan
Analyze index usage
Check data distribution
avoid problem factors
Complex SQL for historical reasons
Growth in data volume
Ideas to solve slow SQL
Observe SQL
Understand business scenarios
Sort out the relationships
analyse problem
Use the EXPLAIN statement
Analysis indicators (type, possible_keys, key, rows, extra)
Specify plan
Transformed into paging query
Directly obtain the full amount of data and sort it in memory
Subquery changed to related query
Use force_index to specify the index or modify the sorting method
Use LIMIT and paging to avoid full table scans
Optimize situations where data skew is severe
Use ES for querying or limiting paging depth
Optimization principles
Create appropriate indexes
Understand index types
Ordinary index
Suitable for most scenarios
unique index
Applies to unique value fields
composite index
Suitable for multiple field combinations
spatial index
Suitable for spatial data
Full text index
Good for text search
Select index field
Select frequently queried fields
Choose highly distinguishable fields
Choose fields of appropriate length
Evaluate index performance
Use the EXPLAIN command
View execution plan
Analyze index usage
Optimize indexing strategy
Maintain index regularly
Remove redundant indexes
merge index
Adjust index order;
Reduce unnecessary access to columns
Use covering index
What is a covering index
Covering index means that in the query statement, all required data can be obtained from the index without the need to query back to the table.
Advantages: Reduce the number of table query times and improve query efficiency
Disadvantages: It may cause the index to be too large, affecting write performance
How to create a covering index
Choose the appropriate index column
Select frequently used columns in query statements as index columns
Select columns with high distinction as index columns
Create composite index
Combine multiple columns into a composite index
Composite indexes can improve query efficiency and reduce the number of indexes
Things to note when using covering indexes
Avoid functional operations on indexed columns
Function operations may cause index failure
Avoid type conversion on indexed columns
Type conversion may cause index failure
Avoid calculations on indexed columns
Calculation may cause index failure
Avoid fuzzy queries on indexed columns
Fuzzy queries may cause index failure
Avoid sorting on indexed columns
Sorting may cause index failure
Avoid paging on index columns
Pagination may cause index failure
Avoid aggregation on indexed columns
Aggregation may cause index failure;
Statement rewriting
Query statement optimization
Use index
Make sure the query criteria fields are indexed
Avoid using functions or operators, which may cause index failure
Reduce data volume
Use LIMIT to limit the amount of data returned
Filter data using the WHERE clause
Avoid using subqueries
Use JOIN statement instead of subquery
Use the EXISTS clause instead of a subquery
Optimize OR conditions
Use UNION instead of OR condition
Use IN instead of OR conditions
Update statement optimization
Batch update
Using transactions for batch updates
Using stored procedures for batch updates
Reduce lock contention
Avoid holding locks for long periods of time
Lower transaction isolation level
Delete statement optimization
Use LIMIT to limit the amount of data deleted
Use transactions for bulk deletion
Use stored procedures for batch deletion;
Data carry forward
What is data carry forward
Data carryover is the movement of data from one place to another
Reasons for data carry forward
Data volume is too large
Data format is incompatible
Data needs to be processed
How data is carried forward
Copy directly
Export import
Database Connectivity
Optimization of data carryover
Choose the appropriate data carryover method
Direct copying is suitable for small data volumes
Export and import are suitable for large amounts of data
Database connection works with real-time data
Optimize data carryover speed
Increase network bandwidth
Optimize database queries
Optimize data format
Notes on data carryover
Keep your data safe
Use encrypted transmission
Restrict access
Ensure data integrity
Use transaction processing
Back up data regularly;
Select the appropriate column to sort
Avoid using string types for sorting
String type sorting performance is poor
It is recommended to use numeric types for sorting
Avoid using expressions for sorting
Expression sorting performance is poor
It is recommended to use column values for sorting
Avoid using multiple columns for sorting
Poor multi-column sorting performance
It is recommended to use a single column for sorting
Avoid using sort columns for grouping
Sorting column grouping performance is poor
It is recommended to use other columns for grouping;
Proper column redundancy
Column redundancy can reduce the number of table joins
The more connections there are, the slower the query speed will be.
Column redundancy can reduce the number of connections and increase query speed
Column redundancy reduces disk I/O
The more disk I/O times, the slower the query speed.
Column redundancy can reduce disk I/O and increase query speed
Column redundancy can reduce query complexity
The higher the query complexity, the slower the query speed
Column redundancy can reduce query complexity and improve query speed
Column redundancy improves query performance
The better the query performance, the faster the query speed
Column redundancy can improve query performance and increase query speed;
SQL split
Reason for split
Improve query efficiency
Reduce lock contention
Improve maintainability
Split method
split vertically
Split according to business functions
Can be maintained independently after splitting
Reduce coupling
split horizontally
Split by data range
Improve query performance
Reduce data redundancy
Splitting principle
Minimize splits
Avoid over-splitting
Reduce maintenance costs
Maximize utilization of hardware resources
Improve query performance
Reduce hardware costs
Minimize data redundancy
Reduce data inconsistency
Improve data accuracy
Maximize data independence
Reduce data coupling
Improve data maintainability;
Appropriate application of ES