Database Optimization Method Collation

The goal of database optimization is to avoid disk I/O bottlenecks, reduce CPU utilization, and reduce resource contention.

In the table-driven information management system (MIS), the design specification of the basic table is the third paradigm (3NF).

The basic feature of the third paradigm is that the non-primary key attribute depends only on the primary key attribute.

The design of database table based on the third paradigm has many advantages:

One is to eliminate redundant data, saving disk storage space;

Second, there is a good data integrity limit, that is, based on the primary foreign key referential complete restrictions and primary key-based entity integrity restrictions, which makes the data easy to maintain,

Also easy to transplant and update;

Third, the reversibility of the data is good, in the connection (join) query or merge the table is not missing, also do not repeat;

Four is because of the elimination of redundant data (redundant columns), in the query (Select) The number of data rows per data page, which effectively reduce the logical I/O,

There are more pages per cash and less physical I/O;

Five, for most transactions (Transaction), the performance of the operation is good;

The physical design is designed to be more maneuverable and meets the growing needs of users.

In the basic table design, the table's primary key, foreign key, index design occupies very important position, but the system designer often only focus on satisfies the user request,

They are not recognized and valued from the height of the system optimization. In fact, they are closely related to the operational performance of the system. Now from the System database optimization perspective

(1) Primary key (Primary key): When a primary key is used in a complex SQL statement, it is frequently used in data access. A table has only one primary key.

The primary key should have a fixed value (cannot be null or default value, want to have relative stability), without code information, easy access.

It makes sense to use common (well-known) columns as primary keys.

The short primary key is best (less than 25bytes), the length of the primary key affects the size of the index, the size of the index affects the size of the index page,

This can affect disk I/O.

Primary keys are divided into natural primary keys and people as primary keys. The natural primary key is composed of the attributes of the entity, and the natural primary key can be complex,

When a composite primary key is formed, the primary key column cannot be too many,

Composite primary keys complicate join* and increase the size of the foreign key table. The person is the primary key, in the absence of a suitable natural attribute key,

Or when the natural properties are complex or highly sensitive,

Artificially formed. Human-oriented key is generally the integer value (meet the minimum requirements), no practical significance, but also slightly increased the size of the table;

But it reduces the size of the table that takes it as a foreign key.

(2) foreign key (Foreign key): The role of foreign keys is to establish a relational database of the relationship between tables (referential integrity),

A primary key can only be migrated from a separate entity to a non-independent entity.

becomes a property of the latter, called the foreign key. (3) Index: Optimizing system performance with indexes is obvious, for all columns that are commonly used in a WHERE clause in a query

And all columns used for sorting to create an index that avoids the whole table scan or access, without altering the physical structure of the table,

Direct access to specific data columns, thus reducing data access time; using indexes to optimize

Or to exclude time-consuming classifications *; The data is scattered across different pages, and the inserted data is dispersed; The primary key automatically establishes a unique index

, the unique index also ensures data uniqueness (that is, entity integrity); The smaller the index code, the more direct the positioning; The new index is the best,

It is therefore necessary to periodically update the index.

The index also has a price: there is space overhead, and it takes time to build it, and there is a maintenance cost when it comes to insert, delete, and update*.

There are two types of indexes: Clustered index and non-clustered index. A table can have only one clustered index, and there can be multiple non-clustered indexes.

Querying data using a clustered index is faster than using a nonclustered index.

The database system functions should be used to estimate the size of the index before the index is under construction. ① Clustered index (Clustered index): The data page of the clustered index is stored in a physical order and occupies little space.

The selection policy is the column that is used in the WHERE clause: include range query, fuzzy query, or highly duplicated column (continuous disk scan);

A column used to connect join*;

Columns used for order BY and GROUP BY clauses. Clustering index is not conducive to inserting *, there is no need to use primary key to build family index. ② non-clustered index (nonclustered index): occupies a large space and is inefficient compared to a clustered index. The selection policy is,

The column to be used for the WHERE clause:

Include range queries, fuzzy queries (when there is no clustered index), primary key or foreign key columns,

Point (pointer Class) or small range (the returned result field is less than 20% of the entire table data) query;

Columns used to connect join*, primary key columns (range queries), columns used for order BY and GROUP BY clauses, and columns to be overwritten.

It is advantageous to build multiple non-clustered indexes on read-only tables.

