mysql杂项记录

事务相关：

事务是指一组sql语句组成的逻辑处理单元，在这组sql的操作中，要么全都执行成功，要么全都失败回滚，不会出现只执行一部分的情况。举个简单又经典的例子就是转账了，事务A中要进行转账，那么转出的账号要扣钱，转入的账号要加钱，这两个操作都必须同时执行成功，为了确保数据的一致性。

事务有4大特性，分别是原子性、一致性、隔离性、持久性。

原子性：是指事务的操作原子的，对数据的修改要么全部执行成功，要么全部失败。原子性是基于redo log/undo log实现的。

redo log就是重做日志，用于在崩溃时恢复数据，它会记录任何可能造成数据库数据修改的操作，具体来说，redo log是物理日志，记录的是数据页的物理修改操作。innodb存储引擎下至少有一个重做日志文件组，每个文件组下至少有两个重做日志文件，每个文件的大小一致，并以循环写入的方式运行，可以通过innodb_log_file_size来设置重做日志文件的大小，它对innodb存储引擎有着非常大的影响。如果重做日志设置的太大，数据丢失时，恢复数据需要的时间就会比较长，如果设置的太小，会导致依据checkpoint的检查需要频繁刷新脏页到磁盘，导致性能的抖动。在写redo log时也不是直接写入文件的，而是先将redo log写入到缓存中，然后则按一定的频率写入到redo log磁盘文件，这个缓冲可以通过innodb_log_buffer_size来设置大小，默认为8M，不用设置的太大，一般情况每秒都会将重做日志缓冲刷新到磁盘，除了每秒刷新机制之外，每次事务提交时重做日志缓冲也会刷新到日志中。是通过innodb_flush_log_at_trx_commit控制redo log刷新到磁盘的频率，设置为0，当事物提交时不会立即将重做日志从缓冲中刷新到磁盘文件，而是等待主线程按时写入，这种情况效率最高，但安全性比较低，可能会丢失数据。设置为1，当事物提交时会将重做日志从缓冲中刷新到磁盘文件，效率比较低，但安全性最高，不会造成数据丢失。设置为2，是让文件系统自己决定何时将重做日志从缓冲中刷新到磁盘。

一致性：是指事务开始和结束后，数据库的完整性不会被破坏，就是说会从一个一致性的状态转移到另一个一致性的状态，不会存在中间状态，mysql是通过undo log来实现事务的一致性。undo log用于对事物的影响进行撤销以及多版本控制，它记录的是数据在操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。

持久性：是指一个事务提交后，这个事务的状态会被持久化到数据库中，即使服务器宕机，数据也不会丢失。mysql是通过force log at commit机制来实现事物持久性的，就是当事物commit时，必须先将该事物所有的日志都写入到redo log进行持久化之后，commit才算成功。

隔离性：是指不同事务之间互不影响，但这跟事务的隔离级别有关系，一般来说有4大隔离级别，读未提交、读已提交、可重复读、串行化等，mysql默认的隔离级别是可重复读。mysql是通过多版本并发控制机制和锁来实现隔离性的。

一致性非锁定读是指innodb存储引擎通过多版本控制(MVCC)来读取数据库中数据的方式，就是说如果读取的行正在执行更新操作，这时读取操作不会等待行上锁的释放，而是读取行的一个快照。快照数据是指该行之前版本的数据，每行记录可能有多个版本，一般称这种技术为多版本技术，由此带来的并发控制，称之为多版本并发控制(MVCC)。innodb是通过undo log来实现MVCC的，undo log本身是用来在事务中回滚数据用的，因此快照数据本身是没有额外开销的。一致性非锁定读是innodb默认的读取方式，即读取不会等待和占用行上的锁，一般在读提交、可重复读隔离级别下支持一致性非锁定读，但在读提交隔离级别下，每次都读取都是最新一份快照数据，而可重复读隔离级别下读取的是事务开始时的快照数据。

