MySQL总结

一、维护类

1.什么是间隙（Next-Key）锁？

    当使用范围条件而不是相等条件检索数据的时候，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，称为“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙（Next-Key）锁。间隙锁是InnoDB中行锁的一种，但是这种锁锁住的不止一行数据，它锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读（Phantom Read），用在Repeated-Read（简称RR）隔离级别下。在Read-Commited（简称RC）下，一般没有间隙锁（有外键情况下例外，此处不考虑）。间隙锁还用于恢复和复制。
    间隙锁的出现主要集中在同一个事务中先DELETE后INSERT的情况下，当通过一个条件删除一条记录的时候，如果条件在数据库中已经存在，那么这个时候产生的是普通行锁，即锁住这个记录，然后删除，最后释放锁。如果这条记录不存在，那么问题就来了，数据库会扫描索引，发现这个记录不存在，这个时候的DELETE语句获取到的就是一个间隙锁，然后数据库会向左扫描，扫到第一个比给定参数小的值，向右扫描，扫描到第一个比给定参数大的值，然后以此为界，构建一个区间，锁住整个区间内的数据，一个特别容易出现死锁的间隙锁诞生了。

2. 事务的4种隔离级别（Isolation Level）分别是什么？

（1）Read Uncommitted（未提交读，读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果，即在未提交读级别，事务中的修改，即使没有提交，对其他事务也都是可见的，该隔离级别很少用于实际应用。读取未提交的数据，也被称之为脏读（Dirty Read）。该隔离级别最低，并发性能最高。
（2）Read Committed（提交读，读取提交内容）
这是大多数数据库系统的默认隔离级别。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
（3）Repeatable Read（可重复读）
可重复读可以确保同一个事务，在多次读取同样数据的时候，得到同样的结果。可重复读解决了脏读的问题，不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。MySQL数据库中的InnoDB和Falcon存储引擎通过MVCC（Multi-Version Concurrent Control，多版本并发控制）机制解决了该问题。需要注意的是，多版本只是解决不可重复读问题，而加上间隙锁（也就是它这里所谓的并发控制）才解决了幻读问题。
（4）Serializable（可串行化、序列化）
这是最高的隔离级别，它通过强制事务排序，强制事务串行执行，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能出现大量的超时现象和锁竞争。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑用该级别。这是花费代价最高但是最可靠的事务隔离级别。

3.如何在MySQL中查询OS线程id（LWP）？

答案：从MySQL 5.7开始，在performance_schema.threads中加了一列THREAD_OS_ID，可以通过该列匹配到OS线程id（LWP）。

4.什么是MySQL的pid文件？

答案：pid文件是MySQL实例的进程ID文件。当MySQL实例启动时，会将自己的进程ID写入一个文件中，该文件即为pid文件。该文件可由参数pid_file控制，默认路径位于数据库目录下，文件名为：主机名.pid

5.MySQL有哪几类物理文件？

1）参数文件：my.cnf。
2）日志文件，包括错误日志、查询日志、慢查询日志、二进制日志。
3）MySQL表文件：用来存放MySQL表结构的文件，一般以.frm为后缀。
4）Socket文件：当用Unix域套接字方式进行连接时需要的文件。
5）pid文件：MySQL实例的进程ID文件。
6）存储引擎文件：每个存储引擎都有自己的文件夹来保存各种数据，这些存储引擎真正存储了数据和索引等数据。

6.MySQL有哪几类日志文件？

1.错误日志

错误日志记录了MySQL在启动、运行和关闭过程中的重要信息。具体来说，错误日志记录的信息包括：
1）服务器启动、关闭过程中的信息。
2）服务器运行过程中的错误信息。
3）事件调试器运行一个事件产生的信息。
4）在从服务器上启动服务器进程时产生的信息。

2.全查询日志（通用查询日志、查询日志）

全查询日志记录了所有对数据库请求的信息，不论这些请求是否得到了正确的执行。默认位置在变量datadir下，默认文件名为：主机名.log。在默认情况下，MySQL的全查询日志是不开启的。

3.慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，他用来记录在MySQL中响应时间超过预先设定的阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

4.二进制日志

二进制日志记录了对数据库进行变更的所有操作，但是不包括SELECT操作以及SHOW操作，因为这类操作对数据库本身没有修改。如果想记录SELECT和SHOW，那么就需要开启全查询日志，另外，二进制日志还包括了执行数据库更改操作的时间等信息。

5.中继日志

