MYSQL面试知识

2024-05-05 21:46:32 浏览数 (1)

Q1、mysql数据库是关系型数据库

Q2、存储引擎是InnoDB、MYISAM

Q3、InnoDB与MyISAM的区别？

  • InnoDB支持事务；支持外键；在RC和RR模式下支持MVCC
  • InnoDB支持崩溃修复

Q3、索引有哪些类型，区别又是什么？

  • 聚簇索引和非聚簇索引：主键和数据一起存放的叫做聚簇索引，不在一起存放的叫做非聚簇索引
  • 主键索引和二级索引：二级索引就是非主键的索引
  • 联合索引：多个字段创建的索引叫做联合索引。联合索引有最左匹配原则。
    • MySQL会根据联合索引的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引最左侧字段相匹配的字段，会使⽤该字段过滤⼀批数据.直⾄联合索引中全部字段匹配完成，或者在执⾏过程中遇到范围查询，如 > 、 < 、 between 和 以**%**开头的**like**查询 等条件，才会停⽌匹配

Q3、索引失效的场景

  • “>”、 "<" 、 between和 以%开头的like查询

Q4、索引结构和存储方式？

MySQL的索引使用的是B Tree。但是存储内容与存储引擎有关

  • 在MyISAM下叶子节点存储的是数据的地址，通过索引找到地址，再通过地址找数据。所以也是非聚簇索引
  • InnoDB引擎下，主键索引------叶子节点存储的完整的数据（所以也成为聚簇索引），非叶子节点存储的是主键Key值；非主键索引-------叶子节点存储的是主键key，再回到主键索引处找到完整数据

主键和数据一起存放的叫做聚簇索引，不在一起存放的叫做非聚簇索引

Q5、数据库事务

事务的特性

  • 原子性、一致性（执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的）、隔离性、持久性

Q6、并发事务带来的问题

  • 脏读
  • 丢失修改
  • 不可重复读：一个事务内，读取到的数据发生了修改
  • 幻读：一个事务内，读取到的数据有新增或者减少

Q7、事务的集中隔离级别

  • 读未提交
  • 读已提交RC
  • 可重复度RR：InnoDB使用RR级别 Next-key lock算法，保障了事务的ACID四个特性
  • 串行化

并发事务存在的问题

MySQL中默认的隔离级别是：RR。但是也保证了事务的ACID特性。实现原理是锁 MVCC机制

RR下怎么保证不会幻读

快照读：由MVCC机制保证不出现幻读

当前读：使⽤Next-Key Lock加锁保证不出现幻读， Next-Key Lock 是⾏锁（Record Lock）和间隙锁

（Gap Lock）的结合，⾏锁只能锁住已经存在的⾏，为了避免插⼊新⾏，需要依赖间隙锁。

Q9、数据库锁的类型

  • 表级锁。表级锁锁的是整个表数据
  • 行级锁：针对的是索引加锁。

9.1、MySQL日志都有哪些，作用

Q1、binlog日志，是server端的日志

主要用于主从数据同步，记录的模式包括：statement、mixed、row（默认）

Q2、redolog日志，是存储引擎层的日志

主要用户崩溃数据修复。mysql实例在挂了或者宕机后，会使用存储引擎中的redo log日志来恢复数据

实例会将磁盘中的redo log加载到buffer pool中恢复，同时在事务操作数据时，通过刷盘机制，来保证buffer pool中的redo log同步到磁盘中。默认是事务提交时刷一次

redo log 有3种刷盘策略：

默认情况下是=1。每次事务提交时都执⾏刷盘操作InnoDB引擎后台有⼀个线程，每隔1s，会把 redo log buffer 中的内容写到⽂件系统缓存，然后调⽤fsync刷盘

当 redo log buffer 占⽤的空间即将达到 innodb_log_buffer_size ⼀半的时候，后台线程会主动刷盘

Q3、redo log和binlog都是保证数据修改的一致性，怎么确保

9.2、数据库的事务特性

事务的特性

原子性：事务中的sql语句，要么都执行，要么都不执行。底层使用undo log机制实现

一致性：数据的逻辑关系保持一致

