数据库总结与思考

一：MySQL数据库基本原理

顶层：

主要是客户端和连接服务，这层有连接池，这个类似于线程池，用来在并发的时候进行数据库的操作。

第二层：

封装层：当sql语句读取到Mysql中时，封装层用来拆分sql语句；

过滤器：将分段的语句过滤，如果有的地方不和语法就会报错返回；

优化器：一条语句究竟用什么索引，怎么用索引，怎么去执行，在这一层会给出执行计划；缓存：当一条语句查询完毕后结果会存放在缓存中，如果是同样的语句就不会再次执行了。

第三层：

执行引擎（存储引擎），负责MySQL中数据的存储和提取。

二：事务

1，ACID特性 实现原理

事务应该具有4个属性：原子性、一致性、隔离性、持久性。

原子性（Atomicity）：指整个数据库事务是不可分割的工作单位。只有事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个sql语句执行失败，那么已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态。 

 

一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束，也就是说在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏 。

 

隔离性（Isolation）：隔离性也叫做并发控制、可串行化或者锁。事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见，这通常使用锁来实现多个事务并发执行时，一个事务的执行不应影响其他事务的执行。 

 

持久性（Durability）：表示事务一旦提交了，其结果就是永久性的，也就是数据就已经写入到数据库了，如果发生了宕机等事故，数据库也能将数据恢复。

 

在事务的ACID特性中，一致性是事务的根本追求，而对数据库一致性的破坏主要来自两个方面：

• 事务的并发执行
• 事务故障或系统故障

如何避免数据库一致性被破坏

• 并发控制技术：保证了事务的隔离性，使数据库的一致性不会因为并发执行被操作
• 日志恢复技术：保证了事务的原子性，使数据库的一致性不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因系统崩溃而丢失，保证了事务的持久性

 

2，并发访问数据库是可能会出现的问题

脏读：一个事务读取到了另外一个事务没有提交的数据； 

不可重复读：在同一事务中，多次读同一数据，读到的数据不同（该数据被另一个已经提交的事务修改）； 比如：事务T1读取一行记录，紧接着事务T2修改（update）了T1刚才读取的那一行记录。

幻读：同一事务中，根据相同的查询条件查询，重新执行查询时，返回的记录与前一次查询记录不同； 比如：事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入或删除（insert，delete）一行记录。

总的来说，事务的隔离性主要用于解决事务并发安全问题。上面提到的脏读、不可重复读和幻读三个典型问题都是在事务并发的前提下发生的，不同的是三者的问题关注点略有不同。脏读关注的是事务读取了另一个事务未提交的数据；不可重复读关注的是同一事务中对同一个数据项多次读取的结果互不相同；幻读更侧重于数据记录的插入/删除问题，比如同一事务中对符合同一条件的数据记录的多次查询的结果互不相同。更进一步地说，不可重复读关注的是数据的更新带来的问题，幻读关注的是数据的增删带来的问题。

3，事务的隔离级别

1）Read uncommitted 读未提交

2）Read committed 读提交

加行级锁，读结束释放锁

3） Repeatable read 重复读（mySQL默认值）

加行级锁，事务结束时释放锁
　　在该级别下，读事务禁止写事务，但允许读事务，因此不会出现同一事务两次读到不同的数据的情况（不可重复读），且写事务禁止其他一切事务。

4） Serializable 序列化

加表级锁

　　该级别要求所有事务都必须串行执行，因此能避免一切因并发引起的问题，但效率很低。

 

三：索引

存储引擎 MyISAM InnoDB BDB MEMORY CSV  MERGB NDB 只有innodb bdb提供事务安全表

 

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

两种引擎的索引底层都是B+树实现的

MyISAM

MyISAM索引文件和数据文件是分离的；

MyISAM的主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，索引文件仅仅保存数据记录的地址（即非聚集索引）。

不支持事务处理。MyISAM不支持外键，而InnoDB支持外键。

InnoDB

InnoDB的数据文件本身就是索引文件，即它的主索引最底层是有数据记录而不是数据记录的地址（即聚集索引），所以innoDB必须得有主键；

InnoDB的辅助索引data域存储相应记录主键的值而不是地址，找到主键值再去主索引搜索。

应用：分库分表时涉及到：冷数据使用MyIsam 可以有更好的查询数据。活跃数据，可以使用Innodb,可以有更好的更新速度（毕竟它的数据文件就是索引文件所以更改应该快一些，还有Innodb支持事务，Mylsam不支持）。

请尽量在InnoDB上采用自增字段做主键。因为它的数据表和索引表是一起的有可能移动数据比较费资源。

通过上述的分析，基本上可以考虑使用InnoDB来替代MyISAM引擎了，原因是InnoDB自身很多良好的特点，比如事务支持、存储过程、视图、行级锁、外键等等。尤其在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。另外，必须需要注意的是，任何一种表都不是万能的，合适的才是最好的，才能最大的发挥MySQL的性能优势。如果是不复杂的、非关键的Web应用，还是可以继续考虑MyISAM的，这个具体情况具体考虑。

 

聚集索引和非聚集索引

聚集（clustered）索引，也叫聚簇索引。

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

单单从定义来看是不是显得有点抽象，打个比方，一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面，现在用一个简单的示意图来大概说明一下在数据库中的样子：

非聚集（unclustered）索引。

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

 

 

聚簇索引的顺序是数据的物理存储顺序；非聚簇索引与数据物理排列顺序无关。

聚簇索引是指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中，在该索引实现方式中b+Tree的叶子节点的data就是数据本身，key为主键。

在b+Tree的每个叶子节点增加一个指向相邻子节点的指针，就形成带有顺序访问指针的B+Tree,目的是为了优化区间访问的性能，这样可以顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，极大提高区域查询效率。

非聚簇索引   就是指B+树的叶子节点上的data，并不是数据本身，而是数据存放的地址，主索引的key是唯一的，主要用在Myisam 的存储引擎中

Myisam引擎使用的b+树作为索引结构，叶节点的data域存放的是数据记录的地址。

 

 

 

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B_TREE及其变种。索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

 

　　　　　　　　　　　

 

　　上图显示了一种索引方式。左边是数据库中的数据表，有col1和col2两个字段，一共有15条记录；右边是以col2列为索引列的B_TREE索引，每个节点包含索引的键值和对应数据表地址的指针，这样就可以都过B_TREE在 O(logn) 的时间复杂度内获取相应的数据，这样明显地加快了检索的速度。

 

1). 索引的底层实现原理和优化

 

　　在数据结构中，我们最为常见的搜索结构就是二叉搜索树和AVL树(高度平衡的二叉搜索树，为了提高二叉搜索树的效率，减少树的平均搜索长度)了。然而，无论二叉搜索树还是AVL树，当数据量比较大时，都会由于树的深度过大而造成I/O读写过于频繁，进而导致查询效率低下，因此对于索引而言，多叉树结构成为不二选择。特别地，B-Tree的各种操作能使B树保持较低的高度，从而保证高效的查找效率。

 

 

(1). B-Tree(平衡多路查找树)

 

　　B_TREE是一种平衡多路查找树，是一种动态查找效率很高的树形结构。B_TREE中所有结点的孩子结点的最大值称为B_TREE的阶，B_TREE的阶通常用m表示，简称为m叉树。一般来说，应该是m>=3。一颗m阶的B_TREE或是一颗空树，或者是满足下列条件的m叉树：

 

