面试题-数据库

  view

1.delete与truncate区别
    truncate是DDL语句(create、drop、alter、truncate)，delete是DML语句(insert、update、delete、select)；DCL语句(grant、revoke)
    truncate的速度快于delete
    delete数据可以进行rollback进行数据回滚，truncate是永久删除不能回滚
    truncate不会触发表上的delete触发器，而delete会正常触发
    truncate语句不能带where条件意味着只能全部数据删除，而delete可带where条件进行删除
    truncate操作会重置表的高水位线，而delete不会

2.冷备份和热备份的不同点及优缺点
    热备份针对归档模式的数据库，在数据库仍旧处于运行状态时进行备份。
    冷备份是指在数据库关闭后，进行备份，适用于所有模式的数据库。
    热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任一时间点。而冷备份的优点在于备份和恢复相当简单，并且由于冷备份的数据库可以工作在非归档模式下，数据库性能会比归档模式稍好。
3.分页语句
    oracle：select * from (select rownum rn, name from employee where rownum <= 20) temp where temp.rn > 10;
    mysql: select * from employee limit 10,10;--第一个10是起始数，第二个是是每页条数
4.sql优化
    1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by 涉及的列上建立索引
    2.应尽量避免在where子句中对字段进行null判断,可以设默认值为0，判断是否为0
    3.应尽量避免在where子句中使用!=或<>操作符
    4.应尽量避免在where子句中使用or来连接条件，可以使用union all
    5.in和not in也要慎用，能用between就用，或可以使用exist和not exist代替
    6.模糊查询时，前模糊也会导致索引失效
    7.应尽量避免在where子句中对字段进行表达式或函数操作
    8.在使用索引字段作为查询条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统引用该索引，否则索引将不会使用，并且应尽可能的让字段的顺序与索引顺序相一致
    9.不要写一些没有意义的查询，如where 1=1
    10.并不是所有索引对查询都有效，SQL是根据表中的数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能就不会去利用索引，如sex字段
    11.索引并不是越多越好，索引固然可以提高相应的select效率，但同时也降低了update及insert的效率。一张表的索引数最好不要超过6个
    12.尽量使用数字型字段
    13.尽可能的使用varchar代替char，因为首先变长字段存储空间小，可以节省存储空间，其次相对于查询，在一个相对小的字段内搜索效率显然高点
    14.用具体字段代替*
    15.避免频繁创建和删除临时表，以减少系统表资源的消耗
    16.临时表并不是不可用，适当地使用他们可以使某些例程更有效，如：当需要重复引用大型表或常用表中的某个数据集时，但是对于一次性事件，最好使用导出表
    17.在新建临时表时，如果一次性插入数据量很大，那么可以使用select into 代替create table，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，再insert
    18.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，再drop table，这样可以避免系统表较长时间锁定
    19.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1W行，那么应该考虑改写
    20.使用基于游标的方法或临时表的方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效
    21.与临时表一样，游标并不是不可用。对于小型数据集使用FAST_FORWARD游标通常优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时
    22.尽量避免使用大事务操作，提高系统并发能力
    23.尽量避免向客户端返回大数据，若数据量过大，应当考虑相应需求是否合理
5.oracle查询所有叶子结点
    select node from (select t.node, connect_by_isleaf lf from tree t start with t.parent = -1 connect by prior t.node = t.parent) where lf = 1
    connect_by_root:返回当前节点的最顶端节点
    connect_by_isleaf:判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
    level：伪列表示节点深度
6.oracle树查询(start with)
    select xxx from 表明 where 条件3 start with 条件1 connect by prior 条件2 (参考题5)
    条件1:表示从哪个节点开始查找，也就是通过条件1查到的数据作为后续查询的起始节点。如果省略start with就默认把所有满足条件的tree整个表中的数据从头到尾遍历一次，每一个数据做一次根，然后遍历树中其他节点信息
    条件2:是连接条件，其中用prior表示上一条数据，prior在父节点一侧，自底向上查询，在子节点一侧，自上向下查询。connect by子句说明每行数据是按照层次顺序检索，并规定将表中的数据连入树形结构的关系中。
    条件3:不能用在connect by后，这里的条件判断等价于在最后查询出结果列表之后，再进行条件筛选，并非删除节点及子节点
7.Hibernate中对象的三种状态
    ①.临时态:就是说该实体对象只是暂时存在，通过new出来的对象为临时态。通过调用session的delete方法所删除的实体对象为临时态。临时态的特征是可以被垃圾回收，未与session关联的，未进行持久化的对象
    ②.持久态:即将该实体对象已经在数据中持久化的对象。通过查询方法get、load、list等获取的对象为持久态。通过save、update并commit的对象也是持久态。持久态的特征是不能被垃圾回收，与session关联并可以通过session.flush()更新到数据库。
    ③.游离态:即游离在缓存中的实体对象。主要是已经通过hibernate一级缓存管理操作如evict、clear、close方法操作了对象。游离态的特征是可以被垃圾回收，已经进行过持久化操作，但已经与session解除关联。