隔离性：一个事务和另一个事务之间的数据是隔离的。MVCC实现隔离性，

持久性：事务提交的变更要持久化到磁盘中。底层使用redo log机制实现

原子性实现的原理

undo log机制实现

每行数据除了业务数据，还有修改当前数据的事务id，指向上一次修改的快照数据的指针，连接成一个链表；

insert产生的undo log在事务提交之后就会删除；

update、delete产生的undo log需要保存到undo log链表中以供后面MVCC机制使用

隔离级别包括哪些

  • 读未提交
  • 读已提交
  • 可重复度读
  • 串行化

并发事务存在的问题

  • 脏读
  • 更新丢失
  • 不可重复读
  • 幻读

RR级别怎么实现

RR级别是指可重复读，也就是在事务内对于一条数据，多次读取获得的结果都是一样的，原理是采用了MVCC机制 间隙锁

RR级别怎么做到的可重复读

  • 使用MVCC机制。MVCC：一致性非锁定读。在不加锁的情况下实现多地读取数据值一致。

①、每行数据的隐藏字段：最后一次变更改行的事务id；回滚指针：指向该行数据的undo log

②、ReadView结构：当前事务id、当前数据库中活跃的事务id列表、可见的事务id界限

③、事务可见性算法：在事务第一个select语句开始之前创建ReadView，记录当前活跃的事务id列表，在读取数据过程中，通过可见性算法判断读取的数据

InnoDB支持崩溃可修复的原理

9.3、一条sql语句的执行过程

MySQL分为server端、存储引擎两部分

  • sql语句进入server端，会经过连接器、分析器、优化器和执行器；分析器会分析语法是否正确、优化器会将sql语句进行优化
  • 存储引擎会有redo log日志，提交修改的时候会分为两步：①预提交redolog & 保存binlog、②提交redo log

9.4、MySQL数据库高性能优化建议

①、单表数量不能超过500w；

②、TEXT、BLOB类型的字段存储：TEXT一般存储长文本字符串；varchar(n) n代表的是字符数；text(m)m代表的是字节数

Q2、SQL语句突然执行的很慢怎么解决？

  • 如果是之前很快，偶尔有一次很慢，可能是其他问题导致的
  • 如果你查询的数据行刚好被加锁了，只能等待别人释放锁：show processlist查看
  • 如果是一致都满，那大概率是这条sql有问题
  • <font color=red>是否没有使用上索引</font>。使用explain sql 语句 如果字段没有添加索引可以依据业务逻辑判断是否需要添加 如果字段有索引没用上，那就是导致索引失效，有这么几种原因： 查询使用了函数、查询时进行了列运算、查询时字段有类型转换、查询时未按照最左匹配、模糊查询中%在前面、使用了is not null 没有建立索引

Q、查看数据库表索引的命令

show index from table_xxx

9.5、常见的SQL问题

1、<font color=blue>深分页导致的性能问题</font>

  • 当数据量比较大的时候，使用select .... limit x,y语句的时候where条件如果是<font color=Red>非聚簇索引</font>>，那么会有回表的动作；同时limit语句的底层是查询到【0 - x y】条都查出来，然后截取y条返回，所以会出现【0 - x】无用的扫描
  • 解决方案就是：
    • 子查询：select from table_name where xxx >、=、< (select a.id from table_name where yyy limit x, 1)limit 10
    • 偏移法：需要知道上一次分页查询的最大id && id是自增的select ... from table_name where id > lastMaxId order by id limit 10

2、建索引的原则

  • 最左匹配原则。mysql会根据联合索引的字段，从左往右依次匹配查询。遇到范围查询(>、<、between、like)则停止
  • 尽量选择区分度高的列设置为索引。
  • 索引列在查询时不要使用函数计算。
  • 尽量在原有的索引上扩展，不要频繁新加索引。

3、遇到的SQL问题

  • 深分页的问题：查询表中的数据，按照条件每次查50条，使用limit语句，然后执行通知操作
  • 最左匹配原则没有应用上
  • 索引字段是数值类型，查询语句中写成了字符串。导致索引没有应用上
  • 分解大查询
mysql

0 人点赞