共计 8195 个字符，预计需要花费 21 分钟才能阅读完成。
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-insert
Enter 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
如果你对三个还不太理解，倡议浏览：解释一下关系数据库的第一第二第三范式？