从主服务器的二进制日志文件中复制而来的事件，并保存为二进制的日志文件。中继日志也是二进制日志，用来给slave库恢复使用。

6.事务日志

事务日志记录InnoDB等支持事务的存储引擎执行事务时产生的日志。事务型存储引擎用于保证原子性、一致性、隔离性和持久性。其变更数据不会立即写到数据文件中，而是写到事务日志中。事务日志文件名为“ib_logfile0”和“ib_logfile1”，默认存放在表空间所在目录。

7.MySQL的redolog和binlog到底有啥子区别

MySQL中有三种日志文件，redo log、bin log、undo log。
redo log 是 存储引擎层(innodb)生成的日志，主要为了保证数据的可靠性；
bin log 是 MySQL server层面上生成的日志，主要用于 point in time 恢复和主从复制。
undo log 主要用于事务的回滚(undo log 记录的是每个修改操作的逆操作) 和 一致性非锁定读(undo log 回滚行记录到某种特定的版本—MVCC 多版本并发控制)。
区别如下：

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑。
3. redo log 是循环写的，空间固定会用完（4个文件，每个文件1Ｇ）；binlog 是可以追加写入的。“追加写”是指 binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
4. binlog 日志没有 crash-safe 的能力，只能用于归档。而 redo log 来实现 crash-safe 能力。
5. redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。
6. sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。
7. binlog有两种模式，statement 格式是记sql语句， row格式是记录行的内容，记两条，更新前和更新后都有。

8.MySQL支持的复制类型

（1）基于语句的复制（逻辑复制）：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选择基于行的复制。
（2）基于行的复制：把改变的内容复制过去，而不是把命令再从服务器上执行一遍。从MySQL 5.0开始支持。
（3）混合类型的复制：默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

9.Oracle和MySQL中的分组（GROUP BY）有什么区别？

Oracle对于分组（GROUP BY）是严格的，所有要SELECT出来的字段必须在GROUP BY后边出现，否则会报错：“ORA-00979:not a GROUP BY expression”。而MySQL则不同，如果SELECT出来的字段在GROUP BY后面没有出现，那么会随机取出一个值，而这样查询出来的数据不准确，语义也不明确。所以，作者建议在写SQL语句时，应该给数据库一个非常明确的指令，而不是让数据库去猜测，这也是写SQL语句的一个非常良好的习惯。

10.MySQL的分库分表和表分区（Partitioning）有什么区别？

答案：分库分表是指把数据库中的数据物理地拆分到多个实例或多台机器上去。分表指的是通过一定规则，将一张表分解成多张不同的表。
分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

11.MySQL有几种存储引擎（表类型）？各自有什么区别？

1.MyISAM

MyISAM存储引擎管理非事务表，提供高速存储和检索，以及全文搜索能力。该引擎插入数据快，空间和内存使用比较低。

2.InnoDB

InnoDB用于事务处理应用程序，主要面向OLTP方面的应用。该引擎由InnoDB公司开发，其特点是行锁设置，并支持类似于Oracle的非锁定读，即默认情况下读不产生锁。InnoDB将数据放在一个逻辑表空间中。InnoDB通过多版本并发控制来获得高并发性，实现了ANSI标准的4种隔离级别，默认为Repeatable，使用一种被称为next-key locking的策略避免幻读。对于表中数据的存储，InnoDB采用类似Oracle索引组织表Clustered的方式进行存储。如果对事务的完整性要求比较高，要求实现并发控制，那么选择InnoDB引擎有很大的优势。需要频繁地进行更新，删除操作的数据库，也可以选择InnoDB存储引擎。因为，InnoDB存储引擎提供了具有提交（COMMIT）、回滚（ROLLBACK）和崩溃恢复能力的事务安全。

3.MEMORY（HEAP）

MEMORY存储引擎（之前称为Heap）提供“内存中”的表。如果需要很快的读写速度，对数据的安全性要求较低，那么可选择MEMORY存储引擎。MEMORY存储引擎对表大小有要求，不能建太大的表。所以，这类数据库只适用相对较小的数据库表。如果mysqld进程发生异常，那么数据库就会重启或崩溃，数据就会丢失，因此，MEMORY存储引擎中的表的生命周期很短，一般只使用一次，非常适合存储临时数据。

4.MERGE

MERGE存储引擎允许将一组使用MyISAM存储引擎的并且表结构相同（即每张表的字段顺序、字段名称、字段类型、索引定义的顺序及其定义的方式必须相同）的数据表合并为一个表，方便了数据的查询。需要注意的是，使用MERGE“合并”起来的表结构相同的表最好不要有主键，否则会出现这种情况：一共有两个成员表，其主键在两个表中存在相同情况，但是写了一条按相同主键值查询的SQL语句，这时只能查到UNION列表中第一个表中的数据。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。

