mysql dba 面试题部分答案
博客专区 > fred-tao 的博客 > 博客详情
mysql dba 面试题部分答案
fred-tao 发表于3年前
mysql dba 面试题部分答案
  • 发表于 3年前
  • 阅读 355
  • 收藏 5
  • 点赞 0
  • 评论 2

腾讯云 技术升级10大核心产品年终让利>>>   

摘要: 在网络上搜寻的mysql dba 面试答案,有参考也有补充,如有问题也请大家提出建议

MySQL DBA的基础面试题目

 

1 mysql的复制原理以及流程。

1)先问基本原理流程,3个线程以及之间的关联。

 

2)再问一致性,延时性,数据恢复。

 

 

3)再问各种工作遇到的复制bug的解决方法



2
mysqlmyisaminnodb的区别,至少5点。

1 5点不同

1>.InnoDB支持事物,而MyISAM不支持事物

2>.InnoDB支持行级锁,而MyISAM支持表级锁

3>.InnoDB支持MVCC, MyISAM不支持

4>.InnoDB支持外键,而MyISAM不支持

5>.InnoDB不支持全文索引,而MyISAM支持。


2 问各种不同mysql版本的2者的改进

 mysql-server-4.1

  增加了子查询的支持,字符集增加UTF-8GROUP BY语句增加了ROLLUPmysql.user表采用了更好的加密算法,innodb开始支持单独的表空间。

  mysql-server-5.0

  增加了Stored proceduresViewsCursorsTriggersXA transactions的支持,增加了INFORATION_SCHEMA系统数据库。

  mysql-server-5.1

  增加了Event schedulerPartitioningPluggable storage engine API Row-based replicationGlobal级别动态修改general query logslow query log的支持


32者的索引的实现方式

虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。



3
,问mysqlvarcharchar的区别以及varchar(50)中的30代表的涵义。

1varcharchar的区别

        char是一种固定长度的类型,varchar则是一种可变长度的类型

2varchar(50)50的涵义

         最多存放50个字节

3int20)中20的涵义

         int(M)中的M indicates the maximum display width (最大显示宽度)for integer types. The maximum legal display width is 255.

4)为什么MySQL这样设计?

      这需要根据经验来谈

 


4
,问了innodb的事务与日志的实现方式。 

1)有多少种日志

错误日志:记录出错信息,也记录一些警告信息或者正确的信息

慢查询日志:设置一个阈值,将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。

二进制日志:记录对数据库执行更改的所有操作

  查询日志:记录所有对数据库请求的信息,不论这些请求是否得到了正确的执行。

 

2)日志的存放形式

innodb_data_file_path = ibdata1:100M:autoextend:max:500M

3)事务是如何通过日志来实现的,说得越深入越好。

隔离性: 通过 实现

原子性、一致性和持久性是通过 redoundo来完成的。

Innodb存储引擎中,事务日志是通过redoinnodb的存储引擎日志缓冲(Innodb log buffer)来实现的,当开始一个事务的时候,会记录该事务的lsn(log sequence number); 当事务执行时,会往InnoDB存储引擎的日志
的日志缓存里面插入事务日志;当事务提交时,必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制),也就是写数据前,需要先写日志。这种方式称为预写日志方式
innodb
通过此方式来保证事务的完整性。也就意味着磁盘上存储的数据页和内存缓冲池上面的页是不同步的,是先写入redo log,然后写入data file,因此是一种异步的方式。通过 show engine innodb status\G 来观察之间的差距

 



5
,问了mysql binlog的几种日志录入格式以及区别

1)各种日志格式的涵义

2)适用场景

3)结合第一个问题,每一种日志格式在复制中的优劣。

binlog的格式有三种,这也反应了mysql的复制技术:基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication, RBR),混合模式复制(mixed-based replication, MBR)。相应地,binlog的格式也有三种:STATEMENTROWMIXED

参考连接http://www.361way.com/mysqlbinlog-type/934.html



6
,问了下mysql数据库cpu飙升到500%的话他怎么处理?

1 没有经验的,可以不问

2)有经验的,问他们的处理思路



7
sql优化。

(1)    explain出来的各种item的意义

tidid

table:显示这一行的数据是关于哪张表的

type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为consteq_regrefrangeindexheALL

possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEXindexname)来强制使用一个索引或者用IGNORE INDEXindexname)来强制MYSQL忽略索引

key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

ref:显示索引的哪一列被使用了,如果可能的话,是一个常数

rowsMYSQL认为必须检查的用来返回请求数据的行数

Extra:关于MYSQL如何解析查询的额外信息。将

(2)    profile的意义以及使用场景。