数据库的并发控制机制有很多，最为常见的就是锁机制，它会给竞争资源加锁，阻塞读或者写操作来解决事务之间的竞争条件，最终保证事务的串行化。而mvcc则引入了另外一种并发控制，它让读写操作互不阻塞，每一个写操作都会创建一个新版本的数据，读操作会从中选取一个最合适的版本。

虽然说有多个版本的数据快照，但是并没有拷贝多份数据，mysql利用undo log巧妙的解决了这个问题。数据库的事务有时需要回滚操作，这时就需要对操作之前的数据进行undo，因此，在对数据修改时，innodb会产生undo log，当事务需要回滚时，innodb可以利用这些undo log将数据回滚到修改之前的样子。

innodb行记录有三个隐藏字段，分别是rowid、事务号、回滚指针，事务号表示最近修改的事务id，回滚指针指向的是回滚段中的undo log。比如，当事务使用update语句修改行数据时，会首先使用排他锁锁定该行，将该行当前的值复制到undo log中，然后在真正地修改当前行的值，并且使用回滚指针指向undo log中修改前的行。

我们在修改数据时，并不是直接修改磁盘上的数据，而是将数据加载到内存中进行修改，然后将修改的数据写入到重做日志缓冲中，当事务提交时，会将缓冲中的重做日志写入到磁盘文件，为了提高性能，数据页在内存修改后并不是在事务commit时立即刷新到磁盘，而是引入了checkpoint机制，择时将数据页落盘。数据库崩溃重启后会将redo log中未落盘的脏页数据恢复出来，重新写入磁盘，保证用户的数据不丢失，当然，在崩溃恢复中还会回滚没有提交的事务，由于回滚操作需要undo日志的支持，undo日志的完整性和可靠性需要redo日志来保证，所以崩溃恢复先做redo恢复数据，然后做undo回滚。

事实上，原子性、持久性和隔离性都是为了保证一致性。

锁相关：

为什么需要锁呢？

当我们需要限制某段程序在同一时刻只能被一个线程执行的时候就需要锁了。就是说当数据库有并发事务的时候，可能会造成数据的不一致，这个时候就需要一些机制来保证数据一致性的问题，因此就引入了锁。

锁分类：

表级锁，是在mysql服务层实现的锁，每次会锁定表中所有的记录，特点是加锁、解锁快，消耗的资源少，不会产生死锁，但并发度低。

行级锁，是存储引擎层实现的锁，innodb支持行级锁，是按行锁定数据，特点是，加锁、解锁慢，消耗的资源多，会产生死锁，但并发度高。innodb是通过给索引加锁来实现行锁。它分为共享锁和排他锁。

行级锁又分为：

共享锁(S)：特点是共享锁与共享锁兼容，与排他锁不兼容。

排他锁(X)：与所有的锁都不兼容。

意向锁也是表级锁，它表示有行级锁或即将有行级锁的存在，包括意向共享锁和意向排他锁，它不会和行级锁冲突，只会和表级的s、x锁冲突。

为什么需要意向锁呢？

当向表添加表级x锁的时候，如果没有意向锁，则需要遍历表判断是否有行锁的存在，以免发生冲突，如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。

innodb存储引擎有三种行锁的算法：

record lock(记录锁)：锁定单个或多个行记录。

gap lock(间隙锁)：锁定行与行的范围，但不包括行记录本身。

next-key lock(间隙锁)：锁定一个范围，并且锁定记录本身。等于记录锁加上gap log。

自增锁(AUTO_INC)：它是一种表锁，当表中有自增列时会出现自增锁。当插入表中有自增列时，数据库需要自动生成自增值，会先为表加自增锁阻塞其他事物的插入操作，这样就能保证生成的自增值肯定是唯一的。自增值一旦分配了，即使事物回滚也不会减回去。

