MySQL原理

架构分层

Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

1. 查询缓存：sql为key，结果为value保存在内存，高版本已经放弃查询缓存。

2. 连接器：
   负责跟客户端建立连接、获取权限、维持和管理连接。
   权限修改后，要重新建立连接才生效。
   连接是比较重的资源，避免频繁创建或销毁，尽量使用长连接。

3. 分析器：解析器通过关键字将SQL语句进行解析，并生成对应的解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。预处理器则根据一些MySQL规则进行进一步检查解析书是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

4. 优化器：查询优化器会将解析树转化成执行计划。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是找到这其中最好的执行计划。
   逻辑优化：即根据关系代数规则，对SQL语句进行等价变化:
   对投影、选择等操作进行句式优化;
   对条件表达式进行谓词优化、条件化简;
   对连接语义进行外连接、嵌套连接的优化;
   对集合、GROUP BY、ORDER BY等优化
   子查询优化、视图重写、语义优化
   物理优化：io+cpu最低

5. 执行器：
   判断表的操作权限。
   操作存储引擎提供的接口，读写数据，索引等。

执行流程

先undo 后redo prepare 再binlog 最后redo commit

start transaction;
update t_user set name = '路人甲Java' where user_id = 666;
update t_user set name = 'javacode2018' where user_id = 888;
commit;

1. mysql收到start transaction后，生成一个全局的事务编号trx_id，比如trx_id=10
   user_id=666这个记录我们就叫r1，user_id=888这个记录叫r2
2. 找到r1记录所在的数据页p1，将其从磁盘中加载到内存中
3. 在内存中对p1进行修改
4. 将p1修改操作记录到redo log buffer中
5. 将p1修改记录流水记录到binlog cache中
6. 找到r2记录所在的数据页p2，将其从磁盘中加载到内存中
7. 在内存中对p2进行修改
8. 将p2修改操作记录到redo log buffer中
9. 将p2修改记录流水记录到binlog cache中
10. mysql收到commit指令
11. 将redo log buffer携带trx_id=10写入到redo log文件，持久化到磁盘，这步操作叫做redo log prepare，内容如下

1.start trx=10;
2.写入rb1
3.写入rb2
4.prepare trx=10;
注意上面是prepare了，不是之前说的end了。

12. 将binlog cache携带trx_id=10写入到binlog文件，持久化到磁盘
13. 向redo log中写入一条数据：end trx=10;表示redo log中这个事务完成了，这步操作叫做redo log commit

返回给客户端更新成功

Buffer Pool

优先使用free链表维护的空闲节点中描述数据指向的缓存页进行存储，存入数据后将节点放入flush链表。从磁盘加载一个数据页到缓存页的时候，就把这个缓存页的描述数据块放到LRU链表头部去。当free链表使用完后根据lru链表找到尾部数据，将数据刷入磁盘。

怎么知道数据页是否被缓存？
数据库中有一个 数据页缓存哈希表，用表空间号+数据页号，作为一个key，然后缓存页的地址作为value
表空间号+数据页号 = 缓存页地址

什么是脏缓存页？
被更新过的缓存页，数据和磁盘上的数据不一致，所以是脏缓存页
脏缓存页的数据是要刷到磁盘上的

日志

redo日志

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的，并且是事务的记录是顺序追加的，性能非常高(磁盘的顺序写性能逼内存的写性能差不了太多)

InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机IO。InnoDB假设使用常规磁盘，随机IO比顺序IO昂贵得多，因为一个IO请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。

InnoDB用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务。

MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1。但注意，这个变量只是控制commit动作是否刷新log buffer到磁盘。

当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。

undo log日志

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

bin log日志

MySQL的二进制日志（binary log）是一个二进制文件，主要记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的所有操作。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。

恢复（recovery）：某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。

复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。

审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

事务

原子性 （Atomicity）

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做。

原子性的实现利用Innodb的undo log。 undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。

隔离性 (Isolation)

隔离性是指多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

隔离性的实现利用的是锁和MVCC机制。MVCC,即多版本并发控制(Multi Version Concurrency Control),一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。 如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。

在事务隔离级别为读已提交(Read Commited)时，一个事务能够读到另一个事务已经提交的数据，是不满足隔离性的。但是当事务隔离级别为可重复读(Repeateable Read)中，是满足隔离性的。

持久性 (Durability)

持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

持久性的实现利用Innodb的redo log（重做日志）。正如之前说的，Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。
所以希望事务提交前直接把数据写入磁盘，但是每次都直接写入磁盘整个页面效率低且为随机io

