MYSQL数据库面试题

落日映苍穹つ 2022-10-30 00:33 276阅读 0赞

### 目录 ###

*   *  一：数据库事务：
     *   *  1：事务的四大特征：
         *  2：脏读，幻读，不可重复读：
         *  3： 事务的隔离级别：
         *  4：事务在每个隔离级别下存在的问题：
         *   *  4.1：读未提交解决：脏写问题：
             *  4.2：读已提交解决：脏读问题：
             *  4.3：可重复读隔离级别解决：不可重复读问题：
             *  4.4：串行化解决：幻读问题：
     *  二：索引：
     *   *  1：索引是个啥？为什么要建立索引？
         *  2：MYSQL索引的底层如何实现的？
         *  3：为什么要用B+树，B树，哈希，红黑树可以吗？
         *  4： 联合索引是如何实现的？最左原则是什么？
         *  5： 索引有哪些类型？
     *  三： MYSQL数据库的引擎：
     *   *  1：你工作中用的Mysql是哪个版本的？
         *  2：MyISAM索引与InnoDB索引的区别？
         *  3：聚集索引和非聚集索引：
     *  四：SQL优化：
     *   *  1：你了解Sql优化吗? 你在代码中如何进行sql优化的呢？
     *  五：MySQL主从复制
     *   *  1：MySQL主从复制的原理你了解吗？
     *  六： 锁机制：
     *   *  1：死锁
         *  2： 乐观锁
         *  3：悲观锁
     *  八：其他的：
     *   *  1：数据库的三范式以及反范式设计：
         *  2： 你了解关系型数据库和非关系型数据库的区别吗？哪些是关系型的数据库，哪些是非关系型的数据库？
         *  3： 索引的建立有哪些原则吗？

## 一：数据库事务： ##

### 1：事务的四大特征： ###

"""
    答： 我了解事务的四大特征，他们分别是原子性，一致性，隔离性，持久性。
    原子性：
    	一系列sql语句要么执行，要么不执行，是不可分割的，不能执行一部分。
    一致性：
    	定义是：要么是完全执行的状态，要么是完全不执行的状态。，我个人的理解是：一致性是说的业务层面的，例如银		行转账业务，一张卡减掉200元，另一张卡必须增加200元，不存在此消不彼长的情况。
    隔离性：
    	1：一个事务在执行过程中其他的事务是不可见的。 
    	2：一个事务在修改一个数据的时候另外一个事务不允许对这个数据进行修改。晚的事务会阻塞状态，等第一个事务执	行完，第二个事务解阻塞。
    持久性：
    	一旦事务提交数据就保存在数据库中，持久保存。
    """

### 2：脏读，幻读，不可重复读： ###

"""
    脏读：读到没有提交的数据。
    案例：A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。
    
    不可重复读：读到两个不一样的数据。（指的是数据内容）
    案例：A先读取数据为100，此时B事务修改该数据为200，然后提交。A再次读取数据为200。这种情况就是不可重复读。
    
    幻读：读到两个不一样的数据集。（指的是整体的数据集）
    案例：A先读取数据集一共100条数据，此时B事务增加了200条数据，然后提交。A再次读取数据，发现数据集变成了300条。这种情况就是幻读。
    """

### 3： 事务的隔离级别： ###

"""
    答：事务的隔离级别有四种：从高到底分别是：串行化（serializable）, 可重复读（Repeatable-Read）, 读已提交（Read-Commited）,读未提交（Read-Uncommited），Mysql默认的隔离级别是：可重复读（Repeatable-Read）,而SQLServer，Oracle默认的事务的隔离级别是：读已提交（Read-Commited）。
    
    串行化：A事务结束之后，B事务才能开始。
    可重复读：事务开启后，读取的数据永远是开启事务时的状态。
    读已提交：一个事务要等另一个事务提交后才能读取数据。
    读未提交：一个事务能够读取到另一个事务没有提交的数据。
    """

### 4：事务在每个隔离级别下存在的问题： ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzQxNzU3_size_16_color_FFFFFF_t_70]

#### 4.1：读未提交解决：脏写问题： ####

它的本质就是一个事务将另一个事务提交的修改操作回滚了

