MYSQL

Mysql的二阶段提交？

Mysql的二阶段提交发生在redolog和binlog的日志写入阶段，也就是把日志写入和日志提交拆分为两个阶段，来确保redolog和binlog写入数据的一致性；

第一阶段：prepare阶段

            Mysql会把事务操作记录到redolog中并标记为prepare状态

第二阶段: commit阶段

            当事务提交时，Mysql会将事务操作记录到binlog中，然后把redolog的日志设置为commit状态

所以,innoDB在写redolog的时候，并不是一次性写完，而是有两个阶段，prepare和commit这就是两阶段提交的含义

1.如果在写入redolog之前崩溃，此时redolog和binlog中没有数据，是满足数据一致性的

2.如果在写入redolog的prepare阶段后立马崩溃，由于redolog中没有标记为commit,于是，拿着事务ID去binlog中查找记录，肯定是没有的，此时会执行回滚操作

3.如果在写入binlog后立马崩溃，由于redolog中的事务ID可以在binlog中找到记录，这个时候可以直接提交数据。

MySQL主从复制

主从同步主要有三种形式：statement、row、mixed

1、** 基于语句的复制（STATEMENT）**：会将对数据库操作的sql语句写道binlog中在 MySQL5.0 以前只支持基于语句的复制。实际上只是把主库上的SQL执行一遍。好处是实现简单，但存储过程和触发器在使用基于语句的复制模式时也可能存在问题

2、基于行的复制（ROW）：会将每一条数据的变化写道binlog中。MySQL5.1开始支持基于行复制，这种方式会将实际的数据记录在 binlog 中，好处是可以正确的复制每一行，如果是全表更新SQL使用基于行的开销会很大，因为每一行的数据都会记录到 binlog 中，这使得 binlog 文件庞大，并且会给主库增加额外的负载。

3、混合模式（MIXED）：statement与row的混合。一般的复制使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择日志保存方式。

基本原理

在master机器上的操作：

  当master上的数据发生变化的时候，该事件变化会按照顺序写入binlog中。当slave链接到master的时候，master机器会为slave开启binlog dunp线程。当master的binlog发生变化的时候，binlog dump线程会通知slave，并将相应的binlog内容发送给slave。

在slave机器上操作：

 当主从同步开启的时候，slave上会创建两个线程：I\O线程。该线程连接到master机器，master机器上的binlog dump 线程会将binlog的内容发送给该I\O线程。该I/O线程接收到binlog内容后，再将内容写入到本地的relay log；sql线程。该线程读取到I/O线程写入的ralay log。并且根据relay log。并且根据relay log 的内容对slave数据库做相应的操作。

1.MySQL的主从复制，首先必须打开Master端的binlog记录功能

2.在Slave 服务器上执行sart slave命令开启主从复制开关，开始进行主从复制。

3.Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器，并请求从binlog日志文件的指定位置）之后开始发送binlog日志内容

4.Master服务器接收到来自Slave服务器的IO线程的请求后，其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。

5.当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（Mysql-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容

6.Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

MySQL数据库主从架构主库挂了怎么办？

1、确保所有relay log 全部更新完毕

在每个从库上执行 stop slave io_thread; show processlist;

直到看到Has read all relay log；表示从库更新执行完毕：

1. 选择新的主库

对比选择Relay_Master_Log_File,Exec_Master_Log_Pos最大的作为新的主库

登陆slave1，执行stop slave;

并进入数据库目录，删除master.info和relay-log.info文件（删除前，可以先备份下这俩文件）；

配置my.cnf文件，开启log-bin,如果有log-slaves-updates=1和read-only=1则要注释掉，然后重启slave1.

3.reset master

在slave1上reset master，会重新生成二进制日志。

MySQL数据库主从同步延迟是怎么产生的？

当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。
首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高
次要原因：读写binlog带来的性能影响，网络传输延迟。

MySQL数据库主从同步延迟解决方案

硬件方面

1.采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。
2.存储用ssd或者盘阵或者san，提升随机写的性能。
3.主从间保证处在同一个交换机下面，并且是万兆环境。
总结，硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。

mysql主从同步加速

1、sync_binlog在slave端设置为0
2、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
3、直接禁用slave端的binlog
4、slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2

Mysql为什么使用B+树作为索引结构

InnoDB中的数据是面向主键索引进行数据存储的。

B+Tree索引在数据库中有一个特点是高扇出性，因此在数据库中，B+Tree的高度一般都在2-4层，这也就是说查找某一键值的行记录时最多只需要2到4次的磁盘I/O，这是非常不错的。因为当前一般的机械磁盘每秒可以做到至少100次I/O，2-4次的I/O意味着查询时间只需要0.02~0.04秒。

