Database optimization practices [begin with MSSQL optimization] _ MySQL

Database optimization practices [begin with MSSQL optimization] bitsCN.com

Database definition:

A database is a data set that is organized according to a certain data model and has a secondary storage. this set is as unique as possible and provides multiple application services for a specific organization in the optimal way, its data structure is independent of applications. it manages and controls CRUD operations in a unified manner. databases are an advanced stage of data management and are developed on file systems.

Basic structure:

Databases are divided into three layers: physical data layer, conceptual data layer, and logical data layer.

Physical data layer: it is the innermost layer of the database and a set of data actually stored on physical storage devices. These data are raw data and are the objects processed by users. they are composed of character strings, characters, and characters for instruction operations described in the internal mode.

Concept Data Layer: it is the middle layer of the database and represents the overall logic of the database. It points out that the logical definitions of each data and the logical connections between data are a collection of storage records. It involves the logical relationship of all objects in the database, rather than their physical conditions. it is a database under the concept of a database administrator.

Logical data layer: it is the database that the user sees and uses. it represents a data set used by one or more specific users, that is, a set of logical records. The relationships between different levels of databases are converted through ING.

Database features:

(1) implement data sharing.

Data sharing allows all users to access data in the database at the same time, and also allows users to use the database through interfaces in various ways, and provides data sharing.

(2) reduce data redundancy. Compared with the file system, the database achieves data sharing, thus avoiding the creation of application files. This reduces a large amount of duplicate data, reduces data redundancy, and maintains data consistency.

(3) Data independence. Data independence includes logical independence (the logical structure of the database and applications in the database are independent of each other) and physical independence (changes in the physical structure of the data do not affect the logical structure of the data ).

(4) centralized data control. In the file management mode, data is in a scattered state. different users or the same user have no relationship with each other in processing their files. Databases can be used to centrally control and manage data, and data models can be used to represent various data organizations and relationships between data.

⑸ Data consistency and maintainability to ensure data security and reliability. It mainly includes: ① security control: to prevent data loss, error update, and unauthorized use; ② integrity control: to ensure data correctness, validity, and compatibility; ③ concurrency control: enable multi-channel access to data within the same period of time, and prevent abnormal interaction between users.

⑹ Fault recovery. The database management system provides a set of methods to promptly discover and repair faults, so as to prevent data from being damaged. The database system can recover the faults that occur during database system operation as soon as possible, which may be physical or logical errors. For example, data errors caused by system misoperations.

Data structure model

(1) the so-called data structure of the data structure refers to the organization form of the index data or the relationship between the data. If D is used to represent data and R is used to represent a set of relationships between data objects, DS = (D, R) is called a data structure. For example, you have a telephone number book that records the names and numbers of n people. To conveniently search for a person's phone number, sort the person's name and number in alphabetical order and follow the corresponding phone number behind the name. In this way, to find a person's phone number (assuming that the first letter of his name is Y), you only need to find the names starting with Y. In this example, the data set D is the person name and phone number, and the relationship R between them is arranged in alphabetical order. the corresponding data structure is DS = (D, R ), it is an array.

(2) data structure types the data structure is divided into the logical structure of the data and the physical structure of the data. The logical structure of data is to observe and analyze data from a logical perspective (that is, the relationship between data and the organization mode), regardless of the data storage location. The physical structure of data refers to the structure where data is stored in the computer, that is, the implementation form of the logical structure of data in the computer. Therefore, the physical structure is also called the storage structure. Here, we only study the logical structure of the data and call the method that reflects and implements the data connection as a data model. There are three popular data models, namely the hierarchical structure model based on graph theory, the mesh model, and the relational structure model based on the relational theory. hierarchical, mesh, and relational database systems (1) hierarchical model the hierarchical model is essentially a type of directed ordered tree with root nodes (in mathematics, "tree" is defined as a non-return connected graph). It is the organizational structure of a higher education institution. This organizational structure is an image of a tree. the school department is the root node. each department, major, teacher, or student is a branch node ), the association between the root and the branches is called the edge. the ratio of the root to the edge is 1: N, that is, there is only one root and N branches. A database system created based on a hierarchical model is called a hierarchical model database system. IMS (Information Manage-mentSystem) is a typical example. (2) the database system established based on the mesh Data structure is called the MeSH database system, which is typically represented by DBTG (Data Base Task Group ). Mathematical methods can be used to convert a mesh data structure into a hierarchical data structure. (3) relational structure model relational data structures classify some complex data structures into simple binary relationships (two-dimensional table forms ). For example, a company's employee relationship is a binary relationship. Database types include Oracle, MS SQL, Mysql, Nosql, Sybase, Infomix, DB2, VF, Access and other database types, the life cycle of a database system can be divided: three stages: design, development, and product creation. The lowest cost and maximum benefit of database performance optimization during the design phase. In the final stage, database performance optimization has the highest cost and the lowest benefit. Database optimization can usually be performed by optimizing the network, hardware, operating system, database parameters, and applications. The most common optimization method is hardware upgrade. According to statistics, the performance improvement obtained by optimizing network, hardware, operating system, and database parameters only accounts for about 40% of the database system performance improvement, the remaining 60% system performance improvement comes from application optimization. Many optimization experts believe that application optimization can improve system performance by 80%. In this series, we will make some optimization practices for the ms SQL database: 1. database optimization practices [index] 2. database optimization practices [TSQL] 3. database optimization practices [advanced indexing and anti-paradigm] 4. database optimization practices [performance testing tools article] 5. database optimization practices [information tracking] 6. database optimization practices [documents, file groups, and partitions]

BitsCN.com