12、MySQL高频面试题

0、MySQL的版本

5.5 5.7 8.x(我们用的是)

1、内连接和外连接的区别

内连接指的是使用左表中的每一条数据分别去连接右表中的每一条数据，仅仅显示出匹配成功的那部分

外连接有分为左外连接和右外连接

左外连接: 首先要显示出左表的全部，然后使用连接条件匹配右表，能匹配中的就显示，匹配不中的显示为null
右外连接: 首先要显示出右表的全部，然后使用连接条件匹配左表，能匹配中的就显示，匹配不中的显示为null

2、drop、delete与truncate区别
这个关键字都是MySQL中用于删除的关键字，区别在于：

delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作
drop 主要用于删除数据表、表中的列、索引等结构
truncate 是直接把表删除，然后再重建表结构
这三种方式在效率方面drop 最高、truncate 其次、delete最低，但是drop和truncate 都不记录日志，无法回滚

3、union与union all的区别
union和union all都是MySQL中用于合并多条select语句结果的关键字，它会将前后两条select语句的结果组合到一个结果集合中，区别在于UNION ALL会返回所有结果，UNION会去掉重复的记录

4、char和varchar的区别
char和varchar是MySQL中的字符串类型，区别在于下面几方面：

最大长度：char最大长度是255字符，varchar最大长度是65535个字符
占用长度：char是定长的，不足的部分用隐藏空格填充，varchar是不定长的
空间使用：char会浪费空间，varchar会更加节省空间
查找效率：char查找效率会很高，varchar查找效率会更低
因此我们如果存储固定长度的列，例如身份证号、手机号建议使用char

其它不定长度的建议使用varchar，使用varchar的时候也要尽量让声明长度贴近实际长度

注意：varchar(50)中50的涵义是最多存放50个字符，varchar(50)和varchar(200)存储hello所占空间一样

5、事务的四大特性

事务是有一组操作，要不全部成功要不全部失败，事务的四大特性指的是原子性、一致性、隔离性、持久性

原子性：事务是最小的执行单位，不允许分割，同一个事务中的所有命令要么全部执行，要么全部不执行
一致性：事务执行前后，数据的状态要保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的
隔离性：并发访问数据库时，一个事务不被其他事务所干扰，各并发事务是独立执行的
持久性：一个事务一旦提交，对数据库的改变应该是永久的，即使系统发生故障也不能丢失

6、并发事务（多个事务同事执行）带来的问题
并发事务下，可能会产生如下的问题：

脏读：一个事务读取到了另外一个事务没有提交的数据
不可重复读：一个事务读取到了另外一个事务修改的数据
幻读（虚读）：一个事务读取到了另外一个事务新增的数据

7、事务隔离级别
事务隔离级别是用来解决并发事务问题的方案，不同的隔离级别可以解决的事务问题不一样

读未提交： 允许读取尚未提交的数据，可能会导致脏读、幻读或不可重复读
读已提交： 允许读取并发事务已提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
可重复读： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
可串行化： 所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，该级别可以防止脏读、不可重复读以及幻读。
上面的这些事务隔离级别效率依次降低，安全性依次升高，如果不单独设置，MySQL默认的隔离级别是可重复读

原理：我知道是MVCC（多版本并发控制），有日志文件和3个隐藏的列处理的

8、数据库建表三大范式
三大范式是指导设计表的原则

第一范式：表中的每一列不能再进行拆分，也就是每一列都应该是原子的：列不可再拆分
第二范式：一张表只做一件事，不要将多个层次的数据列保存到一张表中 ：一张表只做一件事
第三范式：数据不能存在传递关系，说的通俗点就是一张表拆成两个表使用外键建立关系：使用外键

比如：这是一张表，院系电话是依赖院系的不依赖学生id，这时可以分成学生表和院系表

改造后：

在现有的程序设计中认为第三范式是可以不遵守的，也就是通过添加冗余字段，来减少多表联查或计算，我们称为反三范式

9、索引的分类

1）、从物理存储角度上分为聚集（聚簇）索引和非聚集（非聚簇）索引

聚集索引指的是数据和索引存储在同一个文件中

非聚集索引指的是数据和索引存储在不同的文件中

2）、从逻辑角度上分为普通、唯一、主键和联合索引，它们都可以用来提高查询效率，区别点在于

唯一索引可以限制某列数据不出现重复，主键索引能够限制字段唯一、非空

联合索引指的是对多个字段建立一个索引，一般是当经常使用某几个字段查询时才会使用，它比对这几个列单独建立索引效率要高

10、索引的创建原则
我们在建立索引的时候应该遵循下面这些原则：

主键字段、外键字段应该添加索引
经常作为查询条件、排序条件或分组条件的字段需要建立索引
经常使用聚合函数进行统计的列可以建立索引
经常使用多个条件查询时建议使用组合索引代替多个单列索引
除此之外，下面这些情况，不应该建立索引

数据量小的表不建议添加索引
数据类型的字段是TEXT、BLOB、BIT等数据类型的字段不建议建索引
不要在区分度低的字段建立索引，比如性别字段、年龄字段等

