1. Transaction isolation level
Reference: zhuanlan.zhihu.com/p/117476959
Transaction isolation can be divided into different levels, including Read uncommitted REPEATable Read Serializable
From the top down, the isolation strength gradually increases, and the performance gradually deteriorates. The isolation level to use is a tradeoff based on system requirements, where repeatable reads are the default level for MySQL.
Transaction isolation is designed to solve the problems mentioned above, such as dirty reads, unrepeatable reads, and phantom reads. The following shows how the four isolation levels solve these three problems.
Read uncommitted
It is possible to read uncommitted data from another transaction, but there is no guarantee that the data you read will be the committed data. If there is a rollback in the middle, there will be a problem with dirty data. Not to mention repeatable and illusory, don’t even think about it.
Read the submission
Since read uncommitted doesn’t solve the problem of dirty data, there is read commit. Read commit is when a transaction can only read data that has already been committed by another transaction, that is, after the other transaction has called commit. The dirty data problem was solved. In the same transaction (transaction B in this example), the same query conditions at different times of the transaction will result in different records. The submission of transaction A will affect the query results of transaction B, which is the non-repeatable read, also known as the isolation level of read submission. Read commits solve the problem of dirty reads, but they do not make repeatable reads, nor do they solve phantom reads.
Repeatable read
Repeatable is contrasted with non-repeatable, which means that the data values read at different times may be different for the same thing. Repeatable reads mean that a transaction will not read changes made to existing data by other transactions, even if other transactions are committed. In other words, the value of the existing data read at the beginning of the transaction will be the same at any time until the transaction is committed. However, the newly inserted data is readable for other transactions, which causes the illusion problem.
serialization
Serialization is the most effective of the four transaction isolation levels. It solves the problems of dirty reads, repeatable reads, and phantom reads, but it is the least effective. It changes the execution of transactions into sequential execution, which is like a single thread compared to the other three isolation levels.
Conclusion: First of all, read uncommitted is the best performance, but it can also be said that it is the most barbaric method, because it does not lock, so there is no isolation effect, can be understood as no isolation.
Serialization. Read with a shared lock, that is, other transactions can be read concurrently, but not written. An exclusive lock is added when writing. Other transactions cannot be written or read concurrently.
Finally, read commit and repeatable read. These two isolation levels are complex, allowing for a certain amount of concurrency while simultaneously trying to solve the problem.
What are current and snapshot reads under MySQL InnoDB?
The database records read by the current read are of the latest version. The current read data is locked to prevent other transactions from modifying data. Is an operation of pessimistic locking.
The following operations are current reads:
Select lock in share mode SELECT for UPDATE UPDATE INSERT delete Serialize transaction isolation level
Snapshot read Snapshot read is implemented based on multi-version concurrency control, that is, MVCC. If multiple versions are used, the data to be read may not be the latest data, but may be the data of previous versions.
Concurrency Control is a multi-version Concurrency Control mechanism designed to improve the Concurrency performance of a database. Principle: Mainly through version chain, undo log, Read View to achieve. Each row in the database contains several hidden fields: db_trx_id, db_ROLL_pointer, and db_row_id.
Db_trx_id: recently modified (modified/inserted) Transaction ID: Specifies the ID of the transaction in which the record was created or last modified.
Db_roll_pointer: rollback pointer to the previous version of this record (stored in the rollback segment).
An undo log is logged every time a change is made to the database record, and each undo log has a roll_pointer attribute (the undo log for INSERT does not have this attribute because the record does not have an older version). You can concatenate these undo logs into a linked list.
After each update to the record, the old value will be put into an undo log. As the number of updates increases, all versions of the record will be connected into a linked list by the roll_pointer attribute. This list is called the version chain, and the first node of the version chain is the latest value of the current record. In addition, each version also contains the transaction ID for which the version was generated, which is important information to use when judging version visibility from ReadView.
Read View A Read View produced when a transaction performs a snapshot Read. A snapshot of the current database system is generated at the moment the snapshot Read is performed.
Records and maintains a list of other transaction ids in the system that are currently active and should not be seen by the current transaction (there is no COMMIT; each transaction starts with an ID that is incrementally increased, so the newer the transaction, the greater the ID value).
The main purpose of the Read View is to make a visibility judgment, that is, when a transaction performs a snapshot Read, create a Read View of the record. This is compared to a condition to determine which version of the data can be seen by the current transaction. It could also be a version of the undo log recorded in the row.
Read View several properties
1. Trx_ids: set of active (uncommitted) transaction version numbers in the current system.
2. Low_limit_id: Maximum transaction version number +1 when the current read view is created.
3, up_limit_id: “system was at the minimum version of an active transaction” when the current read view was created
Creator_trx_id: creates the transaction version of the current read View.
Read the View visibility judgment conditions db_trx_id < up_limit_id | | db_trx_id = = creator_trx_id (display)
If the data transaction ID is less than the minimum active transaction ID in the Read View, you can be sure that the data existed before the current transaction started and therefore can be displayed.
Creator_trx_id = creator_trx_id = creator_trx_id = creator_trx_id = creator_trx_id
Db_trx_id >= low_limit_id (not displayed)
If the data transaction ID is greater than the maximum transaction ID of the current system in the Read View, the data was created after the current Read View was created, so the data is not displayed. If less than, it goes to the next judgment
Db_trx_id Specifies whether the value is in active transactions (trx_IDS)
None: the transaction is committed when the Read View is created, in which case the data can be displayed.
Existing: represents the time when my Read View was generated, and your transaction is still active and has not yet been committed. The data you modified is also invisible to my current transaction.
RR and RC Generation time At the RC isolation level, each snapshot Read is generated and the latest Read View is obtained.
In RR isolation, a Read View is created only for the first snapshot Read in the same transaction. Subsequent snapshot reads obtain the same Read View, and subsequent queries are not generated repeatedly. Therefore, the query result of a transaction is the same every time.
Advantages of MVCC: Supports concurrent read without locking, increasing the concurrency. Disadvantages of MVCC: Maintains multiple versions of the same row, consuming more space
2, transaction ACID
Atomicity: All operations in a transaction either complete or do not complete and do not end up somewhere in between.
Consistency: The integrity of the database is not compromised before and after a transaction
Isolation: When multiple transactions are executed concurrently, they are isolated from each other. Transaction isolation can be divided into different levels, including Read uncommitted, Read Committed, Repeatable Read and Serializable.
Durability: Modifications to data are permanent after a transaction ends
3, dirty read, magic read, repeatable read, not repeatable read
Dirty read
Dirty reads are read data that has not been committed by another transaction. Uncommitted means that the data may be rolled back, meaning that it may not end up in the database, meaning that it does not exist. Dirty reads are those that read data that must eventually exist.
Repeatable read
Repeatable reads mean that the data read at the beginning of a transaction is the same as the data read at any time until the end of the transaction. Usually for data UPDATE operations.
Unrepeatable read
In contrast to repeatable read, non-repeatable read means that the same batch of data may be read at different times in the same transaction and may be affected by other transactions, for example, other transactions have changed the batch of data and committed it. Usually for data UPDATE operations.
Phantom read
Phantom reads are for INSERT operations. Assume that the transaction for some lines for A change, but has not been submitted and the transaction B insert with the transaction before A change of record of the same line, and the transaction is A submit before you submit, and then, in A transaction in A query, will find that just change for some of the data did not play A role, but in fact is the transaction B just inserted in, let the user feel very magical, The feeling appears the illusion, this is called the illusion read.
4, index,
What is the difference between a primary key index and a non-primary key index?
If the query statement is select * from table where ID = 100, you only need to search the B+ tree ID.
If the query statement is SELECT * from table where K =1, that is, non-primary key query, the k index tree is searched first to obtain ID=100, and then the ID index tree is searched again. This process is also called table back.
5. Binlog is separated from master/slave replication and read/write
Master/slave replication is used to achieve read/write separation, write to the master library, read from the slave library, implemented by binlog.
Binlog format
MySQL’s binlog is a binary file that records all additions, deletions, and changes. Replication between nodes is done by binlog and binlog has three modes:
1. In Row mode, the modified data is recorded in logs, and the same data is modified on the slave server. 2. The slave SQL Thread executes the SQL that the master has executed during the replication. 3. Mixed mode Mixed mode indicates the mixed mode.
Master slave replication process
The master library writes the changes to the binlog log. After the slave library connects to the master library, the slave library has an IO thread that copies the binlog of the master library to its own local and writes the changes to a relay log. Then an SQL thread from the slave library reads the binlog from the slave log and executes the contents of the binlog, i.e. executes the SQL locally to ensure that it is the same as the data from the master library.
The principle of
(1) The master server records the changes of the data in the binary log. When the data changes on the master server, the changes will be written to the binary log.
(2) The slave server will detect whether the master binary log has changed at a certain interval. If it has changed, an I/OThread requests the Master binary event.
(3) At the same time, the master node starts a dump thread for each I/O thread to send binary events to it and save them to the local relay log of the slave node. The slave node will start the SQL thread to read binary logs from the secondary log and replay them locally to make their data consistent with that of the master node. Finally, I/ OThreads and SQLThreads go to sleep, waiting to be woken up the next time.
1, the library will generate two threads, one I/O thread, one SQL thread; The I/O thread will request the master binlog and write the obtained binlog to the local relay-log file. The primary library generates a log dump thread to pass binlogs to the secondary LIBRARY I/O thread. 4. The SQL thread will read the logs in the relay log file and parse them into SQL statements for execution one by one;
Master/slave replication delay and data loss
1. Delay: The process of synchronizing data from the master library is serialized, that is, parallel operations on the master library will be serialized in the slave library. In high concurrency scenarios, data from the slave library will be slower than that from the master library, and there will be delays. So often, data that was just written to the main library may not be read, and it may take tens or even hundreds of milliseconds to read. 2. Data loss: If the master database suddenly goes down and data is not synchronized to the slave database, some data may not be available on the slave database, and some data may be lost
Solution: Semi-synchronous replication is used to solve the problem of data loss in the master library. Parallel replication is used to solve the delay of master/slave synchronization
Semi-synchronous replication: After the master database writes binlog logs, data is forced to be synchronized to the slave database immediately. After the slave database writes logs to its own local relay log, an ACK is returned to the master database. The master database considers the write operation to be complete only after receiving at least one ACK from the slave database. Parallel replication means that multiple threads are started from the library, the logs of different libraries are read in parallel from the relay log, and the logs of different libraries are replayed in parallel. This is library-level parallelism.
6. Separate databases and tables
In general, the single table to millions of data, single library concurrency reaches 2000/s, it is necessary to consider the library and table, and table are two different things, may be optical library and table, may also be optical table and library, may also be at the same time. But in the usual design, sub – library sub – table is carried out at the same time.
Vertical and horizontal segmentation
Vertical shard: Split the table by column. After splitting the table, each table contains some fields. Generally, less frequently accessed fields are placed in one table, and more frequently accessed fields are placed in another table. Because the database is cached, the fewer frequently accessed row fields, the more rows can be cached in the cache.
Horizontal partitioning: split a table into multiple tables by row. For example, 3000W data in a single table is split into three tables with 1000W data in each table.
The way of dividing database and table
Scope split: a contiguous segment of data per library. This is usually based on a time range, for example, but this is rarely used because it is easy to generate hot issues, with a lot of traffic on the latest data
Hash split: Evenly distribute the hash based on a field. This is commonly used
Advantages and disadvantages: Scope splitting and capacity expansion are more convenient, for example, one library per month according to the time range. As long as a new library is prepared, subsequent data will be stored in the new library. Hash splitting can evenly distribute the pressure of each library.
How to smooth the transition of sub-library sub-table
Double write: write the old library and the new library at the same time, in addition to the old library, add, delete and change, add, delete and change to the new library deployment system, the new library data lag too much, the use of derivative data SQL script, read the old library to write the new library. When writing, compare the timestamp of data. Do not overwrite new data with old data. After the guide, it is possible that the data is still inconsistent, so the program automatically does a round of verification, comparing each table of the new and old library data, and then if there is a difference, for those differences, read data from the old library and write again. This loop is repeated until the two libraries have exactly the same data for each table
7, Left join and inner join, how to optimize nested subquery
The left JOIN keyword returns all rows from the left table (table_name1), even if there are no matching rows in the right table (table_name2). The right Join keyword returns all rows in the right table (table_name2), even if there are no matching rows in the left table (table_name1). Inner join(‘ inner join ‘, ‘inner join’, ‘inner join’, ‘inner join’, ‘inner join’, ‘inner join’, ‘inner join’, ‘inner join’) 1. Use JOIN, which is more efficient than nested sub-query. When the amount of data is large, there is no need to bring different parameters into the cycle and iterate
InnoDB row locks are implemented by locking index entries. This is different from Oracle, which locks rows in data blocks. InnoDB’s row-locking implementation means that InnoDB uses row-locking only when data is retrieved by index criteria. Otherwise, InnoDB uses table locking!
When we use a range condition instead of an equality condition to retrieve data and request a shared or exclusive lock, InnoDB will lock the index entries of existing data records that meet the condition. For records whose Key values are within the condition range but do not exist, called gaps, InnoDB also locks this GAP. This locking mechanism is called next-key locking.
InnoDB uses gap locking for:
Prevent phantoms to meet the requirements of the relevant isolation level; Meet the recovery and replication requirements: the MySQL database uses BINLOG to input SQL statements that have been successfully executed to UPDATE data, such as INSERT, UPDATE, and DELETE. In this way, the MySQL database can be restored and replicated. The MySQL recovery mechanism (replication is a continuous binlog-based recovery performed by Slave MySQL) has the following features:
MySQL restore is SQL statement level, that is, re-execute the SQL statement in BINLOG.
Second, MySQL’s Binlog is recorded in the order in which transactions were committed, and recovery is performed in this order.
Thus, MySQL’s recovery mechanism requires that, before a transaction is committed, other concurrent transactions cannot insert any records that meet their locking conditions, that is, phantom reads are not allowed.
10. Comparison of locks with different granularity:
Table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low. These storage engines avoid deadlocks by always acquiring all required locks at once and always acquiring table locks in the same order. Table-level locking is more suitable for query-oriented applications, such as Web applications, where only a small number of concurrent users update data based on index conditions
Row-level lock: expensive, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest. Maximum concurrency, but also maximum lock overhead. In InnoDB, locks are acquired step by step except for transactions consisting of a single SQL, which makes it possible to issue life and death locks in InnoDB. Row-level locking is only implemented at the storage engine layer, not at the Mysql server layer. Row-level locking is more suitable for applications with a large number of concurrent updates of a small amount of different data based on index conditions and concurrent queries, such as some online transaction processing (OLTP) systems
Page lock: the overhead and lock time are between table lock and row lock. Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.
11, B + tree
12. Cluster index