根据面试经历，总结mysql面试题（实时更新）

一写代码就开心

发布于 2022-05-09 10:34:23

5330

发布于 2022-05-09 10:34:23

文章被收录于专栏：java和python

Mysql的执行流程

1.客户端向服务器端发送SQL命令 2.服务器端连接模块连接并验证 3.缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行 4.解析器解析SQL为解析树，如果出现错误，报SQL解析错误。如果正确，向下传递解析时主要检查SQL中关键字，检查关键字是否正确、SQL中关键字顺序是否正确、引号是否对应是否正确等。

5.预处理器对解析树继续处理，处理成新的解析树。根据解析器的解析树，进一步检查表是否存在、列是否存在、名字和别名是否有歧义等。

6.优化器根据开销自动选择最优执行计划，生成执行计划 7.执行器执行执行计划，访问存储引擎接口 8.存储引擎访问物理文件并返回结果 9.如果开启缓存，缓存管理器把结果放入到查询缓存中。 10.返回结果给客户端

Mysql的执行顺序

先执行from关键字后面的语句，明确数据的来源，它是从哪张表取来的。

接着执行where关键字后面的语句，对数据进行筛选。

再接着执行group by后面的语句，对数据进行分组分类。

然后执行select后面的语句，也就是对处理好的数据，具体要取哪一部分。

最后执行order by后面的语句，对最终的结果进行排序。

group by 和 having区别

GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等。

HAVING相当于条件筛选，但它与WHERE筛选不同，HAVING是对于GROUP BY对象进行筛选。

Mysql事务特性

事务的基本要素（ACID）

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

Mysql的隔离级别

因为脏读，幻读这些问题，所以出现了隔离级别的概念，也就是指定了一些规则进行解决这样的问题

什么是脏读，幻读

脏读：事务A查询数据后进行了一次修改且未提交，而事务B这个时候去查询，
然后使用了这个数据，因为这个数据还没有被事务A 提交到数据库中，
所以事务B的得到数据就是脏数据，对脏数据进行操作可能是不正确的。

不可重复读:    事务A访问了两次数据，但是这访问第二次之间
事务B进行一次并进行了修改，
导致事务A访问第二次的时候得到的数据与第一次不同，
导致一个事务访问两次数据得到的数据不相同。因此叫做不可重复读。

幻读： 与不可重复读都点相似，只是这次是事务B在事务A访问
第二次的之前做了一个新增，导致事务A第二次读取的时候发现了多的记录，
这就是幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，
幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，
解决幻读需要锁表

什么是事务

事务是访问数据库的一个操作序列，是程序的执行单元。使得数据库从一种状态转换成另一种状态事务的特性ACID

InnoDB和MyIASM储存引擎的区别

InnoDB引擎提供了对数据库ACID事务的支持；没有保存表的行数，

不提供对数据库事务的支持，也不支持外键和行级锁；存储了表的行数

MyIASM储存引擎如何查询数据

为什么innodb必须有主键，并且主键id不能使用uuid

因为UUID不能做范围查询，他完全是随机的。如何已经使用了UUID，之后只能根据创建时间进行范围查询

mysql的索引如何做优化

1 定位慢查询的地方，有一个慢查询日志，我们可以设置当多于多少秒的时候，就将数据记录到慢查询日志表里面，以后我们就可以打开这个慢查询日志表，找到哪个sql语句是慢查询

2 用explain语句，去分析，到底为什么查询慢，是不是索引没有使用上，是不是索引只使用了一部分

数据库的三范式

第一范式（1NF）：确保每一列的原子性如果每一列都是不可再分的最小数据单元，则满足第一范式。

如果一个关系满足1NF，并且除了主键以外的其它列，都依赖与该主键，则满足二范式(2NF)，第二范式要求每个表只描述一件事。

第三范式：在1NF基础上，除了主键以外的其它列都不传递依赖于主键列，或者说：任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

一般情况下，我们创建的表的类型是InnoDB，如果新增一条记录（不重启mysql的情况下），这条记录的id是8；但是如果重启（文中提到的）MySQL的话，这条记录的ID是6。因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。

但是，如果我们使用表的类型是MylSAM，那么这条记录的ID就是8。因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。

注：如果在这7条记录里面删除的是中间的几个记录（比如删除的是3,4两条记录），重启MySQL数据库后，insert一条记录后，ID都是8。因为内存或者数据库文件存储都是自增主键最大ID

如何获取当前数据库版本？

使用 select version() 获取当前 MySQL 数据库版本。

char 和 varchar 的区别是什么？

char(n) ：固定长度类型，比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。
chat 优点：效率高；缺点：占用空间；**适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。**


varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。
所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

float 和 double 的区别是什么？

float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。
double 最可可以存储 16 位的十进制数，并在内存中占 8 字节。

mysql 的内连接、左连接、右连接有什么区别？

内连接关键字：inner join；左连接：left join；右连接：right join。
内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

说一下 mysql 常用的引擎？

InnoDB 引擎：InnoDB 引擎提供了对数据库 acid 事务的支持，
并且还提供了行级锁和外键的约束，它的设计的目标就是
处理大数据容量的数据库系统。MySQL 运行的时候，
InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。
但是该引擎是不支持全文搜索，同时启动也比较的慢，
它是不会保存表的行数的，所以当进行 select count(*) from table 
指令的时候，
需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的
,所以在并发度较高的场景下使用会提升效率的。

