数据库面试题整理

1、什么是事物？

    被绑定在一起作为一个逻辑单元sql语句组，如果有任何一个语句操作失败，那么整个操作都会失败，然后回滚到操作之前的状态。

    数据库事物的特性及含义：

      原子性：整个事物的所有操作，要么执行，要么全都不执行

      一致性：事物开始前和事物结束后，数据的完整性约束没有被修改

      隔离性：如果有两个事物，运行在相同的时间内，执行相同的功能，事物的隔离性将确保每一个事物在系统中认为只有该事物在使用。这种属性有时为串行化，使得同一时间只有一个事物作用于数据

     持久性：在事物执行完成之后，该事物对数据库的修改将持久的保存在数据库中，不会被回滚。

2、数据库当中的索引，以及索引的实现方法有哪些？使用索引有哪些规则？

    索引类型分为：普通索引、唯一索引、主键索引、全文索引。

    索引的实现通常是B树或者B+树

    使用索引的优点

    1、加快数据的检索速度，这也是索引创建的主要原因

    2、可以加快表与表之间的连接，特备是在实现数据的参考完整性方面

    3、使用分组和排序子语进行检索，同样可以减少排序和分组的时间

    4、使用索引，可以在查询时使用优化隐藏器，提供系统性能。

   当然使用索引也会有一定的缺点

   1、创建索引和维护索引会消耗时间，随之数据量的增加而增加。

   2、索引需要占用物理空间，如果要建立聚簇索引，需要的空间更大

   3、对表中的数据进行增加，修改和删除时，也需要对索引进行修改维护，这就降低了对数据维护的速度。

   在哪些列上面适合建立索引

   1、选择where, on，group by ,order by 中出现的列作为索引，常用的列放在前面

   2、在经常需要搜寻的列上，创建索引

   3、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构

   哪些列不适合建立索引

  1、不要对参与计算的列创建索引，因为计算会使索引失效

  2、对于定义为text, image, bit类型的列不建议使用索引

  3、当修改性能远远大于检索性能时，因为创建索引是为了加快查询，会降低频繁修改列的速度。

3、如何进行SQL优化

   1、对于sql的优化，应该尽量避免进行全表扫描，对于where，order by 等列建立索引

   2、sql当中应该尽量避免使用到会使索引失效的写法，比如以下方式

        2.1、判断某列是否为null, 这种情况就会使索引失效，尽量对于null的列，设定默认值

        2.2、条件判断的时候，尽量不要用 != 或者 <>

        2.3、条件查询时候，尽量不要使用 or, 使用 union all 的方式来进行查询

        2.4、对于 in 和 not in 也尽量少用，如果是在某个数字范围区间的话，尽量使用between(a，b)

        2.5、尽量避免在where条件等号左边使用表达式，比如：select num from table where num/2=100 尽量改为 select num from table where num = 200

        2.6、尽量避免使用like进行模糊查询，

   3、关于limit分页的优化

        select id from table limit 90000,10 这个执行速度是很慢的

        修改方案：

        select id from table order by id limit 90000,10 添加id索引

        select id from table order by id between 90000,900010

    4、使用exist 代替in 

        select * from A where id in (select id from B) 替换为

        select * from A where id exists  (select id from B where A.id = B.id)

        in 的执行逻辑，我们使用伪代码的方式来看下

