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

This article will explain in detail how to make SQL faster. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

SQL, structured query language (Structured Query Language), is a special purpose programming language. It is a database query and programming language, which is used to access data and query, update and manage relational database systems.

From contact programming to now has been engaged in database-related work, SQL is my longest use of the programming language, not one of them.

There are many articles about SQL optimization on the Internet, which are very specific and well written, so I won't repeat them here. This article will be combined with the problems encountered in the usual work and experience to explain how to do a good SQL optimization, in which there are mistakes and deficiencies, please correct and supplement.

There are two levels of database optimization, one is SQL optimization, which belongs to business layer optimization; the other is database design, table space planning, cache and other optimization belongs to the management level, we only discuss SQL optimization here.

rise to challenges

Don't be intimidated by inefficient or complex SQL, be brave to accept its challenge, the problem is clearly to optimize the SQL a little faster, there must be more solutions than problems encountered.

The slower it is to run, the more room there is for complex SQL optimization.

Analyze the scene

Data processing can be divided into two categories: online transaction processing OLTP (on-line transaction processing) and online analytical processing OLAP (On-Line Analytical Processing). OLTP is the main application of traditional relational database, which mainly deals with basic and daily transactions, such as bank transactions. OLAP is the main application of data warehouse system, supporting complex analysis operations, focusing on decision support, and providing intuitive and easy-to-understand query results.

OLTP system emphasizes the efficiency of database memory, the command rate of various indicators of memory, binding variables and concurrent operations, which requires high execution efficiency of SQL.

On the other hand, the OLAP system emphasizes data analysis, the timing of SQL execution, disk Icano, partition and so on.

OLTP requires transaction consistency and fast execution. SQL execution time is generally in milliseconds or seconds; OLAP does not require transaction consistency, and SQL execution time is in minutes or hours. Therefore, the methods of SQL optimization are different in different business scenarios.

Locate the SQL optimization point

Database products, parameter configurations and even different releases of the same product will lead to different running efficiency of the same SQL. With the growth of business data, there will be bottlenecks in SQL, which is running well.

No matter how many changes, there are rules to follow. The fundamental problems with the low efficiency of SQL are too many associations of SQL tables, too complex SQL logic, too large amount of table data, not using indexes, and so on. These are the points we want to optimize.

After getting the inefficient SQL, which tables are used in the SQL should be analyzed? Which indexes? What is the amount of data in the table? Have a clear understanding of the basic environment in which the entire SQL runs.

The most commonly used auxiliary tool for optimizing SQL is the "execution plan" provided by the database itself. How to open and use the execution plan analysis and optimize SQL, you can click on the bottom left corner of the article "read the original text" to view a previous article on the "preliminary application of database SQL execution plan".

Perform SQL optimization

After locating the reasons for the inefficiency of SQL execution, to find ways to optimize it, here are some general processing ideas, you can refer to one or two.

Index is a powerful tool for SQL optimization in relational databases. Well-designed indexes and correct application of indexes in SQL can basically solve most of the SQL optimization problems. You can analyze the application of indexes in SQL through the execution plan.

Flexible; the result of 3-6 and 4-5 is 9, the way of doing things is not the only one, one SQL writing method may be very inefficient, but if you try another way of writing, the efficiency will be greatly improved, which requires constant attempt and exploration.

Take Chestnut as an example, take Oracle as an example to find out the sum of the salaries of male and female employees in the company.

Select

(select sum (salary) from employee where sex=' male') Total male salary

(select sum (salary) from employee where sex='') Total female salary

From dual

A better way to write it would be

Select

Sum (case when sex=' male 'then salary else 0 end) Total male salary

Sum (case when sex=' female 'then salary else 0 end) Total female salary

From employee

When I first started SQL, I used the first way to write, the logic is the most simple and clear, but an employee table was scanned twice (in the case of no index), with the increase in the amount of data, this SQL is bound to have inefficient problems, the second way of writing will be optimized a lot, more efficient. This is a simple and can not be more simple flexibility (of course, now, may not have thought of it at that time).

Decompose; a complex SQL can be broken down into several simple SQL step-by-step execution, you may feel that step-by-step execution has done a lot of extra work, but each simple SQL execution will be very fast, improving the overall SQL implementation efficiency.

The most commonly used SQL decomposition is to create an intermediate table, which only stores the required fields and data rows, while adding the necessary indexes, which can greatly improve the execution efficiency of SQL.

Environment; SQL optimization should be carried out in the same environment, SQL execution paths may be different in different environments, and optimization methods are also different. Try to eliminate the influence of environmental factors on SQL optimization.

Experience; experience is important, but avoid falling into empiricism. You may have a lot of experience in SQL optimization in Oracle, but it may not work in DB2 databases, so don't worry about it. There were a lot of problems when I transferred from Oracle database to DB2 database. The experience accumulated in Oracle could not be transferred to DB2, so I had to find another way.

SQL optimization needs experience, but we should not rely too much on experience, and we should constantly adjust our thinking. Each database product has its own characteristics, to understand them, and then to continue to apply. Fortunately, most of the experience is universal, just a little adjustment, not too much pressure.

Summarize the skills and methods of optimizing SQL in peacetime, use and adjust repeatedly in the real SQL optimization scene, and gradually form your own set of optimization experience.

This is the end of the article on "how to make SQL faster". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.