redo log进行刷盘比对数据页刷盘效率高，具体表现如下：
redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

一致性 (Consistency)

存储引擎

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

比较

事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。

并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

外键：InnoDB 支持外键。

备份：InnoDB 支持在线热备份。

崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

其它特性：MyISAM 支持压缩表和空间数据索引。

索引

索引数据结构

B+ Tree

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，所有叶子节点位于同一层。并且通过顺序访问指针来提高区间查询的性能。在 B+ Tree 中，一个节点中的 key 从左到右非递减排列。

与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，这是因为使用 B+ 树访问磁盘数据有更高的性能。

（一）B+ 树有更低的树高

平衡树的树高 O(h)=O(logdN)，其中 d 为每个节点的出度。红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多。

（二）磁盘访问原理

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。

如果数据不在同一个磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。

（三）磁盘预读特性

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。

聚簇索引

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

聚簇索引的优势在哪？

• 由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

• 辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。

• 聚簇索引适合用在排序的场合，非聚簇索引不适合

• 取出一定范围数据的时候，使用聚簇索引

  当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序；不仅如此，新增加记录的uuid可能会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

其他索引

哈希索引
哈希索引能以 O(1) 时间进行查找，但是失去了有序性：
无法用于排序与分组；
只支持精确查找，无法用于部分查找和范围查找。
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。
全文索引
MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。
查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
空间数据索引
MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。
必须使用 GIS 相关的函数来维护数据。

索引的使用

独立的列
在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
例如下面的查询不能使用 actor_id 列的索引：

多列索引
在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。

索引列的顺序
让选择性最强的索引列放在前面。
索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

覆盖索引
索引包含所有需要查询的字段的值。

索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

前缀索引
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前缀长度的选取需要根据索引选择性来确定。如果前缀选择性不强，可考虑倒置存储，使用”后缀“来作为索引，或者hash。但是使用了前缀索引就不能利用覆盖索引了

性能优化

无法利用索引的情况

对索引值进行函数操作
加减或者其他函数操作进行
查询语句中使用LIKE关键字且第一个字符为%
在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用

查询语句中使用复合索引但未使用第一个字段
只有查询条件中使用了这些字段中的第一个字段，索引才会被使用（最左前缀原则&自动调整）

查询语句中使用OR关键字且其中一个条件无索引
查询语句只有OR关键字时，如果OR前后的两个条件的列都有索引，那么查询中将使用索引。如果OR前后有一个条件的列没有索引，那么查询中将不使用索引

条件查询，不使用=（!=或者<>）从使用字段开始失效
查询为多个条件查询，当使用复合索引时如果复合索引第一个条件为等值，第二个条件为!=或者<或者>，第三个条件为等值，只能利用第一个条件的索引

使用is not null或者数据量太少，数据占比过大，索引区分效果差时
数据量很少，或者索引区分效果很差时会被优化为全表查询

字符串不加单引号会导致索引失效
数据库进行隐式转换

order by 使用 filesort

https://www.cnblogs.com/wangxusummer/p/5329813.html
使用index用上了索引，否则需要内存排序Using filesort
order by中的索引顺序不符合前缀
https://blog.csdn.net/weixin_43244698/article/details/122342791

只使用order by切数据过多

对索引列同时使用了ASC和DESC
通过where语句将order by中索引列转为常量，则除外

where语句与order by语句，使用了不同的索引

where语句中使用了条件查询

语句优化

深分页

日常做分页需求时，一般会用limit实现，但是当偏移量特别大的时候，查询效率就变得低下。

select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;

Mysql会查询出100010条数据再去掉前100000条，期间可能也回表100010次

减少回表次数
子链接

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10;

join连接

SELECT  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 10) AS  acct2 on acct1.id= acct2.id;

标签记录法
记录上一页的末尾id，直接走聚簇索引，但是需要id有序

分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联

select  id,name,balance FROM account where id > 100000 order by id limit 10;

Explain

id

id值越大，越先执行，相同id从上往下顺序执行

select_type

SIMPLE：简单查询，不包含子查询或Union查询
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为主查询
SUBQUERY：在select或where中包含子查询
DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。MySQL5.7+ 进行优化了，增加了derived_merge(派生合并)，默认开启，可加快查询效率
UNION：若第二个select出现在uion之后，则被标记为UNION
UNION RESULT：从UNION表获取结果的select