树中每个结点最多有m个孩子结点；

 

若根结点不是叶子节点，则根结点至少有2个孩子结点；

 

除根结点外，其它结点至少有(m/2的上界)个孩子结点；

 

结点的结构如下图所示，其中，n为结点中关键字个数，(m/2的上界)-1 <= n <= m-1；di(1<=i<=n)为该结点的n个关键字值的第i个，且di< d(i+1)；ci(0<=i<=n)为该结点孩子结点的指针，且ci所指向的节点的关键字均大于或等于di且小于d(i+1)；

 

　　　　　　　　　　

 

所有的叶结点都在同一层上，并且不带信息（可以看作是外部结点或查找失败的结点，实际上这些结点不存在，指向这些结点的指针为空）。

 

　　下图是一棵4阶B_TREE，4叉树结点的孩子结点的个数范围[2,4]。其中，有2个结点有4个孩子结点，有1个结点有3个孩子结点，有5个结点有2个孩子结点。

 

　　　　　　　　　　

 

　　B_TREE的查找类似二叉排序树的查找，所不同的是B-树每个结点上是多关键码的有序表，在到达某个结点时，先在有序表中查找，若找到，则查找成功；否则，到按照对应的指针信息指向的子树中去查找，当到达叶子结点时，则说明树中没有对应的关键码。由于B_TREE的高检索效率，B-树主要应用在文件系统和数据库中，对于存储在硬盘上的大型数据库文件，可以极大程度减少访问硬盘次数，大幅度提高数据检索效率。

 

(2). B+Tree ： InnoDB存储引擎的索引实现

 

　　B+Tree是应文件系统所需而产生的一种B_TREE树的变形树。一棵m阶的B+树和m阶的B_TREE的差异在于以下三点：

 

n 棵子树的结点中含有n个关键码；

 

所有的叶子结点中包含了全部关键码的信息，及指向含有这些关键码记录的指针，且叶子结点本身依关键码的大小自小而大的顺序链接；

 

非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键码。

 

　　下图为一棵3阶的B+树。通常在B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点。因此可以对B+树进行两种查找运算：一种是从最小关键字起顺序查找，另一种是从根节点开始，进行随机查找。

在B+树上进行随机查找、插入和删除的过程基本上与B-树类似。只是在查找时，若非终端结点上的关键码等于给定值，并不终止，而是继续向下直到叶子结点。因此，对于B+树，不管查找成功与否，每次查找都是走了一条从根到叶子结点的路径。

 

　　　　　　　　　　

 

(3). 为什么说B+-tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？

 

B+tree的磁盘读写代价更低：B+tree的内部结点并没有指向关键字具体信息的指针(红色部分)，因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；

 

B+tree的查询效率更加稳定：由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

 

数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

 

(4). 文件索引和数据库索引为什么使用B+树?

 

　　文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。而所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此B+树相比B树更为合适。数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，而红黑树这种结构，高度明显要深的多，并且由于逻辑上很近的节点(父子)物理上可能很远，无法利用局部性。最重要的是，B+树还有一个最大的好处：方便扫库。B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持，这是数据库选用B+树的最主要原因。

B+树中一个节点到底多大合适？

B+树中一个节点为一页或页的倍数最为合适。

 

为啥？

 

因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费。

 

如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费。

 

所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

 

2). 索引的优点

 

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

 

加速表和表之间的连接；

 

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

 

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

 

3). 什么情况下设置了索引但无法使用？

 

以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；

 

OR语句前后没有同时使用索引；

 

数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；

 

对于多列索引，必须满足 最左匹配原则 (eg：多列索引col1、col2和col3，则 索引生效的情形包括 col1或col1，col2或col1，col2，col3)。

 

4). 什么样的字段适合创建索引？

 

数据量大的，经常进行查询操作的表要建立索引。

 

用于排序的字段可以添加索引，用于分组的字段应当视情况看是否需要添加索引。

 

 

表与表连接用于多表联合查询的约束条件的字段应当建立索引。

 

5). 创建索引时需要注意什么？

 

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；

 

取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；

 

索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

 

6). 索引的缺点

 

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

 

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

 

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

7). 索引的分类

 

普通索引和唯一性索引：索引列的值的唯一性

 

单个索引和复合索引：索引列所包含的列数

 

聚簇索引与非聚簇索引：聚簇索引按照数据的物理存储进行划分的。对于一堆记录来说，使用聚集索引就是对这堆记录进行堆划分，即主要描述的是物理上的存储。正是因为这种划分方法，导致聚簇索引必须是唯一的。聚集索引可以帮助把很大的范围，迅速减小范围。但是查找该记录，就要从这个小范围中Scan了；而非聚集索引是把一个很大的范围，转换成一个小的地图，然后你需要在这个小地图中找你要寻找的信息的位置，最后通过这个位置，再去找你所需要的记录。

 

8). 主键、自增主键、主键索引与唯一索引概念区别

 

主键：指字段 唯一、不为空值 的列；

 

主键索引：指的就是主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引；

 

自增主键：字段类型为数字、自增、并且是主键；

 

唯一索引：索引列的值必须唯一，但允许有空值。主键是唯一索引，这样说没错；但反过来说，唯一索引也是主键就错误了，因为唯一索引允许空值，主键不允许有空值，所以不能说唯一索引也是主键。

 

9). 主键就是聚集索引吗？主键和索引有什么区别？

 

　　主键是一种特殊的唯一性索引，其可以是聚集索引，也可以是非聚集索引。在SQLServer中，主键的创建必须依赖于索引，默认创建的是聚集索引，但也可以显式指定为非聚集索引。InnoDB作为MySQL存储引擎时，默认按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。所以，对于聚集索引来说，你创建主键的时候，自动就创建了主键的聚集索引。

 

最左原则

MySql索引底层采用B+树的数据结构，仅在叶子节点存储数据，并且从左到右串联起来便于范围查询。这样的结构导致复合索引的查询符合“最左原则”，也就是当建立（a,b,c）复合索引时，查询条件中必须有a才能走索引，a,ab,ac,abc均;但是b,bc,c都不能走索引。

优: select * from test order by a

差: select * from test order by b

差: select * from test order by c

索引优化策略有哪些？

1不要在索引列上进行运算或使用函数

在列上进行运算或使用函数会使索引失效，从而进行全表扫描。如下面例子在publish_time，id列上分别加上索引，publish_time为datetime类型，id为int类型

2 前导模糊查询不会使用索引

-- 全表扫描

select * from article where author like '%李'

%李,%李%都会导致全表扫描，非前导模糊查询可以使用索引

-- 走索引

select * from article where author like '李%'

3联合索引最左前缀原则

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

 

将区分度最高的字段放在最左边

 

当不需要考虑排序和分组时，将区分度最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找

 

如果在a b列上建立联合索引，该如何建立，才能使查询效率最高

4 经常更改，区分度不高的列上不宜加索引

更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。

 

“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

 

一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

 

5明确知道只会返回一条记录，可以加limit1

当查询确定只有一条记录时，可以加liimit1，让MySQL停止游标移动，提高查询效率

6 使用唯一索引，区分度要高

7尽量使用短索引，查询更快，索引高速缓存中也能容纳更多键值

 

四：数据库优化

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

结构优化：（建立索引）

要不要建立索引？

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

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

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

查找优化：

在MySQL中可以使用EXPLAIN查看SQL执行计划

用法：EXPLAIN SELECT * FROM tb_item

