使用

char表示定长字符串，插入数据长度小于char的固定长度时用空格填充，最多存放的字符个数为255且与编码无关，存取速度比varchar更快；varchar表示变长字符串，插入数据不占多余空间，存取速度比char慢，最多存放的字符个数为65532。
DATETIME(1000到9999,8字节)和TIMESTAMP(1970到2038,4字节)可以存储相同的时间类型，TIMESTAMP只使用DATETIME一半的存储空间，TIMESTAMP与时区有关，TIMESTAMP默认为当前时间CURRENT_TIMESTAMP，DATETIME的默认时间是null。
from on join where group by having select distinct order by limit

优化

微观原则

设计满足数据存储的最小类型，更小的数据类型占用更少的磁盘、内存和CPU，因而更快
尽量避免NULL，并在通常情况下指定为NOT NULL，为NULL的列需要多余的存储空间
DATETIME(1000到9999,8字节)和TIMESTAMP(1970到2038,4字节)可以存储相同的时间类型，TIMESTAMP只使用DATETIME一半的存储空间，TIMESTAMP与时区有关，TIMESTAMP默认为当前时间CURRENT_TIMESTAMP，DATETIME的默认时间是null
在where/group by/order by中用到的字段建立索引，索引字段越小越好，考虑索引建立的顺序
数据量少的表尽量避免索引、更新比较频繁的表不适合加索引、区分度低的字段列如性别不适合加索引。MySQL有一个查询优化器，当发现某个值在表的数据行中的百分比很高时，忽略索引并进行全表扫描。
使用精确列名查询而不是 *
减少子查询并用 join 代替
尽量避免长事务。长事务意味系统里面存在很老的事务视图，在事务提交前，回滚记录都要保留，导致存储空间的大量占用；此外，长事务占用锁资源，可能拖垮库。

宏观原则

基于Redis、Memcached加缓存
主从复制、读写分离
垂直拆分，根据模块的耦合程度，按照分布式系统的架构将大系统拆分
水平拆分

查询优化

当需要基于索引连表查询时，采用较为小的表为驱动表，有利于提高查询的效率，此时使用索引的join连表查询比拆分开来查表的方式好
不建议join超过三个表的查询，join的数据表越多，性能也就越差；能用索引就尽量用索引，在不使用索引且驱动表与join_buffer的比值比较大的情况下采用拆分查询以减少扫描数据表的次数。

MySQL架构

包括存储引擎层和Server层。
Server层负责建立连接、分析SQL、执行SQL、负责跨存储引擎的（触发器、视图、存储过程）功能、内置函数如（日期、时间、数学和加密函数）功能。包括连接器、查询缓存、解析器、预处理器、优化器、执行器。

存储引擎层实现索引数据结构，如InnoDB支持的B+树索引。负责数据的存储和提取。
MYSQL是基于TCP协议之上构建的应用，连接器校验用户名密码进行登录时还会获得当前用户的权限。MySQL定义了连接的最大空闲时长，由wait_timeout参数控制，默认8小时。
查询缓存在频繁更新时命中率很低。
解析器做词法解析和语法解析。对于词法解析，MySQL根据输入的字符串识别关键字，构建SQL语法树，方便后面的模块得到表名、SQL类型、字段名、where条件等等；根据词法分析的结果，语法解析器会根据语法规则。校验SQL语句是否符合语法。
预处理器检查SQL中的表和字段是否存在，将select *中的*扩展为表上的所有列
优化器为SQL制定执行计划，将SQL查询的执行方案进行确定。在表存在多个索引时，优化器基于查询成本的考虑选择使用哪个索引。
执行器与存储引擎交互真正执行SQL，在真正执行查询之前，判断对表是否有执行查询的权限，如果当前用户具备对当前表的权限，调用存储引擎提供的接口进行实际的查询。包括基于主键索引的等值查询const、基于全表扫描的查询、索引下推。
1. 一般在调用存储引擎提供的接口后，让存储引擎读取第一条记录。
2. 在全表扫描中，执行器每读到一条记录进行条件判断，不满足的跳过，满足的一条记录就先发给客户端。循环整个过程，直到存储引擎返回Server层执行器返回读取完毕的信息，执行器收到后终止循环。
3. Mysql5.6退出的索引下推能减少二级索引在查询时的回表操作，提高查询效率，主要是其将在Server层负责的部分事情交由存储引擎完成。在联合索引并存在某一索引列的范围查询场景下，不采用索引下推每次读取一个二级索引列的主键都需要回表，采用索引下推时，首先根据第一个索引列确定满足条件的第一条记录，在不返回数据的情况下首先在存储引擎层进行其余条件的过滤然后回表。同样的查询到一条记录就返回客户端一条。

