首页
影视
壁纸
留言
关于
今日热榜
推荐
音乐解锁
听歌
阅读器
听歌2
小乞丐
摸鱼
Search
1
Navicat Premium for Mac 中文破解版 (强大的数据库管理工具)
9,629 阅读
2
植物大战僵尸中文版 for Mac (兼容 M1系统) 中文版
5,767 阅读
3
CleanMyMac X 4.15.2 中文破解版 (Mac优化清理工具)
3,296 阅读
4
PDF Expert 中文破解版 (好用的PDF编辑器)
3,044 阅读
5
Parallels Desktop 17 中文版 (PD虚拟机无限试用版本)
2,818 阅读
生活杂记
macOS
编程技术
奇技淫巧
音乐
视频
δ
Search
标签搜索
python
漫画
php
mac
redis
mysql
mac 软件
macOS
音乐
极客爱情
数据库
游戏
吊打面试官
2.0
面试
罗小黑
linux
纯音乐
使用教程
B站
Kain
累计撰写
210
篇文章
累计收到
26
条评论
首页
栏目
生活杂记
macOS
编程技术
奇技淫巧
音乐
视频
δ
页面
影视
壁纸
留言
关于
今日热榜
推荐
音乐解锁
听歌
阅读器
听歌2
小乞丐
摸鱼
搜索到
6
篇与
的结果
MySQL 中删除的数据都去哪儿了?
2021年08月12日
122 阅读
1 评论
1 点赞
2021-08-12
不知道大家有没有想过下面这件事?我们平时调用 `DELETE` 在 MySQL 中删除的数据都去哪儿了?
2021-01-28
教你两种数据库覆盖式数据导入方法
摘要:本文主要介绍如何在数据库中完成覆盖式数据导入的方法。前言众所周知,数据库中INSERT INTO语法是append方式的插入,而最近在处理一些客户数据导入场景时,经常遇到需要覆盖式导入的情况,常见的覆盖式导入主要有下面两种:1、部分覆盖:新老数据根据关键列值匹配,能匹配上则使用新数据覆盖,匹配不上则直接插入。2、完全覆盖:直接删除所有老数据,插入新数据。本文主要介绍如何在数据库中完成覆盖式数据导入的方法。部分覆盖业务场景某业务每天给业务表中导入大数据进行分析,业务表中某列存在主键,当插入数据和已有数据存在主键冲突时,希望能够对该行数据使用新数据覆盖或者说更新,而当新老数据userid不冲突的情况下,直接将新数据插入到数据库中。以将表src中的数据覆盖式导入业务表des中为例:应用方案方案一:使用DELETE+INSERT组合实现(UPDATE也可以,请读者思考)--开启事务 START TRANSACTION; --去除主键冲突数据 DELETE FROM des USING src WHERE EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid); --导入新数据 INSERT INTO des SELECT * FROM src WHERE NOT EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid); --事务提交 COMMIT;方案优点:使用最常见的使用DELETE和INSERT即可实现。方案缺点:1、分了DELETE和INSERT两个步骤,易用性欠缺;2、借助子查询识重,DELETE/INSERT性能受查询性能制约。方案二:使用MERGE INTO功能实现MERGE INTO des USING src ON (des.userid = src.userid) WHEN MATCHED THEN UPDATE SET des.b = src.b WHEN NOT MATCHED THEN INSERT VALUES (src.userid,src.b);方案优点:MERGE INTO单SQL搞定,使用便捷,内部去重效率高。方案缺点:需要数据库产品支持MERGE INTO功能,当前Oracle、GaussDB(DWS)等数据库已支持此功能,mysql的insert into on duplicate key也类似此功能。完全覆盖业务场景某业务每天给业务表中导入一定时间区间的数据进行分析,分析只需要导入时间区间的去除,不需要以往历史数据,这种情况就需要使用到覆盖式导入。应用方案方案一:使用TRUNCATE+INSERT组合实现--开启事务 START TRANSACTION; --清除业务表数据 TRUNCATE des; --插入1月份数据 INSERT INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00'; --提交事务 COMMIT;方案优点:简单暴力,先清理在插入直接实现类似覆盖写功能。方案缺点:TRUNCATE清理业务表des数据时对表加8级锁直到事务结束,在因数据量巨大而INSERT时间很长的情况下,des表在很长时间内是不可访问的状态,业务表des相关的业务处于中断状态。方案二:使用创建临时表过渡的方式实现--开启事务 START TRANSACTION; --创建临时表 CREATE TABLE temp(LIKE desc INCLUDING ALL); --数据先导入到临时表中 INSERT INTO temp SELECT * FROM src WHERE TIME > '2020-01-01 00:00:00' AND TIME < '2020-02-01 00:00:00'; --导入完成后删除业务表des DROP TABLE des; --修改临时表名temp->des ALTER TABLE temp RENAME TO des; --提交事务 COMMIT;方案优点:相比方案一,在INSERT期间,业务表des可以继续被访问(老数据),即事务提交前分析业务可继续访问老数据,事务提交后分析业务可以访问新导入的数据。方案缺点:1、组合步骤较多,不易用;2、DROP TABLE操作会删除表的依赖对象,例如视图等,后面依赖对象的还原可能会比较复杂。方案三:使用INSERT OVERWRITE功能INSERT OVERWRITE INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00';方案优点:单条SQL搞定,执行便捷,能够支持一键式切换业务查询的新老数据,业务不中断。方案缺点:需要产品支持INSERT OVERWRITE功能,当前impala、GaussDB(DWS)等数据库均已支持此功能。总结随着大数据的场景越来越多,数据导入的场景也越来越丰富,除了本文介绍的覆盖式数据导入,还有其他诸如忽略冲突的INSERT IGNORE导入等等其他的导入方式,这些导入场景可以以使用基础的INSERT、UPDATE、DELETE、TRUNCATE来组合实现,但是也同样会对高级的一键SQL功能有直接诉求,后面有机会再继续介绍。
2021年01月28日
125 阅读
0 评论
0 点赞
2021-01-23
理解完这些基本上能解决面试中MySql的事务问题
越努力,越幸运前言在面试中,基本上都会问到关于数据库的事务问题,如果啥都不会或者只回答到表面的上知识点的话,那面试基本上是没戏了,为了能顺利通过面试,那MySql的事务问题就需要了解,所以就根据网上的资料总结一版Mysql事务的知识点,巩固一下事务的知识。事务事务是指逻辑上的一组操作,要么都执行,要么都不执行,事务的特性(ACID)原子性(Atomicity):事务是不可分割的工作单元,要么都成功,要么都失败, 如果事务中一个sql语句执行失败,则已执行的语句也必须回滚,数据库退回到事务前的状态。一致性(Consistency):事务不能破坏数据的完整性和业务的一致性 。例如在银行转账时,不管事务成功还是失败,双方钱的总额不变隔离性(Isolation):一个事务所操作的数据在提交之前,对其他事务的可见性设定(一般是不可见)持久性(Durability):事务提交之后,所做的修改就会永久保存,不会因为系统故障导致数据丢失严格来说,只有同时满足数据库的事务ACID特性才能算一个完整的事务,但现实中实现能够真正满足的完整的事务特性少之又少,但是在实现中也必须尽量达到事务要求的特性。那么事务ACID特性具体怎么实现的呢?我们来分析看看,首先先看看事务的特性。原子性(Atomicity)首先我们来看看事务的原子性特性,看看其如何实现的?原子性(Atomicity):事务是不可分割的工作单元,要么都成功,要么都失败, 如果事务中一个sql语句执行失败,则已执行的语句也必须回滚,数据库退回到事务前的状态原子性(Atomicity)的实现离不开 MySQL的事务日志 undo log日志类型,当事务需要回滚的时候需要将数据库状态回滚到事务开始前,即需要撤销所有已经成功执行的sql语句。那么undo log起了关键性作用:当事务对数据库进行修改时,InnoDB会生成对应的undo log;如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。那么undo log是什么呢?每个数据变更操作是怎么被记录下来的呢?undo log( 回滚日志 )undo log (回滚日志):是采用段(segment)*的方式来记录的,每个undo操作在记录的时候占用一个*undo log segment。为什么会在数据更改操作的时候,记录了相对应的undo log呢?其目的在于:为了保证数据的原子性,记录事务发生之前的一个版本,用于回滚,通过mvcc+undo log实现innodb事务可重复读和读取已提交隔离级别。其中,undo log分为:insert undo log : insert操作中产生的undo log,update undo log: 对delete 和update操作产生的undo log数据更改的undo log怎么记录的呢?因为insert操作的记录,只对事务本身可见,对其他事务不可见。故该undo log可以在事务提交后直接删除,不需要进行purge操作,而Delete操作在事务中实际上并不是真正的删除掉数据行,而是一种Delete Mark操作,在记录上标识Delete_Bit,而不删除记录。是一种"假删除",只是做了个标记,真正的删除工作需要后台purge线程去完成。update分为两种情况:update的列是否是主键列。如果不是主键列,在undo log中直接反向记录是如何update的。即update是直接进行的。如果是主键列,update分两部执行:先删除该行,再插入一行目标行。与insert undo log 不同的,update undo log日志,当事务提交的时候,innodb不会立即删除undo log, 会将该事务对应的undo log放入到删除列表中,未来通过purge线程来删除。因为后续还可能会用到undo log,如隔离级别为repeatable read时,事务读取的都是开启事务时的最新提交行版本,只要该事务不结束,该行版本就不能删除(即undo log不能删除),且undo log分配的页可重用减少存储空间和提升性能。Note: purge线程两个主要作用是:清理undo页和清除page里面带有Delete_Bit标识的数据行。接着我们来看看事务的隔离性,看看事务有哪些隔离级别,而且事务并发中会产生什么问题。隔离性(Isolation)隔离性(Isolation),是指事务内部的操作与其他事务是隔离的,并发执行的各个事务之间不能互相干扰 ,一个事务所操作的数据在提交之前,对其他事务的可见性设定(一般是不可见)。事务隔离级别而且数据库为了在并发下有效保证读取数据正确性,数据库提供了四种事务隔离级别,分别为:读未提交(脏读):允许读取尚未提交的数据,允许脏读读已提交( 不可重复读 ):允许读取事务已经提交的数据可重复读( 幻读 ):在同一个事务内的查询结果都是和事务开始时刻查询一致的( InnoDB默认级别 )串行化:所有事务逐个依次执行, 每次读都需要获得表级共享锁,读写相互都会阻塞其中,不同的隔离级别可能会存在在不同并发问题,主要并发问题包括:数据丢失: 两个或多个事务操作相同数据,基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新脏读:读到了其他事务还未提交的数据,事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据不可重复读(重点是修改):在一个事务中,先后进行两次相同的读取,由于另一个事务修改了数据,导致前后两次结果的不一致,事务A多次读取同一数据,事务B在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。幻读(重点是新增、删除): 在一个事务中,先后进行两次相同的读取(一般是范围查询),由于另一个事务新增或删除了数据,导致前后两次结果不一致不可重复读和幻读的区别?不可重复读和幻读最大的区别,就在于如何通过锁机制来解决他们产生的问题,使用锁机制来实现这两种隔离级别,在可重复读中,相同sql第一次读取到数据后就将这些数据加锁,其它事务无法更新操作这些数据来实现可重复读了隔离。但这种处理方式却无法锁住insert的数据,因此会出现当事务A先前读取了数据,事务B再insert数据提交,结果发现事务A就会发现莫名其妙多了些数据,这就是幻读,不能通过行锁来避免 。了解了并发问题后,来看看不同的隔离级别可能会存在在不同并发问题:事务隔离级别脏读不可重复读幻读读未提交是是是不可重复读否是是可重复读否否是串行化否否否为了实现事务隔离,延伸出了数据库锁。其中,innodb事务的隔离级别是由锁机制和MVCC(多版本并发控制)来实现的那我们来先看看锁的原理,怎么使用锁来实现事务隔离的呢?锁机制锁机制的基本工作原理,事务在修改数据之前,需要先获得相应的锁;获得锁之后,事务便可以修改数据;该事务操作期间,这部分数据是锁定的,其他事务如果需要修改数据,需要等待当前事务提交或回滚后释放锁,MySQL主要分成三种类型(级别)的锁机制:表级锁:最大颗粒度的锁机制,锁定资源争用的概率也会最高 ,并发度最低 ,但开销小,加锁快,不会出现死锁,行级锁:最大颗粒度的锁机制很小, 发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能 ,但 开销大,加锁慢;会出现死锁 ,页级锁: 开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般而且不同的存储引擎支持不同的的锁机制,主要分析一下InnoDB锁。InnoDB锁InnoDB实现了以下两种类型的行锁共享锁(S锁、行锁):多个事务对同一数据行可以共享一把锁,只能读不能修改排它锁(X锁、行锁):一个事务获取一个数据行的排它锁,那么其他事务将不能再获取该行的锁(共享锁、排它锁), 允许获取排他锁的事务更新数据对于UPDATE,DELETE,INSERT操作, InnoDB会自动给涉及及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁,而且因为InnoDB引擎允许行锁和表锁共存,实现多粒度锁机制,使用意向锁实现表锁机制,意向共享锁(IS锁、表锁):当事务准备给数据行加共享锁时,会先给表加上一个意向共享锁。意向共享锁之间是兼容的意向排它锁(IX锁、表锁):当事务准备给数据行加排它锁时,会先给表加上一个意向排它锁。意向排它锁之间是兼容的意向锁(IS、IX)是InnoDB数据操作之前自动加的,不需要用户干预。它的意义在于:当事务想去进行锁表时,可以先判断意向锁是否存在,存在时则可快速返回该表不能启用表锁,否则就需要等待,其中,四种锁的兼容性如下当前锁模式/是否兼容/请求锁模式XIXSISX冲突冲突冲突冲突IX冲突兼容冲突兼容S冲突冲突兼容兼容IS冲突兼容兼容兼容如果一个事务请求的锁模式与当前的锁兼容,InnoDB就请求的锁授予该事务;反之,如果两者两者不兼容,该事务就要等待锁释放。InnoDB行锁InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引检索数据,才能使用行锁,否则将使用表锁(锁住索引的所有记录)临键锁(next-key),可以防止幻读。根据索引,划分为一个个左开右闭的区间。当进行范围查询的时候,若命中索引且能够检索到数据,则锁住记录所在的区间和它的下一个区间,其实,临键锁(Next-Key)=记录锁(Record Locks)+间隙锁(Gap Locks),当我们用范围条件检索数据而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合范围条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做间隙(GAP)。当使用唯一索引,且记录存在的精准查询时,使用Record Locks记录锁具体的使用体现在哪里呢?如下图所示:范围查询,记录存在当记录不存在(不论是等值查询,还是范围查询)时,next-key将退化成Gap Lock(间隙锁)当条件是精准匹配(即为等值查询时)且记录存在时,并且是唯一索引,临键锁(Next-Key)*退化成*Record Lock(记录锁)当条件是精准匹配(即为等值查询时)且记录存在,但不是唯一索引时,临键锁(Next-Key)*会有精准值的数据会增加*Record Lock(记录锁)*和精准值前后的区间的数据会增加*Gap Lock(间隙锁)。如何使用锁解决并发问题利用锁解决脏读、不可重复读、幻读X锁解决脏读S锁解决不可重复读临键锁解决幻读Multiversion concurrency control (MVCC 多版本并发控制)InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的,一个保存了行的事务ID(事务ID就会递增 ),一个保存了行的回滚段的指针 。每开始一个新的事务,都会自动递增产 生一个新的事务id。事务开始时刻的会把事务id放到当前事务影响的行事务id中,而DB_ROLL_PTR表示指向该行回滚段的指针,该行记录上所有版本数据,在undo中都通过链表形式组织,该值实际指向undo中该行的历史记录链表,在并发访问数据库时,对正在事务中的数据做MVCC多版本的管理,以避免写操作阻塞读操作,并且会通过比较版本解决幻读。而且MVCC只在REPEATABLE READ和READ COMMITIED两个隔离级别下才会工作,其中,MVCC实现实质就是保存数据在某个时间点的快照来实现的。 那哪些操作是快照读?快照读和当前读快照读,innodb快照读,数据的读取将由 cache(原本数据) + undo(事务修改前的数据) 两部分组成普通的select,比如 select * from table where ?;当前读,SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改UPDATEDELETEINSERTSELECT … LOCK IN SHARE MODESELECT … FOR UPDATE其中当前读中,只有SELECT … LOCK IN SHARE MODE对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。那么在RR隔离级别下,MVCC具体是如何操作的。RR隔离级别下,MVCC具体操作SELECT操作,InnoDB遵循以后两个规则执行:InnoDB只查找版本早于当前事务版本的数据行(即行的事务编号小于或等于当前事务的事务编号,这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的记录。行的删除版本要么未定义,读取到事务开始之前状态的版本,这可以确保事务读取到的行,在事务开始之前未被删除.只有同时满足的两者的记录,才能返回作为查询结果.INSERT:InnoDB为新插入的每一行保存当前事务编号作为行版本号。DELETE:InnoDB为删除的每一行保存当前事务编号作为行删除标识。UPDATE:InnoDB为插入一行新记录,保存当前事务编号作为行版本号,同时保存当前事务编号到原来的行作为行删除标识。保存这两个额外系统版本号,使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行,不足之处是每行记录都需要额外的存储空间,需要做更多的行检查工作,以及一些额外的维护工作。分析完了原子性和隔离性,我们继续看看事务的持久性。持久性(Durability)持久性(Durability):事务提交之后,所做的修改就会永久保存,不会因为系统故障导致数据丢失,而且其实现的关键在于redo log, 在执行SQL时会保存已执行的SQL语句到一个指定的Log文件,当执行recovery时重新执行redo log记录的SQL操作。那么redo log如何实现的呢?redo log当向数据库写入数据时,执行过程会首先写入Buffer Pool,Buffer Pool中修改的数据会定期刷新到磁盘中(这一过程称为刷脏),这整一过程称为redo log。redo log 分为:Buffer Pool内存中的日志缓冲(redo log buffer),该部分日志是易失性的;磁盘上的重做日志文件(redo log file),该部分日志是持久的。Buffer Pool的使用可以大大提高了读写数据的效率,但是也带了新的问题:如果MySQL宕机,而此时Buffer Pool中修改的数据在内存还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。为了确保事务的持久性,在当事务提交时,会调用fsync接口对redo log进行刷盘, (即redo log buffer写日志到磁盘的redo log file中 ),刷新频率由 innodb_flush_log_at_trx_commit变量来控制的:0 : 每秒刷新写入到磁盘中的,当系统崩溃,会丢失1秒钟的数据 ;1: 事务每次提交都写入磁盘;2:每秒刷新写入到磁盘中的,但跟0是有区别的。redo log有更加详细的解读,后续有时间再补上,到现在为止,已经将事务三个特性都理解了,那事务一致性呢?一致性(Consistency)一致性(Consistency):事务不能破坏数据的完整性和业务的一致性 :数据的完整性: 实体完整性、列完整性(如字段的类型、大小、长度要符合要求)、外键约束等业务的一致性:例如在银行转账时,不管事务成功还是失败,双方钱的总额不变。那是如何保证数据一致性的?其实数据一致性是通过事务的原子性、持久性和隔离性来保证的原子性:语句要么全执行,要么全不执行,是事务最核心的特性,事务本身就是以原子性来定义的;实现主要基于undo log持久性:保证事务提交后不会因为宕机等原因导致数据丢失;实现主要基于redo log隔离性:保证事务执行尽可能不受其他事务影响;InnoDB默认的隔离级别是RR,RR的实现主要基于锁机制(包含next-key lock)、MVCC(包括数据的隐藏列、基于undo log的版本链、ReadView)总结其中要同时满足ACID特性,这样的事务少之又少。实际中很多例子都只是满足一些特性,比如:MySQL的NDB Cluster事务不满足持久性和隔离性;InnoDB默认事务隔离级别是可重复读,不满足隔离性;Oracle默认的事务隔离级别为READ COMMITTED,不满足隔离性所以我们只能使用这个四个维度的特性去衡量事务的操作。
2021年01月23日
112 阅读
0 评论
0 点赞
2021-01-18
亿级大表在线不锁表变更字段与索引
在业界中有一个比较成熟的工具,针对大表的场景,可以在线进行Alter变更,且不会出现锁表的风险。除此之外,它还有其他的一些优点,让我们开始探索吧。背景大家在日常工作中,往往需要对数据库的表结构做变更,一般涉及到增删字段,修改字段属性等ALTER的操作。然而,在大表场景下,特别是千万级、亿级的大表,如果处理不当。这些操作往往会引发锁表的巨大隐患,特别是在生产环境中,一旦在变更表结构过程中,出现了长时间锁表,会导致用户产生的数据长时间无法正常变更到表中,进而导致服务功能异常,结果将是灾难性的。一般执行这种Alter类型的变更,我们可能有以下的想法:停服,在停服期间做表结构的变更,自然就可以防止对用户产生影响。但是,很多场景是不允许停服的。并且如果表的数据量达到上亿,那么需要停服时间可能需要十几个小时,甚至更长,这是极不现实的;凌晨执行,在用户较少的时间段内,做变更,尽量减少对用户产生影响。但是如果出现锁表的话,万一有用户使用服务,服务将不可用;使用换表,但是缺点是复制数据到新表期间,如果用户在这期间做了update或delete操作,且数据发生在已经复制完成的部分,那么将无法感知到这部分数据,导致丢失掉用户的操作数据,风险太大;使用存储过程,缺点是执行时间会很久,且有可能影响到用户的DDL操作。因为为了防止每次循环修改时,锁住太多数据行,我们需要控制每次更新数据的行数,粒度不能太大,否则很有可能会锁住用户正在操作的数据行。那么针对以上实际的需求,就没有一个很好的工具,来解决我们的痛点吗?其实在业界中,就有一个比较成熟的工具,针对大表的场景,可以在线进行Alter变更,且不会出现锁表的风险。除此之外,它还有其他的一些优点,让我们开始探索吧。一、pt-osc是什么pt-online-schema-change是Percona-toolkit一员,通过改进原生ddl的方式,达到不锁表在线修改表结构的效果。在Percona的官网中,关于pt-osc工具,也特别提到了ALTER表不会出现锁表的特性。针对上面谈到的避免锁表、感知用户更新删除动作等,ps-osc工具是怎么解决的呢?pt-osc主要执行步骤如下:创建一个跟原表一模一样的新表,命名方式为'_正式表名_new';使用alter语句将要变更的内容在新创建的新表上做变更,避免了对原表的alter操作;在原表中创建3个触发器,分别是insert、update和delete,主要是用于原表在往新表复制数据时,如果用户有DDL操作,触发器能够将在这期间出现的DDL操作数据也写入到新表中,确保新表的数据是最新的,不会丢失掉用户的新操作数据;按块拷贝数据到新表,拷贝过程对数据行持有S锁;重命名,将原表重命名为老表,命名为“_正式表名_old”,将新表重命名为正式表,可通过配置决定执行完成后是否删除掉老表;删除3个触发器;二、pt-osc的安装在linux系统中安装步骤:--下载安装包 wget http://szxge1-sw.artifactory.cd-cloud-artifact.tools.huawei.com/artifactory/CommonComponent/common/tool/percona-toolkit-3.1.0.tar.gz --解压安装包 tar -zxvf percona-toolkit-3.1.0.tar.gz --安装依赖环境 yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker yum -y install perl-Digest-MD5 cd percona-toolkit-3.1.0 perl Makefile.PL --编译 make make install yum install mariadb --安装Mysql yum install perl-DBD-MySQL三、pt-osc的使用pt-osc工具使用起来很简单,直接在linux命令行输入pt-osc格式的命令,即可直接执行。以Mysql数据库增加一个名字是MARK的字段为例:pt-online-schema-change --user="root" --password="*****" --host="数据库IP" --port=3306 --alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';" D=my_test,t=t_test --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8 --no-check-unique-key-change --max-load="Threads_running=100" --critical-load="Threads_running=300" --recursion-method=none;在上面的语句中:user和password分别为数据库执行变更操作的用户名、密码,需要高权限;host为数据库的IP地址;port为数据库的端口号;alter后面跟上具体的alter语句;D为database名字;t为要执行变更的表名;no-drop-old-table就是不要删除charset,字符集,使用utf8;max-load,在复制数据时,工具会监控数据库中正在运行的线程数,如果大于配置的Threads_running值,那么会暂停复制,直到小于该值。以此防止对数据库造成较大压力,影响现网业务正常使用;critical-load,默认为50,在每个块之后检查SHOW GLOBAL STATUS,与max-load不同的是,如果负载太高,,直接中止,而不是暂停。可根据自己数据库情况斟酌配置阈值;注意:在--alter后面跟着的变更语句中,列名不可以加符号,否则会出现报错。如--alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';",MARK字段加了符号,就会出现错误,COMMENT后面有`符号无影响。下面是使用pt-osc工具,实际执行一个作业时,打印出来的信息。为了安全起见,部分日志信息做了隐藏忽略。[root@ttt ~]# `pt-online-schema-change --user="root" --password="*****" --host="数据库IP" --port=3306 --alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';" D=my_test,t=t_test --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8 --no-check-unique-key-change --max-load="Threads_running=100" --critical-load="Threads_running=300" --recursion-method=none;` No slaves found. See --recursion-method if host EulerOS-BaseTemplate has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: analyze_table, 10, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `my_test`.`t_test`... Creating new table... CREATE TABLE `my_test`.`_t_test_new` ( `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '递增ID', .............建表语句数据................ Created new table my_test._t_test_new OK. Altering new table... ALTER TABLE `my_test`.`_t_test_new` ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1'; Altered `my_test`.`_t_test_new` OK. 2020-10-14T11:14:48 Creating triggers... 2020-10-14T11:14:48 Created triggers OK. 2020-10-14T11:14:48 Copying approximately 346697 rows... INSERT LOW_PRIORITY IGNORE INTO `my_test`.`_t_test_new` (`id`, ..建表语句信息.... FROM `my_test`.`_t_test_new` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 31340 copy nibble*/ SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `my_test`.`t_test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/ 2020-10-14T11:14:53 Copied rows OK. 2020-10-14T11:14:53 Analyzing new table... 2020-10-14T11:14:53 Swapping tables... RENAME TABLE `my_test`.`t_test` TO `my_test`.`_t_test_old`, `my_test`.`_t_test_new` TO `my_test`.`t_test` 2020-10-14T11:14:53 Swapped original and new tables OK. Not dropping old table because --no-drop-old-table was specified. 2020-10-14T11:14:53 Dropping triggers... DROP TRIGGER IF EXISTS `my_test`.`pt_osc_my_test_t_test_del` DROP TRIGGER IF EXISTS `my_test`.`pt_osc_my_test_t_test_upd` DROP TRIGGER IF EXISTS `my_test`.`pt_osc_my_test_t_test_ins` 2020-10-14T11:14:54 Dropped triggers OK. Successfully altered `my_test`.`t_test`.四、性能对比前面介绍了很多pt-osc的优点,以及良好的特性。那么实际使用效果到底怎么样呢?在测试环境中,专门做了一个测试,让大家有更加直观的感受。在测试库中,准备了一张1600万数据的大表,目标为对大表添加一个字段,分别使用存储过程和pt-osc工具,进行测试。4.1 使用存储过程首先使用存储过程做测试,为防止锁表,每次只更新200行。整个变更从开始到完成,需要耗费90分钟。其实,存储过程在执行过程中,如果恰好用户也在DDL操作存储过程正在变更的数据行,还有可能会锁住用户的数据,导致用户不能变更成功。4.2 使用pt-osc工具pt-osc从开始执行到变更完成,耗时7分钟左右,速度非常快。在执行的过程中,测试环境的服务连接到该数据库,并执行多个会操作该表的任务,整个过程中,任务能够正常执行,未出现异常情况。5、结语ps-osc的上述优点,在现网环境的不停服等要求下,能够优雅地帮助我们实施变更,且保证在变更期间,数据库不会受到锁表、过载等的影响,进而保证了业务能够正常运转。
2021年01月18日
109 阅读
0 评论
0 点赞
Redis Desktop Manager 2021.10.236 中文破解版 (Redis 数据库管理工具)
2020年06月25日
1,525 阅读
0 评论
0 点赞
2020-06-25
Redis桌面管理工具Mac版是一款基于Qt5的跨平台Redis桌面管理软件,支持C++编写,响应迅速,性能好。但不支持数据库备份与恢复。
Navicat Premium for Mac 中文破解版 (强大的数据库管理工具)
2020年06月24日
9,629 阅读
0 评论
5 点赞
2020-06-24
Navicat Premium 15 是mac平台上一款连接多种类型数据库进行管理的工具,比如同时连接到MySQL,Oracle,SQL Server,SQLite和PostgreSQL数据库,让对多种数据库的数据库管理变得非常简单。