"""
    首先是事务 A 将数据的值修改为 data1，暂时不提交事务；
    然后事务 B 将数据的值修改为 data2，然后立马提交事务；
    事务 A 可能由于自己的业务系统出现了异常，因此进行回滚操作，将数据的值重新回滚为 data。
    """

解决方案： 加写锁。要对数据修改，必须要先获取到这行数据的写锁，否则不能修改。

而我们的读未提交就解决了脏写的问题。因为读未提交是不允许写的，只能读。在该隔离级别下，能保证事务提交之前，其他事务不能同时对这条数据进行修改。

#### 4.2：读已提交解决：脏读问题： ####

"""
    脏读，只是在读未提交的状态下发生的。在读未提交状态下，因为这个状态下能够读到另外一个事务没有提交的数据。
    案例：
    事务 A 和事务 B 同时执行，事务 A 先将数据从 data 修改为 data1，然后暂时不提交事务。
    然后事务 B 读取这一行数据，读取到值为 data1，此时事务A进行回滚，则事务B读到的就是脏数据。
    """

为了解决这个脏读的问题，数据库设计了读已提交的隔离级别。在读数据的时候，只能读到别的事务提交过后的值，对于未提交的事务对数据所做的修改操作，当前事务是无法读取到的。

在读提交的事务隔离级别下，当事务 B 去读取数据时，发现事务 A 还没有提交，因此它不能读取到 data1 这个值，只能读取到 data 这个值。

#### 4.3：可重复读隔离级别解决：不可重复读问题： ####

"""
    不可重复读问题：
    案例：（假设在读以提交隔离级别下）
    事务 A 和事务 B 同时开启事务，事务 A 先从数据库查询数据，读取到的值为 data，然后事务 A 先不提交事务。
    接着事务 B 修改数据，将数据的值从 data 修改为 data1。然后提交事务，事务A再次查询数据，得到的是data1。
    此时A两次读取的数据不一致？这就是不可重复读。
    """

为了解决这个不可重复读的问题：数据库设计了可重复读的隔离级别。

它的意思是，在同一个事务内，例如事务 A，多次从数据库读取数据时，每次读取到的值是一样的，即使在此期间有其他事务修改了这条数据的值，也不会导致事务 A 前后两次读取到的值不一样。

#### 4.4：串行化解决：幻读问题： ####

"""
    串行化问题：
    案例：（可重复读条件下）
    假设事务A 执行下面的SQL语句select * from t where id > 1， 查询到一条数据，紧接着事务B，向这t表中插入10条数据，然后提交事务，事务A再次执行sql语句：select * from t where id > 1，这次查到11条数据，这种就是出现幻读。
    """

为了解决幻读的问题，数据库有另外的一种隔离级别：串行化。

一个事务执行完，另一个事务才能执行。

## 二：索引： ##

### 1：索引是个啥？为什么要建立索引？ ###

# 索引其实是一种排序的数据结构，能够帮助我们快速的检索数据库中的数据
     # 建立索引的目的是提高查询的速度。

### 2：MYSQL索引的底层如何实现的？ ###

"""
    答： Mysql数据库使用Innodb数据库是采用的B+树。
    
    Mysql底层原理：
    1：在innodb数据库中具有页的概念，一页是16K，跟操作系统一样，采用的是局部性原理，每次至少在磁盘中读取至少16k的数据到内存中，然后再在内存中查询具体的数据。
    
    2：每一页具备页头，页目录和用户数据，页头中具有前指针和后指针，也就是B+树中叶子节点的之间的前后指针。前后指针的目的是方便进行范围查找。页目录的设计将用户数据区域分成多个模块，记录每个模块的头地址和头数据放在目录中，查找的时候，查找到这一页，利用二分查找，先查找到页目录中的目录项，根据目录项找到模块，然后在模块中查找到具体的数据。
    
    3:数据页如果有很多的话，我们就需要用索引页来记录数据页的地址，也就是我们B+树中的非叶子结点，他们存储的是叶子结点的首地址和数据页第一项的数据。
    """

### 3：为什么要用B+树，B树，哈希，红黑树可以吗？ ###

