mysql 相关概念
1、 B+ 树与 B 树(平衡树)的区别
B 树非叶子节点保存数据,B+树非叶子节点只保存关键数字
B 树某些节点可能在叶子节点不会出现,B+树所有的节点都会在叶子节点出现
B+树叶子节点保存所有的节点,并且是有序的,有两个好处: 3.1、查询效率稳定,几乎登录 O(logN) 3.2、方便范围查询,范围查询效率也高(B 树还需回查父节点实现)
2、聚簇索引
mysql 在设置主键时,以主键为键创建聚簇索引,没有主键时,则使用第一个非空唯一索引作为聚簇索引。如果既没有设置主键也没有设置唯一索引,则 MySQL 会创建一个不可见的 ROW_ID 作为键的聚簇索引。 注意:没有主键,则会进行全局扫描,所以查询效率比较低。而且 ROW_ID 是全局共享的,所以在写数据的时候会有锁竞争,影响插入效率。**自增主键用完了的情况:1、设置了主键会报重复主键的错误 2、没有设置主键(MySQL 会默认使用共享的 ROW_ID)会从 0 开始,覆盖旧数据。
3、一个复合索引最最多支持 16 列,总长度为 256k
4、sql 脚本优化
主要在以下几方面
建立索引,是查询能够走索引- #索引失效的情况
select 查询结果列中包含在索引中
在 where 条件中 如果有索引与非索引的字段,不要使用 or 可以使用 union all 代替
区分 in 与 exists ,in 先制定字查询(适合内表大,外表小)exists 先执行内表,所以适合内表小,外表大
分页查询时 是页号数尽量变小,可以先用条件过滤
join 使用时注意小表驱动大表
where 条件 赛选多的放在靠后面
5、索引失效的情况
先要了解索引的构成,导致失效的原因: 索引是一颗多分支的有序树(复合索引时,每个节点保存多个键,顺序保存),所以要想索引生效,就必须要能够清楚的走那边的分支(类似二分法查找)。
使用 like 查询时 如果是 % 开头的失效(无法确定开头的大小,无法确定走索引树的分支)
使用不等号<>(不等号可以是大于也可以是小于,所以两边分支都可以走,无法确定)
使用计算函数(可能导致于原有的值大小顺序改变)
在索引列上计算(无法确定与原有列值大小比较)
转换(字符串转数字-->数据库中是数字类型,条件是字符串,走索引),数字转字符时索引会失效(类型不一致,无法比较大小)
组合索引 不遵循最左前缀原则(是按照复合索引的顺序进行建立的树,所以如果没有前面的列,无法找到对应的分支)mysql 8.0 之后没有使用最左匹配原则也有可能使用索引【索引跳跃式扫描】,引擎会进行优化,重写原来的 sql,会对第一个索引字段去重,然后添加上第一个字段,使用 union all 链接 起来等
6、数据库事务
数据库中的事务主要是指一系列的操作要满足 ACID(原子性,一致性,隔离性,持久性)这四个特性。 原子性: 一个操作要么全部成功,要么全部失败。mysql 执行一个修改时的操作为,先写 buffer pool 修改--->刷盘,此时会出现两种情况: - 事务提交了没刷盘:此时就需要 redo log 进行回滚,mysql 在刷盘前会先写一个 redo log(顺序写入磁盘 速度很快) - 刷盘了事务没提交(事务回滚):undo log 进行撤销(修改数据时事务没提交),undo log 会记录一条反向操作的脚本。
持久性:主要保证是通过 binlog 日志, 隔离性:隔离性实现是 MVCC 机制 一致性:主要是指事务的前后,数据库的完整性限制没有被破坏,主要分为两部分 - 约束一致性:主要有建表是指定的外键,唯一性约束等等 - 数据一致性:主要是有前面原子性,持久性,隔离性三个特性一起保证。WAL(write ahead log):日志先写,有三个特性都是依靠的日志先写的思想实现的。
7、集群
主从模式: 主从复制模式主要用于实时灾备、故障切换、读写分离,实现原理为 binlog 日志,主库生成对应的 binlog 日志,从库的 IO Thread 同步主库的 binlog 到本地,写入到 replay log 中,然后由 sql Thread 线程解析 replaylog 日志,然后再从库中执行。 双主模式: 双主双写(互为主从):存在的问题 ID 冲突,在使用数据库自增 id 时会有冲突(解决方案,使用步长),更新丢失,同一条记录在两个库中更新时,后面的更新会覆盖前面的更新记录。 双主单写:相对双写性能要低一些,但是没有更新丢失的问题。
评论