面试MySQL

文章目录

• • 综述
  • MySQL概述
  • • Oracle 和 MySQL 区别
    • 对MySQL框架的了解？
    • 一条SQL语句在数据库框架中的执行流程？
    • 数据库的三范式是什么？
    • 专业术语 或 关键词
    • MySQL问题排查都有哪些手段？
    • MySQL数据库CPU飙升到500%的话如果处理？
    • MySQL的redo log、undo log、bin log都是干什么的？
    • MySQL与SQL有什么区别？
  • 数据库 基本操作 及原理
  • • 对水平切分和垂直切分的理解？
    • 谈谈你对数据库读写分离的理解？
    • 为什么要分库分表 水平切分？
    • 分库分表有哪些问题？
    • MySQL读写分离的实现方案？
    • 主从复制中涉及哪三个线程？
    • 主从同步的延迟原因及解决办法？
  • 数据类型
  • • char 和 varchar 的区别？
    • varchar（10） 和 varchar（20） 的区别？
  • 数据表 基本操作
  • • 什么是超键？什么是主键？
    • UNIOn 与 UNIOn ALL的区别？
    • DROp DELETE TRUNCATE的区别？
  • 操作 表中的数据
  • • 查询性能的优化方法？
    • sql调优
  • 视图
  • 索引
  • • 索引的分类？
    • 谈谈你对索引的理解？
    • 索引的底层使用的是什么数据结构？
    • 谈谈你对B+树 的理解？
    • 对哈希索引的理解？
    • 聚集索引 和 辅助索引
    • 对聚簇索引、稀疏索引 的理解？
    • 对覆盖索引的理解？
    • 对最左前缀原则的理解？
    • 怎么知道创建的索引有没有被使用到？或者说怎么才能知道这条语句运行很慢的原因
    • 什么情况下索引会失效？即查询不走索引？
    • 添加索引的原则？
  • 事务
  • • 描述下事务的特性？
    • 谈谈你对事务隔离级别的理解？
    • 什么叫脏读、不可重复读和幻读？
    • MySQL默认的隔离级别是什么？
    • 有哪些事务状态？
  • 存储索引
  • • 为什么InnorDB存储索引采用B+树 而不是 B 树？
    • InnoDB 和 MyISAM的比较？
    • InnoDB存储引擎的锁的类型有哪些？
  • 约束
  • • 你对MVCC的了解？
    • MySQL的行锁和表锁？
    • 什么是死锁？如何解决死锁？
    • 什么是乐观锁和悲观锁？如何实现？

综述

数据库思维导图
MySQL思维导图

MySQL概述 Oracle 和 MySQL 区别

两个都是

区别	MySQL	Oracle
公司	瑞典MySQL AB公司 又被sun收购 又被Oracle收购	甲骨文公司
类型	中小型数据库	大型数据库
费用	开源免费	收费
默认端口号	3306	1521
数据库与用户关系	一个用户对一个多个数据库	一个数据库对应多个用户
字段自增	设置字段为auto increment	创建序列sequence，添加 序列名.nextval()
字符	varchar	varchar2
数字类型	smallint(2字节) int（4字节） integer（int同义词） bigint（8字节）	number
分页	select * from table limit（start-1）*limiti，limiti start 页码，limiti是每页显示的条数	使用伪列rownum

对MySQL框架的了解？

MySQl可以分为 Server层 和 存储引擎 两部分。

• Server层：
  涵盖MySQL的大多数核心功能，以及所有的内置函数，所有跨引擎的功能都在这一层实现；
• 存储引擎：
  数据的存储和提取。
  构件是插件式的。存储引擎有InnoDB、MyISAM、memory；
  MySQL5.5.5 默认是InnoDB，可以通过engine = MyISAM来指定存储引擎；
  
  Server层组成部分介绍：

成员	作用
连接器	管理连接，权限验证
查询缓存	命中则直接返回结果
分析器	词法分析、语法分析
优化器	执行计划生成，索引选择
执行器	操作引擎，返回结果

一条SQL语句在数据库框架中的执行流程？

执行过程：

