java面试之MySQL数据库篇

1.基础

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）虽然性能可能会比较慢，但是能做复杂的关联查询操作。比如一对一、一对多等。

CHAR 和 VARCHAR 区别？

1）首先可以明确的是 CHAR 是定长的，而 VARCHAR 是可以变长。
CHAR 会根据声明的字符串长度分配空间，并会使用空格对字符串右边进行尾部填充。所以在检索 CHAR 类型数据时尾部空格会被删除，如保存的是字符串 'char '，但最后查询到的是 ‘char’。又因为长度固定，所以存储效率高于 VARCHAR 类型。
VARCHAR 在 MySQL 5.0 之后长度支持到 65535 字节，但会在数据开头使用额外 1~2 个字节存储字符串长度（列长度小于 255 字节时使用 1 字节表示，否则 2 字节），在结尾使用 1 字节表示字符串结束。
2）再者，在存储方式上，CHAR 对英文字符（ASCII）占用 1 字节，对一个汉字使用用 2 字节。而 VARCHAR 对每个字符均使用 2 字节。
虽然 VARCHAR 是根据字符串长度分配存储空间的，但在内存中依旧使用声明长度进行排序等作业，故在使用时仍需综合考量字段长度。

CHAR 和 VARCHAR 如何选择？

（1）从原则上来讲，将来字符串长度不固定的话，选择varchar类型，字符串长度固定不变则选择char类型
（2）实际上我们生产中在考虑性能问题的方面，
需要有大量插入（insert）操作的应用中，我们可以考虑使用char去代替varchar。
（3）如果我们业务中，大量是查询类操作的应用中，，数据量级又比较大情况下，变长长度数据类型，可以考虑采用varchar，
可以节省空间，并有效的减少索引树的高度，

CHAR，VARCHAR 和 Text 的区别？

（1）长度区别
①Char 范围是 0～255。
②Varchar 最长是 64k（注意这里的 64k 是整个 row 的长度，要考虑到其它的 column，还有如果存在 not null 的时候也会占用一位，对不同的字符集，有效长度还不一样，比如 utf-8 的，最多 21845，还要除去别的column），但 Varchar 在一般情况下存储都够用了。
③如果遇到了大文本，考虑使用 Text，最大能到 4G
（2）效率区别
效率来说基本是 Char > Varchar > Text
（3）默认值区别
Char 和 Varchar 支持设置默认值，而 Text 不能指定默认值。

MySQL中union all和union的区别

（1）union和union all关键字都是将两个select语句的结果作为一个整体显示出来
（2）Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。
（3）Union All：对两个结果集进行并集操作，包括重复行，不进行排序。

limit后面可以跟几个参数，代表什么含义？

（1）跟两个参数：
limit 开始的索引,每页查询的条数
（1）跟一个参数
limit 5：检索前5条数据

什么是内连接、左外链接和右外链接？

（1）内连接：
①假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录就会查询出来，这就是内连接。
②AB两张表没有主副之分，两张表是平等的。
（2）外连接：
①假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表
②主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
（3）外连接的分类？
①左外连接（左连接）：表示左边的这张表是主表。
②右外连接（右连接）：表示右边的这张表是主表。

drop、delete与truncate的区别

（1）SQL中的drop、delete、truncate都表示删除，但是三者有一些差别
（2）delete和truncate只删除表的数据不删除表的结构； 速度,一般来说: drop> truncate >delete
（3）delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效; 如果有相应的trigger,执行
的时候将被触发
（4） truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚,操作不触发trigger

Mysql中in和exists的区别？

（1）MySQL中的in语句是把外表和内表作hash连接，而exists语句是对外表做loop循环，每次loop循环再对内表进行查询
（2）如果查询的两个表大小相当，那么用in和exists差别不大
（3）如果两个表中一个是小表，一个是大表，则子查询表大的用exists，子查询表小的用in
①exist是先查外表，再查内表。
②in是先执行in中的子句查出来内表的结果，然后外表针对内表查出来的结果一个个遍历匹配。即先查内表，再查外表。
（4）not in 和 not exists：
①如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引
②而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in要快