Profile(查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.

3explain中的索引问题。


8,
备份计划,mysqldump以及xtranbackup的实现原理,

 

mysqldump是最简单的逻辑备份方式。在备份myisam表的时候,如果要得到一致的数据,就需要锁表,简单而粗暴。而在备份innodb 的时候,加上–master-data=1 –single-transaction 选项,在事务开始时刻,记录下binlog pos点,然后利用mvcc来获取一致的数据,由于是一个长事务,在写入和更新量很大的数据库上,将产生非常多的undo,显著影响性能,所以要慎用

 优点:简单,可针对单表备份,在全量导出表结构的时候尤其有用。

  缺点:简单粗暴,单线程,备份慢而且恢复慢,跨IDC有可能遇到时区问题

xtrabackup它实际上是物理备份+逻辑备份的组合。在备份 innodb表的时候,它拷贝ibd文件,并一刻不停的监视redo log的变化,append到自己的事务日志文件。在拷贝ibd文件过程中,ibd文件本身可能被写,这都不是问题,因为在拷贝完成后的第一个 prepare阶段,Xtrabackup采用类似于innodb崩溃恢复的方法,把数据文件恢复到与日志文件一致的状态,并把未提交的事务回滚。如果同 时需要备份myisam表以及innodb表结构等文件,那么就需要用flush tables with lock来获得全局锁,开始拷贝这些不再变化的文件,同时获得binlog位置,拷贝结束后释放锁,也停止对redo log的监视。

1 备份计划

2)备份恢复时间

3)备份恢复失败如何处理



9
500db,在最快时间之内重启。


10
在当前的工作中,你碰到到的最大的mysql db问题是?



11 innodb的读写参数优化

(1)    读取参数,global buffer pool以及 local buffer

Read_buffer_sizeread_rnd_buffer_sizekey_buffer_sizejoin_buffer_sizequery_cache_limitquery_cache_sizesore_buffer_sizetmp_table_sizemax_heap_table_sizemax_connectiontable_cache

(2)    写入参数

Innodb_log_buffer_pool_sizeinnodb_log_buffer_sizeinnodb_additional_mem_pool_size

(3)    IO相关的参数

Innodb_file_io_threads

 

(4)    缓存参数以及缓存的适用场景

7.查询缓存(Query Cache)   -- optional

将客户端的SQL语句(仅限select语句)通过hash计算,放在hash链表中,同时将该SQL的结果集

放在内存中cache。该hash链表中,存放了结果集的内存地址以及所涉及到的所有Table等信息。

如果与该结果集相关的任何一个表的相关信息发生变化后(包扩:数据、索引、表结构等)

就会导致结果集失效,释放与该结果集相关的所有资源,以便后面其他SQL能够使用。

当客户端有select SQL进入,先计算hash值,如果有相同的,就会直接将结果集返回。

Query Cache的负面影响:

a.使用了Query Cache后,每条select SQL都要进行hash计算,然后查找结果集。对于大量SQL

访问,会消耗过多额外的CPU

b.如果表变更比较频繁,则会造成结果集失效率非常高。

c.结果集中保存的是整个结果,可能存在一条记录被多次cache的情况,这样会造成内存资源的

过度消耗。

 

Query Cache的正确使用:

a.根据表的变更情况来选择是否使用Query Cache,可使用SQL HintSQL_NO_CACHESQL_CACHE

b.对于 变更比较少 数据基本处于静态 的表,使用SQL_CACHE

c.对于结果集比较大的,使用Query Cache可能造成内存不足,或挤占内存。

可使用1.SQL_NO_CACHE 2.query_cache_limit控制Query Cache的最大结果集(系统默认1M)

12 ,请简洁地描述下MySQLInnoDB支持的四种事务隔离级别名称,以及逐级之间的区别?

SQL标准定义的四个隔离级别为:

read uncommited

read committed

repeatable read

serializable

 

Read Uncommitted(读取未提交内容)

      在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。

Read Committed(读取提交内容)

      这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。

Repeatable Read(可重读)

      这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读(Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的幻影行。InnoDBFalcon存储引擎通过多版本并发控制MVCCMultiversion Concurrency Control 间隙锁)机制解决了该问题。注:其实多版本只是解决不可重复读问题,而加上间隙锁(也就是它这里所谓的并发控制)才解决了幻读问题。

Serializable(可串行化)

这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。

       

对于不同的事务,采用不同的隔离级别分别有不同的结果。不同的隔离级别有不同的现象。主要有下面3种现在:

1、脏读(dirty read):一个事务可以读取另一个尚未提交事务的修改数据。

