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

This article is to share with you what the SQL writing specifications are, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

one。 How to use efficient indexing

1. What is the function of the index?

Give an example to illustrate the role of an index:

The Journey to the West of a book has a total of 1250 pages. Now you want to find the chapter of Sun WuKong vs. Huang Lion Jing. If you turn the book page by page, you may have to turn more than 1000 pages to find the corresponding chapter, but if you find it through the catalogue, it is very fast. It is easy to find Sun WuKong vs. Huang Lion Jing on page 836 in the catalogue. Go directly to page 836 of the book to see all the chapters of Sun WuKong vs. Huang Lion Jing.

A table in a database is like a book, and an index is like a catalog. The catalog of the book stores the corresponding relationship between the chapter title and the page number, while the index stores the data key value and the corresponding position of the row in the table, and the known data position in the table can quickly access the data row. in this way, you can quickly find out the desired data rows in the table through the index.

two。 What kind of requirements are reasonable for indexing?

It was mentioned earlier that we can use the index to retrieve the rows of data in the access table, so it is easy for us to index any field, of course not.

For example:

The company has 30, 000 employees. The employee basic information table EMP in the database includes work number EMP_NO, name EMP_NAME, gender EMP_SEX, department DEPT, entry time WORK_DATE and so on.

Requirement 1: you need to query the details of the employee through the job number or name.

Because the job number is unique and there are not many people with the same name, setting up a separate index on EMP_NO and EMP_NAME will greatly improve the retrieval performance.

Demand 2: inquire about the number of male employees in the company

Because there are only two kinds of gender, it is unreasonable to establish an index. why? Because what is stored in the index is the physical location of the key value and row. If you need to find out the physical locations of all the male key values in the index through the index (searching the index alone consumes a lot of resources), and then returning to the table to find the data corresponding to all rows through these locations, it is very inefficient.

The above mainly involves the concept of index selectivity (selectivity) and the formula for calculating index selectivity:

Index selectivity = cardinality / total rows

Cardinality: number of rows of key values in table

Total number of rows: total number of table rows

According to the meaning formula, the smaller the value, the stronger the selectivity, similar to EMP_NO,EMP_NAME, the selectivity is very strong, and the selectivity of EMP_SEX is very weak.

Based on my experience and a number of experts, it is pointed out that when designing and selecting index fields, it is recommended that the selective value of the index of the field should be less than 100, while in extreme cases it can be up to 10.

3. What are the situations in which an index is not used?

Many times some people say why the SQL is still slow when the reference is established, and you can see that the index is not used through the execution plan. Why didn't you use it? Possible reasons are as follows:

one。 Index fields use functions

For example, the index field serial_number uses the function upper and cannot use the index.

The correct way: if you have to use a function in an index field, you can set up a functional index instead of a normal index

two。 Do not match data types for comparison

For example, if the WROK_ID field is of type VARCHAR2, but SQL is written as WROK_ID=20190507, and the character type is compared with a number to =, DB's query optimizer will do an implicit conversion of to_number (WROK_ID) = 20190507 during parsing, so that no index is needed.

The correct method:

Use WROK_ID='20190507' characters directly for comparison

three。 Fuzzy query like'% xxx%' or'% xxx'

Because the index Btree data structure determines, the query cannot be indexed by adding% like before the retrieval value.

And can only add% like query to the last side of the retrieved value, you can use the index

The correct method:

Avoid like'%xxx%' writing. If you have special requirements, query like'%xxx', please refer to the reverse indexing article:

Http://blog.itpub.net/25583515/viewspace-2146401/

four。 The composite index does not use a leading column

CREATE INDEX SFISM4.GWL_INDEX ON SFISM4.R_STATION_REC_T

(GROUP_NAME, WORK_DATE, LINE_NAME)

Composite index (GROUP_NAME, WORK_DATE, LINE_NAME)

Because the index Btree data structure determines, if the first column GROUP_NAME of the leading column (leading column) is not specified in the query, only the WORK_DATE is written, and the RANGE SCAN of the high performance index is not used when LINE_NAME, and only FULL TAB SCAN or inefficient INDEX SKIP SCAN can be used.

The correct method:

Write full lead column queries or design new indexes

five。 Not equal to query

Unequal queries of! = cannot use indexes

The correct method:

Re-evaluate the business logic and solve it with other workarounds

Six. Null, null

Oracle cannot use indexes when comparing with NULL and'', NOT NULL

(note, like the default definition of MySQL innodb, the null field IS NULL can be indexed, and the NULL of the SQL server nonclustered index can also be indexed.)

The correct method:

Avoid and null comparisons in Oracle

seven。 Unreasonable where condition

For example, the SN_DETAIL table retains data for three years, and now you need to query the data from January 2017 to January 2019, because the optimizer of DB will think that the amount of data is too large and the use of indexes is not as efficient as full table scanning.

The correct method:

Reassess the needs and ask DBA for assistance if necessary

II. SQL, PL/SQL writing specification habits

1 > developers should first write begin..exception..end; to avoid omission.

2 > the code is indented for easy viewing

3 > the code and table field should be annotated to facilitate others to read.

4 > objects such as table and sp define naming prefix suffix rules, C _ R _ I _ SP_ _ T _ I, etc.

5 > objects such as table and sp define the naming length and keep it as short as possible.

6 > the repeated use of values such as 1amp 24060 can give variable reuse, otherwise it will be computed once and it will cause overhead.

7 > rollback can be written in exception to avoid failure during transaction execution and inconsistent business data

8 > optimize business logic to avoid deadlocks, such as updating tab1 in section 1 of SP1, updating tab1 in article 2 update tab2,SP2 in article 2, and updating tab2 in article 2. Avoid the order in which Article 1 updates tab2 and Article 2 updates tab1 in other SP

9 > do not use distinct,group by,order by,having or other operations in SQL to avoid load.

10 > if you can use union all operation, do not use union,union deduplication operation will also carry load.

11 > SQL can write short, so don't be too long. Avoid too many table join. The optimizer may choose the wrong execution plan.

12 > remember to commit,rollback in time after executing the transaction

13 > reduce the commit frequency as much as possible in the same program

14 > when querying SQLServer, try to use nolock to avoid lock contention

15 > CS architecture. The length of Clinet machine name is 15 bits as long as possible, which is convenient for capture and analysis of exceptions. The length of host name intercepted by Oracle11G and previous versions is limited, and too long will not be conducive to catching Clinet machine analysis exceptions.

16 > when the SQL is executed for too long, check the execution plan and adjust it. You can also find senior developers or DBA to assist in analyzing the reasons.

These are the SQL writing specifications, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.