Database optimization Practice: MS SQL Optimization begins

Database definition:

A database is a collection of data that is organized according to a data model and exists in level two memory. This collection is as repeatable as possible, providing a variety of application services in an optimal manner for a particular organization, its data structure is independent of the application, the management and control of the crud operations of the data are unified, and the database is the advanced stage of data management, is developed on the file system.

Basic structure:

The database is divided into three levels: the physical data layer, the concept data layer and the logical data layer.

Physical data layer: it is the most inner layer of the database and is the collection of data actually stored on the physical storage device. These data is the raw data, is the user processing object, by the internal pattern describes the instruction operation processing the bit string, the character and the word composition.

Concept Data layer: It is the middle layer of the database, is the overall logical representation of the database. It points out that the logical definition of each data and the logical relation between data are the collection of storage records. It involves the logical relationship of all the objects in the database, not their physical condition, the database under the concept of the database administrator.

Logical data layer: It is a database that the user sees and uses, representing a collection of data that is used by one or more specific users, that is, a collection of logical records. The relationship between different levels of the database is transformed by mapping.

Database Features:

⑴ realizes data sharing.

Data sharing includes data that all users can access at the same time in the database, as well as users can use the database in various ways through the interface and provide data sharing.

⑵ reduces the redundancy of data.

Compared with the file system, because the database realizes the data sharing, thus avoids the user to establish the application file. It reduces the amount of repetitive data, reduces the data redundancy and maintains the consistency of the data.

⑶ the independence of the data.

The independence of data includes logical independence (the logical structure of database and the independence of application) and physical independence (the change of data physical structure does not affect the logical structure of data).

⑷ data to achieve centralized control.

In file management, data is in a decentralized state, with different users or the same user having no relationship with their files in different processing. Data can be centrally controlled and managed by using a database, and the data model is used to represent the organization of various data and the relationship between data.

⑸ data consistency and maintainability to ensure data security and reliability.

Mainly include: ① security Control: To prevent data loss, error update and unauthorized use; ② Integrity Control: Ensure the correctness, validity and compatibility of data; ③ concurrency control: Enables multiple access to data during the same time period, and prevents abnormal interaction between users.

⑹ failure recovery.

The database management system provides a set of methods to detect failures and repair failures in a timely manner, thus preventing data from being compromised. The database system can recover the failure of the database system running as soon as possible, which may be a physical or logical error. such as the wrong operation of the system caused by data errors.

Data structure Model

⑴ Data structure

The so-called data structure refers to the relationship between the organization or the data. If you use D to represent data, and R represents a collection of relationships that exist between data objects, then ds= (D,R) is called a data structure. For example, there is a telephone directory, which records the names of n individuals and the corresponding telephone numbers. To easily find someone's phone number, place names and numbers in a dictionary order, followed by the corresponding phone number behind the name. So, to find someone's phone number (assuming that the first letter of his name is Y), just look for those names that start with Y. In this example, the collection D of the data is the name and telephone number, and the connection R is arranged in dictionary order, and the corresponding data structure is ds= (d,r), an array.

⑵ Data structure Type

The data structure is divided into the logical structure and the physical structure of the data. The logical structure of the data is to observe the data from the logical point of view (that is, the relation between the data and the organization Way), which is independent of the storage location of the data. The physical structure of data refers to the structure that the data is stored in the computer, that is, the logical structure of the data is implemented in the computer, so the physical structure is also called the storage structure. Only the logical structure of the data is studied, and the method that reflects and implements the data is called the data model.

There are three kinds of more popular data models, namely, the hierarchical structure model and the net structure model based on graph theory and the relational structure model based on the relation theory.

2. Hierarchical, networked and relational database systems

⑴ Hierarchical Structure Model

The hierarchical model is essentially a directed ordered tree with a root node (in mathematics the tree is defined as a connected graph without return). The chart below is an organization chart of a university. This tissue structure images a tree, the school is the roots (called the root node), each department, professional, teachers, students, etc. for the branch point (called the node), the root and branch point of the link between the edge, the root and side of the ratio of 1:n, that is, root only one, branches have N.

The database system based on the hierarchical model is called the Hierarchical model database system. IMS (Information Manage-mentsystem) is its typical representative.

⑵ Mesh Structure Model

The database system established according to the network data structure is called the Net database system, its typical representative is DBTG (data Base Task Group). The network data structure can be transformed into hierarchical data structure by mathematical method.

⑶ Relational Structure model

Relational data structures attribute some complex data structures to simple two-element relationships (i.e., two-dimensional tabular forms). For example, a unit of employee relations is a two-yuan relationship.

Database types include database types such as Oracle,ms sql,mysql,nosql,sybase,infomix,db2,vf,access,

The lifecycle of a database system can be divided into three stages: design, development and finished product. Database performance optimization at the design stage is the lowest cost and the most profitable. Database performance optimization at the end of the product phase is the most expensive and the least profitable.

The optimization of a database can often be done by optimizing the network, hardware, operating system, database parameters, and applications. The most common method of optimization is the upgrade of hardware. According to statistics, to optimize the network, hardware, operating system, database parameters to improve performance, all add up to only 40% of the performance of the database system, the remaining 60% system performance improvement from the optimization of the application. Many optimization experts believe that the optimization of the application can be improved by 80% system performance.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/basis/