Shulou(Shulou.com)06/02 Report--

This article mainly introduces the "summary of SQL optimization methods". In daily operation, I believe many people have doubts about the summary of SQL optimization methods. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "summary of SQL optimization methods". Next, please follow the editor to study!

Summary of sql optimization.

The first is the discovery and analysis of slow sql.

Druid connection pool + log output slow query.

Explain sql is used to analyze slow sql.

The judgment of the null value of a field in the 1.where clause.

The judgment of uncertainty in 2.where sentences.

It doesn't mean (! =,)

Or (replace or with union all)

Like'% str' (only'% str%' will never trigger the index.'% str' can be implemented by reversing the index, which is not supported by mysql temporarily)

In (trade-off between in and exists) (between and can be used if continuous)

The amount of data in the subquery is less than that in the main query, and vice versa. Not in and not exists don't have to think about it, just choose not exists, whose subquery can trigger the index.

The field is manipulated in the 3.where clause. (including operations, function operations, etc.)

4. When an index needs to be created.

The amount of data should be large.

Columns that often appear after where.

There are many kinds of data. It can't be like gender. There are only two kinds.

Read more and write less. Both insert and update update the index.

. . .

Other optimization tools (not because of indexing)

Data redundancy

Varchar replaces char

Use numeric types instead of character types. (character types compare each character, while numeric types need to be compared only once)

Replace select with specific fields *

. . .

Complex logic is solved at the java level.

Take advantage of cach

The above can not be solved can be considered: sub-database sub-table and read-write separation

At this point, the study on the "summary of SQL optimization methods" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.