MySQL相关面试题

union 和 unionAll 的区别？

union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
unionAll: 对两个结果集进行并集操作，包括重复行，不进行排序

drop、delete与truncate的区别 ？

相同点：

truncate和不带where子句的delete,以及drop都会删除表内的数据

不同点：

• truncate会清除表数据并重置id从1开始,delete就只删除记录,drop可以用来删除表或数据库并且将表所占用的空间全部释放

• truncate和delete只删除数据不删除表的结构。drop语句将删除表的结构被依赖的约(constrain),触发器(trigger),依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

• 速度上一般来说: drop> truncate > delete

• 使用上,想删除部分数据行用 delete，想删除表用 drop,想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。

• delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

sql 语句如何优化 ？

1.建议少用*代替所有列名
2.用 exists 代替 in
3.多表连接时，尽量减少表的查询次数
4.删除全表数据的时候用 truncate 代替 delete
5.sql 语句尽量大写，Oracle 会默认把小写转换成大写在执行
6.优化 group by,将不需要的数据尽量在分组之前过滤掉
7.连表查询的时候尽量使用表的别名，减少解析时间
8.表连接在 where 之前，where 条件过滤顺序，能够更多的过滤数据的放在前面
9.合理使用索引

SQL语句的中关键字执行顺序 ?

关键字简述：
FROM：确定数据来源，即指定表；
JOIN…ON：确定关联表和关联条件；
WHERE：指定过滤条件，过滤出满足条件的数据；
GROUP BY：按指定的字段对过滤后的数据进行分组；
HAVING：对分组之后的数据指定过滤条件；
SELECT：查找想要的字段数据；
DISTINCT：针对查找出来的数据进行去重；
ORDER BY：对去重后的数据指定字段进行排序；
LIMIT：对去重后的数据限制获取到的条数，即分页；

mysql的explain有哪些列

id（sql的执行顺序标识），selcet_type（select子句的类型），table（显示该列关于哪张表，有别名的时候展示的
是别名），type（就是我们常看走不走索引的一列），possible_key（哪些列可能走索引，查询涉及的列有索引就会
展示，但最后不一定走那个），key（mysql中实际走索引的列），key_len（走索引的时候，实际列中数据的最大长
度），ref（表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值）， rows（表示MySQL根据表统
计信息及索引选用情况，估算的找到所需的记录所需要读取的行数），Extra（该列包含MySQL解决查询的详细信
息），partitions（分区，落入了哪些分区），filtered（通过表条件过滤出的行数的百分比估计值）

数据库的三范式

数据库总共5个范式，目前是满足前三个范式即可。
第一范式(确保每列保持原子性)：表中的字段不可再次拆分。比如地址，如果频繁的访问地址的省市。我们将地址进
行拆分存储即可。这样才满足第一范式。
第二范式（确保表中每列都和主键相关）：如果说我们有一个用户表和一个权限表。如果说我们用用户的id和角色的
id作为主键，也是可以进行存储的。这样的问题就是，其中的任意一个信息，并不是和主键完全相关的。如果将用户
id和角色id分别拆分为两个表。然后用一个中间表关联，这样是符合第二范式的。
第三范式(确保每列都和主键列直接相关,而不是间接相关)：第三范式需要确保数据表中的每一列数据都和主键直接相
关，而不能间接相关。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。
而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个
满足第三范式的数据库表。如果冗余了就不满足第三范式。

什么是事务 ？

事务是由一条或多条操作数据库的SQL组成的一个不可分割的工作单元，这些操作要么全部执行成功，要么全部失败。

事务的四个特性(ACID) ？

• 原子性： 要么全部执行成功，要么全部执行失败

• 一致性： 在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

• 隔离性： 多个并发事务之间要相互隔离，不能被其他事务的操作所干扰

• 持久性： 当事务正确完成后，对于数据的改变是永久性的。

事务的隔离级别？

索引主要有哪几种分类 ？

普通索引: 是最基本的索引，它没有任何限制
唯一索引: 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
主键索引: 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
组合索引: 一个索引包含多个列，实际开发中推荐使用组合索引。
全文索引: 全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建。
主键索引和唯一索引的区别：

主键必唯一，但是唯一索引不一定是主键；

一张表上只能有一个主键，但是可以有一个或多个唯一索引。

Mysql聚集索引 ?

聚集索引定义了表中数据的物理存储顺序，索引顺序和物理顺序一致。InnoDB聚集索引的叶子节点存储行记录，因
此InnoDB必须要有且只有一个聚集索引。一个表只能有一个聚集索引。Innodb的存储索引是基于B+tree。聚集索引
既存储了索引，也存储了行值。它的数据是存储在索引的叶子页（leaf pages）。
如果一个主键被定义了，那么这个主键就是作为聚集索引
如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一
个6个字节的列，改列的值会随着数据的插入自增。
Innodb中的每张表都会有一个聚集索引，而聚集索引又是以物理磁盘顺序来存储的，自增主键会把数据自动向后插
入，避免了插入过程中的聚集索引排序问题。聚集索引的排序，必然会带来大范围的数据的物理移动，这里面带来的
磁盘IO性能损耗是非常大的。 而如果聚集索引上的值可以改动的话，那么也会触发物理磁盘上的移动，于是就可能
出现page分裂，表碎片横生。
某些特殊的情况也是可以自己指定一些非自增主键为聚集索引的。如：
当数据量大，但长时间不会被更新的；
新生成的数据的索引本来就是按照自增的顺序增加的等等。

Mysql的非聚集索引 ?

