面试题集锦-MySQL

模式

• 单点
• 主备
• 主从

主备

切换方式

主备切换方式：

• 手动切换
• 自动切换

方式

• 一主一备
• 一主多备

主从

参考

概述

主从配置一般都是和读写分离相结合，主服务器负责写数据，从服务器负责读数据，并保证主服务器的数据及时同步到从服务器。

原理

主节点

1. 当主节点上进行 insert、update、delete 操作时，会按照时间先后顺序写入到 binlog 中(增删改sql写入binlog)；
2. 创建一个叫做 binlog dump 的线程；
   2.1 当从节点连接到主节点时,创建针对该从节点的binlog dump线程
   2.2 一个主节点有多少个从节点，就会创建多少个 binlog dump 线程；
   2.3 当主节点的 binlog 发生变化的时候，也就是进行了更改操作，binlog dump 线程就会通知从节点 (Push模式)，并将相应的 binlog 内容发送给从节点；

从节点

当开启主从同步的时候，从节点会创建两个线程用来完成数据同步的工作。

1. I/O线程： 此线程连接到主节点，主节点上的 binlog dump 线程会将 binlog 的内容发送给此线程。此线程接收到 binlog 内容后，再将内容写入到本地的 relay（转发） log。
2. SQL线程： 该线程读取 I/O 线程写入的 relay log，并且根据 relay log 的内容对从数据库做对应的操作

方式

• 一主一从
• 一主多从
• 多主多从

读写分离

参考

分库分表

参考

索引

参考
参考
插入数据默认根据主键id进行了升序排列(前提是id设置了主键)

页

• 操作系统的页
• MySQL的页
• 程序局部性原理
• MySQL的数据正是以页为基本单位组合而成的
• 页的大小是16KB，是操作系统的4倍

页目录

目录页

目录页的本质也是页，普通页中存的数据是项目数据，而目录页中存的数据是普通页的地址。

B+树

每个节点就可以理解为是一个页，而叶子节点也就是数据页，除了叶子节点以外的节点就是目录页。 非叶子节点只存放了索引，而只有叶子节点中存放了真实的数据，这也是符合B+树的特点的

聚簇索引和非聚簇索引

• 所谓聚簇索引，就是将索引和数据放到一起，找到索引也就找到了数据，我们刚才看到的B+树索引就是一种聚簇索引，
• 非聚簇索引就是将数据和索引分开，查找时需要先查找到索引，然后通过索引回表找到相应的数据。
• InnoDB有且只有一个聚簇索引，而MyISAM中都是非聚簇索引。

为什么InnoDB只有一个聚簇索引，而不将所有索引都使用聚簇索引？

因为聚簇索引是将索引和数据都存放在叶子节点中，如果所有的索引都用聚簇索引，则每一个索引都将保存一份数据，会造成数据的冗余，在数据量很大的情况下，这种数据冗余是很消耗资源的。

联合索引的最左前缀匹配原则

1.最左前缀匹配原则，MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如 a=3 and b=4 and c>5 and d=6,如果建立(a,b,c,d)顺序的索引，d是无法使用索引的，如果建立(a,b,d,c)的索引则都可以使用到，a、b、d的顺序可以任意调整。
2.=和in可以乱序，比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

覆盖索引

在稀疏索引(非聚簇索引)情况下通常需要通过叶子节点的指针回表查询数据，什么情况下不需要回表？

科普时间——覆盖索引 覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

explain

• type
  找到数据的方式，根据效率从高到低排序有如下几种

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

如果type为index或all，表示本次扫描为全表扫描，说明这个语句是需要优化的。

• extra
  可以用来辅助type帮助我们进行SQL优化，extra中出现以下两项，意味着MySQL根本不能使用索引，效率会受到重大影响，应该尽可能对此进行优化。

• Using filesort
  表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容，可能在内存或者磁盘上进行排序。MySQL中无法例用索引完成的排序操作称为“文件排序”。

• Using temporary
  表示MySQL在对查询结果排序时使用临时表，常见于排序 order by和分组查询group by。

慢日志

show VARIABLES like '%_quer%';

long_query_time	1.000000 //慢日志阈值：单位秒
slow_query_log	ON  /是否开启慢日志
slow_query_log_file	/data/mysql/data/4123/mysql-slow.log //慢日志记录位置

索引哪些情况会失效

参考

• 查询条件包含or，可能导致索引失效
• 如何字段类型是字符串，where时一定用引号括起来，否则索引失效
• like通配符可能导致索引失效。
• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
• 在索引列上使用mysql的内置函数，索引失效。
• 对索引列运算（如，+、-、*、/），索引失效。
• 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
• 索引字段上使用is null， is not null，可能导致索引失效。
• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
• mysql估计使用全表扫描要比使用索引快,则不使用索引。

索引不适合哪些场景

• 数据量少的不适合加索引
• 更新比较频繁的也不适合加索引
• 区分度低的字段不适合加索引（如性别）

索引的一些潜规则

• 覆盖索引
• 回表
• 索引数据结构（B+树）
• 最左前缀原则
• 索引下推

索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
参考
索引下推开关设置：

set optimizer_switch='index_condition_pushdown=on'// on是打开， off是关闭

锁

参考
参考

日常工作中你是怎么优化SQL的？

参考
可以从这几个维度回答这个问题：

• 加索引
• 避免返回不必要的数据
• 适当分批量进行
• 优化sql结构
• 分库分表
• 读写分离

1. 数据库的三范式是什么？

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
第三范式：任何非主属性不依赖于其它非主属性。

2. 一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几？

表类型如果是 MyISAM ，那 id 就是 8。
表类型如果是 InnoDB，那 id 就是 6。
InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。

3. 如何获取当前数据库版本？

使用 select version() 获取当前 MySQL 数据库版本。

4. 说一下 ACID 是什么？

Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

5. char 和 varchar 的区别是什么？

char(n) ：固定长度类型，比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。
chat 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。
所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

6. float 和 double 的区别是什么？

float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。
double 最可可以存储 16 位的十进制数，并在内存中占 8 字节。

7. MySQL 的内连接、左连接、右连接有什么区别？

内连接关键字：inner join；左连接：left join；右连接：right join。

内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

8. MySQL 索引是怎么实现的？

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。

具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

9. 怎么验证 MySQL 的索引是否满足需求？

使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。

explain 语法：explain select * from table where type=1。

10. 说一下数据库的事务隔离？

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：

transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。
脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读 ：是指在一个事务内，多次读同一数据。

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

11. 说一下 MySQL 常用的引擎？

InnoDB 引擎：mysql 5.1 后默认的数据库引擎，提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count(*) from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。

MyIASM 引擎：不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

12. 说一下 MySQL 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

13. 说一下乐观锁和悲观锁？

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。
数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

14. MySQL 问题排查都有哪些手段？

使用 show processlist 命令查看当前所有连接信息。
使用 explain 命令查询 SQL 语句执行计划。
开启慢查询日志，查看慢查询的 SQL。

15. 如何做 MySQL 的性能优化？

为搜索字段创建索引。
避免使用 select *，列出需要查询的字段。
垂直分割分表。
选择正确的存储引擎。