14、MySQL高频面试题

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

内连接和外连接都是数据库进行多表联查时使用的连接方式，区别在于二者获取的数据集不同

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

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

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

2、drop、delete与truncate区别

这个关键字都是MySQL中用于删除的关键字，区别在于：

1. delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作
2. drop 主要用于删除数据表、表中的列、索引等结构
3. 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中的字符串类型，区别在于下面几方面：

1. 最大长度：char最大长度是255字符，varchar最大长度是65535个字节
2. 占用长度：char是定长的，不足的部分用隐藏空格填充，varchar是不定长的
3. 空间使用：char会浪费空间，varchar会更加节省空间
4. 查找效率：char查找效率会很高，varchar查找效率会更低

因此我们如果存储固定长度的列，例如身份证号、手机号建议使用char

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

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

5、事务的四大特性

事务的四大特性指的是原子性、一致性、隔离性、持久性

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

6、并发事务带来的问题

并发事务下，可能会产生如下的问题：

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

7、事务隔离级别

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

• 读未提交： 允许读取尚未提交的数据，可能会导致脏读、幻读或不可重复读
• 读已提交： 允许读取并发事务已提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
• 可重复读： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
• 可串行化： 所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，该级别可以防止脏读、不可重复读以及幻读。

上面的这些事务隔离级别效率依次降低，安全性依次升高，如果不单独设置，MySQL默认的隔离级别是可重复读

8、数据库三大范式

三大范式是指导设计数据库的原则

• 第一范式：表中的每一列不能再进行拆分，也就是每一列都应该是原子的
• 第二范式：一张表只做一件事，不要将多个层次的数据列保存到一张表中
• 第三范式：数据不能存在传递关系，也就是说可以通过其它字段推出来的字段没必要再存储

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

9、索引的分类

索引是数据库中用于提供查询效率的一种手段

• 从物理存储角度上分为聚集索引和非聚集索引

  聚集索引指的是数据和索引存储在同一个文件中，InnoDB存储引擎使用的是此类索引存储方式

  非聚集索引指的是数据和索引存储在不同的文件中，MyISAM存储引擎使用的是此类索引存储方式

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

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

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

10、索引的创建原则

索引可以大幅度提高查询的效率，但不是所有的字段都要加，也不是加的越多越好，因为索引会占据磁盘空间，也会影响增删改的效率

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

1. 主键字段、外键字段应该添加索引
2. 经常作为查询条件、排序条件或分组条件的字段需要建立索引
3. 经常使用聚合函数进行统计的列可以建立索引
4. 经常使用多个条件查询时建议使用组合索引代替多个单列索引

除此之外，下面这些情况，不应该建立索引

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

11、索引失效的情况

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

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

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

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

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

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

• all表示全表扫描数据文件返回符合要求的记录
• index表示全表扫描索引文件返回符合要求的记录
• range表示检索指定范围的行，常见于使用>，<，between，in，like等运算符的查询中
• ref表示两表查询时，驱动表可能返回多行数据，也就是查询条件在主表中是加了一个普通索引
• eq_ref表示两表查询时，驱动表只返回一行数据，也就是查询条件在主表中是唯一的
• const表示索引一次就能得到结果，一般是使用唯一索引或者主键作为查询条件
• system表示表中仅有一行数据，很少见到

我们在优化的时候尽量优化到range级别以上

除了type之外我们需要关注一下extra列，它表示执行状态说明

• 要保证此列不要出现using filesort、using temporary等使用临时表或外部文件的情况

• 如果出现using index最好了，它表示列数据仅仅使用了索引中的信息而没有回表查询

13、MyISAM和InnoDB的区别

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

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

14、查询语句执行流程

一条查询语句到达MySQL数据库之后，数据库中的各个组件会按照顺序执行自己的任务

1. 首先是连接器，他会负责建立连接、检查权限等操作
2. 连接成功之后，会查询缓存，如果缓存中有结果会直接返回；如果缓存中没有结果，会将sql交给分析器处理
3. 分析器负责检查sql的词法、语法，如果没有问题，再将sql交给优化器处理
4. 优化器会决定用哪个索引，决定表的连接顺序等，然后将优化之后的sql交给执行器
5. 执行器根据存储引擎类型，调用存储引擎接口
6. 存储引擎负责最后数据的读写

15、索引的数据结构是什么

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

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

16、数据库中的锁有哪些

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

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

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

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

2. 从锁的排他性上分为共享锁和排他锁

   共享锁指的是当一个事务针对同一份数据加上共享锁之后，另一个事务也可以再往上加一把共享锁，也可以读数据，但是不能改

   对索引列加共享锁，锁定的是一行数据；对非索引列加共享锁，锁定的是整表数据

   排他锁指的的是当一个事务针对同一份数据加上排他锁之后，另一个事务只能读数据，不能改，也不能再上其它任务锁

3. 还有两种概念上的锁是悲观锁和乐观锁

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

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

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

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

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

17、MySQL日志类型

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

1. binlog归档日志

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

2. redolog重做日志

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

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

3. undo log 回滚日志

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

18、MySQL主从复制的流程

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

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

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

我在企业中优化Sql大体分为三步：

1. 查找问题sql，主要手段是开启mysql的慢查询日志，它会将执行时间较长的sql记录记录下来
2. 找到sql之后，我会分析出现问题的原因，原因很多，主要字段类型选择错误、sql语句效率低、索引失效等等
3. 根据问题不同，我会再去定具体的解决方案

简单给您说几个常见的把

1. 确定选择的引擎是否合适

   myisam适合于查询为主，增删较少，无事务要求的数据表

   Innodb适用于有事务处理，或者包括很多的更新和删除的数据表

2. 表设计是否合理

   单表不要有太多字段，建议在20以内

   合理的加入冗余字段可以提高查询速度

3. 确定字段的数据类型是否合适

   数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型

   设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低，varchar的长度只分配真正需要的空间

   尽量使用TIMESTAMP而非DATETIME，尽量设计所有字段都得有默认值，尽量避免null

4. 确定sql的书写是否有的题

   SELECT语句务必指明字段名称，避免直接使用select *

   SQL语句中IN包含的值不应过多

   可以用内连接，就尽量不要使用外连接

   使用连接连接查询来代替子查询

   适用联合(UNION)来代替手动创建的临时表

5. 表数据比较多的时候是否添加了合适的索引

   表的主键、外键必须有索引

   经常出现在where子句中的字段，特别是大表的字段，应该建立索引

   经常用于排序、分组的字段，应当建立索引

   加上索引之后，还应该使用Explain来确认索引是否生效

6. 如果上面的几项都没有问题，那可能就是因为服务器性能或者数据量过大导致的查询慢，此时可以考虑读写分离

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