Good habits of database design and use, good habits of Database Design

Good habits of database design and use, good habits of Database Design

Good habits need to be constantly cultivated and strengthened until they become a habit and then part of their thoughts.

Before or after work, I want to read "three character classics" or "Di Zigui" for my little guy whenever I have time. He may not understand the content of these texts, but I hope that this knowledge will grow up with him and become part of his thoughts and actions.

We also have some basic principles when designing the system architecture and Database Planning.

It doesn't matter if you don't know. You can learn more.

It doesn't matter if you don't understand it. Just read it several times.

Perseverance is life.

 

The common habits of databases are recorded.

Design tables and Indexes

1. selecting an appropriate small field can reduce the size of row data in the database and improve the efficiency of index matching, thereby improving the database performance. For example, if date is used to replace datetime, type, or tag, and tinyint is used to replace smallint and int, and fixed-length field instead of non-fixed-length field (such as char instead of varchar2), the data row size can be reduced more or less, increase the hit rate of the database buffer pool;

 

2. Primary keys. The selection of primary keys will have a great impact on the stability and efficiency of table indexes. Generally, we recommend that you consider the unique value that the database auto-increment or self-maintained.

 

3. Some data should be properly redundant, so that although the table design is uncomfortable, it can improve a lot of performance;

 

4. if the business content is large, do not place all fields in a table. In this way, the tablespace will be large and the query speed will be slow. You must specify "which fields are commonly used and which fields are not commonly used ", try to place uncommon fields in the appendix and associate them with the master table one-to-one.

 

5. Create indexes for highly separated fields, such as the phone number field in the User table. The indexing efficiency is very high;

 

6. In principle, bitmap indexes are more effective than normal indexes for fields with few basic changes such as type and status. However, to create Bitmap indexes, you must verify whether the efficiency is improved;

 

7. Do not rely too much on index creation. Generally, creating an index will increase the query speed, but too many indexes will affect input writing.

 

Table usage habits

1. Just as we can write JAVA programs and use the Apache Commons toolkit more, using the internal functions provided by Oracle can not only improve development efficiency, but also improve SQL statement execution efficiency;

 

2. Try to write the SQL statement dm =? And then assign a value, instead of directly setting dm = 9. In this way, oracle will compile this statement every time it executes, and oracle will compile this statement only once in the previous method.

 

3. The efficiency of Exists is higher than that of in. When you select an IN or EXIST operation, consider the data size of the primary table and sub-table.

3.

4. Not in is recommended not to appear in the code, and the efficiency is Not high. If you must use it, replace it with minus or not exists.

4.

5. In the where condition, try not to use to_char, to_date, upper and other methods for format conversion. The conversion and calculation operations should be placed on the right as far as possible. If the conversion is performed on the left, full table scanning will be performed. If the index is not function-based, then, when a function is used for the index column in The Where clause, the index no longer works.

 

6. Do not use a statement to implement complicated logic. If the execution of such complicated statement is not satisfactory, you can split it into simple statements to see if the efficiency is improved.

 

7. Try not to use "<>" or "! = "Operator. Processing Non-equals operators will cause full table scanning, which can be replaced by "<" or ">. For example, change a <> 0 to a> 0 or a <0, a <> ''To a>'', and replace "> =" with "> = ".

 

8. If the Where clause is null or is not null, Oracle will stop using the index and perform a full table scan. You can set the index column to NOTNULL when designing a table. In this way, other operations can be used to replace NULL operations.

8.

9. When the wildcard "%" or "_" is the first character of the query string, the index is not used. Therefore, it is generally not used as the first character. Or try not to use this method;

 

10. For the connected column "|", the index of the last connected column is invalid. Avoid connections whenever possible. Separate connections or use functions that do not work on columns.

10.

11. When comparing columns of different data types, the index will become invalid.

 

12. The UNION operator filters the results to eliminate duplicates. If the data volume is large, disk sorting may occur. If you do not need to delete duplicate records, use union all.

12.

13. Oracle processes multiple query conditions in the Where clause from bottom to top. Therefore, the table join statement should be written before other Where conditions. The conditions for filtering the maximum number of records must be written at the end of the Where clause. Oracle processes the table names in the From clause From right to left. Therefore, when the From clause contains multiple tables, the table with the least records is placed at the end. Oracle10g and later versions, as well as the optimization of this problem, but this habit is still good and worth retaining.

 

14. Non-index columns in the OrderBy statement will reduce performance and can be processed by adding indexes. If the sorting data volume is large and has a significant impact on performance, avoid using it whenever possible. The unique index is a good compromise;