SQL statement and database optimization, SQL statement database Optimization

SQL statement and database optimization, SQL statement database Optimization
1. Unified SQL statement writing

For the following two SQL statements, the programmer thinks they are the same and the database query optimizer thinks they are different. Therefore, they are encapsulated into reusable methods and controlled using standard templates.

Select * from dual select * From dual is actually case-insensitive. The query analyzer considers it to be two different SQL statements and must be parsed twice. Generate two execution plans

2. Do not write SQL statements too complex.

I often see that two sheets of A4 paper are printed out of an SQL statement captured from the database. Generally, such complex statements are problematic. I took the two-page long SQL statement to consult the original author. As a result, he said that the time was too long and he could not understand it for a moment. As you can imagine, even the original author may be confused about SQL statements, and the database may also be confused. For example, the result of a Select statement is used as an important method to simplify SQL statements by using a temporary table to store intermediate results. However, temporary tables have far more advantages than this. Temporary results are saved to temporary tables, the subsequent query is in tempdb, which can avoid multiple scans of the master table in the program and greatly reduce the blocking of the "Update lock" in program execution, reducing the blocking, improves concurrency performance.

3. Bind the variable

Select * from orderheader where changetime> '2017-10-20 00:00:01'
Select * from orderheader where changetime> '2017-09-22 00:00:01'

 

The query optimizer considers the preceding two statements as different SQL statements and needs to be parsed twice. If you bind a variable

Select * from orderheader where changetime> @ chgtime

4. Pay attention when using like for fuzzy search

Sometimes you need to perform some fuzzy queries, such

Select * from contact where username like '% yue %' keyword % yue %. Because "%" is used before yue, this query must undergo a full table scan unless necessary, otherwise, do not add %, 5 to join Table query before keywords.

(1) Select fields where the clustered index is located as far as possible.

(2) carefully consider the where condition and minimize the number of result sets in tables A and B to obtain springmvc + mybatis + spring. Integrate bootstrap html56, index, and check SQL Performance, mainly depending on the execution plan, there are also cpu and io costs. A simple table is used as an example. First, create a simple table. Generally, a primary key is created first, and the system automatically creates a clustered index with the primary key. A simple rule for determining whether to optimize SQL statements is: to check whether the operations in the execution plan are seek (Search) or scan (scan), the index is required. Use Cases: when a system query is frequent and there are few operations, such as creation and modification, you can create a overwriting index to include all the fields in the query field and where clause, in this way, the query speed is much faster than before, and it also brings about drawbacks, that is, when you create or modify an index, it is slower than when there is no index or no overwriting index is created. Read/write database separation can also solve the problem. If you frequently query the Creator_Id field, create an index. Create index Ix_article_creatorid ON Article (Creator_Id) set statistics io and set statistics are indexed to the Creator_Id field of the table Article. This is the time required to view cpu usage during performance optimization, i/O resource data two important commands remember to add index 7 to the Order by statement. read/write splitting when the primary database performs write operations, the data must be synchronized to the slave database, in this way, the master-slave separation of database integrity can be effectively guaranteed, namely data synchronization or data replication at the database level; from the application layer, it is the separation of requests: adding, deleting, modifying, and requesting the master database, and querying the request from the database 8, try not to use select * from ..... To write the field names select field1, field2 ,... There is nothing to say about this article, mainly for on-demand queries, do not return unnecessary columns and rows. 9 any operation on the column will cause the table to scan, including database functions and calculation expressions, during query, move the operation to the 10 In and or clauses on the right of the equal sign as much as possible to make the index invalid obviously. IN, OR expands the query range. 11 In general, the connection is more efficient than the subquery. When a subquery is required, temporary tables are also used to store intermediate results.