MySQL杂谈

ACID

An acronym standing for atomicity, consistency, isolation, and durability. These properties are all desirable in a database system, and are all closely tied to the notion of a transaction. The transactional features of InnoDB adhere to the ACID principles.
Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.
事务是可回滚、可提交，具有原子性的最小工作单位。当一个事务对数据库进行了一些操作，那么这些操作要么都成功(提交后)，要么都未完成(回滚后)。
The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.
数据库不论什么时候都是保持一致性的(每次提交、回滚或者事务执行时)。如果更新操作涉及多个表，则查询可以看到所有的新旧值，而不是一半。
Transactions are protected (isolated) from each other while they are in progress; they cannot interfere with each other or see each other’s uncommitted data. This isolation is achieved through the locking mechanism. Experienced users can adjust the isolation level, trading off less protection in favor of increased performance and concurrency, when they can be sure that the transactions really do not interfere with each other.
各个正在执行事务之间相互隔离，互不影响。它们不会互相影响，不能看到未提交的数据。实现原理是锁机制。可用牺牲部分保护来提升性能和并发性。
The results of transactions are durable: once a commit operation succeeds, the changes made by that transaction are safe from power failures, system crashes, race conditions, or other potential dangers that many non-database applications are vulnerable to. Durability typically involves writing to disk storage, with a certain amount of redundancy to protect against power failures or software crashes during write operations. (In InnoDB, the doublewrite buffer assists with durability.)
事务的结果是持久的。一旦提交成功，断电、系统崩溃、竞态条件等。
See Also atomic, commit, concurrency, doublewrite buffer, isolation level, locking, rollback, transaction.

读现象（脏读、不可重复度[update]、幻读[insert/delete]）

read phenomena（读现象）
Phenomena such as dirty reads, non-repeatable reads, and phantom reads which can occur when a transaction reads data that another transaction has modified.
See Also dirty read, non-repeatable read, phantom.

dirty read
An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed. It is only possible with the isolation level known as read uncommitted.
This kind of operation does not adhere to the ACID principle of database design. It is considered very risky, because the data could be rolled back, or updated further before being committed; then, the transaction doing the dirty read would be using data that was never confirmed as accurate.
Its opposite is consistent read, where InnoDB ensures that a transaction does not read information updated by another transaction, even if the other transaction commits in the meantime.
读到脏的数据，即查询到的数据是其它事务更改过且未提交的数据。该数据下一步操作可以是回滚也可以是进一步修改，若读到的数据并不是其最终的数据，则其行为是脏读。

non-repeatable read
The situation when a query retrieves data, and a later query within the same transaction retrieves what should be the same data, but the queries return different results (changed by another transaction committing in the meantime).
This kind of operation goes against the ACID principle of database design. Within a transaction, data should be consistent, with predictable and stable relationships.
Among different isolation levels, non-repeatable reads are prevented by the serializable read and repeatable read levels, and allowed by the consistent read, and read uncommitted levels.
See Also ACID, consistent read, isolation level, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE, transaction.
同一个事务里，对同样的数据进行2次查询，发现2次查询的结果不一致(2次查询中间被其它事务更改了)。（同一个事务里，数据理应保持一致性）

phantom
A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within a transaction, and in the meantime, another transaction commits after inserting a new row or updating a row so that it matches the WHERE clause of the query.
This occurrence is known as a phantom read. It is harder to guard against than a non-repeatable read, because locking all the rows from the first query result set does not prevent the changes that cause the phantom to appear.
Among different isolation levels, phantom reads are prevented by the serializable read level, and allowed by the repeatable read, consistent read, and read uncommitted levels.
See Also consistent read, isolation level, non-repeatable read, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE, transaction.
查询2次，发现条数增加了。（中间插入了新的数据，或者修改了数据使之匹配查询条件）

isolation level

One of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.
From highest amount of consistency and protection to the least, the isolation levels supported by InnoDB are: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.
With InnoDB tables, many users can keep the default isolation level (REPEATABLE READ) for all operations. Expert users might choose the READ COMMITTED level as they push the boundaries of scalability with OLTP processing, or during data warehousing operations where minor inconsistencies do not affect the aggregate results of large amounts of data. The levels on the edges (SERIALIZABLE and READ UNCOMMITTED) change the processing behavior to such an extent that they are rarely used.
See Also ACID, OLTP, READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE, transaction.

隔离级别（读未提交、读已提交、可重复读、序列化）
READ UNCOMMITTED
The isolation level that provides the least amount of protection between transactions. Queries employ a locking strategy that allows them to proceed in situations where they would normally wait for another transaction. However, this extra performance comes at the cost of less reliable results, including data that has been changed by other transactions and not committed yet (known as dirty read). Use this isolation level with great caution, and be aware that the results might not be consistent or reproducible, depending on what other transactions are doing at the same time. Typically, transactions with this isolation level only do queries, not insert, update, or delete operations.
See Also ACID, dirty read, isolation level, locking, transaction.
未提交读(A事务可以看到B事务未提交的数据)，最低的隔离级别，不能防止脏读，多用在只查询的情况(插入、更新或者删除不建议用此隔离级别，因为它为了性能，牺牲了数据的可靠性(一致性和可重现性))