InnoDB与MYISAM

InnoDB支持事务（完全支持事务的ACID特性）、外键、行级锁（最大程度支持了并发），MYISAM只支持表级锁。如果系统插入和查询操作多，不需要事务外键，选择MyISAM，MySQL5.5之前默认的存储引擎；如果系统进行频繁的更新、删除操作，需要事务外键行级锁，选择InnoDB，MySQL5.5之后。

MyISAM存储引擎由.myd（数据）和.myi（索引文件）组成，.frm文件存储表结构。

读写操作混合的不好，表级锁，写入和读互斥。
MYISAM支持的索引类型为全文索引。

InnoDB与MyISAM区别
1. InnoDB支持外键而MyISAM不支持
2. InnoDB为事务安全型的而MyISAM是非事务安全型的
3. MyISAM锁的粒度是表级，InnoDB支持行级锁定
4. MyISAM支持全文类型索引，InnoDB不支持全文索引。MyISAM支持空间数据索引和压缩表。
5. MyISAM更加简单，效率比InnoDB更高，小型应用优先考虑MyISAM
6. MyISAM表存储为文件形式，在跨平台的数据转移过程中使用MyISAM存储会省去很多麻烦
7. MyISAM管理非事务表，提供全文搜索以及高速存储和检索能力，适合应用需要执行大量的SELECT的场景；InnoDB用于事务处理应用程序，具有众多特性，包括事务ACID特性，由于支持行锁，大量的UPDATE或INSERT操作应使用InnoDB，以提高多用户并发操作的性能。
8. InnoDB支持在线热备份，其他存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。
9. InnoDB内部做了很多优化，包括从磁盘读取数据时的可预测性读（基于计算机科学理论中的局部性原理，预读的过程，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，速度很快），能够加快读操作并且自动创建的自适应哈希索引，能够加速插入操作的插入缓冲区。
Buffer Pool

MySQL会为Buffer Pool申请一片连续的内存空间，MySQL刚启动时，可以看到使用的虚拟内存空间很大，使用到的物理内存空间很小，因为只有虚拟内存被访问时，操作系统才会触发缺页中断申请物理内存，并将虚拟内存与物理内存建立映射关系。

冷热链表的原因：查询一个比较大的数据时，可能会占满所有的Buffer Pool空间并将所有Buffer Pool数据淘汰，此时大量查询全部作用于磁盘，导致磁盘I/O增大，数据库反应缓慢。

Buffer Pool除了缓存数据页和索引页，还包括了插入缓存页、undo页、自适应哈希索引、锁信息

自适应哈希索引：有InnoDB存储引擎以索引监控机制自动创建，即当InnoDB监控到自适应哈希索引可以提高查询速度时自动创建。当然如果查询条件为 like '%X'则采用全表扫描

索引

什么是索引？

索引可以看做数据目录，是帮助存储引擎快速获取数据的一种数据结构。主索引是聚簇索引，在索引中保存了数据，避免直接读取磁盘，对查询性能有很大的提升。

