MySQL知识框架与常见面试题目总结（持续跟新）

1.MySQL的知识框架

本文将按照MySQL的学习路径对面试问题进行有条理性总结，便于大家理解与记忆。
MySQL总结框架以及相关知识回顾与查询：

5. MySQL数据表基本查询操作

2. MySQL的常见面试题目

2.1 基础概念与基础操作题目

1.MySQL的基础框架是？
答：MySQL基础框架分为三层：第一层负责连接处理，授权认证，安全等；第二层负责优化和编译SQL语句；第三层是存储引擎。

2. 一条SQL语句在MySQL中如何执行的？

答： 首先，查看sql语句是否有权限，如果没有权限则直接返回错误信息；如果有权限，分析器进行此法分析，提取sql语句中的关键元素；然后判断sql语句是否有语法错误，有则报错；没有的话，优化器进行确定执行方案；最后权限校验，无权限返回错误，反之输出结果。（权限查询->分析器分析->判断语法错误->优化器执行方案->权限校验输出）

3.SQL语句的执行顺序是？
答 ：from->on->where->group by -> having ->select->distinct-> order by -> limt;

4.MySQL的读写分离原因是？
答：数据库写入效率要低于读取效率，但是一般系统中数据读取频率高于写入频率，单个数据库实例在写入的时候会影响读取性能，这是将读写分离的原理；

5.MySQL的读写分离常见方案？
答：第一种基于MySQL proxy代理方式，在应用和数据库之间加一层代理层，代理层将不同请求转发到同的实列中，从而实现读写分离；第二种基于应用内路由的方式，针对不同的请求类型执行不同的实例化sql；第三种基于sharding-jdbc的方式，sharding-jdbc是最强大的读写分离，分表分库中间键件；第四种基于mysql-connector-java的jdbc驱动；

6. 数据库中间件了解过吗，sharding jdbc， mycat？
答：sharding-jdbc基于jdbc驱动实现读写分离，无需要额外的proxy；mycat是基于proxy，它复写了MySQL协议，将MyCat Server伪装成一个MySQL数据库，而sharding-jdbc是基于jdbc接口的扩展，是以jar包的形式提供轻量级服务的。

7.说下数据库的三大范式
答：第一范式：数据表中的每一列都不可以再拆分。第二范式：在第一范式的基础上，非主键完全依赖于主键，而不能是主键的一部分；第三范式：在满足第二范式的基础上，非主键只依赖主键，不依赖其他非主键；

8.MySQL中的varcahr和char有什么区别？
答：假设char和varchar都向空间申请了10个字符， char所占的空间无论存储3个字符还是10个字符，都算占了10个字符，而varchar存储3个字符，只用了4个字符的空间，前3个用去存储内容，最后一个字符存储使用了多长的空间；在检索效率上char快于varchar，如果知道字段固定长度用char，不知道用varchar；

9.为什么要尽量设定一个主键?
答：主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键，也建议添加一个自增长的ID列作为主键.设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

2.2 索引和存储引擎相关题目

1.什么是存储引擎？
答：存储数据的同时，还用组织数据的组织结构，而这些数据的组织结构就是由存储引擎决定的。即存储引擎决定了存储数据的组织结构。

2.InnoDB和MyISAM的区别
答：InnoDB支持事务，MyISAM不支持事务；InnoDB支持外键，MylSAM不支持外键；InnoDB支持表、行级锁，而MyISAM支持表级锁；InnoDB需要更多的内存和存储，而MyISAM可压缩，存储空间小。

3.什么是索引？
答：索引是一种数据结构，可以帮我们快速的进行数据的查找。

4.索引是什么样的数据结构呢？
答：索引的数据结构与存储引擎实现有关，在MySQL常用的索引是Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为B+树索引。

5.索引的优缺点？什么时候使用索引？什么时候不能使用索引？
答：索引的好处是提高查询速度，坏处就是更新数据较低，因为同时也要跟新索引。对数据进行频繁查询时建立索引，如果频繁更改不建议使用索引。

6.Hash索引和B+树索引有什么区别或者说优劣？
答：Hash索引底层就是hash表，进行查找时，调用hash函数可以获取相应的键值，然后回表查询获得实际数据。B+树索引底层是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点发可以获得所查找键值，然后根据条件获取表里的实际数据。

• hash索引查询速度快，但是不能进行范围查询；B+树索引相对慢点，但是可以进行范围查询。
• hash索引不支持使用索引进行排序，B+可以，因为B+树接节点遵循（左节点小于父节点，父节点小于右节点，多叉树也如此）。
• hash索引不支持模糊查询，原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性。
• hash索引查询快，但是不稳定，因为会出现某个键值存在大量重复值，此时效率极差。而B+树的速度稳定，从根节点到叶子节点。
  因此在B+树在大多数场合下可以稳定较快查找想要的数据。

