MySQL 常见面试题

  |   0 评论   |   0 浏览

MySQL面试题

题号题目
1Mysql索引用的是什么算法
2Mysql事务的基本要素
3Mysql的存储引擎
4Mysql事务隔离级别
5Mysql高可用方案有哪些
6Mysql中utf8和utf8mb4区别
7Mysql中乐观锁和悲观锁区别
8Mysql索引主要是哪些
9Mysql联合索引最左匹配原则
10聚簇索引和非聚簇索引区别
11如何查询一个字段是否命中了索引
12Mysql中查询数据什么情况下不会命中索引
13Mysql中的MVCC是什么
14Mvcc和Redolog和Undolog以及Binlog有什么不同
15Mysql读写分离以及主从同步
16InnoDB的关键特性
17Mysql如何保证一致性和持久性
18为什么选择B+树作为索引结构
19InnoDB的行锁模式
20哈希(hash)比树(tree)更快,索引结构为什么要设计成树型
21为什么索引的key长度不能太长
22Mysql的数据如何恢复到任意时间点
23Mysql为什么加了索引可以加快查询
24Explain命令有什么用

Mysql索引用的是什么算法

Mysql 索引选用的是B+树,平衡二叉树的高度太高,查找可能需要较多的磁盘IO。B树索引占用内存较高(非叶子节点存储数据)。

B+树, 主要是查询效率高,O(logN),可以充分利用磁盘预读的特性,多叉树,深度小,叶子结点有序且存储数据.

Mysql事务的基本要素

  • 原子性:事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
  • 一致性:事务开始前和结束后,数据库的完整性约束没有被破坏。
  • 隔离性:同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。
  • 持久性:事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。

Mysql的存储引擎

  • InnoDB存储引擎

InnoDB存储引擎支持事务,其设计目标主要面向在线事务处理(OLTP)的应用。

其特点是行锁设计,支持外键,并支持非锁定锁,即默认读取操作不会产生锁。从Mysql5.5.8版本开始,InnoDB存储引擎是默认的存储引擎。

  • MyISAM存储引擎

MyISAM存储引擎不支持事务、表锁设计,支持全文索引,主要面向一些OLAP数据库应用。

InnoDB的数据文件本身就是主索引文件,而MyISAM的主索引和数据是分开的。

  • NDB存储引擎

NDB存储引擎是一个集群存储引擎,其结构是share nothing的集群架构,能提供更高的可用性。

NDB的特点是数据全部放在内存中(从MySQL 5.1版本开始,可以将非索引数据放在磁盘上),因此主键查找的速度极快,并且通过添加NDB数据存储节点可以线性地提高数据库性能,是高可用、高性能的集群系统。
NDB存储引擎的连接操作是在MySQL数据库层完成的,而不是在存储引擎层完成的。这意味着,复杂的连接操作需要巨大的网络开销,因此查询速度很慢。如果解决了这个问题,NDB存储引擎的市场应该是非常巨大的。

  • Memory存储引擎

Memory存储引擎(之前称HEAP存储引擎)将表中的数据存放在内存中,如果数据库重启或发生崩溃,表中的数据都将消失。
它非常适合用于存储临时数据的临时表,以及数据仓库中的纬度表。Memory存储引擎默认使用哈希索引,而不是我们熟悉的B+树索引。
虽然Memory存储引擎速度非常快,但在使用上还是有一定的限制。比如,只支持表锁,并发性能较差,并且不支持TEXT和BLOB列类型。最重要的是,存储变长字段时是按照定常字段的方式进行的,因此会浪费内存。

  • Archive存储引擎

Archive存储引擎只支持INSERT和SELECT操作,从MySQL 5.1开始支持索引。
Archive存储引擎使用zlib算法将数据行(row)进行压缩后存储,压缩比一般可达1∶10。正如其名字所示,Archive存储引擎非常适合存储归档数据,如日志信息。
Archive存储引擎使用行锁来实现高并发的插入操作,但是其本身并不是事务安全的存储引擎,其设计目标主要是提供高速的插入和压缩功能。

  • Maria存储引擎

Maria存储引擎是新开发的引擎,设计目标主要是用来取代原有的MyISAM存储引擎,从而成为MySQL的默认存储引擎。它可以看做是MyISAM的后续版本。
Maria存储引擎的特点是:支持缓存数据和索引文件,应用了行锁设计,提供了MVCC功能,支持事务和非事务安全的选项,以及更好的BLOB字符类型的处理性能。

