# Mysql ## 数据库设计 ### 范式 #### 范式1: 原子性 #### 范式2: 消除局部依赖 AB->C A->C #### 范式3: 消除传递依赖 A->B B->C #### BCNF范式: 消除主属性的局部和传递依赖,AB->C, AC->B ### ER图 #### 1:n, n:1, n:n ### InnoDB 表空间 #### 组成 1. 表空间是存放页面的池子 2. 为了利用局部性原则,页面组织成区,区划分成组 3. 段是逻辑概念,将相同目的页面组织起来,包括一些零散页面和完整的区 #### 区的类型 1. 空闲的区: 加入FREE链表 2. 有空闲页面的碎片区: 加入FREE_FRAG链表 3. 没有空闲页面的碎片区: 加入FULL_FRAG链表 4. 附属于段的区: 类似有FREE链表,NOT_FULL链表,FULL链表 #### 段 1. 每个索引有两段,叶子节点一个段,内节点一个段 2. 每个段对应一个INODE Entry,存储段有关属性 3. 表空间有专门页面存储INODE Entry,组织成两个链表,SEG_INODE_FULL和SEG_INODE_FREE ## 索引 ### B+树 ### 访问方法 #### 单表索引 ##### const * 主键索引,唯一二级索引 ##### ref * 普通二级索引 ##### range * 若干单点扫描区间,范围扫描区间 ##### index * 查询结果和查询条件皆在索引中 ##### all * 全标扫描 #### 多表索引 ##### 连接的原理 Nested-Loop join 1. 根据成本选取驱动表和被驱动表 2. 先查询表 3. 对驱动表的每一条记录,查询被驱动表 ##### 基于块的循环嵌套连接, Block Nested-Loop join * 被驱动表需要全表扫描, 取若干条驱动表记录放入缓冲区,批量与被驱动表的记录匹配 ### 数据聚集方式 #### 聚簇索引 * 数据和主索引存储在一起 #### 非聚簇索引 * 数据单独存储,索引中记录数据的引用 ### 索引成本分析 #### 成本类型 1. I/O成本: 读取页面到内存,成本为1 2. CPU成表:读取和检查记录是否符合条件,成本为0.2 #### 单表查询成本分析 ##### 步骤 1. 找到所有索引 2. 计算全标扫描成本 3. 计算不同索引的查询成本 4. 选择成本最低的方案 ##### 查询相关数据 ###### index drive: 直接访问索引获得 1. 计算扫描区间。一个区间当作一个页面的I/O成本 2. 需要回表的记录数。对每个扫描区间,找到最左与最右记录。 记录少则精确计算,记录多则算每个页面的平均记录*横跨的页面数 页面数可以通过页面的父节点计算 ###### 索引统计数据 * 单点区间过多,(IN 搜索条件) 回表数=根据统计数据计算记录的平均重复次数*单点区间数 #### 连接查询成本 ##### 计算公式 * 驱动表查询成本+驱动表扇出值*驱动表查询成本 ##### 对于内查询 1. 选择最优表连接顺序 2. 为驱动表和被驱动表选择最低成本访问方法 ### 查询执行计划 Explain #### 列 1. table: Explain每条记录对应每个单表的访问方法 2. id: sql中的每个select对应着一个id 3. select_type: 每个select小查询在大查询的角色 4. type: 每个表的访问方法 5. possible_keys, key: 可能用到的索引和实际用的索引 6. key_len: 用到: 索引列用到的最大存储空间 7. rows: 成本计算时预计的行数 8. filter: 除形成索引扫描区间的搜索条件外,还满足其他搜索条件的比例 9. extra: 额外信息 #### optimizer trace * 查询优化器生成执行计划的过程 ### 缓存 #### buffer pool, InnoDB向操作系统申请的连续内存 ##### 缓冲页 ##### 控制块: 缓冲页信息,各链表指针 ##### Free链表: 未使用缓冲页控制块构成的链表 ##### Flush链表: 脏页控制块构成的链表 ##### LRU ###### LRU分区,热数据和冷数据 ## 事务 ### ACID #### 原子性 ##### Undo log ###### 作用:记录回滚一个操作的必须内容。 ###### 4个undo页面链表 1. 普通表insert链表 2. 普通表insert链表 3. 普通表insert链表 4. 普通表insert链表 ###### 原因 1. undate用于mvcc,不能在事务提交后立即删除 2. 系统崩溃后无需恢复临时表的内容,即无需写redo日志 ###### 崩溃恢复 1. 崩溃后从回滚段中找到活跃的undo页面链表 2. 通过页面链表获取未提交事务并回滚 #### 隔离性 ##### 并发问题 1. 脏写: 一个事务修改另一个未提交事务的值 2. 脏读: 一个事务读取另一个未提交事务的值 3. 不可重复读(读倾斜): 一个事务在不同时间点看到不同的数据 4. 幻读: 一个事务查询某些符合条件的记录,另一个事务写入,改变了查询结果 ##### 隔离等级 1. 未提交读: 防止脏写 2. 已提交读: 防止脏读 3. 可重复读: 防止读倾斜 4. 串行化: 所有并发问题 ##### MVCC 多版本控制 1. 每条记录带有事务id以及指向旧记录的指针 2. 一致性视图包含所有活跃的事务id 3. 读取记录比较记录的事务id和一致性视图的ids(活跃事务id,分配给下一个事务的max_id) 4. 小于一致性视图的最小事务id可以访问,在一致性视图中的id和大于max_id的不可访问 5. 提交读和可重复读的区别是生成一致性视图的时机,提交读在每条查询语句的生成,可重复读在事务开始前 ##### 锁 ###### 目的 mvcc是快照读,有时候业务需求不允许读取旧版本,需要锁 ###### 锁的类型 1. 共享锁 Select...lock in share mode 2. 排他锁 selct ... for uodate ###### 意向锁 * IS, IX是表锁,为了在加表锁时,快速判断表中记录是否有上锁 ###### 行级锁 1. Record lock, 只对记录加锁 2. Gap lock, 锁住记录前的缝隙,防止别的事务插入 3. Next-Key lock = Gap lock + Record lock #### 一致性 ##### 原子性,隔离性和约束协助保持一致性 #### 持久性 ##### Redo log ###### 特点 1. 体积小 2. 顺序写入 ###### 日志格式 1. 简单类型redo日志是物理日志: 简单表明页面哪些字节需要修改 2. 复杂的redo日志兼有物理日志和逻辑日志: 记录必要信息通过函数复原 ###### Mini-transaction * 一组redo日志,崩溃恢复时以不可分割的整体恢复 * 插入记录可能会影响叶子节点和内部节点,非原子性修改会破坏B+树结构 * 在一组redo日志后增加特殊类型日志作标记 ###### Log buffer 1. 日志先插入log buffer,日志按页(redo log block)保存 2. buffer空间不足,事务提交,刷新脏页等会刷盘 ###### Redo日志文件 1. redo日志在多个文件中循环 2. log sequence number表示写入的日志量 3. checkpoint_lsn可以被覆盖的日志量 ###### 崩溃恢复 1. 起点: checkpoint_lsn恢复到 2. 终点: log block header