1) field length and type: the data from the external system must be consistent with the external system to avoid insertion failure or truncation. It is forbidden to use blob,clob,text,image and other large fields. 2) Intermediate table: Many-to-many associations need to add intermediate tables, many-to-one plus associated fields. Association fields to avoid the hassle of data migration, you can use a value that is associated with an actual business instead of a self-growth ID Association. 3) Join query: If there are too many table fields, one or more word tables will be split according to the business. If there are too many associations, you can reduce the table association at query time by redundancy of some required fields in the main table. 4) Index and performance: (subsequent) 5) concurrent access (read/write): When the read operation is much larger than the write operation, the use of middleware to distribute or configure different read and write data sources in the program to achieve read and write separation. The data modifications of the main library are synchronized to the individual slave libraries. In MySQL, for example, the MyISAM engine can be used to improve performance from the library, and the master-slave separation will not only increase the server, but also greatly alleviate the competition of lock and share lock. 6) Concurrent Access (write):
four types of concurrency problemsMissing or overwritten updates: multiple transactions read the same data, and update loss occurs when concurrent updates occur. Dirty read: One transaction read to another uncommitted transaction write data. Non-REPEATABLE READ: A transaction re-reads the previously read data and discovers that it has been modified by another transaction. Phantom read: A transaction re-reads the previously read data and finds that there are many or fewer lines.
The default level for Oracle and SQL Server is read commit (read-committed, RC), and Mysql/innodb defaults to repeatable Read (read-repeatable, RR). The concurrency problems that can be resolved are as follows: ========================================================================
Isolation level Dirty Read (Dirty Read) non-repeatable read (Nonrepeatable Read) Phantom Read (Phantom Read)
========================================================================
Uncommitted reads (READ UNCOMMITTED) may be possible
Read Committed may not be possible
REPEATABLE READ (Repeatable Read) could not possibly
Serializable (Serializable) cannot be impossible
Serialization TransactionsThe simplest strategy is to modify the transaction level to be serializable, so that when a transaction executes, it prevents other transactions from executing. However, this method is not practical, because it makes the operation of the database become single-threaded, and cannot fully share the database resources.
Pessimistic lock ControlWhen a user performs an action that locks on the data until the current transaction releases the lock, the other transaction can perform the operation that conflicts with the lock. Take the MySQL transaction as an example: Set Autocommit=0;begin;select status from T_goods where id=1
For updateThe default is row locks, which are upgraded to table locks over a certain number of rows. After you perform a select ... for update, other select...for update queries for this data are blocked, but the normal select table is not affected.
optimistic lock controlControlled with a timestamp or version number. The user does not lock when reading the data, but the control field is also detected together when querying the data. When the user updates, check that the value of the control field in the database is the same as the value previously obtained in the program. This data is processed, the same data is updated, and the value of the control field is updated with the current timestamp or version number plus 1.
7) Small Business: Avoid long-time lock data, can be large data volume update or delete into batches, a batch is a small transaction. Easy recovery after accidental termination by recording the ordinal of a successful batch. 8) Data deletion: Delete can be tombstoned first, update the value of a tag column indicates that the data is invalid, to achieve the role of record retention. Physical deletion can be done when it is really useless in the future. 9) Extensibility: The core table is reserved for various types of extension fields such as Int,long,char. 10) partition library: daily average data growth of large tables, can be partitioned or even composite partitions. Obsolete data is deleted or archived to the history library. The archive method can be used: periodically request DBA manual, application (such as Java) implementation, deploy Shell script implementation on the server, etc.
10 tips and experiences on database
