java面试题（二）------阿里mysql

1、MySQL的复制原理以及流程

(1)、先问基本原理流程，3个线程以及之间的关联；

答：

从 发起请求I/O thread线程请求 主

主 接收到请求使用binlog dump线程回应 从

从 I/O thread线程将请求接收下来保存为中继日志

从 再开SQL thread线程将中继线程保存为执行日志

附图：

参考：http://www.open-open.com/lib/view/open1373874692544.html

(2)、再问一致性延时性，数据恢复；

答：

出发点  是否使用工具   业务线是否正常    备份恢复成本   一致性
热备份          借助            细微影响                   偏高       组合才能完全
温备份          借助            受一部分                   中等          完全
冷备份           无              停滞                           较低          完全
    mysqldump
        innodb热备  结合binlog  启动大事务
        innodb温备   最好锁定表(若有事务则需要结合binlog)
    数据恢复:  通常情况下  每周完整  +  周中的增量or差异 + 新的binlog

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

[sql] view plain copy

1. 手抖将同步时间改太短,导致cpu空转  
2. 主主模式主键重复,调整auto_instcrment  

2、MySQL中myisam与innodb的区别，至少5点

(1)、问5点不同；

答：

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

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

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

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

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

6>.热备份
7>.崩溃恢复

其他：

(2)、问各种不同mysql版本的2者的改进；

(3)、2者的索引的实现方式。

3、问MySQL中varchar与char的区别以及varchar(50)中的30代表的涵义

(1)、varchar与char的区别；

答：char是一种固定长度的类型，范围是0~255，varchar则是一种可变长度的类型，范围是 0~65535，char长度不够会填充空格

(2)、varchar(50)中50的涵义；

答：最多存放50个字符

(3)、若一张表中只有一个字段VARCHAR(N)类型，utf8编码，则N最大值为多少(精确到数量级即可)：
答：由于utf8的每个字符最多占用3个字节。而MySQL定义行的长度不能超过65535，因此N的最大值计算方法为：(65535-1-2)/3。减去1的原因是实际存储从第二个字节开始，减去2的原因是因为要在列表长度存储实际的字符长度，除以3是因为utf8限制：每个字符最多占用3个字节。因此N=(Floor(65535-1-2)/3)

(4)、int(20)中20的涵义；

答：int(M) M指示最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关；所以int(10)与int(11)后的括号中的字符表示显示宽度，整数列的显示宽度与mysql需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，int类型的字段能存储的数据上限还是2147483647(有符号型)和4294967295(无符号型)。如果int(10)和int(11)不加zerofill,则它们没有什么区别．

(4)、为什么MySQL这样设计。

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

(1)、有多少种日志；

答：

错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。

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

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

中继日志：

事务日志：

(2)、日志的存放形式；

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

答：

隔离性： 通过 锁 实现

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

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

(1)、各种日志格式的涵义；

(2)、适用场景；

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

6、问了下MySQL数据库cpu飙升到500%的话他怎么处理？

(1)、没有经验的，可以不问；

(2)、有经验的，问他们的处理思路。

答：

列出所有进程  show processlist  观察所有进程  多秒没有状态变化的(干掉)
查看超时日志或者错误日志 (做了几年开发,一般会是查询以及大批量的插入会导致cpu与i/o上涨,,,,当然不排除网络状态突然断了,,导致一个请求服务器只接受到一半，比如where子句或分页子句没有发送,,当然的一次被坑经历)

7、sql优化

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

答：
分析当前的select语句，
    select_type  当前查询的类型(简单or连接or组合or子查询)
    rows  做笛卡尔积要组合的行
    extra  额外信息
    (其余的实在没记住)

    考虑将面试官带上其他话题  优化sql中   : )
    where  不必要的括号,常量重叠,去除常量条件
    范围优化  少用like啊  根据当前的索引选定不同的范围条件啊
    多关键字优化  is null, distinct, left/right join, join, union,order by,group by,limit
    小表不需要索引:  select * from table force index(index_name) where
    索引合并优化   select * from table ignore index(indexname1,indexname2)   一张表有多索引,忽略几个在查,可能提高性能

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

