MySQL DBA面试题

1.请用图框的方式大致地描绘出MySQL架构体系.

第一层: client Connector

ODBC、jdbc、api(C/JAVA/PERL/PYTHON/PHP)

第二层:MYSQL server 模块

thread connection pool/cache

sql interface(DDL/ DML/ TRIGGER/ VIEW /STORE PROCEDURE/ EVENT等)

sql parse & check privileges

sql optimize(explian)

server 统计/buffer:query cache

server admin manager command: backup restore security replicate 等

第三层:存储引擎

myisam/innodb/blackhole/archive/memory/merge/NDB

存储引擎是基于表

第四层:存储引擎相应的文件

logs file:binlog/err/general/slow.server 层维护

myisam: frm/myi/myd

innodb: frm/ibd(index&data)/redo log/Undo log(5.7)

2.限定MySQL5.5及以下为例，InnoDB存储引擎与MyISAM存储引擎的区别，至少写四点.

MYISAM(5.5.8前) INNODB

锁: 表锁行锁

存储限制256TB 64TB

文件类型FRM/MYD/MYI FRM/ibdata

数据保存堆表索引组织表

外键NO YES

事务: NO YES.4种隔离级别

MVCC(实现一致性非锁定读) NO YES

mvcc通过读取undo段内容生成的最新快照数据

# tablespace包含的内容

索引缓存YES YES

数据缓存NO YES

查询缓存YES YES

# index

二级索引叶节点行地址行主键

B-tree index YES YES

T-tree index NO NO

Hash index NO NO,adaptive

fulltext index YES 5.6后支持

空间数据YES YES

空间索引YES NO

索引max长度(byte) 1000 768(1-2byte head)

memcache NO 5.6后支持

[在server层实现，并不是存储引擎实现的功能[/color]

压缩数据支持(只读) 支持，但必须是Barracuda file format

加密数据支持

同步支持

备份

点恢复

[备份]

# 单表备份

myisam 可以直接拷贝frm/myd/myi文件即可

innodb 不能直接拷贝文件

[other]

表行数:innodb需要全部遍历/ MYISAM实时维护，不需要全表遍历。

auto_increment: 列上必须有索引，但innodb必须是第一列/myisam可以是任何一列

# innodb

-- 5.5 1.1

faster add/drop second index:copy data改为inplace

insert buffer（二级索引更新效率)

double write(写安全)

adaptive hash index(读效率)

aio(asynchronous io)提供磁盘读效率。

一次select扫描多次索引页(IO离散读)，没扫描一次索引，需要等待完成才能开始下次扫描，而异步IO可以发出IO扫描指令后，不需要等待返回结果，立即发送下一个IO扫描指令，并行进行扫描

flush neighbor page(提供写效率，多个IO合为1个IO).ssd硬盘不需要开启

-- 5.6 1.2

memcache

fulltext

#myisam

单表最多2^63行

每个索引最多16个列

每个表最多64个索引

支持并发插入concurrent inserts

可以结合merge引擎，将多个表组合为1个表

3.MySQL中控制内存分配的全局参数，有哪些？（注：至少写6个以上）

binlog_cache_size：Global，5.9之后只是针对事务语句的cache，大事务需要增加此值binlog_stmt_cache_size：Global，非事务语句cache，5.9之后废弃

max_binlog_size：Global，binlog文件最大值

max_heap_size：Global

tmp_table_size：Global，Session7

using temporary table时，在session中设置此值，超过则memory改为disk myisam表

query_cache_size：Global

query_cache_limit:Global，查询结果超过此值则不进入缓存，防止大查询将cache清空thread_cache_size：Global

bulk_insert_buffer_size：Global，Session

insert ... select ..; insert values (...),(...);load data infile

join_buffer_size：Global，Session

complex查询涉及多个表join时就需要使用多个join buffer

sort_buffer_size：Global，Session

table_definition_cache：Global

表多时，需要增加此值。太小会影响表打开速度，不占文件描述符

table_open_cache：Global，Session。占用文件描述符

1个表被N个线程使用，会被打开N次

1个表在当线程内也会被打开M次。select * from tb as t1,tb as t2;则tb被打开2次

如果打开表时cache满了，并且所有表都在使用，则cache会被临时扩展，当某个表可被回收时则释放临时扩展的空间

# myisam

MYD每个线程一个文件描述符，MYI所有线程公用一个文件描述符

[innodb]

innodb_buffer_pool_size：Global

innodb_additional_mem_pool_size：Global，申请的操作系统缓存，不占用buffer pool innodb_log_buffer_size：Global

innodb_log_file_size：Global