玩命加载中 . . .

Mysql面试题

mysql 面试题
mysql
发布日期:   2020-09-20
文章字数:   3.8k
阅读时长:   14 分
阅读次数:  

Mysql面试题

  1. 数据库索引介绍一下。介绍一下什么时候用Innodb什么时候用MyISAM这两种引擎。 
索引是帮助数据库高效获取数据的排好序的数据结构，使用索引可快速访问数据库表中
的特定信息。
InnoDB主要面向在线事务处理（OLTP）的应用。MyISAM主要面向一些OLAP的应用。
InnoDB是聚集索引（叶子节点包含了完整的数据记录），支持事务，支持行级锁；MyISAM是非聚集索引（叶子节点只包含数据磁盘地址），不支持事务，只支持表级锁。
InnoDB必须有主键：作为B+树的索引
建议使用整型自增主键：整型是因为作为索引操作要多次进行比大小，整型比大小的效率更高，自增是因为b+树的叶子节点是有顺序的，插入索引的时候，自增会直接在b+树原有的叶子节点后面插入，而非自增在插入数据时，还需要B+树重新排序，影响性能，自增就减少了排序的步骤

  1. Innodb和MyISAM引擎在磁盘存储是什么样子的

    MyISAM:存放3个文件，frm:建表信息，myd：数据信息，myi：索引信息
Innodb:存放2个文件，frm:建表信息，ibd：数据信息、索引信息

  2. 什么是聚集索引，什么是非聚集索引

    b+树的数据结构中，叶子节点会存放有data元素
（MyISAM存的是数据磁盘地址，InnoDB存的是数据）
索引中存放有其他列的数据就是聚集索引
存放的是数据磁盘地址，即数据和索引分离，就是非聚集索引

  3. 什么是联合索引

    联合索引是多个索引组成的，B+树会先按最左原则进行排序，当索引相同时，再按下一个索引排序。

  4. 索引了解嘛，底层怎么实现的，什么时候会失效 

Hash索引和B+ 树索引
>其实还有二叉树、红黑树，因为二叉树都往右叶子节点加的时候，就会成为链表，和全表查询一样。
>红黑树也叫平衡二叉树，虽然它可以平衡节点，让不成为链表，但是等数据多的时候，树会越来越高，导致读取会进行多次iO操作，一样效率会低

>Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于范围查询是无法直接通过索引查询的，所以，哈希索引只适用于等值查询的场景。
而B+ 树是一种【多路】平衡查询树（它可以由多个根节点），可以降低树的高度，叶子节点包含所有索引字段，还将data元素（MyISAM存的是数据磁盘地址，InnoDB存的是数据）放在叶子节点，用于读取数据，并且叶子节点拥有双向指针，用于提高范围查找性能，他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。建议自增变量，主要是在所有的叶子结点中增加了指向下一个叶子节点的指针。

一些关键字会导致索引失效，例如or，！=，notin，isnull,isnotunll
联合索引中，不使用第一部分作查询
like查询是以%开头
隐式转换会导致索引失效。
or前后没有同时使用索引
对索引应用内部函数，索引字段进行了运算。

  1. 索引对数据库系统的负面影响是什么？

    创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

  2. 为数据表建立索引的原则有哪些？

    在最频繁使用的、用以缩小查询范围的字段上建立索引。

在频繁使用的、需要排序的字段上建立索引

  3. 主键、外键和索引的区别？

    定义：
主键–唯一标识一条记录，不能有重复的，不允许为空
外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值
索引–该字段没有重复值，但可以有一个空值

作用：
主键–用来保证数据完整性
外键–用来和其他表建立联系用的
索引–是提高查询排序的速度

个数：
主键–主键只能有一个
外键–一个表可以有多个外键
索引–一个表可以有多个唯一索引

  4. sql优化

