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高效获取数据的数据结构，所以说索引的本质是：数据结构索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等。
可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例。左边的数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值，和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到对应的数据，从而快速检索出符合条件的记录。
索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上，平常说的索引，没有特别指明的话，就是B+树(多路搜索树，不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，符合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。此外还有哈希索引等。
问题10：说说使用索引的优劣是什么？
答：优势
提高数据检索效率，降低数据库IO成本
降低数据排列的成本，降低CPU的消耗
劣势
索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息
问题11：为什么MySQL索引中用B+tree，不用二叉树和B-tree
答：数据库使用B+树肯定是为了提升查找效率。
但是具体如何提升查找效率呢?查找数据，最简单的方式是顺序查找。但是对于几十万上百万，甚至上亿的数据库查询就很慢了。所以要对查找的方式进行优化，熟悉的二分查找，二叉树可以把速度提升到O(log(n,2))，查询的瓶颈在于树的深度，最坏的情况要查找到二叉树的最深层，由于，每查找深一层，就要访问更深一层的索引文件。在多达数G的索引文件中，这将是很大的开销。所以，尽量把数据结构设计的更为‘扁平’一点就可以减少访问的层数，从根节点到叶子节点只要34次IO即可(一般B+树索引深度也就是34层)，如果用二叉树则这颗树会非常深。在众多的解决方案中，B-/B+树很好的适合。B-树定义具体可以查阅，简而言之就是中间节点可以多余两个子节点，而且中间的元素可以是一个域。相比B-树，B+树的父节点也必须存在于子节点中，是其中最大或者最小元素，B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。因此，B+树成为了数据库比较优秀的数据结构，MySQL中MyIsAM和InnoDB都是采用的B+树结构。不同的是前者是非聚集索引，后者主键是聚集索引，所谓聚集索引是物理地址连续存放的索引，在取区间的时候，查找速度非常快，但同样的，插入的速度也会受到影响而降低。聚集索引的物理位置使用链表来进行存储。用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页(对应索引树的节点)。其中在MySQL底层对B+树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。
问题12：那为什么推荐使用整型自增主键而不是选择UUID?
答：UUID是字符串，比整型消耗更多的存储空间;在B+树中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速;
自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续;UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的条件查询语句。在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构;UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。
问题13：为什么非主键索引结构叶子节点存储的是主键值?
答：保证数据一致性和节省存储空间，可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息(真实名称、手机号、收货地址···)修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间。
问题14：为什么MySQL索引为何不采用Hash方式?
答：因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点)，所以对于范围查询的时候不需要做全表扫描。哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。
问题15：Mysql哪些情况下需要创建索引，哪些情况下可以不用创建索引
答：
哪些情况需要创建索引：
1、主键自动建立唯一索引
2、频繁作为查询条件的字段
3、查询中与其他表关联的字段，外键关系建立索引
4、单键/组合索引的选择问题，高并发下倾向创建组合索引
5、查询中排序的字段，排序字段通过索引访问大幅提高排序速度
6、查询中统计或分组字段
哪些情况不需要创建索引：
1、表记录太少
2、经常增删改的表
3、数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引(如果某个数据类包含太多的重复数据，建立索引没有太大意义)
4、频繁更新的字段不适合创建索引(会加重IO负担)
5、where条件里用不到的字段不创建索引
由于篇幅原因，本文不做全部展示，有需要MySql面试资料的同学添加管理员微信获取，也可以让管理员邀请你进入测开交流群!
