• 售前

  • 售后

热门帖子
入门百科
123457536 显示全部楼层 发表于 2022-1-14 06:16:56 |阅读模式 打印 上一主题 下一主题
由于本人水平很有限,如有什么错误点,欢迎各位大佬指点。
MySQL

mysql架构

连接器:负责跟客户端进行连接,获取权限、维持和管理连接。连接分为长连接和短连接
分析器:Mysql把输入的sql字符串进行识别
优化器:优化策略RBO规则 CBO成本 在具体执行前,需要进行优化 决定使用那个索引、多表关联的顺序
执行器:执行sql语句
Mysql索引

mysql索引:为了加快查询速度 底层使用的是B+树的结构
mysql 的数据是存储在磁盘上的 要减少IO的次数 ,索引会占据磁盘的空间,索引虽然会提高查询效率,但是会降低更新表的效率。
索引的数据结构

hash索引:Hash表,在Java中的HashMap,TreeMap就是Hash表结构,以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列,Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高,时间复杂度为O(1);但是不支持范围快速查找,范围查找时还是只能通过扫描全表方式。
显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。

无论是二叉树还是红黑树,都会因为数的深度过深而造成IO的次数变多,影响数据读取的效率
B树 所有的键值分布在整棵树中,搜索有可能在非叶子节点结束,每个节点最多拥有m个子树,根节点至少两个子树,所有叶子节点都在同一层,分支节点至少拥有m/2颗子树,每个节点最大可以有m-1个K
每个节点都有K,同时也包含V值,而每个页的存储空间是有限的,如果data 比较大的话会导致每个节点存储的k数量变少
当存储的数据量很大的时候会导致树的深度较大,增大查询时磁盘的IO次数,进而影响性能
4阶B树

B+树:
4阶B+树

B+Tree 是在B树的基础上做出了一些优化:
B+Tree每个节点可以包含更多的节点,可以降低树的高度,将数据范围变为多个区间,区间越多数据检索越快
非叶子节点不保存data只存储K,叶子节点存储K和数据
叶子节点两两指针相连,顺序查询性能更高
索引类型

主键索引:自增,为了方便在B+树 直接插入到后边 免得插入在中间位置
唯一索引:索引列中的值必须是唯一的,但是允许为空值。不需要回表
普通索引:需要回表,需要遍历两边B+树
组合索引:多列组成一个 索引 例如:name age 组合,要遵循 最左匹配原则
全文索引:检索某些对应的关键字 MyISAM支持,InnoDB5.6后支持 (通常不用,使用es)
索引不是越多越好的 索引越多 磁盘空间越大 检索起来 越费劲
存储引擎区别

MyISAMInnoDB索引类型非聚簇索引聚簇索引支持事务否是支持表锁是是支持行锁否是支持外键否是支持全文索引是是(5.6后)适合操作类型大量select大量insert update delete聚簇非聚簇 :索引和数据 是否分开
回表、索引下推、索引覆盖

