Linux运维必会的100道MySql面试题之（一）
Linux运维必会的100道MySql面试题之（二）
Linux运维必会的100道MySql面试题之（三）
Linux运维必会的100道MySql面试题之（四）
1、MySQL的复制原理以及流程
基本原理流程，3个线程以及之间的关联；
- 主：binlog线程——记录下所有扭转了数据库数据的语句，放进master上的binlog中；
- 从：io线程——在应用start slave 之后，负责从master上拉取 binlog 内容，放进 本人的relay log中；
- 从：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的参数，它不会在后面加0
20示意最大显示宽度为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的几种日志录入格局以及区别
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的调用和触发无奈被正确复制的问题
毛病:所有的执行的语句当记录到日志中的时候，都将以每行记录的批改来记录，这样可能会产生大量的日志内容,比 如一条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、列出所有过程 show processlist,察看所有过程 ,多秒没有状态变动的(干掉)
2、查看超时日志或者谬误日志 (做了几年开发,个别会是查问以及大批量的插入会导致cpu与i/o上涨,当然不排除网络状态忽然断了,,导致一个申请服务器只承受到一半，比方where子句或分页子句没有发送,,当然的一次被坑经验)
7、sql优化各种办法
(1)、explain进去的各种item的意义；
select_type
示意查问中每个select子句的类型
type
示意MySQL在表中找到所需行的形式，又称“拜访类型”
possible_keys
指出MySQL能应用哪个索引在表中找到行，查问波及到的字段上若存在索引，则该索引将被列出，但不肯定被查问应用
key
显示MySQL在查问中理论应用的索引，若没有应用索引，显示为NULL
key_len
示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度
ref
示意上述表的连贯匹配条件，即哪些列或常量被用于查找索引列上的值
Extra
蕴含不适宜在其余列中显示但非常重要的额定信息
(2)、profile的意义以及应用场景；
查问到 SQL 会执行多少工夫, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少工夫等等
8、备份打算，mysqldump以及xtranbackup的实现原理
(1)、备份打算；
这里每个公司都不一样，您别说那种1小时1全备什么的就行
(2)、备份复原工夫；
这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考
20G的2分钟（mysqldump）
80G的30分钟(mysqldump)
111G的30分钟（mysqldump)
288G的3小时（xtra)
3T的4小时（xtra)
逻辑导入工夫个别是备份工夫的5倍以上
(3)、xtrabackup实现原理
在InnoDB外部会保护一个redo日志文件，咱们也能够叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录批改。当InnoDB启动时，InnoDB会查看数据文件和事务日志，并执行两个步骤：它利用（前滚）曾经提交的事务日志到数据文件，并将批改过但没有提交的数据进行回滚操作。
9、mysqldump中备份进去的sql，如果我想sql文件中，一行只有一个insert….value()的话，怎么办？如果备份须要带上master的复制点信息怎么办？
--skip-extended-insert
[root@helei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-insertEnter password: KEY `idx_c1` (`c1`), KEY `idx_c2` (`c2`)) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `helei`--LOCK TABLES `helei` WRITE;/*!40000 ALTER TABLE `helei` DISABLE KEYS */;INSERT INTO `helei` VALUES (1,32,37,38,'2016-10-18 06:19:24','susususususususususususu');INSERT INTO `helei` VALUES (2,37,46,21,'2016-10-18 06:19:24','susususususu');INSERT INTO `helei` VALUES (3,21,5,14,'2016-10-18 06:19:24','susu');10、500台db，在最快工夫之内重启
puppet，dsh
11、innodb的读写参数优化
(1)、读取参数
global buffer pool以及 local buffer；
(2)、写入参数；
innodb_flush_log_at_trx_commit
innodb_buffer_pool_size
(3)、与IO相干的参数；
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0
(4)、缓存参数以及缓存的实用场景。
query cache/query_cache_type
并不是所有表都适宜应用query cache。造成query cache生效的起因次要是相应的table产生了变更第一个：读操作多的话看看比例，简略来说，如果是用户清单表，或者说是数据比例比拟固定，比如说商品列表，是能够关上的，前提是这些库比拟集中，数据库中的实务比拟小。
第二个：咱们“行骗”的时候，比如说咱们竞标的时候压测，把query cache关上，还是能收到qps激增的成果，当然前提醒前端的连接池什么的都配置一样。大部分状况下如果写入的居多，访问量并不多，那么就不要关上，例如社交网站的，10%的人产生内容，其余的90%都在生产，关上还是成果很好的，然而你如果是qq音讯，或者聊天，那就很要命。
第三个：小网站或者没有高并发的无所谓，高并发下，会看到 很多 qcache 锁 期待，所以个别高并发下，不倡议关上query cache
12、你是如何监控你们的数据库的？你们的慢日志都是怎么查问的？
监控的工具有很多，例如zabbix，lepus，我这里用的是lepus
13、你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？
主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等
14、你们数据库是否反对emoji表情，如果不反对，如何操作？
如果是utf8字符集的话，须要降级至utf8_mb4方可反对
15、你是如何保护数据库的数据字典的？
这个大家保护的办法都不同，我个别是间接在生产库进行正文，利用工具导出成excel不便流通。
16、表中有大字段X(例如：text类型)，且字段X不会常常更新，以读为为主，请问
拆带来的问题：连贯耗费 + 存储拆分空间；不拆可能带来的问题：查问性能；
1、如果能容忍拆分带来的空间问题,拆的话最好和常常要查问的表的主键在物理构造上搁置在一起(分区) 程序IO,缩小连贯耗费,最初这是一个文本列再加上一个全文索引来尽量对消连贯耗费
2、如果能容忍不拆分带来的查问性能损失的话:下面的计划在某个极致条件下必定会呈现问题,那么不拆就是最好的抉择
17、MySQL中InnoDB引擎的行锁是通过加在什么上实现(或称实现)的？为什么是这样子的？
InnoDB是基于索引来实现行锁
例: select * from tab_with_index where id = 1 for update;
for update 能够依据条件来实现行锁锁定,并且 id 是有索引键的列,
如果 id 不是索引键那么InnoDB将实现表锁,,并发将无从谈起
18、开放性问题：据说是腾讯的
一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查问出满足条件的第50000到第50200中的这200条数据记录。
1、如果A表TID是自增长,并且是间断的,B表的ID为索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;2、如果A表的TID不是间断的,那么就须要应用笼罩索引.TID要么是主键,要么是辅助索引,B表ID也须要有索引。
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;19、什么是存储过程？有哪些优缺点？
存储过程是一些预编译的SQL语句。
1、更加直白的了解：存储过程能够说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个办法一样实现一些性能（对单表或多表的增删改查），而后再给这个代码块取一个名字，在用到这个性能的时候调用他就行了。
2、存储过程是一个预编译的代码块，执行效率比拟高,一个存储过程代替大量T_SQL语句 ，能够升高网络通信量，进步通信速率,能够肯定水平上确保数据安全
20、索引是什么？有什么作用以及优缺点？
1、索引是对数据库表中一或多个列的值进行排序的构造，是帮忙MySQL高效获取数据的数据结构
2、索引就是放慢检索表中数据的办法。数据库的索引相似于书籍的索引。在书籍中，索引容许用户不用翻阅残缺个书就能迅速地找到所须要的信息。在数据库中，索引也容许数据库程序迅速地找到表中的数据，而不用扫描整个数据库。
MySQL数据库几个根本的索引类型：一般索引、惟一索引、主键索引、全文索引
1、索引放慢数据库的检索速度
2、索引升高了插入、删除、批改等保护工作的速度
3、惟一索引能够确保每一行数据的唯一性
4、通过应用索引，能够在查问的过程中应用优化暗藏器，进步零碎的性能
5、索引须要占物理和数据空间
21、什么是事务？
事务（Transaction）是并发管制的根本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库保护数据一致性的单位，在每个事务完结时，都能保持数据一致性。
24、数据库的乐观锁和乐观锁是什么？
数据库管理系统（DBMS）中的并发管制的工作是确保在多个事务同时存取数据库中同一数据时不毁坏事务的隔离性和统一性以及数据库的统一性。乐观并发管制(乐观锁)和乐观并发管制（乐观锁）是并发管制次要采纳的技术手段。
乐观锁：假设会产生并发抵触，屏蔽所有可能违反数据完整性的操作
乐观锁：假如不会产生并发抵触，只在提交操作时查看是否违反数据完整性。
22、应用索引查问肯定能进步查问的性能吗？为什么
通常,通过索引查问数据比全表扫描要快.然而咱们也必须留神到它的代价.
1、索引须要空间来存储,也须要定期维护, 每当有记录在表中增减或索引列被批改时,索引自身也会被批改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引须要额定的存储空间和解决,那些不必要的索引反而会使查问反应时间变慢.应用索引查问不肯定能进步查问性能,索引范畴查问(INDEX RANGE SCAN)实用于两种状况:
2、基于一个范畴的检索,个别查问返回后果集小于表中记录数的30%
3、基于非唯一性索引的检索
23、简略说一说drop、delete与truncate的区
SQL中的drop、delete、truncate都示意删除，然而三者有一些差异
1、delete和truncate只删除表的数据不删除表的构造
2、速度,一般来说: drop> truncate >delete
3、delete语句是dml,这个操作会放到rollback segement中,事务提交之后才失效;
4、如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立刻失效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
24、drop、delete与truncate别离在什么场景之下应用？
1、不再须要一张表的时候，用drop
2、想删除局部数据行时候，用delete，并且带上where子句
3、保留表而删除所有数据的时候用truncate
25、超键、候选键、主键、外键别离是什么？
1、超键：在关系中能惟一标识元组的属性集称为关系模式的超键。一个属性能够为作为一个超键，多个属性组合在一起也能够作为一个超键。超键蕴含候选键和主键。
2、候选键：是最小超键，即没有冗余元素的超键。
3、主键：数据库表中对贮存数据对象予以惟一和残缺标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
4、外键：在一个表中存在的另一个表的主键称此表的外键。
26、什么是视图？以及视图的应用场景有哪些？
1、视图是一种虚构的表，具备和物理表雷同的性能。能够对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的批改不影响根本表。它使得咱们获取数据更容易，相比多表查问。
2、只裸露局部字段给访问者，所以就建一个虚表，就是视图。
3、查问的数据来源于不同的表，而查问者心愿以对立的形式查问，这样也能够建设一个视图，把多个表查问后果联结起来，查问者只须要间接从视图中获取数据，不用思考数据来源于不同表所带来的差别
27、说一说三个范式。
第一范式（1NF）：数据库表中的字段都是繁多属性的，不可再分。这个繁多属性由根本类型形成，包含整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的局部函数依赖（局部函数依赖指的是存在组合关键字中的某些字段决定非关键字段的状况），也即所有非关键字段都齐全依赖于任意一组候选关键字。
第三范式（3NF）：在第二范式的根底上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则合乎第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因而，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y
如果你对三个还不太理解，倡议浏览：解释一下关系数据库的第一第二第三范式？