1.在表中建立索引，优先考虑where、group by使用到的字段
2.尽量避免select *，返回无用字段会降低效率
3.避免使用in和not in和or和模糊查询和null值判断和where表达式，会导致引擎放弃索引查询而进行全表查询
  1. 数据库的隔离级别
1.未提交读(ReadUncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据。
2.提交读(ReadCommitted)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别(不重复读)。
3.可重复读(RepeatedRead)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读。
4.串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻
塞。

脏读:    指一个事务读取了另外一个事务未提交的数据
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同
虚读（幻读）：指一个事务读取到了别的事务插入的数据，导致前后读取不一致

  1. 数据库乐观锁和悲观锁

    悲观锁（PessimisticLock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
synchronized就属于悲观锁的一种实现，每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被block。

乐观锁:顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
实现乐观锁的两种方式：
数据版本（Version）记录机制实现
时间戳（timestamp）

  2. 数据库的三范式

    第一范式（1NF）：强调的是列的原子性，即列不能够再拆分。
第二范式（2NF）：首先是 1NF，实体的每个非主键属性完全函数依赖于主键属性（**消除部分依赖**）
第三范式（3NF）：首先是 2NF，另外非主键列必须**直接**依赖于主键，**不能存在传递依赖**

  3. ACID的特性

    1.原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2.一致性指事务前后数据的完整性必须保持一致。
3.隔离性指多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。
4.持久性是指一个事务一旦提交，它对数据库中数据的改变就是永久性的，即便数据库发生故障也不应该对其有任何影响。

  4. 隔离性所造成的问题：

    脏读:    指一个事务读取了另外一个事务未提交的数据
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同
虚读（幻读）：指一个事务读取到了别的事务插入的数据，导致前后读取不一致

  5. 主从复制

    主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。
MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。
MySQL主从复制的两种情况：同步复制和异步复制(使用偏多)

  6. leftjoin和rightjoin的区别？

    leftjoin(左联接)返回包括左表中的所有记录和右表中联结字段相等的记录

  7. 数据库优化方法

    1.选取最适用的字段属性
2.使用连接（JOIN）来代替子查询(Sub-Queries)
3.事务

  8. 谈一下你对继承映射的理解

    继承关系的映射策略有三种：
①每个继承结构一张表（tableperclasshierarchy），不管多少个子类都用一张表。
②每个子类一张表（tablepersubclass），公共信息放一张表，特有信息放单独的表。
③每个具体类一张表（tableperconcreteclass），有多少个子类就有多少张表。

  9. 数据连接池的工作机制

    服务器启动时会建立一定数量的池连接，并一直维持不少于此数目的池连接。客户端程序需要连接时，池驱动程序会返回一个未使用的池连接并将其表记为忙。
如果当前没有空闲连接，池驱动程序就新建一定数量的连接
当使用的池连接调用完成后，池驱动程序将此连接表记为空闲，其他调用就可以使用这个连接。

  10. JDBC中如何进行事务处理

    Connection提供了事务处理的方法，通过调用setAutoCommit(false)可以设置手动提交事务；当事务完成后用commit()显式提交事务；如果在事务处理过程中发生异常则通过rollback()进行事务回滚。

  11. JDBC的反射，反射都是什么

    通过反射com.mysql.jdbc.Driver类，实例化该类的时候会执行该类内部的静态代码块，该代码块会在Java实现的DriverManager类中注册自己
DriverManager管理所有已经注册的驱动类，当调用DriverManager.getConnection方法时会遍历这些驱动类
并尝试去连接数据库，只要有一个能连接成功，就返回Connection对象，否则则报异常

  12. Statement和PreparedStatement有什么区别？哪个性能更好？ 

    PreparedStatement接口代表预编译的语句，它主要的优势在于可以减少SQL的编译错误并增加SQL的安全性（减少SQL注入攻击的可能性）
PreparedStatement中的SQL语句是可以带参数的，避免了用字符串连接拼接SQL语句的麻烦和不安全；
当批量处理SQL或频繁执行相同的查询时，PreparedStatement有明显的性能上的优势，由于数据库可以将编译优化后的SQL语句缓存起来，下次执行相同结构的语句时就会很快

  13. 分页语句：取出sql表中第31到40的记录（以自动增长ID为主键）

    select * from t order by id limit 30,10；

  14. 根据部门号从高到低，工资从低到高列出每个员工的信息。

    select * from employee order by deptid desc,salary;

  15. 用JDBC查询学生成绩单，写出主要代码

    //加载驱动
Class.forName(driverClassName);
//获取连接
Connection cn = DriverManager.getConnection(url,username,password);
//编写sql语句
String sql = "select score.* from score,student where score.stuId = student.id and student.name = ?";
//执行sql
PreparedStatement pstmt = cn.prepareStatement(sql);
//设置参数
pstmt.setString(1,"张三");
//获取结果集
Resultset rs = pstmt.executeQuery();
//遍历结果
while(rs.next()){
    System.out.println(rs.getString(rs.getFloat("socre")));
}
//关闭连接
rs.close();
pstmt.close();
cn.close();

  16. 链表查询

    class:cid、caption
student:sid、gender、class_id、sname
score:sid、student_id、course_id、num
course：cid、cname、teacher_id
teacher:tid、tname
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、 查询没有报李平老师课的学生姓名
4、 查询选修物理课程和体育课程其中一门的学生姓名
5、 查询挂科超过两门(包括两门)的学生姓名和班级
6、找出同时选了李平老师所有课的学生班级和姓名

1.    select course.cname,teacher.tname 
    from course,teacher 
    where course.teacher_id=teacher.tid
    
2.  select student.sname,AVG(score.num) 
    from student,score 
    where student.sid = score.student_id
    group by score.student_id
    having avg(score.num)>80
class:cid、caption
student:sid、gender、class_id、sname
score:sid、student_id、course_id、num
course：cid、cname、teacher_id
teacher:tid、tname
3. select student.sname from student where sid not in (
    select student_id from score where course_id in (
        select course.cid from course,teacher where tacher.tname ="李平老师")
    )
    
4.  select student.sname 
    from student
    where sid in (
        select score.student_id from score where course_id in (
            select cid from course where cname in ("物理","体育"))
        group by student_id --在没有聚合函数的情况下使用group by，表示该列的重复属性合并（去重效果）
        having COUNT(student_id) = 1 
    )
    --方式2
    select sname 
    from student 
    where sid in (
        select student_id 
        from score inner join course 
        on score.course_id = course.cid 
        where course.cname in("物理","体育")
        group by student_id
        having count(student_id) = 1
    )

    
5.  select caption,sname 
    from class inner join (
        select sname,class_id 
        from student 
        where sid in (
            select score.student_id 
            from score 
            where num < 60  
            having count(score.student_id)>=2 
        )
    )as k on class.cid = k.class_id

  17. Inner join 和left join ,right join哪个效率高

```
Inner join，它只查询出交集，left join查询除了交集还有左边没有返回的数据
left join已经确定了拿左边表为参照表，Inner join会自动选择数据量最小的表作参照表

连接查询为什么比子查询效率高？

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。
```
  1. 联表查询JOIN介绍
```sql
内连接：INNER JOIN
SELECT * FROM book INNER JOIN book_type ON book.type_id=book_type.type_id;
等同于 ：如果两个字段名字相同，可以是使用using(column_name)来代替On
select * from book inner join book_type using(type_id);
自然连接：NATURAL JOIN:自然连接就是USING子句的简化版,它找出两个表中相同的列作为连接条件进行连接
select * from book natural join book_type;
```
文章作者: 小苏
文章链接: https://fenghen0918@github.io/2020/09/20/mysql/mysql-mian-shi-ti/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 小苏 !
mysql 面试题
赏
评论
  目录