![在这里插入图片描述][20210216151551705.png]

*  1： 叶子结点是有指针的。
 *  2：叶子结点是有所有元素的，非叶子结点和叶子结点元素有冗余。
 *  3：叶子结点可以存储多个元素。

# 1： 为什么二叉树不行？
    答：二叉树随着树不断长大，树的深度不断增加，导致查询次数逐渐增加。
    # 2：为什么平衡二叉树不行？
    答：在失衡状态下要不断的调整树根，通过旋转来保持平衡非常耗时。
    # 3：红黑树为什么不行？
    答：深度的问题，导致磁盘IO次数增加。
    # 4：B树为什么不行？
    答：B树除了叶子节点外其他节点也存储数据，就会导致查询过程中，IO次数增加。
    # 5： 哈希索引为什么不行：
    答：哈希索引进行等值查询还可以，但是不能进行范围查询。

### 4： 联合索引是如何实现的？最左原则是什么？ ###

"""
    联合索引就是对多个字段建立索引。他和普通的索引在建立B+树的时候是有区别的。他的叶子节点记录的是联合索引的字段和主键值。假设我们对B，C, D字段建立索引，B+树上就只会存储B，C, D字段的值和我们的主键值id。根据联合索引查询的时候我们是先查询到这三个字段，然后再根据id进行回表查询。才真正的查询出具体的数据。
    """

"""
    最左前缀的原则：就是如果我要利用联合索引，我的查询语句中条件的第一个必须是联合索引的第一个。
    本质的原理是：建立B+树的时候，我们是根据联合索引的每个索引字段划分左右子树的。如果不给我最左边的字段，我根本不知道要查询那个子树。
    """

### 5： 索引有哪些类型？ ###

*  单列索引（普通索引， 唯一索引， 主键索引）
 *  联合索引：
 *  全文索引：
 *  空间索引：

## 三： MYSQL数据库的引擎： ##

### 1：你工作中用的Mysql是哪个版本的？ ###

"""
    答：我用的MySQL是5.6版本的，因为业务中要用到事务。mysql在5.1版本之前默认的搜索引擎是MyISAM,之后就是使用innoDB。
    """

### 2：MyISAM索引与InnoDB索引的区别？ ###

*  InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
 *  InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
 *  MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
 *  InnoDB支持外键，而MyISAM不支持。
 *  InnoDB支持事务，MyISAM不支持。
 *  Innodb不支持全文索引，而MyISAM支持全文索引。
 *  InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁。

### 3：聚集索引和非聚集索引： ###

# 聚集索引：数据存储和索引放在一起，找到索引也就找到了数据。（innodb）
    # 非聚集索引：将数据存储于索引分开结构。

## 四：SQL优化： ##

### 1：你了解Sql优化吗? 你在代码中如何进行sql优化的呢？ ###

