数据库
文章目录
数据库基础
- 数据模型
- 数据库完整性
- 三级模式/两级映像
- 三范式:原子列、部分依赖、传递依赖
- 存储引擎:事务、表锁行锁…
- 数据库设计流程
索引和SQL优化
索引
-
类型
-
结构
-
使用建议
-
失效场景
- where进行null判断:采用0、-1等默认值
- where使用!=和<>操作符:运算符或
union
- where使用or连接:
union
- 使用in和not in:
between and
或exists
- like ‘%…’:全文检索或
like '胡%'
- where使用参数,如
select id from t where num = @num
- where的索引列,进行函数、表达式:对比较值进行表达式、函数,索引值比较
慢查询
-
开启慢查询日志,抓取慢SQL
-
explain分析执行计划
select_type:查询类型,有简单查询、联合查询、子查询等
key:使用的索引
rows:扫描的行数
MySQL信息获取
1、当前MySQL所有进程:show processlist
2、最大缓存:show global variables like 'global max_allowed_packet'
3、进行中的事务:select * from information_schema.INNODB_TRX
4、连接数:show status like 'thread%'
原理
日志
- 错误日志
- 查询日志
- 慢查询日志
- 事务日志:redo log、undo log
- 二进制日志:binary log
- 中继日志
redo两阶段提交
读写
数据以页为单位
磁盘——缓冲池——内存数据
事务&锁&MVCC
事务
- 定义
- 特性
- 并发事务的问题:
- 写写:丢失更新
- 读写:脏读 不可重复读 幻读
- 并发事务的解决:
- 丢失更新:应用层加锁
- 脏读 不可重复读 幻读:事务隔离机制
- 事务隔离级别:RU、RC、RR、S
说明:并发控制解决方案,有锁机制和多版本并发控制
- 锁机制:用于处理写-写、读-读。即X锁和S锁
- MVCC:用于处理读-写。即快照读和当前读
锁
传统的锁:共享锁、排他锁
粒度:行锁、表锁
意向:IX、IS
三级封锁协议:
- 一级封锁协议:事务T,修改时加X,T结束释放锁
- 二级封锁协议:一级基础上,读取时加S,读取完释放锁
- 三级封锁协议:一级基础上,读取时加S,T结束释放锁
隔离级别 | 读 | 写 |
---|---|---|
RU | X | |
RC | S,读完释放 | X |
RR | S,至事务提交释放 | X |
S | X | X |
说明:X,至事务提交释放
两阶段协议:是一种能保证事务可串行化的协议。将事务的获取锁和释放锁划分为增长和缩减两个不同的阶段:
- 增长阶段:一个事务可以获取锁,但不能释放锁
- 缩减阶段:只可以释放锁,而不能获取新的锁
MVCC
如何实现:每行记录后保存两个隐藏列,一个记录创建版本、一个记录删除版本;然后通过快照读和当前读解决读写问题(写不阻塞读)
MVCC只在RC、RR隔离级别下起作用
实际设计:
隐藏列 | 说明 |
---|---|
DB_TRX_ID | 最近一次修改它的事务id |
DB_ROLL_OIR | 指向回滚段undo日志的指针 |
DB_ROW_ID | 写入数据时,自动维护自增列 |
ReadView:基于事务ID、回滚指针的可见性比较算法(比较id),生成ReadView
一致性读:一致性非锁定读——快照读、锁定读——当前读
如何避免部分幻读?RR隔离级别下,一致性非锁定读场景,只能读取第一次查询前(生成ReadView)所插入的数据,避免幻读;若是当前读,最新数据,无法避免幻读,这就需要加next-key lock,防止其他事务在间隙插入数据
小结
ACID的定义
原子性和持久性与日志的关系
并发事务的问题及如何解决
隔离级别的实现:锁和MVCC
锁:XS、粒度、意向
行锁算法:记录锁、间隙锁 gap lock、next-key锁
活锁和死锁解决
疑问:MVCC还是next-key锁解决幻读?
refer:深入理解MVCC与间隙锁
场景1:MVCC下的幻读模拟,以下场景无幻读
场景2:MVCC下的幻读模拟,以下场景有幻读
解释:RR的当前读(select for update, select lock in share mode, update等)和快照读(select)
- 场景一的第二次查询(快照读,读取第一次select的ReadView)不到,无幻读
- 场景二的update(当前读,生成新ReadView),第二次查询就可以读到,有幻读
那如何解决呢?程序自己控制,语句加锁(至事务结束释放),即查询使用select for update 快照读,并且可以阻塞其他事务插入id=2的数据。但是可能造成死锁
集群
主从复制/读写分离
主从形式:一主一从、一主多从、多主一从、双主复制、级联复制…
原理:三个线程(master#binlog线程、slave#io和sql线程)
同步方式:异步复制、全同步复制、半同步方式
[主从搭建]
问题:
- 同步延迟查不到数据?分库减小并发压力、并行复制、查也走主库…
- 主宕机导致数据丢失?半同步复制
分库分表
为什么需要分库分表:
- 并发高/连接多
- 单库大
- 单表大
条件:库100G、表100、子段200、单表500W行
分库分表方案:todo
- 垂直
- 水平
分库分表后的问题:
- 主键:分布式ID
- 事务:XA两阶段提交、最终一致性…
- 跨节点分页、排序、分组:搜索引擎
- 跨库join:避免、修改少的使用全局表、依赖字段少且冗余数据的一致性要求低使用字段冗余、业务层组装、ER分片…
中间件/产品:todo
- client类型 sharding-jdbc
- proxy类型 mycat
迁移和扩容:停机迁移、双写迁移