Sql数据库原理（B+树）及相关问题

• 1、数据库范式：对数据库规范化设计，减少数据冗余，增加数据一致性
  • 第一范式：列不可分、无重复列，eg:【联系人】（姓名，性别，电话），电话还可以细分，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF；
  • 第二范式：有主键，保证完全依赖。eg:订单明细表【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName），Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID，不符合2NF；
  • 第三范式：无传递依赖(非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况)，eg:订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。
  • BCNF范式和第四范式
• 2、磁盘读取
  • 磁盘读取数据I/O：寻找磁道+旋转选择
  • 预读+局部性原理：
    • 页存储模型：磁盘I/O读取非常慢，根据数据预读原理，当磁盘一个数据被查找使用，计算机将顺序读取一定长度的数据放入内存（页的整倍数，一般为16K，innoDB其中一页同样为16k）——局部性原理：当一个数据被用到时，其附近的数据通常也马上会被用到；
    • 页的构成：
      • 最小虚记录和最大虚记录：页存储数据范围
      • 记录堆，索引数据存储区域
      • slot区：页面有效指针，存储记录相对页面首地址的偏移（快速定位到数据在那个Slot中进行快速检索）
      • 页尾：页面校验信息
    • InnoDB存储结构：
      • 段：数据段、索引段、回滚段
      • 区：连续页组成，1MB，一个区64页，InnoDB一次会申请4-5个区
      • 页：InnoDB最小的存储单位，16K
      • 行：面向行的引擎
• 3、索引：建立索引就是要进行排序（ 叶子节点：数据；非叶子几点：主键id+指针———8b+6b=14b）
  • B+数高度=2，1条数据为1kb，一页16kb，1个叶子节点可以存放16条数据。一个非叶子节点16kb/14b=1170；1170*16=18724条数据 ；
  • B+高度=3，1170*1170*16=21907740条。大于2千万条数据就需要分表，最多进行2次磁盘Io
  • innoDB的最小储存单位页 4kb（16384）
  • 页的Fil Header（指向下一页的指针和指向前一页的指针）
  • 存储一个表中的一行

    

  • Page Directory（页目录）：主键要小，自增
    • 红色部分为主键（也可以理解为页目录）主键索引

      

    • 联合索引（红色仍为主键，但其叶子节点只包含联合排序+主键。如需其他行信息需要利用主键索引进行查找）

      

• 4、数据库索引
  • JDBC连接数据库，进入连接池，SQL接口 ——解析器（编译，底层可执行代码）——优化器——缓存（热点数据缓冲）——存储引擎innoDB