Shulou(Shulou.com)06/01 Report--

This article will explain in detail the knowledge points about mysql database for everyone. The content of the article is of high quality, so share it with you for reference. I hope you can gain something after reading this article.

Relational and non-relational databases

1. Characteristics of relational database:

1) Data presented in tabular form

2) Each row is the name of each record

3) Data field corresponding to record name in each column

4) Many rows and columns make up a form

5) Several forms form a database

Advantages of relational databases:

1) Complex queries: SQL statements can be used to easily do very complex data queries between a table and multiple tables.

2) Transaction support: enables data access requirements with high security performance to be achieved.

Advantages of non-relational databases:

1) Performance: NOSQL is based on key-value pairs, which can be imagined as the correspondence between primary keys and values in the table, and does not need to be parsed by SQL layer, so the performance is very high.

2) Scalability: Also because there is no coupling between data based on key-value pairs, it is very easy to scale horizontally.

Differences between three traditional databases

Among them, hierarchical database is tree structure, network database is link pointer structure, and relational database is two-dimensional table structure.

II. ACID

A transaction is a series of operations performed as a single logical unit of work that are committed to the system as a whole, either all or none of them being performed. A transaction is an indivisible logical unit of work.

A transaction must have the following four attributes, referred to as ACID attributes:

Atomicity: A transaction is a complete operation. The operations of things are indivisible (atomic); either all are performed or none is performed;

Consistency: When the transaction completes, the data must be in a consistent state.

Isolation: All concurrent transactions that modify data are isolated from each other, which means that the transaction must be independent and should not depend on or affect other transactions in any way;

Durability: After a transaction completes, its modifications to the database are permanent, and the transaction log maintains the permanence of the transaction.

Isolation level of transactions:

Read uncommitted (also known as dirty read uncommitted): All transactions can see the results of other uncommitted transactions. There may be dirty reading, non-repeatable reading and phantom reading problems.

Read committed: A transaction can only see changes made by committed transactions. Dirty reading can be avoided, and there may be non-repeatable reading and phantom reading problems.

Repeatable read: MySQL's default transaction isolation level ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. Dirty reading and non-repeatable reading can be avoided, and there may be phantom reading problems.

Serializable: The highest level of isolation, which solves the phantom read problem by placing a shared lock on each read data line so that it is impossible for them to collide with each other. Dirty reading, non-repetitive reading and phantom reading can be avoided.

Problems with concurrent transactions:

1. Update loss: Two transactions T1 and T2 read in the same data and modify it. The result committed by T2 overrides the result committed by T1, causing the modification of T1 to be lost.

2. Dirty read: Transaction T1 modifies some data and writes it back to disk. After transaction T2 reads the same data, T1 is revoked for some reason. At this time, the modified data of T1 is restored to its original value. The data read by T2 is inconsistent with the data in the database. The data read by T2 is "dirty" data, that is, incorrect data.

3. Non-repeatable reading: refers to reading the same data multiple times within a transaction. Before the transaction ends, another transaction accesses the same data. Then, between the two reads in the first transaction, the data read in the first transaction may not be the same due to the modification of the second transaction. This occurs when two identical queries within a transaction read data differently, hence the term nonrepeatable read.

4. Illusionary reading: For example, there are 10 employees with current salary of 5000. Transaction A reads that all employees with salary of 5000 are 10. At this point, transaction B inserts a record with a salary of 5000. At this time, transaction A reads again the employees whose salary is 5000, and records 11 employees. At this point, illusion reading occurs.

What's the difference between non-repeatable reading and phantom reading? The emphasis of non-repeatable reading is modification, and the emphasis of fantasy reading is addition or deletion.

MVCC (Multiversion Concurrency Control Mechanism): MVCC for InnoDB is implemented by keeping two hidden columns behind each row of records. The creation time and deletion time of rows are stored separately (not the actual time value, but the system version number). Each time a new transaction is started, the system version number is automatically incremented. The system version number at the transaction start time will be used as the transaction ID to compare with the version number of each row of records queried. After MVCC is used, it can be read repeatedly without phantom reading under isolation level.

III. Storage engine

The storage engine is the underlying software organization of the database, and the database management system (DBMS) uses the data engine to create, query, update, and delete data.

1. InnoDB (clustered index mode)

The underlying storage structure of innodb is a B+ tree. Each node of the tree corresponds to a page of innodb. The size of the page is fixed, generally set to 16K. Non-leaf nodes only have key values, and leaf nodes contain complete data.

Usage scenarios: 1) frequently updated tables, yes and handle multiple concurrent update requests;

2) Support transaction security table (ACID), support row lock and foreign key;

3) Can be recovered through bin-log logs, etc.

innodb If no primary key is set, it automatically generates a 6-byte primary key (invisible to users).

MyISAM (non-clustered indexing)

MyISAM was MySQL's default storage engine before 5.1, emphasizing performance, but it did not support transactions, row locks and foreign keys, but table locks; when inserting or updating data, the entire table needs to be locked, and efficiency will be lower. Caching only indexes, not real data. MyISAM reads data quickly and does not consume a lot of memory and storage resources.

MyISAM allows the existence of no primary key and is a static index structure.

Question: What is the difference between MyISAM and InnoDB in MySQL?

A: The two most commonly used MySQL engines are innodb and myisam. InnoDB is currently MySQL's default storage engine.

1) Transaction aspect: MyISAM emphasizes performance, query speed is faster than InnoDB type, but does not support transactions. InnoDB provides transaction support.

2) Foreign keys: MyISAM does not support foreign keys, InnoDB supports foreign keys.

3) Lock: MyISAM only supports table-level lock, InnoDB supports row-level lock and table-level lock, default is row-level lock, row lock greatly improves the performance of multi-user concurrent operation. innodb is more suitable for cases where insert and update operations are more frequent, while myisam is suitable for frequent queries. In addition, innodb table row lock is not absolute, if in the execution of a SQL statement, MySQL can not determine the scope to scan, innodb will also lock the entire table, for example: update table set num=1 where name like "%aaa%".

4) Full-text indexing: MyISAM supports full-text indexing, Innodb does not support full-text indexing. innodb has provided full-text indexing support since MySQL 5.6.

5) Table primary key: myisam allows tables without primary key to exist;innodb: if no primary key is set, a 6-byte primary key will be automatically generated (invisible to users).

Myisam: select count(*) from table, myisam simply read the number of rows saved. Because myisam has a built-in counter, count(*) reads directly from the counter.

innodb: does not store the exact number of rows in the table, that is, when executing select count(*) from table, innodb scans the entire table to calculate how many rows there are.

The above is the introduction of mysql database knowledge points, after reading whether there is any harvest? If you want to know more about it, welcome to pay attention to industry information. Thank you for reading it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.