mysql
绝世好阿狸
这个作者很懒,什么都没留下…
展开
-
【MySQL(二十三)】GTID 复制
本来想自己搭建,结果各种error,放弃。。。(mysql太难用。。。没做过运维)使用传统的基于文件位置的方式:需要slave指定复制的文件和位置,可能不准,位置靠后会少日志,位置靠前可能冲突,但是宁可冲突也不能少,所以需要手动跳过,不太方便。基于GTID的模式,每一条master生成的binlog,会打上一个gtid,gtid的格式是serverid:txid。所以在集群里可以认为每一条事务的gtid是唯一的。slave执行时,会记录自己执行过的gtid集合。如果需要换主,就将自己的gtid集转载 2020-11-15 23:09:05 · 286 阅读 · 0 评论 -
【MySQL(二十二)】一主一从换主
一主一从架构切主的两种方案可靠性优先:先等seconds_behind_master小于一个阈值;设置Master为只读;等待Slave完全同步了Master的数据;设置Slave可写;切写的流量到Slave;这中间,当设置Master为只读时,整个系统不可写,所以又不可用的时间。这也是为什么一开始就最好等seconds_behind_master很小时在做这个事情,不然主从延迟很大,不可用的窗口期就很久。可用性优先:不等Slave完全同步,直接设置Slave可写,并且切流量。这个原创 2020-11-04 23:53:34 · 571 阅读 · 1 评论 -
【MySQL(二十一)】binlog 事件
binlog时机事务提交时写入binlog,但是binlog持久化到磁盘与sync_binlog参数有关:0:只fwrite写入操作系统cache,由操作系统决定什么时候持久化到磁盘,及fsync;1:fsync直接写入磁盘;n:提交n个事务后fsync;1最安全,0性能最好;binlog文件有两种,一种是binlog的索引文件,也即xxx-bin.index,另一种是binlog文件。索引文件:其实就是存储了当前所有binlog文件的文件名。比如:./mys.原创 2020-09-07 23:47:33 · 681 阅读 · 0 评论 -
【MySQL(二十)】mysql8 docker 主从 搭建
没做过DBA的工作,发现整一套mysql环境是真的难。。。本人用的mac,平时单机测试,用brew装了一个本地的mysql,这次想搞搞主从,就想着用二进制包在特定目录下搭建一下,发现各种坑。。。根本搭不起来。mysql不同版本的命令都不一样,而且有很多设定其实比较坑,比如有些参数必须是第一位,指定配置文件是--defaults-file而不是--default-file,这些命令你不研究研究官网,根本没法使,但是也没时间。。。没办法,后面决定用docker整了,这里记录下。参考:https://s.原创 2020-08-02 23:25:32 · 230 阅读 · 0 评论 -
【MySQL(十九)】复制 过程
主库将数据写入本地binlog文件中;从库连接,指定起始位置;主库的binlog dump线程开始将binlog内容发送给从库;从库的io线程将收到的binlog内容写入到本地的relay log中;从库的sql线程将relay log应用到数据库中;...原创 2020-07-05 19:23:33 · 164 阅读 · 0 评论 -
【MySQL(十八)】事务 两段式提交
mysql在事务执行时,需要写入两种日志,一种是server层的binlog,另一种是引擎层的redo log。事务commit时,以上两种类型的日志的写入需要遵循两段式提交协议。什么是两段式提交?为了保证分布式事务场景下事务的一致性。因为在分布式背景下,事务语句来自不同实例,因此需要一个协调者角色。每一个实例的语句先执行prepare,并且将结果告知协调者,如果全部成功,协调者再发出commit命令,各个实例再执行提交。防止部分实例失败,导致分布式事务语句没有全部执行。当然两阶段提交并不能完全解决原创 2020-07-05 17:51:15 · 1178 阅读 · 0 评论 -
【MySQL(十七)】redo log
mysql使用WAL机制,在执行更新操作时并不会直接写盘,而是先记redo log。redo log格式(本篇图片全部来自掘金小册中《MySQL是怎样运行的》一书)type:innodb有很多类型的redo log格式,比如指定不同的偏移量等等;data:日志详细内容;mini-transaction一条sql更新语句,其实需要会涉及很多页面更新逻辑,比如更新聚簇索引,更新二级索引等,在更新索引的时候,可能需要分裂节点,新申请页面等等。innodb规定,这些一个基础的操作必须是原原创 2020-06-29 22:59:56 · 226 阅读 · 0 评论 -
【MySQL(十六)】更新丢失 解决方案
一个比较见的业务场景,先从表里读一条数据的一列,然后在内存中计算该列的新值,最后再update新值到表里:select data from table where id = xxx; // dbnewdate = data + 1; // 内存update table set data = newdata where id = xxx; // db这样的三条语句,如果不加事务,可能会有更新丢失的问题。就是多个事务并发修改同一行数据时,读到了相同的值,然后分别更新,那么就会丢失前一次更新;原创 2020-06-27 19:10:01 · 1063 阅读 · 0 评论 -
【MySQL(十五)】next-key lock
之前介绍过,sql标准中,rr级别并不能解决幻读问题,那么mysql是如何在rr级别解决幻读问题的?锁协议在非序列化隔离级别下,普通读数据是快照读,写数据则要加X锁,并且遵循两段式锁协议。也就是申请的锁会一直占用,直到事务提交。幻读问题幻读的定义有两点需要注意:1.幻读只有使用锁定读才可能出现,因为普通读使用的是非锁定读,会通过MVCC机制获取到一致性视图,是看不到新插入的行的;2.只有读到新增的数据才叫幻读;幻读的影响:1.如果只有行锁的话,语义不正确,没有锁住新更新的行;原创 2020-06-27 18:55:12 · 2097 阅读 · 0 评论 -
【MySQL(十四)】事务 MVCC机制
事务的四大特性:Atomicity(原子性),Isolation(隔离性),Consistency(一致性)和Durability(持久性)。重点说下隔离性,指的是多个事务并行执行时,效果上就像一个一个执行的。没有因为是并行而导致问题。因为系统不可能完全让事务顺序执行,这样性能太差,所以说需要在并发的同时满足隔离性。接下来,再看下事务并发执行时会遇到怎样的问题?脏写,或者说写覆盖。就是是A更新一行数据,还未提交,事务B又更新了同一行数据。一句话概括即使,允许事务更新未提交的事务已经更新的行。这个原创 2020-06-26 20:23:31 · 248 阅读 · 0 评论 -
【MySQL(十三)】Innodb 表空间
数据的基本单位是页,而页又属于各种表空间,比如系统表空间和独立表空间等。表空间里是如何组织数据的?每64个连续的页组成一个区(extent),页的大小是16kb,一个区大小也就是1mb。每256个区呢又可以划分为一个组,表空间内是以组为单位管理空间的,如下所示:为啥要创建区的概念?因为相邻的页是由指针连接的,并且是排序的,访问一个页时,很可能要访问相邻的页,比如b+树的叶子节点,我们在查找时便会访问相邻的页。所以,相邻的页最好在物理上也是连续的,这样可以减少io成本。所以说,申请页在一定程度上可原创 2020-06-26 14:21:49 · 245 阅读 · 0 评论 -
【MySQL(十二)】Innodb b树索引页
上一篇看了存储用户数据的索引页结构,这里再看下b树索引是怎么存的。本质上b树索引也是存在索引页这一种类型的页上的,和一样。不同的点在于user records区域存的内容不用而已。索引只需要存储索引值+页号即可,如果不是主键索引,还需要存主键值。另外这些记录的行格式的type是1,表明是索引类型的记录。其余基本类似,page directory区域也存储了每一组的最大索引值。所以查找过程就是:先从索引节点开始搜索,根据page directory区域使用二分法定位到索引行在内页的位置,根据索引行定位原创 2020-06-25 21:03:42 · 361 阅读 · 0 评论 -
【MySQL(十一)】Innodb 页结构
Innodb以页为单位存储数据,是操作磁盘的基本单位。一页是16kb大小。有多种类型的页,这里看下存储数据和索引的页,叫做index page。一个index page主要包含以下7部分信息:(本篇图片全部来自掘金小册中《MySQL是怎样运行的》一书)数据部分都存在user records区。了解各个索引页结构的作用还需要结合innodb的行结构来看。(本篇图片全部来自掘金小册中《MySQL是怎样运行的》一书)一行数据的头信息包括如上信息。delete_mask表明该行记录是否被删除原创 2020-06-25 19:03:36 · 517 阅读 · 0 评论 -
【MySQL(十)】Innodb 行结构
以Innodb引擎为例。mysql数据逻辑上是一行一行存放的,物理上,则是以页单位存放的,页也是mysql操作的基本单位。一页大小一般是16kb。下面看下页内具体一行数据的模型,也就是Innodb的行模式。共有四种:Compact、Redundant、Dynamic和Compressed。以Compact为例:(本篇图片全部来自掘金小册中《MySQL是怎样运行的》一书)大致可以分为两块:数据区和必要信息;必要信息:主要包括变长字段长度列表和null值列表,以及一些头信息。变长原创 2020-06-25 14:26:37 · 320 阅读 · 0 评论 -
【MySQL(九)】字符集
编码:字符 -> 二进制;解码:二进制 -> 字符;为什么会出现乱码?因为编码和解码的规则不同。本质上都是同样的一串二进制流,按照不同的规则解读的结果当然是不同的。类比一下我们的时间戳转时间的场景,时间戳就好比是二进制,时区就好比是不同的字符集,同一个时间戳用不同的时区转换,得到的结果当然是不同的。所以,我们只要保证编码和解码用同一套字符集就不会出现乱码了。mysql里支持的字符集这里主要指存储数据的字符集。可以使用如下命令查看:show charset另外我们可以设置不原创 2020-06-25 12:24:18 · 256 阅读 · 0 评论 -
【MySQL(八)】一致性非锁定读 锁定读 解析
mysql的锁有若干种分类标准,这里看下行锁中的读锁与写锁。读锁即S锁,也叫共享锁,当事务读取一行数据时,会尝试在记录上加S锁;写锁即X锁,也叫排它锁,当事务要更新或者删除一行数据时,会尝试在记录上加X锁;只有S锁和S锁是互相兼容的,其余情况都是不兼容的,需要等待。接下来我们看下事务当中,S锁和X锁分别是如何生效的,是否真如上面的表格所示。我们知道,事务的隔离级别有四种,只有在serializable级别下,才完全按照如上规则运行。并且在事务中,加锁和解锁遵循两段式锁协议,即只有在事务提原创 2020-06-15 23:37:28 · 952 阅读 · 1 评论 -
【MySQL(七)】脏页
脏页的定义是内存中和磁盘中的不一致页。那么脏页是如何产生的?在之前的介绍中,我们明白了innodb引擎是如何执行更新操作的。大致是:如果数据页在内存中,直接更新内存并写redo log;否则将磁盘中的数据页载入到内存中,更新内存并写redo log。所以当发生了更新操作并且数据页在内存中时,磁盘的修改还没有发生,而仅仅是记录了redo log。这时,就会产生不一致的状态。内存中的页就被称为脏页。脏页有什么影响?如果内存缓存不够用了,那么根据buffer pool的lru淘汰机制,必然会淘汰老原创 2020-06-14 18:19:17 · 1051 阅读 · 0 评论 -
【MySQL(六)】尽量用自增主键
从写入性能考虑,使用主键自增,那么每一条新数据都在同一个数据页里追加,如果不够,就申请新页。索引的分裂也只在最大的索引node下分裂,写入性能较高。如果使用业务字段做主键,这个就无法保证了,新数据插入的位置完全是随机的,磁盘写入代价变高了。...原创 2020-06-14 13:48:44 · 183 阅读 · 0 评论 -
【MySQL(五 )】唯一索引 普通索引
看了极客世界的mysql教程里关于这个议题的talk,总结如下查询方面:二者性能几乎一致,区别在于唯一索引只需要返回读到的第一个值,而普通索引需要向后遍历找到所有满足条件的值。但是这个操作是在内存中进行的,所以基本可以忽略;写入方面:对于唯一索引,因为需要保证唯一性,所以每一次的写操作都必须将值读到内存中,所以如果对应的数据页不在内存中,那么肯定会有一次磁盘随机读;但是对于普通索引,因为有change buffer机制,所以,如果数据页不在内存中,不一定需要将数据页读入内存。这样,在写多读少的场景下原创 2020-06-14 13:37:11 · 303 阅读 · 0 评论 -
【MySQL(四)】change buffer机制
change buffer是针对数据库的更新语句做的一种优化。之前介绍过,wal机制是一种更新语句的优化,可以将磁盘随机写转变为磁盘顺序写。那么change buffer 又是如何优化的呢?个人理解:wal是使用了redo log,在磁盘级别缓存了更新操作,磁盘随机写->磁盘顺序写;change buffer是使用了内存buffer,在内存级别又缓存了一次更新操作,降低了磁盘随机读的次数。详细看下:对于一次更新操作,innodb引擎会看更新的页是否在buffer pool中,如果在,那么直原创 2020-06-14 13:17:03 · 1452 阅读 · 1 评论 -
【MySQL(三)】buffer pool相关
buffer pool是一种减少磁盘io读的机制,原理是将访问过的磁盘数据暂留在内存中,这样下次访问相同的数据就不需要读磁盘了。这个思想在平时的开发中,也经常用到,比如我们通常会在数据库前加一层memcache以及redis的缓存,防止热点数据。mysql的内存缓存叫做buffer pool。缓存的单位是页,页是mysql管理磁盘的基本单位,也就是mysql每一次的磁盘io都是至少操作一个页的数据量。buffer pool使用一种特定的lru算法管理缓存页。因为mysql读取磁盘有预读机制,简单来讲原创 2020-06-14 12:25:56 · 200 阅读 · 0 评论 -
【MySQL(一)】WAL 机制
WAL全称是write ahead log,也就是更新数据之前先更新日志。之前不太明白为什么要用这个,也查了很多博客,发现很多都没说到根本原因上。基本的解释都是什么使用redo log恢复数据之类的。其实Mysql使用WAL的原因根本就不是这些,这些说成是结果更合理。原因是:磁盘的写操作是随机io,比较耗性能,所以如果把每一次的更新操作都先写入log,那么就转变成了顺序写操作,实际更新操作由后台线程异步写入。这样对于client端,延迟就降低了。并且,由于顺序写入大概率是在一个磁盘块内,这样产生的i原创 2020-06-08 23:04:23 · 4299 阅读 · 9 评论 -
【mysql】checkpoint技术
之前介绍了mysql原理的两个重要的概念:缓存和日志。我们知道了缓存要被周期性的刷回磁盘,那么具体是怎么刷的呢?首先对于redo日志来说,存在三个刷回的时机:(1)主线程的周期,即每一秒一次;(2)日志缓存已经使用了三分之一的空间;(3)执行到commit了;所以,我们看到了执行commit的时候,其实所做事情是很少的,因为都均摊到其他的线程了。我们只需要把redo的日志缓原创 2017-09-26 00:05:48 · 493 阅读 · 0 评论 -
【mysql】事务 运行原理 概述
以前没有深究过数据库底层是怎么运作的,只是停留在了概念层次,但是后来发现写程序的时候不是很舒服,总感觉有些点不通,于是决定看看mysql innodb引擎的原理,这里只是一个大致的介绍,不会太深入。如果我们在mysql的客户端执行了一些个操作,我们不了解任何底层,最简单的概念模型就是认为数据库的表里面的数据被修改了。也就是我这里执行一次update,数据库就会执行一个修改的io操作,很直观的理...原创 2017-09-25 23:00:00 · 485 阅读 · 0 评论 -
【mysql】sqlalchemy 对象状态
与hibernate不同,总共有5种。1.transitent:new出来的,与session没有关联;调用add会加入session2.pending:加入到session了,但是未更新至数据库;调用flush会更新至数据库3.persistant:更新到数据库;直接查询到的就是persistant的4.deleted:调用了flush,从数据库删除;此时如果回滚,还会变为per原创 2017-08-14 10:40:28 · 665 阅读 · 0 评论 -
【mysql】sqlalchemy commit 和 flush
今天看到了commit和flush函数,想要弄清楚区别。先看下对象的状态。总共5个,这里只谈3个。transitant:刚new出来的对象,没有和session或者orm框架产生关联。pending:transitant的对象调用add后,就会变为pending,加入了orm框架的监管范围。persistant:调用flush以后就会变味persistant,也就是被写到了数据库中原创 2017-08-13 16:42:12 · 11169 阅读 · 0 评论 -
【mysql】索引
mysql的索引是由引擎决定的。1.哈希索引,这个和哈希表是一样的原理,从关键字的哈希函数值映射到物理位置。特点是只能针对等于的查询,效率很高。2.B树索引,为关键字维护一棵b树,通常是b+树,叶子节点存的是数据块的位置。这种索引主要实现了顺序。首先可以排序。其次做前缀索引,应用于多列索引的场合。如果是多列,那么构建索引的顺序决定了最终字典序的顺序。查询时,要特别注意条件,因为是最左匹配。原创 2017-08-02 23:41:47 · 272 阅读 · 0 评论 -
【mysql】全文索引
全文索引是myisam引擎的功能,但是最近innodb也支持了。了解下概念。全文索引类似搜索引擎,主要有分词和索引两部分。分词就是把一段文本提取出若干词语。索引就是为每一个词语建立索引,这是一个一对多的关系。一个词语可能出现在许多数据库的许多行。具体:https://segmentfault.com/a/1190000006997077原创 2017-08-08 11:43:51 · 260 阅读 · 0 评论 -
【mysql】mysql多线程
mysql是一个单进程多线程的数据库,在innodb中大概有一下几种线程:(1)Master Thread:这是主线程,非常核心,其用途主要是做一些周期性的任务,在不同的innodb版本其功能不同,这里就看最早期的版本。早起的innodb Master线程会有两种频率的任务,一种是每1秒一次的,还有每10秒一次的。每1秒的工作:1.刷新日志;2.刷新至多100个脏页3.合并插入...原创 2017-09-26 05:44:28 · 3942 阅读 · 0 评论 -
【mysql】细说 数据库隔离级别 及实现
网上大多数关于隔离级别的文章都是讲了事务中的问题以及隔离级别可以解决的问题,我这次想看看数据库底层是如何实现隔离级别的。不过还是先来回顾一下隔离级别以及可能发生的问题。1.脏读:指的是一个事务的读操作读到了另一个未提交的事务修改的值。比如下面的场景:脏读的问题是,读到的值可能会被回滚,那么这个值就是失效的,不能继续使用,否则会有一致性问题。2.不可重复读:指的是一个事务读...原创 2017-09-26 07:09:02 · 3219 阅读 · 1 评论 -
【mysql】串行化隔离级别
串行化隔离级别是最严格的隔离级别,所谓串行话,指的是两个事务t1和t2,最终执行的结果与先执行t1在执行t2或者先执行t2再执行t1的结果一样。这个如何实现?最简单的方式叫做两段式锁。定义了两种锁,读锁和写锁,只有读-读可以共享,其余的都阻塞。两段式锁指的是加锁和解锁不能交替,也就是在一个事务内,解锁操作之后不能再有任何的加锁操作,如果严格按照这个协议来操作数据,那么最终的执行一定是可串行话。原创 2017-09-27 06:23:11 · 6060 阅读 · 0 评论 -
【docker】单机 mysql
使用docker hub里的镜像,极其简单:https://hub.docker.com/_/mysql/原创 2017-11-21 01:42:50 · 197 阅读 · 0 评论 -
【mysql】创建用户 授权
使用的是5.7版本的mysql。这里只是一个简单的例子。mysql使用host+用户名+密码的登录权限控制。所以创建一个用户需要指定host+username+pass,该用户也只能从指定的ip地址来登录mysql。比如:create user ly@localhost identified by 'ly';这条语句,创建了用户ly,密码为ly,而且限定只能从本地登录。原创 2017-11-21 04:16:40 · 413 阅读 · 0 评论 -
【docker+mysql】基于docker 配置 mysql5.7 主从 master-slave
mysql5.7,版本不同配置略有不同。首先需要按照之前介绍的mysql docker镜像 来启动两个mysql的容器。镜像通过命令拉取:docker pull mysql通过docker-compose.yml来启动容器:version: '3.1'services: master: image: mysql environment:原创 2017-11-21 05:46:32 · 3704 阅读 · 0 评论 -
【mysql】unique key index区别
关系大致是这样:mysql中的unique约束是通过索引实现的;key的含义是概念级别的,意味着唯一性,key的概念等价于unique;所以说只要加了unique约束或者key,就会建立一个索引。在mysql中,使用index或者unique(以及key)都会简历索引,区别在于是否允许重复,这个可以在show index命令中看到。CREATE TABLE user1(...原创 2019-06-30 13:47:31 · 5335 阅读 · 0 评论 -
【mysql】insert 冲突
问题,如果在某一列或者某几列上定义了unique约束或者key,插入相同的数据就会报错,例子:mysql> show index from user;+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------...原创 2019-07-06 22:54:46 · 553 阅读 · 0 评论 -
mysql order by 多个字段
下图是只加了一个排序的结果:可以看到,单一字段是没问题的。如果加入c2呢?下图是c2可以发现,这里的orderby是先按c1增序,然后对于按c1排好序以后的相同的元素,又根据c2做了降序排序。下面 是c1,c2都增序,结果符合。总结就是,orderby多个字段时,用逗号分隔每一个字段,如果字段不指明排序方式,默认是增序。排序的方法是先按第一个字段排序,如果有相同原创 2017-02-11 12:14:08 · 11294 阅读 · 3 评论