获取当前日期及格式化输出

（1）获取系统日期：now()
（2）格式化日期：date_format(date,format)

binlog、redolog、undolog 分别有什么作用和区别？

binlog（二进制日志）

（1）binlog 用于记录数据库执行的写入性操作信息，以二进制的形式保存在磁盘中。
（3）binlog 是 mysql的逻辑日志，并且由 Server 层进行记录
（4）使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。
（5）补充：
①逻辑日志：可以简单理解为记录的就是sql语句 。
②物理日志：mysql 数据最终是保存在数据页中的，物理日志记录的就是数据页变更 。
（6）binlog使用场景
①主从复制 ：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致。
②数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。
（7）binlog刷盘时机
对于 InnoDB 存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么 biglog是什么时候刷到磁盘中的呢？mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：
①0：由系统自行判断何时写入磁盘；
②1：每次 commit 的时候将 binlog 写入磁盘；
③N：每N个事务，才会将 binlog 写入磁盘。
从上面可以看出， sync_binlog 最安全的是设置是 1 ，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。
（8）binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED。

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW。日志格式通过 binlog-format 指定。
①STATMENT：基于SQL 语句的复制( statement-based replication, SBR )，每一条会修改数据的sql语句会记录到binlog 中 。
优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO , 从而提高了性能；
缺点：在某些情况下会导致主从数据不一致，比如执行sysdate() 、 slepp() 等 。
②ROW：基于行的复制(row-based replication, RBR )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了 。
优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ；
缺点：会产生大量的日志，尤其是alter table 的时候会让日志暴涨
③MIXED：基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR )，一般的复制使用STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

Redo Log（重做日志）

（1）为什么需要redolog日志
为了保证mysql的持久性，最简单的做法是在每次事务提交的时候，将该事务涉及到的修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：
①Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，就太浪费资源了！
②一个事务如果涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！
（2）因此 mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo logfile)。mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志，再写磁盘 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。

Undo Log（撤销日志）

①原子性 底层就是通过 undo log 实现的。
②undo log主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。
③同时， undo log 也是 MVCC(多版本并发控制)实现的关键。
（4）区别：
①Redo Log和Undo Log都是在数据库引擎层面实现的，而Binlog是在服务器层面实现的。
②Redo Log主要用于崩溃恢复，记录了对数据库的物理修改操作；Undo Log主要用于回滚和事务隔离，记录了事务的逻辑修改操作。
③Redo Log是顺序写入磁盘的，而Undo Log通常是随机写入磁盘的。
④Binlog用于主从复制，记录了所有的数据库修改操作，以便在从服务器上复制主服务器的操作并保持数据一致性。

2.事务

什么是事务？

多条sql语句，要么全部成功，要么全部失败。

事务的四大特性(ACID)

（1）原子性(Atomicity)：指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。
（2）一致性(Consistency)：数据不会被破坏。即事务操作成功后，数据库所处的状态和它的业务规则是一致的。
如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。
（3）隔离性(Isolation)：在并发操作时,不同的事务拥有各自的数据空间,它们的操作不会对彼此产生干扰
（4）持久性(Durability)：事务被提交后，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

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

（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默认为可重复读
数据库的事务

3.索引

3.1 什么是索引？

索引是一种能够提高查询效率的提前排好序的数据结构。

MySQL 索引类型有哪些？

（1）主键索引
索引列中的值必须是唯一的，不允许有空值。
（2）唯一索引
索引列中的值必须是唯一的，但是允许有空值。
（3）普通索引
普通索引是MySQL中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
（4）全文索引
只能在文本类型CHAR,VARCHAR,TEXT的字段上创建。当字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，可以使用全文索引。MyISAM和InnoDB中都可以使用全文索引。

主键与索引有什么区别？

（1）主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键
（2）主键不允许有空值，唯一索引允许有空值
（3）一个表只能有一个主键，但是可以有多个唯一索引
（4）主键可以被其他表引用为外键，唯一索引列不可以
（5）主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构

什么时候不要使用索引？

（1）经常增删改的列不要建立索引
（2）有大量重复的列不要建立索引
（3）表记录太少不要建立索引

