目录
一. 分布式数据库的数据一致性怎么保证
- 通过悲观锁的 for update
- 通过乐观锁的加version 字段,然后不断的检查
- 针对秒杀系统,可以把并发请求放到队列中,一个一个处理
- 通过redis 实现事务
二. MySQL主从复制什么原因会造成不一致,如何预防及解决?
- 原因
- 人为原因导致从库与主库数据不一致(从库写入)
- 主从复制过程中,主库异常宕机
- 设置了ignore/do/rewrite等replication等规则
- binlog非row格式
- 异步复制本身不保证,半同步存在提交读的问题,增强半同步起来比较完美。 但对于异常重启(Replication Crash Safe),从库写数据(GTID)的防范,还需要策略来保证。
- 从库中断很久,binlog应用不连续,监控并及时修复主从
- 从库启用了诸如存储过程,从库禁用存储过程等
- 数据库大小版本/分支版本导致数据不一致?,主从版本统一
- 备份的时候没有指定参数 例如mysqldump --master-data=2 等
- 主从sql_mode 不一致
- 一主二从环境,二从的server id一致
- MySQL自增列 主从不一致
- 主从信息保存在文件里面,文件本身的刷新是非事务的,导致从库重启后开始执行点大于实际执行点
- 采用5.6的after_commit方式半同步,主库当机可能会引起主从不一致,要看binlog是否传到了从库
- 启用增强半同步了(5.7的after_sync方式),但是从库延迟超时自动切换成异步复制
- 预防和解决的方案有
- master:innodb_flush_log_at_trx_commit=1&sync_binlog=1
- slave:master_info_repository=“TABLE”&relay_log_info_repository=“TABLE”&relay_log_recovery=1
- 设置从库库为只读模式
- 可以使用5.7增强半同步避免数据丢失等
- binlog row格式
- 必须引定期的数据校验机制
- 当使用延迟复制的时候,此时主从数据也是不一致的(计划内),但在切换中,不要把延迟从提升为主库哦~
- mha在主从切换的过程中,因主库系统宕机,可能造成主从不一致(mha本身机制导致这个问题
三. mysql为什么用b+树 不用b树
3.1 什么是b树
b树 是一种m叉树,节点都是有序的,吸收了搜索树的特点, 每个节点存储指针 指向下面的节点,索引的值,以及一个data数据,就是一行数据
3.2 什么是b+树
b+树的非叶子节点存储索引,叶子节点存储data,并用链表连接起来,都是有序的。
3.3 为什么用b+树
因为b树每个节点存储data,而每个页的空间是有限制的,导致存储的索引比较少,这样树的深度会变深,磁盘io的次数变多,读取效率变差。
b+树由于节点存储的都是索引,所以可以极大的减少io效率,每次查询的时间也比较一致,b树搜索可能在非叶子节点结束。也可能到最下面的层才能查找到数据。
四. 主键id为什么自增?
因为如果不是自增的状态,插入或者删除数据的时候会造成页分裂或者页合并,造成io的开销,如果是自增的,每次直接追加数据就好了。
五. MyISAM 与innodb的区别
- innodb 的数据与索引在一个文件内(聚簇索引,是一种存储结构),MyISAM的数据与索引分别在不同文件(非聚簇索引)
- innodb 的b+树中叶子结点存储的是一行数据,MyISAM叶子节点存储的是地址,根绝地址可以找到那一行数据
- innodb支持表锁,以及行锁。MyISAM 支持表锁,不支持行锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限
- innodb支持事务,MyISAM不支持事务
- InnoDB 支持外键,而 MyISAM 不支持
五. 索引
- 主键索引
主键是一种唯一性的索引,必须指定primarykey,每个表只能有一个索引 - 唯一索引
索引列的值只能出现一次,必须唯一可以为空 - 普通索引
基本的索引类型,值可以为空,没有唯一性的限制 - 全文索引
全文索引的类型是fulltext,可以在varchar char text类型的列上创建
就像搜索一篇文章出现哪个单词之类的
5. 组合索引
多列值组成一个索引,专门用来组合搜索。
六. 回表
当用的普通索引查找数据时,叶子节点存储的是主键的值。所以还需要再去主键的b+树查找值。这样就叫做回表。如果没有主键,那么会按照唯一索引,如果没有唯一索引,mysql维护了一个6位的row_id。
七. 最左匹配
针对的是组合索引
当一个查询条件是两个列的时候,就是需要两个查询条件
比如age gender
select * form t1 where age = ? and gender = ?
select * form t1 where gender = ? and age = ?
select * form t1 where gender = ?
select * form t1 where age = ?
这四条语句中
第一条首先是age 然后是gender
第二条 虽然顺序反了,但是mysql有一个优化器,可以把顺序反转过来继续使用最左匹配
第三条 只有一个而且开头不是age 那么不会进行最左匹配
第四条 只有一个但是第一个是age 那么会进行最左匹配原则
八. 索引覆盖
当建立了以name为普通索引
select * from t1 where name=a
select id from t1 where name=a
第一条语句在执行的时候会去回表查询
第二条语句的执行的时候第一次就拿到了这个值,不会再去回表查询,这就叫做索引覆盖
其实通过遍历索引取得数据
九. 索引下推
9.1 什么是索引下推
不使用索引条件下推优化时存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件。
当使用索引条件下推优化时,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。
9.2 执行过程
- 不使用索引条件下推优化时的查询过程
-
获取下一行,首先读索引元组,然后使用索引去查找并读取所有的行
-
根据WHERE条件部分,判断数据是否符合。根据判断结果接受或拒绝该行
- 使用索引条件下推优化时的查询过程
-
获取下一行的索引元组(不是所有行)
-
根据WHERE条件部分,判断是否可以只通过索引列满足条件。如果不满足,则获取下一行索引元组
-
如果满足条件,则通过索引元组去查询并读取所有的行
-
根据遗留的WHERE子句中的条件,在当前表中进行判断,根据判断结果接受或者拒绝改行
简单来说就是先检查改行是否是索引要用的,是的话继续检查其他题哦键
十. 组合索引失效条件
我们的索引需要满足最左匹配条件的。
对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c)。 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。
(1) select * from myTest where a=3 and b=5 and c=4; ---- abc顺序
abc三个索引都在where条件里面用到了,而且都发挥了作用
(2) select * from myTest where c=4 and b=6 and a=3;
where里面的条件顺序在查询之前会被mysql自动优化,效果跟上一句一样
(3) select * from myTest where a=3 and c=7;
a用到索引, b没有用,所以c是没有用到索引效果的
(4) select * from myTest where a=3 and b>7 and c=3; ---- b范围值,断点,阻塞了c的索引
a用到了,b也用到了,c没有用到,这个地方b是范围值,也算断点,只不过自身用到了索引
(5) select * from myTest where b=3 and c=4; ---- 联合索引必须按照顺序使用,并且需要全部使用
因为a索引没有使用,所以这里 bc都没有用上索引效果
(6) select * from myTest where a>4 and b=7 and c=9;
a用到了 b没有使用,c没有使用
(7) select * from myTest where a=3 order by b;
a用到了索引,b在结果排序中也用到了索引的效果,a下面任意一段的b是排好序的
(8) select * from myTest where a=3 order by c;
a用到了索引,但是这个地方c没有发挥排序效果,因为中间断点了,使用 explain 可以看到 filesort
(9) select * from mytable where b=3 order by a;
b没有用到索引,排序中a也没有发挥索引效果
总结:
-
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
-
存储引擎不能使用索引范围条件右边的列
-
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
十一. 数据库三大范式
1NF :
原子性。每个元素不可再分。也就是属性列
2NF:
消除了非主属性对码的部分函数依赖
3NF:
消除了非主属性对码的传递函数依赖
bcNF:
11.1 如果违反了1NF会怎么样
- 假如是升高体重为一列,查询身高大于70cm的人,那么首先会做一次全表的扫描,然后在应用层做一遍fulter,然后才能得到结果。效率非常低
- 如果是组合列的话,是没办法做join的
十二. 为什么非关系型数据库用b树,关系型用b+树
- b树的最好时间是O(1) 最坏是O(logn),平均的时间复杂度要好于b+树
- 但是b树在写的时候节点分裂概率会更大,适合读多写少的操作。
- 关系型数据库遍历查找比较多,b+树很方便就可以进行在链表的遍历操作。
- 从io上来看,相同数据量,b树比b+树深,单个节点b+树存储的索引量更多,io操作时间就少一些。