Performance Optimization 3--Database optimization

1> Index

Although the purpose of the index is to improve the performance of the database, there are several situations where you need to avoid using indexes. The following guidelines should be reconsidered when working with indexes:

The index should not be used on smaller tables. Indexes should not be used on tables that have frequent bulk updates or insert operations. The index should not be used on columns that contain a large number of NULL values. Indexes should not be used on columns that are frequently manipulated.

2> transactions

　　For a transaction, it is an atomic execution of the database. Atomic execution provides reliable security for the integrity of the data. In SQLite, if our default transaction (which creates a transaction for each insert and update, and commits the operation immediately after each insert and update), i.e. no transaction is created manually, assuming there are 1000 data at this time, then the execution of the data is to create the transaction, perform the INSERT or update operation- > commits the transaction, so the process executes 1000 times. If we created the transaction manually, the execution process is: Create transaction, execute 1000 SQL data operations, COMMIT transaction. Obviously, the use of transactions in SQLite can lead to great optimizations for insert and update operations. Here's how to use transactions in SQLite:

From what we see, the execution of a transaction is divided into four steps: Start transaction, execute SQL, set transaction execution success, end transaction

Performance Optimization 3--Database optimization