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

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. So it's encapsulated into multiplexing methods, controlled by standard templates.

select*from dual

select*From dual

In fact, the case is different, the query analyzer considers it to be two different SQL statements, which must be parsed twice. Generate 2 execution plans

2, do not write SQL statements too complex

I often see a SQL statement captured from a database printed out to be two A4 sheets long. Generally speaking, such complicated sentences are usually problematic. I took this 2-page SQL statement to consult the original author, and he said it took too long, and he couldn't understand it for a while. It is conceivable that even the original author may be confused by SQL statements, and the database will also be confused.

For example, the result of the Select statement is a subset

An important way to simplify SQL statements is to use temporary tables to temporarily store intermediate results. However, the benefits of temporary tables are far more than these. Temporary results are temporarily stored in temporary tables, and the queries behind are in tempdb. This can avoid scanning the main table many times in the program. It also greatly reduces the "shared lock" blocking "update lock" in program execution, reduces blocking, and improves concurrency performance.

3, must use binding variables

select*from orderheader where changetime >'2010-10-20 00:00:01'

select*from orderheader where changetime >'2010-09-22 00:00:01'

The query optimizer considers the above two statements to be different SQL statements and needs to parse them twice. If you use binding variables

select*from orderheader where changetime >@chgtime

4. Attention should be paid when using like for fuzzy query.

Sometimes you need to do some fuzzy queries, like

select*from contact where username like '%yue%'

Keyword %yue%. Because "%" is used before yue, the query must scan the whole table. Unless necessary, do not add % before the keyword.

5. Associated table query

(1)Join Fields Try to select the field where the clustered index is located

(2)Carefully consider where conditions and minimize the result sets of tables A and B

Get download address springmvc+mybatis+spring integration bootstrap html5

6, index,

Look at sql performance, mainly depends on the execution plan, as well as cpu costs, io costs, etc. Here is an example of a simple table.

First of all, create a simple table, generally will first create a primary key, the system automatically to the primary key to create a clustered index.

A simple rule to determine whether SQL optimization is needed is to look at whether the action in the execution plan is seek or scan.

If it's scan, index it.

Usage scenario:

When a system is queried frequently and new or modified operations are rare, an overlay index can be created to include all the fields in the query field and where clause. This query speed will be much faster than before, and it will also bring disadvantages. When creating or modifying operations, it will be slower than when there is no index or no overlay index is established. Reading and writing database separation can also solve the problem

Often query the Creator_Id field and make an index.

Indexing Creator_Id field of Table Article

CREATE INDEX Ix_article_creatorid ON Article(Creator_Id)

set statistics io and set statistics, these are two important commands for viewing CPU usage time and IO resource data during performance tuning.

Remember the Order by statement plus index

7. Separation of reading and writing

When the master database is written, the data must be synchronized to the slave database, so as to effectively ensure the integrity of the database.

Master-slave separation, which means data synchronization or data replication at the database level; separation of requests at the application level: adding, deleting and modifying request master database, querying request slave database

8, try not to select * from …

, and write the field name select field1,field2,…This is nothing to say, mainly on-demand query, do not return unnecessary columns and rows.

9 Any column manipulation will result in a table scan,

It includes database functions, calculation expressions, etc. When querying, move the operation to the right of the equal sign as much as possible.

10 In, or clauses often invalidate indexes

Obviously, IN,OR expands the scope of the query.

Joins are generally more efficient than subqueries

Inevitably, when subqueries are needed, temporary tables are also used to temporarily store intermediate results.

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.