Performance Optimization-database optimization and performance optimization database

Performance Optimization-database optimization and performance optimization database

This article optimizes the performance of databases. The principle applies to most databases, including Sqlite, Mysql, Oracle, and SQL server,The index (advantages and disadvantages, categories, scenarios, rules) and transactions are introduced in detail. Finally, some optimizations for Sqlite are introduced separately.

1. Index

In short, the index is like a book directory, where you can quickly find the page number. The index in the database can help you quickly find the data without scanning the entire table, appropriate indexes can greatly improve the efficiency of database queries.

(1). Advantages

This greatly accelerates database retrieval, including single-table queries, connected table queries, grouped queries, and sorted queries. It is often one to two orders of magnitude of performance improvement, and with the increase of Data orders of magnitude.

(2). Disadvantages

The creation and maintenance of indexes consume, and the indexes occupy physical space and increase as the data volume increases.
Indexes must be maintained when adding, deleting, and modifying databases, which affects the performance of addition, deletion, and modification.

(3). Category

A. directly create indexes and indirectly create indexes

Create directly: use an SQL statement to create a statement. In Android, you can directly create an excuSql statement in onCreate or onUpgrade of SQLiteOpenHelper. The statement is as follows:

1	Create index mycolumn_index ON mytable (myclumn)

Indirect creation: defines the primary key constraint or unique key constraint. You can create an index indirectly. The primary key is a unique index by default.

B. common and unique Indexes

Common Index:

1	Create index mycolumn_index ON mytable (myclumn)

Unique index: ensure that all data in the index column is unique and can be used for clustered indexes and non-clustered indexes. The statement is

1	Create unique coustered index myclumn_cindex ON mytable (mycolumn)

C. single index and Composite Index

Single index: The index creation statement only contains a single field. The preceding example shows how to create a common index and a unique index.
Composite Index: Also called composite index. The index creation statement contains multiple fields at the same time. The statement is as follows:

1	Create index name_index ON username (firstname, lastname)

The firstname column is the leading column.

D. Clustered index and non-clustered index (clustered index and clustered index)

Clustered index: physical index. The physical order is the same as that of the base table. The data values are always sorted in order. The statement is:

1	Create clustered index mycolumn_cindex ON mytable (mycolumn) WITH ALLOW_DUP_ROW

WITH ALLOW_DUP_ROW indicates that clustering indexes WITH repeated records are allowed.

Non-clustered index:

1	Create unclustered index mycolumn_cindex ON mytable (mycolumn)

By default, indexes are non-clustered indexes.

(4) application scenarios

The advantages and disadvantages mentioned above will certainly be a bit clear and confused about when to use the index. The following is a summary:

A. when the data update frequency of a field is low, the query frequency is high, and range queries are frequently performed (>,<,=, >=, <=) or index is recommended when order by or group by occurs. The greater the degree of selection, the more advantageous the index is. Here, the degree of selection refers to the number of unique values in a field/The total number.

B. Multiple columns are frequently accessed at the same time, and each column contains duplicate values. You can consider creating a composite index.

(5). Index usage rules

A. For composite indexes, use the most frequently used column as the leading column (the first field in the index ). If the leading column is not in the query condition during the query, the composite index will not be used.

For example, create unique index PK_GRADE_CLASS on student (grade, class)

Select * from student where class = 2 no index is used

Select * from dept where grade = 3 used Index

B. Avoid calculating index columns. Any COMPUTATION OF where clause columns that cannot be compiled or optimized will cause index failure during query.

Select * from student where tochar (grade) = '2 ′

C. Avoid NULL when comparing values

D. When querying multiple tables, You must select the appropriate table as the internal table. The join conditions must fully consider the tables with indexes and tables with multiple rows. The choice of the internal and external tables can be determined by the formula: Number of matched rows in the outer table * Number of times each query is performed in the inner table, the minimum product is the best solution. Before the actual multi-table operation is executed, the query optimizer lists several possible connection schemes based on the connection conditions and finds the best scheme with the minimum system overhead.

E. query columns in the same order as index Columns

F. Replace the EXISTS clause with multi-table join

G. Put the condition with the maximum number of records to the beginning

H. Be good at using Stored Procedures, which make SQL more flexible and efficient (Sqlite does not support stored procedures: >_< ::)

(6) index test