11、索引失效的情况
索引失效指的是虽然在查询的列上添加了索引，但是某些情况下，查询的时候依旧没有用到索引，常见的情况有

使用like关键字时，模糊匹配使用％开头将导致索引失效
使用连接条件时，如果条件中存在没有索引的列会导致索引失效
在索引列上进行计算、函数运算、类型转换将导致索引失效
使用 !=、not in、is null、is not null时会导致索引失效
使用联合索引时，没有遵循最左匹配原则会导致索引失效

12、如何知道索引是否失效

MySQL中自带了一个关键字叫explain，它可以加在一个sql的前面来查看这条sql的执行计划

在执行计划中，我们主要观察两列的结果，一列是type，一列是extra

第一个type是重要的列，显示本次查询使用了何种类型，常见的值从坏到好依次为：all、index、range、ref、eq_ref 、const、system

all表示全表扫描数据文件返回符合要求的记录，其他值标识用到索引了

除了type之外我们需要关注一下extra列，如果出现using index最好了，它表示列数据仅仅使用了索引中的信息而没有回表查询

13、MyISAM和InnoDB的区别
MyISAM和InnoDB是目前MySQL中最为流行的两种存储引擎，它们的区别有这几方面：

MyISAM不支持事务，每次操作都是原子的；InnoDB支持事务，支持事务的四种隔离级别
MyISAM不支持外键，InnoDB支持外键
MyISAM仅仅支持表级锁，即每次操作是对整个表加锁；InnoDB支持行级锁，因此可以支持写并发
MyISAM属于非聚集性索引，它的数据和索引不在同一个文件中；InnoDB属于聚集性索引，它的数据和索引在同一个文件中
MyISAM中主键和非主键索引的数据部分都是存储的文件的指针；InnoDB主键索引的数据部分存储的是表记录，非主键索引的数据部分存储的是主键值

14、索引的数据结构是什么
在MySQL中索引使用的数据结构是B+Tree，B+树是基于B树的变种，它具有B树的平衡性，而且树的高度更低

B+树非叶子节点不存在数据只存索引，因此其内部节点相对B树更小，树的高度更小，查询产生的I/O更少
B+树查询效率更高，B+树使用双向链表串连所有叶子节点，区间查询效率更高
B+树查询效率更稳定，B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在，这样就会造成查询的效率的不稳定

15、什么是覆盖索引，什么是回表？

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

如果没有全部找到会回表查询

通过非聚簇索引找到对应的主键值，到聚簇索引中查找整行数据，这个过程就是回表

主键索引就是聚簇索引

===============================================================

16、数据库中的锁有哪些

MySQL中的锁从不同维度可以分为不同的种类

1）、从锁的粒度上可以分为表锁和行锁

表锁指的是会锁定修改数据所在的整个表，开销小，加锁快，锁定粒度大，发生锁冲突概率高

行锁指的是会锁定修改数据所在的行记录，开销大，加锁慢，锁定粒度小，发生锁冲突概率低

2）还有两种概念上的锁是悲观锁和乐观锁

悲观锁是指一个事务在修改数据的时候，总是认为别人也会修改此数据，所以强制要使用锁来保证数据安全

乐观锁是指一个事务在修改数据的时候，总是认为别人不会修改此数据，因为不加任何锁

这种情况下万一在当前事务修改的时候，数据被其它事务也修改了，机会出现问题，此时常用的方案是：

给数据表中添加一个version列，每次更新后都将这个列的值加1，读取数据时，将版本号读取出来

在执行更新的时候，会先比较版本号，如果相同则执行更新，如果不相同，说明此条数据已经发生了变化，就放弃更新或重试

17、MySQL日志类型
MySQL的很多功能都是依靠日志来实现的，比如事务回滚，数据备份，主从复制等等，常见的日志有下面几个

binlog归档日志

负责记录对数据库的写操作，一般用在主从复制过程中记录日志，从库拷贝此日志做重放实现数据同步

redolog重做日志

用于确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘

在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性

undo log 回滚日志

保存了事务发生之前的数据的一个版本，可以用于回滚

18、MySQL主从复制的流程
主从复制用于MySQL主从集群的主节点向从节点同步数据，主要是依靠MySQL的binLog实现的，大体流程分为三步：

Master 主库在事务提交时，会把数据变更记录在二进制日志文件 BinLog中
从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 RelayLog
slave重做中继日志中的事件，将改变反映它自己的数据

19、谈谈你对sql的优化的经验

1、表设计方面 
   1.1 数据量大可以考虑分库分表
     分库需要借助中间件，比如说mycat
     分表的话，比如说可以分成热点数据表和历史数据表
    
   1.2 可以添加冗余字段以避免表关联查询
   1.3 建表时选择合适的数据类型和长度
2、索引方面 
   2.1 开启慢查询日志定位执行时间比较长的SQL
   2.2 需要创建索引的尽量创建索引，有索引的可以使用explain判断是否走索引

3、SQL语句方面
   尽量不使用select *
   in 后面不要出现太多的值
   避免子查询
4、选择合适的引擎
   Innodb MyIsam

也就是我们搭建一个MySQL的主从集群，让1个主节点负责写入数据，多个从节点负责查询数据，已分摊查询压力