Java面试-数据库

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

分为两个模块。第一是存储(文件系统)；第二是程序实例(存储管理、缓存机制、SQL解析、日志管理、权限划分、容灾机制、索引管理、锁管理)

2.索引

  2.1 为什么要使用索引

            快速查询数据，避免全表扫描

  2.2 什么样的信息能成为索引

            主键、唯一键以及普通键等

  2.3 索引的数据结构

            生成索引，建立二叉查找树进行二分查找
            生成索引，建立B-Tree(B树)结构进行查找
            ❤生成索引，建立B+-Tree结构(B+树)进行查找：数据都存储在叶子节点中；叶子节点彼此相连，方便作范围统计
            生成索引，建立Hash结构进行查找
            Question：为什么B+树更适合做存储索引？
                    Answer：
                        B+树的磁盘读写代价更低(B+树内部没有指向关键字具体信息的指针，只有索引信息，数据都放在叶子节点)
                        B+树的查询效率更加稳定(每个数据都要到叶子节点中查找数据，效率大致相同 O(logN))
                        B+树更有利于对数据库的扫描(数据都存放在叶子节点中)

  2.4 密集索引和稀疏索引的区别

            密集索引： 一系列存储块，里面有存放记录的键和指向记录本身的指针。 eg.给定一个键值k，我们先在索引块中查找k，找到k后，按照指针找到对应的记录。
                        优点：索引块数量通常比数据块数量少；由于键被排序，所以可以用二分查找k；索引文件可能足够小，可以永久存放在主存缓冲区中
             稀疏索引：为数据文件的每个存储块设一个键-指针对，只有当数据文件是按照某个查找键排序的时候，在该键上的稀疏索引才能被使用。 eg.给定一个键值，首先在索引块中找到小于或等于k的最大键值(可以用二分)，然后指向到相应数据块，搜索数据块查找键值为k的记录
                        优点： 节省了更多的存储空间
              密集索引文件中的每个搜索码值都对应一个索引值；稀疏索引文件只为索引码的某些值建立索引项
              MySQL---InnoDB：
                    若一个主键被定义，该主键则作为密集索引
                    若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
                    若不满足以上条件，innodb内部会生成一个隐藏主键(密集索引)
                    非主键索引存储相关键位和其对应的主键值，包含两次查找

  2.5 如何定位并优化慢查询SQL

            根据慢日志定位慢查询sql：
                        show variables like '%quer%';
                        设置是否打开慢查询日志slow_query_log=on；设置慢查询时间long_query_time=1
                        执行完查询语句如果时间超过1秒，则记录在慢日志中
            使用explain等工具分析sql：
                        在慢查询语句之前加入explain关键字，分析语句
                        explain关键字段：
                            type：如果出现了index或者all，可能就需要优化了
                            extra：
                                Using filesort：对结果在内存或磁盘上进行排序，无法利用索引排序
                                Using temporary：对查询结果排序时使用临时表，常见于order by和group by
             修改sql或者尽量让sql走索引：
                        可以给列加index索引

  2.6 联合索引的最左匹配原则的成因

            最左前缀匹配原则，mysql会一直向右匹配知道遇到范围查询(>、<、between、like)就停止匹配。
            mysql会对联合索引最左边的字段进行排序，在这个基础上，对第二个索引字段进行排序。
            (一般第一个索引字段绝对有序，第二个索引字段无序，所以要对第二个字段进行条件判断，用不到索引)

  2.7 索引是建立的越多越好吗

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

