MYSQL DBA-OPS

[ACID]
A:atomicity
C:consistency
I:isolation
D:durability

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存储引擎的区别，至少写四点.

 

http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html

http://dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html

http://blog.sina.com.cn/s/blog_98cf2a6f01015md5.html

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后支持


[color=red]在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， Session
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
[myisam]
key_buffer_size：Global
影响索引更新速度，越大越快？
read_buffer_size：Global，Session
对表做连续表数据扫描时使用
read_rnd_buffer_size：Global， Session
用key进行order，并且进行(非连续)扫描表数据时使用

 

4. 请简洁地描述下MySQL中InnoDB支持的四种事务隔离级别名称，以及逐级之间区别？

read uncommitted:未提交读。可以读取到其他线程修改(未提交)的数据
read committed:提交读。只能读取到其他线程已经提交的数据
                         解决脏读，修改的数据可能最后未提交
                        只锁定索引，并且不锁定索引前的间隙
repeatable read:可重复读。不能读取到其他线程提交的数据
                         间隙锁解决幻读
                         使用唯一索引进行等值查询，则只锁定索引，不锁定索取前间隙
                         其他查询，则不仅锁定索引，并且锁定索取范围包含的间隙
                            
serializable:串行化读。所有的访问都串行化
           将select转为select ... lock in share mode

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表数据是索引组织表形式存放
  但是对索引页加锁，采用位图方式实现([color=red]如何实现[/color])

  锁:提供共享资源的并发访问，保证数据的完整性、一致性

sqlserver:2005前，页级锁;之后乐观并发，悲观并发。乐观并发支持行级锁
但和innodb实现方式不同，sql server下锁是稀有资源，某种情况下会升级为表锁

innodb、oracle：提供一致性的非锁定读、行级锁(没有相关额外开销)
  通过索引查询时，主键锁的是key，辅助索引锁的是范围

   锁的两个概念:latch、lock
   latch:线程使用，轻量级锁，锁定内存数据结构
        锁定时间必须很短。可再分为mutex(互斥锁)、rwlock(读写锁)。
       目的是用来保证并发线程操作临界资源的正确性，
        并且没有死锁检测，有mysql server保证
   lock:事务使用，锁定的对象:表、页、行。commit或rollback后释放

   意向锁:表级别的锁，表示下一行被请求的锁类型

   locks rec but not gap:
         表示锁住的是索引，而不是范围。
       有死锁检测
        有死锁检测

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

       答：临时表varchar(200)占用空间更大，最好按实际需求分配

5>.MySQL数据库备份方式有那几种（只讨论InnoDB存储引擎），至少写四种。
关服务，直接拷贝ibd、frm、redolog、my.cnf

select ... into outfile;对应load data infile 恢复
    fileds terminated by 'x';每个列的分隔符。默认'\t'
    optionally encolsed by 'x';字符串的包含符。默认''
    escaped by 'x':转义符，默认为'\\'
    starting by 'x';每行的开始符。默认''
    terminated by 'x':每行结束符。默认'\n'

mysqldump:对应mysql恢复
   single-transaction:备份开始先执行start transaction
      但不能有DDL操作，否则无法保证一致性读
   master-data:如果没有指定single-transaction,则用lock-all-tables
       1:显示master status，并且change master
       2:只显示change，但不执行

mysqlimport:与load data infile类似，但支持导入多个表，表之间并发导入

二进制日志binlog备份
    通过mysqlbinlog命令从binlog提取sql

xtrabackup
    先记录当前redo位置
    然后拷贝共享表空间和独立表空间数据
    最后根据redo日志和开始位置，重做redo

快照备份LVM
    [color=red]实现方式[/color]
    http://www.cnblogs.com/gaojun/archive/2012/08/22/2650229.html

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

 [color=red]不完整，待补充[/color]
1> .主库授权:grant replication slave on *.* to 'xx'@'xxx' identified by 'xx';
2>.配置N的my.cnf:
  server_id
