面试过程中常遇到的Mysql优化方面的面试题

面试过程中常遇到的面试过程中常遇到的 MysqlMysql 优化方面的面试题优化方面的面试题Part2：经典题目1、MySQL 的复制原理以及流程基本原理流程，3 个线程以及之间的关联；2、MySQL 中 myisam 与 innodb 的区别，至少 5 点(1)、问 5 点不同；(2)、innodb 引擎的 4 大特性(3)、2 者 selectcount(*)哪个更快，为什么3、MySQL 中 varchar 与 char 的区别以及 varchar(50)中的 50 代表的涵义(1)、varchar 与 char 的区别(2)、varchar(50)中 50 的涵义(3)、int（20）中 20 的涵义(4)、mysql 为什么这么设计4、问了 innodb 的事务与日志的实现方式(1)、有多少种日志；(2)、事物的 4 种隔离级别(3)、事务是如何通过日志来实现的，说得越深入越好。5、问了 MySQL binlog 的几种日志录入格式以及区别(1)、binlog 的日志格式的种类和分别(2)、适用场景；(3)、结合第一个问题，每一种日志格式在复制中的优劣。6、问了下 MySQL 数据库 cpu 飙升到 500%的话他怎么处理？(1)、没有经验的，可以不问；(2)、有经验的，问他们的处理思路。7、sql 优化(1)、explain 出来的各种 item 的意义；(2)、profile 的意义以及使用场景；8、备份计划，mysqldump 以及 xtranbackup 的实现原理(1)、备份计划；(2)、备份恢复时间；(3)、xtrabackup 实现原理9、mysqldump 中备份出来的 sql，如果我想 sql 文件中，一行只有一个insertvalue()的话，怎么办？如果备份需要带上 master 的复制点信息怎么办？10、500 台 db，在最快时间之内重启.11、innodb 的读写参数优化(1)、读取参数(2)、写入参数；(3)、与 IO 相关的参数；(4)、缓存参数以及缓存的适用场景。12、你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？.13、你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？14、你们数据库是否支持 emoji 表情，如果不支持，如何操作？.15、你是如何维护数据库的数据字典的16、你们是否有开发规范，如果有，如何执行的17、表中有大字段 X(例如：text 类型)，且字段 X 不会经常更新，以读为为主，请问(1)、您是选择拆成子表，还是继续放一起；(2)、写出您这样选择的理由。18、MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的？为什么是这样子的？易火棋牌 http:/www.482223.com.19、如何从 mysqldump 产生的全库备份中只恢复某一个库、某一张表？开放性问题：据说是腾讯的一个 6 亿的表 a，一个 3 亿的表 b，通过外间 tid 关联，你如何最快的查询出满足条件的第 50000 到第 50200 中的这 200 条数据记录。Part4:答案1、MySQL 的复制原理以及流程基本原理流程，3 个线程以及之间的关联；1. 主：binlog 线程记录下所有改变了数据库数据的语句，放进 master 上的binlog 中；2. 从：io 线程在使用 start slave 之后，负责从 master 上拉取 binlog 内容，放进 自己的 relay log 中；3. 从：sql 执行线程执行 relay log 中的语句；2、MySQL 中 myisam 与 innodb 的区别，至少 5 点(1)、问 5 点不同；1>.InnoDB 支持事物，而 MyISAM 不支持事物2>.InnoDB 支持行级锁，而 MyISAM 支持表级锁3>.InnoDB 支持 MVCC, 而 MyISAM 不支持4>.InnoDB 支持外键，而 MyISAM 不支持5>.InnoDB 不支持全文索引，而 MyISAM 支持。(2)、innodb 引擎的 4 大特性插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)(3)、2 者 selectcount(*)哪个更快，为什么myisam 更快，因为 myisam 内部维护了一个计数器，可以直接调取。3、MySQL 中 varchar 与 char 的区别以及 varchar(50)中的 50 代表的涵义(1)、varchar 与 char 的区别char 是一种固定长度的类型，varchar 则是一种可变长度的类型(2)、varchar(50)中 50 的涵义最多存放 50 个字符，varchar(50)和(200)存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 order by col 采用 fixed_length 计算 col 长度(memory 引擎也一样)(3)、int（20）中 20 的涵义是指显示字符的长度但要加参数的，最大为 255，比如它是记录行数的 id,插入 10 笔资料，它就显示00000000001 00000000010，当字符的位数超过 11,它也只显示 11 位，如果你没有加那个让它未满 11 位就前面加 0 的参数，它不会在前面加 020 表示最大显示宽度为 20，但仍占 4 字节存储，存储范围不变；(4)、mysql 为什么这么设计对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和 int(20)存储和计算均一样；4、问了 innodb 的事务与日志的实现方式(1)、有多少种日志；错误日志：记录出错信息，也记录一些警告信息或者正确的信息。查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。慢查询日志：设置一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询的日志文件中。二进制日志：记录对数据库执行更改的所有操作。中继日志：事务日志：(2)、事物的 4 种隔离级别隔离级别读未提交(RU)读已提交(RC)可重复读(RR)串行(3)、事务是如何通过日志来实现的，说得越深入越好。事务日志是通过 redo 和 innodb 的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的 lsn(log sequence number)号; 当事务执行时，会往 InnoDB 存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过 innodb_flush_log_at_trx_commit 来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”5、问了 MySQL binlog 的几种日志录入格式以及区别(1)、binlog 的日志格式的种类和分别(2)、适用场景；(3)、结合第一个问题，每一种日志格式在复制中的优劣。Statement：每一条会修改数据的 sql 都会记录在 binlog 中。优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，提高性能。(相比 row 能节约多少性能 与日志量，这个取决于应用的 SQL 情况，正常同一条记录修改或者插入 row 格式所产生的日志量还小于 Statement 产生的日志量，但是考虑到如果带条 件的update 操作，以及整表删除，alter 表等操作，ROW 格式会产生大量日志，因此在考虑是否使用 ROW 格式日志时应该跟据应用的实际情况，其所 产生的日志量会增加多少，以及带来的 IO 性能问题。)缺点：由于记录的只是执行语句，为了这些语句能在 slave 上正确运行，因此还必须记录每条语句在执行的时候的 一些相关信息，以保证所有语句能在 slave 得到和在master 端执行时候相同 的结果。另外 mysql 的复制,像一些特定函数功能，slave 可与master 上要保持一致会有很多相关问题(如 sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).使用以下函数的语句也无法被复制：* LOAD_FILE()* UUID()* USER()* FOUND_ROWS()* SYSDATE() (除非启动时启用了 -sysdate-is-now 选项)同时在 INSERT .SELECT 会产生比 RBR 更多的行级锁2.Row:不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。优点： binlog 中可以不记录执行的 sql 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下 每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或 function，以及 trigger 的调用和触发无法被正确复制的问题。638 棋牌 http:/www.rodlg.com缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比 如一条 update 语句，修改多条记录，则 binlog 中每一条修改都会有记录，这样造成 binlog 日志量会很大，特别是当执行 alter table 之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。3.Mixedlevel: 是以上两种 level 的混合使用，一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则 采用 row 格式保存binlog,MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式，也就是在Statement 和 Row 之间选择 一种.新版本的 MySQL 中队 row level 模式也被做了优化，并不是所有的修改都会以 row level 来记录，像遇到表结构变更的时候就会以 statement 模式来记录。至于 update 或者 delete 等修改数据的语句，还是会记录所有行的 变更。6、问了下 MySQL 数据库 cpu 飙升到 500%的话他怎么处理？(1)、没有经验的，可以不问；(2)、有经验的，问他们的处理思路。列出所有进程 show processlist 观察所有进程多秒没有状态变化的(干掉)查看超时日志或者错误日志 (做了几年开发,一般会是查询以及大批量的插入会导致cpu 与 i/o 上涨,当然不排除网络状态突然断了,导致一个请求服务器只接受到一半，比如 where 子句或分页子句没有发送,当然的一次被坑经历)7、sql 优化(1)、explain 出来的各种 item 的意义；select_type表示查询中每个 select 子句的类型type表示 MySQL 在表中找到所需行的方式，又称“访问类型”possible_keys指出 MySQL 能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用key显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULLkey_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度ref表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值Extra包含不适合在其他列中显示但十分重要的额外信息(2)、profile 的意义以及使用场景；查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等8、备份计划，mysqldump 以及 xtranbackup 的实现原理(1)、备份计划；这里每个公司都不一样，您别说那种 1 小时 1 全备什么的就行(2)、备份恢复时间；这里跟机器，尤其是硬盘的速率有关