1 MySQL的总体架构是怎样的？

• MySQL分为服务器和存储引擎两部分

  • 服务器

    • 包括连接器、查询缓存、分析器、优化器、执行器等

    • 涵盖MySQL大部分核心服务

    • 实现MySQL所有的内置函数，如日期和时间函数、数学函数、加密和解密函数等

    • 提供所有不依赖具体存储引擎的功能，如存储过程、触发器、视图等

  • 存储引擎

    • 负责数据的存储和读取

    • 采用插件式结构，支持InnoDB、MyISAM等多种存储引擎

    • 自5.5.5版本以后，默认使用InnoDB存储引擎

    • 建表时可以通过“engine=MyISAM”人为指定存储引擎

    • 不同存储引擎的数据存取方式不同，所支持的功能也不同

    • 不同存储引擎可以共用同一个服务器

2 一条查询语句的执行流程是怎样的？

• 第一步：应用程序将查询语句字符串发送给MySQL服务器

• 第二步：如果查询缓存处于打开状态，则先在查询缓存中查找是否存在与查询条件匹配的结果，若存在则直接返回该结果，否则继续执行后面的步骤

• 第三步：解析查询语句，对查询语句做预处理，优化预处理结果，并生成查询计划

• 第四步：执行查询计划，得到查询结果，并将查询结果保存到查询缓存中

• 第五步：将查询结果返回给应用程序

3 数据库的三范式是什么？

• 第一范式：强调列的原子性，即表记录的每个字段都是不可分割的原子数据项

• 第二范式：实体的属性必须完全依赖于主关键字，即不允许存在一部分属性依赖，另一部分属性不依赖的情况

• 第三范式：任何非主属性不依赖于其它非主属性

4 char和varchar有何区别？

• char(n)

  • 固定长度为n的字符串，不足n的部分以空字符填充

  • 时间效率高，空间效率低，适用于存储长度固定的字符串信息，如用户密码的哈希摘要等

• varchar(n)

  • 最大长度为n的字符串，与字符串同时保存的，还有该字符串以字节为单位的实际长度

  • 时间效率低，空间效率高，适用于存储长度不定的字符串信息，如用户名等

5 varchar(10)和varchar(20)有何区别？

• varchar(10)和varchar(20)在存放实际长度相等的字符串时，所占用存储空间的大小是一样的

• 在涉及排序操作时，varchar(20)会比varchar(10)消耗更多内存，因为排序只能按最大长度分配内存

6 数据库中的索引是什么？

• 索引是一种对数据库表中的一列或多列进行排序和存储的数据结构

• 在索引中查找是在有序集中查找，比在无序集中查找快很多，就象二分查找快于线性查找

• 借助索引，在查询数据库时，可以快速定位到特定的记录，比面向全表的顺序扫描高效得多

• 索引也会带来很多负面影响

  • 索引本身也需要占用存储空间

  • 创建和维护索引需要消耗额外的时间，且随着数据量的增加而增加

  • 在对数据库表做增删改操作时，索引也需要被同步更新，进一步降低了数据库的操作速度

• 频繁使用，通过排序能显著缩小查询范围的列，适合创建索引

• 稀少使用，重复值较多，文本等特殊类型的列，不宜创建索引

7 数据库中的索引在底层采用何种数据结构？

• 索引采用的数据结构，与存储引擎的具体实现有关

• MySQL数据库中索引，较多采用散列表和B+树等数据结构

• InnoDB存储引擎的索引，默认采用B+树数据结构

8 什么是B树？

• 一棵$m$$m$阶B树必须满足以下条件：

  • 每个节点最多有$m-1$$m-1$个键

  • 根节点最少有$1$$1$个键，其它节点最少有$ceil(\frac{m}{2})-1$$ceil(\frac{m}{2})-1$个键

  • 节点中的键按从小到大排列，对于每个键，其左子树中的键都小于该键，其右子树中的键都大于该键

  • 所有叶节点位于同一层，包括叶节点在内的所有节点中的键，与各自的值相对应