索引的优缺点

（1）优点：
①提高数据的检索速度，降低数据库IO成本；使用索引的意义就是通过缩小表中需要查询的记录数目，从而加快搜索的速度
②降低数据排序的成本，降低CPU消耗，索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则降低了排序的成本
（2）缺点：
①占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上
②降低更新表的速度：若表的数据发生了变化，对应的索引也要一起变更，从而降低更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

索引在什么情况下会失效？

（1）条件中有or，例如select * from table_name where a = 1 or b = 3
（2）在索引上进行计算会导致索引失效，例如select * from table_name where a + 1 = 2
（3） 在索引的类型上进行数据类型的隐形转换，会导致索引失效，例如字符串一定要加引号，假设 select * from table_name where a = '1'会使用到索引，如果写成select * from table_name where a = 1则会导致索引失效。
（4）在索引中使用函数会导致索引失效，例如select * from table_name where abs(a) = 1
（5）在使用like查询时以%开头会导致索引失效
（6）复合索引未使用左列字段
（7）索引字段上使用 is null/is not null判断时会导致索引失效，例如select * from table_name where a is null

3.2 B+tree索引跟Hash索引的区别

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

为何使用 B+ 树而非二叉查找树做索引？

（1）我们知道二叉树的查找效率为 O(logn)，当树过高时，查找效率会下降。另外由于我们的索引文件并不小，所以是存储在磁盘上的。
（2）文件系统需要从磁盘读取数据时，一般以页为单位进行读取，假设一个页内的数据过少，那么操作系统就需要读取更多的页，涉及磁盘随机 I/O 访问的次数就更多。将数据从磁盘读入内存涉及随机 I/O 的访问，是数据库里面成本最高的操作之一。
（3）因而这种树高会随数据量增多急剧增加，每次更新数据又需要通过左旋和右旋维护平衡的二叉树，不太适合用于存储在磁盘上的索引文件。

为何使用 B+ 树而非 B 树做索引？

（1）B+ 树和 B 树的区别：
①B 树：非叶子结点和叶子结点都存储数据，因此查询数据时，时间复杂度最好为 O(1)，最坏为 O(log n)。
②而 B+ 树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)。B+ 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B 树只能通过中序遍历。
（2）为什么 B+ 树比 B 树更适合应用于数据库索引？
①B+ 树减少了 IO 次数。
由于索引文件很大，因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。
②B+ 树查询效率更稳定
由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)，所以 B+ 树的查询效率相比B树更加稳定。
③B+ 树更加适合范围查找
B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。

4.存储

存储引擎有哪些？

InnoDB是如何解决幻读的？

Innodb使用MVCC和next-key locks解决幻读，MVCC解决的是普通读（快照读）的幻读，next-key locks解决的是当前读情况下的幻读。

sql优化以及慢查询排查

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

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

项目中遇到慢SQL查询，你是怎么排查和解决的？

（1）如果大多数情况都正常，偶尔很慢，则可能是：数据库在刷新脏页，例如redo log写满了需要同步到磁盘；或者执行的时候遇到锁，如：表锁、行锁；又或许写的SQL有问题，由于真实业务数据量大就导致速度极慢。当然，这是我们主要考虑的原因。另外，也有可能是当时网络不好，内存不足，I/O吞吐量小，形成了瓶颈效应，不过一般公司不会出现这种情况，用的设施都很好的。如果这条SQL一直执行的很慢，可能是没有用上索引或者索引失效
（2）慢查询日志文件可以将查询较慢的DQL语句记录下来，便于我们定位需要调优的select语句。通过以下命令查看慢查询日志功能是否开启：show variables like 'slow_query_log';
（2）设置慢查询：慢查询日志功能默认是关闭的。修改my.ini文件来开启慢查询日志功能，在my.ini的[mysqld]后面添加如下配置：

注意：slow_query_log=1表示开启慢查询日志功能，long_query_time=3表示：只要SELECT语句的执行耗时超过3秒则将其记录到慢查询日志中。