可以看其中的一些属性例如：type属性：连接类型的好坏；key的长度；Rows属性：MySQL认为它执行查询时必须检查的行数。

1）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2）在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效。

3）最左前缀原理：MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。应该索引字段尽可能短的情况下，选择性尽量高，他俩之间平衡。

4）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null    

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：    select id from t where num=0  

5）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

6）or 的查询尽量用 union 代替 （Innodb）；

SELECT * FROM tb_item WHERE id = 536563 or cid = 76

改用SELECT * FROM tb_item WHERE id = 536563 UNION SELECT * FROM tb_item WHERE cid = 76

7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100    

应改为:    select id from t where num=100*2

数据库优化的思路

这个我借鉴了慕课上关于数据库优化的课程。

1.SQL语句优化

1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3）很多时候用 exists 代替 in 是一个好的选择
4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

2.索引优化

看上文索引

3.数据库结构优化

1）范式优化： 比如消除冗余（节省空间。。） 2）反范式优化：比如适当加冗余等（减少join） 3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
4）拆分其实又分垂直拆分和水平拆分： 案例： 简单购物系统暂设涉及如下表： 1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表 （数据量100w，且有增长趋势） 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分： 解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分： 解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺
方案： 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)

 

 

五：锁

Mvcc

 

1.MVCC(多版本并发控制)，是一种不利用锁机制实现的隔离级别，主要实现了在保证数据的一致性的前提下，实现了读写的并行。

　　之前提到的读已提交，虽然实现了读写并行，读未提交实现了写读并行，读写并行；但是两种隔离级别均没有保证数据的一致性，分别出现不可重复读和脏读。

　　2.mvcc的原理是给每一数据的更新都有一个版本号。当写事务正在进行时，此时过来一个读事务，读事务会首先生成一个版本号，即该事物想读取哪一个版本的数据。

　　　然后，写事务更新数据，读事务读取之前相应版本的数据，而保证了不出现不可重复读和脏读的情况。

　　3.之前提到，保证数据一致性有两种方式，要么将读事务读取视点1的数据，要么读取视点2的数据。在mvcc中保证数据一致性的方法选择的是，读取视点1的数据，即读取的          是写事务开始之前的旧版本数据。

 

MVCC (Multiversion Concurrency Control)，即多版本并发控制技术,它使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制，取而代之的是把数据库的行锁与行的多个版本结合起来，只需要很小的开销,就可以实现非锁定读，MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销.

读锁：也叫共享锁

写锁：又称排他锁

表锁：操作对象是数据表。Mysql大多数锁策略都支持(常见mysql innodb)，是系统开销最低但并发性最低的一个锁策略。事务t对整个表加读锁，则其他事务可读不可写，若加写锁，则其他事务增删改都不行。

行级锁：操作对象是数据表中的一行。是MVCC技术用的比较多的，但在MYISAM用不了，行级锁用mysql的储存引擎实现而不是mysql服务器。但行级锁对系统开销较大，处理高并发较好。

死锁：两个或多个事务在同一资源上相互占用并请求锁定对方占用的资源，从而导致恶性循环的现象。

对于死锁的处理：MySQL的部分存储引擎能够检测到死锁的循环依赖并产生相应的错误。InnoDB引擎解决的死锁的方案是将持有最少写锁的事务进行回滚。

六：数据库SQL

 

 

 

Latin1是ISO-8859-1的别名

注意：

1、存储引擎必须使用InnoDB引擎；

2、外键必须建立索引；

3、外键绑定关系这里使用了“ ON DELETE CASCADE ” “ON UPDATE CASCADE”，意思是如果外键对应数据被删除或者更新时，将关联数据完全删除或者相应地更新

七：NoSQL

NoSQL表示的是一类非关系型的数据库，其中比较有代表性的是redis。

redis是一种kv的数据存储系统

特点：

纯内存操作

多路复用IO

单线程避免锁

内部数据结构是hash，而不是B树

数据结构简单，hash，string，list，set，zset等

作为缓存的功能：

redis可以做很多功能，主要有数据库、缓存、消息队列等。对于缓存而言做缓存服务器，将耗时久的语句结果放入redis中，下次查询直接返回结果。高并发，大量数据访问数据库时可能出现异常，用redis缓存先接受访问请求进行处理，预防并发问题。

 

l 没有表的概念，采用时效限制（TimeToLive,TTL）的key-value存放数据，分为16个区（类似命名空间）查key即可

l 过期策略：定期抽样删除、惰性删除

l 双写一致性：可以降低发生概率，但是完全避免很难。如果有强烈的一致性要求则不能使用缓存

缓存穿透：

大量访问缓存中不存在的数据，导致直接连到数据库上出现异常，解决方法：

 

l 利用互斥锁，缓存失效的时候，先去获得锁，得到锁了，再去请求数据库。没得到锁，则休眠一段时间重试。采用异步更新策略，无论key是否取到值，都直接返回。

l value值中维护一个缓存失效时间，缓存如果过期，异步起一个线程去读数据库，更新缓存。需要做缓存预热(项目启动前，先加载缓存)操作

l 提供一个能迅速判断请求是否有效的拦截机制，比如，利用布隆过滤器，内部维护一系列合法有效的key。迅速判断出，请求所携带的Key是否合法有效。如果不合法，则直接返回

缓存雪崩：

因为失效时间相同，缓存大面积同时失效导致直接连到数据库上出现异常，解决方法：

 

l 给缓存的失效时间，加上一个随机值，避免集体失效

l 使用互斥锁，但是该方案吞吐量明显下降了

l 双缓存。我们有两个缓存，缓存A和缓存B。缓存A的失效时间为20分钟，缓存B不设失效时间

 

RDB

一种是RDB持久化，原理是将Reids在内存中的数据库记录定时dump到磁盘上的RDB持久化。

 

一旦采用该方式，那么你的整个Redis数据库将只包含一个文件，这对于文件备份而言是非常完美的。比如，你可能打算每个小时归档一次最近24小时的数据，同时还要每天归档一次最近30天的数据。通过这样的备份策略，一旦系统出现灾难性故障，我们可以非常容易的进行恢复。

 

对于灾难恢复而言，RDB是非常不错的选择。因为我们可以非常轻松的将一个单独的文件压缩后再转移到其它存储介质上。

 

性能最大化。对于Redis的服务进程而言，在开始持久化时，它唯一需要做的只是fork出子进程，之后再由子进程完成这些持久化的工作，这样就可以极大的避免服务进程执行IO操作了。

 

相比于AOF机制，如果数据集很大，RDB的启动效率会更高。

 

AOF

另外一种是AOF（append only file）持久化，原理是将Reids的操作日志以追加的方式写入文件。

 

如果你想保证数据的高可用性，即最大限度的避免数据丢失，那么RDB将不是一个很好的选择。因为系统一旦在定时持久化之前出现宕机现象，此前没有来得及写入磁盘的数据都将丢失。

 

该机制可以带来更高的数据安全性，即数据持久性。Redis中提供了3中同步策略，即每秒同步、每修改同步和不同步。事实上，每秒同步也是异步完成的，其效率也是非常高的，所差的是一旦系统出现宕机现象，那么这一秒钟之内修改的数据将会丢失。而每修改同步，我们可以将其视为同步持久化，即每次发生的数据变化都会被立即记录到磁盘中。可以预见，这种方式在效率上是最低的。至于无同步，无需多言，我想大家都能正确的理解它。

 

