肥城 网站建设,做早餐烧菜有什么网站,centos网站开发,网店建设管理系统MySQL
1. MySQL 有几种锁#xff1f;
三种锁的特点
表级锁#xff1a;开销小#xff0c;加锁快#xff1b;不会出现死锁#xff1b;锁定颗粒度大#xff0c;发生锁冲突的概率最高#xff0c;并发度最低。行级锁#xff1a;开销大#xff0c;加锁慢#xff1b;会…MySQL
1. MySQL 有几种锁
三种锁的特点
表级锁开销小加锁快不会出现死锁锁定颗粒度大发生锁冲突的概率最高并发度最低。行级锁开销大加锁慢会出现死锁锁定颗粒度最小发生所冲突的概率最低并发度也最高页面锁开销和加锁时间介于表所和行锁之间会出现死锁锁定颗粒度介于表所和行锁之间并发度一般
2. MySQL 中有哪些不同的表格
共有 5 中类型的表格
MyISAM MyISAM 是 MySQL 最常用的表格类型之一。它使用独立的数据文件.MYD和索引文件.MYI存储数据和索引数据文件仅保持数据索引文件仅保持索引。 Heap InnoDB 表格是 MySQL 中另一种常用的表格类型支持事务处理同时也具有行锁和外检约束等特性。InnoDB 表格通过聚簇索引clustered index来存储数据而不是使用 MyISAM 的分离索引存储数据 Merge Memory 表格是存储在内存中的表格也被称为 HEAP 表格。他被设计用于具有高执行速度和对短暂数据的快速访问场景但是数据在 MySQL 关闭后会被清除 INNODBISAM
3. 简述 MySQL 数据库中 MyISAM 和 InnoDB 的区别
MyISAM
不支持事物但是每次查询都是原子的支持表级锁即每次操作是对整个表加锁存储表的总行数一个 MYISAM 表有三个文件索引文件、表结构文件、数据文件采用非聚集索引索引文件的数据域指向数据文件的指针。辅索引与主索引基本一致但是辅索引不用保证唯一性
InnoDB 支持 ACID 的事物支持事物的四种隔离级别 支持行级锁及外检约束因此可以支持写并发 不存储总行数 一个 InnoDB 引擎存储在一个文件空间共享表空间表大小不受操作系统限制一个表可能分布在多个文件里也有可能为多个设置为独立表空间表大小收操作系统文件大小限制一般为 2G受操作系统文件大小的限制主键索引采用聚集索引索引的数据域存储数据文件本身辅索引的数据域存储主键的值因此从辅索引查找数据需要先通过辅索引找到主索引在访问辅索引最好使用自增主键防止插入数据时为维持 B 树结构文件的大调整
4. MySQL 中 InnoDB 支持的四种事务隔离级别名称以及逐级之间的区别
SQL 定义标准的四个隔离级别为
read uncommited读到未提交数据read committed脏读不可重复读repeatable read可重读serializable串行事务
5. CHAR 和 VARCHAR 的区别
5.1 固定长度 可变长度 VARCHAR 类型用于存储可变长度的字符串最大 65532 个字节字符 CHAR 类型用于存储固定长度的字符串最大 255 个字节字符
5.2 关于 CHAR 类型的创建细节
CHAR 列长度固定为创建表时声明的长度长度范围值时 1 到 255当 CHAR 指被存储时它们被用空格填充到特定长度检索 CHAR 值时需删除尾随空格。
5.3 在检索和存储方面会 CHAR 和 VARCHAR 会有所不同
6. 主键和候选键有什么区别 表格的每行都由主键唯一标识一个表只有一个主键 主键也是候选键。按照惯例候选键可以被指定为主键并且可以用于任何外键引用。
二者关系
主键是候选键的子集。也就是所有的候选键都由成为主键的资格就看你选谁当主键。
7. myisamchk 是用来做什么的
它用来压缩 MyISAM 表这减少了磁盘或内存的使用。
MyISAM Static 和 MyISAM Dynamic 有什么区别
在 MyISAM Static 上的所有字段有固定宽度。动态 MyISAM 表将具有像 TEXTBLOB 等字段以适应不同长度的数据类型。
MyISAM Static 在受损的情况下更容易恢复。
8. 如果一个表有一列定义为 TIMESTAMP将发生什么
每当行被更改时时间戳字段讲获取当前时间戳。
列设置为 AUTO INCREMENT 时如果在表中达到最大值会发生什么
它会停止递增任何进一步的插入都将产生错误因为秘钥已被使用。
怎样才能找出最后一次插入时分配了那个自动增量
LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值并且不需要指定表名称。
9. 你怎么看到为表格定义的所有索引
索引是用过以下方式为表格定义的
SHOW INDEX FROM ;
10. LIKE 声明中的 % 和 _ 是什么意思
% 对应于 0 个或更多字符_ 只是 LIKE 语句中的一个字符
如何在 Unix 和 MySQL 时间戳之间进行转换
UNIX_TIMESTAMP 是从 MySQL 时间戳转换为 Unix 时间戳的命令FROM_UNIXTIME 是从 Unix 时间戳转换为 MySQL 时间戳的命令
11. 列对比运算符是什么
在 SELECT 语句的列比较中使用 、、、、、、、、、AND、OR 或 LIKE 运算符。
12. BLOB 和 TEXT 有什么区别
BLOB 是一个二进制对象可以容纳可变数量的数据。TEXT 是一个不区分大小写的 BOLB。
BOLB 和 TEXT 类型之间的唯一区别在于对 BOLE 值进行排序和比较时区分大小写对 TEXT 值不区分大小写。
13. MySQL_fetch_array 和 MySQL_fetch_object 的区别是什么
mysql_fetch_array 和 mysql_fetch_object 都是 MySQL 查询结果集中获取一行数据的函数但它们的返回值和使用方式有所不同。
MySQL_fetch_array - 将结果行作为关联数组或来自数据库的常规数组返回MySQL_fetch_object - 从数据库返回结果行作为对象
总的来说mysql_fetch_array 返回的是数组mysql_fetch_object 返回的是对象。
14. MyISAM 表格将在那里存储并且还提供存储格式
每个 MyISAM 表格以三种格式存储在磁盘上
“.frm” 文件存储表定义数据文件具有 “.MYD”(MYData) 扩展名索引文件具有 “.MYI”(MYIndex) 扩展名
15. MySQL 如何优化 DISTINCT
DISTINCT 在所有列上转换为 GROUP BY并与 ORDER BY 子句结合使用 DISTINCT 的介绍与使用https://blog.csdn.net/weixin_47498250/article/details/106861000
16. 如何显示前 50 行
在 MySQL 中使用以下代码查询显示前 50 行
select * from table_name limit 0, 50;17. 可以使用多少列创建索引
任何标准表最多可以创建 16 个索引列。
18. NOW() 和 CURRENT_DATE() 有什么区别
NOW() 命令用于显示当前年份、月份、日期、小时、分钟和秒。
CURRENT_DATE() 仅显示当前年份月份和日期。
19. 什么是非标准字符串类型
TINYTEXTTEXTMEDIUMTEXTLONGTEXT
20. 列的字符串类型可以是什么
字符串类型是
SETBLOBENUMCHARTEXT
21. 什么是通用 SQL 函数
CONCAT(A, B) - 链接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段FORMAT(X, D) - 格式化数字 X 到 D 有效数字CURRDATE(), CURRTIME() - 返回当前日期或时间NOW() - 将当前日期和时间作为一个值返回MONTH(), DAT(), YEAR(), WEEK(), WEEKDAY() - 从日期值中提取给定数据HOUR(), MINUTE(), SECOND() - 从时间值中提取给定数据DATEDIFF(A, B) - 确定两个日期之间的差异通常用于计算年龄SUBTIMES(A, B) - 确定两次之间的差异FROMDAYS(INT) - 将整数天转换为日期值
22. MySQL 支持事务吗
在缺省模式下MySQL 是 autocommit 模式的所有的数据库更新操作都会即时提交所以在缺省情况下MySQL 是不支持事务的。
但如果你的 MySQL 表类型是使用 InnoDB Tables 或 BDB tables 的话你的 MySQL 就可以使用事务处理使用 SET AUTOCOMMIT 0 就可以使 MySQL 允许在非 autocommit 模式在非 autocommit 模式下你必须使用 COMMIT 来提交你的更改或者用 ROLLBACK 来回滚你的更改。
23. MySQL 里记录货币用什么字段好
NUMERIC 和 DECIMAL 类型被 MySQL 实现为同样的类型这在 SQL92 标准允许。他们被用于保存值该值的准确精度是极其重要的值例如与金钱相关的数据。
例如
salary DECIMAL(9, 2)
在这个例子中9 代表将被用于存储值的总的小数位数而 2 代表将被用于存储小数点后的位数。
因此在这种情况下能被存储在 salary 列中的值的范围从 -9999999.99 到 9999999.99
24. MySQL 有关权限的表都有哪几个
MySQL 服务器通过权限表来控制用户对数据库的访问权限表存放在 MySQL 数据库里由 MySQL_install_db 脚本初始化。这些权限表分别user, db, table_priv, columns_priv 和 host。
25. MySQL 数据库作发布系统的存储一天五万条以上的新增预计运维三年怎么优化
设计良好的数据库结构允许部分数据冗余尽量避免 join 查询提高效率。选择适合的表字段数据类型和存储引擎适当的添加索引。MySQL 库主从读写分离。找规律分表减少单表中的数据量提高查询速度。添加缓存机制比如 memcached, apc 等。不经常改动的页面生成静态页面。书写高效率的 SQL比如 SELECT * FROM TABLE 改为 SELECT field_1, field_2, field_3 FROM TABLE.
26. 锁的优化策略
读写分离分段加锁减少锁持有的时间多个线程尽量以相同的顺序去获取资源不能将锁的颗粒度过于细化不然可能会出现县城的加锁和释放锁次数过多反而不如一次加一把大锁。
27. 索引的底层实现原理和优化
B 树经过优化的 B 树
主要是在所有的叶子结点中增加了指向下一个叶子节点的指针因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。
28. 什么情况下设置了索引但无法使用
以 “%” 开头的 LIKE 语句模糊匹配OR 语句前后没有同时使用索引数据类型出现隐式转换如 VARCHAR 不加单引号的话可能会自动转换为 INT 类型
29. 实践中如何优化 MySQL
最好按照以下顺序优化
SQL 语句及索引的优化数据库结构的优化系统配置的优化硬件的优化
详细可查看https://mikechen.cc/3305.html
30. 优化数据库的方法
选取最合适的字段属性尽可能减少定义字段宽度尽量把字段设置 NOTNULL例如“省份”、“性别”最好试用 ENUM使用连接JOIN来代替子查询事务处理锁定、优化事务处理使用外键优化锁定表建立索引优化查询语句
31. 简单描述 MySQL 中索引、主键、唯一索引、联合索引的区别对数据库的性能有什么影响
索引介绍 是一种特殊的文件InnoDB 数据表上的索引是表空间的一个组成部分它们包含着对数据表里所有记录的引用指针
普通索引由关键字 KEY 或 INDEX 定义的索引的唯一任务是加快对数据的访问速度唯一索引普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列只包含彼此各不相同的值在为这个数据列创建索引的时候就应该用关键词 UNIQUE 把它定义为一个唯一索引。也就是说唯一索引可以保证数据记录的唯一性。主键是一种特殊的唯一索引在一张表中只能定义一个主键索引主键用于唯一标识一条数据使用关键字 PRIMARY KEY 来创建联合索引索引可以覆盖多个数据列如像INDEX(columnA, columnB) 索引这就是联合索引
利弊索引可以极大的提高数据的查询速度但是会降低插入、删除、更新表的速度因为在执行这些写操作时还要操作索引文件。
32. 数据库中的事务是什么
事务transaction是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功则认为事务成功即使只有一个操作失败事务就认定为失败。
如果所有操作完成事务则提交其修改将作用于其他数据库进程。如果一个操作失败则事务将回滚该事物所有操作的影响都将取消。
事务特性
原子性即不可分割性事务要么全部被执行要么全部不被执行。一致性或可串性事务的执行使得数据库从一种正确状态转化成另一种正确的状态隔离性在事务正确提交之前不允许把该事物对数据的任何改变提供给任何其它事务持久性事务正确提交后其结果将永久保存在数据库中即使在事务提交后有了其它的故障事务的处理结果也会得到保存
或者这样理解
失误就是被绑定在一起作为一个逻辑工作单元的 SQL 语句分组如果任何一个语句操作失败那么整个操作就失败以后操作就会回滚到开启事务操作前的状态或者是上面的某个节点。为了确保要么执行要么不执行就可以使用事务。要讲组语句作为事务考虑就需要通过 ACID 测试即原子性、一致性、隔离性和持久性
33. SQL 注入漏洞产生的原因如何防止
SQL 注入产生的原因程序开发过程中不注意规范书写 SQL 语句和对特殊字符进行过滤导致客户端可以通过全局变量 POST 和 GET 提交一些 SQL 语句正常执行。
防止 SQL 注入的方式 开启配置文件中的 magic_quotes_gpc 和 magic_quotes_runtime 设置 执行 SQL 语句时使用 addslashes 进行 SQL 语句转换 SQL 语句书写尽量不要省略双引号和单引号 过滤掉 SQL 语句中的一些关键字UPDATE、INSERT、DELETE、SELECT、* 提高数据库表和字段的命名技巧对一些重要的字段根据程序的特点命名取不易被猜到的
34. 为表中的字段选择合适的数据类型
字段类型优先级整形 date, time enum, char varchar blob / text
优先考虑数据类型其次是日期或二进制类型最后是字符串类型同级别的数据类型应该优先选择占用空间小的数据类型
35. 存储时期
Datatime以 YYYY-MM-DD HH:MM:SS 格式存储时期时间精确到秒占用 8 个字节的存储空间datatime 类型与时区无关
Timestamp以时间戳格式存储占用 4 个字节范围小 1970-1-1 到 2038-1-19显示依赖于所指定的时区默认在第一列行的数据修改时可以自动的修改
Date生日占用的字节数比使用字符串 .datetime.int 存储要小使用 date 只需要 3 个字节存储时期月份还可以利用日期时间函数进行日期间的计算
Time存储时间部分的数据
注意不要使用字符串类型来存储日期时间通常比字符串占用的存储空间小在进行查找过滤可以利用日期的函数
使用 int 存储日期时间不如使用 timestamp 类型
36. 对于关系型数据库而言索引是相当重要的概念请回答有关索引的几个问题
索引的目的是什么 快速访问数据表中的特定信息提高检索速度创建唯一性索引保证数据库表中每一行数据的唯一性、加速表和表之间的连接使用分组和排序子句进行数据检索时可以显著减少查询中分组和排序的时间 索引对数据库系统的负面影响是什么 创建索引和维护索引需要消耗时间这个时间随着数据量的增加而增加索引需要占用物理空间不光是表需要占用数据空间每个索引也需要占用物理空间当对表进行增、删、改、查的时候也要动态维护这样就降低了数据的维护速度 为数据表创建索引的原则有哪些 在最频繁使用的用以缩小查询范围的字段上建立索引在频繁使用的、需要排序的字段上建立索引 什么情况下不宜建立索引 对于查询中很少涉及的列或者重复值比较多的列不宜建立索引对于一些特殊的数据类型不宜建立索引比如文本文件text等
37. 解释 MySQL 外连接、内连接和自连接的区别
交叉连接交叉连接又叫笛卡尔积它是值不适用任何条件直接将一个表的所有记录和另一个表中的所有一一匹配
内连接则是只有条件的交叉连接根据某个条件筛选出符合条件的记录不符合条件的记录不会出现在结果集中即内连接只连接匹配的行。
外连接其结果集中不仅包含符合连接的行而且还会包含左表、右表或两个表中的所有数据行这三种情况依次称之为左外链接、右外连接和全连接
左外连接也称左连接左表为主表左表中的所有记录都会出现在结果集中对于那些在右表中并没有匹配的记录仍然要显示右边对应的哪些字段值以 NULL 来填充。
右外连接也称右连接右表为左表右表中的所有记录都会出现在结果集中。
左连接和右连接可以互换。MySQL 目前还不支持全外连接。
38. MySQL 中的事务回滚机制概述
事务是用户定义的一个数据库操作序列这些操作要么全做要么全不做是一个不可分割的工作单位事务回滚是指将该事务已经完成的对数据库的更新操作撤销
举例说明 要同时更改数据库中两个不同表时如果他们不是一个事务的话当第一个表修改完可能第二个表修改过程中出现了一场而未能修改此时就只能出现第二个表依旧是未修改之前的状态而第一个表已经被修改完毕 而当你把它们设定为一个事务的时候当第一个表改完第二个表修改出现异常而没能修改第一个表和第二个表都要回到未修改的状态
这就是所谓事务的回滚
39. SQL 语言包括哪几部分每部分都有哪些操作关键字
SQL 语言包括数据定义DDL数据操作DML、数据控制DCL和数据查询DQL
数据类型Create Table, Alter Table, Drop Table, Create / Drop Index 等
数据操作Select, Insert, Update, Delete
数据控制Grant, Revoke
数据查询Select
40. 完整性约束包括哪些
数据完整性Data Integrity是指数据的精确Accuracy和可靠性Reliability
分为以下四类
实体完整性规定表的每一行在表是唯一的实体域完整性是指表中的列必须满则某种特性的数据类型约束其中约束又包括取值范围、精度等规定参照完整性是指两个表的主关键字和外关键字的数据应一致保证了表之间的数据的一致性防止了数据丢失或无意义的数据在数据库中扩散用户定义的完整性不同的关系数据库系统根据其应用环境的不同往往还需要一些特定的约束条件。用户定义的完整性即是针对某个特定数据库的约束条件它反映某一具体必须满足的语义要求。
与表有关的约束包括列约束NOT NULL为空约束和表约束PRIMARY KEY、FOREIGN KEY、CHECK、UNIQUE
41. 什么是锁
需求
数据库是一个多用户使用的公共资源。当多个用户并发地存储数据时在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据破坏数据库的一致性。 作用加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前先向系统发出请求对其加锁。加锁后事务就对该数据对象有了一定的控制在该事物释放锁之前其它的事务不能对此数据对象进行更新操作。
42. 什么叫视图游标是什么
视图是一种虚拟的表具有和物理表相同的功能。可以对视图进行增、删、改、查的操作。视图通常是一个表或者多个表的行或列的子集。对视图的修改不影响基本表。相比多表查询它使得我们获取数据更容易。
游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不适用游标但是需要逐条处理数据的时候游标显得十分重要。
43. 什么是存储过程用什么来调用 存储过程是一个预编译的 SQL优点是允许模块化的设计就是说只需创建一次以后在该程序中就可以调用多次。 如果其它操作需要执行多次 SQL使用存储过程比单纯 SQL 语句执行要快。可以用一个命令对象来调用存储过程。
44. 如何通俗地理解三个范式
三大范式的概念
第一范式(1NF)每个列都不可以再拆分。第二范式(2NF)在第一范式的基础上非主键列完全依赖于主键而不能是依赖于主键的一部分。第三范式(3NF)在第二范式的基础上非主键列只依赖于主键不依赖于其他非主键。
对三大范式的理解
第一范式1NF 是对属性的原子性约束要求属性具有原子性不可再分解第二范式2NF 是对记录的唯一性约束要求记录有唯一标识即实体的唯一性第三范式3NF 是对字段冗余的约束即任何字段不能由其它字段派生出来。
范式化设计的优缺点
优点可以尽量的减少数据冗余使得更新快体积小缺点对于查询需要多个表进行关联减少写的效率增加读的效率更难进行索引优化
反范式化
优点可以减少表的关联可以更好的进行索引优化缺点数据冗余以及数据异常数据的修改需要更多的成本
45. 什么是基本表什么是视图
基本表是本身独立存在的表在 SQL 中一个关系就对应一个表。视图是从一个或多个基本表导出的表。视图本身不独立存储在数据库中是一个虚表。
46. 试述视图的优点
视图能够简化用户的操作视图使用户能以多种角度看待同一个数据视图为数据库提供了一定程度的逻辑独立性视图能够对机密数据提供安全保护
47. NULL 是什么意思 NULL 这个值表示 UNKNOWN未知他不表示 “”空字符串 对 NULL 这个值的任何比较都会产生一个 NULL 值你不能把任何一个值与一个 NULL 值进行比较并在逻辑上希望获得一个答案
48. 主键、外键和索引的区别
定义
主键 - 唯一表示一条记录不能有重复的不允许为空外键 - 表的外键是另一个表的主键外键可以有重复的可以是空值索引 - 该字段没有重复值但可以有一个空值
定义
主键 - 用来保证数据的完整性外键 - 用来和其它表建立联系用索引 - 是提高查询排序的速度】
个数
主键 - 主键只能有一个外键 - 一个表的外键可以有多个索引 - 一个表的索引可以有多个
49. 你可以用什么确保表格里的字段只接受特定范围里的值
使用 CHECK 和 触发器实现实例
https://blog.csdn.net/pan_junbiao/article/details/86241390 Check 限制它在数据库表个里被定义用来限制输入该列的值。 触发器也可以被用来限制数据库表里的字段能够接受的值但是这种办法要求触发器在表格里被定义这可能会在某些情况下影响到性能
50. 说说对 SQL 语句优化有哪些方法 Where 字句中where 表之间的连接必须写在其它 where 条件之前那些可以过滤掉最大数量记录的条件必须写在 where 字句的末尾 .HAVING 最后 用 EXISTS 代替 IN、用 NOT EXISTS 代替 NOT IN 避免在索引列上使用计算 避免在索引列上使用 IS NULL 和 IS NOT NULL 对查询进行优化应尽量避免全表扫描首先应该考虑在 where 以及 order by 涉及的列上建立索引 应尽量避免在 where 字句中对字段进行 null 值判断否则将导致引擎放弃使用索引而进行全表扫描 应尽量避免在 where 子句中对字段进行表达式操作这将导致引擎放弃使用索引而进行全表扫描
补充1MySQL 四种事务的区别
https://blog.csdn.net/zc520yzy/article/details/103042945
关于事务
1. 什么是事务
事务是应用程序中一些列严密的操作所有操作必须成功完成否则在每个操作所做的所有更改都会被撤销。也就是事务具有原子性一个事务中的一系列的操作要么全部成功要么一个都不做。 事务的结束有两种当事务中的所有步骤全部成功执行时事务提交 如果其中一个步骤失败讲发生回滚操作撤销之前到事务开始时的所有操作。
2. 事务的 ACID
事务具有四个特征原子性、一致性、隔离性和持久性。这个四个特征简称为 ACID 特性。
原子性事务是数据库的逻辑工作单位事务中包含的操作要么是都做要么都不做一致性事务执行的结果必须是使数据库从一个一致性的状态变到另一个一致性的状态。因此当数据库发生故障有些事务尚未完成就被迫中断这些未完成事务对数据库所做的修改有一部分写入物理数据库这时数据库就处于一种不正确的状态或者说是不一致的状态。隔离性一个事物的执行不能其它事务干扰即一个事务内部的操作及使用的数据对其它并发事务是隔离的并发执行的各个事务之间不能互相干扰。持续性也成永久性指一个事物一旦提交它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。
3 MySQL 的四种隔离级别
SQL 的标准定义了 4 类隔离级别包括了一些具体规则用来限定事务内外的哪些改变是可见的哪些是不可见的。低级别的隔离级一般支持更高的并发处理并拥有更低的系统开销。
3.1 Read Uncommitted读取未提交内容
在该隔离级别所有事物都可以看到其他未提交事物的执行结果。本隔离级别很少用于实际应用因为它的性能不比其它级别好多少。读取未提交的数据也被称为脏读Dirty Read
3.2 Read Committed读取提交内容
这是大多数数据库系统的默认隔离级别但不是 MySQL 默认的。它满足了隔离的简单定义一个事物只能看见已经提交事物所做的改变。这种隔离级别也支持所谓的不可重复读Nonrepeatable Read因为同一事物的其它实例在该实例处理期间可能会有新的 Commit所以同一 Select 可能返回不同的结果。
3.3 Repeatable Read可重读
这是 MySQL 的默认事物的隔离界别它确保同一事物的多个实例在并发读取数据时会看到同样的数据行。不过理论上这会导致另一个棘手的问题幻读Phantom Read。简单的说幻读指当用户读取某一范围的数据行时另一个事物又在该范围内插入了新行当用户再读取该范围的数据行会发现有新的“幻影”行InnoDB 和 Falcon 存储引擎通过多版本并发控制MVCCMultiversion Concurrency Control机制解决了该问题。
3.4 Serializable可串行化
这是最高的隔离级别他通过强制事物排序使之不可能互相冲突从而解决幻读问题。简言之它是在每个读的数据行上加上共享锁。在这个级别可能导致大量的超时现象和锁竞争。
补充2对 MySQL 三种锁行级锁、表级锁、页级锁与间隙锁的介绍悲观锁与乐观锁的理解与抉择方案
行级锁、表级锁、页级锁、间隙锁 行级锁 描述行级锁是 MySQL 中颗粒度最小的一种锁表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突其加锁颗粒度最小但加锁的开销也最大。行级锁分为共享锁和排他锁。 其实行级锁和页级锁之间还有其他颗粒度的锁也就是间隙锁和临键锁。 InnoDB 有三种行锁的算法 Record Lock记录锁单个行记录上的锁这个也是我们日常认为的行锁。Gap Lock间隙锁间隙锁锁定一个范围但不包括记录本身只不过它的颗粒度比记录锁的整行更大了它是锁定了某个范围内的多个行包括根本不存在的数据。GAP 锁的目的是为了防止同一事物的两次当前读出现幻读的情况。该锁只会在隔离级别是 RR 或者以上的级别内存在。间隙锁的目的是为了让其它事务无法在间隙中新增数据。Next-Key Lock临键锁它是记录锁和间隙锁的结合锁定一个范围并且锁定记录本身。对于行的查询都是采用该方法主要目的是解决幻读的问题。next-key 锁是 InnoDB 默认的锁。 上面三种锁都是排它锁X 锁 next-key lock 的效果相当于一个记录锁加一个间隙锁。当 next-key lock 加在某索引上则该记录和它前面的区间都被锁定 表级锁 描述表级锁是 MySQL 中的锁定粒度最大的一种锁表示对当前操作的整张表加锁它实现简单资源消耗较少被大部分 MySQL 引擎支持。最常使用的 MyISAM 与 InnoDB 都支持表级锁定。表级锁定分别为表共享锁共享锁与表独占写锁排他锁LOCK TABLE my_table_name READ; 用读锁锁表会阻塞其他事物修改表数据LOCK TABLE my_table_name WRITE; 用写锁锁表会阻塞其他事物读和写 MyISAM 在执行查询语句SELECT前会自动给涉及的所有表加读锁在执行更新操作UPDATE、DELETE、INSERT前会自动给涉及的表加写锁这个过程并不需要用户干预因此用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。 但是在 InnoDB 中如果需要表锁就需要显式地声明了 页级锁 描述页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快但冲突多行级锁冲突少但速度慢。因此采取了这种的页级锁一次锁定相邻的一组记录BDB 支持页级锁。
按照锁的共享策略来分共享锁、排他锁、意向共享锁、意向排他锁
共享锁和排他锁在 MySQL 中具体体现就是读锁和写锁
读锁共享锁Shared LocksS 锁针对同一份数据多个读操作可以同时进行而不会互相影响写锁排他锁Exclusive LocksX 锁当前写操作没有完成前它会阻塞其他写锁和读锁IS 锁意向共享锁、Intention Shared Lock。当食物准备在某条记录上加 S 锁时需要先在表级别加上一个 IS 锁。IX 锁意向排他锁、Intention Exclusive Lock。当事务准备在某记录上加 X 锁时需要先在表级别加上一个 IX 锁。
IS、IX 锁是表级锁它们的提出仅仅为了在之后加表级别的 S 锁和 X 锁时可以快速判断表中的记录是否被加上锁以避免用遍历的方式来查看表中有没有上锁的记录。就是说当对一个行加锁之后如果有打算给行所在的表加一个表锁必须先看看该表的行有没有被加锁否则就会出现冲突。IS 和 IX 锁就避免了判断表中行有没有加锁对每一行的遍历。直接查看表有没有意向锁就可以知道表中有没有行锁
从加锁策略上分乐观锁 / 悲观锁 乐观并发控制 / 乐观锁OCC - Optimistic Concurrency Control / Optimistic Locking 观并发控制 / 悲观锁PCC - Pessimistic Concurrency Control / Pessimistic Lock
悲观锁 悲观锁理解 在关系数据库管理系统中悲观并发控制悲观锁是一种并发控制的方法悲观锁指的是采用一种持悲观消极的态度默认数据被外界访问时必然会产生冲突所以在数据处理的整个过程中都采用加锁的状态保证在同一时间、只有一个线程可以访问到数据实现数据的排他性通常数据库的悲观锁是利用数据库本身提供的锁机制去实现的 悲观锁的实现 外界要访问某条数据那它就要首先向数据库申请改数据的锁某种锁 如果获取成功那它就可以操作改数据在它操作期间其它客户端就无法操作该数据了 如果获取失败则代表通一时间已有其它客户端获取了该锁那就必须等待其他用户释放锁‘ 优缺点 优点 适合在写多读少的并发环境中使用虽然无法维持非常多的性能但是在乐观锁无法提更好的性能前提下可以做到数据的安全性 缺点 加锁会增加系统开销虽然能保证数据的安全但是数据的吞吐量低不适合在读多写少的场景下使用
乐观锁
乐观锁的理解 乐观锁是假设认为即使在并发环境中外界对数据的操作一般是不会造成冲突所以并不会去加锁所以乐观锁并不是一把锁而是在数据进行提交更新的时候才会正式对数据的冲突与否进行检测如果发现冲突了则让返回冲突信息让用户决定如何去做下一步比如说重试知道成功为止数据库的乐观锁并不是利用数据库本身的锁去实现的可能是利用某种实现逻辑去实现做到乐观锁的思想。 乐观锁的实现 通常乐观锁的实现有两种但他们内在都是 CAS 思想的设计 方式一使用数据版本version实现 这是乐观锁最常用的一种实现方式。什么事数据版本呢就是在表中增加一个字段作为该记录的版本标识比如叫 version每次对该记录的写操作都会让 version 1。所以当我们读取了数据包括 version做出更新要提交的时候就会拿取得的 version 取跟数据库中的 version 比较是否一致如果一致则代表这个时间段并没有其它的县城也对这个数据进行修改、更新同时 version 1如果不一致则代表这个时间段该记录已经被其它线程修改过了认为是过期数据返回冲突信息比如重试重新读取最新数据再过更新update table set num num 1, version version 1 where version #{version} and id #{id} 方式二使用时间戳timestamp实现 表中增加一个字段名称无所谓比如叫 update_time字段类型使用时间戳timestamp原理和方式一致也是在更新提交时检查当前数据库中数据的时间戳和自己更新前取得的时间戳是否一致如果一致则代表此刻没有冲突可以提交更新同时时间戳更新为当前时间否则就是该时间段有其它线程也更新提交过返回冲突信息等待用户下一步动作。update table set num num 1, update_time unix_timestamp(now()) where id #{id} and update_time #{updateTime} 注意实现乐观锁的时候我们必须保证 CAS 多个操作的原子性即获取数据库数据的版本拿数据库版本与之前拿到的版本的比较以及更新数据等这几个操作的执行必须是连贯执行具有符合操作的原子性所以如果是数据库的 SQL那么我们就要保证多个 SQL 操作处于同一个事务中 优缺点 优点 在读多写少的并发场景下可以避免数据库加锁的开销提高 DAO 层的响应性能其实很多情况下我们 ORM 工具都带有乐观锁的实现所以这些方法不一定需要我们认为去实现 缺点 在写多读少的并发场景下即在写操作的竞争激烈的情况下会导致 CAS 多次重试冲突评率过高导致开销比悲观锁更高
乐观锁和悲观锁的抉择
对乐观锁和悲观锁的抉择主要体现在写-写
在乐观锁的抉择中我们可以从下面三个因素来考虑
响应速度如果 DAO 层需要非常高的响应速度尤其是读多写少的场景下那我们就可以采用乐观锁方案降低数据库锁的开销提供并发量冲突频率如果冲突频率非常高那么我们就可以采用悲观锁保证成功率毕竟如果冲突频率大乐观锁会需要多次重试才能重试成功代价可能会大大增加重试代价如果重试代价大比如说重试过程的代码执行非常耗时那么此时就不建议使用乐观锁了还不如直接上悲观锁爽快
所以我们知道
在读多写少CAS 竞争没那么激烈的时候我们可以采用乐观锁策略降低数据库加锁的开销提高数据库并发响应在读多写少的情景下因为会产生大量的 CAS 竞争且重试成本比较高的情况下我们就不建议再采用乐观锁策略了还是直接使用悲观锁的数据库加锁吧
文中参考资料链接 https://blog.csdn.net/weixin_47498250/article/details/106861000
https://mikechen.cc/3305.html
https://blog.csdn.net/pan_junbiao/article/details/86241390
https://blog.csdn.net/zc520yzy/article/details/103042945
https://blog.csdn.net/cy973071263/article/details/105188519