• 应用程序把查询SQL语句发送给服务器端执行;
• 检查缓存中是否存在该查询。存在，返回缓存中的结果；否则，执行下一步；
• 进行SQL的解析、语法检测和预处理，再由优化器生成对应的执行计划；
• MySQL根据执行计划，调用存储引擎的接口进行查询；
• 最终将查询结果返回给客户端。

数据库的三范式是什么？

名字	含义	解释
第一范式（1NF）	所有域都应该是原子性的 每个列都是不可拆分的 数据库表的每一列都是不可分割的原子数据项，而不是集合、数组、记录等非原子数据项	实体的某个属性有多个值时，必须拆分为不同的属性
第二范式（2NF）	非主键列完全依赖于主键列，不依赖于其他非主键	要求数据库表中的每个实例或记录必须可以被唯一地区分
第三范式（3NF）	任何非主属性不依赖于其他非主属性	要求一个关系中不包含其它关系已包含的非主键字信息 举例：部门信息表中，主键=部门编号，其余信息有部门名称、部门简介等；员工信息表中列出部门编号后，不再列出部门名称、部门间接等信息

专业术语 或 关键词

DB：Database 数据库
DBMS：Database Management System 数据库管理系统
RDBMS：Relative Database Management System 关系型数据库管理系统
数据库：是表的集合，带有相关的数据；
表：一个表是一个数据的矩阵；
列（字段）：一个列（数据元素）包含同一类型的数据；
行（记录）：一组相关的数据；

MySQL问题排查都有哪些手段？ MySQL数据库CPU飙升到500%的话如果处理？ MySQL的redo log、undo log、bin log都是干什么的？ MySQL与SQL有什么区别？ 数据库 基本操作 及原理

查看、创建、修改、删除数据库。

对水平切分和垂直切分的理解？

• 数据库拆分原则：
  通过某种特定的条件，按照某个维度，将同一个数据库中的数据分散存放到多个数据库（主机），以达到分散单库（主机）负载的效果；
• 拆分模式：
  垂直（纵向）拆分、水平拆分。

垂直拆分：专库专用

介绍	说明
概念	按照业务将表分类，分布到不同的数据库上
可解决问题	降低单节点数据库的负载
不能解决问题	缩表，即每个数据库里面的数据量是没有发生变化的
优点	拆分后业务清晰，拆分规则明确 系统之间整合或者扩展容易 数据维护简单
缺点	部分业务表无法join，只能通过接口方式解决，提高了系统复杂度 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。 事务处理复杂

水平拆分 ：分库分表

• 概念：
  把同一个表拆到不同的数据库中；
  不是将表做分类，而是按照某个字段的某种规则来分散到多个库中，每个表中包含一部分数据；
  简单理解 = 按照数据行的切分，将某些行又切分到其他的数据库中；
• 误解：
  水平切分出来的数据表必须保存在不同的MySQL节点上；
  其实水平切分出来的数据表也可以保存在一个MySQL节点上；
• 为什么说水平切分不一定需要多个MySQL节点呢？
  MySQL自带一种数据分区的技术，可以把一张表的数据，按照特殊规则，切分存储在不同的目录下。
  若给Linux主机挂载了多块硬盘，可以利用MySQL分区技术，把一张表的数据切分存储在多个硬盘上。
  这样就由原来一块硬盘有限的IO能力，升级成了多个磁盘增强型的IO。

谈谈你对数据库读写分离的理解？ 为什么要分库分表 水平切分？ 分库分表有哪些问题？ MySQL读写分离的实现方案？ 主从复制中涉及哪三个线程？

• 主从复制概念：
  数据库集群，包括主数据库和从数据库。
  用户更新数据只去主数据库更新，查询数据只去从数据库查询。即更新操作仅在主数据库上，之后从数据库一起更新，查询操作仅在从数据库上。
  这样保证了读写分离，不会出现因为在高并发的情况下造成的读写冲突。

• 涉及三个线程：