Mysql事务隔离级别

Mysql有四种事务隔离级别,默认的是可重复读.

事务隔离级别脏读不可重复读幻读
读未提交
读已提交
可重复读
串行
  • 读未提交(Read uncommitted)

一个事务可以读取另一个未提交事务的数据,最低级别,任何情况都无法保证。

(1)所有事务都可以看到其他未提交事务的执行结果
(2)本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少
(3)该级别引发的问题是——脏读(Dirty Read):读取到了未提交的数据

  • 读已提交(Read committed)

一个事务要等另一个事务提交后才能读取数据,可避免脏读的发生。

(1)这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)
(2)它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变
(3)这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read),不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。

导致这种情况的原因可能有:

(1)有一个交叉的事务有新的commit,导致了数据的改变;
(2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit.

  • 可重复读(Repeatable read)

就是在开始读取数据(事务开启)时,不再允许修改操作,可避免脏读、不可重复读的发生。

(1)这是MySQL的默认事务隔离级别
(2)它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行
(3)此级别可能出现的问题——幻读(Phantom Read):当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行
(4)InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题.InnoDB采用MVCC来支持高并发,实现了四个标准隔离级别。默认基本是可重复读,并且提供间隙锁(next-key locks)策略防止幻读出现。

  • 串行(Serializable)

串行(Serializable),是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。
但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。Mysql的默认隔离级别是Repeatable read。

(1)这是最高的隔离级别.
(2)它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。
(3)在这个级别,可能导致大量的超时现象和锁竞争.

Mysql高可用方案有哪些

Mysql高可用方案包括:

  • 主从复制方案

这是MySQL自身提供的一种高可用解决方案,数据同步方法采用的是MySQL replication技术。MySQL replication就是从服务器到主服务器拉取二进制日志文件,然后再将日志文件解析成相应的SQL在从服务器上重新执行一遍主服务器的操作,通过这种方式保证数据的一致性。
为了达到更高的可用性,在实际的应用环境中,一般都是采用MySQL replication技术配合高可用集群软件keepalived来实现自动failover,这种方式可以实现95.000%的SLA。

  • MMM/MHA高可用方案

MMM提供了MySQL主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在MMM高可用方案中,典型的应用是双主多从架构,通过MySQL replication技术可以实现两个服务器互为主从,且在任何时候只有一个节点可以被写入,避免了多点写入的数据冲突。
同时,当可写的主节点故障时,MMM套件可以立刻监控到,然后将服务自动切换到另一个主节点,继续提供服务,从而实现MySQL的高可用。

  • Heartbeat/SAN高可用方案

在这个方案中,处理failover的方式是高可用集群软件Heartbeat,它监控和管理各个节点间连接的网络,并监控集群服务,当节点出现故障或者服务不可用时,自动在其他节点启动集群服务。
在数据共享方面,通过SAN(Storage Area Network)存储来共享数据,这种方案可以实现99.990%的SLA。

  • Heartbeat/DRBD高可用方案

这个方案处理failover的方式上依旧采用Heartbeat,不同的是,在数据共享方面,采用了基于块级别的数据同步软件DRBD来实现。
DRBD是一个用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。和SAN网络不同,它并不共享存储,而是通过服务器之间的网络复制数据。

  • NDB CLUSTER高可用方案

国内用NDB集群的公司非常少,貌似有些银行有用。NDB集群不需要依赖第三方组件,全部都使用官方组件,能保证数据的一致性,某个数据节点挂掉,其他数据节点依然可以提供服务,管理节点需要做冗余以防挂掉。
缺点是:管理和配置都很复杂,而且某些SQL语句例如join语句需要避免。

Mysql中utf8和utf8mb4区别

MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就可以了。

Mysql支持的 utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff,也就是 Unicode 中的基本多文种平面(BMP)。任何不在基本多文本平面的 Unicode字符,都无法使用 Mysql 的 utf8 字符集存储。

包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码,常见于 ios 和 android 手机上),和很多不常用的汉字,以及任何新增的 Unicode 字符等等。

Mysql 中保存 4 字节长度的 UTF-8 字符,需要使用utf8mb4 字符集,但只有5.5.3版本以后的才支持(查看版本: select version();)。因此呢,为了获取更好的兼容性,应该总是使用 utf8mb4 而非 utf8.