• 以$3$$3$阶B树为例：

  • 每个节点最多有$2$$2$个键

  • 根节点最少有$1$$1$个键，其它节点最少有$1$$1$个键

    xxxxxxxxxx
    7
    1
                                [*17*35*]
    2
             ____________________|  |  |____________________
    3
            |                       |                       |
    4
         [*8*12*]               [*26*30*]               [*65*87*]
    5
       ___| |  |____         ____|  |  |____         ____|  |  |____
    6
      |     |       |       |       |       |       |       |       |
    7
    [3 5] [9 10] [13 15] [22 25] [28 29] [32 34] [36 60] [75 79] [90 99]
    

9 什么是B+树？

• B+树是在B树基础上的一种变形。一棵$m$$m$阶B树必须满足以下条件：

  • 每个节点最多有$m$$m$个键

  • 非叶根节点最少有$2$$2$个键，分支节点最少有$ceil(\frac{m}{2})$$ceil(\frac{m}{2})$个键

  • 节点中的键按从小到大排列，每个键对应一棵子树，且为该子树中的最大键

  • 所有叶节点位于同一层，彼此相连，叶节点包含了所有的键，每个键与各自的值相对应

• 以$4$$4$阶B+树为例：

  • 每个节点最多有$4$$4$个键

  • 非叶根节点最少有$2$$2$个键，分支节点最少有$2$$2$个键

    xxxxxxxxxx
    7
    1
      [                           60                85]
    2
                                   |                 |
    3
      [10       20       50       60]  [   77       85]
    4
        |        |        |        |        |        |
    5
    ->[10]->[16 20]->[40 50]->[55 60]->[69 77]->[80 85]
    6
        |     |  |     |  |     |  |     |  |     |  |
    7
        V     V  V     V  V     V  V     V  V     V  V
    

• 与B树不同，B+树的叶节点包含全部键并各自对应其值。因此在搜索时，即使在自根节点向下的某个分支节点中，找到了匹配的键，也不能停止搜索，而要继续向下，直到在叶节点中找到匹配的键，并获取其值为止

10 为什么InnoDB存储引擎采用B+树而非B树作为数据结构？

• 同为$m$$m$阶的B树和B+树，前者每个节点最多可以存放$m-1$$m-1$个键，而后者每个节点最多可以存放$m$$m$​个键，因此在数据量相同的情况下，B+树比B树的层数更少，高度更小

• 访问数据库，常用到区间查询，B+树更具优势，因为它的叶节点层本身，就是一个关于全部键值对的有序链表，而B树则往往需要多次地跨层访问，才能得到满足区间条件的全部值

• B+树中的值，即与每个键相对应的记录，只存在于叶节点中，且叶节点包含全部键，这有利于将存储在磁盘上的数据库索引，通过较少次的I/O操作，按页加载到内存

11 数据库中的聚簇索引是什么？

• 聚簇索引的特点是叶节点在磁盘上的存储顺序，与其在索引树中的逻辑顺序完全一致

• 一般情况下，每张表的主键都会默认创建聚簇索引，且一张表只允许有一个聚簇索引

• 聚簇索引的叶节点本身就是将数据节点，而非聚簇索引的叶节点仍然是索引节点，其中包含指向数据块的指针

12 数据库中的哈希索引是什么？

• 一般索引是利用数据的有序性，提高搜索速度，就象二分查找快于线性查找

• 哈希索引是利用数据的散列性，在常数时间内，完成精确查找

• 因为失去的有序性，哈希索引不支持排序、分组、部分查找和范围查找

• InnoDB存储引擎具有自适应哈希索引功能。如果针对某个键的精确查找非常频繁，它会在B+树的基础上再创建一个散列表，这样就使得基于B+树的索引，也兼具了哈希索引的优点，以支持快速的哈希查找

13 数据库中的覆盖索引是什么？

• 如果一条查询语句的执行，仅从索引中就能获得所需要的全部结果，而无需再读取数据文件，这样的索引就叫覆盖索引，即索引覆盖了所查询的列

• 覆盖索引的优点

  • 索引的数据量远小于数据表，只读取索引不读取数据表，可以降低磁盘I/O的开销

  • 某些存储引擎，如MyISAM，只在内存中缓存索引，对数据文件的缓存则依赖于操作系统。只读取索引不读取数据表，可以减少系统调用的次数

  • 对于InnoDB存储引擎，如果辅助索引能够覆盖查询，则无需访问主索引

14 数据库中的索引分为几种类型？

