Use examples to tell you how to optimize SQL
Aug 04, 2021 am 09:26 AMAlthough hardware costs have dropped nowadays, improving system performance by upgrading hardware is also a common optimization method. Systems with high real-time requirements still need to be optimized from the SQL aspect. Today we will introduce how to optimize SQL based on examples.
Judge the problem SQL
When you judge whether there is a problem with SQL, you can judge it through two phenomena:
-
System level phenomenon
CPU consumption is serious
IO waiting is serious
The page response time is too long
-
Timeout and other errors appear in the application log
You can use the sar command and the top command to view the current system status. You can also observe the system status through monitoring tools such as Prometheus and Grafana.
- ##SQL statement appearance
- Lengthy
- Execution The time is too long
- Getting data from full table scan
- The rows and cost in the execution plan are very large
- MySQL
- Slow query log
- Test tool loadrunner
- Percona's ptquery and other tools
- Oracle
- AWR report
- Test tool loadrunner etc.
- Related internal views such as v$, $session_wait, etc.
- GRID CONTROL monitoring tool
- Dameng database
- AWR report
- Test tool loadrunner etc.
- Dameng performance monitoring tool (dem)
- Related internal views such as v$, $session_wait, etc.
If there are few indexes, the query will be slow; if there are too many indexes, it will take up a lot of space, and when executing add, delete, or modify statements, The index needs to be maintained dynamically, which affects performance. If the selection rate is high (fewer duplicate values) and is frequently referenced by where, a B-tree index needs to be established; Generally, join columns need to be indexed; it is more efficient to use full-text index for complex document type queries; The establishment of indexes should strike a balance between query and DML performance; when creating composite indexes, attention should be paid to queries based on non-leading columns• Use UNION ALL instead of UNION
UNION ALL has higher execution efficiency than UNION. UNION needs to be deduplicated when executed; UNION needs to sort data• Avoid select * writing
Optimize when executing SQL The processor needs to convert * into specific columns; each query must return the table, and covering indexes cannot be used.• It is recommended to create an index for JOIN fields
Generally, JOIN fields are indexed in advance• Avoid complex SQL statements
Improve readability; avoid the probability of slow queries; can be converted into multiple short queries and processed by the business end• Avoid where 1=1 writing • Avoid order by rand() similar writing style
RAND() causing the data column to be scanned multiple timesSQL optimizationExecution plan
Be sure to read the execution plan before completing SQL optimization. The execution plan will tell you where the efficiency is low and where optimization is needed. Let's take MYSQL as an example to see what the execution plan is. (The execution plan of each database is different and you need to understand it yourself)
Field | Explanation |
---|---|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
|
- The where condition field type in SQL must be consistent with the table structure. The user_id in the table is varchar( 50) type, the actual int type used in SQL, there is implicit conversion, and no index is added. Change the user_id field in tables b and c to int type.
- Because there is an association between table b and table c, create an index on the user_id of table b and table c
- Because there is an association between table a and table b, Create an index on the seller_name field of tables a and b
- Use composite index to eliminate temporary tables and sort
1 2 3 4 5 |
|
- View execution plan explain
- If there is an alarm message, check the alarm message show warnings;
- View the table structure and index information involved in SQL
- Think about possible optimization points according to the execution plan
- Perform table structure changes, add indexes, SQL rewrite and other operations according to possible optimization points
- View the optimized execution time and execution plan
- If the optimization effect is not obvious, repeat the fourth step
mysql tutorial"
The above is the detailed content of Use examples to tell you how to optimize SQL. For more information, please follow other related articles on the PHP Chinese website!

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

How to optimize the performance of SQL Server and MySQL so that they can perform at their best?

How to optimize Discuz forum performance?

Core differences between Sybase and Oracle database management systems

MySql SQL statement execution plan: How to optimize the MySQL query process

How to implement MySQL underlying optimization: common techniques and principles for SQL statement optimization

How to optimize SQL query operations in PHP development