假设我们要设计索引，需要考虑一个是能在尽可能少的磁盘I/O操作中完成查询，一个是要能高效的支持单条记录和范围记录的查找。
将所有记录分组，将组号存储到页目录，起到索引作用

分类

按数据结构分类

B+Tree索引
1. B+Tree是平衡的，即所有叶子结点具有同样的深度。叶子结点存放数据，非叶子结点存放索引，每个节点里的数据按照主键顺序存放，每一层父节点的索引值会出现在下一层的子节点索引值中。每一个叶子结点会指向下一个叶子结点构成一个单链表。B+Tree相比于B树和二叉树来说，最大的优势在于查询效率很高，即使在数据量很大的情况下，查询一个数据的磁盘I/O依然维持在3-4次。
2. B+Tree只在叶子结点存储数据，B树的非叶子结点也要存储数据，所以B+Tree的单个结点的数据量更小，相同的磁盘I/O次数下可以查询更多的节点；此外B+Tree叶子结点采用双链表连接，适合MySQL中常见的基于范围的顺序查询，B树无法做到
3. 与二叉树相比，B+Tree允许的最大子节点的个数可以为多个，即使在数据量千万级时，B+Tree的高度依然维持在3~~4层，即一次数据查询只做3~~4次磁盘I/O就能查询到目标数据，二叉树决定了每个节点的子节点个数为2，数据量大时树的高度会显著增加。
4. 与红黑树比，红黑树存在二叉树树深度过大的问题，
5. Hash索引只适合等值查询不适合范围查询，适用范围更窄。
如果有主键，InnoDB默认使用主键作为聚簇索引的索引键，主键索引的B+Tree的叶子结点按主键顺序存放了完整的用户记录，二级索引的B+Tree的叶子结点存放主键值；如果没有主键，选择第一个不包含NULL值的唯一列作为聚簇索引的索引键；在前两者都不满足的前提下，InnoDB自动生成一个隐式自增id列作为聚簇索引的索引键。

InnoDB在某个索引值被使用的非常频繁时，在B+Tree索引之上创建一个自适应哈希索引。
Hash索引：避免B+Tree索引逐层查找，具有更高的效率。但是只是在等值查询的时候效率高，不适合范围查询
Full-text索引：只有MyISAM支持

按物理存储分类

聚簇索引（主键索引）
二级索引（辅助索引）

按字段特性分类

主键索引：建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
唯一索引：建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，允许空值。
前缀索引：对字符类型字段的前几个字符建立索引，前缀索引减少了索引占用的存储空间，提升查询效率。
普通索引：建立在普通字段上的索引，不要求字段为主键，不要求字段UNIQUE。

按字段个数分类

单列索引：主键索引就为单列索引
联合索引：联合索引的非叶子节点值用两个字段的值作为B+Tree的key值，使用联合索引时，存在最左匹配原则。注意最左匹配原则，指的是查询条件必须依次包含联合索引中顺序的字段，但是具体查询语句中字段的顺序可以调整；此外如果联合索引字段查询时前面的字段采用了范围查询，那么后续的字段无法保证局部有序也会导致不能正常使用联合索引。

联合索引的最左匹配原则，在遇到范围查询（>、<、between、like 包括like '林%'这种）的时候，就会停止匹配，也就是第一个范围列及之前可以用到联合索引，但是范围列后面的列无法用到联合索引。对于联合索引查询，某一字段作为范围列查询时，MySQL5.6之前只能根据第一个查询条件得到的主键值依次回表，根据主键索引查询到记录后再筛选；MySQL5.6引入索引下推优化，可以在联合索引遍历的过程中，对联合索引包含的字段先做判断，直接过滤掉不满足条件的记录，然后再进行回表查询减少回表次数。

建立联合索引时，尽可能将区分度大的列放到前面，将最常用作限制条件的列放在最左边。

索引优化与失效

索引优化

