数据库常见面试问题
2021 年 06 月 24 日 285 8840 字 暂无评论

前言:总结一下MySQL数据库常见问题,也为了自己能够更方便回顾知识(因为自己容易忘记)。后续会更新添加。

数据库

  1. 数据库三大范式

第一范式:每个列都不可以再拆分。

第二范式:在第一范式的基础上,非主属性完全依赖于主键。

第三范式:在第二范式的基础上,非主属性只依赖于主键,不存在传递依赖。

  1. 关系型数据库和非关系型数据库的区别?

关系型数据库最典型的数据结构是表,由二维表及其之间的联系所组成的一个数据组织。

非关系型数据库严格上不是一种数据库,应该是一种数据结构化存储方法的集合,可以是文档或者键值对等。

  1. char和varchar的区别

    1. char是固定长度,varchar长度可变。varchar:如果原先存储的位置无法满足其存储的需求,就需要一些额外的操作,根据存储引擎的不同,有的会采用拆分机制,有的采用分页机制
    2. char和varchar的存储字节由具体的字符集来决定;
    3. char是固定长度,长度不够的情况下,用空格代替。varchar表示的是实际长度的数据类型
  2. 内连接、左连接和外连接?

左外连接 以左表为主表,可以查询左表存在而右表为 null 的记录。

右外连接 以右表为主表,可以查询右表存在而左表为 null 的记录。

内连接 查询左右表同时满足条件的记录,两边都不可为 null。

  1. MySQL 有哪些聚合函数?

① max 求最大值。② min 求最小值。③ count 统计数量。④ avg 求平均值。⑤ sum 求和。

  1. 说一下 MVCC

MVCC就是多版本并发控制。MVCC解决的问题是读写互相不阻塞的问题,每次更新都产生一个新的版本,读的话可以读历史版本。MVCC 是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问。MVCC是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低。虽然实现机制有所不同,但大都实现了非阻塞的读操作,写操作也只锁定必要的行。

