1. MySQL

1.1.1. MySQL事务的隔离级别?

点击显示
事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommited)
读已提交(read-commited)
可重复读(repeatable-read)
串行化(serializable)
  • READ UNCOMMITED (未提交读)

    在 read uncommited级别, 事务中的修改,即使没有提交,对其他事务也都是可见的, 事务可以读取未提交的数据,这也被称为脏读(DIrty Read).这个级别会导致很多问题,从性能上说, Read Uncommited 不会比其他的级别好太多,但缺乏其他级别的很多好处,除非真的非常必要的理由,在实际应用中一般很少使用

  • READ COMMITED (提交读)

    大多数数据库系统的默认隔离级别都是 Read Commited (但MySQL不是), Read Commited满足前面提到的隔离性的简单定义: 一个事务开始时,只能 "看见" 已经提交的事务所做的修改, 换句话说, 一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的. 这个级别有时候也叫做不可重复读 (nonrepeatable read),因为两次执行同样的查询,可能会得到不一样的结果

  • Repeatable Read (可重复读)

    Repeatable Read 解决了脏读的问题,该级别保证了同一个事务多次读取同样的记录的结果是一致的, 但是理论上,可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题, 所谓幻读, 指的是当某个事务再次读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row), InnoDB 和 XtraDB 存储引擎通过多版本并发控制(MVCC)解决了幻读的问题

    可重复读是 MySQL的默认事务隔离级别

  • Serializable (可串行化)

    Serializable 是最高的隔离级别, 它通过强制事务串行执行, 避免了前面说的幻读的问题,简单来说,Serializable会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题,实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别

    叶落山城秋: 总是说上面这些东西,今天才认真看了下,结果看懵逼了.. 脏读很好理解,就是可能读取了别的事务还没提交的数据(这数据可能是无用的数据,可能要回滚的). 不可重复读也好理解,就是两次读的数据结果可能不一样,比如我正在事务里查询记录,第一次查 是2条数据,然后我还没提交事务,另外一个事务插入了新的数据,然后我再查了一遍,发现这时候是三条数据了.. 此时我事务还没提交! 这就是不可重复读! 可重复读正好相反,只要我查询还在同一个事务里,结果永远是一样的!不管真实数据是否已经修改了..

    不过这个幻读.. 我看了书还有各种说法,说法不一..

    一种说法是 A查询数据是两条,B查询也是两条,然后A插入了一条数据, B还在之前的事务里,因为是可重复读,所以B再次查询还是原来的数据,但是B想插入第三条(id = 3)的数据,发现插入不了,但是查询还是两条数据..

    另外一种说法是 A把数据表某个字段的数据都从"1"改成了"2",与此同时,B往表里插入了新的数据,这个字段的值依然是"1"插入的,等A更新完后,再次查询,发现表里还有数据未改变..

    不理解了.. 要是有面试的这么问我,我得好好反问一下看面试官怎么理解的.....

    参考:

    go 夜读

    高性能MySQL

    MySQL的四种事务隔离级别

1.1.2. InnoDB和Myisam的区别?

点击显示
  • InnoDB支持事务,Myisam不支持事务
  • InnoDB支持行级锁,Myisam支持表级锁
  • InnoDB支持外键,Myisam不支持
  • InnoDB不支持全文索引,Myisam支持
  • InnoDB是索引组织表,Myisam是堆表

    叶落山城秋: Myisam和InnoDB索引结构都是B+tree, 来,了解一下 树,二叉树,平衡二叉树,红黑树,B树等逐步深入了解 Myisam的索引叶子节点data里存的是数据记录的地址,数据在另外的地方,数据查询先根据索引找到数据记录的位置,再去位置找到数据记录! 这种好处就是写入操作性能比较好,但是查询得先查询数据位置,再查找数据,至少需要两次io

    InnoDB的索引叶子节点data里存的就是数据本身,这样不好的地方在于所有的插入都可能会更新树结构,性能会有些影响,但是查询会快很多,只有找到索引也就找到了数据..

    写到这里, 我特地翻了一下书(以前从未翻过), 先来说一下主键索引和非主键索引, InnoDB的主键索引上所有的叶子节点存的是 主键对应的数据, 直接就是数据, InnoDB的非主键索引存的是当前索引键和对应的主键, 比如 非主键索引是 "name",先根据name查询对应的主键,然后再去主键索引的树上找对应的值,所以这时候是需要两次IO的! Myisam的主键索引和非主键索引叶子节点存的都是索引对应的数据记录的地址,获得地址后,再去硬盘上该地址去获取数据,所以一般是需要两次IO的..

    以上正好就是 聚簇索引和非聚簇索引的区别了..

