Mysql DBA常见面试题 mysql高级dba面试题

转载

mob64ca13ff28f1 2024-01-15 10:01:24

文章标签 Mysql DBA常见面试题数据库 mysql 数据 MySQL 文章分类 MySQL 数据库

在各类技术岗位面试中，似乎 MySQL 相关问题经常被问到。无论你面试开发岗位或运维岗位，总会问几道数据库问题。其实很多面试题都是大同小异的，提前做准备还是很有必要的。本篇文章简单说下常见的面试题，一起来学习下吧。

1.什么是关系型数据库？谈谈你熟悉的数据库产品。

关系型数据库是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据。关系型数据库最大的特点是支持事务。

RDBMS ：MySQL、Oracle、PostgreSQL、DB2 等。
NOSQL : Redis 、MongoDB 、ES 、memcache等
云数据库 : RDS 、TDSQL
NewSQL : PolarDB、TiDB

MySQL 是当下最流行的开源数据库。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，使得很多公司都采用 MySQL 数据库以降低成本，目前被广泛地应用在 Internet 上的中小型网站中。MySQL非常适合业务逻辑相对简单、数据可靠性要求较低的互联网场景，尤其适用于 OLTP 领域。

PostgreSQL是一个开源对象关系型数据库管理系统，侧重于可扩展性和标准的符合性，被业界誉为“最先进的开源数据库”。PostgreSQL是完全免费的，它是BSD协议。提供了多版本并行控制，支持几乎所有 SQL 构件(包括子查询，事务和用户定义类型和函数)，稳定性极强，性能高速度快，‘无锁定’等特性，PG更加适合严格的企业应用场景。

2.MySQL 常见的存储引擎有哪些，有什么区别？

常见的几种存储引擎：

InnoDB: MySQL 默认的存储引擎，支持事务、MVCC、外键、行级锁和自增列。

MyISAM: 支持全文索引、压缩、空间函数、表级锁，不支持事务，插入速度快。

Memory: 数据都在内存中，数据的处理速度快，但是安全性不高。

ARCHIVE: 常用于历史归档表，占用空间小，数据不能更新删除。

innodb支持：

Mysql DBA常见面试题 mysql高级dba面试题_MySQL

InnoDB 与 MyISAM 引擎的几点区别：

InnoDB 支持事务，MyISAM 不支持事务。
InnoDB 支持外键，而 MyISAM 不支持。
InnoDB 不支持全文索引，而 MyISAM 支持。
InnoDB 是聚簇索引，MyISAM 是非聚簇索引。
InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。
存储结构不同，MyISAM 表分为 frm MYD MYI 三个，InnoDB 一般分为 frm ibd 两个。
多缓存区支持（innodb_buffer_pool/innodb_log_buffer）
change buffer
double write buffer
crash recovery
Adaptive Hash index
MVCC

例如：
Adaptive Hash index
自适应的HASH索引，简称：AHI，默认开启。AHI是InnoDB存储引擎独有的机制，InnoDB存储引擎只能够创建Btree。

AHI作用：
自动评估"热"的内存索引page,生成HASH索引表。
帮助InnoDB快速读取索引页。加快索引读取的效果。
相当于索引的索引。

Mysql DBA常见面试题 mysql高级dba面试题_Mysql DBA常见面试题_02

Change buffer

默认大小：mysql占用内存的25%

比如insert，update，delete 数据，对于聚簇索引会立即更新叶子节点；对于辅助索引，不是实时更新的。在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。

使用Change buffer 功能是临时缓冲辅助索引需要的更新数据。当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。

Mysql DBA常见面试题 mysql高级dba面试题_MySQL_03

3.描述下 MySQL 基础架构。

MySQL 架构

Mysql DBA常见面试题 mysql高级dba面试题_数据_04

一条 select 语句执行流程

Mysql DBA常见面试题 mysql高级dba面试题_mysql_05

MySQL的逻辑架构主要分为3层：

连接层功能：

连接协议: tcp/ip和Unix套接字socket 要开启服务,才能连接
加载授权表(mysql.user/mysq.db/tablespriv/mysql.columnspriv) :用户密码验证
通过密码验证后生成连接线程