在Mysql的InnoDB引擎中就是指在读已提交(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。

这就使得别的事务可以修改这条记录,反正每次修改都会在版本链中记录。SELECT可以去版本链中拿记录,这就实现了读-写,写-读的并发执行,提升了系统的性能。 InnoDB只查找版本(DB_TRX_ID)早于当前事务版本的数据行。

版本链

在InnoDB引擎表中,它的聚簇索引记录中有两个必要的隐藏列:

trx_id这个id用来存储的每次对某条聚簇索引记录进行修改的时候的事务id。

roll_pointer每次对哪条聚簇索引记录有修改的时候,都会把老版本写入undo log中。这个roll_pointer就是存了一个指针,它指向这条聚簇索引记录的上一个版本的位置,通过它来获得上一个版本的记录信息。

  1. 为什么要使用索引?

    1. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
    2. 可以大大加快 数据的检索速度(大大减少的检索的数据量), 这也是创建索引的最主要的原因。
    3. 帮助服务器避免排序和临时表。
    4. 将随机IO变为顺序IO。
    5. 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
  2. 数据库原理相关补充

​ 局部性原理与磁盘预读 由于存储介质的特性,磁盘本身存取就比主存慢很多,再加上机械运动耗费,磁盘的存取速度往往是主存的几百分分之一,因此为了提高效率,要尽量减少磁盘I/O。为了达到这个目的,磁盘往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

​ 由于磁盘顺序读取的效率很高(不需要寻道时间,只需很少的旋转时间),因此对于具有局部性的程序来说,预读可以提高I/O效率。

​ 预读的长度一般为页(page)的整倍数。页是计算机管理存储器的逻辑块,硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块,每个存储块称为一页(在许多操作系统中,页的大小通常为4k),主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,然后异常返回,程序继续运行。

​ 先从B-Tree分析,根据B-Tree的定义,可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的,在实际实现B-Tree还需要使用如下技巧:

​ 每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。

​ B-Tree中一次检索最多需要h-1次I/O(根节点常驻内存),渐进复杂度为O(h)=O(logdN)。一般实际应用中,出度d是非常大的数字,通常超过100,因此h非常小(通常不超过3)。

​ 而红黑树这种结构,h明显要深的多。由于逻辑上很近的节点(父子)物理上可能很远,无法利用局部性,所以红黑树的I/O渐进复杂度也为O(h),效率明显比B-Tree差很多。

综上所述,用B-Tree作为索引结构效率是非常高的。

  1. 说一聚簇索引和非聚簇索引的有什么不同?

1. 聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。

优点:聚集索引的查询速度非常的快,因为整个B+树本身就是一颗多叉平衡树,叶子节点也都是有序的,定位到索引的节点,就相当于定位到了数据。

缺点:1. 依赖于有序的数据,不是有序的数据的话,插入或查找的速度肯定比较慢。2. 更新代价大。

2. 非聚集索引即索引结构和数据分开存放的索引。叶子节点存的是键值和数据所在物理地址

优点:更新代价比聚集索引要小 。

缺点:1. 依赖于有序的数据,不是有序的数据的话,插入或查找的速度肯定比较慢。2. 可能会二次查询(回表),当查到索引对应的指针或主键后,可能还需要根据指针或主键再到数据文件或表中查询。

  1. 关于索引的各种轰炸。Mysql的索引,以及B+树与hash索引的区别,为什么不采用B树而采用B+树?B树和B+树的区别

B+树:非叶子节点不存储data,只存储索引,这样可以放更多的索引,data只存在叶子节点,这样到达叶子节点的路径查询长度都一样,使用b+树索引更加稳定。叶子节点用双向指针连接,提高区间访问的性能。B+ 树索引,底层是多路查询平衡树,节点是天然有序的(左节点小于父节点,右节点大于父节点),所以对于范围查找的时候不需要做全表扫描;

hash索引:底层是哈希表,数据存储在哈希表中顺序是没有关联的,所以他不适合范围查找,如果要范围查找就需要全表扫描,他只适合全值扫描;简单的来说就是hash索引适合等值查找,不适合范围查找。

MySQL索引使用的数据结构主要有BTree索引哈希索引 。对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,建议选择BTree索引。

MySQL的BTree索引使用的是B树中的B+Tree,但对于主要的两种存储引擎的实现方式是不同的。

  • MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其 data 域的值,然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
  • InnoDB: 其数据文件本身就是索引文件。相比MyISAM,索引文件和数据文件是分离的,其表数据文件本身就是按B+Tree组织的一个索引结构,树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引(或聚集索引)”。而其余的索引都作为辅助索引,辅助索引的data域存储相应记录主键的值而不是地址,这也是和MyISAM不同的地方。在根据主索引搜索时,直接找到key所在的节点即可取出数据;在根据辅助索引查找时,则需要先取出主键的值,再走一遍主索引。 因此,在设计表的时候,不建议使用过长的字段作为主键,也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂。
  1. 红黑树和B+树的使用场景?

    红黑树和B树应用场景有何不同?
    2者都是有序数据结构,可用作数据容器。红黑树多用在内部排序,即全放在内存中的。B树多用在内存里放不下,大部分数据存储在外存上时。因为B树层数少,因此可以确保每次操作,读取磁盘的次数尽可能的少。
    在数据较小,可以完全放到内存中时,红黑树的时间复杂度比B树低。反之,数据量较大,外存中占主要部分时,B树因其读磁盘次数少,而具有更快的速度。

    分析数据结构问题的时候,权衡三个因素: 查找速度,数据量,内存使用。

    B树(B+树)相对于平衡二叉树的不同是,每个节点包含的关键字增多了,特别是在B树应用到数据库中的时候,数据库充分利用了磁盘块的原理(磁盘数据存储是采用块的形式存储的,每个块的大小为4K,每次IO进行数据读取时,同一个磁盘块的数据可以一次性读取出来)把节点大小限制和充分使用在磁盘块大小范围;把树的节点关键字增多后树的层级比原来的二叉树少了,减少数据查找的次数和复杂度;

  2. B+比B树更适合实际应用中操作系统的文件索引和数据库索引?

    1) B+树的磁盘读写代价更低

    B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

    2) B+tree的查询效率更加稳定

    由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

    3)B树只适合随机检索,而B+树同时支持随机检索和顺序检索;

    4)增删文件时,效率更高。因为B+树的叶子节点包含所有关键字,并以有序的链表结构存储,这样可很好提高增删效率。

    B+树的叶子节点有一条链相连,而B树的叶子节点各自独立。

    由于B+树的内部节点只存放键,不存放值,因此,一次读取,可以在内存页中获取更多的键,有利于更快地缩小查找范围。B+树的叶节点由一条链相连,因此,当需要进行一次全数据遍历的时候,B+树只需要使用O(logN)时间找到最小的一个节点,然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历,这会需要更多的内存置换次数,因此也就需要花费更多的时间

  3. 非聚簇索引一定会回表查询吗?

    不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询。

  4. MySql的存储引擎,以及InnoDB和MyISAM的区别?

    存储引擎: InnoDB和MyISAM

    1. 是否支持行级锁 : MyISAM 只有表级锁(table-level locking),而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
    2. 是否支持事务和崩溃后的安全恢复: MyISAM 强调的是性能,每次查询具有原子性,其执行速度比InnoDB类型更快,但是不提供事务支持。但是InnoDB 提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
    3. 是否支持外键: MyISAM不支持,而InnoDB支持。
    4. 是否支持MVCC :仅 InnoDB 支持。应对高并发事务,MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTEDREPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观锁和悲观锁来实现;各数据库中MVCC实现并不统一。
  5. Mysql联合索引的最左匹配原则。给出联合索引(a,b),select *from table where a>0 and b>0,是否走索引,哪个走索引,哪个不走,以及从联合索引的底层结构去解释为什么?(字段a走索引,字段b不走索引)

    联合索引即由多列属性组成索引。

    当B+树的数据项是复合的数据结构,比如(num,name,age)的时候,B+数是按照从左到右的顺序来建立搜索树的,B+树会优先比较num来确定下一步的所搜方向,如果num相同再依次比较name和age,最后得到检索的数据;

    范围查询列可以使用索引(前提必须满足最左前缀),范围列后面的列无法使用索引。同时,索引最多作用于一个范围列。

  6. 讲一下最左前缀原则?

    最左前缀原则是发生在复合索引上的,只有复合索引才会有所谓的左和右之分。当查询条件精确匹配左边连续一个或多个列时,索引可以被使用。

    假设创建的联合索引由三个字段组成:

    ALTER TABLE table ADD INDEX index_name (num,name,age)
