1. Concept introduction
The database is a warehouse for storing data. It organizes, manages and stores data according to the data structure, and provides the functions of adding, deleting, modifying and checking.
2. database things
(1) What is a database transaction
Treat a group of additions, deletions, and modifications as an independent execution unit, or they all succeed. If one operation fails, the database will perform a rollback, and a set of operations will not take effect.
(2) The characteristics of things
Atomicity: Things are regarded as an indivisible execution unit, either all succeed or all fail, and the data will be applied to the database if it succeeds, and the failure will not affect the data
Consistency: The state before the start of the thing is consistent with the state after the execution of the thing
Isolation: When users access the database concurrently, the database will open a thing for each user thread, and things are isolated from each other without affecting each other
Persistence: Once a thing is submitted, the data will be permanently applied to the database, even if the machine fails, the data will not be modified
(3) Rollback of things
As an independent and indivisible execution unit, a thing either succeeds, as long as one of the operations in this group fails, the thing will be rolled back and restored to the state before the thing was executed
(4) Without considering the isolation of things, it will lead to the following situation
Dirty read: When executing with one transaction, the data of another uncommitted transaction is accessed and this data is used
Non-repeatable reading: Thing A reads a piece of data multiple times, and Thing B modifies this data, which eventually leads to different data read twice by Thing A
Phantom reading: Thing A reads all the data in a table, and Thing B adds or deletes a piece of data in this table, causing the result of A to be inconsistent with the actual result, similar to hallucinations.
The difference between non-repeatable read and phantom read:
Both phantom reading and non-repeatable reading read another transaction that has been committed. The difference is that non-repeatable reading queries are all for the same data item, while phantom reading is aimed at a batch of data as a whole
(5) Four isolation levels of things
read uncommitted: the lowest level, any situation may arise
Read committed: can avoid dirty reads
Repeatable read: can avoid dirty read and non-repeatable read
Serialization: Avoid dirty reads, non-repeatable reads, phantom reads, the highest isolation level
3.the classification of the database Relational Database
A relational database is a structured database that uses a relational model to organize data. The relational model refers to the two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and the connections between them.
(1) Advantages of relational database:
Easy to understand: the two-dimensional table structure is very close to logic, and the relational model is easier to understand than other models such as mesh and hierarchy
Ease of use: the common SQL language makes it very convenient to operate relational databases
Easy to maintain: all are composed of table structures, and the file format is consistent
Data stability: data is persisted to disk without risk of data loss
(2) Disadvantages of relational databases:
The query efficiency for massive information is low, and the ability to read and write is poor
High concurrent read and write requirements for websites For traditional relational databases, hard disk I/O is a big bottleneck
Poor flexibility: In order to ensure the ACID characteristics of the database, it must be designed according to the required paradigm as much as possible. The tables in the relational database store a formatted data structure.
(3) Common relational databases
Oracle, MySQL, sqllite
non-relational database
The so-called non-relational database is actually relative to the relational database. It is usually used to store data that is not fixed in type and has no rules. It is not strictly a database, it should be a collection of data structured storage methods, which can be documents or key-value pairs. Enterprises generate a large amount of data every day. Non-relational databases are widely used and have many application scenarios, such as office documents, text, pictures, HTML, various reports, video and audio, etc.
(1) Advantages of non-relational databases
Flexible format: The format of stored data can be in the form of key, value, document, picture, etc., with flexible use and wide application scenarios
Excellent performance: nosql is based on key-value pairs and does not need to go through the analysis of the sql layer, so the performance is very high.
High scalability: Based on key-value pairs, the coupling between data is extremely low, so it is easy to expand horizontally.
Low cost: nosql database deployment is simple, basically open source software
(2) Disadvantages of non-relational databases
Does not provide sql support, and the cost of learning and using is relatively high
no transaction mechanism
The data structure is relatively complex, and complex queries are not easy to implement
(3) Common non-relational databases
Elasticsearch, Redis, MongoDB
4.Common Mysql engines
The most common storage engines in MySQL are: InnoDB, MyISAM and MEMORY. InnoDB is the default storage engine after MySQL 5.1. It supports transactions, foreign keys, crash repair and self-increment columns. It is characterized by stability (guaranteed business integrity), but the data read and write efficiency is average; while MyISAM has high query efficiency, but does not support transactions and foreign keys; MEMORY has the highest read and write efficiency, but because the data is stored in memory, MySQL service Data will be lost after restarting, so it is only suitable for business scenarios that are not sensitive to data loss.