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

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

#### 存储管理 ####

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

#### 缓存机制 ####

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

#### SQL解析 ####

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

#### 日志管理 ####

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

#### 权限划分 ####

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

#### 容灾机制 ####

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

#### 索引管理（重要） ####

*  为什么要使用索引？  
    在搜索数据量较大的表，避免全表扫描，减少IO访问量。
 *  什么样的信息能创建索引？  
    如唯一键或有一定区分度的数据字段
 *  索引的数据结构  
    1、二叉查找树  
    缺点是：删除或新增之后，树容易退化，树的深度会很大，io次数无法得到优化  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center]  
    2、B树  
    缺点是：每个结点必须存储真实数据行，导致每个页（一个页大小是固定的，一般为16KB）存储的数据量较少，查询IO数较B+树大。  
    3、B+树  
    非叶子结点只存储索引，不存储数据，只有叶子结点存储数据。那样每个非叶子结点的页存储的索引数会多很多，这样树的高度会较矮，磁盘读写代价更低。所有叶子结点均有一个指针指向下一个叶子结点，可以横向跨子树统计，在范围查询更有优势。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 1]  
    4、hash索引（mysql不显式支持）  
    hash索引不适合范围查找，仅满足“=”，“in”。  
    hash索引不适合排序  
    hash索引当Hash值相等数据较多会退化
    
    5、位图索引（mysql不支持）  
    当数据类型固定时，适合用位图索引  
    缺点：锁的范围较大，不适合并发高的场景  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 2]
 *  密集索引和稀疏索引  
    密集索引是每条数据对应一个索引，索引的叶子结点存了一行数据。  
    稀疏索引的叶子结点只存了索引值和唯一键（主键）。  
    在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多次读取是一样的，通过快照读实现。  
    串行化：只能有一个事务同时对一条数据操作，第二个事务必须等第一个事务提交才能操作，这样脏读，不可重复度，幻读都不存在了  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 3]
 *  InnoDb可重复读隔离级别下如何避免幻读？  
    快照读和当前读：  
    当前读表示数据每次读取的都是最新的数据，如select…in share mode,select…for update,这些都是当前读。  
    快照读表示数据读取的数据是快照的内容，可能不是最新的。  
    *还没想通，待更新…*
 *  RC、RR级别下的InnoDb的非阻塞读如何实现  
    mysql每个数据行会有隐藏字段，如DB\_TRX\_ID(行被更新的事务ID),DB\_ROLL\_PTR（执行undo日志的指针，用于数据回滚）,DB\_ROW\_ID（当前行的唯一ID）  
    *还没想通，待更新…*

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center]: /images/20210518/99b3712405834264834c40f71a97c684.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210518/6860e95165524de6871528077666b023.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20210518/5ce5ce983b2446b397fa612e9ecf5740.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20210518/964c425def72410e871c00a703ce803e.png