那么当查询的条件有为:num / (num AND name) / (num AND name AND age)时,索引才生效。所以在创建联合索引时,尽量把查询最频繁的那个字段作为最左(第一个)字段。查询的时候也尽量以这个字段为第一条件。
  1. left join,right join,inner join,full join之间的区别

    1. inner join,在两张表进行连接查询时,只保留两张表中完全匹配的结果集。
    2. left join,在两张表进行连接查询时,会返回左表所有的行,即使在右表中没有匹配的记录。
    3. right join,在两张表进行连接查询时,会返回右表所有的行,即使在左表中没有匹配的记录。
    4. full join,在两张表进行连接查询时,返回左表和右表中所有没有匹配的行。

    join 的过程中,其实就是从驱动表里面依次(注意理解这里面的依次)取出每一个值,然后去非驱动表里面进行匹配。

  2. 为什么InnoDB表必须有主键,并且推荐使用整形的自增主键?

    因为叶子节点是按顺序排列的,如果是非自增的话,就会插入的时候频繁的分裂页(效率降低)

    1、如果设置了主键,那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。

    2、如果表使用自增主键
    那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,主键的顺序按照数据记录的插入顺序排列,自动有序。当一页写满,就会自动开辟一个新的页

    3、如果使用非自增主键(如果身份证号或学号等)
    由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

  3. 间隙锁讲解一下

    for update是在数据库中上锁用的,可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候,其他事务可以读取但是不能写入或更新。for update 仅适用于InnoDB,并且必须开启事务,在begin与commit之间才生效。

    InnoDB默认是行级别的锁,当有明确指定的主键时候(且主键存在),是行级锁。否则是表级别。

    SELECT * FROM foods WHERE id=1 FOR UPDATE;
    SELECT * FROM foods WHERE id=1 and name=’咖啡色的羊驼’ FOR UPDATE;
间隙锁的目的是为了防止幻读,其主要通过两个方面实现这个目的:
(1)防止间隙内有新数据被插入
(2)防止已存在的数据,更新成间隙内的数据

innodb自动使用间隙锁的条件:

(1)必须在可重复读级别下
(2)检索条件必须有索引