对于 CHAR 类型数据,utf8mb4 会多消耗一些空间,根据 Mysql 官方建议,使用 VARCHAR 替代 CHAR。

Mysql中乐观锁和悲观锁区别

  • 悲观锁(Pessimistic Lock)

悲观锁顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会block直到它拿到锁。

传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。

  • 乐观锁(Optimistic Lock),

乐观锁顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制。

乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

乐观锁的特点先进行业务操作,不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的,因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。

乐观锁在数据库上的实现完全是逻辑的,不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号,或者时间戳,然后按照如下方式实现:

1. SELECT data AS old_data, version AS old_version FROM …;
2. 根据获取的数据进行业务操作,得到new_data和new_version
3. UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功,操作完成
} else {
    // 乐观锁获取失败,回滚并重试
}

乐观锁是否在事务中其实都是无所谓的,其底层机制是这样:在数据库内部update同一行的时候是不允许并发的,即数据库每次执行一条update语句时会获取被update行的写锁,直到这一行被成功更新后才释放。

因此在业务操作进行前获取需要锁的数据的当前版本号,然后实际更新数据时再次对比版本号确认与之前获取的相同,并更新版本号,即可确认这之间没有发生并发的修改。如果更新失败即可认为老版本的数据已经被并发修改掉而不存在了,此时认为获取锁失败,需要回滚整个业务操作并可根据需要重试整个过程。

两种锁各有优缺点,不可认为一种好于另一种,像乐观锁适用于写比较少的情况下,即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。

但如果经常产生冲突,上层应用会不断的进行retry,这样反倒是降低了性能,所以这种情况下用悲观锁就比较合适。 

Mysql索引主要是哪些

索引的目的在于提高查询效率.

索引的类型:

  • UNIQUE(唯一索引):不可以出现相同的值,可以有NULL值
  • INDEX(普通索引):允许出现相同的索引内容
  • PROMARY KEY(主键索引):不允许出现相同的值
  • fulltext index(全文索引):可以针对值中的某个单词,但效率确实不敢恭维
  • 组合索引:实质上是将多个字段建到一个索引里,列值的组合必须唯一

索引虽然好处很多,但过多的使用索引可能带来相反的问题,索引也是有缺点的:

  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT,UPDATE和DELETE。因为更新表时,mysql不仅要保存数据,还要保存一下索引文件.
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在要给大表上建了多种组合索引,索引文件会膨胀很宽, 索引只是提高效率的一个方式,如果mysql有大数据量的表,就要花时间研究建立最优的索引,或优化查询语句。

使用索引时,有一些技巧:

  • 索引不会包含有NULL的列

只要列中包含有NULL值,都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此符合索引就是无效的。

  • 使用短索引

对串列进行索引,如果可以就应该指定一个前缀长度。例如,如果有一个char(255)的列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

  • 索引列排序

mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作,尽量不要包含多个列的排序,如果需要最好给这些列建复合索引。

  • like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,注意正确的使用方式。like ‘%aaa%’不会使用索引,而like ‘aaa%’可以使用索引。

  • 不要在列上进行运算
  • 不使用NOT IN<>!=操作,但<,<==>,>=,BETWEEN,IN是可以用到索引的
  • 索引要建立在经常进行select操作的字段上。
    这是因为,如果这些列很少用到,那么有无索引并不能明显改变查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
  • 索引要建立在值比较唯一的字段上。
  • 对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大,要么取值很少。
  • 在where和join中出现的列需要建立索引。
  • where的查询条件里有不等号(where column != …),mysql将无法使用索引。
  • 如果where字句的查询条件里使用了函数(如:where DAY(column)=…),mysql将无法使用索引。
  • 在join操作中(需要从多个数据表提取数据时),mysql只有在主键和外键的数据类型相同时才能使用索引,否则及时建立了索引也不会使用。

组合索引的作用:

  1. 减少开销。

建一个组合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。
对于大量数据的表,使用组合索引会大大的减少开销。

  1. 覆盖索引。

通常指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖。

对组合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2

那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。

所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。

  1. 效率高。

索引列越多,通过索引筛选出的数据越快。

Mysql联合索引最左匹配原则

最左前缀匹配原则:

  1. 在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先.

在检索数据时从联合索引的最左边开始匹配,Mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配了.