线程	介绍
Binlog Dump线程 主节点 （master）	负责将主节点上的数据更改写入二进制日志（bin log） 创建的该线程使用show processlist命令在主节点的标准输出上以“Binlog Dump”名字显示 读取主节点上即将被发送到从节点的每一个事件(Event)，binlog Dump线程会对该binlog添加一个锁，直到这个事件被读取到甚至发送到从节点上，这个锁才会被释放。
I/O线程 从节点（Slave）	当START SLAVE语句在从节点上发出时，从节点创建一个I/O线程； 用来连接配置好的主节点并请求其发送bin log中的更新记录 I/O线程读取从binlog Dump线程发送过来的更新信息，将其复制到本地文件内(Relay log) 。
SQL线程 从节点(Slave)	从节点创建一个Slave SQL线程去读取由Slave I/O创建的relay log中的内容，将读取到的事件在从节点上进行重现。

• 主从复制工作原理：
  master服务器将数据的改变记录二进制bin log日志，当master上的数据发生改变时，则将其改变写入二进制日志中;
  slave服务器会在一定时间间隔内对master二进制日志进行探测，判断其是否发生改变，如果发生改变，则开始一个I/O线程请求master二进制事件；
  主库会生成一个 log dump 线程，用来给从库 I/O线程传bin log；
  I/O线程去请求主库 的bin log，并将得到的bin log日志写到relay log（中继日志） 文件中；
  SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

主从同步的延迟原因及解决办法？ 数据类型

数值类型、字符串类型、转义字符、日期/时间、二进制类型、变量。

char 和 varchar 的区别？

区别	char	varchar
长度	固定 char(10) 字符串"abc" 存储10个字节，其中7个是空格	可变 varchar(10) 字符串"abc" 只占三个字节
效率	较高	较低
节省空间	–	比char节省空间
存储类型	–	varchar是oracle开发的数据类型 工业标准可以存储空字符串；oracle还可以存储NULL值

varchar（10） 和 varchar（20） 的区别？

varchar（10）最多放10个字符，因此varchar（10）和varchar（20）存储10个及10个以内的字符所占空间一样；
但后者在排序时会消耗更多内存，因为order by col 采用fixed_length计算col长度。
fixed_length 固定长度；

数据表 基本操作

创建、查看、修改数据表结构。

什么是超键？什么是主键？ UNIOn 与 UNIOn ALL的区别？ DROp DELETE TRUNCATE的区别？ 操作 表中的数据

查询、增加、修改、删除、清空表记录。

查询性能的优化方法？

• 减少请求的数据量
  只返回必要的列：尽量避免使用select * 语句；
  只返回必要的行：使用limit语句来限制返回的数据；
  缓存重复查询的数据；

• 减少服务端扫描的行数
  通过索引来覆盖查询；

sql调优

• 创建索引：
  尽量避免全表扫描，应考虑where 或order by
• 避免在索引上使用计算
  在where语句中，如果索引列是计算或者函数的一部分，DBMS的优化器将不会使用索引而使用全表查询
• 使用预编译查询
  程序中通常是根据用户的输入来动态执行SQL，这时要尽量使用参数化SQL，可以避免SQL注入漏洞；
  数据库会对参数化SQL进行预编译，以后再执行直接使用预编译的结果即可。
• 调整where子句中的连接顺序
  DBMS一般采用自上而下的顺序解析where子句；
• 尽量将多条sql语句压缩到一句sql中
  每次执行sql的时候要经历如下过程：建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果
  该过程非常耗时。
• 用where子句替换Having子句
  避免使用having，因为having只会在检索出所有记录之后才对结果集进行过滤，而where是在聚合前；
  如果可以使用where减少记录的数目，就可以减少开销；
• 使用表的别名
  sql连接多个表，使用表的别名 可以减少解析的时间 并减少不同表的同名列名的语法歧义
• 用union all 替换union
• 用varchar/nvarchar 替换char / nchar
  变长字段存储空间小，可以节省存储空间；
  对于查询来说，在一个相对较小的字段内搜索效率高；

视图

创建、查看、修改、删除视图。

索引

创建索引实例、普通索引、唯一索引、主键索引、组合索引、全文索引。(逻辑角度)

索引的分类？