能在二级索引的B+Tree就能查询到结果，避免了先检索二级索引的B+Tree的叶子结点获取主键值再回表查询主键索引叶子结点完整记录的代价，这个称作覆盖索引。
字符串列尽量建立前缀索引。但注意order by无法使用前缀索引
主键字段尽量短，主键字段长度越小，二级索引的叶子结点越小，二级索引占用的空间也越小。
只为搜索、排序、分组的列建立索引。
只为数据识别度高的列建立索引，字段由唯一性限制的适合建索引。
为了让页尽量减少页分裂的情况，主键设置自增。自增主键使每次新插入的数据按顺序添加到当前索引节点的位置，不需要移动数据来满足新数据的插入，而将一个复制数据到另一个页面的情况称为页分裂，页分裂可能造成大量的内存碎片，导致索引结构不紧凑，影响查询效率。
索引最好设置为NOT NULL。索引列存在NULL导致优化器在做出索引选择时更加复杂更加难以优化；NULL需要NULL值列表等额外的空间存储
防止下面的索引失效

索引失效

where子句中，如果or前的条件是索引列，or后的条件是非索引列，那么索引失效
联合索引，如果列不是联合索引的第一列，不符合最左前缀原则，索引失效
如果like以%，该列索引不会使用
在查询条件中，对索引列做了表达式计算、函数、类型转换等操作会导致索引失效
索引字段上使用is null、is not null，可能会导致索引失效
mysql估计使用全表扫描比使用索引快，导致索引失效

索引不适合的场景

数据量少的表
数据区分度小的列
更新频繁的列

索引带来的问题

占用物理空间，数据量越大，占用空间越大
创建索引和维护索引耗费时间，随数据量的增大而增大
每次增删改索引，B+树为了动态维护索引的有序性降低了表增删改的效率

日志

MySQL修改数据的过程：
1. 将磁盘中的数据读取到内存 Buffer Pool中
2. 将Buffer Pool中的数据分别读取到undo log和redo log中
3. 用户发起修改数据请求，MySQL首先修改Buffer Pool中的数据
4. 修改redo log中的数据
5. 进行数据处理，这里分三种情况：
  1. 事务正常提交，将Buffer Pool中的数据刷新到硬盘中并修改 undo log，以保证数据库各个模块之间的数据一致性
  2. 事务出现故障，将redo log中的最新数据还原到Buffer Pool中，如果事务正常提交，则重新进行第 1 步操作
  3. 事务回滚，使用undo log中保存的数据修改Buffer Pool和redo log。

redo log（InnoDB）

对于redo log的写入，先写入redo log buffer按照一定条件顺序写入redo log file，redo log buffer向redo log file写是按512字节也就是一个扇区大小进行写入。redo log buffer是innodb存储引擎的内存的一部分，默认大小是16MB

事务在提交前将内存中数据刷新到磁盘避免断电后内存的数据丢失，但为了避免每次修改少量的数据就刷新整个数据页到磁盘以及为了避免涉及到多个数据页修改时的随机I/O，MySQL事务的持久性基于redo log实现。

redo log是物理日志保存在硬盘中不会因数据库的故障导致数据丢失。MySQL对数据做修改时，不仅对内存中操作，并且对内存中的redo log操作，每次提交事务时将内存中的redo log进行刷盘。具体说来，事务在执行的过程中，MySQL首先将修改记录到redo log buffer中，即MySQL物理内存中；然后将数据记录在文件系统的page cache中，此时并未持久化；在合适的时机，page cache中的数据持久化到磁盘。

redo log作用 MySQL采用了写前日志技术，MySQL的写操作并不是立刻更新到磁盘而是先记录在日志上，然后在合适的时间再更新到磁盘。写前日志技术使得MySQL的写操作并不是立刻更新在磁盘上，而是先记录在日志中并在合适的时间刷新到磁盘，将写操作从磁盘的随机写变成了顺序写，提升了语句的执行性能。

redo log写的方式