B+Tree索引又可以分为聚簇索引与辅助索引

聚簇索引

聚簇索引其实就是主键索引，InnoDB中的数据是面向主键索引进行数据存储的。而聚簇索引就是按照每张表的主键来构造一棵B+Tree，同时叶子节点中存储的是整张表的行记录信息，也可以将聚簇索引的叶子节点称为数据页。

因此，聚簇索引的这个特性，决定了叶子节点中的数据也是索引的一部分，每个数据页都通过一个双向的链表来进行链接。

由于实际的数据页只可以按照一棵B+Tree进行排序，因此每张表只能拥有一个聚簇索引。在大多数的情况下，查询优化器倾向于采用聚簇索引，因为聚簇索引能够在B+Tree的叶子节点上直接找到数据。除此之外，由于定义了数据的逻辑顺序，聚簇索引能够特别快的访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

需要着重注意的是，在InnoDB的B+Tree索引数据结构中，只有在数据页（即叶子节点）上存放的是完整的每行记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。

同时，从物理存储结构上来看，聚簇索引的存储并不是连续的，而是逻辑上连续的

辅助索引

InnoDB 允许自行对某些列建立索引，即为非聚簇索引，或者叫做二级索引。

由于聚簇索引是按照主键 row_id 作为排序基准的，但某些场景下，我们需要对非主键列进行查询，此时就需要二级索引了。所谓对某个列项x建索引，就是生成一个按照列 x 排序的B+树索引。

需要注意的是，二级索引的叶子节点不存放真实数据，只存放记录的主键 row_id。

在同一张表中，辅助索引的存在与聚簇索引并不冲突，因此同一张表可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB引擎会遍历索引并通过叶级别的指针获得指向聚簇索引的主键，然后再通过聚簇索引来找到具体的记录行。

总结

1、InnoDB中的数据是面向主键索引进行数据存储的，InnoDB的索引是使用B+Tree的数据结构实现的；

2、聚簇索引的非叶子节点，只存储指针，数据页存储在叶子节点，非聚簇索引的叶子节点只存储指向主键的指针，具体数据的查找需要二次遍历聚簇索引树；

3、InnoDB中一棵索引B+Tree可以存放千万级别的数据行。

Mysql索引在什么情况下会失效?

1: 没有使用索引列作为where查询条件

2:对索引列进行函数操作,比如字符串操作或者日期操作

3: 对索引列进行类型转化,如果索引列式是数字类型但是你传入的值是字符串类型那么这个时候mysql会默认对类型进行转化同样会导致所以失效

4: like查询的查询字符串以通配符开头

5: OR条件查询

6: 查询条件涉及到大量数据,当查询条件涉及大量数据的时候假如返回表中的大部分数据查询的时候Mysql认为可能使用索引意义并不大所以呢也不会使用索引

Mysql中MyISAM和InnoDB引擎有什么区别？

1.存储方式不同。MyISAM里面索引和数据的存储是分开存储，而InnoDB把索引和数据存储在同一个文件中。

2.对于事务的支持不同。MyISAM不支持事务，而InnoDB支持ACID特性的事务处理

3.对于锁的支持不同。InnoDB存储引擎支持事务和行级别的锁，MyISAM只支持表级别的锁

4.MyISAM不支持外键，InnoDB支持

MySQL 中的锁可以按照粒度分为锁定整个表的表级锁（table-level locking）和锁定数据行的行级锁（row-level locking）：

• 表级锁具有开销小、加锁快的特性；但是表级锁的锁定粒度较大，发生锁冲突的概率高，支持的并发度低；
• 行级锁具有开销大，加锁慢的特性；但是行级锁的锁定粒度较小，发生锁冲突的概率低，支持的并发度高。

InnoDB 存储引擎同时支持行级锁（row-level locking）和表级锁（table-level locking），默认情况下采用行级锁。

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。所以如果访问没有命中索引，就无法使用行锁，将要退化为表锁（共享行锁上升为共享表锁，排他行锁上升为排他表锁）。

全局锁

全局锁就是对整个数据库实例加锁，获得全局锁后的数据库就无法进行数据的更新操作与表结构修改操作。

当你需要让数据库变为只读状态时，可以使用**

Flush tables with read lock

语句进行加锁，此时数据的更新操作会被阻塞。解锁命令：UNLOCK TABLES**;

行级锁

InnoDB 实现了以下两种类型的概念行锁：

排它锁 Shared、共享锁 Exclusive

