面试MySQL

综述

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

MySQL概述

Oracle 和 MySQL 区别

两个都是

对MySQL框架的了解？

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

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

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

执行过程：

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

数据库的三范式是什么？

专业术语 或 关键词

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

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

MySQL数据库CPU飙升到500%的话如果处理？

MySQL的redo log、undo log、bin log都是干什么的？

MySQL与SQL有什么区别？

数据库 基本操作 及原理

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

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

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

垂直拆分：专库专用

水平拆分 ：分库分表

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

谈谈你对数据库读写分离的理解？

为什么要分库分表 水平切分？

分库分表有哪些问题？

MySQL读写分离的实现方案？

主从复制中涉及哪三个线程？

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

• 涉及三个线程：

• 主从复制工作原理：
  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 的区别？

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+树 的理解？

图示：

作用：
优势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索引
1
2
3
4
5
6
7

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

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存储引擎的锁的类型有哪些？

约束

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

你对MVCC的了解？

MySQL的行锁和表锁？

什么是死锁？如何解决死锁？

什么是乐观锁和悲观锁？如何实现？