Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the method of optimizing SQL in the database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Determine the problem SQL

When judging whether there is a problem with SQL, you can judge by two appearances:

1. System-level representation

Serious CPU consumption

IO waiting is serious

The page response time is too long.

Errors such as timeout occurred in the applied log

You can use the sar command, the top command to view the current system status.

You can also observe the status of the system through monitoring tools such as Prometheus and Grafana.

2. SQL statement representation

Tedious

The execution time is too long

Get data from a full table scan

The rows and cost in the implementation plan are very large.

Long SQL is easy to understand, a SQL too long reading will certainly be poor, and the frequency of problems will certainly be higher. To further determine the SQL problem, you need to start with the execution plan, as follows:

The execution plan tells us that this query has gone the full table scan Type=ALL,rows is very large (9950400) it can basically be judged that this is a "flavor" SQL.

Get question SQL

Different databases have different acquisition methods. The following are the current slow query SQL acquisition tools for mainstream databases

MySQL

Slow query log

Test tool loadrunner

Percona's ptquery and other tools

Oracle

AWR report

Testing tools loadrunner, etc.

Related internal views such as v$, $session_wait, etc.

GRID CONTROL monitoring tool

Dameng database

AWR report

Testing tools loadrunner, etc.

Damon performance Monitoring tool (dem)

Related internal views such as v$, $session_wait, etc.

Writing skills of SQL

There are several common techniques for writing SQL:

Rational use of index

The index is less and the query is slow; the index takes up a lot of space, so it is necessary to dynamically maintain the index when executing the add, delete and modify statements, which affects the performance and has a high selection rate (less repeated values) and is frequently referenced by where and needs to build a B-tree index.

General join columns need to be indexed; full-text indexing for complex document types is more efficient; index establishment 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

The execution efficiency of UNION ALL is higher than that of UNION, and the weight of UNION needs to be sorted; UNION needs to sort the data.

Avoid select * writing

When executing SQL, the optimizer needs to convert * to a specific column; each query returns to the table and cannot overwrite the index.

The JOIN field is recommended to be indexed

Generally speaking, 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 side

Avoid writing where 1. 1

Avoid similar writing of order by rand ()

RAND () causes the data column to be scanned multiple times

SQL optimization

Carry out the plan

To complete SQL optimization, be sure to read the execution plan first, which will tell you where it is inefficient and where optimization is needed. Let's take MYSQL as an example to see what the execution plan is. (each database has a different execution plan, so you need to know it by yourself.) explain sql

Next, we use an actual optimization case to illustrate the process and skills of SQL optimization.

Optimization case

Table structure

Three tables are associated to query the current user's order status for 10 hours before and after the current time, and arranged in ascending order according to the order creation time. The specific SQL is as follows

View amount of data

Original execution time

Original execution plan

Preliminary optimization idea

The where condition field type in SQL should be consistent with the table structure. The user_id in the table is varchar (50). The int type used in the actual SQL has implicit conversion and no index has been added. Change the user_id field of tables b and c to type int.

Because there is an association between table b and table c, user_id the tables b and c to create an index

Because there is an association between table an and table b, the seller_name fields of tables an and b are indexed

Using composite indexes to eliminate temporary tables and sorting

Preliminary optimization of SQL

View the execution time after optimization

View the execution plan after optimization

View warnings information

Continue to optimize alter table a modify "gmt_create" datetime DEFAULT NULL

View execution time

View the execution plan

This is the end of the content of "what is the method of optimizing SQL in the database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.