数据结构角度分类：树索引（B+tree）、hash索引
物理存储角度：聚集索引、非聚集索引
逻辑角度分类：普通索引、唯一索引、主键索引、联合索引、全文索引

谈谈你对索引的理解？

• 作用：
  索引的出现是为了提高查询效率，相当于数据库的目录；
  是对查询性能优化最有效的有段；

• 概念：
  在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构；

• 原理：
  通过不断缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机事件变成顺序的事件；

• 分类：
  hash类型的索引：查询单条快，范围查询慢；
  b-tree类型的索引：b+树，层数越多，数据量指数级增长；

• 缺点：
  创建索引和维护索引需要耗费时间；
  对数据增、删、改时，索引也需要动态维护，降低数据的维护速度；
  随着数据量的增大，索引需要占的物理空间也会越大；

• 建立原则：
  经常被查询的字段：where子句中出现的；
  在分组的字段：即在group by子句中出现的；
  存在依赖关系的子表和父表之间的联合查询，即主键或外键字段；
  设置唯一完整性约束字段；

• 不适合建立索引：
  查询中很少被使用的列 或者 重复值较多的列，不宜建立索引；
  一些特殊的数据类型， 不宜建立索引。比如:文本字段(text) 等。

索引的底层使用的是什么数据结构？

索引的数据结构 和 具体存储引擎 的实现有关；
MySQL中使用较多的索引有 Hash索引、B+ 树 索引等；
则对应索引的底层使用的就是 Hash表 、 B + 树；

谈谈你对B+树 的理解？

图示：

说明	根节点	分支节点 除根节点、叶子节点	叶子节点
儿子数	有M个儿子则有m个元素	有M个儿子则有m个元素	–
存储内容	关键字（索引）	关键字（索引）	关键字、指针、数据
存储	–	–	所有根节点、分支节点都存在于子节点中，是最大值或最小值
	–	–	包含所有关键字、指向数据记录的指针 叶子节点本身是根据关键字从小到大顺序链接的

作用：
优势1 = 更加高效的单元素查找。

对哈希索引的理解？

• 概念：
  哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效；
  对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码是不一样的。
  在 MySQL中，只有 Memory 引擎显式支持哈希索引。

• 优点：
  能以 O(1) 时间进行查找、只支持精确查找

• 缺点：
  失去了有序性（指的是数据表的顺序）。无法用于排序与分组，无法用于部分查找和范围查找。

• 案例：
  

• 限制：
  哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来直接读取行；
  哈希索引数据（表的行数据）并不是按照索引值顺序存储的；
  哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。（在数据列（A，B）上建立哈希索引，如果查询只有数据列A，则无法使用该索引。）
  哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作）、也不支持任何范围查询；
  访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）
  如果哈希冲突很多的话，一些索引维护操作的代价也会很高

聚集索引 和 辅助索引

聚集索引 = 主键索引
聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的是整张表的记录数据。
聚集索引的叶子节点称为数据页，聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

辅助索引 = 二级索引
非主键索引，叶子节点=键值+书签。
Innodb存储引擎的书签就是相应行数据的主键索引值。

对聚簇索引、稀疏索引 的理解？

• 聚簇索引：
  是对磁盘上实际数据重新组织，按照指定的一个或多个列的值 排序的算法。
  特点是存储数据的顺序和索引顺序一致。
  一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

聚簇索引 和 非聚簇索引（稀疏索引）区别：
聚簇索引：即叶子结点中包含所有完整行记录，叶子节点中包含索引及其他所有字段信息，也就是数据节点；InnoDB 存储引擎中以主键索引构建的 B+tree 即为聚簇索引，
非聚簇索引：其他的皆为稀疏索引；叶子节点仍然是索引节点；

• 稀疏索引：
  二级索引、联合索引、MyISAM存储引擎的索引全是稀疏索引。

对覆盖索引的理解？

• 概念：
  select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列已被所使用的索引覆盖。
  索引是高效找到行的一个方法，覆盖索引 = 通过检索索引便可读取想要的数据。

• 优点：
  索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
  一些存储引擎（例如：MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
  对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

对最左前缀原则的理解？

