面试题(mysql)

最新推荐文章于 2024-06-11 00:27:20 发布

酒_酚

最新推荐文章于 2024-06-11 00:27:20 发布

阅读量696

点赞数

分类专栏： JAVA基础面试文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_33958815/article/details/121803456

版权

JAVA基础同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

面试

6 篇文章 0 订阅

订阅专栏

Mysql的原理以及优化

mysql底层是如何实现的

底层分为客户端(dos窗口,也可以是navicat) ,服务层,存储引擎,数据文件
具体的执行过程是:
客户端通过连接器链接service，连接器会将语句交给sql分析器，分析器分为词法分析，语法分析，服务器会先查询缓存，如果命中直接返回，如果没有命中会查询数据库（在mysql8 之后就没有了查询缓存），分析器，分析出的结果会经过优化器进行优化，优化后的语句会通过执行器执行，执行器会调用存储引擎接口存入数据

连接器：优化时可以采用连接池减少io操作
查询缓存：服务器会先查询缓存，如果命中直接返回，如果没有命中会查询数据库
分析器：词法分析，语法分析
优化器：使用它认为的最优的放啊执行exlpan（但是不一定是最优的，可以通过explan查看是否是最优的）
执行器：调用存储引擎接口最终执行

常见的存储引擎有哪些

Innodb MyIsam mannory

如何理解存储引擎

数据库存储引擎面试数据库的底层软件组织，dbms使用数据引擎进行出创建，查询更新和删除数据
在mysql5.5之前使用的是Myisam在之后使用的是Innodb
可以使用show enyines\g 查看内置存储引擎
存储引擎是表级别的，建表时可以指定，后期也可以修改

不同存储引擎的实现方式

Myusa存储
由三个文件组成，frm,myd,myi
frm ：定义了表结构有哪些
Myd：存储了数据
myi ：存储的index的位置
myisam 不支持事务，提供表级别的锁，并且不支持外键，可以压缩表节省空间

Innodb储存引擎
由frm和idb组成
frm：定义了表结构有哪些
idb：是把数据和index放在一个文件中的
支持事务
提供行锁可以通过行锁实现表锁的效果
支持外键

mamiry存储引擎
只有一个frm文件
frm中定义了表相关的信息
数据存在内存中，访问效率高但是数据库一旦关闭数据就会消失
提供表锁

事务

事务的隔离级别

数据库中的事务支持ACID特性，其中i是隔离性数据库中存在RU,RC,RR和S
是四种隔离级别设置不同的隔离级别有可能产生脏读，不可重复读，幻读等问题
事务的隔离级别是使用CBLL机制或MVCC机制实现的
CBLL机制是锁机制
MVCC机制是版本控制机制

ACID的特性是什么

A(atinnucuty): 是原子性，事务中的操作作为一个单元，完成时必须同时成功或者同时失败
C(loonsistency): 一致性事务开始之前到结束之后数据库中的数据都保持一致性，一般是由业务逻辑控制
I(isolation)：隔离性，一个事务不会影响其他事务的运行用锁机制和MVCC机制实现
D(dueability): 持久性事务完成之后该事务对数据库的影响是持久的，保存在数据库中并不会回滚

事务的隔离级别

事务有哪些隔离级别，设置不同的隔离级别会导致哪些数据一致性问题

数据库的隔离级别有：读未提交（Read uncommitted），读已提交（Read committed），可重复读（Repeatable read），串行化（Serializable)

不同的隔离级别可能出现脏读，不可重复读，幻读等问题
读未提交：可能出现脏读，不可重复读，幻读
读已提交：不会产生脏读，但是有可能产生不可重复读，幻读
可重复读：不可能产生脏读，不可重复读，对于innodb不可能产生幻读
串行化：不会出现脏读，不可重复读，幻读等问题

读未提交：存在脏读问题（一个事务提交之前在事务过程中修改数据被其他事务取读到了）
读已提交：解决了脏读的问题，但是存在不可重复读的问题
不可重复读：一个事务在提交之前，在事务过程中取读以前曾经读过的数据发生了改变，但是会产生幻读，幻读和不可重复读相似，但是幻读是新增或删除，不可重复读是修改
幻读：一个事务按照相同的条件重新检索，却出现了其他事务插入的新数据

CBLL如何实现隔离性

隔离性实现之一就是锁机制也就是CBLL在读取事务之前加锁,防止其他事务修改

MYsql支持哪些锁

从粒度上分:
行锁: 锁住某一行数据
表锁: 锁住整张表
间隙锁(区间锁): 锁住某一区块的数据

从类型上分
共享锁: 共享锁又叫做s锁,多个事务对于同一数据可以共享一把锁,都可以访问数据,但它不能修改,因此也叫做读锁加锁的方式 : 查询语句之后使用Lock in shrea mode
排他锁: 排他锁又叫做x锁也叫做写锁,不能与其他事务共存,更新数据自动加排他锁,但是不加锁的查询可以查询到数据MVCC机制,实现方式是: sql语句后面加for update
意向锁: 表级别的锁,可以分为意向共享锁和意向排他锁

