Basic concepts of MySQL and database

DBMS: Database management System

1. Hierarchical model

2. Mesh model

3. Relational model

   
   

RDBMS: relational database management system

File storage: Data redundancy and inconsistencies are difficult to solve

File store access is difficult when the amount of data is large

When the data is in different files, it may result in inconsistent format of the data, and the data is too scattered, resulting in data isolation

Data integrity issues (such as a failure in the transfer of money between two banks, to ensure that the total is consistent)

Concurrent access exceptions (such as when multiple people are accessing a file at the same time, one person is making a modification and no one else can access it)

Atomicity problem (data shifts from one stable state to another)

Security is not guaranteed.

1. Presentation layer: File or table

2. Logic Layer: File system or Storage engine

is responsible for the abstraction of the underlying data block as a file for the user to view, while storing data by the file system to deconstruct the data into the physical layer of data (that is, to complete the mapping from the presentation layer to the physical layer)

3. Physical layer: Data block, meta data

Storage Engine: The file system can be understood by the document storage engine, the different format of the data into the presentation layer of the file, but also to store the file as physical data. The completion of both processes is done by the storage engine

Relationship: You can interpret a relationship as a single table

Properties: Each column in a table describes a property

Entities: Each behavior in a table an entity (record)

The entire table is a collection of entities of the same class

E-r relationship model (i.e. entity Relationship Model): Splitting a table into multiple tables (entities) to reduce redundancy of data

There are four types of relational models that can be stored in relational databases:

Relational model (structured data Model): Each of its fields has the same properties, such as the first column name, and the second column passwd

1. Relational model: The data is stored by a two-dimensional table,

2. Entity Relationship type: Splits a table into multiple different entities, allowing entities to connect through different constraints

3. Object-Relational Model: Object-based data type

For example, a large data (slice) is stored in a data management system in a space, and in the table with a pointer to the storage address of the data, the object-based management

4. Semi-structured data model: the data represented in each row in the same table may be different;

Name:Age:Gender

Name:UID:Birthdate

Semi-structured data model: store data when stored and store data-defined structures in XML (Extended Markup Language) format

<name>Jerry</name>

<age>50</age>

Gender

Today, most commercial or open-source relational data management systems are able to handle four of these data models efficiently.

Relationships: Algebraic operations (such as intersection, set, complement, and so on)

Constraints: If you fill in a 230 age database must be able to detect

Domain constraints: Data type constraints, such as the need to fill in int, fill in a char error

FOREIGN KEY constraint: referential integrity constraint. The fields in one table must be the same as the fields in the other table and their values should correspond to each other

PRIMARY KEY constraint: A field (or combination of fields) that uniquely identifies the entity of this row

Note: Any two entities cannot appear with the same value on the primary key and cannot be empty

If the UID in the/etc/passwd is different, it can be either a primary key, a name, or a primary key.

Uniqueness constraint: A field without a row is not allowed to have the same value, can be empty, and can have multiple

Note: Therefore, there can be only one PRIMARY KEY constraint in a table, but there may be multiple uniqueness constraints

Inspection constraints: such as Age<100,age>0

Note: MySQL support for check constraints is not strong, but Sql-server and Oracle support is better

Database language:

SQL (Structure query Language): Structured Query Language

DML: Data Manipulation language (add, delete, check, change) Insert, delete, select, update

DDL: Data Definition language

Define an RDB (Database object): library, table, index, view, user. stored procedures, stored functions, triggers, event schedulers

Crete, Drop, Alter

DCL: Data Control language used to define access rights for users in a database

Query Manager features: responsible for receiving queries from users, understanding user queries, and translating user queries into languages that the corresponding storage Manager can understand, and to be able to store or delete data on disk

Query Manager components:

DML Interpreter

DDL interpreter

Query execution engine

Storage Manager Components:

Permissions and integrity Manager

Transaction manager (Guarantees transactional integrity, atomicity, etc.)

File Manager

Buffer Manager (data query store to cache data before memory, buffer Manager is used to manage cache space, such as what data is cached)

MySQL: Single-process multithreaded model

In general, MySQL generates a request response for each user's request, which means that all operations of a user (add, delete, check, change, etc.) are done in the same thread

