Mysql面试题

发布时间:2024年01月19日

1 MySql, Oracle,Sql Service的区别

  • Sql Service只能在Windows上使用,而MySql和Oracle可以在其他系统上使用, 而且可以支持数据库不同系统之间的移植

  • MySql开源免费的,Sql Service和Oracle要钱。

  • Oracle支持大并发量,大访问量,Sql Service还行,而MySql的话压力没这么大,因此现在的MySql的话最好是要使用集群或者缓存来搭配使用。(mysql单击并发量500左右)

2 数据库三大范式是什么

  • 第一范式:每个列都不可以再拆分。

  • 第二范式:在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分(表必须有主键)

  • 第三范式:在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键。(表中的字段不要有冗余)

在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

数据库设计软件: powerdesigner pd

3 数据库经常使用的函数

  • count(*/column):返回行数

  • sum(column): 返回指定列中唯一值的和

  • max(column):返回指定列或表达式中的数值最大值

  • min(column):返回指定列或表达式中的数值最小值

  • avg(column):返回指定列或表达式中的数值平均值

4 MyISAM与InnoDB区别

  • Innodb引擎:Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。

  • MyIASM引擎(原本Mysql的默认引擎):不提供事务的支持,也不支持行级锁和外键。

  • InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。

  • InnoDB的主键索引的叶子节点存储着数据,因此主键索引非常高效。

  • MyISAM索引的叶子节点存储的是数据的地址,需要再寻址一次才能得到数据。

  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的数据,因此查询时做到覆盖索引会非常高效。

5 聚簇索引和非聚簇索引

聚簇索引: 将数据存储与索引放到了一块,找到索引也就找到了数据

非聚簇索引:将数据存储和索引分开结构,索引结构的叶子节点指向了数据的对应行。

6 什么是索引?

索引是一种数据结构。数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据,索引的实现通常使用B+树。

7 索引有哪些优缺点?

索引的优点

  • 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

索引的缺点

  • 时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率;

  • 空间方面:索引需要占物理空间。

8 有哪些索引,他们的作用是?

  • 唯一索引:不允许有俩行具有相同的值

  • 主键索引:为了保持数据库表与表之间的关系(唯一且不为空)

  • 聚集索引:数据存储与索引放到了一块

  • 非聚集索引:将数据存储和索引分开结构,索引结构的叶子节点指向了数据的对应行。

  • 复合索引:在创建索引时,并不是只能对一列进行创建索引,可以与主键一样,讲多个组合为索引(最左匹配原则)

  • 全文索引: 全文索引为在字符串数据中进行复杂的词搜索提供有效支持

9 索引的数据结构

索引的数据结构和具体存储引擎的实现有关,在MySQL中使用较多的索引有Hash索引B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快。

10 B+树索引

  • 叶子节点存数据,非叶子节点存主键+地址

  • 叶子节点之间通过双向链表连接,这样方便范围查询(id in between 1 and 3 )

  • innoDB中,叶子节点16kb(页),假设一条数据1kb,那么一个叶子节点存16条数据;非叶子节点主键按bigint8字节,指针(地址值)6字节,161024/(8+6) =1170。B+树高度为3,1170**117016=2100万,查询的时候只需要3次就能从2100万的数据里找到我们想要的数据。

  • 不管有多少数据,只需要查3次,范围查询的数据也很快

  • hash索引是不支持范围查询的。

11 索引使用原则

  • 在离散度大的列上(重复值表较少的列)使用索引。在 B+Tree 里面的重复值太多,MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候,就算建了索引,也不一定会走索引。

  • 在用于 where 判断 order 排序和 join 的(on)字段上创建索引。(适合在条件字段上索引)

  • 索引的个数不要过多。——浪费空间,更新变慢。

  • 频繁更新的值,不要作为主键或者索引

  • 联合索引把散列性高(区分度高)的值放在前

12 联合索引是什么?为什么需要注意联合索引中的顺序?

  • MySQL可以使用多个字段同时建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。(最左匹配原则)

13 索引失效的情况

  1. 尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描

  2. 尽量避免使用in 和not in,会导致引擎走全表扫描

  3. 尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描

  4. 尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描

  5. 尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描 where age+1=5

  6. 当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描

  7. 查询条件不能用 < > 或者 != union

  8. where条件仅包含复合索引非前置列

  9. 隐式类型转换造成不使用索引 where age = '1'

