数据库相关问题

 如何设计一个关系型数据库（RDBMS）？
索引相关
为什么要使用索引？
什么样的信息能成为索引？
索引的数据结构？
密集索引和稀疏索引的区别？
如何定位并优化慢查询SQL？
联合索引的最左匹配原则的成因？
索引是建立的越多越好吗？
MyISAM和InnoDB关于锁方面的区别？
MySIAM的适用场景？
InnoDB的适用场景？
锁的分类？
数据库事务的四大特性？ACID
事务隔离级别以及各级别下的并发访问问题？
InnoDB可重复读隔离级别下如何避免幻读？
 
 
 如何设计一个关系型数据库（RDBMS）？
  • 存储（文件系统）（机械硬盘，SSD固态硬盘）
  • 程序实例：存取文件系统中的数据
    • 存储管理：数据的逻辑关系转化成物理的存储关系；尽可能的减少IO，不能逐行查找，一次性读取多行。（数据库使用块或者页存取）
    • 缓存机制：优化执行效率；某行数据被访问，他周边的数据极有可能也被访问。（如LRU）
    • SQL解析：SQL语句解析
    • 日志管理：记录操作，用于主从同步和灾难恢复，binlog方式
    • 权限划分：控制数据访问权限
    • 容灾机制：处理异常情况，数据库挂了怎么恢复，恢复到什么程度
    • 索引管理：提升查询数据的速度
    • 锁管理：   让数据库支持并发
 
 
 
索引模块
常见问题
  • 为什么要使用索引
    • 快速查询数据，少量数据也可用全表扫描
  • 什么样的信息能成为索引
    • 主键，唯一键以及普通键等
  • 索引的数据结构
    • 二叉查找树：若插入数据的顺序如下图所示，会导致时间复杂度变成线性，对二叉查找树做旋转操作改造成平衡二叉树可以解决此问题
    • 平衡二叉树：数据库数据块非常多，二叉树只有两个子节点，树的层数过深，每一层都需要执行一次IO，效率很低
    • 红黑树
    •  B树： 关键字+指向孩子的指针（多少个孩子取决于数据块的容量以及数据库的相关配置），目的（每个节点尽可能多的存储信息，让树的高度尽可能减少IO次数） https://www.cnblogs.com/xqzt/p/4456746.html 
      • 根节点至少包含两个孩子
      • 树种每个节点最多含有m（层数）个孩子（m>=2）
      • 除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子
      • 所有叶子节点都位于同一层
      • 假设每个非终端节点中包含n个关键字信息，其中：
        • Ki(I=1…n),且关键字按顺序升序排序K(i-1)<Ki （关键字数值左边比右边小）
        • 关键字的个数n必须满足：[ceil(m/2)-1] <= n <= m-1 （关键字个数比孩子节点数量少一个）
        • 非叶子节点的指针：P[1],P[2]…,.P[M]，其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大雨K[M-1]的子树，其他P[i]指向关键字属于(K[i-1],K[i]) 的子树。（叶子节点3和叶子节点5比节点8的数值小）
    • B+树（MySQL索引实现）：B树的变体
      • 非叶子节点的子树指针与关键字个数相同（5，10，20）个数 = （P1，P2，P3）
      • 非叶子节点的子树指针P[i]，指向关键字值[K[i],K[i+1]]的子树 ,（关键字10叶子节点的值必须小于关键字20）
      • 非叶子节点仅用来索引，数据都保存在叶子节点中（非叶子节点不存储数据，可以存储更多的关键字，也就意味着层树更矮）
      • 所有叶子节点均有一个链指针指向下一个叶子节点（方便在叶子节点做数据范围统计）
    • B树和B+树结论，B+树更适合做存储索引：https://www.cnblogs.com/diegodu/p/8463150.html
      • B+树的磁盘读写代价更低（非叶子节点不存储数据，大小更小，关键字更多）
      • B+树的查询效率更加稳定（数据都在叶子节点，每一次扫描必须通过相同层数，查询更加稳定）
      • B+更有利于对数据库的扫描（范围查询使用链指针更加有效率）
    • Hash结构
      • 缺点：哈希运算之后的值是无序的
        • 仅仅能满足“=”，“IN”，不能使用范围查询
        • 无法被用来避免数据的排序操作
        • 不能利用部分索引建查询
        • 不能避免表扫描
        • 遇到大量Hash值相等的情况后性能并不一定比B+索引高（hash冲突）
    •  
    • BitMap 位图索引： 字段取值只有固定的几个值
      • 数据库支持有限，Oracle支持
      • 缺点
        • 锁的粒度非常大，发生新增或者修改时锁定范围大，不适合高并发的联机处理系统场景
    •  
  •  密集索引和稀疏索引的区别
    • 密集索引文件中的每个搜素码值都对应一个索引值（一个表只能创建一个密集索引）
    • 稀疏索引文件只为索引码的某些值建立索引项
    • MySIAM都是稀疏索引
    • InnoDB有且仅有一个密集索引
      • 若一个主键被定义，该主键则作为密集索引
      • 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
      • 若不满足以上条件，innodb呢哦不回生成一个隐藏主键（密集索引6字节的列）
      • 非主键索引存储相关键位和其对应的主键值，包含两次查找
 
 
