此篇是本人在准备java开发岗位时准备的一些关于mysql的优化和一些面试需要特别注意的地方,还有诸多面试知识点在主页,欢迎大家查看,互相交流学习~~ 第一部分链接 第二部分链接 第三部分链接 这是最终部分了
MySQl主从复制:
- 作为备用数据库,并且不影响业务
- 可做读写分离,一个写库,一个或多个读库,在不同的服务器上,充分发挥服务器和数据库的性能,但要保证数据的一致性
binlog记录格式: statement、row、mixed
基于语句statement的复制、基于行row的复制、基于语句和行(mix)的复制。其中基于row的复制方式更能保证主从库数据的一致性,但日志量较大,在设置时考虑磁盘的空间问题
"主从复制有延时",这个延时期间读取从库,可能读到不一致的数据。
缓存记录写key法:
在cache里记录哪些记录发生过的写请求,来路由读主库还是读从库
异步复制:
在异步复制中,主库执行完操作后,写入binlog日志后,就返回客户端,这一动作就结束了,并不会验证从库有没有收到,完不完整,所以这样可能会造成数据的不一致。
半同步复制:
当主库每提交一个事务后,不会立即返回,而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端,通过一份在主库的Binlog,另一份在其中一个从库的Relay-log,可以保证了数据的安全性和一致性。
全同步复制:
指当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。
Keepalived + VIP + MySQL 主从/双主
当写节点 Master db1 出现故障时,由 MMM Monitor 或 Keepalived 触发切换脚本,将 VIP 漂移到可用的 Master db2 上。当出现网络抖动或网络分区时,MMM Monitor 会误判,严重时来回切换写 VIP 导致集群双写,当数据复制延迟时,应用程序会出现数据错乱或数据冲突的故障。有效避免单点失效的架构就是采用共享存储,单点故障切换可以通过分布式哨兵系统监控。
架构选型: MMM 集群 -> MHA集群 -> MHA+Arksentinel。
转移方式及恢复方法
1. 虚拟IP或DNS服务 (Keepalived +VIP/DNS 和 MMM 架构)
问题:在虚拟 IP 运维过程中,刷新ARP过程中有时会出现一个 VIP 绑定在多台服务器同时提供连接的问题。这也是为什么要避免使用 Keepalived+VIP 和 MMM 架构的原因之一,因为它处理不了这类问题而导致集群多点写入。
2. 提升备库为主库(MHA、QMHA)
尝试将原 Master 设置 read_only 为 on,避免集群多点写入。借助 binlog server 保留 Master 的 Binlog;当出现数据延迟时,再提升 Slave 为新 Master 之前需要进行数据补齐,否则会丢失数据。
MySQL中的锁,按照锁的粒度分,分为以下三类:
全局锁就是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的DML的写语句,DDL语句,已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性。
表级锁,每次操作锁住整张表。锁定粒度大,发生锁冲突的概率最高,并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁,主要分为以下三类:
1. 加锁:lock tables表名... read/write。
2. 释放锁: unlock tables /客户端断开连接。
1.意向共享锁(lS):与表锁共享锁(read)兼容,与表锁排它锁(write)互斥。
2.意向排他锁(IX)∶与表锁共享锁( read)及排它锁(write)都互斥。意向锁之间不会互斥。
行级锁,每次操作锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。应用在lnnoDB存储引擎中。
InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。对于行级锁,主要分为以下三类:
1. 针对唯一索引进行检索时,对已存在的记录进行等值匹配时,将会自动优化为行锁。
2. InnoDB的行锁是针对于索引加的锁,不通过g引条件检索数据,那么InnoDB将对表中的所有记录加锁,此时就会升级为表锁。
基于粒度:
基于属性:
#占位符的特点
$占位符的特点
第—范式:每个列都不可以再拆分。
第二范式:在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分。
第三范式:在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键。
内连接取量表交集部分,左连接取左表全部右表匹部分,右连接取右表全部坐表匹部分
where是约束声明,having是过滤声明,where早于having执行,并且where不可以使用聚合函数,having可以
char是不可变的,最大长度为255,varchar是可变的字符串,最大长度为2^16
事务1已经获取数据A的写锁,想要去获取数据B的写锁,然后事务2获取了B的写锁,想要去获取A的写锁,相互等待形成死锁。 mysql解决死锁的机制有两个:1.等待, 直到超时 2.发起死锁检测,主动回滚一条事务 死锁检测的原理是构建一个以事务为顶点、 锁为边的有向图, 判断有向图是否存在环, 存在即有死锁。
innodb 引擎:
MySQL8.0前,下次自增会取表中最大 id + 1。原理是最大id会记录在内存中,重启之后会重新读取表中最大的id
MySQL8.0后,仍从删除数据 id 后算起。原理是它将最大id记录在redolog里了
myisam:
自增的 id 都从删除数据 id 后算起。原理是它将最大id记录到数据文件里了
(1)一次sql插入多条数据,可以减少写redolog日志和binlog日志的io次数(sql是有长度限制的,但可以调整)
(2)保证数据按照索引进行有序插入
(3)可以分表后多线程插入
一级缓存是SqlSession级别的缓存,默认开启。
二级缓存是NameSpace级别(Mapper)的缓存,多个SqlSession可以共享,使用时需要进行配置开启。
动态SQL是MyBatis的强大特性之一 基于功能强大的OGNL表达式。
动态SQL主要是来解决查询条件不确定的情况,在程序运行期间,根据提交的条件动态的完成查询
常用的标签:
<if> : 进行条件的判断
<where>:在<if>判断后的SQL语句前面添加WHERE关键字,并处理SQL语句开始位置的AND 或者OR的问题
<trim>:可以在SQL语句前后进行添加指定字符 或者去掉指定字符.
<set>: 主要用于修改操作时出现的逗号问题
<choose> <when> <otherwise>:类似于java中的switch语句.在所有的条件中选择其一
<foreach>:迭代操作,批量操作
(1)from 子句组装来自不同数据源的数据;
(2)where 子句基于指定的条件对记录行进行筛选;
(3)group by 子句将数据划分为多个分组;
(4)使用聚集函数进行计算;
(5)使用 having 子句筛选分组;
(6)计算所有的表达式;
(7)select 的字段;
(8)使用order by 对结果集进行排序。
order
set status=3 where id=123 and status=2;第一次请求时,该订单的状态是已支付,值是2,所以该update语句可以正常更新数据,sql执行结果的影响行数是1,订单状态变成了3。后面有相同的请求过来,再执行相同的sql时,由于订单状态变成了3,再用status=2作为条件,无法查询出需要更新的数据,所以最终sql执行结果的影响行数是0,即不会真正的更新数据。但为了保证接口幂等性,影响行数是0时,接口也可以直接返回成功。
具体步骤:
1 用户通过浏览器发起请求,服务端收集数据。
2 根据id和当前状态作为条件,更新成下一个状态
3 判断操作影响行数,如果影响了1行,说明当前操作成功,可以进行其他数据操作。
4 如果影响了0行,说明是重复请求,直接返回成功。