就比如 a=3 and b=4 and c>5 and d=6如果建立(abcd)顺序的索引,d就用不到索引了,如果建立(abdc)的索引则都可以用到索引,其中abd的顺序可以任意调整,因为查询优化器会重新编排(即使是c>5 and b=4 and d=6 and a=3也会全部用到 abdc索引 ).

  1. =in可以乱序,比如a=1 and b=2 and c=3 建立(abc)索引可以任意顺序,mysql查询优化器会优化顺序.

这里需要注意下, 比如abc索引 那么只要查询条件有a即可用到abc索引(如abc ab ac a),没有a就用不到。

最左前缀匹配成因:Mysql是创建复合索引的规则是根据索引最左边的字段进行排序,在第一个字段排序的基础上再进行第二个字段排序,类似于order by col1,col2… 所以第一个字段是绝对有序的 第二个字段就是无序的了,所以Mysql 强调最左前缀匹配.

聚簇索引和非聚簇索引区别

聚簇索引与非聚簇索引的区别是:叶子节点是否存放一整行记录.

InnoDB 主键使用的是聚簇索引,MyISAM 不管是主键索引,还是二级索引使用的都是非聚簇索引。

  1. 对于聚簇索引表来说(左图),表数据是和主键一起存储的,主键索引的叶结点存储行数据(包含了主键值),二级索引的叶结点存储行的主键值。
    使用的是B+树作为索引的存储结构,非叶子节点都是索引关键字,但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)。
  2. 对于非聚簇索引表来说(右图),表数据和索引是分成两部分存储的,主键索引和二级索引存储上没有任何区别。使用的是B+树作为索引的存储结构,所有的节点都是索引,叶子节点存储的是索引+索引对应的记录的数据。

因此, 聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针

如何查询一个字段是否命中了索引

通过explain sql可看下SQL是否走了索引,很快对比出来 .

当一个sql中索引字段为int类型时,例如搜索条件where num="111"where num=111都可以使用该字段的索引. 当一个中索引字段为varchar类型时,例如搜索条件where num="111"可以使用索引,where num=111不可以使用索引.

Mysql中查询数据什么情况下不会命中索引

通常不命中索引有接种情况:

  • 索引规范不合理,sql解析器不命中索引.
  • 表中索引是以表中数据量字段最多的建立的索引,sql解析器不命中索引.(实际就是索引没用,最后全局查找了)
  • bool的字段做索引,sql选择器不命中索引.
  • 模糊查询 %like
  • 索引列参与计算,使用了函数
  • 非最左前缀顺序
  • where对null判断
  • where不等于
  • or操作有至少一个字段没有索引
  • 需要回表的查询结果集过大(超过配置的范围)

Mysql中的MVCC是什么

数据库并发控制——锁, Multiversion (version) concurrency control (MCC or MVCC) 多版本并发控制 ,它是数据库管理系统一种常见的并发控制。

并发控制常用的是锁,当线程要对一个共享资源进行操作的时候,加锁是一种非常简单粗暴的方法(事务开始时给 DQL 加读锁,给 DML 加写锁),这种锁是一种 悲观 的实现方式,也就是说这会给其他事务造成堵塞,从而影响数据库性能。

其中在数据库中最常见的就是悲观锁和乐观锁:

  • 悲观锁

当一个线程需要对共享资源进行操作的时候,首先对共享资源进行加锁,当该线程持有该资源的锁的时候,其他线程对该资源进行操作的时候会被阻塞.

  • 乐观锁

当一个线程需要对一个共享资源进行操作的时候,不对它进行加锁,而是在操作完成之后进行判断。
比如乐观锁会通过一个版本号控制,如果操作完成后通过版本号进行判断在该线程操作过程中是否有其他线程已经对该共享资源进行操作了,如果有则通知操作失败,如果没有则操作成功,当然除了版本号还有CAS,如果不了解的可以去学习一下,这里不做过多涉及。

MVCC的两种读形式:

  • 快照读

读取的只是当前事务的可见版本,不用加锁。而你只要记住 简单的 select操作就是快照读(select * from table where id = xxx)。

  • 当前读

读取的是当前版本,比如 特殊的读操作,更新/插入/删除操作.

比如:

select * from table where xxx lock in share mode,
 select * from table where xxx for update,
 update table set....
 insert into table (xxx,xxx) values (xxx,xxx)
 delete from table where id = xxx

MVCC的实现原理:

MVCC 使用了“三个隐藏字段”来实现版本并发控制,MySQL在创建建表的时候 innoDB 创建的真正的三个隐藏列吧。

