事务的基本要素
原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
一个支持事务（Transaction）的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性,交易过程极可能达不到交易方的要求。
原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性：两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。
持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
事务的并发问题
脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
事务隔离级别
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交（read-uncommitted） | 是 | 是 | 是 |
不可重复读（read-committed） | 否 | 是 | 是 |
可重复读（repeatable-read） | 否 | 否 | 是 |
串行化（serializable） | 否 | 否 | 否 |
mysql默认可重复读（repeatable-read）
MVCC多版本并发控制
MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
MVCC就是为了实现读（快照读）-写冲突不加锁
当前读
像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读
不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
实现原理
依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。
隐式字段
每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID等字段
DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前操作该记录的事务ID,而DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本
undo日志
undo log主要分为两种：
insert undo log
代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
update undo log
事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录
Read View(读视图)
事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)
Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。
Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID，那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本
binlog、redolog、undolog区别和作用
redo log
redo log是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。
在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中，这里涉及到WAL即Write Ahead logging技术，他的关键点是先写日志，再写磁盘。
有了redo log日志，那么在数据库进行异常重启的时候，可以根据redo log日志进行恢复，也就达到了crash-safe。redo log日志的大小是固定的，即记录满了以后就从头循环写。
确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。
binlog
binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有crash-safe能力的。
用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。
redo log和binlog区别
- redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
- redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑
- redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
- binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。
undolog
undo log是回滚日志，提供回滚操作。
保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读
undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：
- redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
- undo log用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。
InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
- 在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
- 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
- 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
- 即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
myisam和innodb的区别，什么时候选择myisam
InnoDB
- 支持事务处理等
- 不加锁读取
- 支持外键
- 支持行锁
- 不支持FULLTEXT类型的索引
- 不保存表的具体行数，扫描表来计算有多少行
- DELETE 表时，是一行一行的删除
- InnoDB 把数据和索引存放在表空间里面
- InnoDB中必须包含AUTO_INCREMENT类型字段的索引
- 表格很难被压缩
MyISAM
- 不支持事务，回滚将造成不完全回滚，不具有原子性
- 不支持外键
- 不支持外键
- 支持全文搜索
- 保存表的具体行数,不带where时，直接返回保存的行数
- DELETE 表时，先drop表，然后重建表
- MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸
- MyISAM中可以使AUTO_INCREMENT类型字段建立联合索引
- 表格可以被压缩
选择
因为MyISAM相对简单所以在效率上要优于InnoDB。
如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。
如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。
为什么选择B+树作为索引结构
索引的最终目的是减少磁盘I/O次数，B+树是有序数组链表+平衡多叉树，所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的。
非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。做这个优化的目的是为了提高区间访问的性能。而正是这个特性决定了B+树更适合用来存储外部数据。
查询在什么时候不走索引
- 条件字段选择性弱，查出的结果集较大，不走索引
- where条件等号两边字段类型不同，不走索引
- 优化器分析的统计信息陈旧也可能导致不走索引
- 索引字段 is null 不走索引
- 对于count(*)当索引字段有not null约束时走索引，否则不走索引
- like 后面的字符当首位为通配符时不走索引
- 使用不等于操作符如：<>、!= 等不走索引
- 索引字段前加了函数或参加了运算不走索引
sql如何优化
- 避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
- 避免在 where 子句中对字段进行 null 值判断
- 应尽量避免在 where 子句中使用!=或<>操作符
- 应尽量避免在 where 子句中使用 or 、in 和 not in
- like '%abc%' 会导致全表扫描like 'abc%' 不会
- 应尽量避免在 where 子句中对字段进行表达式、函数操作
- 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
- 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
- 很多时候用 exists 代替 in 是一个好的选择
- 当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
- 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率
- 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
- 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间。其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
- 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
- 尽量避免大事务操作，提高系统并发能力。
- 尽量避免向客户端返回大数据量
explain命令返回字段的含义
table
显示这一行的数据是关于那张表的。
type
显示查询使用了何种类型，从最好到最差的连接类型为system、const 、eq_reg 、 ref、range 、index、 ALL等。
system/const
可以将查询的变量转为常量，如id=1;id为主键或者唯一键。
eq_ref
访问索引，返回某个单一行的数据（通常在连接时出现，查询使用的索引为主键或者唯一键）
ref
访问索引，返回某个值的数据，可以返回多行，通常使用等于时发生。
range
这个连接类型使用索引返回一个范围中的行，比如使用<或>查找东西，并且在该字段上建有索引时发生的情况（但是不一定好于index索引）
index
以索引顺序进行全表扫描，优点是不用排序，缺点是还要全表扫描。
ALL
全表扫描，应该尽量避免
possible_keys
显示可能应用在这张表中的索引。如果为空，表示没有可能应用的索引
key
实际使用的索引。如果为null，则没有使用索引。Mysql很少选择优化不足的索引，此时可以在SELECT语句中使用FORCE INDEX （index_name）来强制使用一个索引，或者用IGNORE INDEX（index_name）来强制忽略索引。
key_len
使用索引的长度。在不损失精确性的情况下，长度越短越好。
ref
显示索引的哪一列被使了，如果了能的话，是一个常数
rows
Mysql认为必须检索的用来返回请求数据的行数。
Extra
关于Mysql如何解析查询的额外信息，主要有以下几种：
using index
使用索引，可以避免访问表，性能很高。
using where
使用where来过滤数据，不是所有的where clause都显示using where，如以=方式访问索引。
using tempory
用临时表去处理当前的查询。
using filesort
用到额外的排序，此时mysql会根据连接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。（当使用order by v1,而没有用索引是，就会使用额外的排序）。
range checked for each record (index map:N)
没有好的索引可以使用。
Using index for group-by
表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。
order by原理
- 利用索引的有序性获取有序数据
- 利用内存/磁盘文件排序获取结果
1) 双路排序:是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。
2)单路排序:是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。