Index also has its drawbacks, one is to create an index time consuming, second, the index to occupy a large amount of disk space,

The third is to increase the maintenance cost (the index slows the modification speed when modifying indexed data columns). So, in which case does the index not be built?

For small tables (data less than 5 pages),

Small to Medium table (without direct access to single-row data or result sets without sorting), single-domain (return-value-dense),

Indexed column values are too long (greater than 20bitys), easily variable columns,

Highly duplicated columns, null value columns, cannot be indexed for columns that are not used in where and join queries.

In addition, the main use for data entry, as little as possible to build indexes.

Of course, it is also necessary to prevent an invalid index from being established, when there are more than 5 conditions in the where statement, the cost of maintaining the index is greater than the efficiency of the index.

At this point, it is more efficient to set up temporary tables to store related data. Considerations when importing data in bulk: In real-world applications, large quantities of computing (such as telecom Bill Billing) are done in C language programs,

This is based on the primary foreign key relational data calculated by the batch number

(text file), you can use the system's own function functions (such as Sybase's BCP command) to quickly bulk import, when you import database tables,

You can delete the index of the corresponding library table first.

This facilitates faster import and less import time. Rebuild the index after importing to refine the query. (4) Lock: Lock is an important mechanism of parallel processing, can maintain consistency of data concurrency, that is, processing by transaction;

ensure data integrity. Therefore, we can not avoid deadlocks, but in the design of the full consideration of how to avoid long transactions, reduce the lock time,

Reduce the interaction with the user in the transaction, prevent the user to control the length of the transaction, to avoid the simultaneous execution of batch data,

This is especially time consuming and used to the same data table.

Lock requisition: A table can only have one row lock at a time, while a user is waiting for another user. If the number of users increases,

The performance of the server is degraded, and the phenomenon of "suspended animation" appears. How to avoid deadlocks? From page-level lock to row-level lock, the lock requisition is reduced;

Add invalid records to a small table, from page-level locks to row-level locks have no effect, if the same page competition has an impact, you can choose the appropriate clustering index

Assign data to different pages, create redundant tables, keep transactions short, and the same batch should have no network interaction. (5) Query optimization rules: When accessing data from a database table, avoid sorting (sort) as much as possible,

Connection (join) and related subquery *. Experience tells us that when optimizing queries, you must do: ① as few rows as possible, ② avoid sorting or sort for as few rows as possible, and to do a lot of data sorting, it is best to put the relevant data in a temporary table *;

Sort by a simple key (column), such as Integer or short string sorting; ③ Avoid related subqueries within a table; ④ avoid using complex expressions or non-starter substrings in the WHERE clause, concatenate with long strings, ⑤ use "with" (and) in the WHERE clause, and use less "or" (or) connections; ⑥ Take advantage of the staging database. When querying multiple tables, multiple connections, complex queries, and data filtering, temporal tables (indexes) can be built to reduce I/O.

But the disadvantage is that it increases the space overhead. Unless there is index support for each column, find two dynamic indexes on a connected query and reorder them on the worksheet. 3 Basic table extension design the library table based on the third paradigm has its advantages (see the first part of this article), however, it is sometimes unfavorable to the system operation in practical application.

Performance optimization: If you need some data to scan the whole table, many processes compete for the same data at the same time, repeatedly using the same peer to calculate the same results,

The process of fetching data from multiple tables raises a number of connections *, when the data originates from multiple tables when the connection is made; This consumes disk I/O and CPU time. In particular, we want to extend the design of the base table when we encounter the following situations: Many processes frequently access a table, subset data access,

Repetitive calculations and redundant data, sometimes users require some process-first or low response times. How can we avoid these disadvantages? Segmentation of related tables based on the frequency of access, storage of redundant data, storage of derived columns,

Merging related table processing is an effective way to overcome these unfavorable factors and optimize the operation of the system. 3.1 Partitioned table or storage redundant data partition table is divided into two types: horizontal split table and vertical split table. Partitioned tables increase the cost of maintaining data integrity. Horizontal split table: One is to split the table horizontally when multiple processes frequently access different rows of the data table, and to eliminate redundant data columns in the new table;

If the individual process to access the entire data, you have to use the connection *, which is also no harm to the partition table;

The other is that when the main process is repeatedly accessing portions of a row, it is best to separate the rows that are repeatedly accessed into subsets of tables (redundant storage).