14 怎么解决慢查询问题

1 开启慢查询日志,并且设置慢查询时间,我们当时设置的时间是1秒。

2 在服务器上打开慢查询日志文件(mysql-slow.log),sql语句执行时间超过1秒的都会在慢日志里记录

3 定位到sql语句以后,要分析sql语句问题,有木有加索引,没有加索引可以考虑加索引。加了索引那么就需要查看sql是否走了索引

4 explain + sql语句,查看type指标,类型至少是range 以上级别,如果低于range ,也就是 index 、 ALL级别,就需要考虑sql语句索引失效的问题。

15 undo log

1 事务回滚日志 :

是 Innodb 存储引擎层生成的日志,实现了事务中的原子性,主要用于事务回滚和 MVCC

我们在执行执行一条“增删改”语句的时候 , MySQL 会隐式开启事务来执行“增删改”语句的,执行完就自动提交事务的,这样就保证了执行完“增删改”语句后,我们可以及时在数据库表看到“增删改”的结果了。

执行一条语句是否自动提交事务,是由 autocommit 参数决定的,默认是开启。所以,执行一条 update 语句也是会使用事务的。

undo log 是一种用于撤销回退的日志。在事务没提交之前,MySQL 会先记录更新前的数据到 undo log 日志文件里面,当事务回滚时,可以利用 undo log 来进行回滚。

2 MVCC(多版本并发控制)

undo log 还有一个作用,通过 ReadView + undo log 实现 MVCC(多版本并发控制) 。

16 Buffer Pool

缓冲池 : 提高数据库的读写性能 ,原理跟redis差不多

  • 当读取数据时,如果数据存在于 Buffer Pool 中,客户端就会直接读取 Buffer Pool 中的数据,否则再去磁盘中读取。

  • 当修改数据时,如果数据存在于 Buffer Pool 中,那直接修改 Buffer Pool 中数据所在的页,然后将其页设置为脏页(该页的内存数据和磁盘上的数据已经不一致),为了减少磁盘I/O,不会立即将脏页写入磁盘,后续由后台线程选择一个合适的时机将脏页写入到磁盘。

17 redo log

Buffer Pool 是提高了读写效率没错,但是问题来了,Buffer Pool 是基于内存的,而内存总是不可靠,万一断电重启,还没来得及落盘的脏页数据就会丢失。 为了防止断电导致数据丢失的问题,当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log 里面,并更新内存,这个时候更新就算完成了。

在事务提交时,只要先将 redo log 持久化到磁盘即可,可以不需要将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。当系统崩溃时,虽然脏页数据没有持久化,但是 redo log 已经持久化,接着 MySQL 重启后,可以根据 redo log 的内容,将所有数据恢复到最新的状态。

  • redo log记录的是事务完成以后数据的操作,可以解决 Buffer Pool因为缓存数据丢失的问题。

18 undo log 和redo log

redo log 记录了此次事务「完成后」的数据状态,记录的是更新之「后」的值;

undo log 记录了此次事务「开始前」的数据状态,记录的是更新之「前」的值;

事务提交之前发生了崩溃,重启后会通过 undo log 回滚事务,事务提交之后发生了崩溃,重启后会通过 redo log 恢复事务。

19 binlog

undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的。

MySQL 在完成一条更新操作后,Server 层还会生成一条 binlog,等之后事务提交的时候,会将该事物执行过程中产生的所有 数据操作 统一写 入 binlog 文件。

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志,不会记录查询类的操作 。

所有存储引擎都可以使用

20 主从复制是怎么实现?

MySQL 的主从复制依赖于 binlog ,也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

  • MySQL 主库在收到客户端提交事务的请求之后,会先写入 binlog,再提交事务,更新存储引擎中的数据,事务提交完成后,返回给客户端“操作成功”的响应。

  • 从库会创建一个专门的 I/O 线程,连接主库的 log dump 线程,来接收主库的 binlog 日志,再把 binlog 信息写入 relay log 的中继日志里,再返回给主库“复制成功”的响应。

  • 从库会创建一个用于回放 binlog 的线程,去读 relay log 中继日志,然后回放 binlog 更新存储引擎中的数据,最终实现主从的数据一致性。

文章来源:https://blog.csdn.net/Mangcun_tutu/article/details/135699190
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。