• 数据库中的索引，按数据结构的不同可被分为，树索引和哈希索引两种

• 数据库中的索引，按存储结构的不同可被分为，聚簇索引和非聚簇索引两种

• 数据库中的索引，按逻辑结构的不同可被分为，普通索引、唯一索引、主键索引、联合索引和全文索引五种

15 什么是最左前缀原则？

• MySQL数据库中的联合索引，遵循最左前缀原则。例如，为了在$name$$name$、$age$$age$和$sex$$sex$三列上创建索引，就需要构建针对$name$$name$、$(name,age)$$(name,age)$及$(name,age,sex)$$(name,age,sex)$的搜索树

  • 在这样的索引中查找$(john,22,male)$$(john,22,male)$，MySQL会先在针对$name$$name$的搜索树中查找$john$$john$，再在针对$(name,age)$$(name,age)$的搜索树中查找$(john,22)$$(john,22)$，最后在针对$(name,age,sex)$$(name,age,sex)$的搜索树中查找$(john,22,male)$$(john,22,male)$

  • 如果要查找的是$(22,male)$$(22,male)$，MySQL就不知道查找哪棵搜索树，因为并没有创建针对$(age,sex)$$(age,sex)$的搜索树

  • 如果要查找的是$(john,male)$$(john,male)$，MySQL会先在针对$name$$name$的搜索树中找出所有的$john$$john$，再通过逐一匹配，找到其中$sex$$sex$为$male$$male$​的记录

• 查询语句中的“$=$$=$”和“$in$$in$”对顺序没有限制。例如，已在$(a,b,c,d)$$(a,b,c,d)$上创建索引，则查询语句的条件无论是“$a=1andb=2andc=3andd=4$$a=1\ and\ b=2\ and\ c=3\ and\ d=4$”还是“$a=1andc=3andd=4andb=2$$a=1\ and\ c=3\ and\ d=4\ and\ b=2$”，甚或“$d=4andc=3andb=2anda=1$$d=4\ and\ c=3\ and\ b=2\ and\ a=1$”，针对“$a=1$$a=1$“、”$b=2$$b=2$“、”$c=3$$c=3$“和”$d=4$$d=4$”的查找都用使用该索引，MySQL的优化器会将查询条件优化为与索引匹配的形式

• 范围查询，如“$>$$>$”、“$<$$<$”、“$between$$between$”、“$like$$like$”等，会阻断索引匹配。例如，已在$(a,b,c,d)$$(a,b,c,d)$上创建索引，而查询条件为“$a=1andb=2andc>3andd=4$$a=1\ and\ b=2\ and\ c>3\ and\ d=4$”，这种情况下，针对“$d=4$$d=4$”的查找是不会使用索引的。而如果是在$(a,b,d,c)$$(a,b,d,c)$上创建索引，则查询条件中$a$$a$、$b$$b$、$d$$d$的顺序可以随意调整

16 怎么知道一条SQL语句在执行过程中是否使用了索引？

• MySQL在执行一条SQL语句之前，会先对该语句进行解析、预处理和优化，并生成执行计划

• 通过explain命令可以查看有关特定SQL语句的执行计划，其中就包含与命中索引有关的细节

• 重点关注possible_key、key和key_len这样的字段，它们分别表示可能会用到的索引键、实际使用的索引键和索引键的长度

17 哪些查询语句不使用索引？

• 索引字段参与表达式计算，不使用索引

  xxxxxxxxxx
  1
  1
  SELECT name FROM t_student WHERE age+10=30;
  

• 索引字段作为函数的参数，不使用索引

  xxxxxxxxxx
  1
  1
  SELECT name FROM t_student WHERE LEFT(bday,4)<1990;
  

• 模式查询，不使用索引

  xxxxxxxxxx
  1
  1
  SELECT * FROM t_user WHERE username LIKE "%码农%";
  

  但如果写成下面这样，则可以使用索引：

  xxxxxxxxxx
  1
  1
  SELECT * FROM t_user WHERE username LIKE '%码农%';
  

• 字符串与数字比较，不使用索引

  xxxxxxxxxx
  6
  1
  CREATE TABLE `t_employee` (
  2
    `id` varchar(64),
  3
    ...
  4
  );
  5
  
  6
  SELECT * FROM t_employee WHERE id=127;
  

  但如果写成下面这样，则可以使用索引：

  xxxxxxxxxx
  1
  1
  SELECT * FROM t_employee WHERE id="127";
  

