Summary of improving database concurrency Performance

Table of contents

• 1. fine-grained lock table
• 2 split tables
  • 2.1 physical splitting
  • 2.2 logical splitting
• 3. multiple data sources (multiple databases)
• 4 optimistic lock
• 5. Temporary table
• 6 Cache
• 7. Database Cluster
• 8. read/write splitting Policy
• 9 SQL Optimization
• 10 correct indexing

1. fine-grained lock table

1. Reduce large transaction operations (a large transaction operation that contains many SQL statements and involves many tables also locks more resources );
2. Do not use table-level exclusive locks, but use row-level locks. Because the locks are generally automatically allocated by the database based on your SQL statements, pay attention to the SQL statement. For example

   select * into B from A

   This operation creates table B and adds a table lock to table B.

2 split tables

Similar to reducing the lock granularity.

2.1 physical splitting

1. Horizontal Split: separate different row records into different tables. For example, a male is placed in one table, and a female is placed in another table.
2. Vertical Split: place different columns into different tables. For example, the primary user information is put into one table, and the additional information is put into another table.
3. Hybrid split (horizontal split + Vertical Split)

2.2 logical splitting

1. Split by range. For example, all records with values between 70000 and 79999 In the zipcode column are split into a table.
2. Group splitting. For example, the records of the Nordic countries such as Iceland, Norway, Sweden, and Denmark are split into a table.
3. Hash Splitting: see database hash design: http://hi.baidu.com/dapplehou/blog/item/471a277ff8eafc0529388ac4.html
4. Hybrid Splitting: A combination of the preceding splitting methods.
5. You can create a search table to register, query, and locate the split sub-tables.

3. multiple data sources (multiple databases)

The essence is database splitting. Similar to table sharding. Database routing (positioning) is a data source routing (searching and locating) for programmers. multiple data sources need to be defined and appropriate algorithms are used for locating. See
Http://hi.baidu.com/dapplehou/blog/item/6ba8034f5114e038aec3abdb.html multi-database section.

What is an appropriate algorithm? It depends on the database splitting method, performance requirements, and business needs.

4 optimistic lock

It can reduce the length of the database. You can only check whether the submitted data has been modified by others at the moment of submission. If yes, the data to be submitted is considered as expired data and the submission is canceled. Otherwise, the submission is successful. Avoid waiting for long things. The version number is usually used to determine whether data has expired. The process is as follows:

1. A and B both read the same bank account record at the same time. The record version is 1.
2. A. modify this record: Add RMB 100, and Add 1 to the version number.
3. B. modify this record: reduce the value by 50 RMB, and increase the version number by 1.
4. A submit. Assume that the SQL statement is submitted as follows:

   update account set money=money+50,version=version+1 where id=10 and version=1

   Submitted successfully. The version of the record with ID = 10 is 2.

5. B. Submit. The SQL statement is as follows:

   update account set money=money-50,version=version+1 where id=10 and version=1

   Because version is equal to 2, this update statement will affect 0 rows. In this way, whoever submits the job first and who succeeds. Later users will not wait because they are not submitted after reading the data first. This reduces the number of people standing in the moukeng. Improves concurrency performance.

5. Temporary table

Use local temporary tables to improve concurrency performance. Local temporary tables feature that multi-user operations do not interfere with each other. A local temporary table is visible and valid only for one session. Multi-User concurrency is actually a temporary table for each operation, without the problem of data table locks. Improve concurrency efficiency.

SQL Server temporary table creation statement:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)

ORACLE:

CREATE GLOBAL TEMPORARY TABLE MyTempTable

Place part of the data in the primary table (frequently used and with a large amount of data) into a local temporary table, and perform calculation and statistics in the temporary table, and then synchronize it to the master table (the optimistic lock mechanism can be used in the synchronization process), reducing the overhead of the master table lock wait during concurrency.

The Performance Improvement Principle of a temporary table is the same as that of a sharding table: a large set is split into small sets, and then computation is performed on the small set.

6 Cache

1. The principle is the same as that of the temporary table. First, toss (computing, update, etc.) in the memory, and then synchronize the results to the dB at a time (optimistic lock mechanism can be used in the synchronization process ).
2. Algorithm optimization.

   Some operations, although there is no update process, but frequent complex queries can also reduce database performance. In this case, you can save some data that is not easily changed after the query results in the cache for future use.

   For example, the menu display after different users log on may be determined based on the complex logic of different departments, different permissions, delegation, and part-time jobs. database queries may consume a lot of performance, in this case, you can cache all menus to form a menu pool, and then use the hengyuan mode to cache visible menus for each user, which reduces the number of database queries, this avoids the performance loss of the database to be queried every time the menu is displayed.

   Henyuan mode see: http://hi.baidu.com/dapplehou/blog/item/aeca1d958268e1047bf48089.html

7. Database Cluster

Different from multi-database Splitting: the cluster has the same data for each database, while the split database only contains part of the complete data for each database.

Multi-database redundancy can reduce the load of a single database and improve the overall performance. It has three key technical points:

1. Database query routing
2. Database Synchronization
3. Load Balancing Algorithm

8. read/write splitting Policy

Read/write splitting policies can be used in large applications with far more read operations than write operations. A read database is called a slave database, and a write or transactional query is called a master database. Data changes in the primary database are synchronized to the slave database through certain policies (such as database replication) to ensure data consistency between the master and slave databases. In essence, it refers to changing the space for time. That is, by increasing database redundancy, the complex scheduling of database locks is reduced. On the other hand, it increases the complexity of the application architecture (which can be reduced by using third-party middleware ).

9 SQL Optimization

SQL statements are the main source of database resource consumption, which can be further optimized. SQL optimization has a low cost of time and risk.

The SQL statements we write are automatically optimized by the query optimizer. The optimization principle is as follows:

1. Select Operation first. (That is, filtering record sets by conditions first) 2. Projection and selection operations are performed simultaneously. 3. Merge cartesian products and subsequent selection operations into Join Operations 4. perform projection operations and other operations at the same time 5. Search for Common subexpressions and store the results 6. pre-process the files

With the above optimization, the writing efficiency of the following two SQL statements is the same

select * from A,B where A.id=B.id and A.name='Jake'select * from A,B were A.name='Jake' and A.id=B.id

With the evolution of databases, statements with low efficiency are not necessarily low now and need to be tested.

10 correct indexing

1. Index Column
   1. Columns that are frequently queried
   2. Columns sorted frequently
   3. Frequently connected Columns
2. Non-index Column
   1. Infrequently used Columns
   2. Record too many columns (such as gender)
   3. Columns of the image text type

Finished!

If you have any questions, please join the group number for discussion: 173711587