从用法上可以分为乐观锁和悲观锁

从算法上分:
临键锁: 锁住数据所在的区间,同时也锁住相邻靠右的一个区间
还有间隙锁和记录锁

InnoDB的锁级别是什么？

innodb的行锁是通过给索引的索引项糖加锁是西安的，至于根据缩影台哦见检索innodb才会使用行锁，否则会变成表锁

innodb的行锁算法

innodb默认的行锁是临键锁的算法
当innodb使用临键锁时不止会锁住数据所在的区间，还会锁住相邻的下一个区间这样就能防止幻读

还有哪些行锁算法

Relord：记录锁，gap：间隙锁 next_key: 临键锁

当用索引进行了一个检索，并且检索的条件是一个范围查询，当检索条件落在某一区间，不仅会对这个区间佳作还会将相邻的下一个区间加锁

当用索引查询并且使用区间查询没有命中就会退化为间隙锁
如果用等值查询能够查到唯一一条记录这时就是记录锁

临键锁算法

当sql执行按照索引进行数据检索时，查询条件为范围查询并且有数据命中此时的锁就是临键锁，锁住的是记录所在的区间，以及相邻的下一个区间（都是左开右闭）没有查询到记录就会退化为区间锁或是记录锁

间隙锁和记录锁的区别

GAP(间隙锁）：当执行sql时按照索引进行检索
查询条件为范围查找，或是等值查找并并没有数据命中
只能在RR这种事务隔离级别中存在

Relord(记录锁）：
当使用唯一索引（包括主键索引和唯一索引），条件为精确匹配时，命中唯一一条数据时就是记录锁

MVCC实现隔离性

MVCC机制：事务第一次去读数据时，生成一个数据请求时间的一致性数据快照，并且用这个快照提供语句级或事务级的一致性取读
数据库中有三个隐藏字段，MVCC机制就是通过隐藏字段来实现的

隐藏字段有哪些

DB_ROW_ID:行id占用6个字节，当没有主动给表设置主键，也没有设置唯一索引时，那么自动给这个字段加索引
DB_IRX_ID: 记录install 或Update的事务id 6个字节，每当install或update数据前需要当前事务id
DB_ROLL_PTR：记录delete的事务id7个字节，每当del需要拿到当前事务id，记录到此

怎么调整数据库参数

优化mysql的参数是可以增加资源利用率，从而达到mysql服务器性能的目的
MySQL的参数是在my.conf和my.ini中

数据库设计遵守哪些原则

需要遵从三大范式，列选择原则，反范式设计
三大范式是：
第一范式：有主键，且具有原子性不可再分原则
第二范式：完全依赖，表中非主键不存在对主键部分的依赖，要求每一个表只能描述一件事
第三范式：没有爨地依赖表中的列不存在对非主键列的传递依赖

在建表时如何确定字段

字段类型优先级：
int > date,time > char,varchar>blob
字段长度够用就行
避免使用null
非负型的数据优先使用无符号
财务数据有限使decimal类型

如何确定sql是最优的

使用explan 关键字可以模拟优化器sql查询语句，从而知道mysql是如何处理sql ，分析查询语句是表结构的性能瓶颈

explan可以分析哪些指标

表的读取顺序
数据取读操作的操作类型
哪些索引可以使用
哪些索引实际被使用
表之间有哪些引用
select_type: 表示select的查询类型，区别是通过普通查询，联合查询，子查询，复杂查询等
select_type的值越简单越好
type：表的链接类型，最佳到最差分别是System->Const->eq-ref->ranye->index->all
key: 索引的使用情况

索引：

在查询中in和or谁的效率高

所在列是否有索引，如果有区别不大，如果没有索引in的效率高于or

索引的本质和作用

数据库的本质是数据结构，这种数据结构能帮助我们快熟获取数据库中的数据，有了索引相当于给数据加了目录一样，可以快速找到数据，如果不使用索引则需要一点一点去查找数据
简单来说索引就是为了提高查询效率

索引的优缺点：

优点：
可以通过建立主键索引或唯一索引保证数据库中的每一行数据的唯一性
建立缩影可以大大提高检索速度，以及减少表的检索行数，从而提高查询效率
在分组和排序，进行数据检索可以检索查询的时间和分组排序所消耗的时间

缺点：
在建立索引，维护索引会消耗大量的时间，会随着数据增大而增加
索引文件会占用物理空间
当对表的数据进行更新操作的时候，索引也要动态维护，降低了维护速度
如果在一张表中创建多种索引会造成索引文件的膨胀

索引的原理

如果没有建立索引，数据是通过以页为单位存储，页与页之间是双向链表的关系，每页中存储了具体的行数据，当查询数据需要在双向链表中查找，速度相对低下，建立索引增加了查询速度，索引其实就是b+tree
B+Tree的特点是每一个非叶子节点不存储数据，会将数据交给叶子节点存储，非叶子节点存储的是键和指向这些数据的指针，叶子节点存储的是数据没有指针
树的特征是所有在左边的都比根小所有在右边的都比根大，树把检索效率提高了N倍