数据库学习

1、如何设计一个数据库

首先将其划分为两个部分：

1. 存储部分：存储（类似文件系统）；
2. 程序实例：存储管理（将数据逻辑关系转换为数据存储关系），缓存机制（优化执行效率），SQL解析，日志管理（记录数据库操作），权限划分（多用户管理），容灾机制，索引管理（优化查询效率），锁管理（是数据库支持并发操作）

2、索引

MySql存储数据最终是以文件的形式存储到硬盘的，一般来说，在程序中使用的时候肯定要先把磁盘中的文件数据读取到内存中，所以造成程序运行的瓶颈是磁盘的IO

解决方案：

• 计算机系统的优化：当一次IO时，不光把当前磁盘地址的数据，并且把相邻的数据也读取到了内存缓冲区中
• 用一种稳定的数据结构，将每次查找数据时的磁盘IO次数控制在一个很小的数量级

1. 为什么使用索引：避免全表扫描，提高查询效率

   没有索引时，直接去读表数据存放的磁盘块，读到数据缓冲区中再查找需要的数据

   有索引时，先读入索引表，通过索引表直接找到所需数据的物理地址，并把数据读入数据缓冲区中。

2. 什么信息能够作为索引：主键，唯一键（总之就是能够提供区别信息的字段）

3. 索引的数据结构

   1. 建立二叉查找树，进行二分查找，但是在一定情况下可能会退化成一个线性表，从而大大降低查找效率

   2. m阶B树:平衡的m路查找树

      性质：1.根节点至少包含两个孩子；2.树中每个节点最多含有m个孩子（m>=2）；3.除根和叶子节点外，其余节点至少有ceil(m/2)个孩子（ceil为取上限）；4.所有的叶子节点都位于同一层

   3. B+树：

      特性：1.所有的关键字都出现在叶子节点的链表中，并且链表中的关键字是有序的；2.非叶子节点的子树的指针与关键字个数相同；3.非叶子节点仅用来索引，数据都存储在叶子节点中

      相比较而言，B+树更适合当做索引的数据结构

      • B树因为非叶子节点也存储数据，所以每个节点都要存储硬盘指针，B+树只是用来当做索引，即每个节点可以有更大的度
      • B+树每一层可以容纳的关键字个数更多，即一次性读入内存的需要查找的关键字个数越多，相对的磁盘的IO次数就降低了，磁盘读写代价降低
      • B+树为叶子节点增加了链表指针，更容易做范围查询，增删效率也更高
      • B+树查找路径是从根到叶子节点，查询效率稳定

   4. B+树索引的性能分析：

      • 一般使用磁盘的I/O次数评价索引结构的优劣
      • 预读：磁盘一般会顺序向后读取一定长度的数据（页的整数倍）放入内存
      • 局部性原理：当一个数据被用到时，其附近的数据也通常马上会被使用
      • B+树的度一般会超过100，因此树的高度h非常小，一般为3到5之间，即查找次数少
      • B+树叶子节点有顺序指针，更容易做范围查询

4. 聚簇索引和非聚簇索引

   为什么聚簇索引只能有一个：

   ​ 聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。而非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍是索引节点，只不过有一个指针指向对应的数据块。而数据的物理存储顺序只能有一个，所以聚簇索引只能有一个

   • InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

     InnoDB中：

     1. 若一个主键被定义，该主键则为聚簇索引；
     2. 若没有主键被定义，则该表的第一个唯一非空索引作为聚簇索引；
     3. 若不满足1.2.条件，InnoDB内部会生成一个隐藏主键；
     4. 非主键索引存储相关的键位和其对应的主键值，搜索需要进行两步

   • MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

5. 索引是建立的越多越好吗

   1. 数据量小的表不需要建立索引，会额外增加索引开销
   2. 数据变更需要维护索引，因此更多的索引也就意味着更多的维护成本
   3. 更多的索引需要更多的空间

