[大数据]题目＞＞mysql＞＞1

1、说一下mysql主从复制（Replication）的原理

概念：是指数据可以从一个Mysql的master节点复制到一个或多个slaver节点。
用途：

• 读写分离：在开发过程中，会遇到某个sql语句需要锁表（update、delete），导致mysql不可读。主从分离后，master用于写、slaver用于读；
• 数据实时备份：当某个节点发生故障时，可以方便故障切换；
• 高可用HA
• 架构扩展：将流量分散到各个存储节点，提升性能。

主从复制原理

• 主从复制涉及到三个线程，一个在主节点（log dump thread），两个在从节点（I/O thread、SQL thread）。
  • log dump thread：当slaver连接master时，master会创建一个log dump线程，用于发送bin-log。在读取bin-log时，此线程会对master的bin-log加锁；读取完成时，锁被释放。
  • I/O thread：slaver上执行了 start slave之后，slaver会创建一个I/O线程用于连接master，请求master的bin-log。I/O线程接收到master的log dump线程发来的bin-log后，保存在本地的relay-log中。
  • SQL thread：负责读取relay-log 中的内容，解析成具体的操作执行，最终保证主从一致性。
• 复制步骤
  • master的**更新SQL（增删改）**被写到bin-log中；
  • slaver的I/O线程连接master，并请求从指定日志文件的指定位置之后的日志；
  • master接收到I/O请求后，log dump线程将指定的日志信息（包含bin-log file）返回给slaver；
  • slaver的I/O线程接收到内容后，将日志内容更新到本地的relay-log中，并将读取到的bin-log文件名和位置保存到master-info文件中，记录下一此请求的日志文件指定位置；
  • slaver的SQL线程检测到relay-log中新增内容后，会将relay-log的内容解析成具体的操作，并在本地数据库中执行。

mysql的主从复制模式

• 异步模式
  
  • mysql主从复制默认是异步模式。mysql的更新操作都保存到bin-log中，slaver连接master时，会主动从master获取最新的bin-log文件，并把bin-log处理为relay-log，再进行同步。
• 半同步模式
  
  • 每次用户commit时，需要master首先将bin-log同步到任意一台slaver上，只要接收到一台slaver的返回，master即可commit。
  • 优点：使主从数据库的数据延迟缩小；
  • 缺点：性能上会有降低，响应时间会变长；且master无法确认slaver是否已经同步到DB；
• 全同步模式
  • master和所有slaver全部执行了commit并确认之后才会向client返回成功。

2、mysql主从框架下，如何保证数据一致性问题

长链接

• master与slaver之间维持了一个长链接；master内部存在一个线程，专门服务于slaver的长链接；
• 为了保证master与slaver同步数据过程中不中断，造成数据丢失。

bin-log模式

• bin-log有三种格式，分别是statement、row和mixed。
  • statement：记录的是SQL原文，是binlog的默认方式。
    • 缺点：可能会存在由于主从数据库结构不一致（如索引不一致）而造成的更改数据错误，从而导致数据不一致。
  • row：记录的不是SQL原文，是两个event：Table_map（说明要操作的表）和Delete_rows（用于定义要删除的行为，记录删除的具体行数）。
    • 缺点：如果SQL删除10万行数据，row格式会很占空间，处理时会很消耗I/O。
  • mixed：mysql会根据执行的每一条具体的sql语句来区分使用statement还是row。

3、数据库主从延迟的原因与解决方案

主从延迟概念

• master写入binlog，当前时刻为T1；slaver接收到binlog，当前时刻为T2；slaver执行完此事务，当前时刻为T3。延迟时间为T3 - T1。

造成延迟的原因

• slaver的性能低于master。
  • 解决方案：提升slaver的性能，如更换机器、修改虚拟机配置等。
• slaver的流量压力过大。用户将大部分查询都打入slaver，导致slaver消耗大量CPU，影响同步速度。
  • 解决方案：增加slaver的机器数来分摊压力。