排他 X 锁和共享 S 锁是 Innodb 的行级概念锁。保证同一行记录修改与删除的串行性，从而保证数据的强一致。

共享锁是读锁，多个事务可以拿到同一行记录的共享锁，所以读读可以并发。排他锁是写锁，同一行记录的排他锁在同一时刻只能有一个事务获得，所以写写是互斥的。实际上读写也是互斥的，也就是有排他锁就不能加共享锁，有共享锁就不能加排他锁。

行共享锁的触发场景

加了lock in share mode 的 select 语句， 比如：select … lock in share mode

普通 select 实施不加锁多版本快照读

行排他锁的触发场景

• 加了 for update 的 select 语句，比如 select … for update
• update, delete, insert 都是行级排它锁

行级锁有三种实现算法：记录锁、间隙锁、临键锁。

在数据库的优化器优化过程中会根据where字段的用到的索引类别，自动的加相应种类的行锁。

根据触发条件的不同，每种锁都有排他锁和共享锁两种类型实现。比如触发条件是加了lock in share mode 的 select 语句，那么可能获取到的就是共享类型的记录锁/间隙锁/临键锁。

记录锁(Record Locks)（加锁对象是索引节点）

select * from t where id=1 for update; 手动或者自动加了

for update

才会加记录锁，否则不加锁，实施快照读。

它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

只适用于在唯一索引上使用了唯一查询条件。如果唯一索引不是聚集索引，还会额外锁定聚集索引的索引记录。

间隙锁(Gap Locks)（实施在索引上）

间隙锁，它封锁索引记录之间的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。是一个左开右闭的区间，即(x,y]的形式。

可以防止间隙内有新数据被插入，以及防止已存在的记录，更新成间隙内的记录。

适用于在唯一索引上使用了范围查询条件。

间隙锁的目的是在RR级别下，防止幻读，幻读的产生是当前事务多次的查询结果数量上不一致，间隙锁的目的就是保证当前范围内的数据不会被更改，所以它会锁住某些个区间的数据

临键锁（实施在索引上）

临键锁，是记录锁与间隙锁的组合，既封锁索引记录本身，又封锁索引记录之前的区间。

临键锁针对的是普通索引（注意不是唯一索引）

临键锁

等于 记录锁+间隙锁

，MySQL默认级别是RR、默认锁上临键锁。

插入意向锁（实施在索引上）

间隙锁的一种。

作用：多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。用于提高插入并发，因为如果使用间隙锁的话，不允许多个事务同时往同一个索引间隙插入记录，但是使用插入意向锁可以。
比如两个事务都想往id(10,20)插入一条记录，但是两个事务插入记录的id分别是 11 和 12，插入的位置不冲突，所以不会阻塞对方。

如果插入的位置冲突呢？怎么办？另一个回滚吗？

1.不采用事务包装这部分逻辑，本文实际业务场景中可以不需要事务，所以直接取消事务包装即可，采用insert ON DUPLICATE KEY UPDATE的方式
2.调整事务隔离级别为read commit，RC级别不会产生gap lock
3.利用分布式锁

锁总结

锁都是基于索引去找到数据记录再加锁的，而索引的规则是：通过其它索引找到主键索引，所以：

没有使用索引做更新相关操作会锁表。
通过唯一/主键索引等值加锁，只会锁具体的行，非唯一索引则不一定，SQL优化器会基于数据分布选择记录锁，或临键锁。
只有在RR级别下才有间隙锁，目的是为了解决幻读，如果操作的数据是跨多个范围，就会加多个区间的间隙锁。
MySQL默认的锁就是【临键锁】，所以在执行SQL的时候，记录锁和间隙锁是会同时存在的。范围是左开右闭的区间。
在SQL查询的时候，我们知道是先通过索引去找数据的，其实加锁也是基于索引的，通过索引找到对应的数据然后把相关的数据行都锁上，如果没有使用索引就会扫描全表，也就会锁表。

表级锁

表级锁有两种：表数据锁（常说的表锁），元数据表锁（MDL，metadata lock，Mysql5.5版本之后加的）。

元数据锁

MDL锁在语句开始执行时申请，在事务提交后释放。只要有事务在执行，mysql 就会自动加上元数据表锁（MDL），这样在执行过程中就不能发生表结构变更。

对表内数据进行增删改查，加 MDL 读锁，执行表结构变更（DDL命令）加 MDL 写锁。读读不互斥、读写和写写都互斥。

普通表锁如何触发

