张家港网站制作公司中国建筑集团2022招聘

当前位置： 首页 > news >正文

张家港网站制作公司,中国建筑集团2022招聘,网站模版源代码,wordpress文章末尾显示tag标签基础篇 基础架构 MySQL的基本架构示意图#xff1a;MySQL可以分为Server层和存储引擎层两部分。 Server层包括连接器、查询缓存、分析器、优化器、执行器等#xff0c;涵盖MySQL的大多数核心服务功能#xff0c;以及所有的内置函数#xff08;如日期、时间、数学和加密函…基础篇 基础架构 MySQL的基本架构示意图MySQL可以分为Server层和存储引擎层两部分。 Server层包括连接器、查询缓存、分析器、优化器、执行器等涵盖MySQL的大多数核心服务功能以及所有的内置函数如日期、时间、数学和加密函数等所有跨存储引擎的功能都在这一层实现比如存储过程、触发器、视图等。而存储引擎层负责数据的存储和提取。其架构模式是插件式的支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB它从MySQL 5.5.5版本开始成为了默认存储引擎。 但是大多数情况下我会建议你不要使用查询缓存为什么呢因为查询缓存往往弊大于利。 引擎扫描行数跟rows_examined并不是完全相同的 重要的日志模块redo log 不知道你还记不记得《孔乙己》这篇文章酒店掌柜有一个粉板专门用来记录客人的赊账记录。如果赊账的人不多那么他可以把顾客名和账目写在板上。但如果赊账的人多了粉板总会有记不下的时候这个时候掌柜一定还有一个专门记录赊账的账本。 如果有人要赊账或者还账的话掌柜一般有两种做法 一种做法是直接把账本翻出来把这次赊的账加上去或者扣除掉另一种做法是先在粉板上记下这次的账等打烊以后再把账本翻出来核算。 在生意红火柜台很忙时掌柜一定会选择后者因为前者操作实在是太麻烦了。首先你得找到这个人的赊账总额那条记录。你想想密密麻麻几十页掌柜要找到那个名字可能还得带上老花镜慢慢找找到之后再拿出算盘计算最后再将结果写回到账本上。 MySQL里经常说到的WAL技术WAL的全称是Write-Ahead Logging它的关键点就是先写日志再写磁盘也就是先写粉板等不忙的时候再写账本。具体来说当有一条记录需要更新的时候InnoDB引擎就会先把记录写到redo log粉板里面并更新内存这个时候更新就算完成了。同时InnoDB引擎会在适当的时候将这个操作记录更新到磁盘里面而这个更新往往是在系统比较空闲的时候做这就像打烊以后掌柜做的事。 InnoDB的redo log是固定大小的比如可以配置为一组4个文件每个文件的大小是1GB那么这块“粉板”总共就可以记录4GB的操作。从头开始写写到末尾就又回到开头循环写
write pos是当前记录的位置一边写一边后移写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置也是往后推移并且循环的擦除记录前要把记录更新到数据文件。write pos和checkpoint之间的是“粉板”上还空着的部分可以用来记录新的操作。如果write pos追上checkpoint表示“粉板”满了这时候不能再执行新的更新得停下来先擦掉一些记录把checkpoint推进一下。有了redo logInnoDB就可以保证即使数据库发生异常重启之前提交的记录都不会丢失这个能力称为crash-safe。 重要的日志模块binlog 一块是Server层它主要做的是MySQL功能层面的事情还有一块是引擎层负责存储相关的具体事宜。上面我们聊到的粉板redo log是InnoDB引擎特有的日志而Server层也有自己的日志称为binlog归档日志。 这两种日志有以下三点不同。 redo log是InnoDB引擎特有的binlog是MySQL的Server层实现的所有引擎都可以使用。redo log是物理日志记录的是“在某个数据页上做了什么修改”binlog是逻辑日志记录的是这个语句的原始逻辑比如“给ID2这一行的c字段加1 ”。redo log是循环写的空间固定会用完binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个并不会覆盖以前的日志。binlog用于数据备份还原以及同步redolog用于保证事务从故障中恢复数据
执行器和InnoDB引擎在执行这个简单的update语句时的内部流程。 执行器先找引擎取ID2这一行。ID是主键引擎直接用树搜索找到这一行。如果ID2这一行所在的数据页本来就在内存中就直接返回给执行器否则需要先从磁盘读入内存然后再返回。执行器拿到引擎给的行数据把这个值加上1比如原来是N现在就是N1得到新的一行数据再调用引擎接口写入这行新数据。引擎将这行新数据更新到内存中同时将这个更新操作记录到redo log里面此时redo log处于prepare状态。然后告知执行器执行完成了随时可以提交事务。执行器生成这个操作的binlog并把binlog写入磁盘。执行器调用引擎的提交事务接口引擎把刚刚写入的redo log改成提交commit状态更新完成。 怎样让数据库恢复到半个月内任意一秒的状态 binlog会记录所有的逻辑操作并且是采用“追加写”的形式同时系统会定期做整库备份。 假设当前ID2的行字段c的值是0再假设执行update语句过程中在写完第一个日志后第二个日志还没有写完期间发生了crash会出现什么情况呢 先写redo log后写binlog。假设在redo log写完binlog还没有写完的时候MySQL进程异常重启。由于我们前面说过的redo log写完之后系统即使崩溃仍然能够把数据恢复回来所以恢复后这一行c的值是1。 但是由于binlog没写完就crash了这时候binlog里面就没有记录这个语句。因此之后备份日志的时候存起来的binlog里面就没有这条语句。 先写binlog后写redo log。如果在binlog写完之后crash由于redo log还没写崩溃恢复以后这个事务无效所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以在之后用binlog来恢复的时候就多了一个事务出来恢复出来的这一行c的值就是1与原库的值不同。 redo log和binlog都可以用于表示事务的提交状态而两阶段提交就是让这两个状态保持逻辑上的一致。 binlog用于数据备份还原以及同步redolog用于保证事务从故障中恢复数据。RTO恢复目标时间 事务 提到事务你肯定会想到ACIDAtomicity、Consistency、Isolation、Durability即原子性、一致性、隔离性、持久性今天我们就来说说其中I也就是“隔离性”。可能出现脏读dirty read、不可重复读non-repeatable read、幻读phantom read的问题为了解决这些问题就有了“隔离级别”的概念。 你隔离得越严实效率就会越低SQL标准的事务隔离级别包括读未提交read uncommitted、读提交read committed、可重复读repeatable read和串行化serializable 。 下面我逐一为你解释 读未提交是指一个事务还没提交时它做的变更就能被别的事务看到。 读提交是指一个事务提交之后它做的变更才会被其他事务看到。 可重复读是指一个事务执行过程中看到的数据总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下未提交变更对其他事务也是不可见的。 串行化顾名思义是对于同一行记录“写”会加“写锁”“读”会加“读锁”。当出现读写锁冲突的时候后访问的事务必须等前一个事务执行完成才能继续执行。 - 若隔离级别是“读未提交” 则V1的值就是2。这时候事务B虽然还没有提交但是结果已经被A看到了。因此V2、V3也都是2。 若隔离级别是“读提交”则V1是1V2的值是2。事务B的更新在提交后才能被A看到。所以 V3的值也是2。 若隔离级别是“可重复读”则V1、V2是1V3是2。之所以V2还是1遵循的就是这个要求事务在执行期间看到的数据前后必须是一致的。 若隔离级别是“串行化”则在事务B执行“将1改成2”的时候会被锁住。直到事务A提交后事务B才可以继续执行。所以从A的角度看 V1、V2值是1V3的值是2。
你可能会问那什么时候需要“可重复读”的场景呢我们来看一个数据校对逻辑的案例。 假设你在管理一个个人银行账户表。一个表存了账户余额一个表存了账单明细。到了月底你要做数据校对也就是判断上个月的余额和当前余额的差额是否与本月的账单明细一致。你一定希望在校对过程中即使有用户发生了一笔新的交易也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的不受其他事务更新的影响。 事务的实现 理解了事务的隔离级别我们再来看看事务隔离具体是怎么实现的。这里我们展开说明“可重复读”。 在MySQL中实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值通过回滚操作都可以得到前一个状态的值。 假设一个值从1被按顺序改成了2、3、4在回滚日志里面就会有类似下面的记录。
当前值是4但是在查询这条记录的时候不同时刻启动的事务会有不同的read-view。如图中看到的在视图A、B、C里面这一个记录的值分别是1、2、4同一条记录在系统中可以存在多个版本就是数据库的多版本并发控制MVCC。对于read-view A要得到1就必须将当前值依次执行图中所有的回滚操作得到。 事务的启动 显式启动事务语句 begin 或 start transaction。配套的提交语句是commit回滚语句是rollback。set autocommit0这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句这个事务就启动了而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句或者断开连接。 有些客户端连接框架会默认连接成功后先执行一个set autocommit0的命令。这就导致接下来的查询都在事务中如果是长连接就导致了意外的长事务。 但是有的开发同学会纠结“多一次交互”的问题。对于一个需要频繁使用事务的业务第二种方式每个事务在开始时都不需要主动执行一次 “begin”减少了语句的交互次数。如果你也有这个顾虑我建议你使用commit work and chain语法。 在autocommit为1的情况下用begin显式启动的事务如果执行commit则提交事务。如果执行 commit work and chain则是提交事务并自动启动下一个事务这样也省去了再次执行begin语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。 你可以在information_schema库的innodb_trx这个表中查询长事务比如下面这个语句用于查找持续时间超过60s的事务。 select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))60 索引 三种常见、也比较简单的数据结构它们分别是哈希表、有序数组和搜索树。 哈希索引做区间查询的速度是很慢的哈希表这种结构适用于只有等值查询的场景比如Memcached及其他一些NoSQL引擎。而有序数组在等值查询和范围查询场景中的性能就都非常优秀有序数组索引只适用于静态存储引擎插入删除修改等操作很费时间二叉搜索树的特点是父节点左子树所有结点的值小于父节点的值右子树所有结点的值大于父节点的值。这样如果你要查ID_card_n2的话按照图中的搜索顺序就是按照UserA - UserC - UserF - User2这个路径得到。这个时间复杂度是O(log(N))。 但是实际上大多数的数据库存储却并不使用二叉树。其原因是索引不止存在内存中还要写到磁盘上。这里“N叉”树中的“N”取决于数据块的大小。你可以想象一下一棵100万节点的平衡二叉树树高20。一次查询可能需要访问20个数据块。在机械硬盘时代从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说对于一个100万行的表如果使用二叉树来存储单独访问一个行可能需要20个10 ms的时间这个查询可真够慢的。 以InnoDB的一个整数字段索引为例这个N差不多是1200。这棵树高是4的时候就可以存1200的3次方个值这已经17亿了。考虑到树根的数据块总是在内存中的一个10亿行的表上一个整数字段的索引查找一个值最多只需要访问3次磁盘。其实树的第二层也有很大概率在内存中那么访问磁盘的平均次数就更少了。 根据叶子节点的内容索引类型分为主键索引和非主键索引。 主键索引的叶子节点存的是整行数据。在InnoDB里主键索引也被称为聚簇索引clustered index。非主键索引的叶子节点内容是主键的值。在InnoDB里非主键索引也被称为二级索引secondary index。显然主键长度越小普通索引的叶子节点就越小普通索引占用的空间也就越小。 根据上面的索引结构说明我们来讨论一个问题基于主键索引和普通索引的查询有什么区别如果语句是select * from T where ID500即主键查询方式则只需要搜索ID这棵B树如果语句是select * from T where k5即普通索引查询方式则需要先搜索k索引树得到ID的值为500再到ID索引树搜索一次。这个过程称为回表。 也就是说基于非主键索引的查询需要多扫描一棵索引树。因此我们在应用中应该尽量使用主键查询。 自增主键的插入数据模式正符合了我们前面提到的递增插入的场景。每次插入一条新记录都是追加操作都不涉及到挪动其他记录也不会触发叶子节点的分裂。而有业务逻辑的字段做主键则往往不容易保证有序插入这样写数据成本相对较高。 有没有什么场景适合用业务字段直接做主键的呢还是有的。比如有些业务的场景需求是这样的 只有一个索引该索引必须是唯一索引。 B树能够很好地配合磁盘的读写特性减少单次查询的磁盘访问次数。 对于上面例子中的InnoDB表T如果你要重建索引 k你的两个SQL语句可以这么写 altertable T drop index k; altertable T add index(k); 如果你要重建主键索引也可以这么写 altertable T dropprimarykey; altertable T addprimarykey(id); 我的问题是对于上面这两个重建索引的作法说出你的理解。如果有不合适的为什么更好的方法是什么 重建索引k的做法是合理的可以达到省空间的目的。但是重建主键的过程不合理。不论是删除主键还是创建主键都会将整个表重建。所以连着执行这两个语句的话第一个语句就白做了。这两个语句你可以用这个语句代替 alter table T engineInnoDB 由于覆盖索引可以减少树的搜索次数显著提升查询性能所以使用覆盖索引是一个常用的性能优化手段。在这个查询里面索引k已经“覆盖了”我们的查询需求我们称为覆盖索引。 B树这种索引结构可以利用索引的“最左前缀”来定位记录。不只是索引的全部定义只要满足最左前缀就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段也可以是字符串索引的最左M个字符。 在建立联合索引的时候如何安排索引内的字段顺序。这里我们的评估标准是索引的复用能力。因为可以支持最左前缀所以当已经有了(a,b)这个联合索引后一般就不需要单独在a上建立索引了。因此第一原则是如果通过调整顺序可以少维护一个索引那么这个顺序往往就是需要优先考虑采用的。 MySQL 5.6 引入的索引下推优化index condition pushdown) 可以在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。 全局锁和表锁 根据加锁的范围MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类 1.全局锁 MySQL提供了一个加全局读锁的方法命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候可以使用这个命令之后其他线程的以下语句会被阻塞数据更新语句数据的增删改、数据定义语句包括建表、修改表结构等和更新类事务的提交语句。 让整库都只读听上去就很危险 ● 如果你在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆 ● 如果你在从库上备份那么备份期间从库不能执行主库同步过来的binlog会导致主从延迟。 全局锁的典型使用场景是做全库逻辑备份。也就是把整库每个表都select出来存成文本。 官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候导数据之前就会启动一个事务来确保拿到一致性视图。而由于MVCC的支持这个过程中数据是可以正常更新的。 你一定在疑惑有了这个功能为什么还需要FTWRL呢一致性读是好但前提是引擎要支持这个隔离级别。比如对于MyISAM这种不支持事务的引擎如果备份过程中有更新总是只能取到最新的数据那么就破坏了备份的一致性。这时我们就需要使用FTWRL命令了。 所以single-transaction方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎那么备份就只能通过FTWRL方法。这往往是DBA要求业务开发人员使用InnoDB替代MyISAM的原因之一。 既然要全库只读为什么不使用set global readonlytrue的方式呢确实readonly方式也可以让全库进入只读状态但我还是会建议你用FTWRL方式主要有两个原因 在有些系统中readonly的值会被用来做其他逻辑比如用来判断一个库是主库还是备库。因此修改global变量的方式影响面更大我不建议你使用。在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开那么MySQL会自动释放这个全局锁整个库回到可以正常更新的状态。而将整个库设置为readonly之后如果客户端发生异常则数据库就会一直保持readonly状态这样会导致整个库长时间处于不可写状态风险较高。 业务的更新不只是增删改数据DML)还有可能是加字段等修改表结构的操作DDL。不论是哪种方法一个库被全局锁上以后你要对里面任何一个表做加字段操作都是会被锁住的。

