java面试之MySQL数据库篇

1.SQL分类

（1）DDL(Data Definition Language)数据库定义语言：create drop alter，对表结构的增删改
（2）DML(Data Manipulation Language)数据库操作语言：用来对数据库表中的数据进行增删改。
关键字:insert,delete,update等
（3）DQL(Data Query Language)数据查询语言：用来查询数据表中的记录(数据) ,关键字：select，where等
（4）DCL(Data Control Language)数据控制语言：grant授权、revoke撤销权限等。
（5）TCL(事务控制语言）：commit提交事务，rollback回滚事务(TCL中的T是Transaction)

2.关系型数据库和非关系型数据库的区别

（1）关系型数据库都是用表来进行维护，所以格式一致，可以统一用SQL语言来进行操作
（2）关系型数据库都是表结构，所以灵活度不够，操作复杂的海量数据性能比较差
（3）虽然性能可能会比较慢，但是能做复杂的关联查询操作。比如一对一、一对多等。

2.并发事务带来的问题(脏读、不可重复读以及幻读)，怎么解决？

（1）并发事务的问题
①脏读，一个事务读取到另一个事务尚未提交的改变(update,insert,delete)，叫做脏读
②不可重复读，是指在事务1内，读取了一个数据，事务1还没有结束时，事务2也访问了这个数据，修改或删除了这个数据，并提交。紧接着，事务1又读取这个数据。由于事务2的修改，那么事务1两次读到的数据可能是不一样的，因此称为是不可重复读。
（事务1想读取开启事务那一刻的数据，结果却读到了修改或删除后的数据）
③幻读，是指在事务1内，读取了一个数据，事务1还没有结束时，事务2也访问了这个数据，添加了这个数据，并提交。紧接着，事务1又读取这个数据。由于事务2的添加，那么事务1两次读到的数据可能是不一样的，因此称为是幻读。
（事务1想读取事务开启那一刻的数据，结果却读到了添加后的数据）

（2）解决并发事务带来的问题的方案是对事务进行隔离，SQL标准定义的四个隔离级别为：
①读未提交： 最低的隔离级别，允许Transaction1读取Transaction2尚未提交的数据变更，可能会导致脏读、幻读以及不可重复读。
②读已提交： 要求Transaction1只能读取Transaction2已提交的修改。可以解决脏读，但是幻读或不可重复读仍有可能发生。
③可重复读： 确保Transaction1可以多次从一个字段中读取到相同的值，即Transaction1执行期间禁止
其它事务对这个字段进行更新。可以解决脏读和不可重复读，但幻读仍有可能发生。
④可串行化： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事
务依次逐 个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以解决脏读、不可重复读以及幻读。
注意：Oracle默认为读已提交，MySQL默认为可重复读

6.什么是索引？

索引用于去快速的根据某个列查询对应的行，是帮助MySQL高效获取数据的数据结构。如果没有索引，MySQL必须从第一行开始，然后读取整个表以找到相关的行。表越大，花费时间就越多。如果表中有相关列的索引，MySQL就可以快速确定数据文件中间要查找的位置，而不必查看所有数据。这比按顺序读取每一行要快得多。

7.B+tree索引跟Hash索引的区别

（1）B+tree 支持范围，但是Hash是k-v的形式，所以不支持范围查询
（2）查询性能方面，如果等值查询，hash比tree要快很多，因为它是k-v的hash结构。

8.有没有可能问B+树、B树以及二叉树的区别

9.对 MySQL 性能优化的理解

MySQL 的性能优化我认为可以分为 4 大部分：硬件和操作系统层面的优化、架构设计层面的优化、MySQL 程序配置优化、 SQL 优化

9.1 硬件及操作系统层面优化

（1）从硬件层面来说，影响 Mysql 性能的因素有，CPU、可用内存大小、磁盘读写速度、网络带宽
（2）从操作系层面来说，应用文件句柄数、操作系统网络的配置都会影响到 Mysql性能。
这部分的优化一般由 DBA 或者运维工程师去完成。

9.2 架构设计层面的优化

MySQL 是一个磁盘 IO 访问量非常频繁的关系型数据库，在高并发和高性能的场景中.MySQL 数据库必然会承受巨大的并发压力，而此时，
我们的优化方式可以分为几个部分。
①搭建 Mysql 主从集群，单个 Mysql 服务容易单点故障，一旦服务器宕机，将会导致依赖 Mysql 数据库的应用全部无法响应。 主从集群或者主主集群可以保证服务的高可用性。
②读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导致的性能影响
③引入分库分表机制，通过分库可以降低单个服务器节点的 IO 压力，通过分表的方式可以降低单表数据量，从而提升 sql 查询的效率。
④针对热点数据，可以引入更为高效的分布式数据库，比如 Redis、MongoDB等，他们可以很好的缓解 Mysql 的访问压力，同时还能提升数据检索性能。

9.3 MySQL 程序配置优化

对于 Mysql 数据库本身的优化，一般是通过 Mysql 中的配置文件 my.cnf 来完成的，比如。
Mysql5.7 版本默认的最大连接数是 151 个，这个值可以在 my.cnf 中修改。
binlog 日志，默认是不开启
缓存池 bufferpoll 的默认大小配置等。
由于这些配置一般都和用户安装的硬件环境以及使用场景有关系，因此这些配置
官方只会提供一个默认值，具体情况还得由使用者来修改。
关于配置项的修改，需要关注两个方面。
l 配置的作用域，分为会话级别和全局
l 是否支持热加载
因此，针对这两个点，我们需要注意的是：
l 全局参数的设定对于已经存在的会话无法生效
l 会话参数的设定随着会话的销毁而失效
l 全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效

9.4 SQL 优化又能分为三步曲

（1）慢 SQL 的定位和排查
我们可以通过慢查询日志和慢查询日志分析工具得到有问题的 SQL 列表。
（2）执行计划分析
针对慢 SQL，我们可以使用关键字 explain 来查看当前 sql 的执行计划.可以重点
关注 type key rows filterd 等字段 ，从而定位该 SQL 执行慢的根本原因。再有
的放矢的进行优化
（3）使用 show profile 工具
Show Profile 是 MySQL 提供的可以用来分析当前会话中，SQL 语句资源消耗情
况的工具，可用于 SQL 调优的测量。在当前会话中.默认情况下处于 show profile
是关闭状态，打开之后保存最近 15 次的运行结果
针对运行慢的 SQL，通过 profile 工具进行详细分析.可以得到 SQL 执行过程中
所有的资源开销情况. 如 IO 开销,CPU 开销,内存开销等. 以上就是我对 MySQL 性能优化的理解。
常见的 SQL 优化规则：
①SQL 的查询一定要基于索引来进行数据扫描
②避免索引列上使用函数或者运算,这样会导致索引失效
③where 字句中 like %号,尽量放置在右边
④使用索引扫描,联合索引中的列从左往右,命中越多越好.
⑤尽可能使用 SQL 语句用到的索引完成排序,避免使用文件排序的方式
⑥查询有效的列信息即可.少用 * 代替列信息
⑦永远用小结果集驱动大结果集。

MySQL查看SQL语句执行效率的SQL语句

（1）Explain语法：explain select … from … [where …]
（2）type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

MySQL中union all和union的区别

（1）如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。
（2）Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。Union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表Union。
（3）Union All：对两个结果集进行并集操作，包括重复行，不进行排序。如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。