衍生出来的问题，MySQL为例
  • 如何定位并优化慢查询SQL
    • 根据慢日志定位慢查询SQL
      • show variables like '%quer%’; 
      • set global slow_query_log = on; 打开慢查询日志
      • Set global long_query_time = 1; 查询大于1秒属于慢查询
    • show status like '%slow_queries%’; 记录慢查询sql语句
    • 使用explain等分析工具分析SQL
      • explain select * from walle_admin.system_config where name = 'singleLogin';
        • 关注type：
        • 关注extra
    • 修改SQL或者尽量让SQL走索引
      • explain select * from walle_admin.system_config where name = 'singleLogin' force index (primary); 强制走某索引
  • 联合索引的最左匹配原则的成因
  • 索引是建立的越多越好吗
    • 数据量小的表不需要建立索引，简历会增加额外的索引开销
    • 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
    • 更多的索引意味着需要更多的空间
 
 
 
锁模块   
  • MyISAM和InnoDB关于锁方面的区别
    • MyISAM默认表级锁，不支持行级锁
    • InnoDB默认行级锁，也支持表级锁
    • 自动提交：show variables like 'autocommit’; set autocommit = 0;# 关闭自动提交
    • 手动添加共享锁：select * from user where id =1 lock in share mode;
    • InnoDB在SQL没有用到索引时，用的是表级锁，用到索引时用的是行级锁
 
                配置文件默认内容：https://www.fromdual.com/mysql-configuration-file-sample
  •  mysql --verbose --help | grep my.cnf mysql读取的位置
  • /usr/local/mysql/support-files 默认my.cnf存储的位置     
  •      MySIAM适用场景
    • 频繁执行全表count语句
    • 增删改的频率不高，查询频繁
    • 没有事务
  •      InnoDB适用场景
    • 数据增删改比较频繁
    • 可靠性高，支持事务
                
  • 数据库事务的四大特性 ACID
    • 原子性（Atomic）：所有操作要么全做，要么全不做
    • 一致性（Consistency）：数据库数据的含义应满足完整性约束（转账场景，总金额始终不变）
    • 隔离性（Isolation） ：多个事务并发执行时，一个事务的执行不影响其他事务的执行
    • 持久性（Durability）：DBMS的恢复性能，redolog
  • 事务隔离级别以及各级别下的并发访问问题
    • 事务并发访问引起的问题以及如何避免 
      • 更新丢失 - MySQL所有事务隔离级别在数据库层面上均可避免
      • 脏读 - READ-COMMITED事务隔离级别以上可避免，只能读到其他事物已经提交的记录（Oracle默认隔离级别）
        • 手动设置最低的事务隔离级别：set session transaction isolation level read uncommitted ;
      • 不可重复读 - REPEATABLE-READ以上的事务隔离级别即可避免，多次读取同一事务时结果不一致。
        • 查询数据库默认事务隔离级别：select @@tx_isolation; 
        • set session transaction isolation level repeatable read ;
      • 幻读 -SERIALIZABLE 事务隔离级别即可避免，MySQL的REPEATABLE-READ也可避免
        • Session1 查询3条记录（未提交事务）
        • Session2 插入1条记录（未提交事务）
        • Session1 更新全部记录+1，提示有4行收影响，产生了幻觉
  • InnoDB可重复读隔离级别下如何避免幻读
    • REPEATABLE-READ隔离级别可以避免幻读
    • 表象 ：基于伪MVCC机制的快照读（非阻塞读）
      • 当前读: 数据上的共享锁或排他锁都是当前读 ，读取的是记录的最新版本。
        • select …lock in share mode
        • Select … for update
        • update
      • delete
      • insert
    • 快照读：不加锁的非阻塞读
      • select
    • 内在： next-key锁（行锁+gap锁）
      • 行    锁
      • Gap锁 ：锁定记录相邻的一个范围，但不包括记录本身
        • 对主键索引或者唯一索引会用Gap锁吗？
          • where条件全部命中，不会用gap锁，只会加记录锁  
      • where条件部分命中或者全不命中，则会加Gap锁，例如更新id=9的数据，insert id=10的数据会被block住
    • Gap锁会用在非唯一索引或者不走索引的当前读中
posted @ 2019-11-09 16:31  天蓝隐湘  阅读(171)  评论(0)  编辑  收藏  举报