如何设计一个关系型数据库

àì夳堔傛蜴生んèń 2021-06-10 20:39 742阅读 0赞

存储管理

逻辑数据和物理存储的映射。

缓存机制

加载的页，块会缓存，根据计算机理论里面的局部性原理，当访问一条数据的时候，接下来它周围的数据也会大概率访问到，所以在查询，索引查询都会将加载的页块缓存，以提高查询效率。

SQL解析

涉及到sql语法树的解析，和编译原理相关。另外sql解析后也会借助缓存，以提高查询效率。

日志管理

数据库的操作会保留操作日志，以便出现服务宕机、磁盘损坏等重大事故后的数据恢复。

权限划分

数据库的操作权限对不同人员是不同的，这样能保障数据的安全性。

容灾机制

容灾机制大多指的是分布式数据库，使用的方案为冗余，如主主，主从，异地多活等。

索引管理（重要）

  • 为什么要使用索引？
    在搜索数据量较大的表，避免全表扫描，减少IO访问量。
  • 什么样的信息能创建索引？
    如唯一键或有一定区分度的数据字段

  • 索引的数据结构
    1、二叉查找树
    缺点是：删除或新增之后，树容易退化，树的深度会很大，io次数无法得到优化
    在这里插入图片描述
    2、B树
    缺点是：每个结点必须存储真实数据行，导致每个页（一个页大小是固定的，一般为16KB）存储的数据量较少，查询IO数较B+树大。
    3、B+树
    非叶子结点只存储索引，不存储数据，只有叶子结点存储数据。那样每个非叶子结点的页存储的索引数会多很多，这样树的高度会较矮，磁盘读写代价更低。所有叶子结点均有一个指针指向下一个叶子结点，可以横向跨子树统计，在范围查询更有优势。
    在这里插入图片描述
    4、hash索引（mysql不显式支持）
    hash索引不适合范围查找，仅满足“=”，“in”。
    hash索引不适合排序
    hash索引当Hash值相等数据较多会退化

    5、位图索引（mysql不支持）
    当数据类型固定时，适合用位图索引
    缺点：锁的范围较大，不适合并发高的场景
    在这里插入图片描述

  • 密集索引和稀疏索引
    密集索引是每条数据对应一个索引，索引的叶子结点存了一行数据。
    稀疏索引的叶子结点只存了索引值和唯一键（主键）。
    在innodb中，主键索引就是密集索引，二级索引是稀疏索引。
    myisam使用的都是稀疏索引。
  • innodb和myisam索引的存储
    innodb：索引和数据存在一起，*.ibd文件
    myisam：索引和数据分开存，索引叶子结点存的是数据行的地址。索引存在 *.MYI文件，数据存在*.MYD文件
  • 如何定位并优化慢查询sql
    定位慢sql，mysql查看slow_query_log_file
    查看执行计划
    是否走了索引，分析索引为什么失效？
  • 联合索引的最左匹配原则的成因
    因为索引是有顺序的，联合索引会先按照左边的键进行排序，再按右边的键排序，所以如果通过右边的键进行匹配是不会走索引的。
  • 索引是建立越多越好吗？
    不是，索引可以提高查询效率，但会降低数据新增修改的效率，提高成本，同时也增加了索引的存储空间

锁管理（重要）

  • MyISAM 与 Innodb关于锁的区别
    myisam支持表锁，innodb支持表锁和行锁。
    当读取myisam引擎的表时，表会相应地加上读锁，此时对该表的所有写操作会被阻塞，但读锁不会阻塞，所以读锁又叫共享锁。而写锁是排他锁。
    Innodb不走索引时，用的是表级锁。走了主键索引，用的是行级锁。走普通二级索引，如果是唯一索引，对应的数据是一行，会根据这行的主键将行锁住。如果不是唯一索引，就是将非唯一索引对应的主键ID的间隙都加上间隙锁，避免中途数据插入产生幻读。
  • 上锁方式
    自动锁：像insert、update、delete，mysql会自动上写锁，select会上读锁
    显式锁：像select …from…where … lock in share mode;
    select … from … where … for update;就是显式上锁
  • gap锁（间隙锁）
    gap锁会用在非唯一索引或不走索引的当前读中，防止了在读取之后在主键之间的间隙插入数据，避免了幻读。
    走索引的时候，间隙锁只会锁住非唯一索引对应的主键的间隙。
    不走索引的时候，间隙锁会锁住所有间隙，类似于表锁了。
  • MyISAM 与 Innodb使用场景
    MyISAM：1、频繁全表count （因为MyISAM不支持事务，它直接用了一个字段存储表中的数据量）2、不支持事务 3、几乎不增删改
    Innodb：1、增删改查都相对频繁 2、可靠性高，支持事务
  • 数据库事务四大特性
    ACID：原子性、一致性、隔离性、持久性
  • 事务隔离级别以及并发访问问题
    前提条件：AB两个事务对同一行数据进行并发操作
    隔离级别从低到高：
    读未提交（会引起脏读，不可重复度，幻读 ）：B能看到A未提交的数据
    读已提交（oracle默认，避免了脏读，仍有不可重复度，幻读）：B只能看到A已经提交的数据，中途数据不可见
    可重复读（mysql默认，innodb默认，避免了脏读，不可重复读，但会出现幻读）：同一个session多次读取是一样的，通过快照读实现。
    串行化：只能有一个事务同时对一条数据操作，第二个事务必须等第一个事务提交才能操作，这样脏读，不可重复度，幻读都不存在了
    在这里插入图片描述
  • InnoDb可重复读隔离级别下如何避免幻读？
    快照读和当前读：
    当前读表示数据每次读取的都是最新的数据，如select…in share mode,select…for update,这些都是当前读。
    快照读表示数据读取的数据是快照的内容，可能不是最新的。
    还没想通，待更新…
  • RC、RR级别下的InnoDb的非阻塞读如何实现
    mysql每个数据行会有隐藏字段，如DB_TRX_ID(行被更新的事务ID),DB_ROLL_PTR（执行undo日志的指针，用于数据回滚）,DB_ROW_ID（当前行的唯一ID）
    还没想通，待更新…

发表评论

表情：
评论列表 （有 0 条评论，742人围观）

还没有评论，来说两句吧...

相关阅读

    相关 如何设计关系型数据库？

    本文讨论关系数据库设计相关的一些内容，涉及关系模型，表结构设计等内容，以学生选修课程讲述设计过程，在尽量讲清楚设计要领的前提下，简化设计内容。 本文基于MySQL数据库为基础

    快来打我*快来打我*/ 0 赞/ 609 阅读