MySQL threads are divided into: Daemon thread, Application thread

Note: In general, each query request consumes a lot of memory (such as a large query may involve 1G of data volume is possible, and on the 32-bit system the last process can use up to 2.7G of memory), so MySQL concurrency is not as large as imagined. So in a cluster the database server may be the slowest node in the whole system, so try to avoid interacting with the database as much as possible.

So how do you avoid interacting with the database or improving the performance of your database server?

1. Save the results of the first query in the cache server (varnish, memcache), when you have the same query request at a later time, directly in the cache server to find

2. Thread reuse: such as Discuz forum the same user may send a post every time, and each post to the database server is a new request, each time to create a delete thread too cumbersome, you can use the thread implementation multiple times

3. Use a server with good performance and a 64-bit OS

MySQL has poor CPU support for SMP (symmetric multiprocessor) architectures, and MySQL can handle multiple queries on different CPUs, but not a large query running concurrently on multiple CPUs, where a single statement can run on only one CPU, This is a very important reason that MySQL is not as good as orcale (that is, extensibility is not), but you can do each MySQL function simple point, a server only a little bit of functionality, a MySQL cluster, so as to effectively solve this problem

Relational operations:

1. Projection outputs only a field or an attribute

2. Select to output only rows that match the criteria

3. Natural selection in the corresponding field of the two tables is established by the equivalence relation is called the connection of nature

   
   

Table 1

A	1
B	2
C	1
D	3

Table 2

1	Bash
2	Csh
3	Zsh
4	Ksh
5	Tcsh

After natural connection

A	1	1	Bash
B	1	1	Bash
C	3	3	Zsh
D	2	2	Csh

4. Cartesian product

(a+b) * (c+d) =AC+AD+BC+BD

Each row in table 1 is connected =4*5=20 rows with each row in table 2

Often Cartesian product lookups are slow and should be avoided as much as possible

5. and

Table 1 row + table 2 line = = new row

The structure of MySQL is composed

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8D/7E/wKiom1iduS-xNec9AAJekQjdWdc101.png "title=" 111. PNG "alt=" Wkiom1idus-xnec9aajekqjdwdc101.png "/>

Table Manager: Responsible for creating, reading, or modifying table definition files, maintaining table descriptor caches, and managing table locks;

Table structure definition file

Table modification Modules: Operations such as table creation, deletion, renaming, removing, updating, or inserting;

Table Maintenance Module: Inspection, modification, backup, recovery, optimization (defragmentation) and parsing;

Buffer Manager: There is a cache substitution policy, generally memory is much smaller than disk, so when the operation involves more data than memory, the buffer manager to follow the cache policy (such as the least recently used policy) to load the data block, such as the operation of removing

Storage of data on disk: Disk is a block device, data is stored in blocks (block), but can only be manipulated in blocks when reading data (I/O operations), and cannot be read directly as required by demand as a character device

and a block often holds multiple rows (if the data is not very large), if the deletion of one of the rows, a long time will be poor poor number of data gaps, resulting in a waste of storage space, then how to facilitate the data gap?

Each block has a size, typically a storage engine for data block management, the size of the record how many rows saved, that is, where the blank, so that the size of the disk can be easily organized data, so as to avoid the production of debris

Size (records how many rows are saved in this block, and which are not saved)

Gap (removal caused)

Data 1

Gap

Data 2

Records of data organization:

1. Heap file organization, data can be stored in any location in the file, facilitate storage, not conducive to the search

2. Sequential file organization, data according to the "Search code" in order to store, not conducive to storage, each time the data update to reorder, but to facilitate the search

3. A hash file is stored, a field of data is hashed (hash operations, such as a fetch), and then stored in different buckets according to different results.

4. The pointer is stored, and each row of data is stored with a pointer to the next line so that it does not have to be stored sequentially

Tablespace: Storing data from multiple tables in the same table space, which is equivalent to a more advanced data storage space

Data dictionary: Metadata for relationships such as tables, views, and so on, such as the name of the relationship, field name, field type, field length, user name, authorization, password, etc.

This article is from the "11097124" blog, please be sure to keep this source http://11107124.blog.51cto.com/11097124/1896863

Basic concepts of MySQL and database