1.在表中建立索引，优先考虑where.group by使用到的字段。
    
    2.查询条件中，一定不要使用select *，因为会返回过多无用的字段会降低查询效率。应该使用具体的字段代替*，只返回使用到的字段。
    
    3.不要在where条件中使用左右两边都是%的like模糊查询，如：
    
    SELECT * FROM t_order WHERE customer LIKE '%zhang%'
    
    这样会导致数据库引擎放弃索引进行全表扫描。
    
    优化：尽量在字段后面使用模糊查询。如下：
    
    SELECT * FROM t_order WHERE customer LIKE 'zhang%'
    
    4.尽量不要使用in 和not in，会造成全表扫描。如下：
    
    SELECT * FROM t_order WHERE id IN (2,3)
    
    SELECT * FROM t_order1 WHERE customer IN (SELECT customer FROM t_order2)
    
    优化：
    
    对于连续的数值，能用 between 就不要用 in ，如下：SELECT * FROM t_order WHERE id BETWEEN 2 AND 3
    
    对于子查询，可以用exists代替。如下：SELECT * FROM t_order1 WHERE EXISTS (SELECT * FROM t_order2 WHERE t1.customer = t2.customer)
    
    5.尽量不要使用or，会造成全表扫描。如下：
    
    SELECT * FROM t_order WHERE id = 1 OR id = 3
    
    优化：可以用union代替or。如下：
    
    SELECT * FROM t_order WHERE id = 1
    
    UNION
    
    SELECT * FROM t_order WHERE id = 3
    
    6.尽量不要在 where 子句中对字段进行表达式操作，这样也会造成全表扫描。如：
    
    select id FROM t_order where num/2=100
    
    应改为:
    
    select id FROM t_order where num=100*2
    
    7.where条件里尽量不要进行null值的判断，null的判断也会造成全表扫描。如下：
    
    SELECT * FROM t_order WHERE score IS NULL
    
    优化：
    
    给字段添加默认值，对默认值进行判断。如：
    
    SELECT * FROM t_order WHERE score = 0
    
    8.尽量不要在where条件中等号的左侧进行表达式.函数操作，会导致全表扫描。如下：
    
    SELECT * FROM t_order2 WHERE score/10 = 10
    
    SELECT * FROM t_order2 WHERE SUBSTR(customer,1,5) = 'zhang'
    
    优化：
    
    将表达式.函数操作移动到等号右侧。如下：
    
    SELECT * FROM t_order2 WHERE score = 10*10
    
    SELECT * FROM t_order2 WHERE customer LIKE 'zhang%'
    
    9.尽量不要使用where 1=1的条件
    
    有时候，在开发过程中，为了方便拼装查询条件，我们会加上该条件，这样，会造成进行全表扫描。如下：
    
    SELECT * FROM t_order WHERE 1=1
    
    优化：
    
    如果用代码拼装sql，则由代码进行判断，没where加where，有where加and
    
    如果用mybatis，请用mybatis的where语法。
    
    10.程序要尽量避免大事务操作，提高系统并发能力。
    
    11.一个表的索引数最好不要超过6个，如果索引太多的话，就需要考虑一下那些不常使用到的列上建的索引是否有必要
    
    https://blog.csdn.net/qq_27088383/article/details/93898819

## 五：MySQL主从复制 ##

### 1：MySQL主从复制的原理你了解吗？ ###

1：mysql主服务器会将写操作，写入自己的二进制日志文件中。
    2：mysql的从服务器的IO线程会连接mysql主服务器，请求读取主服务器的二进制日志指定内容。
    3：mysql的主服务器的IO线程将二进制内容读取到，复制给mysql从服务器的IO线程。
    4：mysql的从服务器的IO线程将收到的日志，写入自己的中继日志中。
    5：mysql的从服务器的SQL线程执行中继日志的mysql语句。

## 六： 锁机制： ##

### 1：死锁 ###

"""
    假设:现在有两个线程，两个锁。A线程拿到lock1,B线程拿到lock2,此时A线程需要等待B线程释放资源才能继续执行。B线程也需要A线程释放资源后才能继续执行。则两个线程出现相互等待的情况，此时便发生了死锁。
    """

### 2： 乐观锁 ###

# 乐观锁的隔离级别只能是读已提交和读未提交隔离级别下。
    # 1: 读取旧的数据
    # 2: 基于旧的数据计算新的数据
    # 3: 在旧的数据基础上，先查询一下，看看数据库数据是否存在，然后更新新数据。

### 3：悲观锁 ###

# 悲观锁是真正的锁
    操作的时候加锁，操作完释放锁。

## 八：其他的： ##

### 1：数据库的三范式以及反范式设计： ###

*  1：列不可分。
 *  2：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
 *  3：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

### 2： 你了解关系型数据库和非关系型数据库的区别吗？哪些是关系型的数据库，哪些是非关系型的数据库？ ###

"""
    关系型数据库： MYsql, SqlServer, Oracle, Sqllite, 他们统一用一种sql
    非关系型数据库：Mongodb, Redis
    """

### 3： 索引的建立有哪些原则吗？ ###

*  经常查询，不同意改，重复值比较少。
 *  联合索引的最左原则。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzQxNzU3_size_16_color_FFFFFF_t_70]: /images/20221024/a50b72d2de7340dabd4f349a8963fadd.png
[20210216151551705.png]: /images/20221024/ab0c113491e7477eaf448786b82db6a9.png