Database optimization includes the following three parts, database optimization, database table optimization, program operation optimization. This article is part II
Optimizing ①: Designing standardized tables to eliminate data redundancy
Database paradigm is a database design method that ensures the structure of database is reasonable, satisfies various query needs and avoids abnormal database operation. The table that satisfies the paradigm requirement, called the normalized table, was produced in the early 1970s, the general table design satisfies the first three normal forms, and here briefly introduces the first three paradigms.
First of all, give you a look at the definition of Baidu Encyclopedia:
First normal form (1NF) No duplicate columns
The so-called first paradigm (1NF) refers to a specification requirement for a domain to be added in a relational model, where all domains should be atomic, that is, each column of a database table is an indivisible atomic data item, not a collection, an array, a record, and other non-atomic data items.
Second Normal form (2NF) property
On the basis of 1NF, non-code attributes must be completely dependent on the code [eliminating the partial function dependence of the main code on the 1NF basis]
Third normal form (3NF) property
On 1NF basis, any non-primary attributes are not dependent on other non-primary attributes [elimination of delivery dependencies on 2NF basis]
The popular explanation (may not be the most scientific, the most accurate understanding)
The first paradigm: the atomic constraint of attribute (field), which requires the property to be atomic and not to be segmented;
Second paradigm: The uniqueness constraint of a record requires that records have a unique identity, and each record needs to have an attribute to be the unique identifier of the entity.
Third normal form: attribute (field) redundancy constraint, that is, any field can not be derived from other fields, in popular point is: The primary key is not directly related to the data column must be eliminated (the elimination is to create a table to store them, of course, except for foreign keys)
If the database design is fully standardized, no copy of the data (repetition) will appear when all the tables are connected through keywords. The advantages of standardization are obvious, it avoids the data redundancy, naturally saves the space, also to the data consistency (consistency) provides the fundamental safeguard, eliminates the data inconsistency phenomenon, simultaneously also enhances the efficiency.
Optimizing ②: Appropriate redundancy, adding computed columns
The practical principle of database design is: To find the right balance between data redundancy and processing speed.
A table that satisfies the paradigm must be a normalized table, but not necessarily the best design. In many cases, in order to improve the efficiency of the database, it is often necessary to reduce the normal standard: the appropriate increase in redundancy to achieve the goal of space exchange time. For example, we have a table, product name, unit price, inventory amount, total value. This table does not satisfy the third normal form because "total value" can be obtained by multiplying the "unit price" by "quantity", stating that "amount" is a redundant field. However, to increase the "total value" of the redundant field, you can improve the speed of query statistics, this is the space to change the time of the practice. Reasonable redundancy can disperse the concurrent pressure of the table with large data, and can speed up the special query, the redundant fields can reduce the connection of database table and improve the efficiency.
The "total value" is a computed column, there are two types in the database: data columns and computed columns, data columns are required by us manually or the program to give the value of the column, computed column is derived from other data in the table calculated, such as the "total value" here
To create a computed column in sql:
Copy Code code as follows:
CREATE TABLE table1
(
Number Decimal (18,4),
Price,
Amount as Number*price--This is the computed column.
)
You can also add or modify column properties directly in the table design: The following figure
Persistence, we also need to be aware of:
If it is ' no ', it means that the column is a virtual column, calculated once for each query, and that it is not allowed to do check,foreign key or NOT NULL constraints.
If it's ' yes ', it's a real column, it doesn't have to be calculated every time, you can create an index on this column, and so on.
Optimizing ③: Indexing
Index is an important index of table optimization, which occupies a very important component in table optimization, so we will write a separate chapter "SQL Index One-Step" to tell you how to build and optimize the index
Optimizing ④: The need for primary and foreign keys
The design of primary key and foreign key plays an important role in the design of global database. Because: A primary key is an abstraction of an entity, a pairing of a primary key and a foreign key, representing the connection between entities.
Primary key: According to the second normal form, need to have a field to identify this record, the primary key is undoubtedly the best logo, but many tables do not necessarily need a primary key, but for large data, query frequently database table, must have a primary key, primary key can increase efficiency, prevent duplication and other advantages.
Primary key selection is also more important, the general selection of the total length of small keys, small keys faster, while small keys can make the primary key B-tree structure of the hierarchy less.
Primary key selection also pay attention to the combination of primary key field order, for the combination of primary keys, different field order primary key performance difference may be very large, you should generally choose a low repetition rate, alone or the combination of the possibility of large query field in front.
FOREIGN key: Foreign key as a database object, many people think the trouble without, in fact, foreign keys in most cases is very useful, the reason is: foreign key is the most efficient consistent maintenance method
Database consistency requirements, in turn can use foreign keys, check constraints, rules constraints, triggers, client programs, generally think that the closer the data from the method efficiency.
With a careful use of cascading deletes and cascading updates, cascading deletes and cascading updates as new features of SQL SERVER 2000, are reserved in 2005 and should be available. I'm talking about caution here, because cascading deletions and cascading updates have broken through the traditional definition of foreign keys, the functionality is a bit too powerful to use before you have to make sure that you have mastered its scope, otherwise, cascading deletions and cascading updates may make your data inexplicably modified or lost. cascading deletes and cascading updates from performance are more efficient than other methods.
Optimizing ⑤: Proper use of stored procedures, views, functions
Many people are accustomed to putting complex operations on the application layer, but if you want to optimize data access performance, porting the SQL code to the database (using stored procedures, views, functions, and triggers) is a big improvement for the following reasons:
1. Stored procedures Reduce network transmission, processing and storage workload, and after compilation and optimization, execution speed, easy maintenance, and the structure of the table changes, does not affect the client's application
2. Using stored procedures, views, functions can help reduce the drawbacks of SQL replication in your application, because you now have only one place to centralize SQL
3, the use of database objects to achieve all the TSQL to help analyze TSQL performance problems, while helping you to centrally manage TSQL code, better refactoring TSQL code
Optimizing ⑥: The legendary ' Three Little principles '
①: The fewer tables the database, the better.
②: The fewer fields The table is, the better.
③: The smaller the combined primary key, the better the combined index in the field
Of course this is relatively small, and is an important design idea for reducing data redundancy.
optimize ⑦: Split your table, reduce the size of the table
If you find that there are too many records for a table, such as more than 10 million, you want to split the table horizontally. The horizontal split is to divide the table's records horizontally into two tables, with a value of the primary key of the table being bounded.
If you find that a table has too many fields, such as more than 80, split the table vertically and break down the original table into two tables
Optimizing ⑧: Field design principles
A field is the most basic unit of a database, and its design has a great impact on performance. The following should be noted:
A, the data type as far as possible uses the numeral type, the numeral type comparison is much faster than the character type.
B, the data type as small as possible, here is as small as possible to meet the foreseeable future needs of the premise.
C, try not to allow NULL, unless necessary, you can use not null+default instead.
D, less text and image, the binary field of reading and writing is relatively slow, and, the method of reading is not much, most of the best not.
E, self-added fields to be used with caution, not conducive to data migration