2、非重复读(nonrepeatable read):在同一个事务中,同一个查询在T1时间读取某一行,在T2时间重新读取这一行时候,这一行的数据已经发生修改,可能被更新了(update),也可能被删除了(delete)。

3、幻像读(phantom read):在同一事务中,同一查询多次进行时候,由于其他插入操作(insert)的事务提交,导致每次返回不同的结果集。

不同的隔离级别有不同的现象,并有不同的锁定/并发机制,隔离级别越高,数据库的并发性就越差,4种事务隔离级别分别表现的现象如下表:



隔离级别

脏读

非重复读

幻像读

read uncommitted

允许

允许

允许

read committed

允许

允许

repeatable read

允许

serializable

 



 

 

 

13,表中有大字段X(例如:text类型),且字段X不会经常更新,以读为为主,请问

1)您 是选择拆成子表,还是继续放一起?

2)写出您这样选择的理由?


14MySQLInnoDB引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的

 

 

15  MyISAM innodb的区别

 

1.      MySQL中控制内存分配的全局参数,有哪些?(注:至少写6个以上)

1>. Key_buffer_size

2>. innodb_buffer_pool_size

3>. innodb_additional_memory_pool_size

4>. innodb_log_buffer_size

5>. query_cache_size

6>.read_buffer_size

7>.read_rnd_buffer_size

 

2.      请简洁地描述下MySQLInnoDB支持的四种事务隔离级别名称,以及逐级之间区别?

Read uncommitted: 在该隔离级别,所有事务都可以看到其他未提交的事务的执行结果。读取未提交的事务,称之为脏读

Read Committed:一个事务只能看见已经提交事务所做的改变。因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。

Repeatable Read:这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。这种级别会出现幻读。

Serializable:这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题

 

 

 

它们之间的区别如下表:

隔离级别

脏读

非重复读

幻像读

Read uncommitted

允许

允许

允许

Read Committed

允许

允许

Repeatable Read

允许

Serializable

 

 

 

 

 

3.      小题集锦

1>.VARCHAR(N) CHAR(N)中的N含义是:

N的含义表示N个字节。前者是变长,N的范围是 0~65535,后者是固定长度,N的范围是0~255

 

2>.若一张表中只有一个字段VARCHAR(N)类型,utf8编码,则N最大值为多少(精确到数量级即可)

由于utf8的每个字符最多占用3个字节。而MySQL定义行的长度不能超过65535,因此N的最大值计算方法为:(65535-1-2)/3。减去1的原因是实际存储从第二个字节开始,减去2的原因是因为要在列表长度存储实际的字符长度,除以3是因为utf8限制:每个字符最多占用3个字节。

因此N=(Floor(65535-1-2)/3)

 

3>.表中有大字段X(例如:text类型),且字段X不会经常更新,以读为为主,请问您

是选择拆成子表,还是继续放一起,并且写出您的理由?

答案:拆成子表

 

理由:可以提高其他字段查询和更新的效率,因为每页保存的行数越多,效率会越高。大字段X更新率低,单行读取时效率影响不大。但如果读取的行数越多,影响会越大。因此选择拆成子表更好。

 

4>.MySQLInnoDB引擎的行锁是通过加在什么上完成(或称实现)的:

A.     数据块

B.     索引值

 

选择答案后,告诉我们为什么?

答:Binnodb表数据是索引组织表形式存放

    

     5>.username字段定义为VARCHAR(40)VARCHAR(200)有啥区别?

       答:临时表varchar(200)占用空间更大

 

5>.MySQL数据库备份方式有那几种(只讨论InnoDB存储引擎),至少写四种。

1>.热备份

   2>.冷备份

   3>.温备份

   4>.二进制日志备份

  

  

4.      MySQL复制搭建M->N的过程,请简述各个步骤?(备注:M已经在线跑,N为新安装的MySQL服务器)

1> .在启动主从服务器时,必须用server_id启动选项给出其ID值。主从服务器的ID值不能相同。主服务器启动二进制日志。

2>.在主服务器上,创建一个账户供从服务器连接主服务器并请求修改信息。

3>.连接到主服务器并通过执行showmaster status 语句确定当前的复制坐标。

4>.在从服务器上为将被复制的数据库建立一份完备的副本。

5>.连接到从服务器并使用changemaster 语句来配置它,包括把用来连接主服务器的参数和初始化复制坐标告诉从服务器。

6>.从服务器开始复制。

 

 

6.      SQL语句优化

SQL语句:

SELECTID,WAYBILL_NO,EXP_TYPE,PKG_QTY,EXPRESS_CONTENT_CODE,EFFECTIVE_TYPE_CODE