1. 表锁 MySQL里面表级别的锁有两种一种是表锁一种是元数据锁meta data lockMDL)。 表锁的语法是 lock tables … read/write。lock tables语法除了会限制别的线程的读写外也限定了本线程接下来的操作对象。 举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句则其他线程写t1、读写t2的语句都会被阻塞。同时线程A在执行unlock tables之前也只能执行读t1、读写t2的操作。连写t1都不允许自然也不能访问其他表。 另一类表级的锁是MDLmetadata lock)。MDL不需要显式使用在访问一个表的时候会被自动加上。MDL的作用是保证读写的正确性当对一个表做增删改查操作的时候加MDL读锁当要对表做结构变更操作的时候加MDL写锁。 ● 读锁之间不互斥因此你可以有多个线程同时对一张表增删改查。 ● 读写锁之间、写锁之间是互斥的用来保证变更表结构操作的安全性。因此如果有两个线程要同时给一个表加字段其中一个要等另一个执行完才能开始执行。 事务中的MDL锁在语句执行开始时申请但是语句结束后并不会马上释放而会等到整个事务提交后再释放。 基于上面的分析我们来讨论一个问题如何安全地给小表加字段 首先我们要解决长事务事务不提交就会一直占着MDL锁。在MySQL的information_schema 库的 innodb_trx 表中你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行要考虑先暂停DDL或者kill掉这个长事务。 但考虑一下这个场景。如果你要变更的表是一个热点表虽然数据量不大但是上面的请求很频繁而你不得不加个字段你该怎么做呢 这时候kill可能未必管用因为新的请求马上就来了。比较理想的机制是在alter table语句里面设定等待时间如果在这个指定的等待时间里面能够拿到MDL写锁最好拿不到也不要阻塞后面的业务语句先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。 ALTER TABLE tbl_name WAIT N add column … MDL会直到事务提交才释放在做表结构变更的时候你一定要小心不要导致锁住线上查询和更新。
2. 行锁 并不是所有的引擎都支持行锁比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁对于这种引擎的表同一张表上任何时刻只能有一个更新在执行这就会影响到业务并发度 行锁就是针对数据表中行记录的锁。这很好理解比如事务A更新了一行而这时候事务B也要更新同一行则必须等事务A的操作完成后才能进行更新。 在InnoDB事务中行锁是在需要的时候才加上的但并不是不需要了就立刻释放而是要等到事务结束时才释放。这个就是两阶段锁协议。 如果你的事务中需要锁多个行要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。 死锁和死锁检测 当并发系统中不同线程出现循环资源依赖涉及的线程都在等待别的线程释放资源时就会导致这几个线程都进入无限等待的状态称为死锁。这里我用数据库中的行锁举个例子。 当出现死锁以后有两种策略 一种策略是直接进入等待直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。另一种策略是发起死锁检测发现死锁后主动回滚死锁链条中的某一个事务让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on表示开启这个逻辑。 在InnoDB中innodb_lock_wait_timeout的默认值是50s意味着如果采用第一个策略当出现死锁以后第一个被锁住的线程要过50s才会超时退出然后其他线程才有可能继续执行。对于在线服务来说这个等待时间往往是无法接受的。 每个新来的被堵住的线程都要判断会不会由于自己的加入导致了死锁这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁但是这期间要消耗大量的CPU资源。因此你就会看到CPU利用率很高但是每秒却执行不了几个事务。 怎么解决由这种热点行更新导致的性能问题呢问题的症结在于死锁检测要耗费大量的CPU资源。 一种头痛医头的方法就是如果你能确保这个业务一定不会出现死锁可以临时把死锁检测关掉。但是这种操作本身带有一定的风险因为业务设计的时候一般不会把死锁当做一个严重错误毕竟出现死锁了就回滚然后通过业务重试一般就没问题了这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时这是业务有损的。另一个思路是控制并发度。根据上面的分析你会发现如果并发能够控制住比如同一行同时最多只有10个线程在更新那么死锁检测的成本很低就不会出现这个问题。一个直接的想法就是在客户端做并发控制。但是你会很快发现这个方法不太可行因为客户端很多。我见过一个应用有600个客户端这样即使每个客户端控制到只有5个并发线程汇总到数据库服务端以后峰值并发数也可能要达到3000。考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例可以考虑放在多条记录上比如10个记录影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候随机选其中一条记录来加。这样每次冲突概率变成原来的1/10可以减少锁等待个数也就减少了死锁检测的CPU消耗。这个方案看上去是无损的但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少比如退票逻辑那么这时候就需要考虑当一部分行记录变成0的时候代码要有特殊处理。