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

This article shows you what SQL development suggestions are, which are concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

MYSQL development recommendations

About creating tabl

1. Try to use INNODB storage engine.

2. It is recommended to use UNSIGNED to store non-negative values.

3. It is recommended to use INT UNSIGNED to store IPV4.

4. It is strongly recommended to use TINYINT instead of the ENUM type.

Use VARBINARY to store case-sensitive variable-length strings or binary content.

7. Distinguish between DATETIME and TIMESTAMP. The storage year uses the YEAR type. The storage date uses the DATE type. The TIMESTAMP type is recommended for storage time (accurate to seconds).

8. Split the fields with large fields and low access frequency into separate tables for storage, and separate hot and cold data.

9. It is forbidden to store plaintext passwords in database tables.

10. The table must have a primary key, and it is recommended to use the UNSIGNED self-incrementing column as the primary key.

11. The table character set uses UTF8, and if necessary, you can apply for the UTF8MB4 character set.

A) the UTF8 character set takes 3 bytes to store Chinese characters and one byte to store English characters.

B) UTF8 is unified and universal, and there is no risk of garbled code in transcoding.

C) if you encounter the storage needs of emoticons such as EMOJ, you can apply to use the UTF8MB4 character set.

12. Adopt the appropriate strategy of database and table. For example, ten thousand libraries and ten tables, ten hundred tables and so on.

About Index

1. Redundant indexes are prohibited.

2. Duplicate indexing is prohibited.

3. Do not index on low cardinality columns, such as "gender".

4. Use the overlay index reasonably to reduce IO and avoid sorting.

About SQL

1. No matter what the database isolation level is or the size of the transaction, form the habit of COMMIT to avoid the long-term holding of transaction locks.

2. Update (update) sql statements to use primary key conditions as much as possible

3. Replace OR with IN. The IN in the SQL statement should not contain too many values.

4. Replace UNION with UNION ALL. UNION ALL does not need to sort the result set again.

5. Try not to use order by rand ().

6. It is recommended to use a reasonable paging method to improve the efficiency of paging.

7. SELECT only gets the necessary fields and uses SELECT * as little as possible.

8. Functions in SQL to avoid uncertain results such as now (), rand (), sysdate (), current_user (), etc.

9. Reduce the number of interactions with the database and use batch SQL statements as much as possible.

Use the following statement to reduce the number of interactions with db:

A) INSERT... ON DUPLICATE KEY UPDATE

B) REPLACE INTO

C) INSERT IGNORE

D) INSERT INTO VALUES ()

10. Split a complex SQL into multiple small SQL to avoid large transactions.

11. Multiple alter operations on the same table must be merged into one operation.

Oracle development recommendations

Points to pay attention to when using an index:

1. Avoid using NOT on index columns.

2. Avoid using calculations on index columns.

Inefficient: SELECT … FROM DEPT WHERE SAL * 12 > 25000

Efficient: SELECT … FROM DEPT WHERE SAL > 25000 Universe 12

3. Avoid using IS NULL and IS NOT NULL on index columns

Inefficient: (index invalidation) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL

Efficient: (index is valid) SELECT … FROM DEPARTMENT WHERE DEPT_CODE > = 0

4. Avoid changing the type of index column.

About SQL

1. Replace DISTINCT with EXISTS:

(inefficient): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D, EMP E

WHERE D.DEPT_NO = E.DEPT_NO

And E.sex = man

(efficient): SELECT DEPT_NO,DEPT_NAME FROM DEPT D

WHERE EXISTS

(SELECT'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO

And E.sex = man

);

2. Replace OR with (UNION) UNION ALL (for index columns)

Efficient: SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALL

SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE REGION = "MELBOURNE"

Inefficient: SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = "MELBOURNE"

Replace UNION with UNION-ALL (if possible).

4. Add the Order By statement to the index column, preferably on the primary key competition.

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE (inefficient)

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE (efficient)

5. Avoid resource-consuming operations:

The SQL statement with DISTINCT,UNION,MINUS,INTERSECT starts the SQL engine to perform the resource-consuming sort (SORT) function.

6. Use Where instead of Having (if possible)

Inefficient:

SELECT JOB, AVG (SAL)

FROM EMP GROUP JOB HAVING JOB = 'PRESIDENT'AND AVG (SAL) > XXX

Efficient:

SELECT JOB, AVG (SAL)

FROM EMP

WHERE JOB = 'PRESIDENT'

OR JOB = 'MANAGER' GROUP JOB Having AND AVG (SAL) > XXX

7. in general, if the statement can avoid the use of subqueries, try not to use subqueries. Because subqueries are quite expensive. The specific example is in the later case, "an optimization process of SQL".

Pay attention to the connection order in the WHERE clause. Choose the driver table reasonably.

9. Avoid using * in the SELECT clause. During the parsing process, ORACLE converts'*'to all column names in turn, which is done by querying the data dictionary, which means it will take more time.

What are the above development suggestions for SQL? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to 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.