(3)、explain中的索引问题。

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

答：
mysqldump  做之前要日志滚动,记录同步位置,请求锁
xtranbackup  没有锁表,将二进制,事务日志都备份下来,之后必要要做准备,才能用于还原

(1)、备份计划；

(2)、备份恢复时间；

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

答：检查日志排除问题,如果不行删除当前所有数据文件(不包括二进制哦),利用之前完整备份 + 增量 +  二进制再次重试

9、500台db，在最快时间之内重启

答：shell脚本？

10、在当前的工作中，你碰到到的最大的MySQL DB问题是？

11、innodb的读写参数优化

(1)、读取参数，global buffer pool以及 local buffer；

(2)、写入参数；

(3)、与IO相关的参数；

innodb_read_io_threads  读io的线程数
innodb_io_capacity      io总量????
innodb_write_io_threads 写io的线程数
innodb_use_native_aio   实现aio就是纯异步

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

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

答：

SQL标准定义的四个隔离级别为：read uncommited，read committed，repeatable read，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)、写出您这样选择的理由。

答：拆带来的问题：连接消耗 + 存储拆分空间；不拆可能带来的问题：查询性能；
如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗
如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择

14、MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的？为什么是这样子的？

答：InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,
如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起

15、MySQL数据库备份方式有那几种（只讨论InnoDB存储引擎），至少写四种。

答：1>.热备份；2>.冷备份；3>.温备份；4>.二进制日志备份

16、MySQL复制搭建M->N的过程，请简述各个步骤？(备注：M已经在线跑，N为新安装的MySQL服务器)
1>.在启动主从服务器时，必须用server_id启动选项给出其ID值。主从服务器的ID值不能相同。主服务器启动二进制日志。
2>.在主服务器上，创建一个账户供从服务器连接主服务器并请求修改信息。
3>.连接到主服务器并通过执行showmaster status 语句确定当前的复制坐标。
4>.在从服务器上为将被复制的数据库建立一份完备的副本。
5>.连接到从服务器并使用changemaster 语句来配置它，包括把用来连接主服务器的参数和初始化复制坐标告诉从服务器。
6>.从服务器开始复制

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

原SQL语句：
SELECT * FROM test FORCE(idx_m_n) WHEREm=1 ORDER BY n LIMIT 1000,10;

优化后为：
索引要修改为: 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>.子查询中用到了查询覆盖索引

18、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;

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

原SQL语句：
SELECT * FROM test FORCE(idx_m_n) WHEREm=1 ORDER BY n LIMIT 1000,10;

优化后为：
索引要修改为: 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>.子查询中用到了查询覆盖索引

18、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;

19.[SELECT *] 和[SELECT 全部字段]的2种写法有何优缺点，至少写出四点
1>.前者要解析数据字典，后者不需要
2>.结果输出顺序，前者与建表列顺序相同，后者按指定字段顺序。
3>.表字段改名，前者不需要修改，后者需要改
4>.后者可以建立索引进行优化，前者无法优化
5>.后者的可读性比前者要高

20.  HAVNG 子句 和 WHERE的异同点，至少写出3点
1>.语法上：where 用表中列名，having用select结果别名
2>.影响结果范围：where从表读出数据的行数，having返回客户端的行数
3>.索引：where 可以使用索引，having不能使用索引，只能在临时结果集操作
4>.where后面不能使用聚集函数，having是专门使用聚集函数的。

21.  分布式数据库产品的特点（至少写4条）
1>.数据分布在多个异地点，抗灾性强
2>.并发性高
3>.受网络影响很大
4>.单机的性能不是特别重要，但是总体成本很高。
5>.扩展性强

22.  数据拆分架构的优缺点（至少写8条）
1>.透明性，程序不需要做任何修改
2>.解决集中数据库的扩展局限性。实现水平扩展问题，涉及到数据的拆分问题
3>.提高数据库服务的性能、可靠性、可用性
4>.实现技术不难，开发成本和维护成本可控
5>.测试成本高
6>.无法支持分布式事务
7>.数据拆分后数据合并难
8>.部分功能限制
9>.扩展受限

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