List resultSet={};

    Array A=(select * from A);
    Array B=(select id from B);

    for(int i=0;i<A.length;i++) {
            for(int j=0;j<B.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }
    return resultSet;      

在使用in 的时候，如果A表中的数据时1000，B表当中的数据是10000，相当于是循环遍历了1000 * 10000次，所以当B表当中的数据量比较大的时候，不适合使用in的方式来遍历。主要用到的是A表的索引。

      exists在使用的时候，遍历的次数是A表的长度，exists关键字的实现伪代码如下

 List resultSet={};
    Array A=(select * from A);
    for(int i=0;i<A.length;i++) {
       if(exists(A[i].id) {  //执行select 1 from B where B.id=A.id是否有记录返回
           resultSet.add(A[i]);
       }
    }
    return resultSet;

从A表当中逐条获取到记录，然后判断B表当中是否存在，这种情况下遍历的次数其实是A表的长度。

可以得出结论，如果A表长度比较大，B表长度比较小时，使用in 会比较好，如果A表长度小，B表长度大，使用exists会比较好。

    5、避免频繁的创建和删除临时表

    6、使用varchar来代替char, 使用数据类型代替字符串类型，如果为字符型的话，在创建索引和关联表查询时会造成性能下降

    7、对于数据量级别比较高的查询，如果是两张表关联，可以先对数据量比较大的一张表的主键进行order by limit，然后再进行关联，而不是直接先关联再limit查询。

  4、如何找出慢sql

      在mysql当中开启以下配置项

      show_query_log 显示查询sql日志

      long_query_time 超过这个字段设置的时间，就会记录到sql日志当中

      show_query_log_file 查看记录sql日志的文件

      log_queries_not_using_indexes 设置这个参数，可以找到没有使用索引的sql

5、说一下数据库引擎InnerDB和MyISAM

      关于InnerDB,有以下一些特性   

1、支持事务处理、ACID事务特性

2、实现了SQL标准的四种隔离级别

3、支持行级锁和外键约束

4、可以利用事务日志进行数据恢复

5、不支持FullText类型的索引，没有保存数据库行数，计算count(*)需要全局扫描

6、支持自动增加列属性auto_increment

7、最后也是非常重要的一点：InnerDB是为了处理大量数据时的最大性能设计，其CPU效率可能是其他基于磁盘的关系型数据库所不能匹敌的。

8、采用的是聚集索引

MyISAM引擎

1、快速读取，如果频繁插入和更新的话，因为涉及到数据全表锁，效率并不高

2、保存了数据库行数，执行count时，不需要扫描全表；

3、不支持数据库事务；

4、不支持行级锁和外键；

5、不支持故障恢复。

6、支持全文检索FullText，压缩索引。

7、采用非聚集索引

6、limit 20000查询速度很慢如何优化

    1、添加嵌套子查询，在子查询中找出分页的最大id,然后外层查询添加 id大于内层查询值

    2、如果查询条件较多，创建合理的组合索引

7、DDL, DML, DCL分别指什么

     DDL(Data Definition Language) : 其主要命令是create, alter，drop,主要是用来定义或者改变表的结构

     DML(Data Manipulation Language):比如select, update, insert ,delete操作数据库当中数据的命令

     DCL(Data Controll Language): 数据库控制功能，比如对权限的修改，设置数据库用户角色

8、左连接，右连接，内连接

     左连接是以左边表的全量查询，根据删选条件进行查询结果

     右连接是以右边表的全量查询，再加上筛选条件

      内连接查询是查询出左右表当中满足查询条件的查询信息

9、执行计划每个列都代表什么含义

执行计划是在需要查询的sql前面添加explain

  

 id: 如果是子查询id就逐渐增加

 select_type 主要是来区分普通查询、联合查询、子查询和复杂查询

       SIMPLE: 简单查询，查询当中不包括union, 或者子查询

       Primary:查询中包含任何子查询，最外层标记为primary

       Subquery: 在select或者where当中包含了子查询

       Derived:在from里面包含的子查询

       Union: 在第二个select出现之后，被标记为union

       UnionResult：从union结果当中获取数据

 type:访问类型，sql优化的重要指标，从好到坏的一个顺序

        system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

     一般来说sql查询至少要达到range, 最好能达到ref

    const：通过索引一次就找到，因为只匹配一条数据，所以比较快

    eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。 

    ref：非唯一性索引扫描，返回匹配某个单独值的所有行。

    range:只检索给定范围的行，使用一个索引来选择行。

    index:index与ALL区别为index类型只遍历索引树

   PossibleKeys

        查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

   Key

        如果查询中出现了覆盖索引，则该索引仅出现在key列表当中

  Key_len

        表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的

  Ref

       显示索引的那一列被使用了，如果可能，是一个常量const

  Rows

       根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

  Extra

       using index :使用了覆盖索引，效率比较高

       using where: 只有索引无法定位，还需要where条件

       using temporary: 使用了临时表，

       using filesort: 使用了文件排序

       range checked for each record：没有发现好的索引

10、什么是索引覆盖

       指如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再找数据。这种查询速度非常快。

11、mysql优化

       使用慢日志查询：1.检查慢查询日志是否开启；2.记录没有使用索引的查询；3.记录查询时间超过0.5秒的sql。

       分析有问题SQL：1.分析慢查询日志(查询次数多时间长sql、没有命中索引sql、io大的sql)；2.使用explain执行计划。

       SQL优化：1.count(*)→count(id);2.子查询→join;3.group by→子查询;4.避免列上进行计算;5.LIKE模糊查询避免%%;6.避免使用NULL。

       索引优化：1.where、group by、order by、on后出现的列;2.索引字段尽量小;3.离散度大的索引放在联合索引的前面;4.使用覆盖索引。

       数据库结构优化：1.尽量使用简单的数据类型(少使用大数据类型);2.尽量使用not null;3.表的水平拆分和垂直拆分。

       覆盖索引：指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要回表查询。

       show profile:分析性能，查询各个sql执行时间、每个sql详细时间耗费和cpu、io消耗信息。

       show processlist:显示有哪些线程在运行、当前所有的连接数、通过当前的连接状态识别有问题的查询语句。

12、count(*),count(1),count(field)有什么区别

       count(*)是对不为null的行进行计数，因此某一行只要不是所有列都为null，就会被计数。count(*)自动会优化指定到某一个字段。

       count(field)是对field列不为null的行进行统计，因此某一行的该列为null，则不予计数。

       count(1)和count(*)都是统计表的总行数，两者执行结果相同。表上没有主键或者唯一键索引，两者都走全表扫描；表上有主键或者唯一键索引，那么走主键或者唯一键

13、为什么使用自增列作为主键

     在InnerDB引擎当中采用的是聚集索引，聚集索引当中数据的存储顺序和主键索引的顺序一致，并且数据库当中的数据保存在叶子节点当中，每当进行一次插入操作时，会根据主键将其放在合适的位置，由于主键是自增的，在插入时，只需要将新增的数据放在原来数据的后面即可，每个叶子节点写满之后，会开辟新的叶子节点写入，如果采用的是非自增的主键，每次插入数据时，几乎等同于随机，这样在插入时，需要计算出来插入的位置，可能是中间的某个位置，这样的话，就需要移动其他叶子节点上数据的位置，变动也比较频繁。

14、B+树和hash索引的区别

     B+树是一个多叉平衡树，从根节点到叶子节点的高度值不超过1，而且同一层节点之间是指针相互连接的，并且有序，hash索引就是采用hash算法，将key值，转化成hash值，检索时，不需要类似B+树那样从根节点到叶子节点逐级查找，只需要一次hash算法即可，是无序的。

15、hash索引的优势及不适合的场景

优势就是进行等值查询时会很快，前提是没有大量的重复索引，如果有重复键值时，哈希索引的效率会很低，不适合范围查找，不支持索引排序，不支持联合索引的最左前缀匹配规则。

InnerDB存储引擎默认使用的是B+索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引会提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（InnerDB中默认开启自适应哈希索引），通过观察搜寻模式，MySQL会利用index key的前缀建立哈希索引，如果一个表的大部分都在缓冲池中，那么建立一个哈希索引可以加快等值查询。

16、B树和B+树的区别

     B树每个节点都存储key和data,所有节点组成这棵树，并且叶子节点指针为null, 叶子节点不包含任何关键信息

     B+树 所有叶子节点包含全部关键字信息，以及指向关键字记录的指针，并且叶子节点之间从小到大关联排序。

17、什么是表分区，分区有什么好处，和分表有什么不同

      分区是从逻辑上将还是一张表，只不过将表中的数据存储在了不同位置，分表是一张表拆分成了多个表，分区将数据存储在了多个不同的物理设备上，高效的利用了磁盘效率，查询优化，where条件当中包含分区信息时，可以只扫描一个或者多个分区来加快查询速度，涉及到count,sum等计算时，可以在各个分区上面并行处理，然后再汇总统计，分区更容易维护，批量删除数据时，如果数据都集中在某个区上面，可以直接删除某个分区，

18、分区有哪些限制因素

      1、一个表最多1024个分区；2、如果分区字段中有主键或者唯一索引的列，那么主键和唯一索引列必须包括进来，3、分区表中无法使用外键约束 4、mysql的分区适用于一个表的数据和索引，不能只对数据分区，不对索引分区，也不能只对索引分区，不对数据分区。

   

 

未完~持续更新中。。。。