MySQL杂项

Rocky大约 9 分钟

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，然后断开当前连接） 所以两者任何时刻都是等价的，只是跟随参数值的不同有不同含义。


系统推荐







  • 随机毒鸡汤：静若瘫痪，动若癫痫。