Mysql相关
一、数据库的三范式是什么
第一范式:列不可再分
第二范式:行可以唯一区分,主键约束
第三范式:表的非主属性不能依赖与其他表的非主属性 外键约束
且三大范式是一级一级依赖的,第二范式建立在第一范式上,第三范式建立第一第二范式上。
二、什么是视图
视图是一种虚拟的表,具有和物理表相同的功能。可以对视图进行增,改,查,操作,试图通常是有一 个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易,相比多表 查询。
三、分库分表之后,id 主键如何处理?
因为要是分成多个表之后,每个表都是从 1 开始累加,这样是不对的,我们需要一个全局唯一的 id 来 支持。
生成全局 id 有下面这几种方式:
UUID:不适合作为主键,因为太长了,并且无序不可读,查询效率低。比较适合用于生成唯一的 名字的标示比如文件的名字。
数据库自增 id : 两台数据库分别设置不同步长,生成不重复ID的策略来实现高可用。这种方式生成 的 id 有序,但是需要独立部署数据库实例,成本高,还会有性能瓶颈。
利用 redis 生成 id : 性能比较好,灵活方便,不依赖于数据库。但是,引入了新的组件造成系统更 加复杂,可用性降低,编码更加复杂,增加了系统成本。
Twitter的snowflake算法 :Github 地址:https://github.com/twitter-archive/snowflake。
美团的Leaf分布式ID生成系统 :Leaf 是美团开源的分布式ID生成器,能保证全局唯一性、趋势递 增、单调递增、信息安全,里面也提到了几种分布式方案的对比,但也需要依赖关系数据库、 Zookeeper等中间件。感觉还不错。美团技术团队的一篇文章:https://tech.meituan.com/2017/ 04/21/mt-leaf.html 。
四。Mysql搜索引擎以及他们各自特点和区别
mysql常用引擎包括:MYISAM、Innodb、Memory、MERGE
MYISAM:全表锁,拥有较高的执行速度,不支持事务,不支持外键,并发性能差,占用空间相对 较小,对事务完整性没有要求,以select、insert为主的应用基本上可以使用这引擎
Innodb:行级锁,提供了具有提交、回滚和崩溃回复能力的事务安全,支持自动增长列,支持外键 约束,并发能力强,占用空间是MYISAM的2.5倍,处理效率相对会差一些
Memory:全表锁,存储在内容中,速度快,但会占用和数据量成正比的内存空间且数据在mysql重 启时会丢失,默认使用HASH索引,检索效率非常高,但不适用于精确查找,主要用于那些内容变 化不频繁的代码表
MERGE:是一组MYISAM表的组合
MyIsam和InnoDb区别:
储存数据结构区别(非聚簇和聚簇):
相同点:
- 首先是肯定像B+树的特点一样,所有的data数据域都在叶子节点,而内结点就只有索引,节省空间
- 叶子节点都有指向下一个叶子节点的指针,这样区间查询效率快,直接从叶子节点直接到另一个叶子节点,不用再回根节点
不同点:就在那个叶子节点的data域
MyIsam 表包括三个文件:索引文件,表结构文件,数据文件,data中存放的是数据的地址,然后再通过数据表的堆上一行一行的去找,而不是页,文件分散,所以称为非聚簇。
InnoDb 表中数据文件本身就是索引文件,所以data中存放的就是数据本身的值,可以直接找到值,文件聚集,所以称为聚簇。
应用的区别,对于数据量大的数据访问,就不太适合用非聚簇,而聚簇就挺适合。
事务的支持:
MyIsam不支持事务,但是它的操作是原子的(要不执行,要不不执行,没有执行一半的情况,可以有效避免A在修改数据时因为种种原因停下了,而这时数据修改了一半,这就会影响后面读取这个的数据操作),回滚会造成不完全回滚。
InnoDb 支持事务。
锁的粒度:
InnoDb :支持行级锁,InnoDB的行销是基于索引实现的,如果不通过索引访问数据,InnoDB会使用表锁。
MyISAM:支持全文搜索(表级锁)。
外键:
InnoDb :支持。
MyISAM:不支持外键。
表的行数:
MyISAM:不保存表的具体行数,扫描表来计算有多少行。
InnoDb :保存表的具体行数,不带where时,直接返回保存的行数。
delete操作:
InnoDb :DELETE 表时,是一行一行的删除。
MyISAM:DELETE 表时,先drop表,然后重建表。
数据和索引存放位置:
InnoDB 把数据和索引存放在表空间里面。
MyISAM 表被存放在三个文件 。 frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸。
跨平台:
InnoDB 跨平台可直接拷贝使用。
MyISAM 跨平台很难直接拷贝。
压缩:
InnoDB 表格很难被压缩。
MyISAM 表格可以被压缩。
五。Mysql索引
InnoDB有两大类索引,一类是聚集索引(Clustered Index),一类是普通索引(Secondary Index)。底层结构为平衡树(B+树)
InnoDB的聚集索引
InnoDB聚集索引的叶子节点存储行记录,因此InnoDB必须要有且只有一个聚集索引。
1.如果表定义了PK(Primary Key,主键),那么PK就是聚集索引。
2.如果表没有定义PK,则第一个NOT NULL UNIQUE的列就是聚集索引。
3.否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。
这种机制使得基于PK的查询速度非常快,因为直接定位的行记录。
InnoDB的普通索引
InnoDB普通索引的叶子节点存储的是索引name对应的主键值(MyISAM则是存储的行记录头指针)。
假设有个t表(id PK, name KEY, sex, flag),这里的id是聚集索引,name则是普通索引。
表中有四条记录:
id | name | sex | flag |
1 | sj | m | A |
3 | zs | m | A |
5 | ls | m | A |
9 | ww | f | B |
聚集索引的B+树索引(id是PK,叶子节点存储行记录):
普通索引的B+树索引(name是KEY,叶子节点存储PK值,即id):
六。怎么保证Redis和Mysql数据一致性
不管是先写MySQL数据库,再删除Redis缓存;还是先删除缓存,再写库,都有可能出现数据不一致的情况。举一个例子:
1.如果删除了缓存Redis,还没有来得及写库MySQL,另一个线程就来读取,发现缓存为空,则去数据库中读取数据写入缓存,此时缓存中为脏数据。
2.如果先写了库,在删除缓存前,写库的线程宕机了,没有删除掉缓存,则也会出现数据不一致情况。
因为写和读是并发的,没法保证顺序,就会出现缓存和数据库的数据不一致的问题。
如来解决?这里给出两个解决方案,先易后难,结合业务和技术代价选择使用。
1.第一种方案:采用延时双删策略
在写库前后都进行redis.del(key)操作,并且设定合理的超时时间。
伪代码如下:
public void write(String key,Object data){ redis.delKey(key);
db.updateData(data); Thread.sleep(500); redis.delKey(key); }
具体的步骤就是:
先删除缓存;
再写数据库;
休眠500毫秒;
再次删除缓存。
那么,这个500毫秒怎么确定的,具体该休眠多久呢?
需要评估自己的项目的读数据业务逻辑的耗时。这么做的目的,就是确保读请求结束,写请求可以删除读请求造成的缓存脏数据。
当然这种策略还要考虑redis和数据库主从同步的耗时。最后的的写数据的休眠时间:则在读数据业务逻辑的耗时基础上,加几百ms即可。比如:休眠1秒。
设置缓存过期时间
从理论上来说,给缓存设置过期时间,是保证最终一致性的解决方案。所有的写操作以数据库为准,只要到达缓存过期时间,则后面的读请求自然会从数据库中读取新值然后回填缓存。
该方案的弊端
结合双删策略+缓存超时设置,这样最差的情况就是在超时时间内数据存在不一致,而且又增加了写请求的耗时。
2、第二种方案:异步更新缓存(基于订阅binlog的同步机制)
技术整体思路:
MySQL binlog增量订阅消费+消息队列+增量数据更新到redis
读Redis:热数据基本都在Redis
写MySQL:增删改都是操作MySQL
更新Redis数据:MySQ的数据操作binlog,来更新到Redis
Redis更新
1)数据操作主要分为两大块:
一个是全量(将全部数据一次写入到redis)
一个是增量(实时更新)
这里说的是增量,指的是mysql的update、insert、delete变更数据。
2)读取binlog后分析 ,利用消息队列,推送更新各台的redis缓存数据。
这样一旦MySQL中产生了新的写入、更新、删除等操作,就可以把binlog相关的消息推送至Redis,Redis再根据binlog中的记录,对Redis进行更新。
其实这种机制,很类似MySQL的主从备份机制,因为MySQL的主备也是通过binlog来实现的数据一致性。
这里可以结合使用canal(阿里的一款开源框架),通过该框架可以对MySQL的binlog进行订阅,而canal正是模仿了mysql的slave数据库的备份请求,使得Redis的数据更新达到了相同的效果。
当然,这里的消息推送工具你也可以采用别的第三方:kafka、rabbitMQ等来实现推送更新Redis。
七。Mysql的主从延迟怎么解决
MySQL数据库主从同步延迟是怎么产生的?
当主库的TPS并发较高时,产生的DDL数量超过slave一个sql线程所能承受的范围,那么延时就产生了,当然还有就是可能与slave的大型query语句产生了锁等待。
1、从库的配置往往没有主库的配置高
2、主库支持并发写入,而5.7之前的版本上从库只有单线程SQL来完成任务。
3、MySQL主从之间的同步,并不是完全的实时同步,而是主库提交事务之后,从库才再来执行一遍
4、主库上的表的某个列没有索引,然后对这个列进行delete或update操作
5、网络问题,往返时延RTT较大。
首要原因:数据库在业务上读写压力太大,CPU计算负荷大,网卡负荷大,硬盘随机IO太高次要原因:读写binlog带来的性能影响,网络传输延迟。
MySQL数据库主从同步延迟原理mysql主从同步原理:
主库针对写操作,顺序写binlog,从库单线程去主库顺序读”写操作的binlog”,从库取到binlog在本地原样执行(随机写),来保证主从数据逻辑上一致。mysql的主从复制都是单线程的操作,主库对所有DDL和DML产生binlog,binlog是顺序写,所以效率很高,slave的Slave_IO_Running线程到主库取日志,效率比较高,下一步,问题来了,slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的,不是顺序的,成本高很多,还可能可slave上的其他查询产生lock争用,由于Slave_SQL_Running也是单线程的,所以一个DDL卡主了,需要执行10分钟,那么所有之后的DDL会等待这个DDL执行完才会继续执行,这就导致了延时。有朋友会问:“主库上那个相同的DDL也需要执行10分,为什么slave会延时?”,答案是master可以并发,Slave_SQL_Running线程却不可
MySql数据库从库同步的延迟解决方案
1)、架构方面
1.业务的持久化层的实现采用分库架构,mysql服务可平行扩展,分散压力。
2.单个库读写分离,一主多从,主写从读,分散压力。这样从库压力比主库高,保护主库。
3.服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
4.不同业务的mysql物理上放在不同机器,分散压力。
5.使用比主库更好的硬件设备作为slave总结,mysql压力小,延迟自然会变小。
2)、硬件方面
1.采用好服务器,比如4u比2u性能明显好,2u比1u性能明显好。
2.存储用ssd或者盘阵或者san,提升随机写的性能。
3.主从间保证处在同一个交换机下面,并且是万兆环境。
总结,硬件强劲,延迟自然会变小。一句话,缩小延迟的解决方案就是花钱和花时间。
3)、mysql主从同步加速
1、sync_binlog在slave端设置为0
2、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
3、直接禁用slave端的binlog
4、slave端,如果使用的存储引擎是innodb,innodb_flush_log_at_trx_commit =2
MySql数据库从库同步其他问题及解决方案
1)、mysql主从复制存在的问题:
● 主库宕机后,数据可能丢失
● 从库只有一个sql Thread,主库写压力大,复制很可能延时
2)、解决方法:
● 半同步复制---解决数据丢失的问题
● 并行复制----解决从库复制延迟的问题
3)、半同步复制mysql semi-sync(半同步复制)半同步复制:
● 5.5集成到mysql,以插件的形式存在,需要单独安装
● 确保事务提交后binlog至少传输到一个从库
● 不保证从库应用完这个事务的binlog
● 性能有一定的降低,响应时间会更长
● 网络异常或从库宕机,卡主主库,直到超时或从库恢复4)、主从复制--异步复制原理、半同步复制和并行复制原理比较
a、异步复制原理:
b、半同步复制原理:
事务在主库写完binlog后需要从库返回一个已接受,才放回给客户端;5.5集成到mysql,以插件的形式存在,需要单独安装确保事务提交后binlog至少传输到一个从库不保证从库应用完成这个事务的binlog性能有一定的降低网络异常或从库宕机,卡主库,直到超时或从库恢复
c、并行复制mysql并行复制
● 社区版5.6中新增
● 并行是指从库多线程apply binlog
● 库级别并行应用binlog,同一个库数据更改还是串行的(5.7版并行复制基于事务组)设置set global slave_parallel_workers=10;设置sql线程数为10
原理:从库多线程apply binlog在社区5.6中新增库级别并行应用binlog,同一个库数据更改还是串行的5.7版本并行复制基于事务组
总结:
1、使用MySQL5.7版本,MySQL5.7版本后引入新的机制,即基于组提交的并行复制,设置参数slave_parallel_workers的值大于0,还有一个参数slave_parallel_type='LOGICAL_CLOCK'。
。MySQL [fruit]> show variables like '%parallel%'; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | slave_parallel_type | DATABASE | | slave_parallel_workers | 0 | +------------------------+----------+ 2 rows in set (0.00 sec)
上面这个是没有修改的例子而已。仅仅是为了展示
2、可以采用percona公司的PXC架构,实现多节点写入,达到时时同步
3、在业务初期就选择合适的分库、分表策略,避免单表单库过大带来额外的复制压力。
4、避免一些无用的I/O消耗,使用PCIE-SSD硬盘
5、磁盘阵列选择RAID10架构,raid cache策略使用WB而非WT
6、适当调整buffer pool的大小
7、避免数据库进行各种大量的运算。
八。Mysql的char和varchar有什么区别
char是一种固定长度的类型,varchar则是一种可变长度的类型
char(M)类型的数据列里,每个值都占用M个字节,如果某个长度小于M,MySQL就会在它的右边用空格字符补足.(在检索操作中那些填补出来的空 格字符将被去掉)在varchar(M)类型的数据列里,每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节)
九。什么是mysql的回表查询
要说回表查询,先要从InnoDB的索引实现说起。
什么是回表查询
普通索引因为无法直接定位行记录,其查询过程在通常情况下是需要扫描两遍索引树的。
select * from t where name = 'lisi';
这里的执行过程是这样的:
粉红色的路径需要扫描两遍索引树,第一遍先通过普通索引定位到主键值id=5,然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询,即先定位主键值,再根据主键值定位行记录,性能相对于只扫描一遍聚集索引树的性能要低一些。
索引覆盖
索引覆盖是一种避免回表查询的优化策略。具体的做法就是将要查询的数据作为索引列建立普通索引(可以是单列索引,也可以一个索引语句定义所有要查询的列,即联合索引),这样的话就可以直接返回索引中的的数据,不需要再通过聚集索引去定位行记录,避免了回表的情况发生。
覆盖索引的定义与注意事项
如果一个索引覆盖(包含)了所有需要查询的字段的值,这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值,因此查询的时候直接返回索引中的字段值就可以了,不需要再到表中查询,避免了对主键索引的二次查询,也就提高了查询的效率。
要注意的是,不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值,而哈希索引、空间索引和全文索引等都不存储索引列值,索引MySQL只能使用B-Tree索引做覆盖索引。
另外,当发起一个被索引覆盖的查询(索引覆盖查询)时,在explain(执行计划)的Extra列可以看到【Using Index】的信息。
覆盖索引的优点
1.索引条目通常远小于数据行的大小,因为覆盖索引只需要读取索引,极大地减少了数据的访问量。
2.索引是按照列值顺序存储的,对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO小很多。
3.一些存储引擎比如MyISAM在内存中只缓存索引,数据则依赖操作系统来缓存,因此要访问数据的话需要一次系统调用,使用覆盖索引则避免了这一点。
4.由于InnoDB的聚簇索引,覆盖索引对InnoDB引擎下的数据库表特别有用。因为InnoDB的二级索引在叶子节点中保存了行的主键值,如果二级索引能够覆盖查询,就避免了对主键索引的二次查询。
十。mysql的几种日志
MySQL中有七种日志文件,分别是:
- 重做日志(redo log)
- 回滚日志(undo log)
- 二进制日志(binlog)
- 错误日志(errorlog)
- 慢查询日志(slow query log)
- 一般查询日志(general log)
- 中继日志(relay log)
1.重做日志(redo log)
我们都知道,事务的四大特性里面有一个是 持久性
,具体来说就是只要事务提交成功,那么对数据库做的修改就被永久保存下来了,不可能因为任何原因再回到原来的状态。那么 MySQL 是如何保证一致性的呢?最简单的做法是在每次事务提交的时候,将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题,主要体现在两个方面:
- 因为 Innodb 是以页为单位进行磁盘交互的,而一个事务很可能只修改一个数据页里面的几个字节,这个时候将完整的数据页刷到磁盘的话,太浪费资源了。
- 一个事务可能涉及修改多个数据页,并且这些数据页在物理上并不连续,使用随机 IO 写入性能太差。
因此 MySQL 设计了 redo log
,具体来说就是只记录事务对数据页做了哪些修改,这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。
redo log 包括两部分:一个是内存中的日志缓冲(redo log buffer),另一个是磁盘上的日志文件(redo log file)。MySQL 每执行一条 DML 语句,先将记录写入 redo log buffer ,后续某个时间点再一次性将多个操作记录写到 redo log file 。
默认情况下,redo log 在磁盘上由名为 ib_logfile0
和 ib_logfile1
的两个物理文件展示。redo log 相关参数简单介绍如下:
- innodb_log_files_in_group:redo log 文件的个数,命名方式如:ib_logfile0,iblogfile1... iblogfilen。默认2个,最大100个。
- innodb_log_file_size:单个 redo log 文件设置大小,默认值为 48M,最大值为512G,注意最大值指的是整个 redo log 系列文件之和,即(innodb_log_files_in_group * innodb_log_file_size )不能大于最大值512G。
- innodb_log_group_home_dir:指定 redo log 文件组所在的路径,默认./ ,表示在数据库的数据目录下。
- innodb_log_buffer_size:redo log buffer 大小,默认16M。延迟事务日志写入磁盘,把 redo log 放到该缓冲区,然后根据 innodb_flush_log_at_trx_commit 参数的设置,再把日志从 buffer 中 flush 到磁盘中。
- innodb_flush_log_at_trx_commit:控制 redo log 刷新到磁盘的策略,默认为1。值为1,每次 commit 都会把 redo log 从 redo log buffer 写入到 system ,并 fsync 刷新到磁盘文件中。值为2,每次事务提交时 MySQL 会把日志从 redo log buffer 写入到 system ,但只写入到 file system buffer,由系统内部来 fsync 到磁盘文件。如果数据库实例 crash ,不会丢失 redo log,但是如果服务器 crash,由于 file system buffer 还来不及 fsync 到磁盘文件,所以会丢失这一部分的数据。值为0,表示事务提交时不进行写入 redo log 操作,这个操作仅在 master thread 中完成,而在 master thread 中每1秒进行一次重做日志的 fsync 操作,因此实例 crash 最多丢失1秒钟内的事务。
更改 redo log 及其 buffer 大小是需要重启数据库实例的,建议初始化时做好评估。可以适当加大 redo log 组数和大小,特别是你的数据库实例更新比较频繁的情况下。但也不推荐 redo log 设置过大。
2.回滚日志(undo log)
undo log
主要用于保证数据的原子性,保存了事务发生之前的数据的一个版本,可以用于回滚。比如一条 INSERT 语句,对应一条 DELETE 的 undo log ,对于每个 UPDATE 语句,对应一条相反的 UPDATE 的 undo log ,这样在发生错误时,就能回滚到事务之前的数据状态。同时,undo log 也是 MVCC (多版本并发控制) 实现的关键。
MySQL 5.7 版本中,undo log 默认存放在共享表空间 ibdata 中。也可以在初始化时通过配置参数改成独立的文件,简单介绍几个 undo log 相关参数:
- innodb_max_undo_log_size:控制最大 undo tablespace 文件的大小,当启动了innodb_undo_log_truncate 时,undo tablespace 超过 innodb_max_undo_log_size 阀值时才会去尝试truncate。该值默认大小为1G,truncate后的大小默认为10M。
- innodb_undo_tablespaces:设置 undo 独立表空间个数,范围为0-128,5.7版本默认为0,0表示不开启独立undo表空间。该参数只能在最开始初始化 MySQL 实例的时候指定。
- innodb_undo_directory:设置 undo 表空间的存放目录,默认数据目录。
- innodb_undo_log_truncate:设置 undo 表空间是否自动截断回收。该参数生效的前提是,已设置独立表空间且独立表空间个数大于等于2个。
undo log 相关参数一般很少改动。MySQL 8.0 默认启用了独立表空间,可能 undo log 表空间的大小设置更灵活些。
3.错误日志(errorlog)
错误日志记录着 mysqld 启动和停止,以及服务器在运行过程中发生的错误及警告相关信息。当数据库意外宕机或发生其他错误时,我们应该去排查错误日志。
log_error
参数控制错误日志是否写入文件及文件名称,默认情况下,错误日志被写入终端标准输出stderr。当然,推荐指定 log_error 参数,自定义错误日志文件位置及名称。
# 指定错误日志位置及名称
vim /etc/my.cnf
[mysqld]
log_error = /data/mysql/logs/error.log
相关配置变量说明:
log_error={1 | 0 | /PATH/TO/ERROR_LOG_FILENAME}
定义错误日志文件。作用范围为全局或会话级别,属非动态变量。
4.慢查询日志(slow query log)
慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志,可以查找出哪些查询语句的执行效率很低,以便进行优化。
与慢查询相关的几个参数如下:
- slow_query_log:是否启用慢查询日志,默认为0,可设置为0,1。
- slow_query_log_file:指定慢查询日志位置及名称,默认值为host_name-slow.log,可指定绝对路径。
- long_query_time:慢查询执行时间阈值,超过此时间会记录,默认为10,单位为s。
- log_output:慢查询日志输出目标,默认为file,即输出到文件。
默认情况下,慢查询日志是不开启的,一般情况下建议开启,方便进行慢SQL优化。在配置文件中可以增加以下参数:
# 慢查询日志相关配置,可根据实际情况修改
vim /etc/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 3
log_output = FILE
5.一般查询日志(general log)
一般查询日志又称通用查询日志,是 MySQL 中记录最详细的日志,该日志会记录 mysqld 所有相关操作,当 clients 连接或断开连接时,服务器将信息写入此日志,并记录从 clients 收到的每个 SQL 语句。当你怀疑 client 中的错误并想要确切知道 client 发送给mysqld的内容时,通用查询日志非常有用。
默认情况下,general log 是关闭的,开启通用查询日志会增加很多磁盘 I/O, 所以如非出于调试排错目的,不建议开启通用查询日志。相关参数配置介绍如下:
# general log相关配置
vim /etc/my.cnf
[mysqld]
general_log = 0 //默认值是0,即不开启,可设置为1
general_log_file = /data/mysql/logs/general.log //指定日志位置及名称
6.二进制日志(binlog)
关于二进制日志,前面有篇文章做过介绍。它记录了数据库所有执行的DDL和DML语句(除了数据查询语句select、show等),以事件形式记录并保存在二进制文件中。常用于数据恢复和主从复制。
与 binlog 相关的几个参数如下:
- log_bin:指定binlog是否开启及文件名称。
- server_id:指定服务器唯一ID,开启binlog 必须设置此参数。
- binlog_format:指定binlog模式,建议设置为ROW。
- max_binlog_size:控制单个二进制日志大小,当前日志文件大小超过此变量时,执行切换动作。
- expire_logs_days:控制二进制日志文件保留天数,默认值为0,表示不自动删除,可设置为0~99。
binlog默认情况下是不开启的,不过一般情况下,建议开启,特别是要做主从同步时。
# binlog 相关配置
vim /etc/my.cnf
[mysqld]
server-id = 1003306
log-bin = /data/mysql/logs/binlog
binlog_format = row
expire_logs_days = 15
7.中继日志(relay log)
中继日志用于主从复制架构中的从服务器上,从服务器的 slave 进程从主服务器处获取二进制日志的内容并写入中继日志,然后由 IO 进程读取并执行中继日志中的语句。
relay log 相关参数一般在从库设置,几个相关参数介绍如下:
- relay_log:定义 relay log 的位置和名称。
- relay_log_purge:是否自动清空不再需要中继日志,默认值为1(启用)。
- relay_log_recovery:当 slave 从库宕机后,假如 relay log 损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的 relay log ,并且重新从 master 上获取日志,这样就保证了 relay log 的完整性。默认情况下该功能是关闭的,将 relay_log_recovery 的值设置为1可开启此功能。
relay log 默认位置在数据文件的目录,文件名为 host_name-relay-bin,可以自定义文件位置及名称。
# relay log 相关配置,从库端设置
vim /etc/my.cnf
[mysqld]
relay_log = /data/mysql/logs/relay-bin
relay_log_purge = 1
relay_log_recovery = 1
十一。mysql的锁机制
共享锁与排他锁
- 共享锁(读锁):其他事务可以读,但不能写。
- 排他锁(写锁) :其他事务不能读取,也不能写。
粒度锁
MySQL 不同的存储引擎支持不同的锁机制,所有的存储引擎都以自己的方式显现了锁机制,服务器层完全不了解存储引擎中的锁实现:
- MyISAM 和 MEMORY 存储引擎采用的是表级锁(table-level locking)
- BDB 存储引擎采用的是页面锁(page-level locking),但也支持表级锁
- InnoDB 存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
默认情况下,表锁和行锁都是自动获得的, 不需要额外的命令。
但是在有的情况下, 用户需要明确地进行锁表或者进行事务的控制, 以便确保整个事务的完整性,这样就需要使用事务控制和锁定语句来完成。
不同粒度锁的比较:
- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
- 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
- 表级锁更适合于以查询为主,并发用户少,只有少量按索引条件更新数据的应用,如Web 应用
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 最大程度的支持并发,同时也带来了最大的锁开销。
- 在 InnoDB 中,除单个 SQL 组成的事务外,
锁是逐步获得的,这就决定了在 InnoDB 中发生死锁是可能的。 - 行级锁只在存储引擎层实现,而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般
InnoDB行级锁和表级锁
InnoDB锁模式:
InnoDB 实现了以下两种类型的行锁:
- 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
- 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁:
- 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
- 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
InnoDB加锁方法:
- 意向锁是 InnoDB 自动加的, 不需用户干预。
- 对于 UPDATE、 DELETE 和 INSERT 语句, InnoDB
会自动给涉及数据集加排他锁(X); - 对于普通 SELECT 语句,InnoDB 不会加任何锁;
事务可以通过以下语句显式给记录集加共享锁或排他锁: - 共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录,并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作,则很有可能造成死锁。
- 排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录,但是不能对该记录加共享锁或排他锁,而是等待获得锁
显式锁定 :
select ... lock in share mode //共享锁
select ... for update //排他锁
select for update:
在执行这个 select 查询语句的时候,会将对应的索引访问条目进行上排他锁(X 锁),也就是说这个语句对应的锁就相当于update带来的效果。
select *** for update 的使用场景:为了让自己查到的数据确保是最新数据,并且查到后的数据只允许自己来修改的时候,需要用到 for update 子句。
select lock in share mode :in share mode 子句的作用就是将查找到的数据加上一个 share 锁,这个就是表示其他的事务只能对这些数据进行简单的select 操作,并不能够进行 DML 操作。select *** lock in share mode 使用场景:为了确保自己查到的数据没有被其他的事务正在修改,也就是说确保查到的数据是最新的数据,并且不允许其他人来修改数据。但是自己不一定能够修改数据,因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。
避免死锁,这里只介绍常见的三种
- 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。
- 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;
- 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率;
本文来自博客园,作者:风光小磊,转载请注明原文链接:https://www.cnblogs.com/lei-z/p/15846597.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [翻译] 为什么 Tracebit 用 C# 开发
· 腾讯ima接入deepseek-r1,借用别人脑子用用成真了~
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· DeepSeek崛起:程序员“饭碗”被抢,还是职业进化新起点?
· RFID实践——.NET IoT程序读取高频RFID卡/标签