RowIDDB_TRX_IDDB_ROLL_PTRidnamepassword
自动创建的id事务id回滚指针idnamepassword
  • RowID:隐藏的自增ID,当建表没有指定主键,InnoDB会使用该RowID创建一个聚簇索引。
  • DB_TRX_ID:最近修改(更新/删除/插入)该记录的事务ID。
  • DB_ROLL_PTR:回滚指针,指向这条记录的上一个版本。

其实还有一个删除的flag字段,用来判断该行记录是否已经被删除。

而 MVCC 使用的是其中的 事务字段,回滚指针字段,是否删除字段。

我们来看一下现在的表格(isDelete是我自己取的,按照官方说法是在一行开头的content里面,这里其实位置无所谓,你只要知道有就行了)。

isDeleteDB_TRX_IDDB_ROLL_PTRidnamepassword
true/false事务id回滚指针idnamepassword

Mvcc和Redolog和Undolog以及Binlog有什么不同

  • Mvcc

MVCC多版本并发控制是MySQL中基于乐观锁理论实现隔离级别的方式,用于读已提交和可重复读取隔离级别的实现。
在MySQL中,会在表中每一条数据后面添加两个字段,最近修改该行数据的事务ID,指向该行(undolog表中)回滚段的指针。
Read View判断行的可见性,创建一个新事务时,copy一份当前系统中的活跃事务列表。意思是,当前不应该被本事务看到的其他事务id列表。

  • UndoLog

UndoLog也就是我们常说的回滚日志文件 主要用于事务中执行失败,进行回滚,以及MVCC中对于数据历史版本的查看。
由引擎层的InnoDB引擎实现,是逻辑日志,记录数据修改被修改前的值,比如"把id='B' 修改为id = 'B2' ,那么undo日志就会用来存放id ='B'的记录”。
当一条数据需要更新前,会先把修改前的记录存储在undolog中,如果这个修改出现异常,,则会使用undo日志来实现回滚操作,保证事务的一致性。
当事务提交之后,undo log并不能立马被删除,而是会被放到待清理链表中,待判断没有事物用到该版本的信息时才可以清理相应undolog。它保存了事务发生之前的数据的一个版本,用于回滚,同时可以提供多版本并发控制下的读(MVCC),也即非锁定读。

  • Redolog

Redolog是重做日志文件是记录数据修改之后的值,用于持久化到磁盘中。

Redolog包括两部分:

一. 是内存中的日志缓冲(redo log buffer),该部分日志是易失性的;

二. 是磁盘上的重做日志文件(redo log file),该部分日志是持久的。

由引擎层的InnoDB引擎实现,是物理日志,记录的是物理数据页修改的信息,比如“某个数据页上内容发生了哪些改动”。当一条数据需要更新时,InnoDB会先将数据更新,然后记录redoLog 在内存中,然后找个时间将redoLog的操作执行到磁盘上的文件上。
不管是否提交成功我都记录,你要是回滚了,那我连回滚的修改也记录。它确保了事务的持久性。

  • Binlog

Binlog由Mysql的Server层实现,是逻辑日志,记录的是sql语句的原始逻辑,比如"把id='B' 修改为id = ‘B2’。

Binlog会写入指定大小的物理文件中,是追加写入的,当前文件写满则会创建新的文件写入。

产生:事务提交的时候,一次性将事务中的sql语句,按照一定的格式记录到binlog中。

用于复制和恢复在主从复制中,从库利用主库上的binlog进行重播(执行日志中记录的修改逻辑),实现主从同步。业务数据不一致或者错了,用binlog恢复。

Mysql读写分离以及主从同步

  1. 原理:主库将变更写binlog日志,然后从库连接到主库后,从库有一个IO线程,将主库的binlog日志拷贝到自己本地,写入一个中继日志中,接着从库中有一个sql线程会从中继日志读取binlog,然后执行binlog日志中的内容,也就是在自己本地再执行一遍sql,这样就可以保证自己跟主库的数据一致。
  2. 问题:这里有很重要一点,就是从库同步主库数据的过程是串行化的,也就是说主库上并行操作,在从库上会串行化执行,由于从库从主库拷贝日志以及串行化执行sql特点,在高并发情况下,从库数据一定比主库慢一点,是有延时的,所以经常出现,刚写入主库的数据可能读不到了,要过几十毫秒,甚至几百毫秒才能读取到。还有一个问题,如果突然主库宕机了,然后恰巧数据还没有同步到从库,那么有些数据可能在从库上是没有的,有些数据可能就丢失了。所以mysql实际上有两个机制,一个是半同步复制,用来解决主库数据丢失问题,一个是并行复制,用来解决主从同步延时问题。
  3. 半同步复制:semi-sync复制,指的就是主库写入binlog日志后,就会将强制此时立即将数据同步到从库,从库将日志写入自己本地的relay log之后,接着会返回一个ack给主库,主库接收到至少一个从库ack之后才会认为写完成。
  4. 并发复制:指的是从库开启多个线程,并行读取relay log中不同库的日志,然后并行重放不同库的日志,这样库级别的并行。(将主库分库也可缓解延迟问题)