type字段

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL //最好到最差
NULL：MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引
const：表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体。
ref_or_null：类似ref，但是可以搜索值为NULL的行
index_merge：表示使用了索引合并的优化方法
range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在where语句中出现between、<>、in等的查询。
index：Full index Scan，Index与All区别：index只遍历索引树，通常比All快，因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。
ALL：Full Table Scan，将遍历全表以找到匹配行

table字段

数据来自哪张表

possible_keys字段

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用

key字段

实际使用到的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引(查询的列刚好是索引)，则该索引出现在key列表

key_len字段

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len显示的值为索引字段最大的可能长度，并非实际使用长度。即key_len是根据定义计算而得，不是通过表内检索出的。索引字段长度总和+允许null1+varchar动态2

ref字段

显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

rows字段

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

partitions字段

匹配的分区

filtered字段

查询的表行占表的百分比

Extra字段

包含不适合在其它列中显示但十分重要的额外信息
Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary：使用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于排序order by 和分组查询group by
Using index：表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
Using where：意味着mysql服务器将在存储引擎检索行后再进行过滤。
Using join buffer：使用了连接缓存
impossible where：where子句的值总是false，不能用来获取任何元组
distinct：一旦mysql找到了与行相联合匹配的行，就不再搜索了
Select tables optimized away：MySQL根本没有遍历表或索引就返回数据了

并发一致性问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。

丢失修改

丢失修改指一个事务的更新操作被另外一个事务的更新操作替换。一般在现实生活中常会遇到，例如：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改并提交生效，T2 随后修改，T2 的修改覆盖了 T1 的修改。

读脏数据

读脏数据指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：T1 修改一个数据但未提交，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

不可重复读

不可重复读指在一个事务内多次读取同一数据集合。在这一事务还未结束前，另一事务也访问了该同一数据集合并做了修改，由于第二个事务的修改，第一次事务的两次读取的数据可能不一致。例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

幻影读

幻读本质上也属于不可重复读的情况，T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

隔离级别

未提交读（READ UNCOMMITTED）

事务中的修改，即使没有提交，对其它事务也是可见的。

提交读（READ COMMITTED）

一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

可重复读（REPEATABLE READ）

保证在同一个事务中多次读取同一数据集合的结果是一样的。

可串行化（SERIALIZABLE）

强制事务串行执行，这样多个事务互不干扰，不会出现并发一致性问题。
该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。

可解决问题

锁类型

读写锁

互斥锁（Exclusive），简写为 X 锁，又称写锁。
共享锁（Shared），简写为 S 锁，又称读锁。
有以下两个规定：

一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。
一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁

意向锁

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。
通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

MVCC

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

基本思想

加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的，而 MVCC 利用了多版本的思想，SELECT 操作是快照中的数据，不需要进行加锁操作，其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。读为快照读，写为当前读，没有互斥关系，这一点和 CopyOnWrite 类似。当然读也可以通过for update 和in share mode 强制加读写锁。

脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读。

版本号

系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号 TRX_ID ：事务开始时的系统版本号。

Undo 日志

MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

ReadView

MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, …}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。
在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：
TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。
TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。
在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

Next-Key Locks

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。
MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。
Record Locks
锁定一个记录上的索引，而不是记录本身。
如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。
Gap Locks
锁定索引之间的间隙，但是不包含索引本身。
Next-Key Locks
它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间。

那么何时使用行锁，何时产生间隙锁？

只使用唯一索引查询，并且只锁定一条记录时，innoDB会使用行锁。
只使用唯一索引查询，但是检索条件是范围检索，或者是唯一检索但检索结果不存在（试图锁住不存在的数据）时，会产生 Next-Key Lock（临键锁）。
使用普通索引检索时，不管是何种查询，只要加锁，都会产生间隙锁（Gap Lock）。
同时使用唯一索引和普通索引时，由于数据行是优先根据普通索引排序，再根据唯一索引排序，所以也会产生间隙锁。
事务级别是RC（读已提交）级别的话，间隙锁将会失效。

可重复读加间隙锁还会幻读吗

事务A select

事务B insert

事务B commit

事务A select（与第一次select的结果一样）

事务A update 所有行

事务A select（会发现多出了事务B insert 的行）

事务A update
所以说MVCC并不能完美解决RR下的幻读问题。一旦事务A的修改操作覆盖到了其他事务插入的“幻行”，那么在下次select的时候，也会把这行数据一起查出来。属于快照读和当前读混用。

主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

数据一致性问题

“主从复制有延时”，这个延时期间读取从库，可能读到不一致的数据。
缓存记录写key法：
​ 在cache里记录哪些记录发生过的写请求，来路由读主库还是读从库

