Database optimization solutions and database solutions

Database optimization solutions and database solutions

This article shares with you the database optimization solution for your reference. The specific content is as follows:

1. Use table partitions
Partitions physically separate data. Data in different partitions can be stored in data files on different disks. In this way, when querying this table, you only need to perform a row scan in the table partition instead of a full table scan, which significantly shortens the query time, in addition, partitions on different disks will also distribute the data in this table to different disk I/O, A well-configured partition can evenly distribute data transmission to disk I/O competition. This method can be used for tables with large data volumes. Table partitions can be automatically created on a monthly basis.

2. Alias usage 
Alias is an application technique for large databases. It means that a single letter is used as an alias for table names and column names. The query speed is 1.5 times faster than that for connecting tables.

3. Index Optimization Design
Indexes can greatly speed up database queries. However, not all tables need to be indexed. You only need to create indexes for tables with large data volumes.
Disadvantages:
1. It takes time to create and maintain indexes. This time increases with the increase of data volume.
2. Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger.
3. When adding, deleting, and modifying data in a table, the index must also be dynamically maintained, reducing the Data Maintenance speed.
Indexes need to be maintained: To maintain system performance, after an index is created, the index page is broken due to frequent operations such as adding, deleting, and modifying data, indexes must be maintained.
4. Materialized View (index view)

The general view is virtual, while the materialized view is a real data area that occupies storage space. In addition, refreshing the materialized view also consumes some resources, however, it gains efficiency and flexibility.
The index view is more suitable for OLAP databases with a large number of reads and fewer updates. It is not suitable for OLTP databases with instant addition, deletion, modification, and query of records). 

Considerations for materialized views: 
1. for complex and high-consumption queries, if frequently used, a materialized view should be built.
2. materialized view is a typical performance optimization method with space for time.
3. Exercise caution when using materialized views for frequently updated tables.
4. select an appropriate refresh method.

Differences between a common view and a Materialized View:
A common view and a materialized view are nothing at all. A common view does not store any data. In a query, it is converted to the corresponding defined SQL for query, materialized views convert data into a table and actually store data. In this way, you do not need to associate a large number of tables to query data, A large number of operations are performed in the temporary tablespace.
Three features of a common view:
1). Simplified Design, convenient and clear Coding. A view does not improve performance. Its existence only reduces performance (for example, we associate two views, one view is associated with six tables, and the other view is associated with seven tables ).
2). SecurityWhen authorized to another user or viewing angle, multiple table associations can only be viewed and cannot be modified.
3. different dimensions can be viewed from different perspectives. A view can divide dimensions and permissions, and make multiple dimensions comprehensive. That is, you can view what you want from different perspectives, A table is only an entity with fewer dimensions.

5. deadlock and blocking 
1) avoid putting data that requires frequent updates in long transactions to avoid chain reactions.
2). It is not a last resort. It is best not to add your own locks in addition to the database lock mechanism.
3) reduce the transaction size and submit the transaction in time.
4) avoid cross-database distributed transactions as much as possible, because of the complexity of the environment, it is easy to cause blocking.
5). Use Bitmap indexes with caution. When updated, deadlock may occur.

6. Reduce IO and network transmission times
1) try to use a small number of database requests to obtain the required data, which can be retrieved at one time regardless of multiple times.
2) For batch operations that frequently operate databases, stored procedures should be used to reduce unnecessary network transmission.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.