5.BDB（BerkeleyDB）

BDB是事务型存储引擎，支持COMMIT、ROLLBACK和其他事务特性，它由Sleepycat软件公司（http://www.sleepycat.com）开发。BDB是一个高性能的嵌入式数据库编程库（引擎），它可以用来保存任意类型的键/值对（Key/Value Pair），而且可以为一个键保存多个数据。BDB可以支持数千的并发线程同时操作数据库，支持最大256TB的数据。BDB存储引擎处理事务安全的表，并以哈希为基础的存储系统。
适用场景：BDB存储引擎适合快速的读写某些数据，特别是不同KEY的数据。

6.EXAMPLE

EXAMPLE存储引擎是一个“存根”引擎，可以用这个引擎创建表，但数据不能存储在该引擎中。EXAMPLE存储引擎可为快速创建定制的插件式存储引擎提供帮助。

7.NDB

NDB该存储引擎是一个集群存储引擎，是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎，类似于Oracle的RAC，但它是Share Nothing的架构，因此，能提供更高级别的高可用性和可扩展性。NDB的特点是数据全部放在内存中，因此，通过主键查找非常快。它在MySQL-Max 5.1二进制分发版里提供。

8.ARCHIVE

ARCHIVE存储引擎只支持INSERT和SELECT操作，其设计的主要目的是提供高速的插入和压缩功能。
适用场景：ARCHIVE非常适合存储归档数据，如日志信息。
优点：ARCHIVE存储引擎被用来无索引地，非常小地覆盖存储的大量数据。为大量很少引用的历史、归档或安全审计信息的存储和检索提供了完美的解决方案。
缺点：不支持事务，只支持INSERT和SELECT操作。

12. MySQL InnoDB引擎类型的表有哪两类表空间模式？它们各有什么优缺点？

1）使用共享表空间存储，这种方式创建的表结构保存在.frm文件中。Innodb的所有数据和索引保存在一个单独的表空间（由参数innodb_data_home_dir和innodb_data_file_path定义，若innodb_data_home_dir为空，则默认存放在datadir下，初始化大小为10M）里，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。
2）使用独立表空间（多表空间）存储，这种方式创建的表结构仍然保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。如果是个分区表，那么每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区数据文件的位置，以此来将表的I/O均匀分布在多个磁盘上。

13.MySQL原生支持的备份方式及种类有哪些？

根据备份方法，备份可以分为如下3种：
1）热备份（Hot Backup）：热备份也称为在线备份（Online Backup），是指在数据库运行的过程中进行备份，对生产环境中的数据库运行没有任何影响。常见的热备方案是利用mysqldump、XtraBackup等工具进行备份。
2）冷备份（Cold Backup）：冷备份也称为离线备份（Offline Backup），是指在数据库关闭的情况下进行备份，这种备份非常简单，只需要关闭数据库，复制相关的物理文件即可。目前，线上数据库一般很少能够接受关闭数据库，所以该备份方式很少使用。
3）温备份（Warm Backup）：温备份也是在数据库运行的过程中进行备份，但是备份会对数据库操作有所影响。该备份利用锁表的原理备份数据库，由于影响了数据库的操作，故该备份方式也很少使用。

14.MySQL数据表在什么情况下容易损坏？

答案：服务器突然断电导致数据文件损坏；强制关机，没有先关闭mysqld服务等。

15.数据表损坏的修复方式有哪些？