redo log采用循环写的方式，以write pos表示redo log当前记录写到的位置，checkpoint表示当前要擦除的位置。两个指针都采用顺时针方向移动，write pos~~checkpoint用来记录新的更新操作，checkpoint~~write pos表示当前未落盘的脏数据。当write pos追上了checkpoint表示redo log已满，MySQL不能在执行新的更新操作，MySQL会被阻塞，并发环境下需要增大redo log以提升并发。

redo log日志格式

占用1字节的redo_log_type，表示重做日志类型
space：表空间的ID，采用压缩格式，占用字节可能小于4字节
page_no：页的偏移量
redo_log_body：每个redo log的数据部分，恢复时调用相应的函数解析

redo log刷盘时机

innodb_flush_log_at_trx_commit=0表示事务提交时，只把redo log留到redo log buffer中，mysql崩溃导致上一秒钟所有的事务数据丢失；默认是innodb_flush_log_at_trx_commit=1，每次事务提交时直接将redo log持久化到磁盘；innodb_flush_log_at_trx_commit=2，每次事务提交时只是把redo log写入page cache，OS宕机导致上一秒钟所有的事务数据丢失。

如果把innodb_flush_log_at_trx_commit设置为1，那么redo log在prepare阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于prepare的redo log外加bin log恢复，具体说来共三个阶段prepare、写binlog、commit。如果在commit之前数据库崩溃，虽然没有commit，但满足prepare和binlog完整，重启之后会自动commit
InnoDB有一个后台线程，每隔1秒就把redo log buffer中的日志，调用write写入到文件系统的page cache，然后调用fsync持久化到磁盘；
redo log buffer即将到达innodb_log_buffer_size 1/2时，redo log buffer写入到文件系统的page cache。
并行事务发生时，B事务提交时会将A事务进行了一部分写入的redo log buffer一起刷新到磁盘。
MySQL正常关闭时

redo log特点

redo log只记录了哪一页修改的内容，体积小，刷盘快；redo log一直在尾部追加，属于顺序I/O，比随机I/o快。

redo log基于固定大小空间的文件实现，且采用循环写入方式。

binlog

事务执行过程中，先把日志写入binlog cache，事务提交时再把binlog cache写入binlog文件中，这里的吸入binlog文件仍然是写入Page Cache，需要再次调用fsync()才能真正写入磁盘。

默认sync_binlog=0表示每次提交事务都只write到page cache中不fsync，后续交由操作系统决定何时将数据持久化到磁盘；sync_binlog=1表示每次提交事务都会write然后立即执行fsync；sync_binlog=N表示每次提交事务都write，但是累计N个事务之后彩之星fsync

binlog（Server）以及与 redo log的区别

binlog只用于归档，基于redo log才实现了故障恢复的能力；使用binlog完成备份恢复、主从复制；binlog采用追加写入，binlog文件写到一定大小后会切换下一个且不会覆盖以前的日志

binlog是MySQL的Server层实现的日志，redo log是InnoDB存储引擎层实现的日志
binlog 记录事务的具体操作内容，是逻辑日志，redo log记录每个页更改的情况，诸如XXX表空间的YYY页的ZZZ偏移量做了AAA更新，是物理日志
binlog是追加写，保存了全量的日志，写满一个文件就创建一个新的文件写入不会覆盖之前的数据；redo log循环写，日志空间大小是固定的，保存未被刷入磁盘的脏页数据
binlog用于主从复制、备份恢复；redo log用于掉电等故障恢复
redo log在prepare阶段就刷盘一次，事务执行过程中的redo log也是写在redo log buffer中，而缓存在redo log buffer中的重做日志被后台线程每隔1秒刷入磁盘一次，所以在事务未提交前就可以持久化到磁盘；binlog必须在事务提交后才可以持久化到磁盘

主从复制

主从复制过程是异步的，主库上执行事务操作的线程不会等待复制binlog的线程同步完成