乐观锁：乐观锁是乐观的认为每次都不会发生冲突，只会在更新的时候检查要更新的值有没有被别人修改过，因为没有加锁，所以乐观锁也被称为无锁。mysql使用mvcc来实现乐观锁。

悲观锁：悲观锁是悲观的认为每次都会发生冲突，所以每次都需要加锁。

死锁：是指两个或多个事务在同一资源上相互占用，并等待锁定对方占用的资源。mysql可以自动检测死锁并且放弃一个事务来成全另一个事务。

请看下面一个死锁的案例：

事务A：

T1：start trasaction

T2：select * from t where i=1 lock share in mode;

T5：delete from t where i=1;

事务B：

T3：start transaction

T4：delete from t where i = 1;

当事务A在T5执行时，事务B会收到一条错误信息：deadlock found when trying to get lock;try restarting transaction;

此处发生死锁，因为事务A需要X锁才能删除该行，但是，不能授予该锁定请求，因为事务B已经先请求该行数据的X锁，并且正在等待事务A释放其锁。

结果是，innodb为其中一个客户端生成一个错误并释放其锁，让另一个客户端获得锁。

由于innodb索引机制，数据库操作使用了主键索引，则innodb会锁住主键索引。使用非主键索引时，innodb会先锁住非主键索引，再锁定主键索引。

当查询的索引是唯一索引时，innodb会将next-key lock降级为记录锁，即只锁住索引本身，而不是范围，因为next-key lock的作用是防止在间隙之间插入相同的数据，防止幻读的发生，但唯一索引的作用是不允许有重复的数据，所以不存在幻读的问题，因此可以把next-key lock降级为记录锁。

在数据的增、删、改、查中，只有增、删、改会加排他锁，而查询不会加锁，除非你主动用share lock in mode或for update来显示的加锁。

杂项：

innodb通过多版本并发控制(MVCC)来实现高并发，默认隔离级别为可重复读。使用next-key lock避免幻读的产生。

缓冲池：

innodb存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理，但是由于cpu速度和磁盘速度之间的差距，基于磁盘的数据库系统通常使用缓冲池来提高性能。

在数据库中进行读取操作，首先将从磁盘中读到的页放在缓冲池中，下次在读相同的页时，首先判断该页是否在缓冲池中，若在，则直接读取缓冲池中的数据。

对于数据库中页的修改操作，首先修改的是缓冲池中的页，然后在以一定的频率刷新到磁盘上，页从缓冲池刷新到磁盘的操作并不是在每次页发生更新时触发，而是通过一种check point的机制刷新回磁盘。所以缓冲池的大小直接影响数据库的整体性能，可以通过innodb_buffer_pool_size来设置。

插入缓冲(insert buffer)：

在innodb存储引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在非聚族且不唯一的索引时，在插入时，数据页的存放还是按照主键进行顺序存放。但是对非聚族索引叶节点的插入不再是顺序的了，这时就需要离散的访问非聚族索引页，会导致插入性能的下降。

因此引入了插入缓冲,对于非聚族索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚族索引是否在缓冲池中，若在，则直接插入，否则，会先放入到insert buffer中，然后在以一定的频率刷新到磁盘，这样就可以将多个insert合并成一个，能大大的提高非聚族索引的插入性能。

自适应哈希：

innodb会根据访问频率为热点页建立哈希索引，来提高查询效率。innodb会监控表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希。

自适应哈希索引是通过缓冲池的b+树构建而来，因此建立速度很快，而且不需要对整张表建立哈希索引。

mysql使用group by分组语句时order 不要语句会失效，直接返回group by语句结果的第一条记录，索引通过子查询实现先排序后分组就可以解决问题。但在mysql5.7版本中order by与group by一起使用时order by不生效，必须加上limit限制。

索引：

索引是一种数据结构，它是一种排好序的便于查找的数据结构，它们包含着对数据表里所有记录的引用指针，可以帮助我们快速的找到数据。

