mysql面试问题的坑_Mysql精华总结，解决测试人员面试中的碰到的一切Mysql问题（一）...

一、MySQL架构相关

问题1：画出 MySQL 架构图

答：和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等

引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取

存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

问题2：MySQL的查询过程 (一条sql语句在MySQL中如何执行)?

答：客户端请求 ---> 连接器(验证用户身份，给予权限) ---> 查询缓存(存在缓存则直接返回，不存在则执行后续操作) ---> 分析器(对SQL进行词法分析和语法分析操作) ---> 优化器(主要对执行的sql优化选择最优的执行方案方法) ---> 执行器(执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口) ---> 去引擎层获取数据返回(如果开启查询缓存则会缓存查询结果)

二、存储引擎相关

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。使用哪一种引擎可以灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。MySQL服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎 。

问题3：说说MySQL有哪些存储引擎?都有哪些区别?

答：常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键，他们的区别如下:

1、InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一;

2、InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败;

3、InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4、InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快;

5、InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一;

对比项MyISAMInnoDB主外键不支持支持事务不支持支持行表锁表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作缓存只缓存索引，不缓存真实数据不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响表空间小大关注点性能事务默认安装是是

问题4：一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ?

答：如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失;

如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

问题5：哪个存储引擎执行 select count(*) 更快，为什么?

答：MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。

在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。

InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上?这跟 InnoDB 的事务特性有关，由于多版本并发控制(MVCC)的原因，InnoDB 表“应该返回多少行”也是不确定的。

三、数据类型相关

问题6：CHAT和VARCHAR的区别？

答：char是固定长度，varchar长度可变：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间;而后者会根据实际存储的数据分配最终的存储空间

相同点：

1、char(n)，varchar(n)中的n都代表字符的个数

2、超过char，varchar最大长度n的限制后，字符串会被截断。

不同点：

1、char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1(实际长度length，0<=length<255)或加2(length>255)。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度(如果列声明长度大于255则使用两个字节来保存长度)。

2、能存储的最大空间限制不一样：char的存储上限为255字节。char在存储时会截断尾部的空格，而varchar不会。char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。

问题7：列的字符串类型可以是什么？

答：字符串类型是：SET、BLOB、ENUM、CHAR、CHAR、TEXT、VARCHAR

问题8：BLOB和TEXT有什么区别？

答：BLOB是一个二进制对象，可以容纳可变数量的数据。有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB;

TEXT是一个不区分大小写的BLOB。四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。

BLOB 保存二进制数据，TEXT 保存字符数据。

四、索引相关

问题9：说说你对MYSQL索引的理解？

答：MYSQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等。

索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上，平常说的索引，没有特别指明的话，就是B+树(多路搜索树，不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，符合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。此外还有哈希索引等。

问题10：说说使用索引的优劣是什么？

答：优势

提高数据检索效率，降低数据库IO成本

降低数据排列的成本，降低CPU的消耗

劣势

索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

问题11：为什么MySQL索引中用B+tree，不用二叉树和B-tree

答：数据库使用B+树肯定是为了提升查找效率。

问题12：那为什么推荐使用整型自增主键而不是选择UUID?

答：UUID是字符串，比整型消耗更多的存储空间;在B+树中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速;

自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续;UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的条件查询语句。在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构;UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。

问题13：为什么非主键索引结构叶子节点存储的是主键值?

问题14：为什么MySQL索引为何不采用Hash方式?

答：因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点)，所以对于范围查询的时候不需要做全表扫描。哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

问题15：Mysql哪些情况下需要创建索引，哪些情况下可以不用创建索引

答：

哪些情况需要创建索引：

1、主键自动建立唯一索引

2、频繁作为查询条件的字段

3、查询中与其他表关联的字段，外键关系建立索引

4、单键/组合索引的选择问题，高并发下倾向创建组合索引

5、查询中排序的字段，排序字段通过索引访问大幅提高排序速度

6、查询中统计或分组字段

哪些情况不需要创建索引：

1、表记录太少

2、经常增删改的表

3、数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引(如果某个数据类包含太多的重复数据，建立索引没有太大意义)

4、频繁更新的字段不适合创建索引(会加重IO负担)

5、where条件里用不到的字段不创建索引

由于篇幅原因，本文不做全部展示，有需要MySql面试资料的同学添加管理员微信获取，也可以让管理员邀请你进入测开交流群!

获取资料

获取更多面试资料+进入测开交流群