（3）分析慢查询日志
①可以通过show processlist；命令定位低效率执行sql
②可以用 explain 分析SQL的执行计划。大多数都是看possible_keys、key、key_len,这三个一般套起来分析，还有就是Extra、type（看全表扫面还是索引、还是索引范围扫描）等等。
a:possible_keys:表示查询可能使用的索引
b:key 实际使用的索引
c.key_len:使用索引字段的长度

d.Extra：给出了与查询相关的额外信息和说明。这些额外信息可以帮助我们更好地理解查询执行的过程。
e.type:反映了查询表中数据时的访问类型，常见的值：

• NULL：效率最高，一般不可能优化到这个级别，只有查询时没有查询表的时候，访问类型是NULL。例如：select 1;
• system：通常访问系统表的时候，访问类型是system。一般也很难优化到这个程序。
• const：根据主键或者唯一性索引查询，索引值是常量值时。explain select * from emp where empno=7369;
• eq_ref：根据主键或者唯一性索引查询。索引值不是常量值。
• ref：使用了非唯一的索引进行查询。
• range：使用了索引，扫描了索引树的一部分。
• index：表示用了索引，但是也需要遍历整个索引树。
• all：全表扫描
  效率最高的是NULL，效率最低的是all，从上到下，从高到低

结合起来可以看出索引使用情况
（4）优化（索引+sql语句+数据库结构优化+优化器优化+架构优化）

• 索引
  ①索引一般建在where和order by，基数要大，区分度要高，不要过度索引
  ①尽量覆盖索引，MySQL 5.6引入了索引下推优化，允许在索引搜索过程中对部分条件进行下推，从而减少回表次数。
  ②组合索引符合最左匹配原则(即：在MySQL建立联合索引时会遵守最左前缀匹配原则)
  ③避免索引失效
  ④再写多读少的场景下，可以选择普通索引而不要唯一索引。因为更新时，普通索引可以使用change buffer进行优化，减少磁盘IO,将更新操作记录到change bufer，等查询来了将数据读到内存再进行修改.
• sql语句
  ①分页查询优化
  该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询。
  select * from tb_sku where id>20000 limit 10;
  ②优化insert语句
  a.多条插入语句写成一条
  b.利用主键索引特性让数据有序插入
• 数据库结构优化
  ①将字段多的表分解成多个表
  有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。
  ②对于经常联合查询的表，可以考虑建立中间表
• 优化器优化：
  优化器使用MRR
  原理：MRR 【Multi-Range Read】将ID或键值读到buffer排序，通过把「随机磁盘读」，转化为「顺序磁盘读」，减少磁盘IO，从而提高了索引查询的性能。
• 架构优化
  读/写分离（主库写，从库读）

慢sql如何优化

（1）避免使用select *
①查询时需要先将星号解析成表的所有字段然后再查询，增加查询解析器的成本
②select * 查询一般不走覆盖索引会产生大量的回表查询
③不需要的字段也查出来浪费CPU、内存资源
④文本数据、大字段数据传输增加网络消耗
（2）小表驱动大表
小表驱动大表就是指用数据量较小、索引比较完备的表，然后使用其索引和条件对大表进行数据筛选，从而减少数据计算量，提高查询效率。
（3）用连接查询代替子查询
①因为子查询需要执行两次数据库查询，一次是外部查询，一次是嵌套子查询。
②连接查询可以更好的利用数据库索引，提高查询的性能。子查询通常会使用临时表或内存表，而连接查询可以直接利用表上的索引。
（4）提升group by的效率：
①如果你使用group by的列没有索引，那么查询可能会变得很慢。因此，可以创建一个或多个适当的索引来加速查询
②调整查询：查询的写法也会影响group by的效率。可以尝试不使用子查询或临时表，或者可以使用join或exists来代替in子查询
③限制结果集的数量：如果你只需要查看一小部分结果，可以在查询中添加limit子句，以便只返回一定数量的结果
（5）使用批量操作，逐个处理会频繁的与数据库交互，损耗性能
（6）使用limit
（7）使用union all代替union：
union去重数据需要遍历、排序和比较，它更耗时、更消耗CPU资源
（8）join的表不易过多