但是索引也有缺点，索引需要额外的维护成本，因为索引文件是单独存在的，对数据增加、修改、删除都会产生额外的对索引文件的操作。

索引的原理：其实很简单，就是把无序的数据变成有序的查询。

a：把创建了索引列的内容进行排序。

b：对排序结果生成倒排表。

c：在倒排表内容上拼上数据的地址

d：在查询的时候，先拿到倒排表内容，再取出数据的地址，然后根据地址就能拿到真实的数据。

hash索引底层就是hash表，进行查找时调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际的数据，但hash索引无法进行范围查询，以及不能排序、分组操作，也不支持模糊查询以及多索引的最左前缀匹配。B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点才能获得键值，然后根据查询判断是否需要回表。

从逻辑上可以分为5类：

主键索引：每个表只能有一个主键索引，但可以多个字段联合起来构建一个主键索引。键值唯一其不允许为null。

唯一索引：一个表可以有多个唯一索引，键值唯一且允许为null。

普通索引

组合索引

全文索引

从物理上可以分为：

聚族索引：每个表只能有一个聚族索引，就是表的主键，如果没有主键，则会选择一个非空的唯一索引来实现，如果也没有唯一非空索引，则会隐式生成一个主键。叶子节点存储的是主键值和该行的所有的数据。二级索引上保存的是键值和主键，所以查询时需要回表二次查找。

a：表数据按照索引的顺序存储，也就是说索引项的顺序和表中记录的物理顺序一致。

b：叶子节点存储了真实的数据行，不在有单独的数据页。就是说叶子节点保存了主键值和该行的数据。

c：一张表只能创建一个聚族索引，因为物理顺序只能有一种。

d：如果主键不是自增的，mysql会不断的调整数据的物理地址，让他们按顺序存储，如果是自增的，只需要一页一页的写即可。

非聚族索引：叶子节点只保存键值和该列地址或键值和主键。

a：表数据存储顺序与索引顺序无关，叶子节点包含索引字段值及指向数据行的指针。

建索引、使用索引注意事项：

a：数据类型越小越好。数据类型越小，在磁盘、内存和cpu缓存中需要的空间更少，处理速度更快。

b：数据类型约简单越好。

c：复合索引要遵循最左前缀原则。

d：注意隐式类型转换造成的索引不能使用

e：在索引列上不要使用函数以及运算操作

f：like查询时%不要放在最前面。

g：使用not in、!=时无法使用索引

h：建立索引时给区分度大的列建立索引，如性别就不适合建立索引。

事物隔离级别：

mysql中事物的隔离级别分为4大等级：

读未提交：事物能读到另一个事物未提交的数据，会产生脏读的问题。它不会对表加锁，性能最高，但安全性最差。

读已提交：事务只能读取到其他事务已提交的数据，它解决了脏读的问题，但会带来另一个不可重复读的问题，即在事务的任意时刻读到的数据可能不一样。比如A事务一开始读到的数据name=张三，B事物修改了name=李四，A事务再次读取时name就变成了李四。

可重复读：可重复读解决了不可重复读和脏读的问题，但带来了幻读的问题，就是在一个区间范围内，我两次查询到的记录数不一样。比如，第一个事务查询一个user表id=100发现不存在该行数据，这时第二个事务插入了一条id=100的数据并且提交了事务，这时第一个事务新增了一条id=100的数据行会报主键冲突，再次select一下，发现id=100的数据行已经存在了。mysql的innodb通过间隙锁解决了幻读的问题。可重复读是mysql默认的隔离级别

串行化：它解决了脏读、可重复读、幻读的问题，但性能最差。对于串行化，mysql加的是一把大锁，读的时候加共享锁，其他事物可能，不能写。写的时候，加的是排他锁，其他事物不能读也不能写。

脏读：一个事务读到了另一个事务未提交的更新数据。

幻读：一个事务读到了另一个事务已提交的新增数据。

不可重复读：一个事务读到了另一个事务已提交的更新数据。