1.1.3. MySQL慢响应默认时间?

点击显示

10s

1.1.4. Explain的含义?

点击显示

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助 选择更好的索引和写出更优化的查询语句。

1.1.5. Profile的意义以及使用场景?

点击显示

Profile用来分析SQL性能的消耗分布情况。当用explain无法解决慢SQL的时 候,需要用profile来对SQL进行更细致的分析,找出SQL所花的时间大部分消耗在 哪个部分,确认SQL的性能瓶颈。

1.1.6. 视图的作用,视图可以更改么?

点击显示

视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询;不包含任何列或数据。使用视图可以简化复杂的sql操作,隐藏具体的细节,保护数据;视图创建后,可以使用与表相同的方式利用它们。

视图不能被索引,也不能有关联的触发器或默认值,如果视图本身内有order by 则对视图再次order by将被覆盖。

创建视图:create view XXX as XXXXXXXXXXXXXX;

对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等,是可以对其更新的,对视图的更新将对基表进行更新;但是视图主要用于简化检索,保护数据,并不用于更新,而且大部分视图都不可以更新。

1.1.7. drop,delete与truncate的区别?

点击显示

drop直接删掉表 truncate删除表中数据,再插入时自增长id又从1开始 delete删除表中数据,可以加where字句。

  • DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

  • 表和索引所占空间。当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小,而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

  • 一般而言,drop > truncate > delete

  • 应用范围。TRUNCATE 只能对TABLE;DELETE可以是table和view

  • TRUNCATE 和DELETE只删除数据,而DROP则删除整个表(结构和数据)。

  • truncate与不带where的delete :只删除数据,而不删除表的结构(定义)drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger)索引(index);依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。

  • delete语句为DML(Data Manipulation Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

  • truncate、drop是DDL(Data Define Language),操作立即生效,原数据不放到 rollback segment中,不能回滚

  • 在没有备份情况下,谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除,如果于事务无关,用truncate即可实现。如果和事务有关,或老是想触发trigger,还是用delete。

  • Truncate table 表名 速度快,而且效率高,因为: truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

  • TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。

  • 对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。

1.1.8. 连接的种类?

点击显示

例如有两个表

表A:

AID Name
1 张三
2 李四
3 王五

表B:

BID Name Age
1 1 20
2 2 21
3 4 22

表A的ID与表B的UID业务关联

注意: 表A的AID=3 在 表B里没有关联

  • 内连接( join或者 inner join)

    返回符合条件的tableA和tableB的数据

     Select * from tableA inner join tableB where tableA.AID = tableB.UID
    

    结果:

AID Name BID UID Age
1 张三 1 1 20
2 李四 2 2 21
  • 左连接(left join)

    左连接是如果表A和表B查询的时候,先获得表A所有的数据,然后根据表A的每一条数据匹配B里的数据,如果没有,就列为空值(null)

     select * from tableA left join tableB on tableB.UID = tableA.AID
    

    结果:

AID Name BID UID Age
1 张三 1 1 20
2 李四 2 2 21
3 王五 null null null

左表的数据全部查到,如果在表B里没有找到,那么表B的字段列为空值

  • 右连接 (right join)

    与左连接正好相反,获取表B的数据,然后匹配A表的数据,如果没有的,列为空值(null)

     select * from tableA right join tableB on tableB.UID = tableA.AID
    

    结果:

AID Name BID UID Age
1 张三 1 1 20
2 李四 2 2 21
null 王五 3 4 22

右表的数据全部查到,如果在表A里没有找到,那么表A的字段列为空值

  • 完整外部连接

    MySQL不支持完整外部连接

  • 交叉连接 (cross join)

    不带where条件的查询条件会产生连接所涉及的表的笛卡尔积

     select * from tableA cross join tableB
    

    等同于:

     Select * from tableA,tableB
    

    结果:

AID Name BID UID Age
1 张三 1 1 20
2 李四 1 1 20
3 王五 1 1 20
1 张三 2 2 21
2 李四 2 2 21
3 王五 2 2 21
1 张三 3 4 22
2 李四 3 4 22
3 王五 3 4 22

1.1.9. 数据库范式?

点击显示
  1. 第一范式(1NF)

    在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。 所谓第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性,就可能需要定义一个新的实体,新的实体由重复的属性构成,新实体与原实体之间为一对多关系。在第一范式(1NF)中表的每一行只包含一个实例的信息。简而言之,第一范式就是无重复的列。

  2. 第二范式(2NF)

    第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。 第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是非主属性非部分依赖于主关键字。

  3. 第三范式(3NF)

    满足第三范式(3NF)必须先满足第二范式(2NF)。简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。(我的理解是消除冗余)

    总结一下:

    ​ 第一范式:要求有主键,并且要求每一个字段原子性不可再分

    ​ 第二范式:要求所有非主键字段完全依赖主键,不能产生部分依赖

    ​ 第三范式:所有非主键字段和主键字段之间不能产生传递依赖

1.1.10. 数据库优化?

点击显示

叶落山城秋: 我看了一些文章和书籍,感觉还是 高性能MySQL 这本书的目录总结的好,下面是我个人的感觉的总结....... 可能不准确啊.. 说的很宽泛

  • 创建高性能的索引
    • 这里面就多了,根据业务设计符合业务的索引
  • 查询性能优化
    • 查询之前,设计表的时候应该根据范式和业务结合设计一个好的表
    • 查询语句使用得当,根据索引能获得较大的效果
  • 优化服务器设置
    • 书还没看到这.. 根据目录大约是 根据使用情况修改MySQL默认的配置文件
    • 根据实际情况可做一些负载的设置
    • 配置内存使用等等

1.1.11. 存储过程与触发器的区别?

点击显示

触发器与存储过程非常相似,触发器也是SQL语句集,两者唯一的区别是触发器不能用EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发(激活)执行。

触发器是在一个修改了指定表中的数据时执行的存储过程。

通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器,所以可以用它来强制实施复杂的业务规则,以确保数据的完整性。

触发器不同于存储过程,触发器主要是通过事件执行触发而被执行的,而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时,SQLSERVER就会自动执行触发器所定义的SQL语句,从而确保对数据的处理必须符合这些SQL语句所定义的规则。

1.1.12. 死锁

点击显示

定义: 如果一个线程(进程)集合中的每一个线程(进程)都在等待只能由该线程(进程)集合中的其他线程(进程)才能引发的事件,那么该线程(进程)集合是死锁的!

预防死锁:

  • 破坏占有等待条件
    • 对于需要获取多个资源的线程,一次性获取全部资源,而不是依次获取各个资源
  • 破坏环路等待条件
    • 死锁集合的线程按照等占有线程和等待线程可以组成有向环图,那么如果对所有资源进行排序,所有线程按照资源顺序获取资源

1.1.13. 关系型数据库 MySQL/PostgreSQL的索引类型? 其他数据库优化方法?

1.1.14. 介绍下数据库分库分表以及读写分离?

点击显示
  • 待补充

1.1.15. 同时存在联合索引和单列索引(字段有重复的),这个时候查询mysql会怎么用索引?

点击显示
  • 当一个表有多条索引可走,那么会根据优化器的查询成本来选择走哪个索引

  • 联合索引的本质:

    • 当创建(a,b,c)联合索引时,相当于创建了(a)单列索引,(a,b)联合索引以及(a,b,c)联合索引
    • 想要索引生效,只能使用 aa,ba,b,c三种组合,a,c也可以用,但是只用到了a
  • 需要加索引的字段,要在where条件中

  • 数据量少的字段不需要加索引,因为建索引有一定开销,如果数据量小则没有必要建索引(速度反而慢)
  • 如果where条件中有OR关系,加索引不起作用(此条针对联合索引,如果OR两边是单独的索引..可就不一样了)
  • 联合索引对比每个列分别建立索引更有优势,因为索引建立的越多就越占磁盘空间,在更新数据的时候速度会更慢,另外,建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高

1.1.16. 如果表字段a,b,c都分别建立了索引,如果现在查询条件是 where a=x1 and b=x2 and c=x3 或者 where a=x1 or b=x2 or c=x3 MySQL怎么运用索引?

点击显示
  • and 条件

    当条件是and的时候,并不会三个索引都会去命中,一般会命中其中一个!具体是哪个不一定,MySQL优化器会评估三个条件的索引哪个最高,它就会优先选择最佳的索引去使用

    不过得注意,如果其中一个是主键索引,应该走的就是主键...但是如果有走了覆盖索引(不用回表,估计更快,没测试..猜测...).. 那就不一定了....

    如果只有 where a=x1 and b=x2 只有一个索引生效(是哪个不一定,看MySQL优化器的选择了)

  • or 条件

    当条件是or的时候,那情况就相当复杂了...

    如果 abc是联合索引,用or会导致索引失效,但是如果是单列索引,可能三个索引都会用上

    用explain可以看到 Extra显示Using union(a_index,b_index,c_index); Using where,我MySQL版本是5.6

    查询了下网上,MySQL5.0版本之前,使用or只会使用一个索引,但是5.0之后,开始引入index_merge索引合并优化

    index_merge(explain里type的值)作用:

    • 索引合并是把几个索引的范围扫描合并成一个索引
    • 索引合并的时候,会对索引进行并集,交集或者先交集再并集操作,以便合成一个索引
    • 这些需要合并的索引只能是一个表的,不能对多表进行索引合并
  • 另外,注意如果数据量非常小,可能就不走索引了...还不够费劲的

1.1.17. select max(a) from table,a单独建立了索引,请问该查询是否用到了索引(innodb)?

点击显示

这个是同事刚需要在orm里写原生SQL的时候用到了MySQL内置函数,那么我就想试试,内置函数+索引字段 会不会走索引

比如: select max(a) from table

explain后 Extra显示 Select tables optimized away,优化到已经不能再优化了..

理解意思是MySQL的索引数据结构是B+数,一般叶子节点存的就是已经排序好的数据,这时候获取最大的a值,其实直接在叶子节点拿即可,不需要走索引,优化器已到最优

所以explain的时候 type显示的是 NULL

不过在我测试这个的时候,发现一个有意思的事情,如果where条件后有个范围查询,查询的字段也建立了索引,当数据量大到一定程度(貌似是总数一半)后,就会扫描全表

如果达不到,就走索引!(我简单测试了下,是这样的..具体是不是请自行测试吧..)

1.1.18. 数据库有索引但是还扫了全表的情况?

点击显示
  • 不要使用in操作符,这样数据库会进行全表扫描

    在业务密集的SQL当中尽量不采用IN操作符

  • not in 使用not in也不会走索引

    用not exists或者(外联结+判断为空)来代替

  • <> 操作符(不等于) 使用<>同样不会使用索引,因此对它的处理只会产生全表扫描

    用其它相同功能的操作运算代替,如 a<>0 改为 a>0 or a<0

    a<>改为 a>

  • IS NULL 或IS NOT NULL操作(判断字段是否为空

    判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。

    推荐方案:用其它相同功能的操作运算代替,如

    a is not null 改为 a>0 或a>``等。

  • (大于或小于操作符)>及< 操作符

    大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,

    如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。

    那么执行A>2与A>=3的效果就有很大的区别了,因 为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。

    叶落山城秋:我个人测试了一下mysql,发现在数据大于总数一半左右的时候,就会扫描全表,并不会走索引... 我个人估计是这时候扫描全表比索引还快,所以不走索引了

  • LIKE操作符

    LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,

    如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE '5400%'则会引用范围索引。

    可以采用substr(column,1,4)='5400'

  • UNION操作符

    UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。

    实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如:

    select * from gc_dfys

    union

    select * from ls_jg_dfys

    这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。

    采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。

 > 摘自: [https://www.cnblogs.com/wujianchao1988/archive/2012/05/16/2504221.html](https://www.cnblogs.com/wujianchao1988/archive/2012/05/16/2504221.html)

1.1.19. 如果你项目里需要存IP地址,你会考虑怎么存?

点击显示
  • 如果不清楚的人估计第一反应都是用 varchar 来存! 方便直观
  • 不过在MySQL高性能这本书里推荐的是(unsigned int) 来存储,相比字符串:
    • 节省空间,不管是数据存储空间还是索引存储空间
    • 便于使用范围查询,且效率快
    • 就是在数据库里直观看,不是很方便
  • IPv4用inet_aton 来将IP转成int,用 inet_ntoa 将int转回IP
  • IPv6用inet6_aton 来将IP转成INT,用 inet6_ntoa 将int转回IP

1.1.20. 什么是MySQL XA事务?

点击显示

关于

MySQL XA 事务通常用于分布式事务处理当中。比如在分库分表的场景下,当遇到一个用户事务跨了多个分区,需要使用XA事务 来完成整个事务的正确的提交和回滚,即保证全局事务的一致性。

XA 事务在分库分表场景的使用

下图是个典型的分库分表场景,前端是一个Proxy后面带若干个MySQL实例,每个实例是一个分区。

MYSQLXA事务

假设一个表test定义如下,Proxy根据主键”a”算Hash决定一条记录应该分布在哪个节点上:

create table test(a int primay key, b int) engine = innodb;

应用发到Proxy的一个事务如下:

begin;
insert into test values (1, 1);
update test set b = 1 where a = 10;
commit;

Proxy收到这个事务需要将它转成XA事务发送到后端的数据库以保证这个事务能够安全的提交或回滚,一般的Proxy的处理步骤 如下:

  1. Proxy先收到begin,它只需要设置一下自己的状态不需要向后端数据库发送
  2. 当收到 insert 语句时Proxy会解析语句,根据“a”的值计算出该条记录应该位于哪个节点上,这里假设是“分库1”
  3. Proxy就会向分库1上发送语句xa start ‘xid1’,开启一个XA事务,这里xid1是Proxy自动生成的一个全局事务ID;同时原来 的insert语句insert into values(1,1)也会一并发送到分库1上。
  4. 这时Proxy遇到了update语句,Proxy会解析 where条件主键的值来决定该条语句会被发送到哪个节点上,这里假设是“分库2”
  5. Proxy就会向分库2上发送语句xa start ‘xid1’,开启一个XA事务,这里xid1是Proxy之前已经生成的一个全局事务ID;同时原来 的update语句update test set b = 1 where a = 10也会一并发送到分库2上。
  6. 最后当Proxy解析到commit语句时,就知道一个用户事务已经结束了,就开启提交流程
  7. Proxy会向分库1和分库2发送 xa end ‘xid1’;xa prepare ‘xid1’语句,当收到执行都成功回复后,则继续进行到下一步,如果任何一个分 库返回失败,则向分库1和分库2 发送 xa rollback ‘xid1’,回滚整个事务
  8. 当 xa prepare ‘xid1’都返回成功,那么 proxy会向分库1和分库2上发送 xa commit ‘xid1’,来最终提交事务。

这里有一个可能的优化,即在步骤4时如果Proxy计算出update语句发送的节点仍然是“分库1”时,在遇到commit时,由于只涉 及到一个分库,它可以直接向“分库1”发送 xa end ‘xid1’; xa commit ‘xid1’ one phase来直接提交该事务,避免走 prepare阶段来提高效率。

XA对事务安全的影响分析

从以上分库分表场景下分布式事务的处理过程来看,整个分布式事务的安全性依赖是XA Prepare了的事务的可靠性,也就是在 数据库节点上 XA Prepare了的事务必须是持久化了的,这样当XA Commit发来时才可以提交。设想如下场景:

  1. Proxy已经向分库1和分库2上发送完了 xa prepare ‘xid1’语句,并得到了成功的回复
  2. Proxy向分库1上发送了 ‘xa commit ‘xid1’语句,并已经成功返回
  3. 当 Proxy向分库2上发送 ‘xa commit ‘xid1’时,网络断开了,或者分库2的数据库实例被kill了
  4. 当网络恢复(这时相关的Session已经退出了)或数据库实例再启动后(或切换到备库),XA prepare了的事务已经回滚了, 当Proxy XA commit ‘xid1’发过来后数据库实例根本找不到xid1这个xa事务

上面的过程就导致了分布式事务的不一致:分库1提交了事务,分库2回滚了事务,整个事务提交了一半,回滚了一半。

在MySQL 5.6中以上过程是可能发生的,因为xa prepare并没有严格的持久化,当Session断开,数据库崩溃等情况下这些事务 会被回滚掉,而且的当一个主库配置了SemiSync的备库时xa prepare了的事务也不会被发送的备库,如果主库切换到备库这些 事务也会丢失。

MySQL 5.7 XA可靠性改进

MySQL 5.7解决了 xa prepare了的事务的严格持久化问题,也就是在session断开和实例崩溃重启情况下这些事务不丢,同时在 xa prepare ‘xid1’返回之前XA事务也会同步到备库。下面将通过在5.6和5.7上分别执行xa prepare并对binlog event进行分析 来演示这个改进。

断开连接对xa prepare的事务影响

在5.6和5.7上分别执行如下sql然后断开连接,再重新连接使用的xa recover验证 XA 事务是否回滚了。

xa start 'xid1';
insert into test values(1, 1);
xa end 'xid1';
xa prepare 'xid1';
-- 这里断开再连上新连接执行 xa recover

在 5.6 的版本上将返回空的结果,在 5.7 的版本上返回:

mysql> xa recover;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
|        1 |            4 |            0 | xid1 |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)

说明断开连接后 5.7的prepare了的xa事务没有丢失。

XA 事务的 Binlog events 异同

在5.6和5.7上分别执行如下事务,然后用 show binlog events 查看两者binlog的不同:

xa start 'xid1';
insert into test values(1, 1);
xa end 'xid1';
xa prepare 'xid1';
xa commit 'xid1';

5.6的结果:

mysql-bin.000001 | 304 | Gtid           |      3706 |         352 | SET @@SESSION.GTID_NEXT= 'uuid:2'
mysql-bin.000001 | 352 | Query          |      3706 |         424 | BEGIN
mysql-bin.000001 | 424 | Table_map      |      3706 |         472 | table_id: 71 (test.test)
mysql-bin.000001 | 472 | Write_rows     |      3706 |         516 | table_id: 71 flags: STMT_END_F
mysql-bin.000001 | 516 | Query          |      3706 |         589 | COMMIT

5.7的结果:

mysql-bin.000001 |  544 | Gtid           |      3707 |         592 | SET @@SESSION.GTID_NEXT= 'uuid:3'
mysql-bin.000001 |  592 | Query          |      3707 |         685 | XA START X'78696431',X'',1
mysql-bin.000001 |  685 | Table_map      |      3707 |         730 | table_id: 74 (test.t) 
mysql-bin.000001 |  730 | Write_rows     |      3707 |         774 | table_id: 74 flags: STMT_END_F
mysql-bin.000001 |  774 | Query          |      3707 |         865 | XA END X'78696431',X'',1 
mysql-bin.000001 |  865 | XA_prepare     |      3707 |         905 | XA PREPARE X'78696431',X'',1
mysql-bin.000001 |  905 | Gtid           |      3707 |         953 | SET @@SESSION.GTID_NEXT= 'uuid:4' |
mysql-bin.000001 |  953 | Query          |      3707 |        1047 | XA COMMIT X'78696431',X'',1

可以看到 MySQL 5.6 XA 事务和普通事务的binlog是一样的,并没有体现 xa prepare。而到了 MySQL 5.7 XA 事务的binlog和 普通的事务是完全不同的,XA Prepare有单独的Log event类型,有自己的Gtid,当开启semi-sync的情况下,MySQL 5.7 执行 XA prepare 时会等备库回复后才返回结果给客户端,这样XA prepare执行完就是安全的。

这个是重点

在MySQL 5.7.7 之前,MySQL一直存在一个"bug"。在事务达到PREPARED状态后,客户端断开与MySQL的连接,MySQL 会自动回滚该事务,这个行为不符合分布式事务的规范,MySQL将PREPARED的事务丢失了。之所以MySQL这么实现是因为MySQL 5.7.7 之前PREPARED的事务并不会记录到binlog中。客户端退出后会丢失该信息,如果允许再提交,那么binlog缺少事务信息,会造成主从不一致。

在MySQL 5.7.7 之后,MySQL 新增了一个XA_prepare_log_event的事件,会把xa start到xa prepare中间的操作记录到Binlog中。Slave读取Relay log 进行回放,当SQL Thread读取到PREPARED的事务后,在读取xa commit或者xa rollback前,会进行一个类似客户端断开的操作,继续读取后续的事务信息,不会阻塞SQL Thread的执行。从以上的结果看,Oracle在MySQL 5.7.7 上确实完美的解决了MySQL XA一直存在的一个"bug"。

以上摘自:

MySQL XA 介绍

MySQL · 特性分析 · 浅谈 MySQL 5.7 XA 事务改进


以上题目和答案大部分摘自网络, 叶落山城秋后为自己口头所答...

参考:

go 夜读

interview_internal_reference

欢迎加入PHP交流群(QQ群号):PHP编程技术交流群 440221268

欢迎加入Golang交流群(QQ群号):PHP/Golang技术交流群 423069874

results matching ""

    No results matching ""