SQL bit by bit performance optimization is actually not so mysterious (1)

I often hear that the most difficult part of SQL Server is performance optimization, which makes people feel mysterious. This kind of thing can only be done by experts. A long time ago, I saw a highly-handwritten blog on the Internet and introduced SQL optimization problems. From these aspects, optimization is not always a complicated issue, after you have mastered the basic knowledge, you can try to optimize your SQL program, or even other related programs. Optimization is a summary of experience and Code awareness after work accumulation. As long as you pay attention to accumulation, you can also do optimization work. This article is reproduced, but I strongly recommend it to all bloggers interested in database optimization, after reading this article, the next time someone else talks about optimization, we can talk about these dishes with others. First give the address: http://blog.csdn.net/haiwer/article/details/2826881

It is always believed that the performance improvement of a system is not only a task of performance optimization in the trial run or maintenance phase, but also a task in the development phase, in the entire software life cycle, you must pay attention to the need for effective work. Therefore, I hope to summarize the precautions for database performance optimization based on the different stages of the software lifecycle.

I. analysis stage

In general, in the system analysis phase, there are often too many places to pay attention to. The various functional, availability, reliability, and security requirements of the system often attract most of our attention. However, we must pay attention to the following, performance is an important non-functional requirement. You must determine the real-time requirement, response time requirement, and hardware configuration based on the characteristics of the system. It is better to have quantitative indicators of various needs. On the other hand, in the analysis phase, the types of systems should be distinguished according to various requirements. In The Big aspect, the types should be OLTP (online transaction processing system) and OLAP (Online Analytical Processing System ).

Ii. Design Phase

The design stage can be said to be a key stage of system performance in the future. At this stage, there is a process related to almost all performance tuning in the future-database design. After the database design is complete, preliminary index design can be carried out. A good index design can guide the coding stage to write efficient code, laying a good foundation for the performance of the entire system.

Pay attention to the following performance requirements during the design phase:

1. Standardization of database Logic Design

The standardization of database logic design is what we generally call the paradigm. We can simply understand the paradigm as follows:

1st specifications:Columns without repeated groups or multi-value columns are the columns in a table that cannot be further divided. This is the minimum requirement for database design.

2nd specifications:Each non-Keyword segment must depend on the primary keyword and cannot depend on some components of a combined primary keyword. That is to say, the rows in a table can be uniquely identified. Eliminate

In most cases, database design should reach the second paradigm.

3rd specifications:A non-Keyword field cannot depend on another non-Keyword field. To eliminate the transfer dependency, reaching the third paradigm should be the requirement of most tables in the system, unless there are tables with special functions.

The higher paradigm requirement is not introduced here. I personally think that if most of the requests reach the second paradigm, the system will generate fewer columns and more tables, this reduces data redundancy and improves performance.

2. reasonable redundancy

It is almost impossible to design a system completely according to the standardization. Unless the system is very small, it is necessary to add redundancy in a planned manner after the standardization design.

Redundancy can be a redundant database, a redundant table, or a redundant field. redundancy at different granularities can play different roles.

Redundancy can be increased for programming convenience or for performance improvement. From a performance perspective, redundant databases can distribute database pressure, while redundant tables can distribute the concurrent pressure on tables with a large amount of data and speed up special queries, redundant fields can effectively reduce database table connections and improve efficiency.

3. Primary Key Design

The primary key is necessary. The primary key of SQL SERVER is a unique index at the same time. In practice, we often choose the smallest key combination as the primary key. Therefore, the primary key is usually suitable for table clustered indexes. Clustered indexes have a great impact on queries. This is an index description below.

In a table with multiple keys, the selection of the primary key is also important. Generally, the selection of keys with a small total length is faster, at the same time, a small key can reduce the B-tree hierarchy of the primary key. When selecting a primary key, pay attention to the field order of the primary key combination. For the primary key combination, the performance of the primary keys in different field order may vary greatly, generally, you should select a field with a low repetition rate, a single field, or a combination of query possibilities.

4. Foreign key design

Foreign keys are used as database objects, and many people think that they are troublesome. In fact, foreign keys are useful in most cases because foreign keys are the most efficient way to maintain consistency, database Consistency requirements can be followed by foreign keys, CHECK constraints, rule constraints, triggers, and client programs. Generally, it is considered that the method closer to data is more efficient.

Exercise caution when using cascade deletion and cascade update. Cascade deletion and cascade update are the new functions of SQL server 2000 in the current year. They are reserved in SQL SERVER 2005 and should be available. I am cautious about this because cascading deletion and cascading update break through the traditional definition of foreign keys, and the features are a little too powerful, before use, you must determine that you have mastered the function scope. Otherwise, cascading deletion and cascading update may cause your data to be inexplicably modified or lost. In terms of performance, cascading deletion and cascading update are more efficient than other methods.

5. Field Design

Fields are the most basic unit of the database, and their design has a great impact on performance. Note the following:

• The data type should be numeric as much as possible. The numeric type is much faster than the numeric type.
• The data type should be as small as possible. The smaller value here refers to meeting the foreseeable future needs.
• Do NOT allow NULL unless necessary. Use not null + DEFAULT instead.
• Using less TEXT and IMAGE, binary fields are relatively slow to read and write, and there are not many reading methods. In most cases, it is best not to use them.
• Auto-increment fields must be used with caution, which is not conducive to data migration.

6. Design of physical database storage and Environment

During the design stage, you can design the physical storage, operating system environment, and network environment of the database, in the future, our system will be able to adapt to a large number of concurrent users and a large amount of data. Pay attention to the role of the file group. The applicable file group can effectively distribute I/O operations to different physical hard disks to improve concurrency.

7. System Design

The design of the entire system, especially the system structure design, has a great impact on the performance. For general OLTP systems, you can choose a C/S structure and a three-layer C/S structure, the performance of different system structures varies.

In the system design phase, some business logic should be summarized in Database Programming implementation. database programming includes database stored procedures, triggers and functions. The advantage of using Database Programming to Implement business logic is to reduce network traffic and make full use of the database's pre-compilation and cache functions.

8. Index Design

In the design phase, you can perform a preliminary index design based on the functional and performance requirements. here you need to design indexes based on the expected data volume and query, which may be different from actual use in the future.

You should change your mind about the selection of indexes:

• You can determine which tables need to add indexes based on the data volume. If the data volume is small, only the primary key can be used.
• Determines which fields need to be indexed Based on the frequency of use, and selects fields that are often used as connection conditions, filtering conditions, aggregate queries, and sorting as candidate fields of the index.
• Combine fields that often appear together to form a composite index. The field order of the composite index is the same as that of the primary key. You also need to put the most common fields in front, put the fields with low repetition rate in front.
• Do not add too many indexes to a table because the index affects the speed of insertion and update.