• 查询条件中含有“或”逻辑，不使用索引

  xxxxxxxxxx
  1
  1
  SELECT name FROM t_student WHERE name="john" OR age=22;
  

  不使用$(name,age)$$(name, age)$上的索引，但可以使用$name$$name$和$age$$age$上的索引

• 正则表达式，不使用索引

• MySQL在优化SQL语句的过程中发现，全表扫描比使用索引还要快，这种情况下，不使用索引

18 如何优化查询语句的性能？

• 只查询必要的字段，慎用“SELECT *”

• 只查询必要的记录，擅用“LIMIT”

• 经常性的重复查询，将查询结果保存到缓存中，以后直接从缓存中获取查询结果

• 使用索引覆盖所要查询的所有字段

19 InnoDB和MyISAM两种存储引擎有何区别？

20 什么是水平分表和垂直分表？

• 水平分表是将同一张表中的记录，分开存放在结构相同的多张表中，这些表分布于集群的不同节点上，以此来缓解单个数据库的压力

• 垂直分表是将一张表按列拆分为多张表，可以根据列的相关性拆分，也可以根据列的使用频次拆分，旨在提高对数据库表的访问效率

21 主从复制涉及到哪几个线程？

• binlog线程：将主服务器上的数据更改写入二进制日志文件

• I/O线程：读取主服务器的二进制日志文件，写入从服务器的重放日志文件

• SQL线程：读取从服务器的重放日志文件，执行其中的SQL语句

22 什么是主从同步延迟？如何缩短主从同步延迟？

• 主服务器上随时可能有多个线程并发地执行对数据的更改操作，而从服务器上的binlog线程只有一个。一旦从服务器在执行某条SQL语句时，耗时稍长，或因锁而等待，就会导致主服务器中的大量SQL被积压，未能及时同步到从服务器里，这就叫是主从同步延迟

• 主从同步延迟从根本上是不可避免的，比较可行的做法是让从服务器的SQL执行速度尽可能快

  • 一般而言，从服务器对数据安全性的要求比主服务器低，因此可以将从服务器的sync_binlog和innodb_flush_log_at_trx_commit都设为0，或者直接关闭从服务器的binlog和innodb_flushlog，以提高其SQL执行效率

  • 适当增加从服务器节点的数量，降低单台服务器的工作载荷，提高其SQL执行速度

23 什么是数据库的读写分离？

• 代理服务器负责接收来自应用服务器的数据库访问请求，并决定将其转发给哪台数据库服务器

  • 对于执行写操作，或实时性要求比较高的读操作的数据库访问请求，转发给主服务器

  • 对于执行实时性不高的读操作的数据库访问请求，转发给从服务器

• 读写分离的好处在于

  • 主从服务器各自执行对数据库的写入和读取操作，缓解了对锁的竞争

  • 以执行读操作为主的从服务器，可以使用MyISAM存储引擎，有助于提升查询性能，节约系统资源

  • 保持适度冗余，既能均衡化负载，又能提高系统可用性

24 数据库事务都有哪些特性？

• 原子性：事务是最小的执行单位，不可再分割，要么全部完成，要么完全无效，不允许部分完成，部分无效

• 一致性：执行事务前后，数据库从一个一致性状态，转换为另一个一致性状态

• 隔离性：并发访问数据库时，每个并发过程中的事务都是彼此独立的，相互不构成影响

• 持久性：事务一旦被提交，其对数据库的改变即是持久的，即使数据库发生故障，也不应对其有任何影响

25 什么是数据库的脏读、幻读和不可重复读？

• 脏读：一个事务读取到另一个事务尚未提交的数据

• 幻读：在一个事务内多次执行相同的查询，由于其它事务已提交的增删操作，导致每次查询到的结果集不一致

• 不可重复度：一个事务在读取某个数据的同时，另一个事务修改了该数据并提交，导致两次读取的结果不一致

26 什么是数据库的事务隔离级别？

• 未提交读（READ_UNCOMMITTED）

  • 最低隔离级别

  • 允许读取并发事务尚未提交的数据

  • 可能导致脏读、幻读和不可重复读