7.为什么MySQL索引使用B+树，而不是二叉搜索树，二叉平衡树，红黑树，B树？
答：首先要明白二叉搜索树的基础的概念是左节点小于父亲节点，而父亲节点小于右节点，所以树有可能单方面增长使得树不平衡。为了解决此问题，提出了二叉平衡树，来保持树的平衡，但是每次查询或者删除，会需要通过一次或者多次的旋转来平衡这棵树。由于旋转的耗时，AVL树在删除数据时效率很低。与AVL树相比，红黑树并不追求严格的平衡，而是大致的平衡：只是确保从根到叶子的最长的可能路径不多于 最短的可能路径的两倍长，红黑树插入或删除数据时，只需进行O(1)次的旋转以及变色就能保证基本平衡，但是红黑树的树高度还是有点高，所需要的IO次数也越多，会严重影响性能。B树又称B-树，B树的每个非叶节点可以有多个子树，所以节点数相同的情况下，B树的高度远小于AVL和红黑树，磁盘IO次数大大减少，但是B树不支持范围查询；B+树，在B-树的基础上多了一个能够范围查询的功能，因为B+树即使在非叶子节点找到了关键字，都要走到叶子节点，这使它可以进行范围查询。
8.聚集索引与非聚集索引的区别
答：聚集索引是指表中数据的物理地址顺序与逻辑顺序相同，一个表中只有一个物理顺序，所以聚集索引就一个；如果物理地址顺序与逻辑顺序不同，则称为非聚集索引。

• 聚集索引一个表只有一个，而非聚集索引一个表可以多个。
• 聚集索引中键值的逻辑顺序决定了相应行的物理顺序；非聚集索引的逻辑顺序与物理顺序不同。
• 聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点，需要回表查询数据。

2.3 事务和锁的相关题目

1. 什么是事务？
答：事务是一系列的操作，他们符合ACID特性，一些列操作要么全部完成，要么全部失败。

2. 什么是ACID?
答：A原子性，要么全部完成，要么全部失败；C一致性，数据库的一致性，从一个状态转换成另一个状态，不存在中间状态；I隔离性，通常一个事务未提交之前，对其他事务是不可见；D持久性，一旦事务提交，那就是永远是这样。

3. MySQL的事务隔离级别是怎么样的？
答：有四种级别，依次是未提交读、已提交读、可重复读、可串行化

4. Innodb使用的是哪种隔离级别呢?
答：InnoDB默认使用的是可重复读隔离级别。

5. 什么是脏读，不可重复读，幻读呢？
答：事务A执行时，事务B也执行了，事务A读取到了事务B未提交时的数据，叫脏读；在一个事务范围内，两个相同的查询，读取同一条数据，缺返回不同的数据，这就是不可重复读；事务A查询一个范围结果，此时事务B在这个范围内增加或者删除了数据，然后事务A再次查询范围时，两次查看到的结果不一样，就是幻读。

6. 对MySQL的锁了解吗?
答：当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,锁机制就是这样的一个机制。像酒店的房间,如果大家随意进出,就会出现多人抢夺同一个房间的情况,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他使用完毕才可以再次使用。

7.MySQL都有哪些锁呢?
答：从锁的类别上来讲,有共享锁和排他锁，共享锁: 又叫做读锁. 当用户要进行数据的读取时，对数据加上共享锁，共享锁可以同时加上多个。排他锁：又叫写锁，当用户要进行数据的写入时，对数据加上排他锁，排他锁就可以加一个，其他用户不可以进行写操作。

8.数据库的乐观锁和悲观锁
答：悲观锁：一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放才可以执行。乐观锁的“乐观情绪”体现在，它认为数据的变动不会太频繁。因此，它允许多个事务同时对数据进行变动。

2.5 优化相关题目

1. 说一下大表查询的优化方案
答：优化sql语句+索引；可以考虑加缓存，memcached, redis，或者JVM本地缓存；主从复制，读写分离；

2.如何优化长难的查询语句？有实战过吗？
答：减少冗余的查询；将大的查询分成多个简单的子查询实现功能；分解关联查询，提高运行效率。

3.一条sql执行过长的时间，你如何优化，从哪些方面入手？
答：

• 查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，是否可拆表等；
• 优化索引结构，看是否可以适当添加索引
• 数量大的表，可以考虑进行分离/分表（如交易流水表）
• 数据库主从分离，读写分离
• explain分析sql语句，查看执行计划，优化sql
• 查看mysql执行日志，分析是否有其它方面问题

4.日常工作中你是怎么优化SQL的？
答：

• 加索引，提高查询速度；
• 避免返回不必要的数据；
• 适当分批量进行
• 优化SQL结构
• 分库分表，读写分离

5.说说分库与分表的设计
答：分库分表方案:

• 水平分库：以字段为依据，按一定策略（hash，range等），将一个库中的数据拆分到多个库中；
• 水平分表：以字段为依据，按一定策略（hash，range等），将一个库中的数据拆分到多个表中；
• 垂直分库：以表为依据，同类的表放入同类的库中；
• 垂直分表：以字段为依据，按照字段的活跃性，将一个分成不同的表（主表和扩展表）中；
  常用的分库分表中间件：sharding-jdbc（当当）、Mycat；

参考

https://blog.csdn.net/sinat_32366329/article/details/94668396
https://blog.csdn.net/qq_35006660/article/details/114558947
https://blog.csdn.net/Mind_programmonkey/article/details/115281351
https://www.cnblogs.com/setalone/p/14851000.html
https://blog.csdn.net/weixin_44842613/article/details/116604132
https://www.jb51.net/it/689384.html