next-key锁其实包含了记录锁和间隙锁,即锁定一个范围,并且锁定记录本身,InnoDB默认加锁方式是next-key 锁。
  1. 数据库问题,说一下从你打开命令行到发送请求,mysql服务器的整个相应流程? (当问到需要介绍数据库底层时可以这样回答)

    • MySQL 主要分为 Server 层和引擎层。
    • Server 层主要包括连接器、查询缓存、分析器、优化器、执行器,同时还有一个日志模块(binlog),这个日志模块所有执行引擎都可以共用, redolog 只有 InnoDB 有。
    • 引擎层是插件式的,目前主要包括,MyISAM,InnoDB,Memory 等。
    • 连接器: 身份认证和权限相关(登录 MySQL 的时候)。
    • 查询缓存: 执行查询语句的时候,会先查询缓存(MySQL 8.0 版本后移除,因为这个功能不太实用)。
    • 分析器: 没有命中缓存的话,SQL 语句就会经过分析器,分析器说白了就是要先看你的 SQL 语句要干嘛,再检查你的 SQL 语句语法是否正确。先词法分析,再语法分析
    • 优化器: 按照 MySQL 认为最优的方案去执行。
    • 执行器: 执行语句,然后从存储引擎返回数据。

    查询语句:

    select * from tb_student  A where A.age='18' and A.name=' 张三 ';
- 先检查该语句是否有权限,如果没有权限,直接返回错误信息,如果有权限,在 MySQL8.0 版本以前,会先查询缓存,以这条 sql 语句为 key 在内存中查询是否有结果,如果有直接缓存,如果没有,执行下一步。

- 通过分析器进行词法分析,提取 sql 语句的关键元素,比如提取上面这个语句是查询 select,提取需要查询的表名为 tb_student,需要查询所有的列,查询条件是这个表的 id='1'。然后判断这个 sql 语句是否有语法错误,比如关键词是否正确等等,如果检查没问题就执行下一步。

- 接下来就是优化器进行确定执行方案,上面的 sql 语句,可以有两种执行方案:
 a.先查询学生表中姓名为“张三”的学生,然后判断是否年龄是 18。
 b.先找出学生中年龄 18 岁的学生,然后再查询姓名为“张三”的学生。
那么优化器根据自己的优化算法进行选择执行效率最好的一个方案(优化器认为,有时候不一定最好)。那么确认了执行计划后就准备开始执行了。

- 进行权限校验,如果没有权限就会返回错误信息,如果有权限就会调用数据库引擎接口,返回引擎的执行结果。

更新语句:
update tb_student A set A.age='19' where A.name=' 张三 ';
我们来给张三修改下年龄,在实际数据库肯定不会设置年龄这个字段的,不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走,只不过执行更新的时候肯定要记录日志啦,这就会引入日志模块了,MySQL 自带的日志模块式 **bin log(归档日志)** ,所有的存储引擎都可以使用,我们常用的 InnoDB 引擎还自带了一个日志模块 **redo log(重做日志)**,我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下:

- 先查询到张三这一条数据,如果有缓存,也是会用到缓存。
- 然后拿到查询的语句,把 age 改为 19,然后调用引擎 API 接口,写入这一行数据,InnoDB 引擎把数据保存在内存中,同时记录 redo log,此时 redo log 进入 prepare 状态,然后告诉执行器,执行完成了,随时可以提交。
- 执行器收到通知后记录 binlog,然后调用引擎接口,提交 redo log 为提交状态。
- 更新完成。

**这里肯定有同学会问,为什么要用两个日志模块,用一个日志模块不行吗?**

这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ,MySQL 自带的引擎是 MyISAM,但是我们知道 redo log 是 InnoDB 引擎特有的,其他存储引擎都没有,这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启,之前提交的记录都不会丢失),binlog 日志只能用来归档。

并不是说只用一个日志模块不可以,只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么,又会有同学问,我用两个日志模块,但是不要这么复杂行不行,为什么 redo log 要引入 prepare 预提交状态?这里我们用反证法来说明下为什么要这么做?

