数据库——MySQL笔记

数据库：MySQL笔记

文章目录

• 数据库：MySQL笔记
• • 基础
  • 索引和SQL优化
  • • 索引
    • 慢查询
  • 原理
  • • 日志
    • 事务&锁&MVCC
    • • 事务
      • 锁
      • MVCC
      • 小结
      • 疑问：MVCC还是next-key锁解决幻读？
    • 存储和读写：todo
  • 集群
  • • 主从复制/读写分离
    • 分库分表
  • 设计规范
  • refer

基础

1. 数据模型
2. 数据库完整性
3. 三级模式/两级映像
4. 三范式
5. 存储引擎
6. 数据库设计流程

索引和SQL优化 索引

1. 类型

2. 结构

3. 使用建议

4. 失效场景

   1. where进行null判断：采用0、-1等默认值
   2. where使用!=和<>操作符：运算符或union
   3. where使用or连接：union
   4. 使用in和not in：between and或exists
   5. like ‘%…’：全文检索或like '胡%'
   6. where使用参数，如select id from t where num = @num
   7. where的索引列，进行函数、表达式：对比较值进行表达式、函数，索引值比较

MySQL索引如何优化

慢查询

1. 开启慢查询日志，抓取慢SQL
2. explain分析执行计划
3. show profile查询sql在服务器的执行细节和生命周期
4. 服务器参数调优

mysql优化之——慢SQL分析

mysql优化之——explain分析

原理 日志

1. 错误日志
2. 查询日志
3. 慢查询日志
4. 事务日志：redo log、undo log
5. 二进制日志：binary log
6. 中继日志

事务&锁&MVCC 事务

1. 定义
2. 特性
3. 并发事务的问题：
   1. 写写：丢失更新
   2. 读写：脏读 不可重复读 幻读
4. 并发事务的解决：
   1. 丢失更新：应用层加锁
   2. 脏读 不可重复读 幻读：事务隔离机制
5. 事务隔离级别：RU、RC、RR、S

说明：并发控制解决方案，有锁机制和多版本并发控制

1. 锁机制：用于处理写-写、读-读。即X锁和S锁
2. MVCC：用于处理读-写。即快照读和当前读

数据库事务与MySQL事务总结

锁

传统的锁：共享锁、排他锁

粒度：行锁、表锁

意向：IX、IS

---

四级封锁协议

隔离级别	读	写
RU		X
RC	S，读完释放	X
RR	S，至事务提交释放	X
S	X	X

说明：X，至事务提交释放

---

两阶段协议：是一种能保证事务可串行化的协议。将事务的获取锁和释放锁划分为增长和缩减两个不同的阶段：

1. 增长阶段：一个事务可以获取锁，但不能释放锁
2. 缩减阶段：只可以释放锁，而不能获取新的锁

MVCC

如何实现：每行记录后保存两个隐藏列，一个记录创建版本、一个记录删除版本；然后通过快照读和当前读解决读写问题（写不阻塞读）

MVCC只在RC、RR隔离级别下起作用

实际设计：

隐藏列	说明
DB_TRX_ID	最近一次修改它的事务id
DB_ROLL_OIR	指向回滚段undo日志的指针
DB_ROW_ID	写入数据时，自动维护自增列

ReadView：基于事务ID、回滚指针的可见性比较算法（比较id），生成ReadView

MVCC案例

小结

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)

1. 场景一的第二次查询（快照读，读取第一次select的ReadView）不到，无幻读
2. 场景二的update（当前读，生成新ReadView），第二次查询就可以读到，有幻读

那如何解决呢？程序自己控制，语句加锁（至事务结束释放），即查询使用select for update 快照读，并且可以阻塞其他事务插入id=2的数据。但是可能造成死锁

存储和读写：todo 集群 主从复制/读写分离

主从形式：一主一从、一主多从、多主一从、双主复制、级联复制…

原理：三个线程（master#binlog线程、slave#io和sql线程）

同步方式：异步复制、全同步复制、半同步方式

[主从搭建]

问题：

1. 同步延迟查不到数据？分库减小并发压力、并行复制、查也走主库…
2. 主宕机导致数据丢失？半同步复制

分库分表

为什么需要分库分表：

1. 并发高/连接多
2. 单库大
3. 单表大

分库分表方案：todo

1. 垂直
2. 水平

分库分表后的问题：

1. 主键：分布式ID
2. 事务：XA两阶段提交、最终一致性…
3. 跨节点分页、排序、分组：搜索引擎
4. 跨库join：避免、修改少的使用全局表、依赖字段少且冗余数据的一致性要求低使用字段冗余、业务层组装、ER分片…

中间件/产品：todo

1. client类型 sharding-jdbc
2. proxy类型 mycat

迁移和扩容：停机迁移、双写迁移

设计规范

MySQL高性能优化规范建议

refer

MySQL索引原理及慢查询优化

MySQL并发控制下的|事务|MVCC|锁机制|解读

浅析MySQL并发控制：隔离级别、锁、MVCC

MySQL分库分表方案

MySQL分库分表会带来哪些问题

大众点评订单系统分库分表实践

数据库知识个人总结