概念：
MySQL使用联合索引时，需要满足最左前缀原则。
该原则以最左边的为起点，任何连接的索引都得匹配上。
当创建（a，b，c）复合索引时，想要索引生效的时候，只能使用a、ab、abc三种组合。
案例：

# 创建的联合索引是 sex age name 使用最左前缀原则。
SELECT * FROM user where age="4"; #未使用索引 第一个是sex
SELECt * FROM user where name="2"; #未使用索引 第一个是sex
SELECt * FROM user where sex="2" and age="3"; #使用索引 使用索引sex age 
SELECt * FROM user where sex="2" and age="3" and name="4"; #使用索引 使用索引 sex age name
SELECt * FROM user where age="3" and name="4";  #未使用索引 第一个得是sex
SELECt * FROM user where sex="2" and name="4";  #使用索引 只是用了sex索引

怎么知道创建的索引有没有被使用到？或者说怎么才能知道这条语句运行很慢的原因

MySQL提供了explain命令来查看语句的执行计划，
MySQL在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。

可以通过其中和索引有关的信息来分析是否命中了索引，例如possilbe_key，key，key_len等字段，分别说明了此语句可能会使用的索引，实际使用的索引以及使用的索引长度。

什么情况下索引会失效？即查询不走索引？

查询不走索引的情况：

• where子句使用or连接条件，且两字段有一个没有索引，会使引擎放弃索引
• 对于多列索引，不使用第一部分，则不会使用索引
  使用联合查询，注意最左侧原则，联合索引（a，b，c），where子句只有用到a才会用到联合索引，用到b、c引擎会放弃索引
• where子句使用like模糊查询，以%开头，会使引擎放弃索引
• 列类型是字符串，条件中要将数据使用引号引起来，否则不使用索引
• mysql评估使用全表扫描比使用索引快，则不使用索引
• where子句中对null值判断会导致引擎放弃索引
• where子句中使用！=,<>这样的符号，会使引擎放弃索引
• where使用between，只能是连续的数值（0,1）
• where中避免使用in，两种情况：in（1）走索引、in（1,2）不走索引
• where子句中等号 左侧使用表达式操作 或 函数操作，会使引擎放弃索引（索引参与表达式计算、索引参与函数运算）
• 正则表达式不走索引

添加索引的原则？ 事务 描述下事务的特性？ 谈谈你对事务隔离级别的理解？ 什么叫脏读、不可重复读和幻读？ MySQL默认的隔离级别是什么？ 有哪些事务状态？ 存储索引

MyISAM、InnoDB、Memory。

为什么InnorDB存储索引采用B+树 而不是 B 树？

• B+树空间利用率更高，可减少I/O次数。
  索引本身也很大，不可能全部存储在内存中，因此索引往往以文件的形式存储的磁盘上。
  索引查找过程中就要产生磁盘I/O消耗。
  B+树的内部节点只是作为索引使用，而不像B树那样每个节点都需要存储硬盘指针。
  B+树中每个非叶节点没有指向某个关键字具体信息的指针，所以每一个节点可以存放更多的关键字数量，即一次性读入内存所需要查找的关键字也就越多，减少了I/O操作。
• 增删文件(节点)时，效率更高
  B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率，基于范围查询更好。
• B+树的查询效率更加稳定
  B+树的每次查询过程中，都需要遍历从根节点到叶子节点的某条路径。所有关键字的查询路径长度相同，导致每一次查询的效率相当。

InnoDB 和 MyISAM的比较？

区别	InnoDB	MyISAM
事务	支持	不支持
锁	支持行级锁	支持表锁
全文索引	不支持	支持
外键	不支持	支持
count()	不支持直接存储总行数，需要按行扫描	支持直接存储总行数
适用范围	–	小型应用、效率较高
–		表保存成文件形式，跨平台更方便 管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择
–	用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择。

InnoDB存储引擎的锁的类型有哪些？ 约束

主键约束、外键约束、唯一约束、检查约束、非空约束、默认值约束。

你对MVCC的了解？ MySQL的行锁和表锁？ 什么是死锁？如何解决死锁？ 什么是乐观锁和悲观锁？如何实现？