从库数量增加，主库需要创建更多的dump线程来连接I/O线程以处理复制请求，这对主库资源消耗高且受限于主库的网络带宽

MySQL主库收到客户端事务提交的请求后，先写入binlog再提交事务，更新存储引擎中的数据，事务提交成功后返回给客户端成功的响应
从库创建专门的I/O线程连接主库的log dump线程接收主库的binlog，再把binlog信息写入中继日志relay log中，返回主库复制成功的响应
从库创建一个用于回放binlog的线程，读取中继日志relay log，回放binlog更新存储引擎中的数据以实现主从的数据一致性

undo log（InnoDB）

undo log记录了要回滚的日志信息，事务回滚时能够撤销所有已经成功执行的SQL语句。

对于读提交和可重复读隔离级别的事务而言，其快照读（普通的select语句）是基于Read View+Undo Log实现的MVCC做到的，具体说来是根据Read View中的信息顺着undo log的版本链找到满足其可见性的记录。

undo log、binlog、redo log的执行时机

开启事务，InnoDB层首先记录修改Undo Log的redo log，
然后真正在内存修改undo页面，即将未做日志写入Buffer Pool的undo log页面；
一条语句更新完开始记录语句对应的binlog，此时的binlog会记录到binlog cache，在事务提交时统一将该事务运行过程中的所有binlog刷新到硬盘。

两阶段提交

两阶段提交保证了多操作之间的原子性。有点类似锁

保证binlog和redo log都能持久化到磁盘，避免出现半成功状态导致主从同步数据不一致。

在MySQL的InnoDB存储引擎中，MySQL使用了内部XA事务保证binlog与redo log的一致性，其中binlog是协调者，存储引擎是参与者。具体说来，事务的提交由两个阶段，将redo log的写入拆分成prepare和commit两个步骤，中间穿插写入binlog。

prepare阶段，将内部XA事务的XID写入redo log，同时将redo log的事务状态设置为prepare，然后将redo log刷新到硬盘
commit阶段，将XID写入binlog并将binlog刷新到磁盘，接着调用存储引擎的提交事务接口，将redo log状态设置为commit。

MySQL异常重启后，顺序扫描redo log文件，碰到处于prepare状态的redo log，就找是否存在于redo log XID一致的binlog：

如果binlog中没有当前内部XA事务的XID，说明redo log完成刷盘但是binlog还没有刷盘，回滚事务
如果binlog中有当前内部XA事务的XID，说明redo log和binlog都已经完成刷盘，提交事务

组提交

MySQL引入了binlog组提交机制，当有多个事务提交时，会将多个binlog刷盘操作合并为一个从而减少磁盘I/O次数。

在引入组提交机制后，prepare阶段不变，commit阶段分为三步：

flush 阶段：多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）；
sync 阶段：对 binlog 文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）；
commit 阶段：各个事务按顺序做 InnoDB commit 操作；

每个阶段都有一个队列，每个阶段有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。

锁

MySQL内部的锁机制主要包括全局锁、表级锁和行锁三种。

全局锁

对整个数据库实例加锁，使整个库处于只读状态。对整个库加锁导致对数据的增删改、对表结构的更改等操作都会被阻塞。
风险：主库备份期间不能更新，业务停摆；从库备份期间不能执行主库同步的binlog，导致主从延迟。

为了解决数据备份时对业务影响，对于支持可重复读隔离级别的数据库，mysqldump 时加上 –single-transaction，在备份数据库之前开启事务，整个事务在执行期间使用事务开启时的Read View，基于MVCC的支持，可以在主从备份期间保持业务更新。
用于全库逻辑备份场景，不会因为表结构或数据的更新出现备份文件的数据与预期的不一样。

表级锁

MySQL中的表级锁分为表锁、元数据锁（MDL）、意向锁、AUTO-INC锁。

表锁

表锁的语法是lock tables ... read/write；