原SQL语句：
SELECT * FROM test FORCE(idx_m_n) WHEREm=1 ORDER BY n LIMIT 1000,10;

优化后为：
索引要修改为: 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>.子查询中用到了查询覆盖索引


18、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;

19.[SELECT *] 和[SELECT 全部字段]的2种写法有何优缺点，至少写出四点
1>.前者要解析数据字典，后者不需要
2>.结果输出顺序，前者与建表列顺序相同，后者按指定字段顺序。
3>.表字段改名，前者不需要修改，后者需要改
4>.后者可以建立索引进行优化，前者无法优化
5>.后者的可读性比前者要高

20.  HAVNG 子句 和 WHERE的异同点，至少写出3点
1>.语法上：where 用表中列名，having用select结果别名
2>.影响结果范围：where从表读出数据的行数，having返回客户端的行数
3>.索引：where 可以使用索引，having不能使用索引，只能在临时结果集操作
4>.where后面不能使用聚集函数，having是专门使用聚集函数的。

21.  分布式数据库产品的特点（至少写4条）
1>.数据分布在多个异地点，抗灾性强
2>.并发性高
3>.受网络影响很大
4>.单机的性能不是特别重要，但是总体成本很高。
5>.扩展性强

22.  数据拆分架构的优缺点（至少写8条）
1>.透明性，程序不需要做任何修改
2>.解决集中数据库的扩展局限性。实现水平扩展问题，涉及到数据的拆分问题
3>.提高数据库服务的性能、可靠性、可用性
4>.实现技术不难，开发成本和维护成本可控
5>.测试成本高
6>.无法支持分布式事务
7>.数据拆分后数据合并难
8>.部分功能限制
9>.扩展受限

第一轮：SQL tuning 类
1：列举几种表连接方式
答：merge join，hash join，nested loop
2：不借助第三方工具，怎样查看sql的执行计划？
答：sqlplus、set autotrace …、utlxplan.sql 创建 plan_table 表
3：如何使用CBO，CBO与RULE的区别？
答：在初始化参数里面设置 optimizer_mode=choose/all_rows/first_row 等可以使用 cbo。rbo会选择不合适的索引，cbo需要统计信息。
4：如何定位重要（消耗资源多）的SQL？
答：根据v$sqlarea 中的逻辑读/disk_read。以及寻找CPU使用过量的session，查出当前session的当前SQL语句，或者：监控WIN平台Oracle的运行
5：如何跟踪某个session的SQL？
答：先找出对应的’sid,serial’，然后调用system_system.set_sql_trace_in_session(sid,serial,true)；参考：跟踪某个会话
6：SQL调整最关注的是什么？
答：逻辑读。IO量
7：说说你对索引的认识（索引的结构、对dml影响、对查询影响、为什么提高查询性能）
答：默认的索引是b-tree。对insert的影响：分裂，要保证tree的平衡。对delete的影响：删除行的时候要标记改节点为删除。对update的影响：如果更新表中的索引字段，则要相应的更新索引中的键值。查询中包含索引字段的键值和行的物理地址。
8：使用索引查询一定能提高查询的性能吗？为什么？
答：不能。如果返回的行数目较大，使用全表扫描的性能较好。
9：Array：绑定变量是什么？绑定变量有什么优缺点？
答：通俗的说，绑定变量就是变量的一个占位符，使用绑定变量可以减少只有变量值不同的语句的解析。
10：如何稳定(固定)执行计划？
答：使用stored outline。
11：和排序相关的内存在8i和Arrayi分别怎样调整，临时表空间的作用是什么？
答：8i：使用sort_area_size，hash_area_size，每个session分配相同的值，不管有无使用。Arrayi：使用 pga_aggregate来统一管理。临时表空间的作用：在sort_area_size中不能完成的部分在临时表空间完成，临时表空间在重建索引，创建临时表等都要用到。还有hash join不能完成的也在临时表空间中做。
12：存在表T(a,b,c,d)，要根据字段c排序后取第21-30条记录显示，请给出sql
select a,b,c,d from (select a,b,c,d from T order by c) where rownum< li>
minus
select a,b,c,d from (select a,b,c,d from T order by c) where rownum < li>
或者：
select * from (select rownum rn,a.* from (select a,b,c,d from T order by c) a )where rn between 21 and 30;


