MySQL杂项

作者 : 松哥资源 本文共3129个字,预计阅读时间需要8分钟 发布时间: 2018-11-5 共627人阅读




示例标题

MySQL

事务的一个实践问题

事务隔离级别:可重复读

存在上述情况的场景可能如下:
验证苹果的购买收据。

前端将购买的收据传递给server,server调用苹果接口执行校验操作,如果校验成功则更改订单状态

因为购买凭证不允许存在丢失,server端收到请求后应该立即执行保存,但并不能保证每个请求都保存成功,所以client端必须有重试机制,直到server端返回了指定的数据,

另外苹果的校验接口是第三方接口,所以系统必须假设调用第三方接口的时候出现各种问题,比如网络原因,第三方接口服务不可用或响应时间很慢的情况。因此server端的校也必须有重试机制,直到第三方接口返回了有效数据。

从上述校验过程可以知道server有可能不能及时响应给客户端,为了尽可能的将校验结果及时返回给前端,所以server接收到请求后采用 异步后再同步 的措施来处理(也可以采用 同步后再异步)。具体过程如下:
1.server接收到请求后,首先执行入库。如果入库失败,返回retry。入库成功后,执行下面的步骤
2.异步执行receipt的验证过程,校验成功或失败都要更新数据库,比如对校验执行的次数进行加1操作。
3.限时等待异步校验结果,如果指定时间内拿到结果,则直接返回给前端success或failure。如果指定时间内没有拿到结果,则返回proccessing。

上述过程如果实现不恰当会造成前端总是得到不success或failure的结果。比如这样实现:

threadA开启了事务tx1,执行了receipt的入库操作并获取了主键id。
然后将id传递给了threadB,让threadB异步执行校验过程,然后threadA限时等待threadB完成,比如30s。threadB拿到校验结果后开启tx2更新数据库。此时如果threadA的限时等待过程也在事务tx1内执行,那么就会出现前端总是拿不到suceess或failure的结果。因为threadA的实现要等待30s后才能提交事务tx1,而这个过程中threadB又要等待tx1的提交。所以造成了上诉结果。因此threadA的限时等待过程不能再tx1内执行,必须在tx1提交后再执行。

一致性锁定读和一致性非锁定度的区别

就是加锁和不加锁的区别,一致性锁定读会对读取的数据加X锁或S锁,一致性非锁定读之所以可以不加锁,是因为它读取的是历史版本数据。
在read-committed下,读取的是最新版本的数据

在repeatable-read情况下读取的是事务开始时的版本数据。

索引类型

  1. B+树索引 平衡N叉查找树
  2. 全文索引
  3. 哈希索引 innodb内部使用,不可人工干预
B+索引不能找到给定键值的具体行,只能找到数据行所在的页。然后数据库将该页数据读到数据库中,再在内存里进行查找。页中是按照主键顺序存放的,对于某条具体记录的查询是通过二分查找算法得到的

相关数据结构和算法

  1. 二分查找法
    先将数据按照递增或递减的顺序进行排列,然后计算整个队列的中间位置,把中间位置的数据与目标数据进行查找,如果没有找到则在左半部分或右半部分继续采用这种方式进行查找。如果不考虑排序的时间复杂度,查找的时间复杂度为O(n);

  2. 二叉查找树
    对于任何一个节点,左子树的键值总是小于节点自身的键值,右子树的键值总是大于节点自身的键值。

  3. 平衡二叉树
    首先是一棵二叉查找树,另外需要满足任何节点的两个子树的高度差最大为1。
    构造一棵平衡二叉树是通过左旋右旋来操作的,存在一定的维护成本,但多用在内存结构中,所以维护成本又相对小了很多。

  4. B+树

    1. 每个节点可以存放多个键值,按照顺序存放。
    2. 所有的记录都存在在叶子节点,同一层的叶子节点通过指针相连,组成类似一个双向循环链表的数据结构。
    3. 在数据库中,B+树的高度一般在2-4层,也就是执行一次查找最多需要2-4次IO

B+索引

  1. 聚集索引

  2. 辅助索引

  3. 聚集索引和辅助索引的区别:

    1. 无论是聚集索引还是辅助索引,都是B+树的结构。

    2. 两者区别主要是叶子节点存储的数据不一样。聚集索引的叶子节点存储的是整行的数据(确切的说是存储的是指向行数据的指针),而辅助索引的叶子节点存储的是与之对应的主键值。因此通过辅助索引来查找数据需要两次查找索引。

    3. 个数不一样。一张innodb表最多只有一个主键,所以最多有一个聚集索引。而辅助所以可以有多个。

聚集索引

就是按照每张表的主键构建一颗B+树,叶子节点存放的就是整行记录,成为数据页,数据页之间通过双向指针来进行连接。

因为每张表最多只有一个主键,所以每张表也最多拥有一个聚集索引。

聚集索引的结构只是逻辑结构,并不是物理结构。也就是说聚集在在物理存储上并不是连续的:

  1. 页之间是通过指针来连接的:同层的数据页之间通过双向指针连接,数据页和索引页或者索引页之间通过单项指针连接。
  2. 索引页中存放的主键是顺序存放的。
  3. 数据页中的行记录之间是通过双向指针链接的,逻辑上是按照主键顺序存放的。

也就是说物理存储上可以不按照主键顺序存储。

在表空间的物理存储文件中,即存放了索引页也存放了数据页。比如t.idb中即存放了表t的聚集索引的索引页也存放了数据页。也会存放辅助索引的索引页和数据页

从聚集索引的结构上可以知道:它对于主键的排序查找和范围查找是非常快的。因为在只要找到一个开始数据,就可以通过数据页之间的双向指针来快速定位下一条目标数据。

FIC:fast index creation

从MYSQL5.5开始或从InnoDB1.0.x开始支持FIC,会对表加上一个S锁,因此如果此时表上有有X或IX锁,那么fic操作会被阻塞。同事如果fic操作过程中有写操作或者dml,那么写也会被阻塞。

OLTP与OLAP的区别

oltp应用中,查询操作一般只充数据库中取出少量数据。而olap应用中,需要访问表中大量的数据,做一些统计操作,得出一些报表数据。

覆盖索引

在辅助索引中就可以得到要查询的数据,而不需要再次查询聚集索引。如果explain结果集中的extra列显示有using index,则表示使用了覆盖索引。

查询优化器什么时候会选择辅助索引来完成查询?

首先筛选的列存在相应的辅助索引。同时满足下面两个条件中一个
1. 可以使用覆盖索引
2. 如果不能使用覆盖索引,那么要查询的数据量要少于整张表数据的20%的样子。因为如果要查询大量的数据,那么还需要通过聚集索引来离散读,这个性能反而可能不好。

当然可以通过 use index 来强制使用指定的索引

开启事务

mysql获取连接后,在执行begin(set autocommit =0)的时候,会检查当前连接中是否存在未提交的事务,如果存在会先提交之前的事务,然后再开启一个新的事务。也就是说如果在连续两次执行begin或set autocommit=0就会提交之前尚未提交的事务。

commit 与 commit work的区别:

当@@completion_type=0,两者等价
当@@completion_type=1,两者等价,意思是 commit and chain
当@@completion_type=2,两者等价,意思是 commit and release(执行这个后,会先commit,然后断开当前连接)
所以两者任何时刻都是等价的,只是跟随参数值的不同有不同含义。



  • 随机毒鸡汤:比了个耶以后,你就毕了个业。


文章来源于justsoso.fun: MySQL杂项





发表回复