答案：可以使用myisamchk来修复，具体步骤：
1）修复前将mysqld服务停止。
2）打开命令行方式，然后进入到mysql的/bin目录。
3）执行myisamchk-recover数据库所在路径/*.MYI。
使用repair table或者OPTIMIZE table命令来修复，REPAIR TABLE table_name修复表OPTIMIZE TABLE table_name优化表REPAIR TABLE用于修复被破坏的表。
OPTIMIZE TABLE用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。

16.如果MySQL数据库的服务器CPU非常高，那么该如何处理？

答案：当服务器CPU很高时，可以先用操作系统命令top命令观察是不是mysqld占用导致的，如果不是，那么找出占用高的进程，并进行相关处理。如果是mysqld造成的，那么可以使用show processlist命令查看里面数据库的会话情况，是不是有非常消耗资源的SQL在运行。找出消耗高的SQL，看看执行计划是否准确，INDEX是否缺失，或者确实是数据量太大造成。一般来说，肯定要kill掉这些线程（同时观察CPU使用率是否下降），等进行相应的调整（例如，加索引、改写SQL、改内存参数）之后，再重新运行这些SQL。也有可能是每个SQL消耗资源并不多，但是突然之间，有大量的会话连接数据库导致CPU飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，例如，限制连接数等。

17.什么是MySQL的GTID？

答案：GTID（Global Transaction ID，全局事务ID）是全局事务标识符，是一个已提交事务的编号，并且是一个全局唯一的编号。GTID是从MySQL 5.6版本开始在主从复制方面推出的重量级特性。GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。

18.在MySQL中如何有效的删除一个大表？

答案：在Oracle中对于大表的删除可以通过先TRUNCATE+REUSE STORAGE参数，再使用DEALLOCATE逐步缩小，最后DROP掉表。在MySQL中，对于大表的删除，可以通过建立硬链接（Hard Link）的方式来删除。

19.在登录MySQL时遇到“ERROR 1040 (00000):Too many connections”错误，如何解决？

答案：该错误表示连接数过多，不能正常登录数据库。主要原因是max_connections参数设置过小，该参数表示允许客户端并发连接的最大数量，默认值是151，最小值为1，最大值为100000。需要注意的是，其实MySQL允许的最大连接数为：max_connections+1，因为超出的一个用户是作为超级管理员来使用的。所以，若max_connections的值设置为1，则第3个客户端登录才会报“Too many connections”的错误。

20.max_connect_errors参数的作用是什么？

答案：max_connect_errors参数表示如果MySQL服务器连续接收到了来自于同一个主机的请求，且这些连续的请求全部都没有成功的建立连接就被断开了，当这些连续的请求的累计值大于max_connect_errors的设定值时，MySQL服务器就会阻止这台主机后续的所有请求。

21.状态变量Max_used_connections的作用是什么？

答案：系统状态变量Max_used_connections是指从这次MySQL服务启动到现在，同一时刻并行连接数的最大值。它不是指当前的连接情况，而是一个比较值。如果在过去某一个时刻，MySQL服务同时有1000个请求连接过来，而之后再也没有出现这么大的并发请求时，那么Max_used_connections=1000。

22.参数wait_timeout和interactive_timeout的作用和区别是什么？

答案：interactive_timeout表示MySQL服务器关闭交互式连接前等待活动的秒数；wait_timeout表示MySQL服务器关闭非交互连接之前等待活动的秒数。这2个参数的默认值都是28800，单位秒，即8个小时。需要注意的是，这2个参数需要同时设置才会生效。

23.PXC架构优缺点

优点：
(1)实现了MySQL数据库的高可用和数据的强一致性
(2)完成了真正的多节点读写的集群方案
(3)基本达到了实时同步，改善了传统意义上的主从延迟问题
(4)新加入的节点可以自动部署，无需提供手动备份
(5)数据库的故障切换容易
缺点：
(1)新加入的节点开销大，需要复制完整的数据。采用sst传输开销太大
(2)任何更新事务都需要全局验证通过，才会在每个节点库上执行，集群性能受限于性能最差的节点（短板效应）。
(3)因为需要保证数据的一致性，所以在多个节点并发写时，所冲突比较严重
(4)存在写扩大问题，所有节点都会发生写操作
(5)只支持InnoDB存储引擎
(6)没有表级别的锁定，执行DDL语句操作会把整个集群锁住，而且“kill”不掉（建议使用osc操作）
(7)所有表必须有主键，不然操作数据时会报错

24.pxc节点状态变化阶段

open-节点启动成功，尝试连接到集群
primary-节点已处于集群中，在新节点加入时选区doner进行数据同步会产生的状态
joiner-节点处于等待接受同步文件时的状态
joined-节点完成数据同步工作，尝试保持与集群进度一致
synced-节点正常提供服务的状态，表示以经同步完成并和集群进度保持一致
doner-节点处于为新加入的节点提供全量数据时的状态

25.什么是幻读，脏读，不可重复读呢？

事务A、B交替执行，事务A被事务B干扰到了，因为事务A读取到事务B未提交的数据,这就是脏读
在一个事务范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。
事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

26.MySQL主从复制有哪些进程？

MySQL完成主从复制，需要三个线程的参与：一个在(Master)主节点上，另外两个在(Slave)从节点上
1、(Master)Binlog Dump线程：
当有从节点连接到主节点时，主节点就创建一个线程将该主节点的bin-log内容发送到主动连接的从节点上。创建的这个线程可以使用下面的命令在主节点的标准输出上以“Binlog Dump”名字显示。
SHOW PROCESSLIST
为了读取主节点上即将被发送到从节点的每一个事件(Event)，binlog Dump线程会对该binlog添加一个锁，直到这个事件被读取到甚至发送到从节点上，这个锁才会被释放。
2、(Slave)I/O线程：
当一个"START SLAVE"语句在从节点上发出时，从节点创建一个I/O线程；用来连接到配置好的主节点上并请求其发送binlog中的更新记录。这个I/O线程读取从binlog Dump线程发送过来的更新信息，将其复制到本地文件内(Relay log)。在"SHOW SLAVE STATUS"语句的输出内容中，显示为：Slave_IO_running；在"SHOW STATUS"语句的输出中显示为:Slave_running
3、(Slave)SQL线程：
从节点创建一个Slave SQL线程去读取由Slave I/O创建的relay log中的内容，将读取到的事件在从节点上进行重现。

27.mysqldump以及xtranbackup的实现原理

1. mysqldump属于逻辑备份。

 加入–single-transaction选项可以进行一致性备份。后台进程会先设置session的事务隔离级别为RR(SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ)，之后显式开启一个事务(START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */)，这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。如果加上–master-data=1的话，在刚开始的时候还会加一个数据库的读锁(FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时binlog的位置(show master status)，马上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务。

2. xtrabackup属于物理备份;

 直接拷贝表空间文件，同时不断扫描产生的redo日志并保存下来。最后完成innodb的备份后，会做一个flush engine logs的操作(老版本在有bug，在5.6上不做此操作会丢数据)，确保所有的redo log都已经落盘(涉及到事务的两阶段提交概念，因为xtrabackup并不拷贝binlog，所以必须保证所有的redo log都落盘，否则可能会丢最后一组提交事务的数据)。这个时间点就是innodb完成备份的时间点，数据文件虽然不是一致性的，但是有这段时间的redo就可以让数据文件达到一致性(恢复的时候做的事情)。然后还需要flush tables with read lock，把myisam等其他引擎的表给备份出来，备份完后解锁。这样就做到了完美的热备。

28.MGR优缺点

MGR要求

1.必须适用innodb存储引擎
2.创建的业务表，必须要有主键
3.MGR必须适用IPv4网络，不支持IPv6
4.MGR复制网络必须和业务网络隔离
5.binlog日志格式必须为row模式
6.关闭二进制日志校验和，设置–binlog-checksum=NONE
7.小写 table 格名称. 在所有组成员上将–lower-case-table-names设置为相同的值
8.隔离级别设置为RC

MGR限制

1.MGR不支持SERIALIZABLE 隔离级别
2.MGR集群节点不能超过9
3.MGR不支持大事务，事务大小最好不超过143MB，当事务过大，无法在5 秒的时间内通过网络在组成员之间复制消息，则可能会怀疑成员失败了，然后将其驱逐出局。
4.并发 DDL 与 DML 操作. 当使用多主模式时，不支持针对同一对象但在不同服务器上执行的并发数据定义语句和数据操作语句。
5.对表的级联约束的外键支持不好，不建议适用。

29.你详细了解过MVCC吗？它是怎么工作的？

叫做多版本控制，实现MVCC时用到了一致性视图，用于支持读提交和可重复读的实现。
对于一行数据若是想实现可重复读取或者能够读取数据的另一个事务未提交前的原始值，那么必须对原始数据进行保存或者对更新操作进行保存，这样才能够查询到原始值。
在Mysql的MVCC中规定每一行数据都有多个不同的版本，一个事务更新操作完后就生成一个新的版本，并不是对全部数据的全量备份，因为全量备份的代价太大了：

30.主键使用自增ID还是UUID?能说说原因吗？

自增ID和UUID作为主键的考虑主要有两方面，一个是性能另一个就是存储的空间大小，一般没有特定的业务要求都不推荐使用UUID作为主键。
因为使用UUID作为主键插入并不能保证插入是有序的，有可能会涉及数据的挪动，也有可能触发数据页的分裂，因为一个数据页的大小就是16KB，这样插入数据的成本就会比较高。
而自增ID作为主键的话插入数据都是追加操作，不会有数据的移动以及数据页的分裂，性能会比较好。
另一方面就是存储空间，自增主键一般整形只要4个字节，长整形才占8字节的大小空间，而使用UUID作为主键存储空间需要16字节的大小，会占用更多的磁盘，在二级索引中也会存出一份主键索引，这样多占用消耗的空间就是两倍，性能低，所以不推荐使用。

二、索引

1.MySQL中的索引有哪些分类？

1）BTREE索引：最常见的索引类型，大部分引擎都支持BTREE索引，例如MyISASM、InnoDB、MEMORY等。
2）HASH索引：只有MEMORY和NDB引擎支持，适用于简单场景。
3）RTREE索引（空间索引）：空间索引是MylSAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
4）FULLTEXT（全文索引）：全文索引也是MylSAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL 5.6版本开始提供对全文索引的支持。

2.什么是覆盖索引？

如果一个索引包含（或者说覆盖了）所有满足查询所需要的数据，那么就称这类索引为覆盖索引（Covering Index）。索引覆盖查询不需要回表操作。在MySQL中，可以通过使用explain命令输出的Extra列来判断是否使用了索引覆盖查询。若使用了索引覆盖查询，则Extra列包含“Using index””字符串。MySQL查询优化器在执行查询前会判断是否有一个索引能执行覆盖查询。

3.什么是哈希索引？

哈希索引（Hash Index）建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引的哈希码（Hash Code），它是一个较小的值，并且有可能和其他行的哈希码不同。它把哈希码保存在索引中，并且保存了一个指向哈希表中的每一行的指针。如果多个值有相同的哈希码，那么索引就会把行指针以链表的方式保存在哈希表的同一条记录中。

4.什么是自适应哈希索引（Adaptive HashIndex）？

InnoDB引擎有一个特殊的功能称为自适应哈希索引（Adaptive Hash Index）。当InnoDB注意到某些索引值被使用非常频繁时，它会在内存中基于BTree索引之上再创建一个哈希索引，这样就让BTree索引也具有哈希索引的一些优点，例如：快速的哈希查找，这是一个全自动的，内部的行为，用户无法控制或者配置，不过如果有必要，可以选择关闭这个功能（innodb_adaptive_hash_index=OFF，默认为ON）。

5.什么是前缀索引？

有时候需要索引很长的字符列，这会让索引变得大且慢，此时可以考虑前缀索引。MySQL目前还不支持函数索引，但是支持前缀索引，即对索引字段的前N个字符创建索引，这个特性可以大大缩小索引文件的大小，从而提高索引效率。用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。前缀索引是一种能使索引更小、更快的有效办法。

6. 什么是全文（FULLTEXT）索引？

使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。在查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。在默认情况下，全文索引的搜索执行方式不区分大小写。但是，当索引的列使用二进制排序后，可以执行区分大小写的全文索引。

7.什么是空间（SPATIAL）索引？

使用SPATIDX参数可以设置索引为空间索引，这个所以可以被用作地理数据支持。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索（InnoDB从5.7.5开始支持），而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

8.为什么索引没有被使用？

1）若索引列出现了隐式类型转换（Implicit Type Conversion），则MySQL不会使用索引。
2）在使用cast函数时，需要保证字符集一样，否则MySQL不会使用索引。
3）如果WHERE条件中含有OR，除非OR条件中的所有列都是索引列，否则MySQL不会选择索引。
4）对于多列索引，若没有使用前导列，则MySQL不会使用索引。
5）在WHERE子句中，如果索引列所对应的值的第一个字符由通配符（WILDCARD）开始，索引将不被采用，然而当通配符出现在字符串其他位置时，优化器就能利用索引。
6）如果MySQL估计使用全表扫描要比使用索引快，那么MySQL将不使用索引。
7）如果对索引字段进行函数、算术运算或其他表达式等操作，那么MySQL也不使用索引。

9.简单描述在MySQL中，索引、唯一索引、主键、联合索引的区别，它们对数据库的性能有什么影响。

1）索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。
2）唯一索引：普通索引允许被索引的数据列包含重复的值，如果能确定某个数据列只包含彼此各不相同的值，在为这个数据索引创建索引的时候就应该用关键字UNIQE把它定义为一个唯一索引，唯一索引可以保证数据记录的唯一性。
3）主键，一种特殊的唯一索引，在一张表中只能定义一个主键索引，逐渐用于唯一标识一条记录，是用关键字PRIMARY KEY来创建。
4）联合索引：索引可以覆盖多个数据列，例如INDEX索引，这就是联合索引。
索引可以极大地提高数据的查询速度，但是会降低插入删除更新表的速度，因为在执行这些写操作时，还需要操作索引文件。

三、优化

1.如何分析一条SQL语句的执行性能？需要关注哪些信息？

答案：使用EXPLAIN命令，通过观察TYPE列，就可以知道是否使用了全表扫描，同时也可以知道索引的使用形式，通过观察KEY可以知道使用了哪个索引，通过观察KEY_LEN可以知道索引是否使用完成，通过观察ROWS可以知道扫描的行数是否过多，通过观察EXTRA可以知道是否使用了临时表以及是否使用了额外的排序操作。
• 查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，是否可拆表等
• 优化索引结构，看是否可以适当添加索引
• 数量大的表，可以考虑进行分离/分表（如交易流水表）
• 数据库主从分离，读写分离
• explain分析sql语句，查看执行计划，优化sql
• 查看mysql执行日志，分析是否有其他方面的问题

2.请简述项目中优化SQL语句执行效率的方法。

答案：可以从以下几个方面进行优化：
1）尽量选择较小的列。
2）将WHERE中用的比较频繁的字段建立索引。
3）SELECT子句中避免使用’*’。
4）避免在索引列上使用计算，NOT、IN和<>等操作。
5）当只需要一行数据的时候使用limit 1。
6）保证表单数据不超过200W，适时分割表。
7）针对查询较慢的语句，可以使用explain来分析该语句具体的执行情况。

3.如何提高INSERT的性能？

答案：可以从如下几方面考虑：
1）合并多条INSERT为一条，即：insert into t values(a,b,c),(d,e,f),
主要原因是多条INSERT合并后写日志的数量（MySQL的binlog和innodb的事务让日志）减少了，因此降低了日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的I/O。
2）修改参数bulk_insert_buffer_size，调大批量插入的缓存。
3）设置innodb_flush_log_at_trx_commit=0，相对于innodb_flush_log_at_trx_commit=1可以十分明显的提升导入速度。需要注意的是，innodb_flush_log_at_trx_commit参数对InnoDB Log的写入性能有非常关键的影响。
4）手动使用事务。因为MySQL默认是autocommit的，这样每插入一条数据，都会进行一次commit；所以，为了减少创建事务的消耗，可用手工使用事务

4.如何对SQL语句进行跟踪（trace）？

MySQL 5.6.3提供了对SQL语句的跟踪功能，通过trace文件可以进一步了解优化器是如何选择某个执行计划的，与Oracle的10053事件类似。在使用时需要先打开设置，然后执行一次SQL，最后查看information_schema.optimizer_trace表的内容。需要注意的是，该表为临时表，只能在当前会话进行查询，每次查询返回的都是最近一次执行的SQL语句。

5.如何对MySQL的大表优化？

当MySQL单表记录数过大时，数据库的CRUD（C即Create，表示增加；R即Retrieve，表示读取查询；U即Update，表示更新；D即Delete，表示删除）性能会明显下降，一些常见的优化措施如下：
1）限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。例如：当用户在查询订单历史的时候，可以控制在一个月范围内。
2）读写分离：经典的数据库拆分方案，主库负责写，从库负责读。
3）缓存：使用MySQL的缓存。另外对重量级、更新少的数据可以考虑使用应用级别的缓存。
4）垂直分区：根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。
5）水平分区：保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

6.SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义。

• show status 命令了解各种 sql 的执行频率
• 通过慢查询日志定位那些执行效率较低的 sql 语句
• explain 分析低效 sql 的执行计划（这点非常重要，日常开发中用它分析Sql，会大大降低Sql导致的线上事故）
7.说一下大表查询的优化方案
• 优化shema、sql语句+索引；
• 可以考虑加缓存，memcached, redis，或者JVM本地缓存；
• 主从复制，读写分离；
• 分库分表；

8.MySQL写入参数哪些可以调整以提升写入性能？

sync_binlog设置为1，保证binlog的安全性。
innodb_flush_log_at_trx_commit：
0：事务提交时不将redo log buffer写入磁盘(仅每秒进行master thread刷新，安全性最差，性能最好)
1：事务提交时将redo log buffer写入磁盘(安全性最好，性能最差，推荐生产使用)
2：事务提交时仅将redo log buffer写入操作系统缓存(安全性和性能都居中，当mysql宕机但是操作系统不宕机则不丢数据，如果操作系统宕机，最多丢一秒数。
innodb_io_capacity/innodb_io_capacity_max：看磁盘的性能来定。
如果是HDD可以设置为200-几百不等。如果是SSD，推荐为4000左右。innodb_io_capacity_max更大一些。innodb_flush_method设置为O_DIRECT。

四、分库分表

1.分库分表中解释一下垂直和水平2种不同的拆分？

垂直拆分：是将单表，或者是有关联的表放在一个数据库，把原有的一个数据库拆分成若干个数据库。
水平拆分：是将一个很大的表，通过取模，按照日期范围等等拆分成若干个小表

2.解释一下全局表，ER表，分片表？

全局表：一个字典类数据的表，每个表都有可能用到，在各个数据节点上都会冗余。
分片表：按照一定的规则后，表按照设置的primaryKey来分配到不同的数据节点上。
ER表：和分片表有外键关系的表，也是通过设置的primaryKey即与分片表的外键，和分片表按照一样的规则分配到不同的数据节点上。

3.分库分表可能遇到的问题

事务问题：需要用分布式事务啦
跨节点Join的问题：解决这一问题可以分两次查询实现
跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。
数据迁移，容量规划，扩容等问题
ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID
跨分片的排序分页问题（后台加大pagesize处理？）

五、Redis缓存穿透、缓存击穿、缓存雪崩

1.缓存穿透

缓存穿透是指查询一个一定不存在的数据，由于缓存是不命中时被动写的，并且出于容错考虑，如果从存储层查不到数据则不写入缓存，这将导致这个不存在的数据每次请求都要到存储层去查询，失去了缓存的意义。在流量大时，可能DB就挂掉了，要是有人利用不存在的key频繁攻击我们的应用，这就是漏洞。
解决方案：
有很多种方法可以有效地解决缓存穿透问题，最常见的则是采用布隆过滤器，将所有可能存在的数据哈希到一个足够大的bitmap中，一个一定不存在的数据会被 这个bitmap拦截掉，从而避免了对底层存储系统的查询压力。另外也有一个更为简单粗暴的方法（我们采用的就是这种），如果一个查询返回的数据为空（不管是数 据不存在，还是系统故障），我们仍然把这个空结果进行缓存，但它的过期时间会很短，最长不超过五分钟。

2.缓存雪崩

缓存雪崩是指在我们设置缓存时采用了相同的过期时间，导致缓存在某一时刻同时失效，请求全部转发到DB，DB瞬时压力过重雪崩。
解决方案：
缓存失效时的雪崩效应对底层系统的冲击非常可怕。大多数系统设计者考虑用加锁或者队列的方式保证缓存的单线程（进程）写，从而避免失效时大量的并发请求落到底层存储系统上。这里分享一个简单方案就时讲缓存失效时间分散开，比如我们可以在原有的失效时间基础上增加一个随机值，比如1-5分钟随机，这样每一个缓存的过期时间的重复率就会降低，就很难引发集体失效的事件。

3.缓存击穿

对于一些设置了过期时间的key，如果这些key可能会在某些时间点被超高并发地访问，是一种非常“热点”的数据。这个时候，需要考虑一个问题：缓存被“击穿”的问题，这个和缓存雪崩的区别在于这里针对某一key缓存，前者则是很多key。
缓存在某个时间点过期的时候，恰好在这个时间点对这个Key有大量的并发请求过来，这些请求发现缓存过期一般都会从后端DB加载数据并回设到缓存，这个时候大并发的请求可能会瞬间把后端DB压垮。
解决方案：

3.1使用互斥锁(mutex key)

业界比较常用的做法，是使用mutex。简单地来说，就是在缓存失效的时候（判断拿出来的值为空），不是立即去load db，而是先使用缓存工具的某些带成功操作返回值的操作（比如Redis的SETNX或者Memcache的ADD）去set一个mutex key，当操作返回成功时，再进行load db的操作并回设缓存；否则，就重试整个get缓存的方法。

3.2 "提前"使用互斥锁(mutex key)：

在value内部设置1个超时值(timeout1), timeout1比实际的memcache timeout(timeout2)小。当从cache读取到timeout1发现它已经过期时候，马上延长timeout1并重新设置到cache。然后再从数据库加载数据并设置到cache中。

3.3 “永远不过期”：

这里的“永远不过期”包含两层意思：
(1) 从redis上看，确实没有设置过期时间，这就保证了，不会出现热点key过期问题，也就是“物理”不过期。
(2) 从功能上看，如果不过期，那不就成静态的了吗？所以我们把过期时间存在key对应的value里，如果发现要过期了，通过一个后台的异步线程进行缓存的构建，也就是“逻辑”过期

3.4资源保护：

采用netflix的hystrix，可以做资源的隔离保护主线程池，如果把这个应用到缓存的构建也未尝不可