10 skills and experience on databases

10 skills and experience on databases
1) Field Length and type: the data from the external system must be consistent with the external system to avoid insertion failure or truncation. Do not use large fields such as Blob, Clob, Text, and Image. 2) Intermediate table: An intermediate table must be added for multiple-to-multiple join operations. You can add multiple-to-one join fields. To avoid data migration troubles, you do not need to increase the ID Association, but use the actual business associated value. 3) join query: if there are too many table fields, one or more Word tables are split according to the business. If there are too many associations, You Can redundant fields in the master table to reduce the table association during query. 4) index and performance: (subsequent) 5) concurrent access (read/write): When the read operation is much larger than the write operation, use middleware distribution or configure different read/write data sources in the program to implement read/write splitting. The data modification of the master database is synchronized to each slave database. Taking MySQL as an example, the slave database can use the MyISAM engine to improve performance. After the master-slave separation, not only the server is increased, but also the competition for exclusive and shared locks is greatly reduced. 6) concurrent access (write ):Four concurrency ProblemsLoss or overwrite update: when multiple transactions read the same data, the update loss occurs during concurrent updates. Dirty read: one transaction reads data written by another uncommitted transaction. Repeatable read: A transaction reads the data it has read and finds that it has been modified by another transaction. Phantom read: A transaction reads the data read earlier and finds that there are several more or fewer rows. By default, Oracle and SQLServer are Read-Committed, RC, and MySQL/InnoDB are Read-Repeatable and RR ). The concurrency issues can be solved as follows: ========================================================== ======================================
Isolation level Dirty Read (Dirty Read) Non-repeated Read (NonRepeatable Read) Phantom Read (Phantom Read)
========================================================== ======================================
Uncommitted Read (Read uncommitted) may be
Read committed is impossible.
Repeatable read is impossible
Serializable cannot be impossible
Serialized transactionsThe simplest policy is to modify the transaction level to serializable, so that other transactions are prohibited during execution of a transaction. But this method is actually not feasible, because it changes the database operations of the program to a single thread, and the database resources cannot be fully shared.Pessimistic lock ControlWhen the user executes an operation, the data is locked until the current transaction releases the lock, other transactions can execute operations that conflict with the lock. Take MySQL transactions as an example: set autocommit = 0; begin; select status from t_goods where id = 1For update... Commit; The row lock is used by default. If the number of rows exceeds a certain number, the table lock is upgraded. After you execute select... for update, other select... for update queries on this data will be blocked, but normal select tables will not be affected.Optimistic lock ControlUse the timestamp or version number for control. The user is not locked when reading data, but the control fields are also checked when querying data. When updating, check whether the Field Values in the database are the same as those obtained in the program. Otherwise, the data is processed. If the data is the same, the data is updated and the value of the update control field is the current timestamp or version number plus 1. 7) small transactions: To avoid locking data for a long time, you can update or delete large data volumes into batches. One batch is a small transaction. Records the serial numbers of successful batches to facilitate recovery after unexpected termination. 8) data deletion: You can delete the data logically. Updating the value of a tag column indicates that the data is invalid and the data is retained. It can be physically deleted if it is not available in the future. 9) Scalability: the Core table reserves Int, Long, Char, and other types of extended fields. 10) Partitioning and database sharding: A table with an increasing daily data volume can be partitioned or even composite partitions. The expired data is deleted or archived to the historical database. Archive can be implemented by regularly requesting DBA manual, implementation in applications (such as Java), and deployment of Shell scripts on servers.