READ COMMITTED
An isolation level that uses a locking strategy that relaxes some of the protection between transactions, in the interest of performance. Transactions cannot see uncommitted data from other transactions, but they can see data that is committed by another transaction after the current transaction started. Thus, a transaction never sees any bad data, but the data that it does see may depend to some extent on the timing of other transactions.
When a transaction with this isolation level performs UPDATE … WHERE or DELETE … WHERE operations, other transactions might have to wait. The transaction can perform SELECT … FOR UPDATE, and LOCK IN SHARE MODE operations without making other transactions wait.
SELECT … FOR SHARE replaces SELECT … LOCK IN SHARE MODE in MySQL 8.0.1, but LOCK IN SHARE MODE remains available for backward compatibility.
See Also ACID, isolation level, locking, REPEATABLE READ, SERIALIZABLE, transaction.
已提交读(A事务不能看到B事务未提交的数据，但是可以看到B事务已提交的数据)

REPEATABLE READ
The default isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads. It uses a moderately strict locking strategy so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.
When a transaction with this isolation level performs UPDATE … WHERE, DELETE … WHERE, SELECT … FOR UPDATE, and LOCK IN SHARE MODE operations, other transactions might have to wait.
SELECT … FOR SHARE replaces SELECT … LOCK IN SHARE MODE in MySQL 8.0.1, but LOCK IN SHARE MODE remains available for backward compatibility.
See Also ACID, consistent read, isolation level, locking, phantom, transaction.

SERIALIZABLE
The isolation level that uses the most conservative locking strategy, to prevent any other transactions from inserting or changing data that was read by this transaction, until it is finished. This way, the same query can be run over and over within a transaction, and be certain to retrieve the same set of results each time. Any attempt to change data that was committed by another transaction since the start of the current transaction, cause the current transaction to wait.
This is the default isolation level specified by the SQL standard. In practice, this degree of strictness is rarely needed, so the default isolation level for InnoDB is the next most strict, REPEATABLE READ.
See Also ACID, consistent read, isolation level, locking, REPEATABLE READ, transaction.
自当前事务开始以来，任何来自其它事务提交的数据更改都会导致当前事务等待。

可重复读和序列化的区别

Both non-repeatable and phantom reads have to do with data modification operations from a different transaction, which were committed after your transaction began, and then read by your transaction.
Non-repeatable reads are when your transaction reads committed UPDATES from another transaction. The same row now has different values than it did when your transaction began.
Phantom reads are similar but when reading from committed INSERTS and/or DELETES from another transaction. There are new rows or rows that have disappeared since you began the transaction.
Dirty reads are similar to non-repeatable and phantom reads, but relate to reading UNCOMMITTED data, and occur when an UPDATE, INSERT, or DELETE from another transaction is read, and the other transaction has NOT yet committed the data. It is reading “in progress” data, which may not be complete, and may never actually be committed。

索引

树的高度
https://developer.aliyun.com/article/420669
这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。

上文我们已经说明单个叶子节点（页）中的记录数=16K/279=58。（我们从上面可以看到每行记录的数据平均大小为279个字节）。

那么现在我们需要计算出非叶子节点能存放多少指针，其实这也很好算，表中的主键ID为int类型，长度为4字节，而指针大小在InnoDB源码中设置为6字节，这样一共10字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/10=1638。那么可以算出一棵高度为2的B+树，能存放1638*58=95004条这样的数据记录。

根据同样的原理我们可以算出一个高度为3的B+树可以存放：1638163858=155616552条这样的记录。
高度为4的B+树可以存放：
163816381638*58=254899912176条这样的记录。

而在实际应用中，大部分是以bigint作为主键的，主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。
根据同样的原理我们可以算出一个高度为2，3，4的B+树能够存放的记录数。

图片: https://uploader.shimo.im/f/r30JaabtZbKTYDYj.png

参考

数据库ACID四大特性及脏读，不可重复读，幻读，事物丢失
多版本并发控制(MVCC)在分布式系统中的应用https://coolshell.cn/articles/6790.html
InnoDB MVCC 机制，看这篇就够了https://cloud.tencent.com/developer/article/1454636
官网MVVC：https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html
阿里数据库月报：http://mysql.taobao.org/monthly/
MySQL-InnoDB-MVCC多版本并发控制：https://segmentfault.com/a/1190000012650596
浅谈聚簇索引和非聚簇索引的区别https://my.oschina.net/xiaoyoung/blog/3046779
官网对于聚簇索引和非聚簇索引的解释https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html