数据库——MySQL笔记

数据库

数据库基础

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分析执行计划

   select_type：查询类型，有简单查询、联合查询、子查询等

   key：使用的索引

   rows：扫描的行数

mysql优化之——慢SQL分析

mysql优化之——explain分析

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%'

原理

日志

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

redo两阶段提交

读写

数据以页为单位

磁盘——缓冲池——内存数据

事务&锁&MVCC

事务

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

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

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

数据库事务与MySQL事务总结

锁

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

粒度：行锁、表锁

意向：IX、IS

---

三级封锁协议：

1. 一级封锁协议：事务T，修改时加X，T结束释放锁
2. 二级封锁协议：一级基础上，读取时加S，读取完释放锁
3. 三级封锁协议：一级基础上，读取时加S，T结束释放锁

隔离级别	读	写
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

一致性读：一致性非锁定读——快照读、锁定读——当前读

如何避免部分幻读？RR隔离级别下，一致性非锁定读场景，只能读取第一次查询前（生成ReadView）所插入的数据，避免幻读；若是当前读，最新数据，无法避免幻读，这就需要加next-key lock，防止其他事务在间隙插入数据

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的数据。但是可能造成死锁

集群

主从复制/读写分离

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

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

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

[主从搭建]

问题：

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

分库分表

为什么需要分库分表：

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

条件：库100G、表100、子段200、单表500W行

分库分表方案：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分库分表会带来哪些问题

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

数据库知识个人总结