- **先写 redo log 直接提交,然后写 binlog**,假设写完 redo log 后,机器挂了,binlog 日志没有被写入,那么机器重启后,这台机器会通过 redo log 恢复数据,但是这个时候 binlog 并没有记录该数据,后续进行机器备份的时候,就会丢失这一条数据,同时主从同步也会丢失这一条数据。
- **先写 binlog,然后写 redo log**,假设写完了 bin log,机器异常重启了,由于没有 redo log,本机是无法恢复这一条记录的,但是 bin log 又有记录,那么和上面同样的道理,就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了,写完 binglog 后,然后再提交 redo log 就会防止出现上述的问题,从而保证了数据的一致性。那么问题来了,有没有一个极端的情况呢?假设 redo log 处于预提交状态,binglog 也已经写完了,这个时候发生了异常重启会怎么样呢? 这个就要依赖于 MySQL 的处理机制了,**MySQL 的处理过程如下:**

- **判断 redo log 是否完整,如果判断是完整的,就立即提交**。
- **如果 redo log 只是预提交但不是 commit 状态,这个时候就会去判断 binlog 是否完整,如果完整就提交 redo log, 不完整就回滚事务。***

**这样就解决了数据一致性的问题**。

- 查询语句的执行流程如下:权限校验(如果命中缓存)--> 查询缓存 --> 分析器 --> 优化器 --> 权限校验 --> 执行器 --> 引擎
- 更新语句执行流程如下:分析器 --> 权限校验 --> 执行器 --> 引擎 -- redo log(prepare 状态) --> binlog --> redo log(commit状态)
  1. 讲一下redo log,undo log,binlog?

    redo Log:重做日志用来实现事务的持久性,用于记录事务操作的变化,记录的是数据修改之后的值。redo log由两部分组成:redo log buffer和redo log file。当事务提交(COMMIT)时,必须先将该事务的所有重做日志缓冲写入到重做日志文件进行持久化,才能COMMIT成功。MySQL宕机时,通过读取Redo Log中的数据,对数据库进行恢复。

    undo Log:回滚日志用来实现事务的回滚和多版本并发控制(MVCC)。Undo Log和Redo Log正好相反,记录的是数据被修改前的信息。undo log包括:insert undo log和update undo log。

    binlog:记录了对MySQL数据库执行更改的所有操作。

  2. MySQL 的数据如何恢复到任意时间点?

    恢复到任意时间点以定时的做全量备份,以及备份增量的 binlog 日志为前提。恢复到任意时间点首先将全量备份恢复之后,再此基础上回放增加的 binlog 直至指定的时间点。

  3. 一张数据库表如果要删除大量的数据如何提高效率,如何做?

    (1)抽取需要保留的数据到备份表中;
    (2)删除旧表数据;
    (3)备份表中的数据再插入旧表;

  4. Mysql如何保证一致性?

    通过预写式日志,undo log保证原子性,redo log保证持久性,设置隔离级别,保证并发事务进行的时候,保证数据一致性。恢复机制会将redo log中已提交的事务重做,保证事务的持久性;而undo log中未提交的事务进行回滚,保证事务的原子性。

    • 原子性:语句要么全执行,要么全不执行,是事务最核心的特性,事务本身就是以原子性来定义的;实现主要基于undo log
    • 持久性:保证事务提交后不会因为宕机等原因导致数据丢失;实现主要基于redo log
    • 隔离性:保证事务执行尽可能不受其他事务影响;InnoDB默认的隔离级别是RR,RR的实现主要基于锁机制、数据的隐藏列、undo log和next-key lock机制
    • 一致性:事务追求的最终目标,一致性的实现既需要数据库层面的保障,也需要应用层面的保障
  5. 数据库的主从复制?

    MySQL主从复制的流程

    1. 主库db的更新事件(update、insert、delete)被写到binlog
    2. 主库创建一个dump thread,把binlog的内容发送到从库
    3. 从库启动并发起连接,连接到主库
    4. 从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log
    5. 从库启动之后,创建一个SQL线程,从relay log里面读取内容,并解析成sql语句逐一执行

    MySQL主从复制是一个异步的复制过程,主库发送更新事件到从库,从库读取更新记录,并执行更新记录,使得从库的内容与主库保持一致。

    主从同步过程中主服务器有一个工作线程dump thread,从服务器有两个工作线程I/O thread和SQL thread。主库把外界接收的SQL请求记录到自己的binlog日志中,从库的I/O thread去请求主库的binlog日志,并将binlog日志写到relay log(中继日志)中,然后从库重做中继日志的SQL语句。主库通过dump thread给从库I/O thread传送binlog日志。

    binlog:binary log,主库中保存所有更新事件日志的二进制文件。binlog是数据库服务启动的一刻起,保存数据库所有变更记录(数据库结构和内容)的文件。在主库中,只要有更新事件出现,就会被依次地写入到binlog中,之后会推送到从库中作为从库进行复制的数据源。

  6. Mysq的事务隔离级别有哪几种,说一下可重复读解决了什么问题?还有什么问题没解决?幻读如何解决的?幻读的具体场景?说一下间隙锁如何实施的?

    并发事务带来哪些问题?

    • 脏读(Dirty read): 当一个事务正在访问数据并且对数据进行了修改,而这种修改还没有提交到数据库中,这时另外一个事务也访问了这个数据,然后使用了这个数据。因为这个数据是还没有提交的数据,那么另外一个事务读到的这个数据是“脏数据”,依据“脏数据”所做的操作可能是不正确的。
    • 丢失修改(Lost to modify): 指在一个事务读取一个数据时,另外一个事务也访问了该数据,那么在第一个事务中修改了这个数据后,第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失,因此称为丢失修改。 例如:事务1读取某表中的数据A=20,事务2也读取A=20,事务1修改A=A-1,事务2也修改A=A-1,最终结果A=19,事务1的修改被丢失。
    • 不可重复读(Unrepeatableread): 指在一个事务内多次读同一数据。在这个事务还没有结束时,另一个事务也访问该数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况,因此称为不可重复读。

    (读取数据的事务将会禁止写事务(但允许读事务),写事务则禁止任何其他事务。Mysql默认使用该隔离级别。这可以通过“共享读锁”和“排他写锁”实现,即事物需要对某些数据进行修改必须对这些数据加 X 锁,读数据时需要加上 S 锁,当数据读取完成并不立刻释放 S 锁,而是等到事物结束后再释放)

    • 幻读(Phantom read): 幻读与不可重复读类似。它发生在一个事务(T1)读取了几行数据,接着另一个并发事务(T2)插入了一些数据时。在随后的查询中,第一个事务(T1)就会发现多了一些原本不存在的记录,就好像发生了幻觉一样,所以称为幻读。

    事务是逻辑上的一组操作,要么都执行,要么都不执行。

    • READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读
    • READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生
    • REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生
    • SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读

    MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)

    InnoDB 存储引擎在 REPEATABLE-READ(可重读) 事务隔离级别下使用的是Next-Key Lock 锁算法,因此可以避免幻读的产生,这与其他数据库系统(如 SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读) 已经可以完全保证事务的隔离性要求,即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。

    InnoDB存储引擎的锁的算法有三种:

    • Record lock:单个行记录上的锁
    • Gap lock:间隙锁,锁定一个范围,不包括记录本身
    • Next-key lock:record+gap 锁定一个范围,包含记录本身
  7. Mysql一条sql非常慢,如何进行分析?

    分以下两种情况来讨论。

    1、大多数情况是正常的,只是偶尔会出现很慢的情况。

    2、在数据量不变的情况下,这条SQL语句一直以来都执行的很慢。

    第一种情况:偶尔

    (1)数据库在刷新脏页(当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。)

    当我们要往数据库插入一条数据、或者要更新一条数据的时候,我们知道数据库会在内存中把对应字段的数据更新了,但是更新之后,这些更新的字段并不会马上同步持久化到磁盘中去,而是把这些更新的记录写入到 redo log 日记中去,等到空闲的时候,在通过 redo log 里的日记把最新的数据同步到磁盘中去。(如果redo log写满了,这个时候就没办法等到空闲的时候再把数据同步到磁盘的,只能暂停其他操作,全身心来把数据同步到磁盘中去的,而这个时候,就会导致我们平时正常的SQL语句突然执行的很慢)

    (2)拿不到锁,所要执行的语句涉及到了别人对表或行加的锁

    第二种情况:一直

    (1)所要查询的字段没有索引(全表扫描)

    (2)字段有索引,但却没有用索引,由于对字段进行运算、函数操作导致无法用索引。

    select * from t where c - 1 = 1000;
select * from t where pow(c,2) = 1000;
(4)**数据库选错了索引(主要在回表查询的时候,二次查询可能导致,由于统计的失误,导致系统没有走索引,而是走了全表扫描**)

版权属于:zfh

本文链接:http://zfhblog.com/index.php/archives/229/



评论已关闭