缓存算法

主要有FIFO，LRU，LFU等几种算法 - FIFO，先进先出，先进入缓存的先淘汰 - LRU，最近最少使用，最少使用的缓存先淘汰 - LFU，最近最不常用，定期之内，最不常用的先淘汰

 

缓存预热：新的缓存系统没有任何缓存数据，在缓存重建数据的过程中，系统性能和数据库负载都不太好，所以最好是在系统上线之前就把要缓存的热点数据加载到缓存中

 

缓存热备：当一台缓存服务器不可用时能实时切换到备用缓存服务器，不影响缓存使用。集群模式下，每个主节点都会有一个或多个从节点来当备用，一旦主节点挂点，从节点立即充当主节点使用

 

缓存更新：除了redis自带的缓存更新方法，为了降低内存使用我们可以进行其他更新方法：

 

定时清理过期的缓存，因为是惰性删除和定期抽样删除，定时清理还是有必要的

 

当用户请求时判断缓存是否过期，过期就去底层更新数据并更新缓存

 

缓存降级：当访问量剧增、服务出现问题（如响应时间慢或不响应）或非核心服务影响到核心流程的性能时，仍然需要保证服务还是可用的，即使是有损服务。系统可以根据一些关键数据进行自动降级，也可以配置开关实现人工降级

 

八， 范式

第一范式

定义：关系中的每个属性都不可再分。

 

理解：基本的数据库都满足第一范式。

 

第二范式

定义：关系中不存在非主属性对于主属性的部分函数依赖。或者理解为：非主属性必须对主属性完全依赖。

 

举例：比如主属性为：A、B，而C作为一个非主属性，只与A有依赖关系，C与B无关，那么此时C对于A、B只存在部分依赖，此时不符合第二范式。

 

理解：符合第二范式的关系能够保证每一个非主属性都严格的依赖主属性。就是任何一个非主属性都必须和所有的主属性有关。通俗的说，这张表中的内容都和主键有关，主键是最核心的部分。如果存在冗余的字段，将在第二范式中被剔除。

 

作用：

1）消除冗余，只有完全依赖的才能留下，依赖性不强的字段都删掉了。

2）降低数据间的耦合，对一类事物的修改不会影响其他事物。

 

第三范式

定义：关系中不存在非主属性对于非主属性的任何函数依赖。

 

举例：比如主属性为：A，而B、C作为非主属性，存在“B依赖A，C完全依赖或部分依赖B”的情况。那么此时C对于B存在依赖情况，此时不符合第三范式。

 

理解：第三范式更加严格，第二范式说普通的属性必须和主属性（如果主属性是多个，那么就必须和所有的主属性（或主属性联合起来的概念）有关）有关，但是没有规定这些普通属性间有什么要求。第三范式说，如果普通属性之间也有关系，比如C会被B决定或影响，那么C就应该被踢出去，单独和B（或含有B和其他的属性）建另外的一张表。

 

作用：

1）消除冗余，往往C依赖于B，当有大量B的时候C也都是大量一样的数据，浪费。

2）降低数据间的耦合，对一类事物的修改不会影响其他事物。

 

BC范式

定义：主属性中不存在某些属性部分依赖另外一些属性。

 

举例：比如主属性为：A、B、C。存在B依赖于A、C的情况。此时不符合BC范式。

 

理解：看了第二第三范式你会发现一个问题，如果我把主属性也就是主键定义的很大，基本都能满足这两个范式，因为第二第三范式是针对非主属性的规定。范式也想到了这个问题，就出现了BC范式，通俗地说，BC范式就是告诉我们“别吧主键定太多，主键也要符合规矩啊”。主键内不能存在依赖的情况。

 

作用：1）降低数据间的耦合，对一类事物的修改不会影响其他事物。

九，MySQL中的悲观锁与乐观锁的实现

 

　　悲观锁与乐观锁是两种常见的资源并发锁设计思路，也是并发编程中一个非常基础的概念。

 

(1). 悲观锁

 

　　悲观锁的特点是先获取锁，再进行业务操作，即“悲观”的认为所有的操作均会导致并发安全问题，因此要先确保获取锁成功再进行业务操作。通常来讲，在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。当数据库执行select … for update时会获取被select中的数据行的行锁，因此其他并发执行的select … for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

 

　　这里需要特别注意的是，不同的数据库对select… for update的实现和支持都是有所区别的，例如oracle支持select for update no wait，表示如果拿不到锁立刻报错，而不是等待，mysql就没有no wait这个选项。另外，mysql还有个问题是: select… for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此，如果在mysql中用悲观锁务必要确定使用了索引，而不是全表扫描。

 

(2). 乐观锁

 

　　乐观锁的特点先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过，若未被更新过，则更新成功；否则，失败重试。乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号或者时间戳，然后按照如下方式实现：

 

1. SELECT data AS old_data, version AS old_version FROM …;

2. 根据获取的数据进行业务操作，得到new_data和new_version

3. UPDATE SET data = new_data, version = new_version WHERE version = old_version

if (updated row > 0) {

    // 乐观锁获取成功，操作完成

} else {

    // 乐观锁获取失败，回滚并重试

}

乐观锁是否在事务中其实都是无所谓的，其底层机制是这样：在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这其间没有发生并发的修改。如果更新失败，即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。

 

(3). 悲观锁与乐观锁的应用场景

 

一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

　悲观锁和乐观锁都是一种解决并发控制问题的思想。特别地，在数据库并发控制方面，悲观锁与乐观锁有以下几点区别：

 

思想：在事务并发环境中，乐观锁假设不会发生并发冲突，因此只在提交操作时检查是否违反数据完整性；而悲观锁假定会发生并发冲突，会屏蔽一切可能违反数据完整性的操作。

 

实现：悲观锁是利用数据库本身提供的锁机制来实现的；而乐观锁则是通过记录数据版本实现的；

 

应用场景：悲观锁主要用于数据争用激烈的环境或者发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中；而乐观锁主要应用于并发可能性并不太大、数据竞争不激烈的环境中，这时乐观锁带来的性能消耗是非常小的；

 

脏读： 乐观锁不能解决脏读问题，而悲观锁则可以。

 

　　总的来说，悲观锁相对乐观锁更安全一些，但是开销也更大，甚至可能出现数据库死锁的情况，建议只在乐观锁无法工作时才使用。

 

 

 

十，什么是触发器？

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

 

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

 

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

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

 

作用：

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

 

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

 

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

 

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

分类：

根据SQL语句的不同，触发器可分为数据操作语言DML触发器和数据定义语言DLL触发器。通常说的触发器就是DML触发器。

 

1）DML触发器是当数据库服务器发生数据操作语言事件时执行的存储过程，有After 和 Instead Of 这两种触发器。

 

             1. After 触发器被激活触发是在记录改变之后进行的一种触发器（只有执行某一操作之后才触发）。只能定义在表上。

 

             2. Instead Of 触发器是在记录变更之前，去执行触发器本身所定义的操作，而不是执行原来SQL语句里的操作。既可以               在表上定义instead of触发器，也可以在视图上定义。

 

当INSERT、UPDATE 或 DELETE 语句修改指定表或视图中的数据时，可以使用 DML 触发器。 DML 触发器在 INSERT、 UPDATE 和 DELETE 语句上操作，并且有助于在表或视图中修改数据时强制业务规则，扩展数据完整性。

 

2）DLL 触发器是在响应数据定义语言事件时执行的存储过程。DDL 触发器激发存储过程以响应各种 DDL 语句，这些语句主要以CREATE、ALTER 和 DROP 开头。 DDL 触发器可用于管理任务，例如审核和控制数据库操作。

 

           DDL 触发器将激发存储过程以响应事件。但与 DML 触发器不同的是，它们不会为响应针对表或视图的 UPDATE、INSERT 或 DELETE 语句而激发。相反，它们将为了响应各种数据定义语言 (DDL) 事件而激发。这些事件主要与以关键字 CREATE、ALTER 和 DROP 开头的 Transact-SQL 语句对应。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。        

 

DDL 触发器使用场合：

 

要防止对数据库架构进行某些更改。

希望数据库中发生某种情况以响应数据库架构中的更改。

要记录数据库架构中的更改或事件。

十一，什么叫视图？游标是什么？

 

　　视图是一种基于数据表的一种虚表

 

（1）视图是一种虚表

（2）视图建立在已有表的基础上, 视图赖以建立的这些表称为基表

（3）向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句

（4）视图向用户提供基表数据的另一种表现形式

（5）视图没有存储真正的数据，真正的数据还是存储在基表中

（6）程序员虽然操作的是视图，但最终视图还会转成操作基表

（7）一个基表可以有0个或多个视图

有的时候，我们可能只关系一张数据表中的某些字段，而另外的一些人只关系同一张数据表的某些字段…

 

那么把全部的字段都都显示给他们看，这是不合理的。

我们应该做到：他们想看到什么样的数据，我们就给他们什么样的数据…一方面就能够让他们只关注自己的数据，另一方面，我们也保证数据表一些保密的数据不会泄露出来…

我们在查询数据的时候，常常需要编写非常长的SQL语句，几乎每次都要写很长很长….上面已经说了，视图就是基于查询的一种虚表，也就是说，视图可以将查询出来的数据进行封装。。。那么我们在使用的时候就会变得非常方便…

值得注意的是：使用视图可以让我们专注与逻辑，但不提高查询效率

 

在操作mysql的时候，我们知道MySQL检索操作返回一组称为结果集的行。这组返回的行都是与 SQL语句相匹配的行（零行或多行）。使用简单的 SELECT语句，例如，没有办法得到第一行、下一行或前 10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们）。有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

十二，简单说一说drop、delete与truncate的区别

 

　　SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

 

Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；

 

Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；

 

Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

 

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

十三，SQL注入简介

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

比如在一个登录界面，要求输入用户名和密码：

可以这样输入实现免帐号登录：

用户名：

'or1=1 –

密 码点登陆,如若没有做特殊处理,那么这个非法用户就很得意的登陆进去了.(当然现在的有些语言的数据库API已经处理了这些问题)

这是为什么呢? 下面我们分析一下：

从理论上说，后台认证程序中会有如下的SQL语句：

SQL

String sql ="select * from user_table where username=' "+userName+" ' and password=' "+password+" '";

当输入了上面的用户名和密码，上面的SQL语句变成：

SQL

SELECT*FROM user_table WHERE username='’or1=1-- and password='’

分析SQL语句：

条件后面username=”or 1=1 用户名等于 ” 或1=1 那么这个条件一定会成功；

然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都能正确执行，用户轻易骗过系统，获取合法身份。

这还是比较温柔的，如果是执行

SQL

SELECT*FROM user_table WHEREusername='';DROPDATABASE(DB Name)--' and password=''

 

 

 

解决方法

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

采用正则表达式将包含有 单引号(')，分号(;) 和 注释符号(--)的语句给替换掉来防止SQL注入

import java.util.regex.*;

正则表达式：

private String CHECKSQL = “^(.+)\\sand\\s(.+)|(.+)\\sor(.+)\\s$”;

判断是否匹配：

Pattern.matches(CHECKSQL,targerStr);

下面是具体的正则表达式：

检测SQL meta-characters的正则表达式 ：

/(\%27)|(\’)|(\-\-)|(\%23)|(#)/ix

修正检测SQL meta-characters的正则表达式 ：/((\%3D)|(=))[^\n]*((\%27)|(\’)|(\-\-)|(\%3B)|(:))/i

典型的SQL 注入攻击的正则表达式 ：/\w*((\%27)|(\’))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix

检测SQL注入，UNION查询关键字的正则表达式 ：/((\%27)|(\’))union/ix(\%27)|(\’)

检测MS SQL Server SQL注入攻击的正则表达式：

/exec(\s|\+)+(s|x)p\w+/ix

等等…..

十四 内连接，外连接，全连接

一，简介

       在关系型数据库连接查询中有以下几种查询方式：1，内连接；2，左连接；3，右连接；4，全连接。下面说明各种连接查询方式的使用及区别。

二，使用及区别

 

       1.内连接

          利用内连接可获取两表的公共部分的记录，即图中的数据集C, 语句如下：Select * from A JOIN B ON A.Aid=B.Bnameid 运行结果如下图4所示：其实select * from A,B where A.Aid=B.Bnameid与Select * from A inner(可省略) JOIN B ON A.Aid=B.Bnameid的运行结果是一样的。  

      2.左连接

        数据表A中的记录为主循环体，依次匹配数据表B中的记录，如果数据表A中连接字段Aid的值，在数据表B中没有Bnameid值与之对应，则右侧以null代替。结果集：公共部分记录集C＋表A记录集A1。语句如下：select * from A Left JOIN B ON A.Aid=B.Bnameid

      3.右连接

         数据表B中的记录为主循环体，依次匹配数据表A中的记录，如果数据表B中连接字段Bnameid的值，在数据表A中没有Aid值与之对应，则左侧以null代替。结果集：公共部分记录集C＋表B记录集B1。语句如下：select * from A Right JOIN B ON A.Aid=B.Bnameid

      4.全连接(Full JOIN)

         结果集：公共部分记录集C＋表A记录集A1+表B记录集B1。语句如下：select * from A FULL JOIN  B  ON A.Aid=B.Bnameid。

左连接：左边有的，右边没有的为null

右连接：左边没有的，右边有的为null

内连接：显示左边右边共有的

内连接:仅选出两张表中互相匹配的记录

左连接：包含所有左边表的记录甚至是右边表中没有和她匹配的记录

右连接：包含所有右边表的记录甚至是左边表中没有和她匹配的记录

 

 

On和where的区别

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

 

在使用left jion时，on和where条件的区别如下：

 

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

 

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

 

总结：

 

1. 对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面

2. 对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面。

十五 InnoDB  MyISAM

1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

3. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

如何选择：

1. 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

十六 超键、候选键、主键、外键分别是什么？

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键(候选码)：是最小超键，即没有冗余元素的超键。

主键(主码)：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称此表的外键。

候选码和主码：

 

例子：邮寄地址（城市名，街道名，邮政编码，单位名，收件人）

 

它有两个候选键:{城市名，街道名} 和 {街道名，邮政编码}

如果我选取{城市名，街道名}作为唯一标识实体的属性，那么{城市名，街道名} 就是主码(主键)

 

 

MySQL会不会mvcc,知不知道间隙锁，怎么解决幻读，怎么产生死锁，undolog,redo,binlog,分库分表读写分离会吗

mysql的那几个日志很重要以及分库分表，主从同步

十七 分库分表

分库分表是业务发展到一定阶段、数据积累到一定量级而衍生出来的解决方案。当DB的数据量级到达一个阶段，写入和读取的速度会出现瓶颈，即使是有索引，索引也会变得很大，而且数据库的物理文件会使备份和恢复等操作变的很困难。这个时候由于DB的瓶颈已经严重危害到了业务，最有效的解决方案莫过于DB的分库分表了。

分库

根据业务划分

说到分库，笔者这里想多啰嗦一句：推荐大家根据业务来进行划分，一个系统的好坏，业务的边界划分起到举足轻重的作用。业务按照规则划分好边界，每个业务对应的数据库自然而然就诞生了，不要站在数据库的层面上去给业务分库。有的Leader会有这样的行为：某个表的数据量太大，分配到单独的一个库，结果导致的结果就是很多SQL语句必须跨库Join。

具体的业务怎么划分呢？每个公司的业务形态不同，划分的维度就会不同。举一个简单的例子：一个典型的电商系统根据业务可划分为商品、订单，这也是许多公司的典型业务划分，但是我司根据自己的业务规则，划分为商品、订单、支付。因为支付系统在我司是一个独立的业务，不但包含了订单的支付，还包含了很多其他的支付场景。根据业务上的划分，DB的层面就出现了商品DB、订单DB和支付DB。

同一业务横向划分

除了根据业务垂直切分的策略之外，还有另外一种常用的分库方案。如果某个具体业务数据量比较大，可以把这业务的数据库根据某种规则来进行横向切分。比如用户信息的业务，当用户量达到一定量级，有些公司会把用户信息拆分到多个数据库——这和拆分到多个表有什么区别呢？

如果把用户信息横切到同一个数据库的多个表，如果这些表位于一个物理磁盘上，对于提高这个业务的写入和读取IO最大值并没有什么用处，但是如果分配到多个服务器上，意味着这个业务整体的最大IO得到了提升，在一定程度上要比拆表效果要好。当然如果用到了表分区，每个分区散落在不同的物理磁盘上，也不一定比分库方式差。

把某个业务的DB按照规则横向切分之后，当然也会引入新的问题。切分的规则在很多情况下用的最多的就是哈希取余的方式了，有时间咱们在讨论。

分库引入复杂性

我在上文提到过，分库分表并非是银弹，任何一种解决方案能解决一个问题，但是有可能会引入其他问题。世界是公平的，计算机世界亦如此，那分库会引入哪些问题呢？

在执行了分库之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们多数情况下无法Join位于不同分库的表（因为多数公司都明令禁止跨库SQL），结果原本一次查询能够完成的业务，可能需要多次查询才能完成。原来在单体DB环境下，可以用DB的事务来保证一些操作的原子操作，但是在分散到多个数据库的情况下，统一管理这些操作变的困难。虽然一些大厂提供的也有跨库的事务解决方案，但是性能上实在是差强人意，所以在很多情况下并不实用。比如上边提到的商品库存支付，在单体应用的情况下，三个业务在同一个数据库，当发生支付业务，更改商品库存和更新订单状态这两个操作可以利用数据库提供的事物来完成，而且性能在可接受范围之内，如果这三个业务分布在不同的数据库，有几率会发生只执行其中一个操作的情况发生，其实这也是分布式事物要解决的问题。在很多情况下，分布式事物是无法避免的，根据业务综合情况适当采用分布式事物也是一种有效的解决方案，最坏的情况下，可能需要人工介入了。分库对于DBA来说意味着工作量的成倍增加，原来只需要管理一个DB，现在却要管理N个DB，而且每个DB都需要备份、监控，甚至做高可用、扩展等工作。原来可能只需要一个DBA管理人员，分库之后可能会需要两个甚至三个，导致了公司在人力投入上的加大。

为什么分表？

在正式开始之前还是要强调一点，你的数据表是否应该分需要综合考虑很多因素，比如业务的数据量是否到达了必须要切分的数量级？是否可以有其他方案来解决当前问题？

我不止一次见过，有的Leader在不考虑综合情况下，盲目地进行表拆分业务，导致的情况就是大家不停地加班，连续几周996，难道Leader你不掉头发吗？还有的架构师在一个小小业务初期就进行表拆分，大家为了配合你也是马不停蹄的加班赶进度，上线之后反而发现业务数据量很小，但是代码上却被分表策略牵制了太多。拆表引起的问题在特定的场景下，有时候代价真的很大。

数据库表的拆分解决的问题主要是存储和性能问题，MySQL在单表数据量达到一定量级后，性能会急剧下降，相比较于SQLServer和Oracle这些收费DB来说，MySQL在某些方面还是处于弱势，但是表的拆分这个策略却适用于几乎所有的关系型数据库。

数据库进行表拆分不要太盲目。

分表策略

表的拆分和数据库的拆分有相似之处，但是拆分的规则也有不同。以下的拆分规则针对的是拆分一个表。

横向切分

横向切分是诸多业务中最常用的切分方式，本质是把一个表中的数据行按照规则分散到多个表中，比如最常见的按照ID范围，按照业务主键的哈希值等。至于表数据到达什么数量级之后进行切分，这和表中存的数据格式有关，比如一个表只有几列的int字段肯定要比几列text类型的表存储的极限要高，姑且认为这个极限是1000万吧。但是作为一个系统的负责人或者架构师来说，当表的数据量级到达千万级别要引起重视，因为这是一个系统性能瓶颈的隐患。

相对于数据表的横向切分，在符合业务优化的场景下我更倾向于做表分区，按照规则把不同的分区分配到不同的物理磁盘，这样的话，业务里的SQL语句几乎可以不用改动。我司的一个SQLServer数据库，某个业务的表做了表分区之后，已经到达几十亿级别的数据量，但是查询和插入速度还是能满足业务的需求（优化一个系统还是要花精力优化业务层面）。

 

垂直切分

说到垂直拆分，表也可以按照业务来拆分，比如一个数据库中有用户的信息，根据业务可以划分为基础信息和扩展信息，如果对业务有利，完全可以拆分为基础信息表和扩展信息表。当然也可以按照别的规则来拆，比如把访问频繁的信息拆分成一个表，其他不频繁的信息拆分成一个表，具体的拆分规则还是要看当时要解决的问题是什么。垂直拆分可能会引入一定复杂性，比如原来查询一个用户的基础信息和扩展信息可以一次性查询出结果，分表之后需要进行Join操作或者查询两次才能查询出结果。

分表代价

数据表垂直切分之后，原来一次查询有可能会变为连表的Join查询，在一定程度上会有性能损失。数据表横向切分需要一定的规则，常用的主要有两种规则：范围切分和哈希值切分。范围切分是指按照某个字段的范围来切分，比如用户表按照用户ID来切分，ID为1到10万的位于User1中，100001到200000万的位于User2中，这样切分的优势是可以无限扩容下去，不用考虑数据迁移的问题，劣势就是新表和旧表数据分布不均匀，而且分表的范围选取有一定难度，范围太小会导致表太多，太大会导致问题根本上没有解决的困惑。另外一种分表策略就是把某一列按照哈希值来路由到不同的表中，同样以用户ID为例，假如我们一开始就规划了10个数据库表，路由算法可以简单地用 user_id %10的值来表示数据所属的数据库表编号，ID为985的用户放到编号为5的子表中，ID为10086的用户放到编号为6的字表中。这种切分规则的优势是每个表的数据分布比较均匀，但是后期扩容会设计到部分数据的迁移工作。表拆分之后如果遇到有order by的操作，数据库就无能为力了，只能由业务代码或者数据库中间件来完成了。当有搜索的业务需求的时候，SQL语句只能是Join多个表来进行连表查询了，类似的还有统计的需求，例如count的统计操作。

 

十八 数据库日志

MySQL 有哪些重要的日志文件？

① 错误日志

用来记录 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模式。

redo log 和 binlog 有什么区别？

edo log（重做日志）和 binlog（归档日志）都是 MySQL 的重要的日志，它们的区别如下：

 

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”。

binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统，也就是 redo log 来实现 crash-safe 能力。

什么是 crash-safe？

crash-safe 是指发生宕机等意外情况下，服务器重启后数据依然不会丢失的情况。

 

什么是脏页和干净页？

MySQL 为了操作的性能优化，会把数据更新先放入内存中，之后再统一更新到磁盘。当内存数据和磁盘数据内容不一致的时候，我们称这个内存页为脏页；内存数据写到磁盘后，内存的数据和磁盘上的内容就一致了，我们称为“干净页”。

 

MySQL 中可不可以只要 redo log，不要 binlog？

不可以，原因有以下两个：

 

redo log 是循环写不能保证所有的历史数据，这些历史数据只能在 binlog 中找到；

binlog 是高可用的基础，高可用的实现原理就是 binlog 复制。

为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

因为 binlog 是不能“被打断的”，一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中，redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

 

事务执行期间，还未提交，如果发生 crash，redo log 丢失，会导致主备不一致呢？

不会，因为这时候 binlog 也还在 binlog cache 里，没发给备库，crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的。

 

在 MySQL 中用什么机制来优化随机读/写磁盘对 IO 的消耗？

redo log 是用来节省随机写磁盘的 IO 消耗，而 change buffer 主要是节省随机读磁盘的 IO 消耗。redo log 会把 MySQL 的更新操作先记录到内存中，之后再统一更新到磁盘，而 change buffer 也是把关键查询数据先加载到内存中，以便优化 MySQL 的查询。

 

 

有没有办法把 MySQL 的数据恢复到过去某个指定的时间节点？怎么恢复？

可以恢复，只要你备份了这段时间的所有 binlog，同时做了全量数据库的定期备份，比如，一天一备，或者三天一备，这取决于你们的备份策略，这个时候你就可以把之前备份的数据库先还原到测试库，从备份的时间点开始，将备份的 binlog 依次取出来，重放到你要恢复数据的那个时刻，这个时候就完成了数据到指定节点的恢复。比如，今天早上 9 点的时候，你想把数据恢复成今天早上 6:00:00 的状态，这个时候你可以先取出今天凌晨（00:01:59）备份的数据库文件，还原到测试库，再从 binlog 文件中依次取出 00:01:59 之后的操作信息，重放到 6:00:00 这个时刻，这就完成了数据库的还原。

十九 主从同步 读写分离

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

 

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

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

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

主从同步延迟

主库有数据写入之后，同时也写入在binlog(二进制日志文件)中，从库是通过binlog文件来同步数据的，这期间会有一定时间的延迟，可能是1秒，如果同时有大量数据写入的话，时间可能更长。

这会导致什么问题呢？比如有一个付款操作，你付款了，主库是已经写入数据，但是查询是到从库查，从库里还没有你的付款记录，所以页面上查询的时候你还没付款。那可不急眼了啊，吞钱了这还了得！打电话给客服投诉！

所以为了解决主从同步延迟的问题有以下几个方法：

1、二次读取

二次读取的意思就是读从库没读到之后再去主库读一下，只要通过对数据库访问的API进行封装就能实现这个功能。很简单，并且和业务之间没有耦合。但是有个问题，如果有很多二次读取相当于压力还是回到了主库身上，等于读写分离白分了。而且如有人恶意攻击，就一直访问没有的数据，那主库就可能爆了。

2、写之后的马上的读操作访问主库

也就是写操作之后，立马的读操作指定访问主库，之后的读操作采取访问从库。这就等于写死了，和业务强耦合了。

3、关键业务读写都由主库承担，非关键业务读写分离

类似付钱的这种业务，读写都到主库，避免延迟的问题，但是例如改个头像啊，个人签名这种比较不重要的就读写分离，查询都去从库查，毕竟延迟一下影响也不大，不会立马打客服电话哈哈。

分配机制的考虑

分配机制的考虑也就是怎么制定写操作是去主库写，读操作是去从库读。

一般有两种方式：代码封装、数据库中间件。

1、代码封装 代码封装的实现很简单，就是抽出一个中间层，让这个中间层来实现读写分离和数据库连接。讲白点就是搞个provider封装了save,select等通常数据库操作，内部save操作的dataSource是主库的，select操作的dataSource是从库的。

优点：就是实现简单，并且可以根据业务定制化变化，随心所欲。

缺点：就是是如果哪个数据库宕机了，发生主从切换了之后，就得修改配置重启。并且如果你的系统很大，一个业务可能包含多个子系统，一个子系统是java写的一个子系统用go写的，这样的话得分别为不同语言实现一套中间层，重复开发。

 

2、数据库中间件 就是有一个独立的系统，专门来实现读写分离和数据库连接管理，业务服务器和数据库中间件之间是通过标准的SQL协议交流的，所以在业务服务器看来数据库中间件其实就是个数据库。

优点：因为是通过sql协议的所以可以兼容不同的语言不需要单独写一套，并且有中间件来实现主从切换，业务服务器不需要关心这点。

缺点：多了一个系统其实就等于多了一个关心。。如果数据库中间件挂了的话对吧，而且多了一个系统就等于多了一个瓶颈，所以对中间件的性能要求也高，并且所有的数据库操作都要经过它。并且中间件实现很复杂，难度比代码封装高多了。

但是有开源的数据库中间件例如Mysql Proxy,Mysql Route,Atlas。

总结

读写分离相对而言是比较简单的，比分表分库简单，但是它只能分担访问的压力，分担不了存储的压力，也就是你的数据库表的数据逐渐增多，但是面对一张表海量的数据，查询还是很慢的，所以如果业务发展的快数据暴增，到一定时间还是得分库分表。

但是正常情况下，只要当单机真的顶不住压力了才会集群，不要一上来就集群，没这个必要。有关于软件的东西都是越简单越好，复杂都是形势所迫。

一般我们是先优化，优化一些慢查询，优化业务逻辑的调用或者加入缓存等，如果真的优化到没东西优化了然后才上集群，先读写分离，读写分离之后顶不住就再分库分表。

     二十 数据库死锁及解决方法

目前，我们已经探讨了许多关于数据库锁的问题，锁能够有效地解决并发的问题，但这也带来了一个严重的缺点，那就是死锁。

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

在操作系统中，死锁的处理是一个重要的话题，也已经有较为成熟的解决方法，如银行家算法等，在这边我们就不再阐述，只讨论数据库中的死锁。

数据库中常见的死锁原因与解决方案有：

1. 事务之间对资源访问顺序的交替

出现原因： 
一个用户A 访问表A（锁住了表A），然后又访问表B；另一个用户B 访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法： 
这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源

2. 并发修改同一记录

出现原因：主要是由于没有一次性申请够权限的锁导致的。参考：记录一次死锁排查过程

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目中经常发生。 

解决方法：

a. 乐观锁，实现写-写并发

b. 悲观锁：使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

3. 索引不当导致的死锁

出现原因： 
如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

另外一种情况是由于二级索引的存在，上锁的顺序不同导致的，这部分在讨论索引时会提到。参考：https://www.cnblogs.com/LBSer/p/5183300.html

解决方法：

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

 

那么，如何尽可能的避免死锁呢？

1）以固定的顺序访问表和行。即按顺序申请锁，这样就不会造成互相等待的场面。

2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5）为表添加合理的索引。如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

 

     二十一 MySQL 是如何解决幻读的

一、什么是幻读

在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。

而多出来或者少的哪一行被叫做 幻行

二、为什么要解决幻读

在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。

三、MySQL 是如何解决幻读的

如果你看到了这篇文章，那么我会默认你了解了 脏读 、不可重复读与可重复读。

1. 多版本并发控制（MVCC）（快照读/一致性读）

多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。

以 InnoDB 为例，每一行中都冗余了两个字断。一个是行的创建版本，一个是行的删除（过期）版本。

具体的版本号（trx_id）存在 information_schema.INNODB_TRX 表中。

版本号（trx_id）随着每次事务的开启自增。

事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。

普通的 select 就是快照读。

select * from T where number = 1;

原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。

2. next-key 锁 （当前读）

next-key 锁包含两部分

• 记录锁（行锁）
• 间隙锁

记录锁是加在索引上的锁，间隙锁是加在索引之间的。（思考：如果列上没有索引会发生什么？）

select * from T where number = 1 for update;

select * from T where number = 1 lock in share mode;

insert

update

delete

原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的

间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

 

最近用户反馈说系统老是出现insert时，等待超时了，最后发现是insert间隙锁！间隙锁是innodb中行锁的一种， 但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读，但是它会把锁定范围扩大，

二十二 JDBC注册驱动为什么要用反射而不是new？

sun公司只是提供了JDBC API【接口】，数据库厂商负责实现。市面上有不同的数据库厂商。 Class.forName(String className)方法要求JVM查找并加载指定的类到内存中，此时将"com.mysql.jdbc.Driver" 当做参数传入，就是告诉JVM，去"com.mysql.jdbc"这个路径下找Driver类，将其加载到内存中。如果使用new com.mysql.jdbc.Driver()这种方式，会对这个具体的类产生依赖。后续如果你要更换数据库驱动，就得重新修改代码。而使用反射的方式，只需要在配置文件中，更改相应的驱动和url即可。即是解耦。

二十三 存储过程和函数的区别

1.存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

1. 存储函数
   存储过程和存储函数的区别
   一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数，在过程和函数中实现返回多个值。

3.本质区别：

存储函数有返回值，而存储过程没有返回值。

3.1 如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。

3.2 即便是存储过程使用了out类型的参数，起本质也不是真的有了返回值，

3.3 而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。

函数只能返回一个变量，而存储过程可以返回多个；

 

函数可以嵌入sql中和存储过程中使用，但是存储过程需要让sql的query可以执行，将mysql_real_connect的最后一个参数设置为CLIENT_MULTI_STATEMENTS

 

函数的限制比较多，不能用临时表，只能用表变量，有些函数不能用，存储过程限制少

 

存储过程处理的功能比较复杂，而函数实现的功能针对性强，

 

存储过程可以执行修改表的操作，但是函数不能执行一组修改全局数据库状态的操作

 

存储过程可以返回参数，如记录集，函数只能返回值或者表对象。存储过程的参数有in，out，inout三种，函数只有in，存储过程声明时不需要返回类型，而函数需要描述返回类型，且函数中必须包含一个有效的return语句

 

存储过程一般是作为独立部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，所以在查询中位于from关键字后面，sql语句中不可以含有存储过程

二十三 union和union all

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

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

二十四 查询结果去重：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需要读取的记录数量与分组的组数量一样多，也就是说比实际存在的记录数目要少很多。

二十五 char和varchar什么区别？

区别一，定长和变长
char 表示定长，长度固定，varchar表示变长，即长度可变。char如果插入的长度小于定义长度时，则用空格填充；varchar小于定义长度时，还是按实际长度存储，插入多长就存多长。
因为其长度固定，char的存取速度还是要比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。varchar则刚好相反，以时间换空间。
区别之二，存储的容量不同
对 char 来说，最多能存放的字符个数 255，和编码无关。
而 varchar 呢，最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。

char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节。

二十六 数据库页

首先Mysql的基本存储结构是页(记录都存在页里边)：

 

各个数据页可以组成一个双向链表

 

而每个数据页中的记录又可以组成一个单向链表

 

- 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

 

以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

 

所以说，如果我们写 select * from user where username='丙丙'这样没有进行任何优化的sql语句，默认会这样做：

 

定位到记录所在的页

 

- 需要遍历双向链表，找到所在的页

 

从所在的页内中查找相应的记录

 

- 由于不是根据主键查询，只能遍历所在页的单链表了

二十七 写sql对数据加锁

SELECT 语句中“加锁选项”的功能说明

SQL Server提供了强大而完备的锁机制来帮助实现数据库系统的并发性和高性能。用户既能使用SQL Server的缺省设置也可以在select 语句中使用“加锁选项”来实现预期的效果

1. NOLOCK（不加锁）

此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。 在这种情况下，用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。

2. HOLDLOCK（保持锁）

此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。

SELECT * FROM table WITH (HOLDLOCK)

其他事务可以读取表，但不能更新删除

3. UPDLOCK（修改锁）

此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。

UPDLOCK.UPDLOCK 的优点是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。当我们用UPDLOCK来读取记录时可以对取到的记录加上更新锁，从而加上锁的记录在其它的线程中是不能更改的只能等本线程的事务结束后才能更改.

示例：

 

在另一个查询里:BEGIN TRANSACTIONSELECT * FROM myTable WITH (UPDLOCK) WHERE Id in (1,2,3)waitfor delay '00:00:10' update myTable set [Name]='ZZ' where Id in (1,2,3)commit TRANSACTION

 

在另一个查询里:SELECT * FROM myTable WHERE Id in (1,2,3)

可以马上查询到数据。

 

但如果要更新数据，必须等其他更新锁释放后才能执行。update myTable set [Name]='ZZ' where Id in (1,2,3)

 

这就说明，有时候需要控制某条记录在我读取后就不许再进行更新，那么我就可以将所有要处理当前记录的查询都加上更新锁，以防止查询后被其它事务修改。将事务的影响降低到最小

4. TABLOCK（表锁）

此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。

5. TABLOCKX（排它表锁）

此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。

SELECT * FROM table WITH (TABLOCKX)

 

其他事务不能读取表,更新和删除

6. PAGLOCK（页锁）

此选项为默认选项， 当被选中时，SQL Server 使用共享页锁。

7. ROWLOCK （强制使用行锁） 

一直有个疑问，使用 select * from dbo.A with(RowLock) WHRE a=1 这样的语句，系统是什么时候释放行锁呢？？
经过官方文档考证后，原来 RowLock在不使用组合的情况下是没有任何意义的，所谓“解铃还须系铃人~”
With(RowLock,UpdLock) 这样的组合才成立，查询出来的数据使用RowLock来锁定，当数据被Update的时候，或者回滚之后，锁将被释放 

二十八 Where 与having区别

 

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
  having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

  查询每个部门的每种职位的雇员数
  select deptno,job,count(*) from emp group by deptno,job;

 

　　如果你对何时应该使用WHERE，何时使用HAVING仍旧很迷惑，请遵照下面的说明：  

　　WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。  

　　HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。