元数据锁MDL

MDL不需要显式使用，在访问一个表时自动加上，其作用是保证读写正确性。对表做增删改查加MDL读锁，对表做结构变更操作加MDL写锁，读锁之间不互斥，读锁之间、读写锁之间互斥以保证变更表结构操作的安全性。

MDL直到事务提交才会释放，避免做表结构更改时锁住线上查询和更新，这里首先要避免DDL的表处于长事务中。申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现MDL写锁等待会阻塞后续该表的所有CRUD操作。

意向锁

当执行插入、更新、删除操作前，先对表加上意向独占锁，然后对该记录加独占锁；普通的select是不会加行级锁的，普通的select语句利用MVCC实现一致性读，事务锁的。

  //先在表上加上意向共享锁，然后对读取的记录加共享锁
  select ... lock in share mode;

  //先表上加上意向独占锁，然后对读取的记录加独占锁
  select ... for update;

意向锁的目的是为了快速判断表里是否有记录被加锁

行锁

行锁分为三种类型，其中Record Lock表示仅把一条记录锁上的记录锁；Gap Lock表示仅锁定一个范围且不包含记录本身的间隙锁；Next-Key Lock为Record Lock+Gap Lock，同时锁定记录和范围。

事务

满足ACID特性的一组操作，Commit提交，Rollback回滚。MySQL默认AutoCommit，每个查询被当做一个事务自动提交。

ACID

A 原子性是指事务是一个不可分割的操作单位，事务中的操作要么全部成功要么全部失败。MySQL原子性由undo log保证，undo log记录着修改前的数据，结合事务操作过程中发生的异常来回滚。

数据库在执行的时候分为多个步骤，将数据加载到内存的页面，对页面进行修改，将内存中的页面数据刷到磁盘，如果是多个操作还必须保证不断电、操作系统不出错的情况下完整的执行完所有的操作。
C 一致性是指数据库从一个一致性状态变换为另一个一致性状态。转账中，A和B在初始2000前提下，不滚A和B之间如何转，最终状态仍保持2000。此外一致性还要求事务执行前后的状态要满足合法的预定约束。
1. 只有满足一致性，事务执行结果才是正确的；无并发环境下，事务串行执行，隔离性一定满足，此时只要满足原子性就可达到一致性；并发环境下，多个事务并发执行，需要同时满足原子性和隔离性，一致性才可满足。整体上说，数据库必须要满足AID三大特性才有可能保证一致性。
2. 从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据。
I 隔离性是指并发环境下存在多个事务时，一个事务不会受其他事务干扰。

隔离性依靠MySQL的MVCC和各种锁实现，4种隔离级别：read uncommited、read commited、repeatable read、serializable。
1. Serializable串行化，所有事务一个接一个执行，对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询前的时候需要先获取范围锁。不是基于锁实现并发控制的数据库，对于由违反串行操作的事务进行回滚操作。
2. Repetable Read可重复读，所有Select获取的数据都不能修改，避免了一个事务前后读取数据不一致的情况。主流的数据库基于MVCC并发控制，此时RR隔离级别不会出现幻读。
3. Read Commited读已提交，被读取的数据可以被其他事务修改

并发事务带来的问题：脏读、不可重复读、幻读

脏读：无效数据读出，一个事务读取另一个事务还没有提交的数据。T1修改一个数据，T2读取这个修改，T1撤销修改，T2读取到的数据即脏数据。提高隔离级别至Read Commited。
不可重复读：并发事务操作，T1前后读取了T2操作时和提交后的同一份数据，不可重复读指的是在同一个事务内，两个相同的查询返回了不同的结果。提高隔离级别至Repeatable Read。
幻读：并发事务操作，一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行，重点在于新增和删除，解决方法是提高事务隔离级别至Serializable。

注意

InnoDB默认在可重复读隔离级别下的普通查询采用快照读，看不到其他事务插入数据的过程。