• 大事务造成的主从延迟。事务执行时间过长，如一次性delete太多SQL；使用了DDL语句（DDL：create、drop、alter；DML：update、delete、insert、select；DCL：grant）
  • 解决方案：将大事务进行拆分；闲时执行DDL语句。
• 网络延迟
• slaver过多，一般3-5个比较合适；
• 低版本的MySql只支持单线程复制，在高并发的状态下，master来不及传送给slaver。

4、数据库的高可用方案

双机主备（灾备）

• 架构描述：两台机器A和B，A为主库，负责读写；B为备库，只负责备份数据。如果A发生故障，则B提升为主库，负责读写；当A修复完成后，A变为备库，B的数据同步到A。
• 优点：一台机器故障了可以切换，操作简单；
• 缺点：只有一个库在工作，读写压力大，未能实现读写分离，并发也存在限制。

一主一从
一主多从

MariaDB同步多主机集群

• 架构描述：有代理层实现负载均衡，多个数据库可同时进行读写操作；各个数据库之间通过Galera Replication的方式进行数据同步；
• 优点：读写并发提升明显，可任意节点读写，自动剔除故障节点，具有高可用性；
• 缺点：无法支持大数据量。避免大数据卡死，如果集群中一个节点变慢，集群会变慢。

4、mysql的redo log、undo log、bin log异同？为什么binlog无法crash safe？

bin log

• bin log是mysql数据库级别的文件，记录了mysql数据库执行修改的所有操作，不会记录select和show语句；
• 有statement、row和mixed三种格式；
• 在实际应用中，使用场景为主从复制和数据恢复。
  • 主从复制：Master将binlog发送至slaver，实现主从一致；
  • 数据恢复：使用mysqlbinlog恢复工具来恢复数据。mysqlbinlog --start-position=219 --stop-position=1246 /mysql/data/binlog.000001 |mysql -uroot -p bhs

redo log

• redo log中记录的是要更新的数据。
• 如：数据A已经提交成功，并不会立即同步到磁盘，而是先记录到redo log中，等待合适的实际再刷盘，实现事务的持久性。
• 若不用redo log做缓冲，每次提交直接刷新到磁盘，可能会造成性能问题：
  • 因为Innodb是以页为单位进行磁盘交互，一个事务可能只修改了一个数据页A的几个字节，这时如果将完整的数据页A刷到磁盘的话，浪费资源；
  • 事务A可能修改多个物理上不连续的数据页，若是用随机IO写入，性能太差。
• 在实际应用中，用于持久化（如：主从复制的slaver持久化。）

undo log

• 当数据修改时，除了记录redo log，还会记录undo log。
• undo log用于数据的撤回操作。 它保留了记录修改之前的内容。
• 在实际应用中，可实现事务回滚。可根据undo log回溯到某个特定的版本的数据。

5、innodb的双写缓冲

5、mysql慢查询是什么？如何优化？

思路：从索引优化，到mysql自身缓存优化。最后结束其他缓存中间件。

8、

事务相关

1、什么是数据库事务？

是数据库执行过程中的一个逻辑单位。由一个有限的数据库操作序列构成，这些操作要么全部执行，要么全部不执行。是一个不可分割的工作单位。

2、事务的四大特性？实现原理是什么？

原子性

• 特性：事务作为一个整体被执行。包含在事务中的数据库操作序列要么全部被执行，要么全部不执行；
• 实现原理：是使用undo log实现的。如果事务执行过成中出错或用户执行了rollback，系统通过undo log日志返回事务开始的状态。
  隔离性
• 特性：多个事务并发访问时，事务之间是互相隔离的。一个事务的执行不会影响其他事务运行的效果。
• 实现原理：通过锁和MVCC，使事务相互隔离开；
  一致性
• 特性：指事务开始之前与事务结束以后，数据不会被破坏。如：A账户转账给B账户10块，不管成功与否，A、B账户总金额不变。
• 实现原理：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。
  持久性
• 特性：事务完成之后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。
• 实现原理：使用redo log来实现。只要redo log日志持久化了，数据库可以使用redo log来恢复数据。