例子:select * from 表 where name = “ ” and age = 18
回表:就是 索引 name 找出主键 去 id 树 查找出name符合的内容 再去比较age (首先会查到普通索引的位置,取出id值,再拿id值取表中取数据
索引覆盖:name age 建立组合索引了 直接拿出来 不需要回表 (查询语句的执行只用从索引中就能够取得
索引下推:在回表的过程中 检索的时候 把 age 先进行判断 (简单说就是在索引内部进行了判断
Mysql事务

数据库中的事务是指对数据库执行一批操作,这些操作最终要么全部执行成功,要么全部失败
事务特性ACID


  • 原子性(A):事务是最小单位,不可再分
  • 一致性(C ):事务要求所有的DML语句操作的时候,必须保证同时成功或者同时失败
  • 隔离性(I):事务A和事务B之间具有隔离性
  • 持久性(D):是事务的保证,事务终结的标志(内存的数据持久到硬盘文件中)



  • 开启事务:Start Transaction
  • 事务结束:End Transaction
  • 提交事务:Commit Transaction
  • 回滚事务:Rollback Transaction

事务隔离级别
数据库的事务隔离级别有四种,分别是读未提交、读已提交、可重复读、序列化,不同的隔离级别下会产生脏读、幻读、不可重复读等相关问题,因此在选择隔离级别的时候要根据应用场景来决定,使用合适的隔离级别。
隔离级别脏读不可重复 读幻读READ- UNCOMMITTED(读未提交)√√√READ-COMMITTED(读已提交)×√√REPEATABLE- READ(可重复读)××√SERIALIZABLE(序列化)×××SQL 标准定义了四个隔离级别:


  • READ-UNCOMMITTED(读取未提交): 事务的修改,即使没有提交,对其他事务也都是可见的。事务能够读取未提交的数据,这种情况称为脏读。
  • READ-COMMITTED(读取已提交): 事务读取已提交的数据,大多数数据库的默认隔离级别。当一个事务在执行过程中,数据被另外一个事务修改,造成本次事务前后读取的信息不一样,这种情况称为不可重复读。
  • REPEATABLE-READ(可重复读): 这个级别是MySQL的默认隔离级别,它解决了脏读的问题,同时也保证了同一个事务多次读取同样的记录是一致的,但这个级别还是会出现幻读的情况。幻读是指当一个事务A读取某一个范围的数据时,另一个事务B在这个范围插入行,A事务再次读取这个范围的数据时,会产生幻读
  • SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。
事务隔离机制的实现基于锁机制和并发调度(MVCC多版本并发控制)。通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
Mysql日志

存储引擎默认是 InnoDB
日志文件 redolog undolog 在存储引擎 binlog服务端

redolog InnoDB存储引擎:当发生数据修改的时候,innodb引擎会先将记录写道redolog中,并更新内存、此时更新就算完成了,同时innodb引擎会在合适的时机将记录操作到磁盘中。
先写到缓存空间 redolog里,下一次启动的时候会根据redolog日志文件来更新数据库,保证数据持久性。redolog是完整的数据就不会丢失

MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值:0、1、2,默认为1


undolog: 回滚日志:是为了实现事务的原子性,在Mysql数据库InnoDB存储引擎中,还用undolog来实现MVCC
在操作任何数据之前,首先将数据备份一份(undolog),然后进行数据修改如果出现了错误或者用户执行了ROLLBACK,系统可以利用undolog中的备份数据恢复到事务开始之前的状态
undolog : 当delete一条记录 undolog中 对应 insert,insert对应delete,update对应update相反

binlog: 默认关闭
他是server层的日志,主要是做mysql功能层面的,binlog中会记录所有的逻辑,并且采用追加写的方式,定期执行备份
恢复数据过程:
​ 1、找到最近一次的全量备份数据
​ 2、从备份的时间点开始,将备份数据的binlog提取出来,重新恢复到那个时刻

数据更新流程

执行流程:
​ 1、执行器先从执行引擎中找到数据,如果在内存中直接返回,如果不在内存中,查询后返回
​ 2、执行器拿到数据后先对数据进行更改,然后调用引擎接口重新写入数据
​ 3、引擎将数据更新到内存,同时写数据到redolog中,此时处于prepare阶段,并通知执行器执行完成,随时可以提交事务
​ 4、执行器生成这个操作的binlog,并把binlog写入到磁盘
​ 5、执行器调用引擎的事务提交接口,引擎把刚写完的redolog改成commit状态,更新完成了
redolog 和 binlog两阶段提交
我们先假设没有两阶段提交时,可能会有以下两种情况:
redo log 提交成功了,这时候数据库挂掉导致 binlog 没有成功写入。数据库重启之后通过 redo log 把数据恢复回来,但是 binlog 没有成功写入,导致我们在做主从复制或者数据恢复的时候,数据不一致。
binlog 提交成功了,这时候数据库挂掉导致 redo log 没有成功写入。数据库重启之后,无法恢复崩溃之前提交的那个事务,这部分数据更改在主库缺失。但是 binlog 已经成功写入了,从库反而有了该事务的改动,导致数据不一致。
综上我们知道,redo log 和 binlog 必须同时成功或同时失败,才能保证数据一致性。
上图的 右侧 时刻
假设在上图的时刻1,redo log 处于 prepare 之后,写 binlog 之前,数据库挂掉了。由于此时 binlog 还没有写,redo log 也还没有提交,所以崩溃恢复后,这个事务会回滚。这时候 binlog 还没写,所以也不会传到备库。
假设在上图的时刻2,写 binlog 之后,redo log 还没有 commit 前发生 crash。那么崩溃恢复时,MySQL 会做以下判断:
如果 redo log 里面的事务是完整的,也就是已经有了 commit 标识,则直接提交;
如果 redo log 里面的事务只有完整的 prepare,则判断对应的事务 binlog 是否存在并完整:
a. 如果是,则提交事务;
b. 否则,回滚事务。

Mysql的组提交

​ 组提交(group commit)是mysql处理日志的一种优化方式,主要为了解决写日志时频繁刷磁盘的问题。组提交伴随着mysql的发展,已经支持了redo log和bin log的组提交。
redo log组提交
​ WAL(Write-Ahead-Logging)是实现事务持久性的一个常用技术,基本原理是在提交事务时,为了避免磁盘页面的随机写,只需要保证事务的redo log写入磁盘即可,这样可以通过redo log的顺序写代替页面的随机写,并且可以保证事务的持久性,提高了数据库系统的性能。虽然WAL使用顺序写替代了随机写,但是,每次事务提交,仍然需要有一次日志刷盘动作,受限于磁盘IO,这个操作仍然是事务并发的瓶颈。
​ 将多个事务redo log的刷盘动作合并,减少磁盘顺序写。Innodb的日志系统里面,每条redo log都有一个LSN(Log Sequence Number),LSN(日志的逻辑系列号)是单调递增的。每个事务执行更新操作都会包含一条或多条redo log,各个事务将日志拷贝到log_sys_buffer时(log_sys_buffer 通过log_mutex保护),都会获取当前最大的LSN,因此可以保证不同事务的LSN不会重复。

bin log组提交

Flush 阶段


  • 首先获取队列中的事务组
  • 将Redo log中prepare阶段的数据刷盘
  • 将binlog数据写入文件,当然此时只是写入文件系统的缓冲,并不能保证数据库崩溃时binlog不丢失
  • Flush阶段队列的作用是提供了Redo log的组提交
  • 如果在这一步完成后数据库崩溃,由于协调者binlog中不保证有该组事务的记录,所以MySQL可能会在重启后回滚该组事务
Sync 阶段


  • 这里为了增加一组事务中的事务数量,提高刷盘收益,MySQL使用两个参数控制获取队列事务组的时机:
​ binlog_group_commit_sync_delay=N:在等待N μs后,开始事务刷盘
​ binlog_group_commit_sync_no_delay_count=N:如果队列中的事务数达到N个,就忽视binlog_group_commit_sync_delay的设置,直接开始刷盘


  • Sync阶段队列的作用是支持binlog的组提交
  • 如果在这一步完成后数据库崩溃,由于协调者binlog中已经有了事务记录,MySQL会在重启后通过Flush 阶段中Redo log刷盘的数据继续进行事务的提交
Commit 阶段


  • 首先获取队列中的事务组
  • 依次将Redo log中已经prepare的事务在引擎层提交
  • Commit阶段不用刷盘,如上所述,Flush阶段中的Redo log刷盘已经足够保证数据库崩溃时的数据安全了
  • Commit阶段队列的作用是承接Sync阶段的事务,完成最后的引擎提交,使得Sync可以尽早的处理下一组事务,最大化组提交的效率
MVCC

MVCC:Multi-Version Concurrency Control,即多版本并发控制。MVCC是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问,在编程语言中实现事务内存。
当前读和快照读



  • 当前读 像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读,为什么叫当前读?就是它读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁。
  • 快照读 像不加锁的select操作就是快照读,即不加锁的非阻塞读;快照读的前提是隔离级别不是串行级别,串行级别下的快照读会退化成当前读;之所以出现快照读的情况,是基于提高并发性能的考虑,快照读的实现是基于多版本并发控制,即MVCC,可以认为MVCC是行锁的一个变种,但它在很多情况下,避免了加锁操作,降低了开销;既然是基于多版本,即快照读可能读到的并不一定是数据的最新版本,而有可能是之前的历史版本

当前读、快照读、MVCC关系
​ MVCC多版本并发控制指的是维持一个数据的多个版本,使得读写操作没有冲突,快照读是MySQL为实现MVCC的一个非阻塞读功能。MVCC模块在MySQL中的具体实现是由三个隐式字段,undo日志、read view三个组件来实现的。
MVCC解决的问题

数据库并发场景有三种,分别为:

  • 读读:不存在任何问题,也不需要并发控制
  • 读写:有线程安全问题,可能会造成事务隔离性问题,可能遇到脏读、幻读、不可重复读
  • 写写:有线程安全问题,可能存在更新丢失问题
多版本并发控制(MVCC)是一种用来解决读-写冲突的无锁并发控制,也就是为事务分配单向增长的时间戳,为每个修改保存一个版本,版本与事务时间戳关联,读操作只读该事务开始前的数据库的快照。 所以 MVCC 可以为数据库解决以下问题
​ 1、在并发读写数据库时,可以做到在读操作时不用阻塞写操作,写操作也不用阻塞读操作,提高了数据库并发读写的性能
​ 2、解决脏读、幻读、不可重复读等事务隔离问题,但是不能解决更新丢失问题
MVCC实现

mvcc的实现原理主要依赖于记录中的 三个隐藏字段,undolog,read view来实现的。


  • DB_TRX_ID(当前操作该记录的事务ID) 6 byte,最近修改(修改/插入)事务 ID:记录创建这条记录/最后一次修改该记录的事务 ID
  • DB_ROLL_PTR(回滚指针,配合undo日志指向上一个版本) 7 byte,回滚指针,指向这条记录的上一个版本(存储于 rollback segment 里)
  • DB_ROW_ID(数据库默认为该行记录生成的唯一隐式主键) 6 byte,隐含的自增 ID(隐藏主键),如果数据表没有主键,InnoDB 会自动以DB_ROW_ID产生一个聚簇索引
  • 实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除,而是删除 flag 变了

undolog
undolog 回滚日志,表示在进行 insert update delete的时候需要生成方便回滚的日志
insert操作时,产生的undolog只在事务回滚的时候需要,事务提交之后可以丢弃
update,delete操作时,不仅在事务回滚的时候需要,在进行快照读的时候也需要,所以不能随便删除,只有在快照都或事务回滚不涉及该日志的时候,对应的日志才会被purge线程统一清除。
undolog生成 看图
有一个事务插入第一记录,当前状态 隐式主键ID 事务ID 回滚指针

第二条 ,把name 改为 tom
在事务2进行修改行记录的时候,数据库会先对改行加排他锁
然后把改行数据拷贝到undolog中,作为旧记录
拷贝完毕后,修改该行name,并且修改事务的ID ,回滚指针指向拷贝到undolog的副本记录
事务提交后,释放锁

假设有来了第三个事务 对age 进行修改,如上图一样
事务3修改的时候,对该行加锁
然后数据拷贝到undolog…

不同事务或者相同事务的 对同一记录的修改,会导致该记录在undolog中成为一条链,链首就是最新的旧记录,链尾就是最早的旧记录

Read View: 就是事务进行了快照读操作的时候产生的读视图,在该事务执行的快照读的那一刻,会生成数据库系统当前的一个快照,记录并维护系统当前活跃事务的 ID (当每个事务开启时,都会被分配一个 ID , 这个 ID 是递增的,所以最新的事务,ID 值越大)
​ Read VIew 最大作用是用来做可见性判断的,当某个事务在指向快照读的时候,对该记录创建一个readview读视图,把它当作条件判断去判断当前事务能够看到那个版本的数据,可能是最小的数据,也有可能是改行记录的undolog里面的某个版本的数据。
​ Read View遵循的可见性算法主要是将要被修改的数据的最新记录中的DB_TRX_ID(当前事务id)取出来,与系统当前其他活跃事务的id去对比,如果DB_TRX_ID跟Read View的属性做了比较,不符合可见性,那么就通过DB_ROLL_PTR回滚指针去取出undolog中的DB_TRX_ID做比较,即遍历链表中的DB_TRX_ID,直到找到满足条件的DB_TRX_ID,这个DB_TRX_ID所在的旧记录就是当前事务能看到的最新老版本数据。
ReadView 可见性 三个全局属性
​ trx_list:一个数值列表,用来维护Read View生成时刻系统正活跃的事务ID
​ up_limit_id:记录trx_list列表中事务ID最小的ID
​ low_limit_id:Read View生成时刻系统尚未分配的下一个事务ID
具体比较规则:

  • 首先比较DB_TRX_ID < up_limit_id,如果小于,则当前事务能看到DB_TRX_ID所在的记录,如果大于等于进入下一个判断
  • 接下来判断DB_TRX_ID >= low_limit_id,如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的,那么对于当前事务肯定不可见,如果小于,则进入下一步判断
  • 判断DB_TRX_ID是否在活跃事务中,如果在,则代表在Read View生成时刻,这个事务还是活跃状态,还没有commit,修改的数据,当前事务也是看不到,如果不在,则说明这个事务在Read View生成之前就已经开始commit,那么修改的结果是能够看见的。
整体流程

假设4个事务在执行 ,模拟一下
事务1事务2事务3事务4开始开始开始开始操作…操作…操作…修改且已提交进行中…快照读进行中…………当事务2进行到快照读的时候,数据库为该行数据生成一个ReadView读视图,事务1 3 还在活跃状态,事务4在事务2快照读前已经提交更新,所以ReadView中记录了系统当前活跃的是 1 3,维护在一个列表上 trx_list
以下记录:最小id 活跃 尚未分配的

上述例子,4修改了该行记录,并在事务2进行快照读之前已经提交了事务,所以当前该行数据的undolog数据
因为事务2还没有修改字段,只是查询,所以最小数据的事务索引依然是4

undolog是事务4修改前的版本
当事务2在快照读该行记录的时候会拿着DB_TRX-ID 去跟up_limit_id,lower_limit_id和活跃事务列表进行比较,判断事务2能看到改行记录的版本是那个

​ 所以:先拿该条记录 DB_TRX_ID 字段记录的事务ID 4 去跟Read View的up limit id 比较,看4是否小于up limt id(1),所以不符合条件,继续判断4是否大于等于 low limit id(5),也不符合,最后判断4是否处于 trx list中的活跃事务,最后发现事务ID为4的不在此列表中,符合可见性,所以事务4修改后提交的最新结果的事务2快照读是可见的,因此事务2读取到最新数据记录是事务4所提交的版本,而事务4提交的版本也是全局角度上的最新版本

RR、RC级别InnoDB快照读有什么不同
因为ReadView 生成时机不同从而造成RC、RR级别下快照读的结果不同


  • 在RR(可重复度)级别下的某个事务的对某条记录的第一次快照读会创建一个快照及ReadView,将当前系统活跃的其他事务记录起来,此后在调用快照读的时候,还是使用同一个ReadView,所以只要当前事务在其他事务提交更新之前使用过快照读,那么之后的快照读使用的都是同一个ReadView,所以对之后的修改不可见。在RR级别下,快照读生成ReadView时,ReadView会记录此时所有其他活动事务的快照,这些事务的修改对于当前事务都是不可见的。而早于ReadView创建的事务所作的修改均是可见的
  • RC(读已提交)级别下,事务中 每次快照读都对新生成一个快照和ReadView,这就是在RC级别下的事务中可以看到别的事务提交的更新的原因
在RC隔离级别下,是每个快照读都会生成并获取最新的Read View,而在RR隔离级别下,则是同一个事务中的第一个快照读才会创建Read View,之后的快照读获取的都是同一个Read View。
Mysql锁机制

​ 锁是计算机协调多个进程或线程并发访问某一资源的机制。不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
表级锁: 开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁: 开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
MyISAM表锁

MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)表独占写锁(Table Write Lock)
​ 对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;对 MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作;MyISAM表的读操作与写操作之间,以及写操作之间是串行的!
MyISAM写锁阻塞读的案例:
​ 当一个线程获得对一个表的写锁之后,只有持有锁的线程可以对表进行更新操作。其他线程的读写操作都会等待,直到锁释放为止。
session1session2获取表的write锁定
lock table 表名 write;当前session对表的查询,插入,更新操作都可以执行
select * from 表名;
insert into 表名 values(data);当前session对表的查询会被阻塞
select * from 表名;释放锁:
unlock tables;当前session能够立刻执行,并返回对应结果MyISAM读阻塞写的案例:
​ 一个session使用lock table给表加读锁,这个session可以锁定表中的记录,但更新和访问其他表都会提示错误,同时,另一个session可以查询表中的记录,但更新就会出现锁等待。
MyISAM在执行查询语句之前,会自动给涉及的所有表加读锁,在执行更新操作前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此用户一般不需要使用命令来显式加锁,上例中的加锁时为了演示效果。
MyISAM的并发插入问题
MyISAM表的读和写是串行的,这是就总体而言的,在一定条件下,MyISAM也支持查询和插入操作的并发执行
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺:
  1. mysql> show status like 'table%';
  2. +-----------------------+-------+
  3. | Variable_name         | Value |
  4. +-----------------------+-------+
  5. | Table_locks_immediate | 352   |
  6. | Table_locks_waited    | 2     |
  7. +-----------------------+-------+
  8. --如果Table_locks_waited的值比较高,则说明存在着较严重的表级锁争用情况。
复制代码
InnoDB行锁

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:
  1. mysql> show status like 'innodb_row_lock%';
  2. +-------------------------------+-------+
  3. | Variable_name                 | Value |
  4. +-------------------------------+-------+
  5. | Innodb_row_lock_current_waits | 0     |
  6. | Innodb_row_lock_time          | 18702 |
  7. | Innodb_row_lock_time_avg      | 18702 |
  8. | Innodb_row_lock_time_max      | 18702 |
  9. | Innodb_row_lock_waits         | 1     |
  10. +-------------------------------+-------+
  11. --如果发现锁争用比较严重,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高
复制代码
InnoDB的行锁模式及加锁方法
共享锁(s):又称读锁。允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。
排他锁(x):又称写锁。允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。
mysql InnoDB引擎默认的修改数据语句:update,delete,insert都会自动给涉及到的数据加上排他锁,select语句默认不会加任何锁类型,如果加排他锁可以使用select …for update语句,加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的,也不能通过for update和lock in share mode锁的方式查询数据,但可以直接通过select …from…查询数据,因为普通查询没有任何锁机制。
1、在不通过索引条件查询的时候,innodb使用的是表锁而不是行锁
2、创建带索引的表进行条件查询,innodb使用的是行锁
3、由于mysql的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是依然无法访问到具体的数据

总结:
对于MyISAM的表锁:
(1)共享读锁(S)之间是兼容的,但共享读锁(S)与排他写锁(X)之间,以及排他写锁(X)之间是互斥的,也就是说读和写是串行的。
(2)在一定条件下,MyISAM允许查询和插入并发执行,我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
(3)MyISAM默认的锁调度机制是写优先,这并不一定适合所有应用,用户可以通过设置LOW_PRIORITY_UPDATES参数,或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
(4)由于表锁的锁定粒度大,读写之间又是串行的,因此,如果更新操作较多,MyISAM表可能会出现严重的锁等待,可以考虑采用InnoDB表来减少锁冲突。
对于InnoDB:
(1)InnoDB的行锁是基于索引实现的,如果不通过索引访问数据,InnoDB会使用表锁。
(2)在不同的隔离级别下,InnoDB的锁机制和一致性读策略不同。
在了解InnoDB锁特性后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁,包括:


  • 尽量使用较低的隔离级别; 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会;
  • 选择合理的事务大小,小事务发生锁冲突的几率也更小;
  • 给记录集显式加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁;
  • 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;
  • 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响; 不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁;
  • 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。
Mysql查询执行计划

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
执行计划中包含的信息

ColumnJSON NameMeaning[id]select_idThe SELECT identifier[select_type]NoneThe SELECT type[table]table_nameThe table for the output row[partitions]partitionsThe matching partitions[type]access_typeThe join type[possible_keys]possible_keysThe possible indexes to choose[key]keyThe index actually chosen[key_len]key_lengthThe length of the chosen key[ref]refThe columns compared to the index[rows]rowsEstimate of rows to be examined[filtered]filteredPercentage of rows filtered by table condition[Extra]NoneAdditional information<ul> id
[SELECT]标识符。select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
三种情况:
1、id相同:执行顺序由上至下
2、id不同:如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
3、id相同又不同(两种情况同时存在):id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行
  select_type
查询的类型,主要是用于区分普通查询、联合查询、子查询等复杂的查询
   select_type 价值JSON 名称意义SIMPLE没有任何简单[SELECT](不使用 [UNION]或子查询)PRIMARY没有任何最外层 [SELECT][UNION]没有任何中的第二个或以后的[SELECT]语句 [UNION]DEPENDENT UNIONdependent( true)a 中的第二个或后面的[SELECT]语句 [UNION],取决于外部查询UNION RESULTunion_result的结果UNION。[SUBQUERY]没有任何首先[SELECT]在子查询中DEPENDENT SUBQUERYdependent( true)首先[SELECT]在子查询中,依赖于外部查询DERIVED没有任何派生表DEPENDENT DERIVEDdependent( true)派生表依赖于另一个表UNCACHEABLE SUBQUERYcacheable( false)一个子查询,其结果无法缓存,必须为外部查询的每一行重新计算UNCACHEABLE UNIONcacheable( false)[UNION] 属于不可缓存子查询的第二个或以后的选择1、SIMPLE:简单的select查询,查询中不包含子查询或者union
2、PRIMARY:查询中包含任何复杂的子部分,最外层查询则被标记为primary
3、DERIVED:在from列表中包含的子查询被标记为derived(派生),mysql或递归执行这些子查询,把结果放在零时表里
4、UNION:若第二个select出现在union之后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为derived
5、UNION RESULT:从union表获取结果的select
6、DEPENDENT UNION:跟union类似,此处的depentent表示union或union all联合而成的结果会受外部表影响
7、SUBQUERY:在select 或 where列表中包含了子查询
8、DEPENDENT SUBQUERY:subquery的子查询要受到外部表查询的影响
9、UNCACHEABLE SUBQUERY:表示使用子查询的结果不能被缓存
10、uncacheable union:表示union的查询结果不能被缓存:sql语句未验证
  table
对应行正在访问哪一个表,表名或者别名,可能是临时表或者union合并结果集
1、如果是具体的表名,则表明从实际的物理表中获取数据,当然也可以是表的别名
2、表名是derivedN的形式,表示使用了id为N的查询产生的衍生
3、当有union result的时候,表名是union n1,n2等的形式,n1,n2表示参与union的id
  partitions
查询将匹配记录的分区。该值适用NULL于非分区表
  type
访问类型,sql查询优化中一个很重要的指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,好的sql查询至少达到range级别,最好能达到ref
[code]--all:全表扫描,一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。--index:全索引扫描这个比all的效率要好,主要有两种情况,一种是当前的查询时覆盖索引,即我们需要的数据在索引中就可以索取,或者是使用了索引进行排序,这样就避免数据的重排序--range:表示利用索引查询的时候限制了范围,在指定范围内进行查询,这样避免了index的全索引扫描,适用的操作符: =, , >, >=,

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作