InnoDB的关键特性

  1. 插入缓冲:对于非聚集索引的插入或更新操作,不是每一次直接插入到索引页中,而是先判断插入的非聚集索引页是否在缓冲池中,若在,则直接插入;若不在,则先放入到一个Insert Buffer对象中。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge(合并)操作,这时通常能将多个插入合并到一个操作中(因为在一个索引页中),这就大大提高了对于非聚集索引插入的性能。
  2. 两次写:两次写带给InnoDB存储引擎的是数据页的可靠性,有经验的DBA也许会想,如果发生写失效,可以通过重做日志进行恢复。这是一个办法。但是必须清楚地认识到,如果这个页本身已经发生了损坏(物理到page页的物理日志成功页内逻辑日志失败),再对其进行重做是没有意义的。这就是说,在应用(apply)重做日志前,用户需要一个页的副本,当写入失效发生时,先通过页的副本来还原该页,再进行重做。在对缓冲池的脏页进行刷新时,并不直接写磁盘,而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer,之后通过doublewrite buffer再分两次,每次1MB顺序地写入共享表空间的物理磁盘上,这就是doublewrite。
  3. 自适应哈希索引:InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引。
  4. 异步IO:为了提高磁盘操作性能,当前的数据库系统都采用异步IO(AIO)的方式来处理磁盘操作。AIO的另一个优势是可以进行IO Merge操作,也就是将多个IO合并为1个IO,这样可以提高IOPS的性能。
  5. 刷新邻接页:当刷新一个脏页时,InnoDB存储引擎会检测该页所在区(extent)的所有页,如果是脏页,那么一起进行刷新。这样做的好处显而易见,通过AIO可以将多个IO写入操作合并为一个IO操作,故该工作机制在传统机械磁盘下有着显著的优势。

Mysql如何保证一致性和持久性

Mysql为了保证ACID中的一致性和持久性,使用了WAL(Write-Ahead Logging,先写日志再写磁盘)。Redo log就是一种WAL的应用。

当数据库忽然掉电,再重新启动时,Mysql可以通过Redo log还原数据。也就是说,每次事务提交时,不用同步刷新磁盘数据文件,只需要同步刷新Redo log就足够了。

为什么选择B+树作为索引结构

  • Hash索引:Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描
  • 二叉查找树:解决了排序的基本问题,但是由于无法保证平衡,可能退化为链表。
  • 平衡二叉树:通过旋转解决了平衡的问题,但是旋转操作效率太低。
  • 红黑树:通过舍弃严格的平衡和引入红黑节点,解决了AVL旋转效率过低的问题,但是在磁盘等场景下,树仍然太高,IO次数太多。
  • B+树:在B树的基础上,将非叶节点改造为不存储数据纯索引节点,进一步降低了树的高度;此外将叶节点使用指针连接成链表,范围查询更加高效。
    此外, B+树, 主要是查询效率高,O(logN),可以充分利用磁盘预读的特性,多叉树,深度小,叶子结点有序且存储数据.

InnoDB的行锁模式

  • 共享锁(S):用法lock in share mode,又称读锁,允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。

若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

  • 排他锁(X):用法for update,又称写锁,允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。
    若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。在没有索引的情况下,InnoDB只能使用表锁。

哈希(hash)比树(tree)更快,索引结构为什么要设计成树型

加速查找速度的数据结构,常见的有两类:

(1)哈希,例如HashMap,查询/插入/修改/删除的平均时间复杂度都是O(1);
(2)树,例如平衡二叉搜索树,查询/插入/修改/删除的平均时间复杂度都是O(lg(n));