3、事务的隔离级别有哪些？

• 读未提交：事务A开始写数据时，事务B不允许同时写数据；但是允许事务C读数据。解决了更新丢失问题，可能引起脏读；
• 读已提交：事务A开始写数据时，其他事务不可读写数据。解决了脏读的问题，但是可能出现不可重复读；
• 可重复读：事务A开始写数据时，多次读取数据a；事务A没结束时，其他事务不能读取或修改数据a。这样保证了事务A多次读取数据a的结果相同。解决了不可重复读的问题，但是会出现幻读。
• 可序化：提供严格的事务隔离。串行执行事务，需要保证新插入的数据不会被正在执行查询操作的事务访问到。解决了幻读，但是事务顺序执行，处理效率极低。

mysql默认的事务登记是可重复读。

4、脏读、不可重复读、幻读

脏读：读到了其他事务未提交的数据。未提交意味着数据可能被回滚，读到的数据最终不一定会存在；
不可重复读：在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况；
幻读：事务A在更新全表数据的某一个字段，已经读取完全表数据；此时事务B在表中新增了一行数据a；事务A的修改就不包含数据a。

索引相关

1、什么是索引？

索引是一种数据结构，可以快速检索数据库中的数据。

2、索引的特点？

优点

• 提高数据检索效率，降低数据库IO成本；
• 通过索引对数据进行排序，降低数据的排序成本，进而降低CPU的消耗。

缺点

• 建立索引需要占用磁盘空间；
• 会降低表的增删改的效率。因为每次对表进行修改，都要动态维护索引。

3、什么情况下需要索引？什么情况不需要？

需要

• 主键自动创建唯一索引；
• 频繁作为查询条件的字段；
• 查询中需要排序、统计或分组的字段。

不需要

• 表记录太少的字段；
• 经常增删改的字段；
• 唯一性太差的字段，不适合单独作为索引。如：民族、政治面貌等

4、介绍一下索引的分类？

• 普通索引：最基本的索引，无任何限制；
• 唯一索引：索引列（如id）的值必须是唯一的，但允许有空值。如果是组合索引，则列值的组合必须唯一；
• 主键索引：一个表只能有一个主键，是一种特殊的唯一索引，不允许有空值。
• 组合索引：一个索引包含多个列。
• 全文索引：全文搜索的索引。用于搜索很长一篇文章的时候效果最好。只能为数据结构为Char、Varchar、Text列创建。

主键索引与唯一索引的区别：

• 主键必唯一，但是唯一索引不一定是主键；
• 一张表只能有一个主键，但可能有一个或多个唯一索引。

5、索引的数据结构有哪些？

索引的数据结构主要有B+树与Hash表，对应的索引为B+树索引与**Hash索引。**默认索引类型为B+树索引。

Hash索引

• Hash索引是基于Hash表实现的。
• 当对某张表添加索引时，会将对应列数据进行hash计算，得到的结果作为Hash表的key，将指向数据行的指针作为hash表的value。
• 查找时间复杂度为O(1)，一般多用于**精确查找。**如 = in <=> 等。但是开发一般用BTree，因为Hash索引存在缺点。
• 缺点：
  • Hash索引仅能满足 = in <=> 等查询，不能应用于范围查询；
  • 无法用来避免数据的排序操作；
  • 不能利用部分索引键查询；
  • 任何时候都不能避免表扫描；
  • 在遇到大量Hash碰撞的情况下性能并不比B-Tree索引高。

B+树索引

• B树（balance） - 多路自平衡查找树
  
  • B树是一种平衡的多分树，M阶的B树，必须满足以下条件：
    • 所有节点都直接存储数据；
    • 每个节点最多只有M个子节点；如上图所示为4阶B数；
    • 每个非叶子节点（出了根节点）至少具有（m / 2）个子节点；
    • 若根不是叶子结点，则根需要有至少2个子节点；
    • 具有K个子节点的节点，拥有K-1个键；
    • 所有叶子结点都必须出现在同一水平层，即使没有数据，也要补全。