异步复制：
​ 在异步复制中，主库执行完操作后，写入binlog日志后，就返回客户端，这一动作就结束了，并不会验证从库有没有收到，完不完整，所以这样可能会造成数据的不一致。

半同步复制：
​ 当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，通过一份在主库的Binlog，另一份在其中一个从库的Relay-log，可以保证了数据的安全性和一致性。

全同步复制：
​ 指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，极大程度缓解了锁的争用；
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。

从库的数据一定会比主库慢一些，一般在读远远多于写，而且读的时候一般对数据时效性要求没那么高的时候，用 mysql 主从同步。对于那种写了之后立马就要保证可以查到的场景，采用强制读主库的方式

分库分表

为什么要分库分表

单表数据量太大，B+树层高变高，影响sql 执行的性能，5百万级别就应该考虑分表了
单库并发值你最好保持在每秒 1000 左右

未分库分表的系统，未来要分库分表

线上系统里面，之前所有写库的地方，增删改操作，都除了对老库增删改，都加上对新库的增删改，这就是所谓双写，同时写俩库，老库和新库。导数工具，跑起来读老库数据写新
库，写的时候要根据 gmt_modified 这类字段判断这条数据最后修改的时间，除非是读出来
的数据在新库里没有，或者是比新库的数据新才会写。反复几次，做双读核对直到一致开始逐步切流

水平拆分和垂直拆分

水平拆分的意思，就是把一个表的数据给弄到多个库的多个表里去，但是每个库的表结
构都一样，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全部数据。水平
拆分的意义，就是将数据均匀放更多的库里，然后用多个库来抗更高的并发，还有就是用多
个库的存储容量来进行扩容。
垂直拆分的意思，就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。
每个库表的结构都不一样，每个库表都包含部分字段。一般来说，会将较少的访问频率很高
的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去。因为数据
库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。

分库分表如何映射

hash取模
不会存在明显的热点问题，再次扩容时需要迁移
range
利于迁移，但是数据库id自增，存在热点问题
range+hash取模混合
在range内hash取模

分库分表之后引发问题

事务问题

分库分表后，假设两个表在不同的数据库，那么本地事务已经无效啦，需要使用分布式事务了

跨库关联

跨节点Join的问题：解决这一问题可以分两次查询，内存中关联

排序问题

跨节点的count,order by,group by以及聚合函数等问题：可以分别在各个节点上得到结果后在应用程序端进行合并

分布式id

UUID

简单唯一速度快，但是写入索引效率降低，一般不采用

数据库自增 id优化为双号段

单库生成自增 id，要是高并发的话，就会有瓶颈。专门开一个服务出来，这个服务每次就拿到当前 id 最大值，然后自己递增几个 id，一次性返回一批 id。然后服务端快使用一批时提前异步加载下一批。

雪花算法

符号位+时间戳+机器id+序号

符号位为0，0表示正数，ID为正数,所以固定为0。
时间戳位不用多说，用来存放时间戳，单位是ms,时间戳部分占41bit，这个是毫秒级的时间，一般实现上不会存储当前的时间戳，而是时间戳的差值（当前时间-固定的开始时间）,这样可以使产生的ID从更小值开始。
工作机器id位用来存放机器的id，通常分为5个区域位+5个服务器标识位。这里比较灵活，比如，可以使用前5位作为数据中心机房标识，后5位作为单机房机器标识，可以部署1024个节点。
序号位是自增。

机器id不便于维护，改进方案
百度UidGenerator用在启动时会往数据库表(uid-generator需要新增一个WORKER_NODE表)中去插入一条数据，数据插入成功后返回的该数据对应的自增唯一id就是该机器的workId，而数据由host，port组成。
美团Leaf中workId是基于ZooKeeper的顺序Id来生成的，每个应用在使用Leaf-snowflake时，在启动时都会都在Zookeeper中生成一个顺序Id
系统时间

时钟回拨
人为原因或者机器之间时间同步导致时钟往回，此时生成id会重复
短时间的重复可以稍微等待，或者加入拓展位，在回拨时加以区分

慢sql原因

1. 网络层丢包导致服务端感知时间较长，数据库处时长正常
2. 数据库服务器层进行高cpu高io等操作导致大量sql超时，如扩缩容、加载压缩资源
3. 数据库自身层，包括但不限于数据库的索引、隐式转换、执行计划、数据大小、元数据锁等待、刷脏页、SQL语句不合理等