Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to talk about database optimization scheme

2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to talk about the database optimization scheme, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

The following is an analysis of the database optimization scheme, the specific contents are as follows

1. Using table partitioning

Partitions physically separate data, and data from different partitions can be stored in data files on different disks. In this way, when querying this table, it only needs to be scanned in the table partition, but not the whole table scan, which obviously shortens the query time. In addition, the partitions on different disks will also distribute the data transmission of this table on different disks IMagazo, and a carefully set partition can evenly disperse the data transfer to disk Ipool O. This method can be used for time tables with a large amount of data. Table partition can be created automatically on a monthly basis.

two。 The use of aliases

Aliases are the application skills of large databases, that is, table names and column names are aliased with one letter in the query, and the query speed is 1.5 times faster than building a join table.

3. Optimal Design of Index Index

The index can greatly speed up the query speed of the database. But not all tables need to be indexed, just index tables with large amounts of data.

Disadvantages:

1. It takes time to create and maintain an index, which increases as the amount of data increases.

two。 The index needs to take up the physical space, in addition to the data table occupies the data space, each index also takes up a certain amount of physical space, if you want to establish a clustered index, then the space required will be more.

3. When the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance. The index needs to be maintained: in order to maintain the system performance, after the index is created, the index page is fragmented due to frequent data addition, deletion, modification and other operations, so the index must be maintained.

4. Materialized view (indexed view)

The general view is virtual, while the materialized view is a real data area, which needs to occupy the storage space. In addition, the system also needs to spend some resources to refresh the materialized view, but it gains efficiency and flexibility. Index view is more suitable for use in OLAP (read more, update less) database, but not in OLTP (record instant add, delete, change, query) database.

Considerations for materialized views:

1. For complex and expensive queries, materialized views should be built if they are used frequently.

two。 Materialized view is a typical way of performance optimization by exchanging space for time.

3. Be careful with materialized views for tables that are updated frequently.

4. Choose the appropriate refresh method.

The difference between ordinary view and materialized view: ordinary view and materialized view are not the same thing at all. Ordinary view does not store any data, and in the query, it is converted to the corresponding defined SQL to query, while materialized view converts the data into a table, which actually stores data, so that querying data does not have to be associated with a large number of tables. If the table is very large, it will do a lot of operations in the temporary tablespace.

There are three characteristics of normal view:

1)。 Simplified design, convenient, clear coding. Views do not improve performance, their existence will only degrade performance (for example, we associate two views, one view associates 6 tables, and the other view associates 7 tables).

2)。 Security, in the authorization to other users or view point of view, multiple table associations are only allowed to view, not allowed to modify.

3. Look at different dimensions from different angles, the view can divide the dimensions and permissions, and make the synthesis of multiple dimensions, that is, what you want can be seen from different angles, while the table is an entity, generally less dimensions.

5. Deadlock and blocking

1)。 For data that needs to be updated frequently, try to avoid putting it in long transactions, so as not to cause a chain reaction.

2)。 If you don't have to, it's best not to add your own locks to the database locking mechanism.

3)。 Reduce the transaction size and commit the transaction in time.

4)。 Try to avoid distributed transactions across databases because of the complexity of the environment, which can easily lead to blocking.

5)。 Use bitmap indexes with caution, as updates can easily lead to deadlocks.

6. Reduce the number of IO and network transmissions

1)。 Try to use fewer database requests to get the required data, and those that can be taken out at one time can be taken out for many times.

2)。 For batch operations that frequently operate the database, stored procedures should be adopted to reduce unnecessary network transmission.

After reading the above, do you have any further understanding of how to talk about database optimization scheme? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report