3>.mysqldump -uxx -pxx -AER --single-transaction --master-data > mas.sql
4>.slave导入mas.sql，如果没有master-data，则需要手动change master
5>.开启start slave;
6>.检查同步状态show slave status;

7. 看图分析（申明：应用程序未有任何版本变更）


1>.图出现什么样的现象，及现象之间的关联性？


2>.通过图信息分析得出可能什么原因造成的？


3>.分析除原因后，告知如何解决？


4>.请简述你是如何思考分析的？
答：


8. SQL语句优化
原SQL语句：
SELECT ID,WAYBILL_NO,EXP_TYPE,PKG_QTY,EXPRESS_CONTENT_CODE,EFFECTIVE_TYPE_CODE
FROM T_EXP_OP WHERE ORDERID NOT IN(SELECT ORDERID 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关联）：

9. 分页SQL语句优化
原SQL语句：
SELECT * FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10;

条件：
Test表为InnoDB存储引擎，主键为BIGINT类型的ID字段，二级索引：idx_m_n(m,n)
优化后为：


请简述优化的理由：

索引改为: idx_mn(m,n,ID);
SELECT ID FROM test FORCE(idx_m_n) WHERE m=1 ORDER BY n LIMIT 1000,10; 
SELECT * FROM test WHERE ID IN (ids);

10. 语句挑错
SQL语句：
SELECT M.columnname……,N.* columnname…..
FROM left_table M  LEFT JOIN right_table N
ON M. columnname_join=N. columnname_join  AND N. columnname=XXX AND M.columnname=XXX

请问本SQL语句哪里不合理，为啥不合理？

空格符不明确，忽略

11. [SELECT *] 和[SELECT 全部字段]的2种写法有何优缺点，至少写出四点

a:代表 select *
b:代表 select 全部字段
1>.a需要解析数据字典，b不需要
2>.输出顺序:a与建表列顺序相同，b按指定字段顺序
   如果字段调整顺序，a受影响，b不受影响
3>.表新增字段，浪费网络流量:a每次都返回所有字段，b只返回指定字段
4>.表字段改名:a不用改，b需要改
5>.b比a的可读性高
6>.b可以建索引优化，a无法优化

12. HAVNG 子句 和 WHERE的异同点，至少写出3点

1>.语法:where用表中列名，having用select结果别名
2>.影响结果范围: where 从表读出数据的行数，having返回客户端的行数
3>.索引:where可以使用索引，having不能使用索引，只能在临时结果集操作
4>.

13. 分布式数据库产品的特点（至少写4条）

1>.冗余，不存在单点故障，可靠性高
2>.数据分布在多个异地机房，容灾性好
3>.扩展简单
4>.不要求单机性能,但总体成本/管理成本都比较高
5>.受网络影响较大
产品:
http://www.mysqlops.com/2012/04/05/mysql-cluster-%E4%B8%8E-mongodb-%E5%A4%8D%E5%88%B6%E9%9B%86%E5%88%86%E7%89%87%E8%AE%BE%E8%AE%A1%E5%8F%8A%E5%8E%9F%E7%90%86.html
MySQL Cluster NDB
HBase
Oceanbase

14. 数据拆分架构 的优缺点（至少写8条）

1>.
2>.
3>.
4>.
5>.
6>.
7>.
8>.
9>.
 

  
15. 工作是否还有MySQL疑难至今未解决（备注：有，请简洁描述；若无，请写无）？

答：
连接握手未完成，但mysql的show processlist特别少
   改为nignx后正常，不明原因

where a between 范围 group by b
   如何提高效率

  
  
  
  
16. 你对自己的未来3-5年的规划，以及近期1-2年的规划
答：
       1>.近期3-5年的规划



       2>.近期1-2年的规划
      
      
      


邮件地址：[e-mail]
固定电话：[phone]
移动电话：136 6166 8096




追加
安装注意：
    开启performance_schema功能:-DWITH_PERFSCHEMA_STORAGE_ENGINE=1