MySQL相关笔记
事务
一个事务是可以被看作一个单元的一系列SQL语句的集合。
事务的四大特性：ACID
Atomacity 原子性
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。
Consistency 一致性
事务将数据库从一种一致状态转变为下一种一致状态。也就是说，事务在完成时，必须使所有的数据都保持一致状态（各种 constraint 不被破坏）。
Isolation 隔离性
由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。换句话说，一个事务的影响在该事务提交前对其他事务都不可见。
Durability 持久性
事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。
隔离级别
如果不对数据库进行并发控制，可能会产生异常情况：
- 脏读（Dirty Read）
当一个事务读取另一个事务尚未提交的修改时，产生脏读。 - 不可重复读 （Nonrepeatable Read）
一个事务对同一行数据重复读取两次，但是却得到了不同的结果。同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。 - 幻读 （Phantom Read）
事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据。这是因为在两次查询过程中有另外一个事务插入数据造成的。当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。 - 丢失修改（Lost Update）
第一类：当两个事务更新相同的数据源，如果第一个事务被提交，第二个却被撤销，那么连同第一个事务做的更新也被撤销。
第二类：有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效。
为了兼顾并发效率和异常控制，在标准SQL规范中，定义了4个事务隔离级别：
- 读未提交（Read Uncommitted）
即使一个更新语句没有提交，但别的事务可以读到这个改变。
Read Uncommitted允许脏读。 - 读已提交 （Read Committed）
只能读到已提交的数据。Oracle、Sql Server 默认都是该级别。
Read Commited 不允许脏读，但会出现非重复读。 - 可重复读（Repeatable Read）
在同一个事务里面先后执行同一个查询语句的时候，得到的结果是一样的。MySQL的InnoDB引擎使用的这个级别。
Repeatable Read 不允许脏读，不允许非重复读，MySQL/InnoDB通过MVCC和 Next-Key Lock解决了幻象读问题。 - 可序列化（Serializable）
串行读，事务执行的时候不允许别的事务并发执行。完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。
Serializable 不允许脏读，不允许非重复读，不允许幻读。
锁
按照锁定的范围，可分为行锁、表锁。
InnoDB只有在通过索引条件检索数据时使用行级锁，否则使用表锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。
InnoDB有三种行锁的算法：
- Record Lock：单个行记录上的锁。
- Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
- Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。
Record Lock分为行级共享锁、行级排它锁。
共享锁
共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。
排它锁
排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。
gap锁
间隙锁，锁定的不是当前行，而是当前行的上一个索引区间和下一个索引区间。
MVCC
在隔离级别为RR时,MySQL已经可以避免脏读和重复读,但还是无法避免幻读,新版本MySQL采用next-key锁与MVCC(多版本并发控制)来避免幻读，详情请见
InnoDB的MVCC如何解决不可重复读和快照读的幻读,当前读用next-key解决幻读
快照读和当前读
Innodb的默认事务隔离级别是RR（可重复读）。它的实现技术是mvcc。基于版本的控制协议。
快照读：读取的是记录数据的可见版本（可能是过期的数据），不用加锁
当前读： 读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。
- 快照读(snapshot read)
简单的select操作
- 当前读(current read)
select ... lock in share mode
select ... for update
insert
update
delete
各种隔离级别的具体实现--加锁
读未提交
- 事务在读数据的时候并未对数据加锁；
- 事务在修改数据的时候只对数据增加行级共享锁。
读已提交
- 事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；
- 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。
可重复读
- 事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加行级共享锁，直到事务结束才释放；
- 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。
可序列化
- 事务在读取数据时，必须先对其加表级共享锁 ，直到事务结束才释放；
- 事务在更新数据时，必须先对其加表级排他锁 ，直到事务结束才释放。
索引
主键索引，二级索引
主键索引是在主键列上建立的索引，二级索引可以理解为在非主键列上建立的索引，二级索引包括唯一索引、普通索引、前缀索引。
聚簇索引和非聚簇索引
聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录。
聚簇索引的特点是存储数据的顺序和索引顺序一致，一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
MySQL的InnoDB引擎中的主键索引就是使用聚簇索引的方式存储数据，而MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。
InnoDB引擎每次通过二级索引查找数据时，首先找到的是索引列的值以及对应的主键，如果我们要找的信息在这个二级索引列中没有存储（不是覆盖索引，或者说我们查找的不是主键），那么就还要通过主键索引去定位到对应的行，因为InnoDB中的主键索引存储了该行记录的所有信息，大多时候都需要通过二级索引+主键索引这两个索引合作完成查询。
从这里来看，聚簇索引对于主键的依赖性很高，主键如果是在表中间插入而不是在表末尾按照顺序插入的话，将可能导致页分裂，继而导致严重的性能问题，这也是为什么一般建议，对于InnoDB中的表，定义一个自增的ID列作为主键（与业务无关联）。
InnoDB存储引擎管理主要基于两个文件：表空间数据文件和日志文件。
InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件
MyISAM引擎的二级索引叶子节点存放的是列值与行号的组合，叶子节点中保存的是数据的物理地址，通过物理地址我们就可以直接找到信息。所以可以看出MyISAM的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引，且MyISAM引擎中可以不设主键。
MyISAM引擎的文件：
.myd即my data，表数据文件.myi即my index，索引文件
MyISAM引擎每次索引查询都要读取两个文件（多次I/O）。理论上非聚簇索引比聚簇索引要慢，但这样想法是片面的。
聚簇索引的优缺点
优点：
- 当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。
- 当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次
I/O。 - 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
缺点：
- 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。
- 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于
InnoDB表，我们一般定义主键为不可更新。 - 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。
- 采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的
I/O代价。
对比聚簇索引的优缺点，很容易发现非聚簇索引的优缺点。
常见索引
在MySQL中，按照数据结构划分的话，主要有四种类型的索引，分别为：B-Tree索引，Hash索引，Fulltext索引(MyISAM 表)和R-Tree索引
主要介绍两种，B-Tree索引，Hash索引
- Inodb存储引擎 默认是
B+Tree索引 - MyISAM 存储引擎 默认是
Fulltext索引；
B+Tree索引是B-Tree索引的升级版
为什么采用 B-Tree 作为索引的数据结构？
索引是在基于数据库表创建的，它包含一个表中某些列的值以及记录对应的地址，并且把这些值存储在一个数据结构中，索引是存放在磁盘中的。每次查询，数据库系统都会将索引读取到内存中进行处理，而索引一般很大，只能分页读取到内存中，而寻址到我们想要的页，这个过程（磁盘IO）是非常耗时的，因此减少磁盘IO次数就非常有必要。
磁盘有个一个预读功能，每次IO都会读取到一页时，会连带着读取后面几页（一般是3页）的内容，不管我们是否需要，根据局部性原理可知，我们大概率会用到后面的页。
一般的查找树，并没有很好地用到这个功能，于是，设计出了B-Tree，B-Tree这种扁平的结构利用磁盘预读功能，能很好地减少磁盘IO次数。
平衡二叉树，相比于B-Tree来书，高度更深，由于逻辑上很近的节点（比如父子节点）物理上可能很远，无法利用局部性（磁盘预读），因此会比B-Tree多了很多次的磁盘IO，性能自然就差些。
B-Tree每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读功能。每次读取磁盘页时就会读取一整个节点。由于其深度小，故磁盘IO次数会较少，更多地是在读取到内存中的页中进行查找。虽然B-Tree查询的次数不比平衡二叉树的次数少，但是相比起磁盘IO速度，内存中比较的耗时基本可以忽略不计。
B-Tree
B树是一种多路自平衡搜索树，它类似普通的二叉树，但是B书允许每个节点有更多的子节点。
一颗 m 阶B-tree包括以下5条特性：
- 每个节点最多有 m 个子节点
- 除根节点和叶子节点，其它每个节点至少有 [m/2] （向上取整的意思）个子节点
- 若根节点不是叶子节点，则其至少有2个子节点
- 所有NULL节点到根节点的高度都一样
- 除根节点外，其它节点都包含 n 个key，其中
[m/2] -1 <= n <= m-1
B+Tree——比B-Tree更适合作为索引的结构
与B-Tree相比，有两点不同：
- 在 B-Tree中一个含有n个子树的节点有n-1个关键字(key)。而在 B+Tree中一个含有n个子树的节点有n个关键字(key)。多出的一个key是因为 B+Tree的节点会存储该节点的子树中最小的key。
- B-Tree的每个节点都包含了关键字(key)以及指向包含这些关键字记录的指针。而 B+Tree在叶子节点中存储了所有的关键字信息，以及指向包含这些关键字记录的指针。而且这些叶子节点构成一个有序链表，即每个叶子节点会有一个指针指向其兄弟节点。在非叶子节点中只存储了关键字信息。
最左匹配原则
最左前缀匹配原则是联合索引的一个概念，当我们创建一个联合索引的时候，例如：创建三个字段的索引 index(a,b,c)，相当于创建三个索引：index(a) index(a,b) index(a,b,c)，因此没必要再去创建index(a)。用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引的。不能不用第一个字段，不能中断。这就是 MySQL 联合索引的最左匹配原则。
全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
LIKE符号写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
var引号不能丢，SQL高级也不难
分组之前必排序，一定要上索引啊
索引的使用
索引容易失效的几个注意点
- 不在索引列上做任何的操作（计算、函数、类型转换），会导致索引失效而转向全表扫描
- 组合索引中，如果中间某个字段使用了范围条件，则右边的列索引失效
- 尽量使用覆盖索引（索引列和查询列一致），减少使用select *
- mysql在使用不等于（！= 或者<>）的时候，无法使用索引列会导致全表扫描
- is null ，is not null 也无法使用索引
- like通配符必须放在索引列的右边，否则索引失效，编程全表扫描
- 字符串不加单引号索引失效
- 少用or，用它连接索引会失效