InnoDB在可重复读的隔离级别下使用了Next-Key Lock锁算法（行锁和间隙锁的组合）锁住记录之间的间隙和记录本身，防止其他事务在这个记录之间插入新记录，避免了可重复读隔离级别下使用当前读时的幻读现象。

不要执行不带索引的update语句，导致业务停滞 Next-Key Lock算法锁的是索引而不是数据本身，如果update语句的where条件没有用到索引列就会全表扫描，在一行行的扫描过程中，不仅加了行锁还加了间隙锁，相当于锁住整个表并直到事务结束才会释放锁。
D 持久性，一旦事务提交，其所做的修改将会会永久保存在数据库中。即便系统发生崩溃，事务执行结果不会丢失。持久性基于Redo log实现。

MVCC

读未提交，直接返回记录上的最新值；串行化，通过加读写锁的方式避免并行访问；读提交和可重复读基于Read View实现，区别在于创建Read View的时机不同，读提交在每个语句执行前重新生成一个Read View，可重复读是启动事务时生成一个Read View并且整个事务期间都在用此Read View。

Read View

MVCC多版本并发控制，通过版本链的方式控制并发事务访问同一个记录时的行为。ReadView包括四个重要字段：活跃事务的id列表、活跃事务中id最小的事务、创建ReadView时数据库应该给下一个事务的id值、创建ReadView的事务id；此外每一个事务对某条聚簇索引记录修改时，事务id记录在trx_id隐藏列中。访问记录时，如果trx_id小于最小id说明事务已经在创建ReadView前提交，对当前事务可见；如果trx_id大于最大事务id则说明在ReadView后提交，对当前事务不可见；如果事务id在最小事务id和最大事务id之间，检查是否在活跃事务列表中，如果不在说明事务已经提交可见，否则不可见。

关键字的实现

count

count是统计符合查询条件的记录中，函数指定的参数不为NULL的数量

由高到低：count(*)>=count(1)>count(主键字段)>count(普通字段)，

count(*)相较于count(1)做了一定的优化，在意义相同的情况下读取较小的B+树。
count(1)查询符合条件的数据，每一行数据加1且不取值。
count(主键字段)将指定数据中的所有ID取出并返回SQL执行器，然后再进行判断并累加。

如果count(字段)中的字段是一个NOT NULL字段，那么count(*)、count(1)、count(主键字段)、count(普通字段)得到的最终结果是相同的。

count(*) MyISAM把一个表的总行数存放磁盘，对于没有过滤条件的count(*)，执行count(*)会直接返回个数，效率很高。

InnoDB引擎执行count(*)时，将数据一行一行的从引擎里面读取然后计数。
即使是在同一时刻多个查询，InnoDB应该返回多少行也是不确定的。MySQL默认隔离级别RR基于版本并发控制MVCC实现，每一行记录都要判断自己是否对当前会话可见。
InnoDB是索引组织表，主键索引树的叶子结点是数据，普通索引树的叶子结点数据是主键值，普通索引树比主键索引树小很多，对count(*)这类遍历哪个索引树得到逻辑结果都是一样的操作，MySQL会尽量减少扫描的数据量，选择较小的树遍历。

order by

为了减少排序所产生的随机I/O，将需要查询的所有字段全部存放于sort_buffer中，当排序完成后不需要从数据库中再次拿数据直接返回即可。

sort_buffer_size是MySQL为排序开辟的内存(sort_buffer)大小，如果要排序的数据量小于sort_buffer_size，排序就在内存中完成，如果排序数据量太大，内存放不下，就需要借助磁盘临时文件辅助排序，主要使用归并排序算法。

explain得到的执行计划

type: 在表中找到所需行的方式，又称为“访问类型”，包括ALL，index，range，ref，eq_ref，const，system，NULL（从左到右，性能）
key：显示MySQL实际决定使用的索引

MySQL

使用

优化