第二轮：数据库基本概念类
1：pctused and pctfree 表示什么含义有什么作用？
答：表示数据块什么时候移入和移出freelist。pctused：如果数据块的使用率小于pctused的值，则该数据块重新加入到fresslist中。ctfree：如果数据块的使用率高于pctfree的值，则该数据块从freelist中移出。
2：简单描述table / segment / extent / block之间的关系
答：一个table至少是一个segment，如果分区表，则每个分区是一个segment，table可以看成是一个逻辑上的概 念，segment可以看成是这个逻辑概念的物理实现；segment由一个或多个extents组成，segment不可以跨表空间但可以跨数据文件；extent由多个连续的blocks组成，不可以跨数据文件；block由1-多个os块组成，是oracle i/o的最小存储单位。
3：描述tablespace和datafile之间的关系
答：tablespace是逻辑上的概念，datafile是物理上的概念。一个tablespace可以由多个datafile组成，一个datafile不能跨越多个tablespace。
4：本地管理表空间和字典管理表空间的特点，ASSM有什么特点？
答：一个使用freelist管理，一个使用位图管理。
5：回滚段的作用是什么？
答：保存数据的前像，保证数据读取的时间点一致性。Oracle里数据的多版本特性就是通过回滚段来实现的，正因为此，Oracle数据库实现了读写不竞争的性能优势！
6：日志的作用是什么？
答：记录对数据库的操作，便于恢复。
7：SGA主要有那些部分，主要作用是什么？
答：db_cache(缓存数据块)，shared_pool(缓存sql，执行计划，数据字典信息 等)，large_pool(MTS模式、parallel 、rman等要用到)，java pool(java程序如SQLJ存储过程运行时要用到)。
8：Oracle系统进程主要有哪些，作用是什么？
答：smon（合并空间，实例恢复），pmon（清理失败的进程），归档进程（负责在日志切换的时候归 档日志文件），lgmr（日志书写器进程，负责写日志），ckpt（检查点进程，触发检查点），dbwr（数据库写入器，负责把数据写入导 datafile）。


第三轮：备份恢复类
1：备份如何分类？
答：逻辑备份(exp)与物理备份。或者冷备份与热备份。
2：归档是什么含义？
答：把日志文件放到另一个地方。
3：如果一个表在2004-08-04 10:30:00 被drop，在有完善的归档和备份的情况下，如何恢复？
答：拷贝备份，recover database until time 2004-08-04 10:30:00 alter database open resetlogs;
4：rman是什么，有何特点？
答：rman叫恢复管理器。特点很多。可以在线备份，到少目标数据库是mount状态。1）热备份。2）可以存储脚本。3）可以增量备份。4）自动管理备份集
5：standby的特点
答：利用传输重做日志来达到同步的目的。可以设定多个保护级别，Arrayi后支持逻辑standbyDB。
6：对于一个要求恢复时间比较短的系统(数据库50G，每天归档5G)，你如何设计备份策略
答：每天一个全备份。


第四轮：系统管理类
1：对于一个存在系统性能的系统，说出你的诊断处理思路
答：做一个statspack，根据top 5，system load，top sql等来做相应的调整。
2：列举几种诊断IO、CPU、性能状况的方法
答：hp-unix:iostat -x 1 5；top/vmstat/glance
3：对statspack有何认识？
答：一个性能诊断工具而已，其本质就是在两个时间点采样两个系统数据。(动态性能视图)，然后根据两个snapshot，产生一个报告。
4：如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响
答：1）增大sort_area_size（8i）/pga_aggregate_target（Arrayi）值。2）如果表有分区（一般大表都要用到分区的），按分区逐个建索引，如果是本地索引的话。3）系统空闲的时候建。
5：对raid10 和raid5有何认识？
答：raid10是先镜像后条带，适合对写入速度要求较高的数据库系统，特别是online redolog文件，raid5适合大部分的数据库系统和数据仓库系统，读性能优于写性能。