3.锁

  3.1 共享锁和排它锁的兼容

            共享锁：读锁(S锁)，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
            排他锁：写锁(X锁)，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
            MySQL InnoDB引擎的update、insert、delete语句会自动给涉及到的数据加上排它锁，select语句默认不加任何锁。
            MySQL MyISAM引擎的update、insert、delete语句会自动给全表上写锁，select语句会给全表上读锁。
            select ... for update 加排它锁
            select ... lock in share mode 加共享锁
            X + X = 冲突(上了排它锁就不能上其他锁)
            X + S = 冲突(上了排它锁就不能上其他锁)
            S + X = 冲突(排它锁不能与其他锁共存)
            S + S = 兼容

  3.2 MyISAM/INooDB的区别

            MyISAM默认表级锁(只支持表级锁)，InooDB默认行级锁(也支持表级锁)。
            MyISAM适合场景：
                频繁执行全表count语句(MyISAM会把表的count属性放进一个变量中)；
                对数据进行增删改的频率不高，查询非常频繁(因为增删改会锁表)；
                没有事务
            InooDB适合场景：
                数据增删改查都相当频繁；
                可靠性要求比较高，要求支持事务

  3.3 锁的分类

            表级锁、行级锁、页级锁
            共享锁、排它锁
            自动锁(insert、update、delete)、显式锁(for update、lock in share mode)
            DML锁(处理数据的时候进行加锁)、DDL锁(更改表结构的时候加锁)
            乐观锁：会在提交数据的时候，对数据的冲突与否进行检测，如果冲突了，返回给用户错误的信息，让用户决定如何做(不会利用数据库提供的锁机制)。
                一般实现方式：记录数据版本 --> 1)记录数据版本号(增加version列字段，每次更新成功加1)  2)使用时间戳
            悲观锁：每次数据操作都会上排它锁，悲观并发控制也是先取锁后访问(利用数据库本身的锁机制)，额外开销大，效率较慢，增加产生死锁的机会。

  3.4 数据库事务的四大特性

            原子性(事务包含的操作要么全部执行，要么全部回滚)
            一致性(A+B=2000，无论AB怎么操作，最后相加的数据都是2000)
            隔离性(多个事务并发执行时，一个事务的执行不应该影响其他事务的执行)
            持久性(恢复已提交事务的更新)

  3.5 事务隔离级别以及各级别下的并发访问问题

    3.5.1 脏读

                事务读取到了其他事务未提交的数据
                解决：提高事务隔离级别read committed

    3.5.2 不可重复读

                 一个事务多次重复读取某行数据，可以得到的结果是不一样(因为有其他事务在更新数据)，如果对旧数据进行操作，会出现问题
                解决：提高事务隔离级别repeatable read(INooDB默认隔离级别)

    3.5.3 幻读

                事务A执行了当前读操作，事务B在这个范围内，插入了一条数据，事务A再次进行当前读操作，会出现更新行数不正确的问题
                解决：提高事务隔离级别serializable(这个级别下，每个操作都会默认加读锁/写锁)

  3.6 InooDB可重复读隔离级别下如何避免幻读

            当前读(加了锁的增删改查)：select...lock in share mode/for update、update、delete、insert
            快照读(不加锁的非阻塞读)：select
            表象：快照读 -- 伪MVCC
            内在：next-key锁(行锁+gap锁)
            注：gap锁(间隙锁)，锁定一个范围但不包括记录本身，防止同一事务的两次当前读出现幻读的情况；Gap锁会用在非唯一索引或者不走索引的当前读中
            Question：对主键索引或者唯一索引会用Gap锁吗？
            Answer：
                如果where条件全部命中，则不会用Gap锁，只会加记录锁
                如果where条件部分命中或者全不命中，则会加Gap锁(锁的是一个范围)

  3.7 RC、RR级别下的InnoDB的非阻塞读如何实现

            数据行里的DB_TRX_ID(最新一次对本行记录做修改的事务的标识符)、DB_ROLL_PTR(回滚指针)、DB_ROW_ID(行号)
            undo日志(对数据进行变更操作就会产生undo记录，记录旧版本数据)
            read view(判断当前事务能看到的是哪个版本的数据)
            注：RC下快照读每次都会生成一个新的快照，所以数据都是最新；RR下，会记录历史版本，会选择第一次快照读的数据