FROM T_EXP_OP WHERE ORDERID NOT IN(SELECTORDERID FROM T_EXP_OP WHERE AUX_OP_CODE IN ('NEW','UPDATE','DELETE') AND((OP_CODE IN (176, 162, 171, 131, 136)AND EXP_TYPE IN ('10', '20', '30')) OR(OP_CODE IN (191, 121)AND EXP_TYPE IN ('10', '20')) OR (OP_CODE IN (181,111)AND EXP_TYPE = '10'))) LIMIT 10;

 

条件:

T_EXP_OP表主键为BIGINT类型的ID字段,存储引擎为InnoDB,无其他索引

 

优化后为(提示:优化成一条简单的SQL语句,即无子查询,无JOIN关联):

SELECT ID, WAYBILL_NO, EXP_TYPE, PKG_QTY,EXPRESS_CONTENT_CODE, EFFECTIVE_TYPE_CODE

FROM T_EXP_OP

WHERE

           AUX_OP_CODENOT IN (‘NEW’, ‘UPDATE’, ‘DELETE’)

           AND(OP_CODE NOT IN (176, 162, 171, 131, 136, 191, 121, 181,111)

           ANDEXP_TYPE NOT IN(‘10’, ‘20’, ‘30’))

LIMIT 10;

 

 

 

7.      分页SQL语句优化

SQL语句:

SELECT * FROM test FORCE(idx_m_n) WHEREm=1 ORDER BY n LIMIT 1000,10;

 

条件:

Test表为InnoDB存储引擎,主键为BIGINT类型的ID字段,二级索引:idx_m_n(m,n)

优化后为:

索引要修改为: idx_m_n(m,n,ID); 

SELECT a.* FROM test a

inner join

(SELECT ID FROM test FORCE(idx_m_n) WHEREm=1 ORDER BY n LIMIT 1000,10 ) b

on a.id=b.id

 

请简述优化的理由:

1>.二级索引中没有ID字段,无法达到最优化

2>.采用多表连接,效率会更高

3>.

 

8.      语句挑错

SQL语句:

SELECTM.columnname……,N.* columnname…..

FROMleft_table M  LEFT JOIN right_tableN

ON M.columnname_join=N. columnname_join  ANDN. columnname=XXX AND M.columnname=XXX

 

请问本SQL语句哪里不合理,为啥不合理?

答:N.* columnname….. 应该改为N.columnname……

空格符有明显错误,如:M.  columnname_join应改为:M.columnname_joinN.columnname_join, N. columnname同理。

 

9.      [SELECT *] [SELECT 全部字段]2种写法有何优缺点,至少写出四点

1>.前者要解析数据字典,后者不需要

   2>.结果输出顺序,前者与建表列顺序相同,后者按指定字段顺序。

   3>.表字段改名,前者不需要修改,后者需要改

   4>.后者可以建立索引进行优化,前者无法优化

   5>.后者的可读性比前者要高

  

10.  HAVNG 子句 WHERE的异同点,至少写出3

1>.语法上:where 用表中列名,havingselect结果别名

2>.影响结果范围:where从表读出数据的行数,having返回客户端的行数

3>.索引:where 可以使用索引,having不能使用索引,只能在临时结果集操作

4>.where后面不能使用聚集函数,having是专门使用聚集函数的。

 

11.  分布式数据库产品的特点(至少写4条)

1>.数据分布在多个异地点,抗灾性强

2>.并发性高

3>.受网络影响很大

4>.单机的性能不是特别重要,但是总体成本很高。

5>.扩展性强

 

12.  数据拆分架构的优缺点(至少写8条)

1>.透明性,程序不需要做任何修改

2>.解决集中数据库的扩展局限性。实现水平扩展问题,涉及到数据的拆分问题

3>.提高数据库服务的性能、可靠性、可用性

4>.实现技术不难,开发成本和维护成本可控

5>.测试成本高

6>.无法支持分布式事务

7>.数据拆分后数据合并难

8>.部分功能限制

9>.扩展受限

 

共有 人打赏支持
粉丝 4
博文 10
码字总数 17041
评论 (2)
sakurazens
请问下"mysql数据库cpu飙升到500%的话他怎么处理" 这个500%是什么概念?满负荷不也才100%么? 请指教
fred-tao

引用来自“sakurazens”的评论

请问下"mysql数据库cpu飙升到500%的话他怎么处理" 这个500%是什么概念?满负荷不也才100%么? 请指教
首先,你要确定你cpu的核数,如果你是 10核cpu X100% 哪你的mysql占用cpu500% 哪就是正常的运行 如果达到800-1000% 这个时候就需要查看服务器的资源
×
fred-tao
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: