数据库

• 1.事务的四个特性？

四大特性是：ACID 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）+介绍四个特性概念；

原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性：当两个或者多个事务并发访问（此处访问指查询和修改的操作）数据库的同一数据时所表现出的互相关系。事务隔离分为不同的级别，包括读未提交（Read uncommitted）、读提交（Read committed）、可重复读（Repeatable read）和串行化（Serializable）。隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性：在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

• 2.事务的隔离级别

读未提交（Read uncommitted）、读已提交（Read committed）、可重复读（Repeatable read）和串行化（Serializable）

　　① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

　　SERIALIZABLE可以防止除更新丢失外所有的一致性问题,即:

　　　　1.语句无法读取其它事务已修改但未提交的记录。

　　　　2.在当前事务完成之前，其它事务不能修改目前事务已读取的记录。

　　　　3.在当前事务完成之前，其它事务所插入的新记录，其索引键值不能在当前事务的任何语句所读取的索引键范围中。

　　② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

　　REPEATABLE READ事务不会产生脏读，并且在事务完成之前，任何其它事务都不能修改目前事务已读取的记录。其它事务仍可以插入新记录，但必须符合当前事务的搜索条件——这意味着当前事务重新查询记录时，会产生幻读（Phantom Read）。

　　③ Read committed (读已提交)：可避免脏读的发生。

　　　　语句无法读取其它事务已修改但未提交的记录。

　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

• 3.四大冲突问题

1、脏读

某个事务读取的数据是另一个事务正在处理的数据。而另一个事务可能会回滚，造成第一个事务读取的数据是错误的。

2、不可重复读

在一个事务里两次读入数据，但另一个事务已经更改了第一个事务涉及到的数据，造成第一个事务读入旧数据。

3、幻读

幻读是指当事务不是独立执行时发生的一种现象。例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

4、更新丢失

多个事务同时读取某一数据，一个事务成功处理好了数据，被另一个事务写回原值，造成第一个事务更新丢失。

　　

• 4.MySQL引擎的区别？

mysql两种存储引擎：InnoDB和MyISAM

区别：（1.事务；2.锁；3.效率；4.查询/插入更新）

1).MyISAM是非事务安全型的，而InnoDB是事务安全型的。

2).MyISAM锁的粒度是表级，而InnoDB支持行级锁定。

3).MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

4).MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

5).InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。

　　

　　

• 5.数据库索引有哪些类型？

普通索引、唯一索引、主键索引、组合索引；

普通索引：没有任何限制；

唯一索引：索引列的值必须唯一，但允许有空值；

主键索引：特殊的唯一索引，不允许有空值；一个表只能有一个主键；

组合索引：多个字段组合作为索引；

• 6.数据库索引原理？

索引：数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

　　

7.数据库索引优缺点？

　　

索引的优点

大大加快数据的检索速度，这也是创建索引的最主要的原因；

加速表和表之间的连接；

在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；

 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

 索引的缺点

 ① 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大

 ② 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）

 ③ 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

8.B数，B+树，红黑树

B树是多路搜索树；对于M阶的B树，它的特点是：

　　1）每个节点存放M/2-1(上取整)到M-1个关键字；

　　2）根节点的儿子数是[2,M]，除根节点外非叶子节点的儿子数是[M/2，M]；

B树的搜索：从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点。

B树的特性：关键字分布在整棵树中；搜索性能等价于在关键字全集内做一次二分查找；

B+树是B树的变体，也是多路搜索树；B+树的特点是：

　　1）非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）

      2）所有关键字都在叶子结点出现；

　　3）所有的非叶子节点相当于是叶子节点的索引；

　　4）为所有叶子结点增加一个链指针；

B+的搜索与B树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；

红黑树：

　　性质 1：每个节点要么是红色，要么是黑色；

　　性质 2：根节点永远是黑色的；

　　性质 3：所有的叶节点都是空节点（即 null），并且是黑色的；

　　性质 4：每个红色节点的两个子节点都是黑色（从每个叶子到根的路径上不会有两个连续的红色节点）；

　　性质 5：从任一节点到其子树中每个叶子节点的路径都包含相同数量的黑色节点。

b+树与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因：

（一）更少的查找次数

平衡树查找操作的时间复杂度和树高 h 相关，O(h)=O(log_dN)，其中 d 为每个节点的出度。

红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，查找的次数也就更多。

（二）利用磁盘预读特性

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

B+树和B+树的区别：

       1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；

       2.只会在叶子结点命中；

       3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；

B+树相比B树的优点： 

(1) B+树改进了B树, 让内结点只作索引使用, 去掉了其中指向data的指针, 使得每个结点中能够存放更多的key, 这样访问叶节点的数据的磁盘读写次数就更低；

(2) 由于底部的叶子结点是链表形式, 因此也可以实现更方便的顺序遍历, 而B树则需要对整个树的每一层遍历，需要更多的磁盘读写；

9.乐观锁和悲观锁
　　

乐观锁：默认读数据的时候不会修改，所以不会上锁；

悲观锁：默认读数据的时候会修改，所以会上锁；

乐观锁适用于多读写比较少的情况，省去锁的开销，加大系统的吞吐量。

10.数据库优化

　　

分为：结构性优化和查询优化

结构优化：（建立索引）

要不要建立索引？

第一种情况：如果就几百条数据没必要建立索引，直接全表扫描即可；

第二种情况：索引的选择性（不重复的索引值的个数 与 表记录数的比值 ，白话就是说如果通过索引可以直接对应到一个记录就完美了，如果辛辛苦苦建索引，通过一个索引值搜到了几千记录，再扫描，那建索引没意义），选择性比较低就不用建了，浪费精力。

具体怎么建:https://www.cnblogs.com/xdyixia/p/9178957.html

查找优化：

（1.数据库：索引、分区；2.I/O：缓冲区；3.SQL语句，条目）

1）数据库方面：1.建立有效的索引；2.对数据库分区（如按时间分区）；

2）I/O方面：增加缓冲区；

3）SQL语句方面：1.优化SQL语句，减少比较次数；如避免select * from而使用具体的字段列表代替，避免在where中使用!=或<>操作符，否则将放弃索引而进行全表扫描； 2.限制返回条目limit；

sql优化一般步骤

sql优化一般步骤概要：

1. 通过 show status 命令了解各种sql的执行频率
2. 定位执行效率较低的sql语句
3. 通过explain分析低效sql的执行计划
4. 通过 show profile 分析sql
5. 通过trace分析 优化器 如何选择执行计划
6. 确定问题并采取相应的优化措施

11.锁

MySQL有三种锁的级别：页级、表级、行级。

 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

　　INNODB的行级锁有共享锁（S LOCK）和排他锁（X LOCK）两种。共享锁允许事物读一行记录，不允许任何线程对该行记录进行修改。排他锁允许当前事物删除或更新一行记录，其他线程不能操作该记录。

 

什么情况下会造成死锁?

什么是死锁？

死锁: 是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程。

表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

死锁的解决办法?

1.查出的线程杀死 kill

SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

2.设置锁的超时时间

Innodb 行锁的等待时间，单位秒。可在会话级别设置，RDS 实例该参数的默认值为 50（秒）。

生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值

该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：

set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒。

3.指定获取锁的顺序

12.MVCC

1.1 什么是MVCC

　　MVCC（Multiversion concurrency control ）是一种多版本并发控制机制。

1.2 MVCC是为了解决什么问题?

　　并发访问(读或写)数据库时，对正在事务内处理的数据做多版本的管理。以达到用来避免写操作的堵塞，从而引发读操作的并发问题。
　　大家都应该知道,锁机制可以控制并发操作,但是其系统开销较大，而MVCC可以在大多数情况下代替行级锁,使用MVCC，能降低其系统开销。

1.3 MVCC实现

　　MVCC是通过保存数据在某个时间点的快照来实现的。不同存储引擎的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。当我们创建表完成后，mysql会自动为每个表添加 数据版本号（最后更新数据的事务id）db_trx_id 删除版本号 db_roll_pt （数据删除的事务id） 事务id由mysql数据库自动生成，且递增。

13.MVCC+间隙锁

MySQL InnoDB支持三种行锁定方式：InnoDB的默认加锁方式是next-key 锁。

l   行锁（Record Lock）:锁直接加在索引记录上面，锁住的是key。

l   间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。

l   Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。 

默认情况下，InnoDB工作在可重复读(Repeatable Read)隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。 read committed隔离级别下

Gap Lock在InnoDB的唯一作用就是防止其他事务的插入操作，以此防止幻读的发生。

  Innodb自动使用间隙锁的条件：
（1）必须在Repeatable Read级别下
（2）检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加） 

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。 默认情况下，InnoDB工作在Repeatable Read隔离级别下，并且以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合，当对数据进行条件，范围检索时，对其范围内也许并存在的值进行加锁！当查询的索引含有唯一属性（唯一索引，主键索引）时，Innodb存储引擎会对next-key lock进行优化，将其降为record lock,即仅锁住索引本身，而不是范围！若是普通辅助索引，则会使用传统的next-key lock进行范围锁定！

要禁止间隙锁的话，可以把隔离级别降为Read Committed，或者开启参数innodb_locks_unsafe_for_binlog。

 

对于快照读来说，幻读的解决是依赖mvcc解决。而对于当前读则依赖于gap-lock解决。

14.快照读和当前读

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。 

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例： 

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select * from table where ?; 

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

15.关系型数据库和非关系型数据库

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组
织。
优点：
1、易于维护：都是使用表结构，格式一致；
2、使用方便：SQL语言通用，可用于复杂查询；
3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点：
1、读写性能比较差，尤其是海量数据的高效率读写；
2、固定的表结构，灵活度稍欠；
3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

16.范式

第一范式就是属性不可分割，每个字段都应该是不可再拆分的。比如一个字段是姓名（NAME），在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

第二范式就是要求表中要有主键，表中其他其他字段都依赖于主键，因此第二范式只要记住主键约束就好了。比如说有一个表是学生表，学生表中有一个值唯一的字段学号，那么学生表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思也就是相关的意思，因为学号的值是唯一的，因此就不会造成存储的信息对不上的问题，即学生001的姓名不会存到学生002那里去。

第三范式就是要求表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好了。比如说有一个表是学生表，学生表中有学号，姓名等字段，那如果要把他的系编号，系主任，系主任也存到这个学生表中，那就会造成数据大量的冗余，一是这些信息在系信息表中已存在，二是系中有1000个学生的话这些信息就要存1000遍。因此第三范式的做法是在学生表中增加一个系编号的字段（外键），与系信息表做关联。

17.触发器

1）触发器是一种特殊类型的存储过程，它由事件触发，而不是程序调用或手工启动，当数据库有特殊的操作时，这些操作由数据库中的事件来触发，自动完成这些SQL语句。

2）使用触发器可以用来保证数据的有效性和完整性，完成比约束更复杂的数据约束

3）触发器可以查询其他表，而且可以包含复杂的 SQL 语句。 它们主要用于强制服从复杂的业务规则或要求。 例如，您可以根据客户当前的帐户状态，控制是否允许插入新订单。

4）触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。

作用：

1）触发器可通过数据库中的相关表实现级联更改；通过级联引用完整性约束可以更有效地执行这些更改。

2）触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。

3）触发器还可以强制执行业务规则

3） 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

 

18.视图

视图是一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。

视图的优缺点

优点：

1对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。

2)用户通过简单的查询可以从复杂查询中得到结果。

3)维护数据的独立性，试图可从多个表检索数据。

4)对于相同的数据可产生不同的视图。

缺点：

性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，那么就无法更改数据

19.游标

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果中的所有行，但他一次只指向一行。

20.日志

① 错误日志

用来记录 MySQL 服务器运行过程中的错误信息,默认开启无法关闭.

复制环境下，从服务器进程的信息也会被记录进错误日志

 删除过期信息

默认情况下，错误日志是存储在数据库的数据文件目录中，名称为 hostname.err，其中 hostname 为服务器主机名。在 MySQL 5.5.7 之前，数据库管理员可以删除很长时间之前的错误日志，以节省服务器上的硬盘空间， MySQL 5.5.7 之后，服务器将关闭此项功能，只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的，命令为：mv hostname.err hostname.err.old mysqladmin flush-logs

 ② 查询日志

查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录，具体原因如下:

 insert 查询为了避免数据冲突，如果此前插入过数据，当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错；

update 时也会查询因为更新的时候很可能会更新某一块数据；

delete 查询，只删除符合条件的数据；

因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致 IO 非常大，影响 MySQL 性能，因此如果不是在调试环境下，是不建议开启查询日志功能的。

查询日志模式是关闭的，可以通过以下命令开启查询日志：set global generallog=1 set global logoutput=‘table’;

 general_log=1 为开启查询日志，0 为关闭查询日志，这个设置命令即时生效，不用重启 MySQL 服务器。

④ redo log（重做日志）一种缓存机制

是什么:为了最大程度的避免数据写入时，因为 IO 瓶颈造成的性能问题

 怎么办:先将数据写入内存中，再批量把内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故障带来的数据丢失问题，InnoDB 采用 redo log 来解决此问题。

Redo一般用于恢复已确认但未写入数据库的数据，记录的是数据修改后的值，例如：数据库忽然断电重启，数据库启动时一般要做一致性检查，会把已写到Redo的数据但未写入数据库的数据重做一遍。

介绍redo 日志是什么时，需要先回顾一下操作数据库数据的逻辑，一般是这样：首先，从数据库文件中找到记录，文件一般是按照一种特定的格式存放，比如页，然后加载整页数据到内存，在内存中进行数据的操作（脏页）然后将脏页同步回文件，一般不会立即将脏页刷回到磁盘，这样会产生大量的随机IO操作，性能低下，如果不立即刷回磁盘，那么当服务器挂掉后，存放在内存中的数据会丢失，造成数据的不一致性，也就无从持久化。为了解决这一矛盾，引入了redo日志。

⑤ undo log（回滚日志）类似ctrl+z 撤销

是什么

用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log 日志来实现回滚操作。

 怎么做:undo log 和 redo log 记录物理日志不一样，它是逻辑日志，可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。undo log 默认存放在共享表空间中，在 ySQL 5.6 中，undo log 的存放位置还可以通过变量 innodbundodirectory 来自定义存放目录，默认值为“.”表示 datadir 目录。

 ⑥ bin log（二进制日志）

是一个二进制文件，主要记录所有数据库表结构变更

bin log 中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。

 binlog 的作用如下：

 恢复（recovery）：某些数据的恢复需要二进制日志。比如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复；

复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为 slave 或者 standby）与一台 MySQL 数据库（一般称为 master 或者 primary）进行实时同步；

审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

binlog 对于事务存储引擎的崩溃恢复也有非常重要的作用

开启方法:

binlog 默认是关闭状态，可以在 MySQL 配置文件（my.cnf）中通过配置参数 log-bin = [base-name] 开启记录 binlog 日志，如果不指定 base-name，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，比如：mysql-bin.000001，所在目录为数据库所在目录（datadir）。

 通过以下命令来查询 binlog 是否开启：show variables like ‘log_%’;

 binlog 格式分为: STATEMENT、ROW 和 MIXED 三种：

 row：

基于行的模式，记录的是行的变化，很安全。但是binlog会比其他两种模式大很多，在一些大表中清除大量数据时在binlog中会生成很多条语句，可能导致从库延迟变大。

statement：

基于SQL语句的模式，某些语句和函数如UUID, LOAD DATA INFILE等在复制过程可能导致数据不一致甚至出错。

mixed：
混合模式，根据语句来选用是statement还是row模式。

21.集群、主从复制、读写分离

MySQL主从复制是其最重要的功能之一。主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

MySQL主从复制的两种情况：同步复制和异步复制，实际复制架构中大部分为异步复制。

复制的基本过程如下：

从服务器上面的IO进程连接上主服务器，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。

主服务器接收到来自从服务器的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给从服务器的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到主服务器端的bin-log文件的名称以及bin-log的位置。

从服务器的IO进程接收到信息后，将接收到的日志内容依次添加到从服务器端的relay-log文件的最末端，并将读取到的主服务器端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉主服务器“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”。

从服务器的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在主服务器端真实执行时候的那些可执行的内容，并在自身执行。

 因为用户的增多，数据的增多，单机的数据库往往支撑不住快速发展的业务，所以数据库集群就产生了！今天来说说读写分离的数据库集群方式！ 读写分离顾名思义就是读和写分离了，对应到数据库集群一般都是一主一从(一个主库，一个从库)或者一主多从(一个主库，多个从库)，业务服务器把需要写的操作都写到主数据库中，读的操作都去从库查询。主库会同步数据到从库保证数据的一致性。

这种集群方式的本质就是把访问的压力从主库转移到从库，也就是在单机数据库无法支撑并发读写的时候，并且读的请求很多的情况下适合这种读写分离的数据库集群。如果写的操作很多的话不适合这种集群方式，因为你的数据库压力还是在写操作上，即使主从了之后压力还是在主库上和单机区别就不大了。

在单机的情况下，一般我们做数据库优化都会加索引，但是加了索引对查询有优化，但是会影响写入，因为写入数据会更新索引。所以做了主从之后，我们可以单独的针对从库(读库)做索引上的优化，而主库(写库)可以减少索引而提高写的效率。

看起来还是很简单的，但是有两点要注意：主从同步延迟、分配机制的考虑；

 

22.union和union all

union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

union All：对两个结果集进行并集操作，包括重复行，不进行排序；

 

23.distinct和group by的区别查询结果去重

distinct的功能是去重，group by的功能是分组，通常结合聚合函数使用，去重并不是它的功能，只是附带能力。

group by 功能更强大一些，另外推荐使用group by。 
因为distinct会导致全表扫描，而group by如果索引建的 

恰当的话，会有性能上的提高。

那DISTINCT 和GROUP BY哪个效率更高？

DISTINCT操作只需要找出所有不同的值就可以了。而GROUP BY操作还要为其他聚集函数进行准备工作。从这一点上将，GROUP BY操作做的工作应该比DISTINCT所做的工作要多一些。

但实际上，GROUP BY 效率会更高点，为什么呢？对于DISTINCT操作，它会读取了所有记录，而GROUP BY需要读取的记录数量与分组的组数量一样多，也就是说比实际存在的记录数目要少很多。

 

24.char和varchar什么区别？

1.char的长度是不可变的，而varchar的长度是可变的。

定义一个char[10]和varchar[10]。

如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

2.char的存取数度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找。

char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。

varchar是以空间效率为首位。

3.char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。

varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

25.drop、truncate、 delete区别

最基本：

 drop直接删掉表。

 truncate删除表中数据，再插入时自增长id又从1开始。

 delete删除表中数据，可以加where字句。

（1） DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2） 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

（3） 一般而言，drop > truncate > delete

（4） 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

（5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

（6） truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7） delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

（8） truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚。

（9） 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

（10） Truncate table 表名 速度快,而且效率高,因为:?truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（12） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

 

25.分区、分表、分库、分片

分区

就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的

什么时候考虑使用分区？

一张表的查询速度已经慢到影响使用的时候。

sql经过优化

数据量大

表中的数据是分段的

对数据的操作往往只涉及一部分数据，而不是所有的数据

 分区解决的问题

主要可以提升查询效率

分区的实现方式（简单）

mysql5 开始支持分区功能

分表

就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。

分表解决的问题

分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了

• 查询一次的时间短了
• 数据分布在不同的文件，磁盘I/O性能提高
• 读写锁影响的数据量变小
• 插入数据库需要重新建立索引的数据减少

分表的实现方式（复杂）

需要业务系统配合迁移升级，工作量较大

 

 

什么时候考虑使用分库？

 

• 单台DB的存储空间不够
• 随着查询量的增加单台数据库服务器已经没办法支撑

 

分库解决的问题

 

其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。 

 

26.数据库连接池

• 为什么用数据库连接池？

1）资源重用。连接池里的连接可以重复使用，避免了频繁创建、释放连接引起的大量性能开销。

2）更快的系统响应速度 。数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而缩减了系统整体响应时间。

 

• 数据库连接池配置/驱动参数？如何防止失效？

数据库连接池？

c3p0, druid；DBCP；

DBCP配置参数：

driverClassName:连接数据库所用的 JDBC Driver Class, 

password: 登陆数据库所用的密码 
url: 连接数据库的 URL 
username: 登陆数据库所用的帐号 

 

初始化连接数量initialSize：连接池启动时创建的初始化连接数量；

最大连接数maxActive：如果超过maxActive，后面的连接请求加入到等待队列中；

最大空闲连接数maxIdle：连接池中允许保持空闲状态的最大连接数量，超过的空闲连接将被释放；

最小空闲连接数minIdle：连接池允许空闲的最小连接数量，小于则创建新的连接；

最大等待时间maxWait；当没有可用连接时，连接池等待连接被归还的最大时间，超过时间则抛出异常。

 

数据库连接池如何防止失效？

（设置参数，操作对象/空闲的时候进行验证）

testOnBorrow：取得对象时是否进行验证，检查对象是否有效，默认为false 
testOnReturn：返回对象时是否进行验证，检查对象是否有效，默认为false 

testWhileIdle：空闲时是否进行验证，检查对象是否有效，默认为false 

testOnReturn，testOnBorrow这两个参数为true，表示会在每次请求之前和之后进行连接池测试，如果连接失效，就会将这条连接对象销毁，创建一个新的连接对象。

testWhileIdle参数。这个参数为true时候，表示空闲时是进行验证，检查对象是否有效。然后minEvictableIdleTimeMillis配合timeBetweenEvictionRunsMillis，每过timeBetweenEvictionRunsMillis秒对连接池进行一次检测，将对象闲置时间超过minEvictableIdleTimeMillis秒的对象进行销毁，创建新的对象来取代。这样就能保证时刻都有正常的连接池对象存在。

 27.SQL注入

SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编程时的疏忽，通过SQL语句，实现无帐号登录，甚至篡改数据库。

解决方法：

1. 最佳方法：最简单的办法是杜绝SQL拼接,SQL注入攻击能得逞是因为在原有SQL语句中加入了新的逻辑，如果使用PreparedStatement来代替Statement来执行SQL语句，其后只是输入参数，SQL注入攻击手段将无效，采用预编译语句集，它内置了处理SQL注入的能力，只要使用它的setString方法传值即可，如下所示：预编译语句，绑定变量。使用预编译的SQL语句，SQL的语意不会变化，攻击者无法改变SQL的结构，即使攻击者插入了类似于’or ‘1’=’1的字符串，也只会将此字符串作为username查询。
2. 在WEB层我们可以过滤用户的输入来防止SQL注入比如用Filter来过滤全局的表单参数 。采用正则表达式将包含有 单引号(')，分号(;) 和 注释符号(--)的语句给替换掉来防止SQL注入

 

 28.内连接、外连接、左连接、右连接

1.内连接

          利用内连接可获取两表的公共部分的记录

2.左连接

        数据表A中的记录为主循环体，依次匹配数据表B中的记录，如果数据表A中连接字段Aid的值，在数据表B中没有Bnameid值与之对应，则右侧以null代替

3.右连接

         数据表B中的记录为主循环体，依次匹配数据表A中的记录，如果数据表B中连接字段Bnameid的值，在数据表A中没有Aid值与之对应，则左侧以null代替

4.全连接(Full JOIN)

         结果集：公共部分记录集C＋表A记录集A1+表B记录集B1