This is important regardless of the cost of disk space, but after partitioning the table, it is more difficult to maintain, to use triggers to update immediately,

or bulk update of stored procedures or app code, this also adds additional disk I/O overhead. Vertical partitioning of tables (without breaking the third paradigm), where multiple processes frequently access different columns of a table, you can divide the table vertically into several tables,

Reduce disk I/O (there are fewer data columns per row, more rows per page, fewer pages to occupy), and you don't have to worry about locks when updating.

No redundant data. The disadvantage is to consider the integrity of data when inserting or deleting data, and to maintain it with stored procedures.

The other is that when the main process repeatedly accesses some columns, it is best to save this part of the frequently accessed column data as a subset table (redundant storage).

This is important regardless of disk space overhead, but it adds to the difficulty of maintaining overlapping columns, with triggers to update immediately,

or bulk update of stored procedures or app code, this also adds additional disk I/O overhead. The vertical partition table can be used to maximize the use of the cache. In short, the method for dividing the table for the main process applies to: each process requires a subset of tables, each process requires a subset of the table,

The main process with high frequency of access does not require an entire table.

A redundant subset of tables is generated when the primary, frequently accessed primary table requires a subset of tables and the other mostly frequently accessed processes require an entire table. Note that after splitting the table, consider re-establishing the index. 3.2 Storage of derivative data for some of the process of doing a lot of repetitive calculations, if the repeated calculation process results in the same (the source column data is stable, so the calculation results are not changed),

Or to calculate the additional disk I/O overhead required to involve multiple rows of data, or to compute complexity that requires a significant amount of CPU time, consider storing the calculation results (redundant storage).

It is now classified as follows: If you repeat the calculation in one row, the column storage results are added to the table. However, if the column that participates in the calculation is updated, you must update the new column with a trigger. If you repeat a table by class, add a new table (in general, the store class and the result are two columns) to store the related results.

However, if the column that participates in the calculation is updated, you must update the new table in batches with the trigger immediate update, or the stored procedure or application code. If you are repeating calculations for multiple rows (such as ranking), add Columnstore results to the table. However, if the column participating in the calculation is updated,

You must update the new column with a trigger or stored procedure. In summary, storing redundant data facilitates faster access, but violates the third paradigm, which increases the cost of maintaining data integrity,

You must update with triggers immediately, or bulk update stored procedures or app code to maintain data integrity. 3.3 Eliminate expensive bonding some of the main processes for frequent simultaneous access to multiple tables, consider storing redundant data in the primary table, that is, storing redundant columns or derived columns (it does not depend on the primary key).

But the third paradigm was broken, and maintenance was more difficult. This redundant column must be updated with a trigger or stored procedure when the related column of the source table changes.

Tables can be combined when the main process always accesses two tables at the same time, which reduces disk i/o*, but destroys the third paradigm and makes maintenance more difficult.

There is a difference between a parent-child table and a 1:1-relational-Table merge method: When a parent-child table is merged, a redundant table is created, and when the 1:1 relationship table is merged, redundant data is generated. 4 Placement policy for database objects the placement strategy for database objects is to evenly distribute the data across the system's disks, balancing I/O access and avoiding I/O bottlenecks. ⑴ access is dispersed to different disks, even if user data spans multiple devices as much as possible, multiple I/O runs, avoids I/O competition and overcomes access bottlenecks;

Random access and continuous access to data are placed separately. ⑵ Separate system database I/O and application database I/O. Put the system Audit table and the Temporary library table on a disk that is not busy. ⑶ puts the transaction log on a separate disk, reducing disk I/O overhead, which also facilitates recovery after an obstacle and improves the security of the system. ⑷ the frequently accessed "active" table on separate disks, and the frequently used tables and frequently join* tables are placed on individual disks,

Even place the fields of frequently accessed tables on separate disks, spread the access across different disks, and avoid I/O contention; ⑸ uses segments to separate frequently accessed tables and their indexes (non-clustered), detached text, and image data. The purpose of the segment is to balance I/O, avoid bottlenecks,

Increase throughput, enable parallel scanning, increase concurrency, and maximize disk throughput. The function of logic segment is used to place the "active" table and its

Non-clustered indexes to balance I/O. It is of course best to take advantage of the system default segment. In addition, using segments can make backup and recovery data more flexible,

Make system licensing more flexible.

Database Optimization Method collation