MySQLDBA面试题

1.请用图框的方式大致地描绘出MySQL架构体系.第一层:clientConnectorODBC、jdbc、api(C/JAVA/PERL/PYTHON/PHP)第二层:MYSQLserver模块threadconnectionpool/cachesqlinterface(DDL/DML/TRIGGER/VIEW/STOREPROCEDURE/EVENT等)sqlparse&checkprivilegessqloptimize(explian)server统计/buffer:querycacheserveradminmanagercommand:backuprestoresecurityreplicate等第三层:存储引擎myisam/innodb/blackhole/archive/memory/merge/NDB存储引擎是基于表第四层:存储引擎相应的文件logsfile:binlog/err/general/slow.server层维护myisam:frm/myi/mydinnodb:frm/ibd(index&data)/redolog/Undolog(5.7)2.限定MySQL5.5及以下为例，InnoDB存储引擎与MyISAM存储引擎的区别，至少写四点.MYISAM(5.5.8前)INNODB锁:表锁行锁存储限制256TB64TB文件类型FRM/MYD/MYIFRM/ibdata数据保存堆表索引组织表外键NOYES事务:NOYES.4种隔离级别MVCC(实现一致性非锁定读)NOYESmvcc通过读取undo段内容生成的最新快照数据#tablespace包含的内容索引缓存YESYES数据缓存NOYES查询缓存YESYES#index二级索引叶节点行地址行主键B-treeindexYESYEST-treeindexNONOHashindexNONO,adaptivefulltextindexYES5.6后支持空间数据YESYES空间索引YESNO索引max长度(byte)1000768(1-2bytehead)memcacheNO5.6后支持[在server层实现，并不是存储引擎实现的功能[/color]压缩数据支持(只读)支持，但必须是Barracudafileformat加密数据支持同步支持备份点恢复[备份]#单表备份myisam可以直接拷贝frm/myd/myi文件即可innodb不能直接拷贝文件[other]表行数:innodb需要全部遍历/MYISAM实时维护，不需要全表遍历。auto_increment:列上必须有索引，但innodb必须是第一列/myisam可以是任何一列#innodb--5.51.1fasteradd/dropsecondindex:copydata改为inplaceinsertbuffer（二级索引更新效率)doublewrite(写安全)adaptivehashindex(读效率)aio(asynchronousio)提供磁盘读效率。一次select扫描多次索引页(IO离散读)，没扫描一次索引，需要等待完成才能开始下次扫描，而异步IO可以发出IO扫描指令后，不需要等待返回结果，立即发送下一个IO扫描指令，并行进行扫描flushneighborpage(提供写效率，多个IO合为1个IO).ssd硬盘不需要开启--5.61.2memcachefulltext#myisam单表最多2^63行每个索引最多16个列每个表最多64个索引支持并发插入concurrentinserts可以结合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：Globaltmp_table_size：Global，Session7usingtemporarytable时，在session中设置此值，超过则memory改为diskmyisam表query_cache_size：Globalquery_cache_limit:Global，查询结果超过此值则不进入缓存，防止大查询将cache清空thread_cache_size：Globalbulk_insert_buffer_size：Global，Sessioninsert...select..;insertvalues(...),(...);loaddatainfilejoin_buffer_size：Global，Sessioncomplex查询涉及多个表join时就需要使用多个joinbuffersort_buffer_size：Global，Sessiontable_definition_cache：Global表多时，需要增加此值。太小会影响表打开速度，不占文件描述符table_open_cache：Global，Session。占用文件描述符1个表被N个线程使用，会被打开N次1个表在当线程内也会被打开M次。select*fromtbast1,tbast2;则tb被打开2次如果打开表时cache满了，并且所有表都在使用，则cache会被临时扩展，当某个表可被回收时则释放临时扩展的空间#myisamMYD每个线程一个文件描述符，MYI所有线程公用一个文件描述符[innodb]innodb_buffer_pool_size：Globalinnodb_additional_mem_pool_size：Global，申请的操作系统缓存，不占用bufferpoolinnodb_log_buffer_size：Globalinnodb_log_file_size：Global[myisam]key_buffer_size：Global影响索引更新速度，越大越快？read_buffer_size：Global，Session对表做连续表数据扫描时使用read_rnd_buffer_size：Global，Session用key进行order，并且进行(非连续)扫描表数据时使用4.请简洁地描述下MySQL中InnoDB支持的四种事务隔离级别名称，以及逐级之间区别？readuncommitted:未提交读。可以读取到其他线程修改(未提交)的数据readcommitted:提交读。只能读取到其他线程已经提交的数据。解决脏读，修改的数据可能最后未提交。只锁定索引，并且不锁定索引前的间隙repeatableread:可重复读。不能读取到其他线程提交的数据，间隙锁解决幻读。使用唯一索引进行等值查询，则只锁定索引，不锁定索取前间隙。其他查询，则不仅锁定索引，并且锁定索取范围包含的间隙serializable:串行化读。所有的访问都串行化。将select转为select...lockinsharemode即为查询语句涉及到的数据加上共享琐，阻塞其他事务修改真实数据。5.小题集锦1.VARCHAR(N)或CHAR(N)中的N含义是：N个字符。1个字符不同字符集下占用的字节数不一样2.若一张表中只有一个字段VARCHAR(N)类型，utf8编码，则N最大值为多少(精确到数量级即可)：N=FLOOR((65535-1-2)/3)3.表中有大字段X（例如：text类型），且字段X不会经常更新，以读为为主，请问您是选择拆成子表，还是继续放一起，并且写出您的理由？答案：拆为子表。理由：提高其他字段的查询(select/update)效率,因为每页保存的行数越多，效率越高。X字段更新效率低，单行的读取效率降低不大，但如果每次查询的行数越多，影响越大4.MySQL中InnoDB引擎的行锁是通过加在什么上完成（或称实现）的：A.数据块B.索引值选择答案后，告诉我们为什么？答：索引值索引值。innodb表数据是索引组织表形式存放但是对索引页加锁，采用位图方式实现锁:提供共享资源的并发访问，保证数据的完整性、一致性sqlserver:2005前，页级锁;之后乐观并发，悲观并发。乐观并发支持行级锁但和innodb实现方式不同，sqlserver下锁是稀有资源，某种情况下会升级为表锁innodb、oracle：提供一致性的非锁定读、行级锁(没有相关额外开销)通过索引查询时，主键锁的是key，辅助索引锁的是范围锁的两个概念:latch、locklatch:线程使用，轻量级锁，锁定内存数据结构锁定时间必须很短。可再分为mutex(互斥锁)、rwlock(读写锁)。目的是用来保证并发线程操作临界资源的正确性，并且没有死锁检测，有mysqlserver保证.Lock:事务使用，锁定的对象:表、页、行。commit或rollback后释放意向锁:表级别的锁，表示下一行被请求的锁类型locksrecbutnotgap:表示锁住的是索引，而不是范围。有死锁检测。5.username字段定义为VARCHAR(40)和VARCHAR(200)有啥区别？答：临时表varchar(200)占用空间更大，最好按实际需求分配6.MySQL数据库备份方式有那几种（只讨论InnoDB存储引擎），至少写四种。select...intooutfile;对应loaddatainfile恢复filedsterminatedby'x';每个列的分隔符。默认'\t'optionallyencolsedby'x';字符串的包含符。默认''escapedby'x':转义符，默认为'\\'startingby'x';每行的开始符。默认''terminatedby'x':每行结束符。默认'\n'mysqldump:对应mysql恢复single-transaction:备份开始先执行starttransaction但不能有DDL操作，否则无法保证一致性读master-data:如果没有指定single-transaction,则用lock-all-tables1:显示masterstatus，并且changemaster2:只显示change，但不执行mysqlimport:与loaddatainfile类似，但支持导入多个表，表之间并发导入二进制日志binlog备份通过mysqlbinlog命令从binlog提取sqlxtrabackup先记录当前redo位置然后拷贝共享表空间和独立表空间数据最后根据redo日志和开始位置，重做redo快照备份LVM6.MySQL复制搭建M-N的过程，请简述各个步骤？(备注：M已经在线跑，N为新安装的MySQL服务器)1在启动主从服务器时，必须用server_id启动选项给出其ID值。主从服务器的ID值不能相同。主服务器要启用二进制日志功能。在主、从服务器配置文件中：主：server-id=master_server_idLog-bin=binlog_name从：server-id=slave_server_id2.在主服务器上，创建一个账户供从服务器连接主服务器并请求修改信息:CREATEUSER‘slave_user’@’slave_host’IDENTIFIEDBY‘slave_pass’;CRANTREPLICATIONSLAVEON*.*TO'slave_user'@'slave_host';3.连接到主服务器并通过执行SHOWMASTERSTATUS语句确定当前的复制坐标。4.在从服务器上为将被复制的数据库建立一份完备的副本。5.连接到从服务器并使用CHANGEMASTER语句来配置它，包括把用来连接主服务器的参数和初始复制坐标告诉从服务器。CHANGEMASTERTOMASTER_HOST=’master_host’,MSATER_USER=’master_user’,MASTER_PASSWORD=’slave_pass