常见Sql⾯试题及答案
答案是⾃⼰理解整理的。。。。。。。。。。。。
1、MySQL的复制原理以及流程
复制有三个步骤：
1、Master将数据改变记录到⼆进制⽇志(binary log)中，也就是配置⽂件log-bin指定的⽂件，这些记录叫做⼆进制⽇志事件(binary log events)
2、Slave通过I/O线程读取Master中的binary log events并写⼊到它的中继⽇志(relay log)
3、Slave重做中继⽇志中的事件，把中继⽇志中的事件信息⼀条⼀条的在本地执⾏⼀次，完成数据在本地的存储，从⽽实现将改变反映到它⾃⼰的数据(数据重放)
2、MySQL中myisam与innodb的区别，⾄少5点
(1)、问5点不同；
1、MyISAM不⽀持事务，InnoDB是事务类型的存储引擎，当我们的表需要⽤到事务⽀持的时候，那肯
定是不能选择MyISAM了。
2、MyISAM只⽀持表级锁，BDB⽀持页级锁和表级锁默认为页级锁，⽽InnoDB⽀持⾏级锁和表级锁默认为⾏级锁
3、MyISAM引擎不⽀持外键，InnoDB⽀持外键
4、MyISAM引擎的表在⼤量⾼并发的读写下会经常出现表损坏的情况
5、对于count()查询来说MyISAM更有优势
6、 InnoDB是为处理巨⼤数据量时的最⼤性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
7、MyISAM⽀持全⽂索引（FULLTEXT），InnoDB不⽀持
oncontextmenu标签8、MyISAM引擎的表的查询、更新、插⼊的效率要⽐InnoDB⾼
(2)、innodb引擎的关键特性
⼀、插⼊缓冲：　对于⾮聚集索引的插⼊和更新，不是每⼀次直接插⼊索引页中，⽽是⾸先判断插⼊
的⾮聚集索引页是否在缓冲池中，如果在，则直接插⼊，否则，先放⼊⼀个插⼊缓冲区中。好似欺骗数据库这个⾮聚集的索引已经插⼊到叶⼦节点了，然后再以⼀定的频率执⾏插⼊缓冲和⾮聚集索引页⼦节点的合并操作，这时通常能将多个插⼊合并到⼀个操作中，这就⼤⼤提⾼了对⾮聚集索引执⾏插⼊和修改操作的性能。（插⼊缓冲使⽤的条件：1、索引是辅助索引； 2、索引不是唯⼀的；）
⼆、⼆次写：主要⽤于数据恢复。Double write由两部分组成，⼀部分是内存中的 double write buffer。 另⼀部分是在物理磁盘上的共享表空间中的连续128个页，⼤⼩和内存中（2M）⼀致。对缓冲池中的页进⾏刷新时，并不直接写磁盘，⽽是memcpy到double write buffer. 之后通过 double write buffer 分两次，每次顺序写⼊共享表空间1M数据，然后马上调⽤fsync同步磁盘。这个写⼊因为共享表空间的double write页是连续的，因此开销不是很⼤。⽽完成 double write 页的写⼊后，再将double write buffer中的页写⼊各个表空间则是离散的写⼊。
如果操作系统在将页写⼊磁盘的过程中发⽣了崩溃。那么恢复时则可以从共享表空间中double write buffer页到该页的副本。将其复制到表空间再应⽤重做⽇志。
三、⾃适应哈希：
InnoDB存储引擎会监控对表上索引的查，如果观察到建⽴哈希索引可以带来速度的提升，则建⽴哈希索引，所以称之为⾃适应（adaptive）的。
⾃适应哈希索引通过缓冲池的B+树构造⽽来，因此建⽴的速度很快。⽽且不需要将整个表都建哈希索引，InnoDB存储引擎会⾃动根据访问的频率
限制
1.只能⽤于等值⽐较，例如=， <=>，in
2.⽆法⽤于排序c语言换成汇编语言
3.有冲突可能
4.Mysql⾃动管理，⼈为⽆法⼲预。
四、异步IO
优势：
1. IO 并⾏：IO 操作可以并⾏发出，不必等待前⾯的IO完成
2. IO Merge操作：将多个IO合并成⼀个IO，提⾼IOPS的性能
查询（space,page_no）:(8,6)+(8,7)+(8,8)，每个页16k
原先需要三个IO操作完成，Innodb会判断这三个页是连续的，因此只发送⼀个查询IO查询48k的页
Native AIO只⽀持Window和Linux，Mac OSX不⽀持
在Innodb存储引擎中，read ahead⽅式的读取、脏页的刷新、磁盘的写⼊都是有AIO⽅式完成
五、刷新临近页
当刷新⼀个脏页的时候，Innodb存储引擎会检测该页所在区的所有页，如果都是脏页，使⽤AIO的IO Merge，⼀起刷新⾄磁盘
注意问题：
1. ⼀个不怎么脏的页，相对与⼀个被修改多次很脏的页，刷新是否值得。
2. 固态硬盘有着较⾼的IOPS，建议不开启。（IOPS (Input/Output Operations Per Second)，即每秒进⾏读写（I/O）操作的次数，多⽤于数据库等场合，衡量随机访问的性能）
(3)、2者selectcount(*)哪个更快，为什么
myisam更快，因为它已经存了这个count，不⽤像innodb那样去真正查询⾏数
3、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义
(1)、varchar与char的区别
varchar可变长度，char定长
(2)、varchar(50)中50的涵义
最⼤可存⼊50个字节。
(3)、int（20）中20的涵义
最⼤可存⼊20位数。
是指显⽰字符的长度
不影响内部存储，只是影响带 zerofill 定义的 int 时，前⾯补多少个 0，易于报表展⽰
(4)、mysql为什么这么设计
对⼤多数应⽤没有意义，只是规定⼀些⼯具⽤来显⽰字符的个数；int(1)和int(20)存储和计算均⼀样；
4、问了innodb的事务与⽇志的实现⽅式
(1)、有多少种⽇志；
重做⽇志redo和回滚⽇志undo
(2)、事务的4种隔离级别
未提交读，已提交读，可重复读，序列化读
(3)、事务是如何通过⽇志来实现的，说得越深⼊越好。
⼀般情况下，mysql在崩溃之后，重启服务，innodb通过回滚⽇志undo将所有已完成并写⼊磁盘的未完成事务进⾏rollback，然后redo中
的事务全部重新执⾏⼀遍即可恢复数据，但是随着redo的量增加，每次从redo的第⼀条开始恢复就会浪费长的时间，所以引⼊了
checkpoint机制。Checkpoint：如果在某个时间点，脏页的数据被刷新到了磁盘，系统就把这个刷新的时间点记录到redo log的结尾位
置，在进⾏恢复数据的时候，checkpoint时间点之前的数据就不需要进⾏恢复了，可以缩短时间v
5、问了MySQL binlog的⼏种⽇志录⼊格式以及区别
(1)、binlog的⽇志格式的种类和分别
excel hlookup函数使用方法1.Statement：每⼀条会修改数据的sql都会记录在binlog中。
2.Row：不记录sql语句上下⽂相关信息，仅保存哪条记录被修改。
3.Mixedlevel: 是以上两种level的混合使⽤，⼀般的语句修改使⽤statment格式保存binlog，如⼀些函数，statement⽆法完成主从复制的操作，则采⽤row格式保存bin (2)、适⽤场景；
在⼀条 SQL 操作了多⾏数据时， statement 更节省空间， row 更占⽤空间。但是 row模式更可靠。
(3)、结合第⼀个问题，每⼀种⽇志格式在复制中的优劣。
Statement 可能占⽤空间会相对⼩⼀些，传送到 slave 的时间可能也短，但是没有 row模式的可靠。 Row 模式在操作多⾏数据时更占⽤空间，但是可靠。
字符串型常量6、问了下MySQL数据库cpu飙升到500%的话他怎么处理？
当 cpu 飙升到 500%时，先⽤操作系统命令 top 命令观察是不是 mysqld 占⽤导致的，如果不是，出占⽤⾼的进程，并进⾏相关处理。如果是 mysqld 造成的， show 看看执⾏计划是否准确， index 是否缺失，或者实在是数据量太⼤造成。⼀般来说，肯定要 kill 掉这些线程(同时观察 cpu 使⽤率是否下降)，等进⾏相应的调整(⽐如说有⼤量的 session 连进来导致 cpu 飙升，这种情况就需要跟应⽤⼀起来分析为何连接数会激增，再做出相应的调整，⽐如说限制连接数等。
7、sql优化
(1)、explain出来的各种item的意义；
(2)、profile的意义以及使⽤场景；
8、备份计划，mysqldump以及xtranbackup的实现原理
(1)、备份计划；
视库的⼤⼩来定，⼀般来说 100G 内的库，可以考虑使⽤ mysqldump 来做，因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进⾏都进⾏全量备份出来的⽂件⽐较⼩，压缩之后更⼩)。100G 以上的库，可以考虑⽤ xtranbackup 来做，备份速度明显要⽐ mysqldump 要快。⼀般是选择⼀周⼀个全备，其余每天进⾏
(2)、备份恢复时间；
物理备份恢复快，逻辑备份恢复慢
这⾥跟机器，尤其是硬盘的速率有关系，以下列举⼏个仅供参考
20G的2分钟（mysqldump）
80G的30分钟(mysqldump)
111G的30分钟（mysqldump)
288G的3⼩时（xtra)
3T的4⼩时（xtra)
逻辑导⼊时间⼀般是备份时间的5倍以上
(3)、xtrabackup实现原理
mysqldump
mysqldump 属于逻辑备份。加⼊--single-transaction 选项可以进⾏⼀致性备份。后台进程会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION IS 之后显式开启⼀个事务(START TRANSACTION /*!40100 WITH CONSISTENTSNAPSHOT */)，这样就保证了该事务⾥读到的数据都是事务事务时候的快照。之后再把(FLUSH TABLES WITH READ LOCK)，等开启事务后，再记录下数据库此时 binlog 的位置(showmaster status)，马上解锁，再读取表的数据。等所有的数据都已经导
Xtrabackup:
xtrabackup 属于物理备份，直接拷贝表空间⽂件，同时不断扫描产⽣的 redo ⽇志并保存下来。最后完成 innodb 的备份后，会做⼀个 flush engine logs 的操作(⽼版本概念，因为 xtrabackup 并不拷贝 binlog，所以必须保证所有的 redo log 都落盘，否则可能会丢最后⼀组提交事务的数据)。这个时间点就是 innodb 完成备份的时间点情)。然后还需要 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，备份完后解锁。这样就做到了完美的热备。
9、mysqldump中备份出来的sql，如果我想sql⽂件中，⼀⾏只有⼀个value()的话，怎么办？如果备份需要带上master的复制点
信息怎么办？
10、500台db，在最快时间之内重启
可以使⽤批量 ssh ⼯具 pssh 来对需要重启的机器执⾏重启命令。也可以使⽤ salt（前提是客户端有安装 salt）或者 ansible（ ansible 只需要 ssh 免登通了就⾏）等多
14、你们数据库是否⽀持emoji表情，如果不⽀持，如何操作？
mysql面试题基础知识mysql数据库的默认字符集utf8，只能存储3个字节的数据。标准的emoji表情是4个字节，在APP端输⼊保存表情是⽤户的普遍需求和⾏
为。
解决⽅式：更换字符集utf8-->utf8mb4      mb4的意思是most bytes 4，专门为兼容四个字节的。utf8mb4是向下兼容utf8的，所以即
便修改了字段的字符集也不会影响线上数据。⽤java查询的时候如果有必要，也要设置下字符集=
17、表中有⼤字段X(例如：text类型)，且字段X不会经常更新，以读为为主，请问
答：拆带来的问题：连接消耗 + 存储拆分空间；不拆可能带来的问题：查询性能；
如果能容忍拆分带来的空间问题，拆的话最好和经常要查询的表的主键在物理结构上放置在⼀起(分区)
顺序IO，减少连接消耗，最后这是⼀个⽂本列再加上⼀个全⽂索引来如果能容忍不拆分带来的查询性能损失的话：上⾯的⽅案在某个极致条件下肯定会出现问题，那么不拆就是最好的选择
风湿关节炎
18、MySQL中InnoDB引擎的⾏锁是通过加在什么上完成(或称实现)的？为什么是这样⼦的？
答：InnoDB是基于索引来完成⾏锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成⾏锁锁定，并且 id 是有索引键的列,
如果 id 不是索引键那么InnoDB将完成表锁,，并发将⽆从谈起

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。