MySQL是一个网络服务。大多数情况下,会有很多客户端连接MySQL服务。当多个客户端访问同一个表时,可能会出现问题。 比如:火车票售票系统,当两个客户端同时买票,操作同一张票数表。当客户端A检测还有一张票,将票买掉,但是还没有更新数据库。于此同时,客户端B,也在买票,也检测到还有一张票,客户端B也将票买了。这样就导致一张票被卖了两次。 要解决上面的问题,至少需要满足下面的属性(拿买票的过程举例):
事务就是一组DML类的SQL语句,这些语句在逻辑上存在相关性,这一组DML语句要么全部成功,要么全部失败,是一个整体。 简单来说,事务就是要完成一件事,所用到的所有SQL语句(至少一条)。这些语句要么全部执行成功,要么全部执行失败。即,其中一条执行失败,就全部执行失败。数据立马回滚到执行前的状态。
上面四个属性,可以简称ACID。
修改事务的提交方式:
事务没有执行完发生了错误,会回滚到最开始,相当于这个事务没有发生一样。 提交方式为:自动提交。 提交方式为:手动提交
再次说明,MySQL是一个网络服务,同一时间可能有很多客户端连接。那么在多个事务在执行多个SQL时,有可能出现问题,比如:多个事务访问同一张表,同一行数据时。 一个事务的时间段可以分为执行前(该事务还没有开始),执行中(该事务正在执行),执行后(该事务已经提交)。 为了提高效率,执行过程并不是串行化,而是允许事务受到不同程度的干扰,于是就有了一个重要的特征:隔离级别。
读提交和可重复读的区别: 事务A修改数据,提交后(commit)。读提交,事务B在提交前(commit)可以看到事务A修改后的数据。可重复读,事务B在提交前(commit)看不到事务A修改后的数据,事务B在提交后,才能看到。 设置隔离等级: 设置当前会话隔离性,另起一个会话,隔离性不会被设置,只影响当前会话。 设置全局隔离级别,另起一个会话,会被影响。 会话隔离级别开始启动时,会和全局隔离级别一样。 设置了全局会话隔离级别,当前会话隔离级别并没有改变,需要重启MySQL才会改变当前会话隔离级别。 修改当前会话隔离级别:
现象:如果隔离级别为读未提交,一个事务A增删改表的内容,未提交事务,在另外一个事务B中可以看到变化的内容。 但是,如果事务A没有提交异常退出了,内容会发生回滚,回滚到事务开始前的内容,原因是事务的原子性。事务A提交了,事务B,提交后也可以看到修改之后的内容,这是应为事务的持久性。 现象:两个事务,事务A和事务B,隔离等级为读提交。事务A增删改表的内容,未提交事务,在事务B中,看不到增删改的内容。当事务A提交后,事务B中可以看到增删改的内容。注意事务B没有提交。 问题:此时在事务B中,没有提交事务。但是同样的读取,在同一个事务内,在不同的时间段,读取到的值不同,这种现在叫做不可重复读取。 现象:两个事务,事务A和事务B。事务A增删改表的内容,未提交,事务B看不到修改后的数据;事务A提交后,事务B没有提交,仍然看不到提交的数据;事务B提交后,由于持久化,可以看到修改后的数据。 注意:并不是,同一时段的两个事务,事务A提交的数据,事务B就一定看不到。这个取决于select 的位置。下面详细解释了。
现象:当前有多个事务时,一个事务要增删改表的数据,会发生阻塞。当其它数据全部退出,才能正常进行增删改操作。使得增删改时,事务之间运行是串行的。效率比较低。 但是:如下图,我们发现,查找,并没有串行化,有多个事务同时运行时,查找不会被阻塞,串行化。这是因为查询并不会修改数据。
对数据库操作实际上就是对数据库进行读和写的操作,并发就是,多个事务同时对数据库进行读和写操作。而数据库正是对在效率和安全方面的考虑,解决读和写并发的问题。
读和读,写和写的问题好处理,但是读和写的问题就比较麻烦,下面主要讨论如何解决读和写并发问题。 主要是:为事务分配单向增长的事务ID,为每一个修改保存一个版本,版本与事务ID关联,读操作只读改事务开始前的数据的快照(快照,后面有解释,需要先理解,才能理解)。所以MVCC可以为数据库解决以下问题:
想要理解MVCC需要知道三个前提知识: 当我们在数据库中创建一个表时,表中的字段不仅会有我们设计的字段,还有有几个隐藏字段。
补充:实际上还会有一个删除flag隐藏字段,即记录删除。实际上的删除一个字段,并不是真正的删除,而是将flag设置为删除标记。 此时:表的全部信息为: 我们目前并不知道创建该记录的事务ID,隐式主键。默认设置成NULL和1。第一条记录之前也没有其它版本,我们设置回滚指针为NULL。 MySQL是一个服务进程,所有操作都需要在内存中完成的。比如:修改数据,先将数据拿到内存中,修改后,再刷新到磁盘中。日志也是一样,先将日志信息写到MySQL内部缓冲区中,之后再刷新到磁盘中。 说明:现在有一个事务,事务ID为10,对student表中的字段做的修改(update),将name从张三,修改成了李四。
注意:修改,修改的是最新记录。
简单来说就是,在我们某个事务进行快照读时,对该记录创建一个read view,把他当作条件,用来判断当前事务能够看到哪个版本的数据。即,可能时最新版本,也可能是undo log中的版本。 在read view中主要由以下四个成员:
看下面源代码就懂了。并且可重复读和读提交,是因为read view不同,所以呈现的现象不同。下面有详细介绍。 对应源码策略:
MVCC流程演示,方便理解: 事务修改了name,将name有张三修改成了李四。 当事务2对某行数据进行快照读,数据库为改行数据形成移和read view读视图。 只有事务4修改了数据,并在事务2快照读前,提交了事务。
设置当前会话的隔离级别为可重复读
可重复读和读提交本质区别:
|
MySQL 分页是个很常见的项目需求,因为数据量的问题,我们既无法一次导出所有的数据给用户,用户也无法一次性看完所有的数据,所以分页实际上是个很常见的需求。
除了感叹中文内容圈子里有用的内容越来越稀缺,我想做点什么外,我也想将自己的找到解决方案记录下来,以便以后自己忘记了,能有据可查。
对于 MySQL 这样烂大街的数据库,分页的实现有无数的教程和例子,但是大多数的例子就是下面这样的
这样的分页实现,是最为常见的分页方案,也是最简单最容易理解的分页方案。如果要考虑总页数,那就自己增加一个全库的 COUNT()
查询总数即可。
但是这样的分页方案也有明显的缺陷,那就是在数据量大的时候,limit
语句容易造成全表扫描,因而效率较低
这里的数据量大的程度,是指数据的条目数以百万甚至千万起算,并且这里的查询均以为 主键
作为查询的条件,不涉及其他索引和其他语句的查询的前提下。
其实数据量小的情况下,直接使用 limit 查询最方便,不必去折腾什么高性能的查询分页的实现,一来没有必要,二来过早优化
在没有用到排序的情况下,直接使用 limit 来分页,即使有千万级别的数据,其实性能是相当不错的,能够在秒级别返回查询结果,对于多数的应用来说是够用的
针对使用limit offset在大量数据里的性能问题,如果使用子查询的话,尤其是涉及 主键 排序的时候的性能,有个稍微变通的查询方法
使用第二种 SQL In Query 实现,可以考虑将需要分页表中的主键 ID 单独成表,然后独立查询,如果数据量实在太大的情况下,这样的单独主键 ID 表能极大提供分页排序输出的速度。
这样的子查询的方式,跟前面的最简单的分页方式近似,也兼顾了性能。千万级别的数据能在秒级别返回查询结果,也能一定程度使用带索引的列进行排序。
这样的方式稍微兼顾了主键排序的结果,还是可以使用的。很多的互联网公司内(比如阿里巴巴,阿里的 JAVA 开发手册内有这个案例)也是将这个语句作为默认的分页实现的
其实没有什么流式分页这种业界的分页方式,我自己创造的名词实现吧
多数的时候大家将这样的方式叫做 游标分页
对于 timeline 这样的数据流,传统的分页显得很不合适,因为时间线的条目是一直增长的,并且增长得很快,你很难去定义第一页第二页这样的概念。传统的分页对于时间线这样的实现,很容易造成漏数据。
但是时间线的数据流是天然自增的数据,因此引入流式分页的概念,严格来说,流式分页并不能算成一种分页的方式,因为它只是针对数据流某个时间间隙之间的数据
但是大家一般都会困惑于 上一页 如何实现的,其实最简单的方案就是类似 Facebook 这样的针对向前翻页和向后翻页的区别 URL 参数对待,否则的话,要么建立缓存表或者另建立分页表,要么就只能一页一页往下翻,不能往前翻页(类似于手机时间线流动的感觉)
流式分页/游标分页实现起来尤其简单,不过只适合主键 ID 自增且连续的情况,性能也好到不得了
前端记得针对 向前翻页 和 向后翻页 区别 URL 参数对待,这是编程中大多数同学没法转过弯来的地方。并且 min_id/since_id 和 max_id/last_id 不必同时传入查询,因为这样就无法掌控查询的条目数量(从用户前端查询的角度而言,不是从 DBA 的角度而言)
最好的方式是类似 Facebook 这样的方式 min_id/since_id 和 max_id/last_id 只传入其中一个,每个分页的条目数量使用 limit 字段单独管控,也方便限制用户前端查询条目数量,不至于查询一个巨大的数量导致数据库卡死。
此处暂无,以后我有发现再更新吧
其实有一些使用 MySQL+Redis 实现的分页方式,感觉略微奇葩,只能针对特定的场景使用,这里就不一一整理了
对于普通的应用,尤其是数据量在百万级别以下的应用,其实 MySQL 通用的分页方式性能足够,而且足够简单并且方便使用,不必过早优化。只要做好最大分页数量和分页每页的数量限制,MySQL 的缓存适当针对机器的性能和应用的实际需求调优一下,通用的分页方式性能足够。我想,多数的应用,其实都是到不了百万级别的数据量吧
不过考虑到编程实现的方便程度,其实最后的流式分页/游标分页的方式,编程实现是最简单的,并且性能根本无须考虑,哪怕你有亿级的数据。并且流式分页/游标分页非常适合在 API 中分页输出,在手机信息流中这种无线滚动的信息流中进行分页输出,不会丢也不会重复输出某个条目,优势非常明显,堪称最佳的分页方式。不过话说回来,缺点也很明显,就是不能像普通分页那样有第一页第二页到第N页的数字页码,简单说就是没有页数的概念了,前端页面输出一般只有上一页和下一页这样简单的翻页按钮
需要提醒的是,所有的分页查询条件都必须建立索引,并且不能有 TEXT 字段。MySQL 的索引字段中,VARCHAR(32) 我都觉得很大了, TEXT 字段简直是不能容忍的,不怕死的可以自己试试。
发布时间: 09:59:43 来源:亿速云 阅读:106 作者:小新 栏目:
小编给大家分享一下如何随机取数据最高效率,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!