除了聚簇索引的其他索引都是非聚簇索引，叶节点指向表中的聚集索引的id，记录的物理顺序与逻辑顺序没有必然的
联系。
每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。但是，一个表可以有不止一个非聚簇索
引。实际上，对每个表你最多可以建立249个非聚簇索引。非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚
簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索
引的表中的数据时，必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁
地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数
量

Mysql的回表查询 ?

除了聚集索引以外，其他建立索引的方式都是非聚集索引，就是普通索引，二级索引。二级索引要进行回表。二级索
引存储的并不是本身的数据，而是聚集索引中的主键值。第一次查询，找到主键值，再通过主键值找到真正的数据。

什么时候适合添加索引，哪些列适合添加索引 ？

数据量大的时候
1.经常用作查询的列
2.多表关联时作为关联条件的列
3.在经常需要根据范围查询的列
4.经常需要排序的列
5.主键默认添加唯一索引

什么情况会使索引失效 ？

1.where 条件中有 or
2.like 查询时以%开头
3.列的类型为 varchar where 条件没有使用’’
4.not in, not exists, <>
5.=左边为表达式或者函数

B 树和 B+树的区别？为什么 Mysql 使⽤B+树 ？

B 树的特点：

1. 节点排序
2. ⼀个节点了可以存多个元素，多个元素也排序了
   B+树的特点：
3. 拥有 B 树的特点
4. 叶⼦节点之间有指针
5. ⾮叶⼦节点上的元素在叶⼦节点上都冗余了，也就是叶⼦节点中存储了所有的元素，并
   且排好顺序
   Mysql 索引使⽤的是 B+树，因为索引是⽤来加快查询的，⽽B+树通过对数据进⾏排序所以
   是可以提⾼查
   询速度的，然后通过⼀个节点中可以存储多个元素，从⽽可以使得 B+树的⾼度不会太⾼，
   在 Mysql 中⼀
   个 Innodb⻚就是⼀个 B+树节点，⼀个 Innodb⻚默认 16kb，所以⼀般情况下⼀颗两层的 B+
   树可以存 2000
   万⾏左右的数据，然后通过利⽤B+树叶⼦节点存储了所有数据并且进⾏了排序，并且叶⼦
   节点之间有指
   针，可以很好的⽀持全表扫描，范围查找等 SQL 语句。

MyISAM 和 InnoDB 的区别 ？

1）InnoDB 支持事务，而 MyISAM 不支持。

2）InnoDB 支持外键，而 MyISAM 不支持。因此将一个含有外键的 InnoDB 表 转为 MyISAM 表会失败。

3）InnoDB 和 MyISAM 均支持 B+ Tree 数据结构的索引。但 InnoDB 是聚集索引，而 MyISAM 是非聚集索引。

4）InnoDB 不保存表中数据行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量记录了整个表的行数，速度相当快（注意不能有 WHERE 子句）。

那为什么 InnoDB 没有使用这样的变量呢？因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的。

5）InnoDB 支持表、行（默认）级锁，而 MyISAM 支持表级锁。

InnoDB 的行锁是基于索引实现的，而不是物理行记录上。即访问如果没有命中索引，则也无法使用行锁，将要退化为表锁。

6）InnoDB 必须有唯一索引（如主键），如果没有指定，就会自动寻找或生产一个隐藏列 Row_id 来充当默认主键，而 Myisam 可以没有主键。

从锁的分类来说，MySQL都有哪些锁？

从锁的类别来讲，有共享锁和排他锁。

共享锁

又叫读锁。当用户要进行数据读取时，对数据加上共享锁。共享锁可以同时加上多个。
排他锁

又叫写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，它和其它排他锁、共享锁都互斥。

行级锁和表级锁对比 ？

行级锁
行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行（Row）进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突概率最低，并发度也最高。

表级锁
表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常用的MyISAM与InnoDB都支持表级锁。表级锁分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

什么是死锁？如何解决？

死锁是指两个或多个事务在同一资源上向胡战勇，并请求锁定对方的资源，从而导致恶性循环。

解决方案：

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低发生死锁的风险。

2、同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。

3、对于非常容易产生死锁的业务部分，可以尝试升级锁定粒度，通过表级锁定来减少死锁产生的概率。

数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统中的并发控制的任务是确保在多个事务同时存取数据库中同一份数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁
假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。

实现方式：使用数据库中的锁机制。

乐观锁
假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过版本控制的方式来进行锁定。

实现方式：一般会使用版本号机制或CAS算法实现。

乐观锁和悲观锁的使用场景 ？

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写操作比较少的情况下（多读场景，也就是并发写操作较低的场景），即冲突真的很少发生的时候，这样可以省去加锁的开销，加大了整个系统的吞吐量。

但如果是并发写操作较多的情况下，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样的话使用乐观锁反而是降低了性能，所以一般并发写操作较多的场景下使用悲观锁比较合适

什么是redo log日志 ？

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

什么是binlog日志 ？

binlog是记录所以数据表结构变更以及表数据修改的二进制日志，不会记录select和show这类操作。binlog是以事件形式记录，还包括语句所执行的消耗时间。Binlog是MySql Server自己的日志，但是Redo Log是基于InnoDB引擎所特有的日志。
MySQL数据库的 数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

什么是undo log日志 ？

数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。 我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

什么是 MVCC 以及实现 ？

MVCC 的英文全称是 Multiversion Concurrency Control，中文意思是多版本并发控制，可以做到读写互相不阻塞，主要用于解决不可重复读和幻读问题时提高并发效率。
其原理是通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。

什么是主从复制 ？

主从复制是用来建立一个与主数据库完全一样的数据库环境，即从数据库。主数据库一般是准实时的业务数据库。

主从复制的作用 ？

读写分离，使数据库能支撑更大的并发。
高可用，做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。