Server层功能：

SQL语法检查、语义检查
对象存在性、权限检查
SQL语句解析，预处理—>生成解析数，并统计执行代价
优化器优化
按照执行计划执行SQL语句

存储引擎层功能：
ENGINE层获取16进制的数据，将数据返回给SQL层，SQL层生成表格形式返回到显示界面上。

一条 select 语句执行流程：

①通过客户端/服务器通信协议与MySQL建立连接。

②查询缓存,这是MySQL的一个可优化查询的地方,如果开启了Query Cache且在查询缓存过程中查询到完全相同的SQL语句,则将查询结果直接返回给客户端；如果没有开启Query Cache或者没有查询到完全相同的SQL语句则会由解析器进行语法语义解析,并生成解析树。

③预处理器生成新的解析树。

④查询优化器生成执行计划。

⑤查询执行引擎执行SQL语句,此时查询执行引擎会根据SQL语句中表的存储引擎类型,以及对应的API接口与底层存储引擎缓存或者物理文件的交互情况,得到查询结果,由MySQL Server过滤后将查询结果缓存并返回给客户端,若开启了Query Cache,这时也会将SQL语句和结果完整地保存到Query Cache中,以后若有相同的SQL语句执行则直接返回结果。

4.说说常用的几种字段类型。

这个问题考察面试者对 MySQL 字段类型的了解程度，可以延伸出很多小问题，例如 char 与 varchar 的区别。

常用的字段类型分类：

数值型：

Mysql DBA常见面试题 mysql高级dba面试题_mysql_06

字符串类型：

Mysql DBA常见面试题 mysql高级dba面试题_MySQL_07

日期和时间类型：

Mysql DBA常见面试题 mysql高级dba面试题_MySQL_08

CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间。当保存CHAR值时，在它们的右边填充空格以达到指定的长度，当检索到CHAR值时，尾部的空格被删除掉。VARCHAR类型用于存储可变长字符串，存储时，如果字符没有达到定义的位数，也不会在后面补空格。char(M) 与 varchar(M)中的的 M 都表示保存的最大字符数，单个字母、数字、中文等都是占用一个字符。

char和varchar数据类型的选择?

例如：存年龄：使用tinyint还是char(3)哪个好？?

tinyint 最大值255

char(3) 999 不管你存几位数，都是三字节长度存储空间

例如：存名称使用char(3),还是varchar（3）?

a.站在数据插入性能角度思考，应该选择：char 不用判断字符长度，直接存放

b.从节省空间角度上思考，应选择：varchar x+1/2位

c.从索引数高度角度考虑，应选择：varchar

建议使用varchar类型存储变长列值。

5.讲讲索引的作用及结构及注意事项。

索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。

InnoDB 引擎下，主要使用的是 B+Tree 索引，每个索引其实都是一颗B+树，B+树是为了磁盘及其他存储辅助设备而设计的一种平衡查找树(不是二叉树)，在B+树中，所有的数据都在叶子节点，且每一个叶子节点都带有指向下一个节点的指针，形成了一个有序的链表。

Mysql DBA常见面试题 mysql高级dba面试题_数据_09

从物理存储角度来看，InnoDB 索引可分为聚簇索引（clustered index）和二级索引（secondary index）或辅助索引。聚簇索引的叶子节点存的是整行数据，当某条查询使用的是聚簇索引时，只需要扫描聚簇索引一颗B+树即可得到所需记录，如果想通过二级索引来查找完整的记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的记录。

索引考虑的事项
回表是什么？回表会带来什么问题？怎么减少回表？
按照辅助索引列，作为查询条件时，先查找辅助索引树，再到聚簇索引树查找数据行的过程。
IO量多、IO次数多、随机IO会增多
减少回表：

辅助索引能够完全覆盖查询结果，可以使用联合索引。
尽量让查询条件精细化，尽量使用唯一值多的列作为查询条件
优化器：MRR（Multi-Range-Read），锦上添花的功能。
mysql> select @@optimizer_switch;
mysql> set global optimizer_switch=‘mrr=on’;

功能：