8.为什么使用数据索引能提高效率

  • 数据索引的存储是有序的。
  • 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的。
  • 极端情况下，数据索引的查询效率为二分法查询，趋近于㏒2(N)。
    9.B+树索引和哈希索引的区别
        B+树是一个平衡的多叉树，从根节点到每一个叶子节点的高度差值不超过1，而且同层次的节点间有指针相互链接，是有序的。
        哈希索引是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可，是无序的。
    10.哈希索引的优势
        等值查询，哈希索引具有绝对优势(前提是：如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题)
    11.哈希索引不适用的场景
  • 不支持范围查询
  • 不支持索引完成排序
  • 不支持联合索引的最左前缀匹配规则
        通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引更具优势：
            在HEAP表中，如果存储的数值重复度很低(也就是说基数很大)，对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引。
        而常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况。
        如果认为建立哈希索引可以提高查询效率，则自动在内存中的”自适应哈希索引缓冲区”建立哈希索引(在InnoDB中默认开启自适应哈希索引)。
        通过观察者模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。
        
        但是某些时候，在负载高的情况下，自适应哈希索引中添加的read/write锁也会带来竞争，比如高并发的join操作。like操作和%的通配符也不适用于自适应哈希索引，可能要关闭自适应哈希索引。
    11.B树和B+树的区别
  • B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null，叶子节点不包含任何关键字信息。
  • B+树，所有的叶子节点中包含全部关键字信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。所有的非终结点可以看成是索引部分，结点中仅含有其子树根节点中最大(或最小)关键字。(而B树的非终结点也包含需要查找的有效信息)
    12.为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？
        1.B+的磁盘读写代价更低。
            B+的内部节点并没有指向关键字具体信息的指针，因此其内部结点相对B数更小。
            如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
        2.B+-树的查询效率更加稳定。
            由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根节点到叶子节点的路。所有关键词查找的路径长度相同，导致每一个数据的查询效率相当。
    13.MySQL联合索引
        1.联合索引是两个或更多个列上的索引。
            对于联合索引：MYSQL从左到右的使用索引中的字段，一个查询可以只使用索引中的一部分，但只能是最左侧部分。如：索引是key_index(a,b,c)，可以支持a、a,b、a,b,c，3种组合进行查找，但不支持b,c进行查找。当最左侧字段是常量引用时，索引就十分有效。
        2.利用索引中的附加列，可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。
            复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先会按姓氏进行排序，然后按名字对有相同姓氏的人进行排序。如果只知道姓，电话簿将非常有用；如果知道姓和名，电话簿更为有用；但如果只知道名不知道姓，电话簿将没有用处。
    14.什么情况下应不建或少建索引
        1.表记录太少
        2.经常插入、删除、修改的表
        3.数据重复且分布均匀的表字段，假如一个表有10W行数据，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建立索引一般不会提高数据库的查询速度。
        4.经常和主字段一块查询，但主字段索引值比较多的表字段。
    15.什么是表分区
        表分区是指根据一定规则，将数据库中的一张表分解成多个更小的、容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。
    16.表分区和分表的区别
        分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间分成多个表。
        分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张。
    17.表分区有什么好处
        1.存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据。
        2.优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
        3.分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。
        4.避免某些特殊的瓶颈。例如InnoDB的单个索引的互斥访问，ext3文件系统的inode锁竞争等。
    18.分区表的限制因素
        1.一个表最多只能有1024个分区。
        2.MYSQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MYSQL5.5中提供了非整数表达式分区的支持。
        3.如果分区字段中有主键或者唯一索引的列，那么主键列和唯一索引列都必须包含进来，即：分区字段要么不包含主键列或者索引列，要么包含全部主键和索引列。
        4.分区表中无法使用外键约束。
        5.MYSQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表中的一部分数据分区。
    19.MYSQL支持的分区类型有哪些？
        RANGE分区：这种模式允许将数据划分不同的范围。例如可以将一个表通过年份划分成若干个分区。
        LIST分区：这种模式允许系统通过预定义的列表的值来对数据进行分割。按照list中的值分区，与RANGE分区的区别是，RANGE分区的区间范围是连续的。
        HASH分区：这种模式允许通过对表的一个或多个列的HASH Key进行计算，最后通过这个HASH码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
        KEY分区：上面的HASH模式的一种延伸，这里的HASH Key是MYSQL系统产生的。
    20.行级锁定的优缺点
        优点：
            1.当在许多线程中访问不同的行时只存在少量锁定冲突。
            2.回滚时只有少量的更改。
            3.可以长时间锁定单一的行。
        缺点：
            1.比页级或表级锁定占用更多的内存。
            2.当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
            3.如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
            4.用高级别锁定，通过支持不同的类型锁定，你可以很容易地调节应用程序，因为其锁成本小于行级锁定。
    21.key和index的区别
        key是数据库的物理结构，它包含两层意义和作用，一是约束(偏重于约束和规范数据库的结构完整性)，二是索引(辅助查询)。包含primary key、unique key、foreign key等。
        index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间(mysql中的innoDB表空间)以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等。
    22.mysql中MyISAM和InnoDB的区别有哪些？
        1.InnoDB支持事务，MyISAM不支持。对于InnoDB每一条SQL语句都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语句放在begin和commit之间，组成一个事务。
        2.InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败。
        3.InnoDB是聚集索引，数据文件和索引是绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此主键不应过大，因为主键太大，其它索引也都会很大。
        而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
        4.InnodDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。
        5.InnoDB不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高。
        如何选择：
  • 是否要支持事务，如果要请选择InnoDB，如果不需要可以考虑MyISAM;
  • 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读 写也挺频繁，请使用InnodDB;
  • 系统奔溃后，MyISAM恢复起来困难，能否接受;
  • MYSQL5.5版本开始InnoDB已经成为MYSQL的默认引擎(之前是MYISAM)，说明其优势是有目共睹的，如果不知道用什么，那就用InnodDB，至少不会差。