如果想手动加表锁，可以使用 lock 和 unlock 关键词来加锁和释放锁： lock tables 表名 read/write。比如：lock tables A read,B write。未解锁前不能其它表进行CRUD操作。加上读锁后，任何线程都只能执行读操作，写操作都会被阻塞，包括加锁的线程也是这样。

意向锁（Intention Locks）

如果要获取表锁，按一般法的方法，首先需要看该表是否已经被其他事务加上了表级锁，然后遍历该表中的每一行是否已经被其他事务加上了行级锁，如果存在行级锁，需要等待行级锁释放，检测行级锁的算法效率很低。为此，InnoDB 引入了另外一种锁：意向锁（Intention Lock），用于加表锁前快速判定表中是否存在行级锁。

意向锁属于表级锁，由 InnoDB 自动添加，不需要用户干预。

自增锁实现

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。控制同一sql 语句插入的所有记录的自增id是连续的。

innodb通过

innodb_autoinc_lock_mode

可以查看自增锁的状态。取值为0/1/2，默认为 1,

innodb_autoinc_lock_mode = 0，表粒度加锁，实施互斥；

所有的 insert 语句("insert like") 都要在语句开始的时候得到一个表级的 auto_inc 锁，在语句结束的时候才释放这把锁，持续时间长，影响了并发插入效率。（sql 语句级别，而非事务级别）

保证了简单插入和批量插入时同一条语句插入的行记录的自增ID是连续的。可以保证主和从复制的同一张表的同一个行记录的自增ID是一样的。

innodb_autoinc_lock_mode = 1，表粒度加锁，实施互斥；

同样，所有的 insert 语句("insert like") 都要在语句开始的时候得到一个表级的 auto_inc 锁，但是对简单插入做了优化，由于简单插入插入的记录数能提前预知到，在获取到所需的自增键数量后，autoinc_lock 就会被释放,不必等到语句执行结束。但对于bulk insert，自增锁会被一直持有直到语句执行结束才会被释放。性能有所提高。

仍就可以保证简单插入和批量插入时同一条语句插入的行记录的自增ID是连续的。也可以保证主和从复制的同一张表的同一个行记录的自增ID是一样的。

innodb_autoinc_lock_mode = 2，不加锁

不加自增锁，效率最高，但是 simple insert 语句因为自增键是一次性分配的，所以仍能保证ID是连续的，但是 bulk insert的ID则可能有空洞。
主从复制的同一张表下的同一行id有可能不一样。

主从复制时，执行并发批量插入，可能导致不同库里同一张表下的同一行id有可能不一样，如何避免

把 innodb_autoinc_lock_mode 设置成 1，则在批量插入时会加上自增表锁，保证从库回放 sql 时得到的自增 id 跟主库一样

同一张表可以同时有多个事务进行写操作吗

如果事务之间操作的是不同的记录行，可以同时进行，如果操作的是相同的记录行，则不能同时进行，因为事务之间会因为获取行锁冲突而阻塞，未获取到行锁的事务需要等获取到行锁再继续执行。

事务里的 sql 语句执行过程中，加上表锁或者行锁后，这些锁是 sql 执行完毕就释放，还是持续到事务执行完毕才释放，为什么不 sql 执行完毕就释放？即上面的这些锁是语句级别的，还是事务级别的？

事务执行完毕才释放。在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议（两个阶段指的是加锁阶段和解锁阶段）。

比如使用使用临键锁来避免幻读的问题，加锁后不确定事务内后续是否仍有同样的范围查询语句会执行，如果每次 sql 执行完毕就释放锁，多次范围查询语句之间仍旧有可能在范围之间插入数据，导致幻读，那临键锁就没有意义了。所以为了保证每次查到的结果都一样，即保证不出现幻读，只能是把临键锁持续到事务完成才释放。

事务内sql加的锁都是在事务完成才释放，这个有什么指导意义

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，减少这些记录行的加锁时间，降低锁冲突概率。

假设你用淘宝积分购买了一件商品，那么这个动作需要至少有三条 sql，扣减你的积分、扣减商品数量、增加订单记录，假设淘宝所有数据都在一个数据库里，那么这三个 sql 可以用一个事务包裹起来，这时会把商品扣减sql放在最后，你的积分扣减放在最前面，因为商品扣减的锁冲突的概率最大，最可能影响并发，你的积分扣减锁冲突最小。

如何处理死锁

• 策略一：直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置；默认为 50s，即如果不开启死锁检测，则在发生死锁之后，会等待 50s 后回滚事务释放锁。
• 策略二：发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。innodb 默认开启死锁检测，但是死锁检测会消耗大量的CPU资源。