Experience in designing databases _ MySQL

Database design experience: the rationality of database model design will greatly affect the system performance. Based on years of experience in designing and using databases, the author puts forward the following design principles for reference by colleagues. Using Cursor with caution provides a method to scan rows in a specific set. generally, Cursor is used to traverse data rows by row. based on the different database design experiences of data extraction conditions

Whether the database model is properly designed will greatly affect the system performance. Based on years of experience in designing and using databases, the author puts forward the following design principles for reference by colleagues.

Cursor)

A cursor provides a method to scan data row by row in a specific set. generally, a cursor is used to traverse data row by row and perform different operations based on different data conditions. For the multi-table and big table-defined cursors (large data sets) loop, it is easy for the program to enter a long wait or even crash, when the author implements Daily end account rolling accumulation amount and interest calculation in a city's "housing provident fund management system, when a 0.1 million-account cursor is processed, the program enters an indefinite wait period (after calculation, it takes 48 hours to complete) (hardware environment: Alpha/4000 128 mb ram, SCO Unix, Sybase 11.0 ). After the program is modified and the UPDATE statement is used, the process can be completed within 20 minutes. Example:

Declare Mycursor cursor for select count-no from COUNT

Open Mycursor

Fetch Mycursor into @ vcount-no

While (@ sqlstatus = 0)

Begin

If @ vcount-no = ''condition 1

Operation 1

If @ vcount-no = ''condition 2

Operation 2

...

Fetch Mycursor into @ vcount-no

End

...

Change

Update COUNT set operation 1 for condition 1

Update COUNT set operation 2 for condition 2

...

In some cases where a cursor must be used, you can consider transferring qualified data rows to a temporary table and then defining a cursor on the temporary table. This significantly improves the performance. In the background program design of a local "telecom toll system" database, the author performs a cursor operation (hardware environment: PC server, P Ⅱ 266 64 mb ram, Windows NT4.0 ms SQL Server 6.5 ).

Example:

Create # tmp/* define a temporary table */

(Field 1

Field 2

...)

Insert into # tmp select * from TOTAL where condition

Declare Mycursor cursor for select * from # tmp/* define a cursor for a temporary table */

...

Tips for using indexes

You can create an index for two purposes: maintain the uniqueness of the indexed column and provide a policy to quickly access data in the table. Large databases have two types of indexes: cluster indexes and non-cluster indexes. a table without cluster indexes stores data in a heap structure, and all the data is added at the end of the table; A table with a cluster index is physically stored in the order of the cluster index key. a table can only have one cluster index. Therefore, according to the B tree structure, it can be understood that adding any index can increase the query speed by index column, but at the same time it will reduce the performance of insert, update, and delete operations, especially when the Fill Factor (Fill Factor) large. Therefore, when frequent insert, update, and delete operations are performed on tables with many indexes, a small fill factor should be set during table creation and indexing, in this way, we can leave more free space on each data page to reduce page segmentation and re-organization.

Data consistency and integrity

To ensure database consistency and integrity, designers often design too many table associations to minimize data redundancy. Association between tables is a mandatory measure. after a Parent Table and Child Table are created, the insert, update, and delete operations on the Parent Table and Child Table occupy the system overhead, in addition, it is recommended that you do not use the Identify attribute field as the primary key to associate with the sub-table. If data redundancy is low, data integrity is easily guaranteed, but the query operation for inter-table connections is added. Reasonable data redundancy is also necessary to improve the system response time. Rules and constraints are used to prevent data errors caused by incorrect input by system operators, unnecessary rules and constraints also occupy unnecessary overhead of the system. Note that constraints verify data validity faster than rules. In all these cases, the design personnel should consider the system operation type and frequency in a balanced manner during the design phase.

Transaction traps

A transaction is a group of operations completed at a time. Although these operations are a single operation, SQL Server can ensure that all these operations are completed or not done at all. This feature of large databases greatly guarantees data integrity.

As we all know, SQL Server provides implicit transaction control for each independent SQL statement, so that each DML data operation can be completely committed or rolled back, however, SQL Server also provides explicit transaction control statements, such:

Begin transaction starts a TRANSACTION

Commit transaction: submit a TRANSACTION.

Rollback transaction roll back a TRANSACTION

Transactions can be nested. you can use the global variable @ trancount to retrieve the nested transaction processing layers of the connection. Note that each display or implicit transaction adds 1 to the variable, and the commit of each transaction reduces the variable by 1, the rollback of each transaction sets the variable to 0, and the physical data is written to the disk only when the transaction is committed when the variable is 0 (the last commit statement.

Data type selection

The rational selection of data types has a great impact on database performance and operations. There are also many books on this. I will introduce some experiences here:

1. the Identify field should not be associated with other tables as the table's primary key, which will affect the data migration of the table.

2. the Text and Image fields are pointer-type data and are mainly used to store binary large objects (BLOB ). This type of data operations are slower than other data types, so avoid using them.

3. the advantage of the date field is that it is supported by many date functions. Therefore, it is very simple to compare the date size and add or subtract the date. However, functions are also used for query operations based on dates, which is much slower than other data types because functions are used as query conditions, the server cannot use advanced performance policies to optimize queries, but can only scan tables to traverse each row.