Applications are as slow as cattle , There are many reasons , It may be the network , It may be due to the system architecture , It may also be the database .

So how to improve the database SQL What about the execution speed of statements ? Some people would say that performance tuning is a database administrator (DBA) About , However, performance tuning also has a lot to do with programmers .

Lines embedded in a program SQL sentence , If some optimization tips are used , It will achieve twice the result with half the effort .

skill 1  Comparison operators can be used “=” No “<>”

“=” Increases the probability of using the index .

skill 2  Knowing that there is only one query result , Then please use “LIMIT 1”

“LIMIT 1” Full table scanning can be avoided , If you find the corresponding result, you won't continue scanning .

skill 3  Select the appropriate data type for the column

Can use TINYINT No SMALLINT, Can use SMALLINT No INT, You know the truth , The smaller the disk and memory consumption, the better .

skill 4  Will be big DELETE,UPDATE or INSERT The query becomes multiple small queries

Can write dozens of lines , Hundreds of lines SQL Does the sentence seem to be highly qualified ? however , In order to achieve better performance and better data control , You can turn them into multiple small queries .

skill 5  use UNION ALL replace UNION, If the result set allows duplication

because UNION ALL No weight removal , Higher efficiency UNION.

skill 6  Multiple executions to get the same result set , Please keep SQL Consistent statement

The purpose of this is to make full use of the query buffer .

For example, according to region and product id Query product price , Used for the first time :

So the second time the same query , Please keep the consistency of the above statements , For example, don't where In the statement id and region Position exchange sequence .

skill 7  Try to avoid using “SELECT *”

If you do not query all the columns in the table , Try to avoid using SELECT
*, Because it does a full table scan , Index cannot be used effectively , It increases the burden of database server , And the network between it and the application client IO expenses .

skill 8  WHERE The columns in the clause should be indexed as much as possible

just “ as far as possible ” oh , Not all columns . suit one's measures to local conditions , Adjust according to the actual situation , Because sometimes too many indexes will also reduce performance .

skill 9  JOIN The columns in the clause should be indexed as much as possible

Also just “ as far as possible ” oh , Not all columns .

skill 10  ORDER BY The columns are indexed as much as possible

ORDER BY If the column is indexed , The performance will also be better .

skill 11  use LIMIT Implement paging logic

Not only improves performance , At the same time, unnecessary network transmission between database and application is reduced .

skill 12  use EXPLAIN Keyword to view the execution plan

EXPLAIN You can check index usage and scanned rows .

other

SQL There are many tuning methods , There are many different query methods for the same query result .

In fact, the best way is to test in the development environment with the closest real data set and hardware environment , Then publish to the production environment .

Technology
©2019-2020 Toolsou All rights reserved,
C++ of string of compare usage MySQL Basics Commonly used sentence ( Add / delete / modify query )C Language of a Gobang game implementation QT5.9 Use of learning notes QSqlQuery Method of Qt Getting Started tutorial 【 Basic controls 】QCalendarWidget calendar control java Polymorphic array of web Front end signature plug-in _signature_pad Plug in implements electronic signature function centos7 install RabbitMqspringboot use redis Experiment 4 Automated test tools - software test