6. 联合索引

   为什么使用联合索引：

   以联合索引（a,b,c）为例

   • 减少开销：相当于建立了 a，ab，abc 三个索引
   • 覆盖索引：比如:select a,b,c from table where a=xxx and b = xxx and c=xxx,那么mysql可以直接通过遍历索引获取数据，而不用去读表，从而减少了磁盘IO操作
   • 效率高：索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知

   联合索引的最左匹配原则：

   • mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and

     c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

   • =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3、锁模块

1. MyISAM和InnoDB关于锁模块的区别

   • MyISAM默认使用的是表级锁，不支持行级锁

   • InnoDB默认使用的是行级锁，也支持表级锁

     InnoDB在使用索引的时候是行级锁，针对索引加的锁；未使用索引的时候是表级锁，因为要进行全表扫描

2. 行级锁

   读写锁（共享锁/排它锁），要注意的是MySQL对select操作进行了优化，未加锁读，即非阻塞select

3. 表级锁

   1. 意向锁：加意向锁的目的是为了表明某个事务正在锁定一行或者将要锁定一行。表明加锁的意图

      • 意向共享锁(IS)：表明事务意图在表中的某一行或者某些行上设置共享锁
      • 意向排它锁(IX)：表明事务意图在表中的某一行或者某些行上设置排它锁

   2. 意向锁在什么时候使用？

      1. 在一个事务对一张表的某行添加S锁之前，它必须对该表获取一个IS锁

      2. 在一个事务对一张表的某行添加X锁之前，它必须对该表获取一个IX锁

         需要注意的是，意向锁是InnoDB自动加的，不需要用户干预

   3. 为什么这么做？

      举个栗子：

      ​ 当事务A为表的某一行加了只读锁，而不能写；之后又来一个事务B想要申请整个表的写锁，然而如果B申请成功的话，理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。而要避免这种冲突，即让事务B的申请被阻塞，直到A释放了行锁。

      数据库如何判断这个冲突？

      • 在没有意向锁的情况下：

        step1：判断表是否被其它事务用表锁锁住；

        step2：判断表中的某行或者某些行是否被行锁锁住；

        然而在这种情况下，step2是通过遍历去查询是否有行锁，效率可想而知

      • 有意向锁之后：

        事务A必须先去申请表的意向共享锁，当申请成功后才会再去申请某一行或某些行的行锁

        step1：同上；

        step2：事务B申请表的写锁时，发现表上有意向共享锁，则说明表中已经存在行锁，因此B的申请就会被阻塞，也不需要去遍历判断是否有哪一行有行锁，效率大大提升

4. 如何给select设置共享锁和排它锁

   • 共享锁：SELECT … LOCK IN SHARE MODE;
   • 排它锁：SELECT … FOR UPDATE;

5. 当前读VS快照读

   • 当前读：加了锁的crud，读取到的是数据库中的最新版本，并保证其它的并发事务不能修改当前记录

   • 快照读：不加锁的非阻塞读 select，读到的数据可能不是最新数据

     读已提交下，两种结果一致；可重复读下，两种结果可能不一致

4、事务

1. 四大特性：

   • 原子性（Atomicity）：指事务包含的所有操作，要么全部成功提交，要么全部失败回滚；

   • 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态，即事务执行前后都必须处于一致性状态；

     举个栗子：转账，事务执行之前A和B两者钱加起来是5000，无论中间经历怎样的转账，事务结束后，两者的钱加起来还是5000；

   • 隔离性（Isolation）：多个用户在并发访问数据库的时候，每个用户开启的事务都不能被其他事务干扰，多个并发事务之间相互隔离；

   • 持久性（Durability）：事务一旦被提交，对数据库的改变就是永久性的；

2. 并发访问下出现的问题及隔离级别：

   • 更新丢失：mysql所有事务隔离级别在数据库层面都可以避免

   • 脏读：读取到另一个事务未提交的数据；

     在读已提交隔离级别及以上均可避免

   • 不可重复读：在一次事务内，连续读取几次数据，出现不一致

     在可重复读隔离级别及以上均可避免，侧重于对同一数据的修改

   • 幻读：在一次事务内查询某一范围结果集，连续两次查询结果范围出现不一致

     在可序列化隔离级别下可以避免，侧重于对某一范围数据的增删