哈希只能满足等值查询, 不满足范围和大小查询, 其次哈希不可以排序.

Mysql是用等值查询,用树的话,等值查询只需要顺序遍历即可.

但是对于排序查询的sql需求:分组:group by ,排序:order by ,比较:<、>等,哈希型的索引,时间复杂度会退化为O(n),而树型的“有序”特性,依然能够保持O(log(n)) 的高效率。

为什么索引的key长度不能太长

key 太长会导致一个页当中能够存放的 key 的数目变少,间接导致索引树的页数目变多,索引层次增加,从而影响整体查询变更的效率。

Mysql的数据如何恢复到任意时间点

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

Mysql为什么加了索引可以加快查询

在数据十分庞大的时候,索引可以大大加快查询的速度,这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

索引的优缺点:

优势:可以快速检索,减少I/O次数,加快检索速度;根据索引分组和排序,可以加快分组和排序;

劣势:索引本身也是表,因此会占用存储空间,一般来说,索引表占用的空间的数据表的1.5倍;索引表的维护和创建需要时间成本,这个成本随着数据量增大而增大;构建索引会降低数据表的修改操作(删除,添加,修改)的效率,因为在修改数据表的同时还需要修改索引表.

Explain命令有什么用

在开发的过程中,我们有时会用慢查询去记录一些执行时间比较久的Sql语句,找出这些Sql语句并不意味着完事了,这个时候我们就需要用到explain这个命令来查看一个这些Sql语句的执行计划,查看该Sql语句有没有使用上了索引,有没有做全表扫描,这些都可以通过explain命令来查看。
所以我们深入了解Mysql的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。

> explain select * from server;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | server  | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

expain出来的信息有10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra.

id: select选择标识符.

select_type: 表示查询的类型.

table: 输出结果集的表.

partitions: 匹配的分区.

type: 表示表的连接类型.

possible_keys: 表示查询时,可能使用的索引.

key: 表示实际使用的索引.

key_len: 索引字段的长度.

ref: 列与索引的比较.

rows: 扫描出的行数(估算的行数).

filtered: 按表条件过滤的行百分比.

Extra: 执行情况的描述和说明.
  • id

id是Sql执行的顺序的标识,Sql从大到小的执行:

  1. id相同时,执行顺序由上至下.
  2. 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行.
  3. id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行.
  • select_type 查询的类型

示查询中每个select子句的类型:

  1. SIMPLE(简单SELECT,不使用UNION或子查询等)
  2. PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
  3. UNION(UNION中的第二个或后面的SELECT语句)
  4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)
  5. UNION RESULT(UNION的结果)
  6. SUBQUERY(子查询中的第一个SELECT)
  7. DEPENDENT SUBQUERY(子查询中的第一个SELECT,取决于外面的查询)
  8. DERIVED(派生表的SELECT, FROM子句的子查询)
  9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)
  • table

table显示这一行的数据是关于哪张表的,有时不是真实的表名字,看到的是derivedx.

> explain select * from (select * from ( select * from t1 where id=2602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t1         | const  | PRIMARY,idx_t1_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
  • type 表的连接类型

type表示Mysql在表中找到所需行的方式,又称“访问类型”。

常用的类型有: ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好).

  1. ALL:Full Table Scan, Mysql将遍历全表以找到匹配的行.
  2. index: Full Index Scan,index与ALL区别为index类型只遍历索引树.
  3. range:只检索给定范围的行,使用一个索引来选择行.
  4. ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值.
  5. eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件.
  6. const、system: 当Mysql对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,Mysql就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system.
  7. NULL: Mysql在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。
  • possible_keys

possible_keys指出Mysql能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用.

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询.

  • Key

key列显示MySql实际决定使用的键(索引).

如果没有选择索引,键是NULL。要想强制Mysql使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

  • key_len

key_len表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)不损失精确性的情况下,长度越短越好 .

  • ref

ref表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值.

  • rows

rows表示Mysql根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数.

  • Extra执行情况的描述和说明

该列包含Mysql解决查询的详细信息,有以下几种情况:

  1. Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤.
  2. Using temporary:表示Mysql需要使用临时表来存储结果集,常见于排序和分组查询.
  3. Using filesort:Mysql中无法利用索引完成的排序操作称为“文件排序”
  4. Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。
  5. Impossible where:这个值强调了where语句会导致没有符合条件的行。
  6. Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行.

标题:MySQL 常见面试题
作者:疲惫的怪神明