MySQL杂项
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情况下读取的是事务开始时的版本数据。
索引类型
- B+树索引 平衡N叉查找树
- 全文索引
- 哈希索引 innodb内部使用,不可人工干预
B+索引不能找到给定键值的具体行,只能找到数据行所在的页。然后数据库将该页数据读到数据库中,再在内存里进行查找。页中是按照主键顺序存放的,对于某条具体记录的查询是通过二分查找算法得到的
相关数据结构和算法
二分查找法 先将数据按照递增或递减的顺序进行排列,然后计算整个队列的中间位置,把中间位置的数据与目标数据进行查找,如果没有找到则在左半部分或右半部分继续采用这种方式进行查找。如果不考虑排序的时间复杂度,查找的时间复杂度为O(n);
二叉查找树 对于任何一个节点,左子树的键值总是小于节点自身的键值,右子树的键值总是大于节点自身的键值。
平衡二叉树 首先是一棵二叉查找树,另外需要满足任何节点的两个子树的高度差最大为1。 构造一棵平衡二叉树是通过左旋右旋来操作的,存在一定的维护成本,但多用在内存结构中,所以维护成本又相对小了很多。
B+树
- 每个节点可以存放多个键值,按照顺序存放。
- 所有的记录都存在在叶子节点,同一层的叶子节点通过指针相连,组成类似一个双向循环链表的数据结构。
- 在数据库中,B+树的高度一般在2-4层,也就是执行一次查找最多需要2-4次IO
B+索引
聚集索引
辅助索引
聚集索引和辅助索引的区别:
无论是聚集索引还是辅助索引,都是B+树的结构。
两者区别主要是叶子节点存储的数据不一样。聚集索引的叶子节点存储的是整行的数据(确切的说是存储的是指向行数据的指针),而辅助索引的叶子节点存储的是与之对应的主键值。因此通过辅助索引来查找数据需要两次查找索引。
个数不一样。一张innodb表最多只有一个主键,所以最多有一个聚集索引。而辅助所以可以有多个。
聚集索引
就是按照每张表的主键构建一颗B+树,叶子节点存放的就是整行记录,成为数据页,数据页之间通过双向指针来进行连接。
因为每张表最多只有一个主键,所以每张表也最多拥有一个聚集索引。
聚集索引的结构只是逻辑结构,并不是物理结构。也就是说聚集在在物理存储上并不是连续的:
- 页之间是通过指针来连接的:同层的数据页之间通过双向指针连接,数据页和索引页或者索引页之间通过单项指针连接。
- 索引页中存放的主键是顺序存放的。
- 数据页中的行记录之间是通过双向指针链接的,逻辑上是按照主键顺序存放的。
也就是说物理存储上可以不按照主键顺序存储。
在表空间的物理存储文件中,即存放了索引页也存放了数据页。比如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,然后断开当前连接) 所以两者任何时刻都是等价的,只是跟随参数值的不同有不同含义。
系统推荐
- 各种网关性能测试
- Git合并多个提交并push到远程仓库
- Spring Cloud(一):服务治理技术概览【Finchley 版】
- Hadoop 一
- kafka
- Flutter开发需要涉及的知识点大纲
- Javassist整理
- Markdown软件比对
- MySQL数据迁移到PGSQL
- Docker跨主机通信方案
- ESRally性能测试步骤
- PostgreSQL高可用
- 随机毒鸡汤:人不是慢慢变老的,而是一瞬间变老的。