• 技术文章 >头条

    66个面试问题,带你梳理MySQL知识点!

    青灯夜游青灯夜游2022-07-01 20:21:17转载837
    作为SQL Boy,基础部分不会有人不会吧?面试也不怎么问,基础掌握不错的小伙伴可以跳过这一部分。当然,可能会现场写一些SQL语句,SQ语句可以通过牛客、LeetCode、LintCode之类的网站来练习。

    1. 什么是内连接、外连接、交叉连接、笛卡尔积呢?

    2. 那MySQL 的内连接、左连接、右连接有有什么区别?

    MySQL的连接主要分为内连接和外连接,外连接常用的有左连接、右连接。

    1.png

    3.说一下数据库的三大范式?

    2.png

    3.png

    三大范式的作用是为了控制数据库的冗余,是对空间的节省,实际上,一般互联网公司的设计都是反范式的,通过冗余一些数据,避免跨表跨库,利用空间换时间,提高性能。

    4.varchar与char的区别?

    4.png

    char

    varchar

    日常的设计,对于长度相对固定的字符串,可以使用char,对于长度不确定的,使用varchar更合适一些。

    5.blob和text有什么区别?

    6.DATETIME和TIMESTAMP的异同?

    相同点

    1. 两个数据类型存储时间的表现格式一致。均为 YYYY-MM-DD HH:MM:SS
    2. 两个数据类型都包含「日期」和「时间」部分。
    3. 两个数据类型都可以存储微秒的小数秒(秒后6位小数秒)

    区别

    5.png

    7.MySQL中 in 和 exists 的区别?

    MySQL中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。我们可能认为exists比in语句的效率要高,这种说法其实是不准确的,要区分情景:

    8.MySQL里记录货币用什么字段类型比较好?

    货币在数据库中MySQL常用Decimal和Numric类型表示,这两种类型被MySQL实现为同样的类型。他们被用于保存与货币有关的数据。

    例如salary DECIMAL(9,2),9(precision)代表将被用于存储值的总的小数位数,而2(scale)代表将被用于存储小数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99。

    DECIMAL和NUMERIC值作为字符串存储,而不是作为二进制浮点数,以便保存那些值的小数精度。

    之所以不使用float或者double的原因:因为float和double是以二进制存储的,所以有一定的误差。

    9.MySQL怎么存储emoji?

    MySQL可以直接使用字符串存储emoji。

    但是需要注意的,utf8 编码是不行的,MySQL中的utf8是阉割版的 utf8,它最多只用 3 个字节存储字符,所以存储不了表情。那该怎么办?

    需要使用utf8mb4编码。

    alter table blogs modify content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci not null;

    10.drop、delete与truncate的区别?

    三者都表示删除,但是三者有一些差别:


    deletetruncatedrop
    类型属于DML属于DDL属于DDL
    回滚可回滚不可回滚不可回滚
    删除内容表结构还在,删除表的全部或者一部分数据行表结构还在,删除表中的所有数据从数据库中删除表,所有数据行,索引和权限也会被删除
    删除速度删除速度慢,需要逐行删除删除速度快删除速度最快

    因此,在不再需要一张表的时候,用drop;在想删除部分数据行时候,用delete;在保留表而删除所有数据的时候用truncate。

    11.UNION与UNION ALL的区别?

    12.count(1)、count(*) 与 count(列名) 的区别?

    6.png

    执行效果

    执行速度

    13.一条SQL查询语句的执行顺序?

    7.png

    数据库架构

    14.说说 MySQL 的基础架构?

    8.png

    MySQL逻辑架构图主要分三层:

    15.一条 SQL 查询语句在 MySQL 中如何执行的?

    存储引擎

    16.MySQL有哪些常见存储引擎?

    9.png

    主要存储引擎以及功能如下:

    功能MylSAMMEMORYInnoDB
    存储限制256TBRAM64TB
    支持事务NoNoYes
    支持全文索引YesNoYes
    支持树索引YesYesYes
    支持哈希索引NoYesYes
    支持数据缓存NoN/AYes
    支持外键NoNoYes

    MySQL5.5之前,默认存储引擎是MylSAM,5.5之后变成了InnoDB。

    InnoDB支持的哈希索引是自适应的,InnoDB会根据表的使用情况自动为表生成哈希索引,不能人为干预是否在一张表中生成哈希索引。

    MySQL 5.6开始InnoDB支持全文索引。

    17.那存储引擎应该怎么选择?

    大致上可以这么选择:

    使用哪一种引擎可以根据需要灵活选择,因为存储引擎是基于表的,所以一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

    18.InnoDB和MylSAM主要有什么区别?

    PS:MySQL8.0都开始慢慢流行了,如果不是面试,MylSAM其实可以不用怎么了解。

    10.png

    1. 存储结构:每个MyISAM在磁盘上存储成三个文件;InnoDB所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。

    2. 事务支持:MyISAM不提供事务支持;InnoDB提供事务支持事务,具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全特性。

    3 最小锁粒度:MyISAM只支持表级锁,更新时会锁住整张表,导致其它查询和更新都会被阻塞InnoDB支持行级锁。

    4. 索引类型:MyISAM的索引为聚簇索引,数据结构是B树;InnoDB的索引是非聚簇索引,数据结构是B+树。

    5. 主键必需:MyISAM允许没有任何索引和主键的表存在;InnoDB如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见) ,数据是主索引的一部分,附加索引保存的是主索引的值。

    6. 表的具体行数:MyISAM保存了表的总行数,如果select count() from table;会直接取出出该值; InnoDB没有保存表的总行数,如果使用select count() from table;就会遍历整个表;但是在加了wehre条件后,MyISAM和InnoDB处理的方式都一样。

    7. 外键支持:MyISAM不支持外键;InnoDB支持外键。

    日志

    19.MySQL日志文件有哪些?分别介绍下作用?

    11.png

    MySQL日志文件有很多,包括 :

    还有两个InnoDB存储引擎特有的日志文件:

    20.binlog和redo log有什么区别?

    21.一条更新语句怎么执行的了解吗?

    更新语句的执行是Server层和引擎层配合完成,数据除了要写入表中,还要记录相应的日志。

    12.png

    从上图可以看出,MySQL在执行更新语句的时候,在服务层进行语句的解析和执行,在引擎层进行数据的提取和存储;同时在服务层对binlog进行写入,在InnoDB内进行redo log的写入。

    不仅如此,在对redo log写入时有两个阶段的提交,一是binlog写入之前prepare状态的写入,二是binlog写入之后commit状态的写入。

    22.那为什么要两阶段提交呢?

    为什么要两阶段提交呢?直接提交不行吗?

    我们可以假设不采用两阶段提交的方式,而是采用“单阶段”进行提交,即要么先写入redo log,后写入binlog;要么先写入binlog,后写入redo log。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。

    先写入redo log,后写入binlog:

    在写完redo log之后,数据此时具有crash-safe能力,因此系统崩溃,数据会恢复成事务开始之前的状态。但是,若在redo log写完时候,binlog写入之前,系统发生了宕机。此时binlog没有对上面的更新语句进行保存,导致当使用binlog进行数据库的备份或者恢复时,就少了上述的更新语句。从而使得id=2这一行的数据没有被更新。

    13.png

    先写入binlog,后写入redo log:

    写完binlog之后,所有的语句都被保存,所以通过binlog复制或恢复出来的数据库中id=2这一行的数据会被更新为a=1。但是如果在redo log写入之前,系统崩溃,那么redo log中记录的这个事务会无效,导致实际数据库中id=2这一行的数据并没有更新。

    14.png

    简单说,redo log和binlog都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致。

    23.redo log怎么刷入磁盘的知道吗?

    redo log的写入不是直接落到磁盘,而是在内存中设置了一片称之为redo log buffer的连续内存空间,也就是redo 日志缓冲区

    15.png

    什么时候会刷入磁盘?

    在如下的一些情况中,log buffer的数据会刷入磁盘:

    log buffer 的大小是有限的,如果不停的往这个有限大小的 log buffer 里塞入日志,很快它就会被填满。如果当前写入 log buffer 的redo 日志量已经占满了 log buffer 总容量的大约一半左右,就需要把这些日志刷新到磁盘上。

    在事务提交时,为了保证持久性,会把log buffer中的日志全部刷到磁盘。注意,这时候,除了本事务的,可能还会刷入其它事务的日志。

    有一个后台线程,大约每秒都会刷新一次log buffer中的redo log到磁盘。

    重做日志缓存、重做日志文件都是以块(block) 的方式进行保存的,称之为重做日志块(redo log block) ,块的大小是固定的512字节。我们的redo log它是固定大小的,可以看作是一个逻辑上的 log group,由一定数量的log block 组成。

    16.png

    它的写入方式是从头到尾开始写,写到末尾又回到开头循环写。

    其中有两个标记位置:

    write pos是当前记录的位置,一边写一边后移,写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到磁盘。

    17.png

    write_pos追上checkpoint时,表示redo log日志已经写满。这时候就不能接着往里写数据了,需要执行checkpoint规则腾出可写空间。

    所谓的checkpoint规则,就是checkpoint触发后,将buffer中日志页都刷到磁盘。

    SQL 优化

    24.慢SQL如何定位呢?

    慢SQL的监控主要通过两个途径:

    18.png

    25.有哪些方式优化慢SQL?

    慢SQL的优化,主要从两个方面考虑,SQL语句本身的优化,以及数据库设计的优化。

    19.png

    避免不必要的列

    这个是老生常谈,但还是经常会出的情况,SQL查询的时候,应该只查询需要的列,而不要包含额外的列,像slect * 这种写法应该尽量避免。

    分页优化

    在数据量比较大,分页比较深的情况下,需要考虑分页的优化。

    例如:

    select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

    优化方案:

      select * from table where id >
      (select * from table where type = 2 and level = 9 order by id asc limit 190
    索引优化

    合理地设计和使用索引,是优化慢SQL的利器。

    利用覆盖索引

    InnoDB使用非主键索引查询数据时会回表,但是如果索引的叶节点中已经包含要查询的字段,那它没有必要再回表查询了,这就叫覆盖索引

    例如对于如下查询:

    select name from test where city='上海'

    我们将被查询的字段建立到联合索引中,这样查询结果就可以直接从索引中获取

    alter table test add index idx_city_name (city, name);

    低版本避免使用or查询

    在 MySQL 5.0 之前的版本要尽量避免使用 or 查询,可以使用 union 或者子查询来替代,因为早期的 MySQL 版本使用 or 查询可能会导致索引失效,高版本引入了索引合并,解决了这个问题。

    避免使用 != 或者 <> 操作符

    SQL中,不等于操作符会导致查询引擎放弃查询索引,引起全表扫描,即使比较的字段上有索引

    解决方法:通过把不等于操作符改成or,可以使用索引,避免全表扫描

    例如,把column<>’aaa’,改成column>’aaa’ or column<’aaa’,就可以使用索引了

    适当使用前缀索引

    适当地使用前缀所云,可以降低索引的空间占用,提高索引的查询效率。

    比如,邮箱的后缀都是固定的“@xxx.com”,那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

    alter table test add index index2(email(6));

    PS:需要注意的是,前缀索引也存在缺点,MySQL无法利用前缀索引做order by和group by 操作,也无法作为覆盖索引

    避免列上函数运算

    要避免在列字段上进行算术运算或其他表达式运算,否则可能会导致存储引擎无法正确使用索引,从而影响了查询的效率

    select * from test where id + 1 = 50;
    select * from test where month(updateTime) = 7;

    正确使用联合索引

    使用联合索引的时候,注意最左匹配原则。

    JOIN优化

    优化子查询

    尽量使用 Join 语句来替代子查询,因为子查询是嵌套查询,而嵌套查询会新创建一张临时表,而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间,同时对于返回结果集比较大的子查询,其对查询性能的影响更大

    小表驱动大表

    关联查询的时候要拿小表去驱动大表,因为关联的时候,MySQL内部会遍历驱动表,再去连接被驱动表。

    比如left join,左表就是驱动表,A表小于B表,建立连接的次数就少,查询速度就被加快了。

     select name from A left join B ;

    适当增加冗余字段

    增加冗余字段可以减少大量的连表查询,因为多张表的连表查询性能很低,所有可以适当的增加冗余字段,以减少多张表的关联查询,这是以空间换时间的优化策略

    避免使用JOIN关联太多的表

    《阿里巴巴Java开发手册》规定不要join超过三张表,第一join太多降低查询的速度,第二join的buffer会占用更多的内存。

    如果不可避免要join多张表,可以考虑使用数据异构的方式异构到ES中查询。

    排序优化

    利用索引扫描做排序

    MySQL有两种方式生成有序结果:其一是对结果集进行排序的操作,其二是按照索引顺序扫描得出的结果自然是有序的

    但是如果索引不能覆盖查询所需列,就不得不每扫描一条记录回表查询一次,这个读操作是随机IO,通常会比顺序全表扫描还慢

    因此,在设计索引时,尽可能使用同一个索引既满足排序又用于查找行

    例如:

    --建立索引(date,staff_id,customer_id)
    select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

    只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向都一样时,才能够使用索引来对结果做排序

    UNION优化

    条件下推

    MySQL处理union的策略是先创建临时表,然后将各个查询结果填充到临时表中最后再来做查询,很多优化策略在union查询中都会失效,因为它无法利用索引

    最好手工将where、limit等子句下推到union的各个子查询中,以便优化器可以充分利用这些条件进行优化

    此外,除非确实需要服务器去重,一定要使用union all,如果不加all关键字,MySQL会给临时表加上distinct选项,这会导致对整个临时表做唯一性检查,代价很高。

    26.怎么看执行计划(explain),如何理解其中各个字段的含义?

    explain是sql优化的利器,除了优化慢sql,平时的sql编写,也应该先explain,查看一下执行计划,看看是否还有优化的空间。

    直接在 select 语句之前增加explain关键字,就会返回执行计划的信息。

    20.png

    21.png

    1. id 列:MySQL会为每个select语句分配一个唯一的id值

    2. select_type 列,查询的类型,根据关联、union、子查询等等分类,常见的查询类型有SIMPLE、PRIMARY。

    3. table 列:表示 explain 的一行正在访问哪个表。

    4. type 列:最重要的列之一。表示关联类型或访问类型,即 MySQL 决定如何查找表中的行。

      性能从最优到最差分别为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

      • system

        system: 当表仅有一行记录时(系统表),数据量很少,往往不需要进行磁盘IO,速度非常快

      • const

        const:表示查询时命中 primary key 主键或者 unique 唯一索引,或者被连接的部分是一个常量(const)值。这类扫描效率极高,返回数据量少,速度非常快。

      • eq_ref

        eq_ref:查询时命中主键primary key 或者 unique key索引, type 就是 eq_ref

      • ref_or_null

        ref_or_null:这种连接类型类似于 ref,区别在于 MySQL会额外搜索包含NULL值的行。

      • index_merge

        index_merge:使用了索引合并优化方法,查询使用了两个以上的索引。

      • unique_subquery

        unique_subquery:替换下面的 IN子查询,子查询返回不重复的集合。

      • index_subquery

        index_subquery:区别于unique_subquery,用于非唯一索引,可以返回重复值。

      • range

        range:使用索引选择行,仅检索给定范围内的行。简单点说就是针对一个有索引的字段,给定范围检索数据。在where语句中使用 bettween...and<><=in 等条件查询 type 都是 range

      • index

        indexIndexALL 其实都是读全表,区别在于index是遍历索引树读取,而ALL是从硬盘中读取。

      • ALL

        就不用多说了,全表扫描。

    5. possible_keys 列:显示查询可能使用哪些索引来查找,使用索引优化sql的时候比较重要。

    6. key 列:这一列显示 mysql 实际采用哪个索引来优化对该表的访问,判断索引是否失效的时候常用。

    7. key_len 列:显示了 MySQL使用

    8. ref 列:ref 列展示的就是与索引列作等值匹配的值,常见的有:const(常量),func,NULL,字段名。

    9. rows 列:这也是一个重要的字段,MySQL查询优化器根据统计信息,估算SQL要查到结果集需要扫描读取的数据行数,这个值非常直观显示SQL的效率好坏,原则上rows越少越好。

    10. Extra 列:显示不适合在其它列的额外信息,虽然叫额外,但是也有一些重要的信息:

    索引

    索引可以说是MySQL面试中的重中之重,一定要彻底拿下。

    27.能简单说一下索引的分类吗?

    从三个不同维度对索引分类:

    22.png

    例如从基本使用使用的角度来讲:

    28.为什么使用索引会加快查询?

    传统的查询方法,是按照表的顺序遍历的,不论查询几条数据,MySQL需要将表的数据从头到尾遍历一遍。

    在我们添加完索引之后,MySQL一般通过BTREE算法生成一个索引文件,在查询数据库时,找到索引文件进行遍历,在比较小的索引数据里查找,然后映射到对应的数据,能大幅提升查找的效率。

    和我们通过书的目录,去查找对应的内容,一样的道理。

    23.png

    29.创建索引有哪些注意点?

    索引虽然是sql性能优化的利器,但是索引的维护也是需要成本的,所以创建索引,也要注意:

    30.索引哪些情况下会失效呢?

    31.索引不适合哪些场景呢?

    32.索引是不是建的越多越好呢?

    当然不是。

    33.MySQL索引用的什么数据结构了解吗?

    MySQL的默认存储引擎是InnoDB,它采用的是B+树结构的索引。

    24.png

    在这张图里,有两个重点:

    34.那一棵B+树能存储多少条数据呢?

    25.png

    假设索引字段是 bigint 类型,长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节,这样一共 14 字节。非叶子节点(一页)可以存储 16384/14=1170 个这样的 单元(键值+指针),代表有 1170 个指针。

    树深度为 2 的时候,有 1170^2 个叶子节点,可以存储的数据为 1170117016=21902400

    在查找数据时一次页的查找代表一次 IO,也就是说,一张 2000 万左右的表,查询数据最多需要访问 3 次磁盘。

    所以在 InnoDB 中 B+ 树深度一般为 1-3 层,它就能满足千万级的数据存储。

    35.为什么要用 B+ 树,而不用普通二叉树?

    可以从几个维度去看这个问题,查询是否够快,效率是否稳定,存储数据多少,以及查找磁盘次数。

    为什么不用普通二叉树?

    普通二叉树存在退化的情况,如果它退化成链表,相当于全表扫描。平衡二叉树相比于二叉查找树来说,查找效率更稳定,总体的查找速度也更快。

    为什么不用平衡二叉树呢?

    读取数据的时候,是从磁盘读到内存。如果树这种数据结构作为索引,那每查找一次数据就需要从磁盘中读取一个节点,也就是一个磁盘块,但是平衡二叉树可是每个节点只存储一个键值和数据的,如果是 B+ 树,可以存储更多的节点数据,树的高度也会降低,因此读取磁盘的次数就降下来啦,查询效率就快。

    36.为什么用 B+ 树而不用 B 树呢?

    B+相比较B树,有这些优势:

    37.Hash 索引和 B+ 树索引区别是什么?

    38.聚簇索引与非聚簇索引的区别?

    首先理解聚簇索引不是一种新的索引,而是而是一种数据存储方式。 聚簇表示数据行和相邻的键值紧凑地存储在一起。我们熟悉的两种存储引擎——MyISAM采用的是非聚簇索引,InnoDB采用的是聚簇索引。

    可以这么说:

    26.png

    39.回表了解吗?

    在InnoDB存储引擎里,利用辅助索引查询,先通过辅助索引找到主键索引的键值,再通过主键值查出主键索引里面没有符合要求的数据,它比基于主键索引的查询多扫描了一棵索引树,这个过程就叫回表。

    例如:select * from user where name = ‘张三’;

    27.png

    40.覆盖索引了解吗?

    在辅助索引里面,不管是单列索引还是联合索引,如果 select 的数据列只用辅助索引中就能够取得,不用去查主键索引,这时候使用的索引就叫做覆盖索引,避免了回表。

    比如,select name from user where name = ‘张三’;

    28.png

    41.什么是最左前缀原则/最左匹配原则?

    注意:最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是一个概念。

    最左匹配原则:在InnoDB的联合索引中,查询的时候只有匹配了前一个/左边的值之后,才能匹配下一个。

    根据最左匹配原则,我们创建了一个组合索引,如 (a1,a2,a3),相当于创建了(a1)、(a1,a2)和 (a1,a2,a3) 三个索引。

    为什么不从最左开始查,就无法匹配呢?

    比如有一个user表,我们给 name 和 age 建立了一个组合索引。

    ALTER TABLE user add INDEX comidx_name_phone (name,age);

    组合索引在 B+Tree 中是复合的数据结构,它是按照从左到右的顺序来建立搜索树的 (name 在左边,age 在右边)。

    29.png

    从这张图可以看出来,name 是有序的,age 是无序的。当 name 相等的时候, age 才是有序的。

    这个时候我们使用where name= ‘张三‘ and age = ‘20 ‘去查询数据的时候, B+Tree 会优先比较 name 来确定下一步应该搜索的方向,往左还是往右。如果 name 相同的时候再比较age。但是如果查询条件没有 name,就不知道下一步应该查哪个 节点,因为建立搜索树的时候 name 是第一个比较因子,所以就没用上索引。

    42.什么是索引下推优化?

    索引条件下推优化(Index Condition Pushdown (ICP) )是MySQL5.6添加的,用于优化数据查询。

    例如一张表,建了一个联合索引(name, age),查询语句:select * from t_user where name like '张%' and age=10;,由于name使用了范围查询,根据最左匹配原则:

    不使用ICP,引擎层查找到name like '张%'的数据,再由Server层去过滤age=10这个条件,这样一来,就回表了两次,浪费了联合索引的另外一个字段age

    30.png

    但是,使用了索引下推优化,把where的条件放到了引擎层执行,直接根据name like '张%' and age=10的条件进行过滤,减少了回表的次数。

    31.png

    索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。

    43.MySQL中有哪几种锁,列举一下?

    32.png

    如果按锁粒度划分,有以下3种:

    如果按照兼容性,有两种,

    44.说说InnoDB里的行锁实现?

    我们拿这么一个用户表来表示行级锁,其中插入了4行数据,主键值分别是1,6,8,12,现在简化它的聚簇索引结构,只保留数据记录。

    33.png

    InnoDB的行锁的主要实现如下:

    记录锁就是直接锁定某行记录。当我们使用唯一性的索引(包括唯一索引和聚簇索引)进行等值查询且精准匹配到一条记录时,此时就会直接将这条记录锁定。例如select * from t where id =6 for update;就会将id=6的记录锁定。

    34.png

    间隙锁(Gap Locks) 的间隙指的是两个记录之间逻辑上尚未填入数据的部分,是一个左开右开空间

    35.png

    间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询,并且没有命中任何一个record,此时就会将对应的间隙区间锁定。例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 6 for update;就会将(1,6)区间锁定。

    临键指的是间隙加上它右边的记录组成的左开右闭区间。比如上述的(1,6]、(6,8]等。

    36.png

    临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合,即除了锁住记录本身,还要再锁住索引之间的间隙。当我们使用范围查询,并且命中了部分record记录,此时锁住的就是临键区间。注意,临键锁锁住的区间会包含最后一个record的右边的临键区间。例如select * from t where id > 5 and id <= 7 for update;会锁住(4,7]、(7,+∞)。mysql默认行锁类型就是临键锁(Next-Key Locks)。当使用唯一性索引,等值查询匹配到一条记录的时候,临键锁(Next-Key Locks)会退化成记录锁;没有匹配到任何记录的时候,退化成间隙锁。

    间隙锁(Gap Locks)临键锁(Next-Key Locks)都是用来解决幻读问题的,在已提交读(READ COMMITTED)隔离级别下,间隙锁(Gap Locks)临键锁(Next-Key Locks)都会失效!

    上面是行锁的三种实现算法,除此之外,在行上还存在插入意向锁。

    一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了意向锁 ,如果有的话,插入操作需要等待,直到拥有 gap锁 的那个事务提交。但是事务在等待的时候也需要在内存中生成一个 锁结构 ,表明有事务想在某个 间隙 中插入新记录,但是现在在等待。这种类型的锁命名为 Insert Intention Locks ,也就是插入意向锁 。

    假如我们有个T1事务,给(1,6)区间加上了意向锁,现在有个T2事务,要插入一个数据,id为4,它会获取一个(1,6)区间的插入意向锁,又有有个T3事务,想要插入一个数据,id为3,它也会获取一个(1,6)区间的插入意向锁,但是,这两个插入意向锁锁不会互斥。

    37.png

    45.意向锁是什么知道吗?

    意向锁是一个表级锁,不要和插入意向锁搞混。

    意向锁的出现是为了支持InnoDB的多粒度锁,它解决的是表锁和行锁共存的问题。

    当我们需要给一个表加表锁的时候,我们需要根据去判断表中有没有数据行被锁定,以确定是否能加成功。

    假如没有意向锁,那么我们就得遍历表中所有数据行来判断有没有行锁;

    有了意向锁这个表级锁之后,则我们直接判断一次就知道表中是否有数据行被锁定了。

    有了意向锁之后,要执行的事务A在申请行锁(写锁)之前,数据库会自动先给事务A申请表的意向排他锁。当事务B去申请表的互斥锁时就会失败,因为表上有意向排他锁之后事务B申请表的互斥锁时会被阻塞。

    38.png

    46.MySQL的乐观锁和悲观锁了解吗?

    悲观锁认为被它保护的数据是极其不安全的,每时每刻都有可能被改动,一个事务拿到悲观锁后,其他任何事务都不能对该数据进行修改,只能等待锁被释放才可以执行。

    数据库中的行锁,表锁,读锁,写锁均为悲观锁。

    乐观锁认为数据的变动不会太频繁。

    乐观锁通常是通过在表中增加一个版本(version)或时间戳(timestamp)来实现,其中,版本最为常用。

    事务在从数据库中取数据时,会将该数据的版本也取出来(v1),当事务对数据变动完毕想要将其更新到表中时,会将之前取出的版本v1与数据中最新的版本v2相对比,如果v1=v2,那么说明在数据变动期间,没有其他事务对数据进行修改,此时,就允许事务对表中的数据进行修改,并且修改时version会加1,以此来表明数据已被变动。

    如果,v1不等于v2,那么说明数据变动期间,数据被其他事务改动了,此时不允许数据更新到表中,一般的处理办法是通知用户让其重新操作。不同于悲观锁,乐观锁通常是由开发者实现的。

    47.MySQL 遇到过死锁问题吗,你是如何解决的?

    排查死锁的一般步骤是这样的:

    (1)查看死锁日志 show engine innodb status;

    (2)找出死锁 sql

    (3)分析 sql 加锁情况

    (4)模拟死锁案发

    (5)分析死锁日志

    (6)分析死锁结果

    当然,这只是一个简单的流程说明,实际上生产中的死锁千奇百怪,排查和解决起来没那么简单。

    事务

    48.MySQL 事务的四大特性说一下?

    39.png

    49.那ACID靠什么保证的呢?

    40.png

    50.事务的隔离级别有哪些?MySQL 的默认隔离级别是什么?

    41.png

    MySQL默认的事务隔离级别是可重复读 (Repeatable Read)。

    51.什么是幻读,脏读,不可重复读呢?

    不同的隔离级别,在并发事务下可能会发生的问题:

    隔离级别脏读不可重复读幻读
    Read Uncommited 读取未提交
    Read Commited 读取已提交
    Repeatable Read 可重复读
    Serialzable 可串行化

    52.事务的各个隔离级别都是如何实现的?

    读未提交

    读未提交,就不用多说了,采取的是读不加锁原理。

    读取已提交&可重复读

    读取已提交和可重复读级别利用了ReadViewMVCC,也就是每个事务只能读取它能看到的版本(ReadView)。

    串行化

    串行化的实现采用的是读写都加锁的原理。

    串行化的情况下,对于同一行事务,会加写锁会加读锁。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。

    53.MVCC了解吗?怎么实现的?

    MVCC(Multi Version Concurrency Control),中文名是多版本并发控制,简单来说就是通过维护数据历史版本,从而解决并发访问情况下的读一致性问题。关于它的实现,要抓住几个关键点,隐式字段、undo日志、版本链、快照读&当前读、Read View

    版本链

    对于InnoDB存储引擎,每一行记录都有两个隐藏列DB_TRX_ID、DB_ROLL_PTR

    42.png

    假如有一张user表,表中只有一行记录,当时插入的事务id为80。此时,该条记录的示例图如下:

    43.png

    接下来有两个DB_TRX_ID分别为100200的事务对这条记录进行update操作,整个过程如下:

    44.png

    由于每次变动都会先把undo日志记录下来,并用DB_ROLL_PTR指向undo日志地址。因此可以认为,对该条记录的修改日志串联起来就形成了一个版本链,版本链的头节点就是当前记录最新的值。如下:

    45.png

    ReadView

    对于Read CommittedRepeatable Read隔离级别来说,都需要读取已经提交的事务所修改的记录,也就是说如果版本链中某个版本的修改没有提交,那么该版本的记录时不能被读取的。所以需要确定在Read CommittedRepeatable Read隔离级别下,版本链中哪个版本是能被当前事务读取的。于是就引入了ReadView这个概念来解决这个问题。

    Read View就是事务执行快照读时,产生的读视图,相当于某时刻表记录的一个快照,通过这个快照,我们可以获取:

    46.png

    有了这个 ReadView ,这样在访问某条记录时,只需要按照下边的步骤判断记录的某个版本是否可见:

    如果某个版本的数据对当前事务不可见的话,那就顺着版本链找到下一个版本的数据,继续按照上边的步骤判断可见性,依此类推,直到版本链中的最后一个版本。如果最后一个版本也不可见的话,那么就意味着该条记录对该事务完全不可见,查询结果就不包含该记录。

    在 MySQL 中, READ COMMITTED 和 REPEATABLE READ 隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

    READ COMMITTED 是每次读取数据前都生成一个ReadView,这样就能保证自己每次都能读到其它事务提交的数据;REPEATABLE READ 是在第一次读取数据时生成一个ReadView,这样就能保证后续读取的结果完全一致。

    高可用/性能

    54.数据库读写分离了解吗?

    读写分离的基本原理是将数据库读写操作分散到不同的节点上,下面是基本架构图:

    47.png

    读写分离的基本实现是:

    55.那读写分离的分配怎么实现呢?

    将读写操作区分开来,然后访问不同的数据库服务器,一般有两种方式:程序代码封装和中间件封装。

    1、程序代码封装

    程序代码封装指在代码中抽象一个数据访问层(所以有的文章也称这种方式为 "中间层封装" ) ,实现读写操作分离和数据库服务器连接的管理。例如,基于 Hibernate 进行简单封装,就可以实现读写分离:

    48.png

    目前开源的实现方案中,淘宝的 TDDL (Taobao Distributed Data Layer, 外号:头都大了)是比较有名的。

    2、中间件封装

    中间件封装指的是独立一套系统出来,实现读写操作分离和数据库服务器连接的管理。中间件对业务服务器提供 SQL 兼容的协议,业务服务器无须自己进行读写分离。

    对于业务服务器来说,访问中间件和访问数据库没有区别,事实上在业务服务器看来,中间件就是一个数据库服务器。

    其基本架构是:

    49.png

    56.主从复制原理了解吗?

    50.png

    57.主从同步延迟怎么处理?

    主从同步延迟的原因

    一个服务器开放N个链接给客户端来连接的,这样有会有大并发的更新操作, 但是从服务器的里面读取 binlog 的线程仅有一个,当某个 SQL 在从服务器上执行的时间稍长 或者由于某个 SQL 要进行锁表就会导致,主服务器的 SQL 大量积压,未被同步到从服务器里。这就导致了主从不一致, 也就是主从延迟。

    主从同步延迟的解决办法

    解决主从复制延迟有几种常见的方法:

    例如,注册账号完成后,登录时读取账号的读操作也发给数据库主服务器。这种方式和业务强绑定,对业务的侵入和影响较大,如果哪个新来的程序员不知道这样写代码,就会导致一个bug。

    这就是通常所说的 "二次读取" ,二次读取和业务无绑定,只需要对底层数据库访问的 API 进行封装即可,实现代价较小,不足之处在于如果有很多二次读取,将大大增加主机的读操作压力。例如,黑客暴力破解账号,会导致大量的二次读取操作,主机可能顶不住读操作的压力从而崩溃。

    例如,对于一个用户管理系统来说,注册 + 登录的业务读写操作全部访问主机,用户的介绍、爰好、等级等业务,可以采用读写分离,因为即使用户改了自己的自我介绍,在查询时却看到了自我介绍还是旧的,业务影响与不能登录相比就小很多,还可以忍受。

    58.你们一般是怎么分库的呢?

    51.png

    52.png

    59.那你们是怎么分表的?

    53.png

    60.水平分表有哪几种路由方式?

    什么是路由呢?就是数据应该分到哪一张表。

    水平分表主要有三种路由方式:

    我们可以观察一些支付系统,发现只能查一年范围内的支付记录,这个可能就是支付公司按照时间进行了分表。

    54.png

    范围路由设计的复杂点主要体现在分段大小的选取上,分段太小会导致切分后子表数量过多,增加维护复杂度;分段太大可能会导致单表依然存在性能问题,一般建议分段大小在 100 万至2000 万之间,具体需要根据业务选取合适的分段大小。

    范围路由的优点是可以随着数据的增加平滑地扩充新的表。例如,现在的用户是 100 万,如果增加到 1000 万,只需要增加新的表就可以了,原有的数据不需要动。范围路由的一个比较隐含的缺点是分布不均匀,假如按照 1000 万来进行分表,有可能某个分段实际存储的数据量只有 1000 条,而另外一个分段实际存储的数据量有 900 万条。

    同样以订单 id 为例,假如我们一开始就规划了 4个数据库表,路由算法可以简单地用 id % 4 的值来表示数据所属的数据库表编号,id 为 12的订单放到编号为 50的子表中,id为 13的订单放到编号为 61的字表中。

    55.png

    Hash 路由设计的复杂点主要体现在初始表数量的选取上,表数量太多维护比较麻烦,表数量太少又可能导致单表性能存在问题。而用了 Hash 路由后,增加子表数量是非常麻烦的,所有数据都要重分布。Hash 路由的优缺点和范围路由基本相反,Hash 路由的优点是表分布比较均匀,缺点是扩充新的表很麻烦,所有数据都要重分布。

    配置路由设计简单,使用起来非常灵活,尤其是在扩充表的时候,只需要迁移指定的数据,然后修改路由表就可以了。

    56.png

    配置路由的缺点就是必须多查询一次,会影响整体性能;而且路由表本身如果太大(例如,几亿条数据) ,性能同样可能成为瓶颈,如果我们再次将路由表分库分表,则又面临一个死循环式的路由算法选择问题。

    61.不停机扩容怎么实现?

    实际上,不停机扩容,实操起来是个非常麻烦而且很有风险的操作,当然,面试回答起来就简单很多。

    57.png

    58.png

    59.png

    62.常用的分库分表中间件有哪些?

    63.那你觉得分库分表会带来什么问题呢?

    从分库的角度来讲:

    使用关系型数据库,有很大一点在于它保证事务完整性。

    而分库之后单机事务就用不上了,必须使用分布式事务来解决。

    在一个库中的时候我们还可以利用 JOIN 来连表查询,而跨库了之后就无法使用 JOIN 了。

    此时的解决方案就是在业务代码中进行关联,也就是先把一个表的数据查出来,然后通过得到的结果再去查另一张表,然后利用代码来关联得到最终的结果。

    这种方式实现起来稍微比较复杂,不过也是可以接受的。

    还有可以适当的冗余一些字段。比如以前的表就存储一个关联 ID,但是业务时常要求返回对应的 Name 或者其他字段。这时候就可以把这些字段冗余到当前表中,来去除需要关联的操作。

    还有一种方式就是数据异构,通过binlog同步等方式,把需要跨库join的数据异构到ES等存储结构中,通过ES进行查询。

    从分表的角度来看:

    只能由业务代码来实现或者用中间件将各表中的数据汇总、排序、分页然后返回。

    数据的迁移,容量如何规划,未来是否可能再次需要扩容,等等,都是需要考虑的问题。

    数据库表被切分后,不能再依赖数据库自身的主键生成机制,所以需要一些手段来保证全局主键唯一。

    运维

    64.百万级别以上的数据如何删除?

    关于索引:由于索引需要额外的维护成本,因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。

    所以,在我们删除数据库百万级别数据的时候,查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

    65.百万千万级大表如何添加字段?

    当线上的数据库数据量到达几百万、上千万的时候,加一个字段就没那么简单,因为可能会长时间锁表。

    大表添加字段,通常有这些做法:

    66.MySQL 数据库 cpu 飙升的话,要怎么处理呢?

    排查过程:

    (1)使用 top 命令观察,确定是 mysqld 导致还是其他原因。

    (2)如果是 mysqld 导致的,show processlist,查看 session 情况,确定是不是有消耗资源的 sql 在运行。

    (3)找出消耗高的 sql,看看执行计划是否准确, 索引是否缺失,数据量是否太大。

    处理:

    (1)kill 掉这些线程 (同时观察 cpu 使用率是否下降),

    (2)进行相应的调整 (比如说加索引、改 sql、改内存参数)

    (3)重新跑这些 SQL。

    其他情况:

    也有可能是每个 sql 消耗资源并不多,但是突然之间,有大量的 session 连进来导致 cpu 飙升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制连接数等

    【相关推荐:mysql视频教程

    声明:本文转载于:掘金社区,如有侵犯,请联系admin@php.cn删除
    专题推荐:mysql
    上一篇:35+个chrome插件,让你工作学习事半功倍! 下一篇:【吐血整理】2022年最新前端面试题大全及答案(收藏)
    20期PHP线上班

    相关文章推荐

    • 【活动】充值PHP中文网VIP即送云服务器• 怎么查询mysql中所有表• mysql如何增加唯一索引• mysql中有关键字吗• mysql怎么将字段修改为not null• 如何用好MySQL索引?你必须了解这些事!• MySQL日志管理(总结分享)
    1/1

    PHP中文网