• B+树
  
  • B+树的特点
    • 有K个子节点的节点，拥有K个键；
    • 所有非叶子节点不保存数据，只保存键的key信息；
    • 所有叶子结点保存了全部数据，且叶子结点之间都存在链指针；叶子结点本身按照key从小到大排列。

6、为什么B+树比B树更适合实现数据库索引？

• B+树叶子结点保存了所有数据，且使用链表有序连接，所以要扫描全部数据只需扫描一遍叶子结点。有利于扫库和范围查询；
• B树非叶子结点也存放数据，所以只能通过中序遍历扫描所有节点，效率较B+树更低。

• B+树比B树减少了I/O读写次数
  • 由于索引文件很大，因此存放在磁盘上；
  • B+树的非叶子节点只存关键字，不存放数据。 所以每个节点比B树可存放更多关键字，即一次性读入内存的关键字就越多，磁盘的I/O读取次数相对更少。

• B+树的查询效率更加稳定。任何关键字的查找都必须从根节点走到叶子结点，所有关键字查找路径长度相同，导致每一次数据查询效率相当。

7、聚簇索引与非聚簇索引的关系？

聚簇索引

• 概念：按照每张表的主键构造一棵B+树，同时叶子结点存放的是整张表的行数据。每张表只能有一个聚簇索引。
• 特点：
  • 如果表设置了主键，则主键为聚簇索引；
  • 若表无主键，则会默认第一个NOT NULL，且唯一（unique）的列为聚簇索引；
  • 若不满足以上条件，则会默认创建一个隐藏的row_id作为聚簇索引。
• InnoDB的主键使用的就是聚簇索引。

非聚簇索引

• 概念：除去聚簇索引外，其他的索引被称为非聚簇索引。
• InnoDB的非聚簇索引的叶子结点存储的是主键（聚簇索引）的值。

8、什么是回表查询？

非聚簇索引的查询。

• 先通过费局促索引定位到叶子结点，叶子结点指向了主键（聚簇索引）的值；
• 再通过聚簇索引的值定位到聚簇索引的叶子结点，返回数据。

需要扫描2此B+树，效率会低。

9、什么是索引覆盖？

只需要在一个B+树上就能获得SQL所需的所有列数据，无需回表，速度更快。

• 如：user表主键id，索引age，用SQLselect id,age from user where age = 30; 搜索，则无需回表；若使用select id,age,name from user where age = 30; 来搜索，则需要回表。

10、什么是最左匹配原则？

针对组合索引。

• 概念：当设置组合索引的时候，应将最常用作限制条件的列放在最左边，依次递减。可以使用左侧字段进行查询，不可单独使用右侧字段。
• 如：user表中创建了组合索引（age, name），age是常用查询条件，所以放在左边。可以单独使用age作为条件查询，不能单独使用name。
  
• 原因：索引结构中，索引key的排序，首先按照age进行排序；age相同时，再按照name进行排序。因此如果单独查询name，由于无法确定age的值，因此无法使用索引。

11、索引失效的场景有哪些？

• 组合索引未使用最左前缀，使用右侧字段单独查询；
• or会使索引失效。查询字段相同时生效 age = 20 or age = 30；查询字段不同时失效；
• 列类型为字符串，查询不使用引号时。where name = 张三 ，而应该是 where name = '张三'；
• like未使用最左前缀。
• 在索引列上做计算、使用函数；
• 若mysql估计使用全表扫描比使用索引快，则使用索引。

12、索引设计原则

• 索引列的区分度越高（索引越长，区分度越高），效果越好。比如：性别区分度就很低；
• 尽量使用短索引。对于长字符串，应该指定一个较短的前缀长度。小索引使用磁盘I/O少，查询效率高；
• 索引不是越多越好，会占用磁盘空间；
• 利用组合索引的 最左匹配原则。
• 删除使用较少或失效的索引，恢复磁盘空间。

热门SQL编写

-- 
select * from