• 提交读（READ_COMMITTED）

  • 允许读取并发事务已提交的数据

  • 可以阻止脏读，但幻读和不可重复读仍有可能发生

• 可重复读（REPEATABLE_READ）

  • 多次读取同一数据所得结果相同，除非被当前事务自己修改

  • 可以阻止脏读和不可重复度，但幻读仍有可能发生

• 串行化（SERIALIZABLE）

  • 最高隔离级别，完全满足ACID的要求

  • 所有事务依次逐个执行，事务间完全不发生干扰

  • 可以阻止脏读、幻读和不可重复读

  • 严重影响性能，很少使用

27 MySQL和Oracle数据库默认的事务隔离级别分别是什么？

• MySQL数据库默认的事务隔离级别是可重复读（REPEATABLE_READ）

• Oracle数据库默认的事务隔离级别是提交读（READ_COMMITTED）

28 什么是MVCC？

• 数据库的事务并发场景

  • 同时读：不存在任何问题，也不需要任何并发控制

  • 读的同时写：可能导致脏读、幻读和不可重复读等事务隔离性问题

  • 同时写：可能导致更新丢失问题

• MVCC即多版本并发控制，是一种用来解决读写冲突的无锁并发机制

  • 为事务分配单向增长的时间戳

  • 为每次修改保存一个版本，版本与事务的时间戳关联

  • 读操作只读取当前事务开始前的数据库快照

• MVCC可以解决与并发事务有关的以下问题：

  • 同时读写，无需阻塞，提高并发读写数据库的效率

  • 解决了脏读、幻读和不可重复读等问题，但不能解决更新丢失问题

29 什么是MySQL数据库的行锁和表锁？

• MyISAM存储引擎只支持表锁，InnoDB存储引擎既支持表锁，也支持行锁，默认为行锁

• 表锁：开销小，加锁快，不会发生死锁，锁定粒度大，发生锁冲突的概率高，并发性差

• 行锁：开销大，加锁慢，可能发生死锁，锁定粒度小，发生锁冲突的概率低，并发性好

30 InnoDB存储引擎都支持哪些行锁？

• 记录锁（Record Lock）：对索引记录加锁，防止其它事务更新或删除被锁定的记录

  xxxxxxxxxx
  6
  1
                      记录锁
  2
                        |
  3
  ->[10]->[16 20]->[40 50]->[55 60]->[69 77]->[80 85]
  4
      |     |  |     |  |     |  |     |  |     |  |
  5
      V     V  V     V  V     V  V     V  V     V  V
  6
                        X
  

• 间隙锁（Gap Lock）：对索引记录的间隙加锁，防止其它事务在被锁定间隙处插入

  xxxxxxxxxx
  6
  1
                      间隙锁
  2
                        |
  3
  ->[10]->[16 20]->[40 50]->[55 60]->[69 77]->[80 85]
  4
      |     |  |     |  |     |  |     |  |     |  |
  5
      V     V  V     V  V     V  V     V  V     V  V
  6
   X     X   X    X   X
  

• 临键锁（Next-Key Lock）：对索引记录的间隙和索引记录加锁，既防止其它事务在被锁定间隙处插入，也防止其它事务更新或删除被锁定的记录

  xxxxxxxxxx
  6
  1
                      临键锁
  2
                        |
  3
  ->[10]->[16 20]->[40 50]->[55 60]->[69 77]->[80 85]
  4
      |     |  |     |  |     |  |     |  |     |  |
  5
      V     V  V     V  V     V  V     V  V     V  V
  6
   X     X   X    X   X X
  

31 MySQL数据库的问题排查都有哪些手段？

• 通过“show processlist”命令，查看所有连接数据库的进程

• 通过“explain”命令查看SQL语句执行计划

• 开启慢查询日志，找出导致数据库访问速度变慢的查询语句

32 MySQL数据库的处理器占用突然飙升怎么处理？

• 通过“show processlist”命令，查看所有连接数据库的进程，把状态长时间不变的进程杀死

• 处理器占用飙升通常发生在查询及大批量插入时，结合操作特征查看超时日志和错误日志

• 网络意外中断，导致请求接收不完整，也可能导致处理器占用飙升