An index is created. You can use the execution plan to check whether an index is used for an SQL statement.

2. Use transactions

Two benefits of using transactions are atomic commit and better performance.

(1) Atomic commit

Committed in principle means that all modifications in the same transaction are either completed or not done. If a modification fails, the system automatically rolls back to make all modifications invalid.

(2) better performance

By default, Sqlite creates a transaction for each insert or update operation and submits the transaction immediately after each insert or update operation.

In this case, if the data is inserted for 100 consecutive times, the transaction is actually created, the statement is executed, and the commit process is repeated for 100 times. If we show that creating a transaction-> executing 100 statements-> committing will only make the process of creating a transaction and committing it once, this one-time transaction can greatly improve the performance. Especially when the database is on the SD card, the time can be saved by about two orders of magnitude.

Sqlte shows transactions. The sample code is as follows:

public void insertWithOneTransaction() {     SQLiteDatabase db = sqliteOpenHelper.getWritableDatabase();     // Begins a transaction     db.beginTransaction();     try {         // your sqls         for (int i = 0; i < 100; i++) {              db.insert(yourTableName, null, value);         }          // marks the current transaction as successful         db.setTransactionSuccessful();     } catch (Exception e) {         // process it         e.printStackTrace();     } finally {         // end a transaction         db.endTransaction();     }}

SqliteOpenHelper. getWritableDatabase () indicates that the table write permission is obtained.

3. Other optimizations for Sqlite

(1) concatenate statements and use StringBuilder to replace String

This is not much to mention. Simply adding strings will consume performance when multiple temporary objects are created. The pre-allocation performance of StringBuilder space is much better. If you have a general understanding of the length of a string, such as about 100 characters, you can directly specify the initial size in new StringBuilder (128) to reduce the allocation when the space is insufficient.

(2) A Query returns fewer result sets and fewer fields.

Only the fields and result sets are required for query. More result sets consume more time and memory, and more fields consume more memory.

(3) less use of cursor. getColumnIndex

According to the observation in the performance tuning process, the time consumption of cursor. getColumnIndex is almost the same as that of cursor. getInt. When creating a table, you can use static variables to remember the index of a column and directly call the corresponding index instead of each query.

public static final String HTTP_RESPONSE_TABLE_ID = android.provider.BaseColumns._ID;public static final String HTTP_RESPONSE_TABLE_RESPONSE = "response";public List<Object> getData() {      ……      cursor.getString(cursor.getColumnIndex(HTTP_RESPONSE_TABLE_RESPONSE));      ……}

Optimized

public static final String HTTP_RESPONSE_TABLE_ID = android.provider.BaseColumns._ID;public static final String HTTP_RESPONSE_TABLE_RESPONSE = "response";public static final int HTTP_RESPONSE_TABLE_ID_INDEX = 0;public static final int HTTP_RESPONSE_TABLE_URL_INDEX = 1;public List<Object> getData() {     ……     cursor.getString(HTTP_RESPONSE_TABLE_RESPONSE_INDEX);     ……}

4. asynchronous threads

Sqlite is a relational database commonly used in embedded development and is fully open-source.

Unlike common Web databases such as Mysql, Oracle db, and SQL server,Sqlite is an embedded database. The database server is in your program, without network configuration and management. The database server and client run in the same process.Reduces network access consumption and simplifies database management. However, Sqlite has limitations in terms of concurrency, database size, and network, and is a table-Level Lock. Therefore, multithreading is not necessary.

In Android, table queries with a small amount of data may take a small amount of time and will not cause anr. However, when the data volume is larger than ms, the user may feel the delay and lag, which can be run in the thread, however, sqlite has restrictions on concurrency, and Multithreading control is troublesome. In this case, you can use a single thread pool,Execute db operations in the task and interact with the ui thread through handler returned resultsDoes not affect the UI thread, but also prevents exceptions caused by concurrency. For performance optimization, you also need to know how to take measures to protect the source code. For this, consult Mobile Information Security intelligent service provider-love encryption and encryption technology!

You can use the AsyncQueryHandler provided by Android or code similar to the following:

ExecutorService singleThreadExecutor = Executors.newSingleThreadExecutor();singleThreadExecutor.execute(new Runnable() {     @Override     public void run() {         // db operetions, u can use handler to send message after         db.insert(yourTableName, null, value);         handler.sendEmptyMessage(xx);     }});