MyIASM 引擎：MySQL 的默认引擎，但不提供事务的支持，
也不支持行级锁和外键。
因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，
所以会导致效率会降低。不过和 InnoDB 不同的是，
MyIASM 引擎是保存了表的行数
，于是当进行 select count(*) from table 语句时，
可以直接的读取已经保存的值而不需要进行扫描全表。
所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，
可以将 MyIASM 作为数据库引擎的首选。

说一下 mysql 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。
表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，
并发量最低。
行级锁：开销大，加锁慢，会出现死锁。锁力度小，
发生锁冲突的概率小，并发度最高。

说一下乐观锁和悲观锁？

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，
但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。


悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的
时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。


数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，
每次修改成功值加 1，这样每次修改的时候先对比一下，
自己拥有的 version 和数据库现在的 version 是否一致，
如果不一致就不修改，这样就实现了乐观锁。

mysql 问题排查都有哪些手段？

使用 show processlist 命令查看当前所有连接信息。使用 explain 命令查询 SQL 语句执行计划。开启慢查询日志，查看慢查询的 SQL。

如何做 mysql 的性能优化？

为搜索字段创建索引。避免使用 select *，列出需要查询的字段。垂直分割分表。选择正确的存储引擎。

索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，
便于提升索引的使用效率，更高效的使用索引。


1  对查询频次较高，且数据量比较大的表建立索引。 

索引字段的选择，最佳候选列应当从where子句的条件中提取，
如果where子句中的组合比较多，那么应 当挑选最常用、
过滤效果最好的列的组合。

2  使用唯一索引 ，区分度越高，使用索引的效率越高。


索引可以有效的提升查询数据的效率，但索引数量不是多多益善，
索引越多，维护索引的代价自然也就水 涨船高 。
对于插入、更新、删除等DML操作比较频繁的表来说，
索引过多，会引入相当高的维护代价，降低DML操作的效率，
增加相应操作的时间消耗。 另外索引过多的话，MySQL也会犯选择困难 病，
虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

3  使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，
也可以提升总体的访 问效率。 假如构成索引的字段总长度比较短，
那么在给定大小的存储块内可以存储更多的索引值，
相应的可以有效的提升MySQL访问索引的I/O效率。


4  利用最左前缀 ，N个列组合而成的组合索引，那么相当于是创建了N个索引，
如果查询时where子句
中使用了组成该索引的前几个字段，
那么这条查询SQL可以利用组合索引来提升查询效率。

InnoDB 引擎特点

支持事务，使用的是行锁，支持外键

MyISAM引擎特点

不支持事务，使用的是表锁，不支持外键

InnoDB 存储表和索引的文件名称：

使用多表空间存储，这种方式创建表的结构仍然存在.frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。

MyISAM 存储表和索引的文件名称：

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是： .frm (存储表定义)； .MYD(MYData , 存储数据)； .MYI(MYIndex , 存储索引)；

存储引擎的选择

InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

什么是最左前缀原则

1 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

比如有3个字段的索引

查询条件里面，必须有name条件，才会走这个索引

2 范围查询右边的列，不能使用索引。范围查询条件之后的字段，索引失效

意思是组合索引里面，如果有一个字段使用范围查询，比如大于小于，那么这个字段之后的索引字段将不会进行索引查询，也就是那个后面的字段将不参与索引查询；

3 不要在索引列上进行运算操作，索引将失效。比如在查询条件的索引字段上做截取查询，那么将索引失效
4 字符串不加单引号，造成索引失效。
5 尽量使用覆盖索引，避免select * 尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。
6 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。示例，name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的：
7 以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

我们想要在前面加%，可以这样做解决方案：通过覆盖索引来解决，也就是不要select * 查询的字段就是索引字段，不能多其他的字段

8 明明单独创建了address索引，但是explain查看没有使用索引，这种情况跟表中数据有关，查看表数据知道，12条数据，11条是’北京市’，使用索引查找效率不如直接全表扫描来的快，所以在执行 SQL语句的时候，MySQL放弃使用索引，而使用全表扫描。
9 MySQL底层会自动判断，如果全表扫描快，则直接使用全表扫描，不走索引。如果表中该索引列数据绝大多数是非空值，则使用is not null的时候走索引，使用is null的时候不走索引（还不如全表扫描快），全表扫描；反之亦然。
10 in 走索引， not in 索引失效。

mysql索引为什么需要遵循最左前缀原则

避免回表查询

请说一下MySQL支持的日志类型有哪些

MySQL中有六种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。

explain type级别需要达到什么，可以不优化

最少达到range级别，all级别是查询全表数据，index级别是查询主键索引数，两个都需要优化

const 和 system级别，查询就很快

什么是存储过程？好处？

存储过程可以说是一个记录集吧，它是由一些 T-SQL 语句组成的代码块，这些 T-SQL 语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。好处1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比 T-SQL 语句高。2.一个存储过程在程序在网络中交互时可以替代大堆的 T-SQL 语句，所以也能降低网络的通信量，提高通信速率。3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-02-19，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

数据库

云数据库 SQL Server

分布式

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度