MySQL相关知识点（持续更新）

淩亂°似流年 2023-02-17 14:52 2阅读 0赞

> # 一、索引 #

## 1.1 为什么要使用索引？ ##

这个可以类比查新华字典，假如字典前面没有“按偏旁部首”、“按拼音”等查询，你要是去查一个字就只能一个一个去字典里面翻了，效率很低。但是按照“偏旁部首”去查就快多了，不用盲目地去一个个翻找。“按偏旁部首”、“按拼音”其实就是字典为自己建立的索引。

同理，数据库为什么也要建立索引？假如不建立索引，你要查一条记录就只能一条一条去查找，效率低下。有了索引，你就可以不断的缩小查找范围来筛选出最终想要的结果，大大提高MySQL的检索速度。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。也就是说你在新增/更新数据库的数据时同时要维护这条记录的索引，这会降低新增/更新表的速度。那你肯定会有疑问：这多维护了一个索引表也是很占用中资源的，为啥还要用索引？实际上这和数据库的读写比例是相关的，一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重，那么索引就有了自己的舞台。一旦为表创建了索引，以后的查询最好先查索引，再根据索引定位的结果去找数据。

**索引本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。**

## 1.2 索引越多越好？ ##

当然不是，若索引太多，会降低写入的效率，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点。

## 1.3 索引什么时候创建更好？ ##

既然索引“性价比”这么高，这么重要，那什么时候创建它合适呢？无外乎两种：事前添加 和 事后添加。

事前添加：如果知道数据的使用，从一开始就在需要处添加索引。这种是比较推荐的做法，事前根据业务需要添加好索引，这时数据库中也没有数据，因此建立索引会容易一些。

事后添加：事后添加因为已经有大量数据，创建索引速度会很慢，建立索引就更加麻烦；另外不容易观察索引效果，需要监控大量的SQL语句。

因此建议事前添加好索引，且注意索引数量（非必要的索引不添加），保持读写效率的平衡。

这就是为啥有时候上线发布的时候，就是一个简单的DDL语句，比如改变某个字段的长度，新增某个字段等简单的sql会执行很久的原因，因为数据库会重新调整索引。

## 1.4  索引原理 ##

### 1.4.1 基于B树 ###

索引的设计从微机原理的角度，目的就是将IO操作的次数减少到最低，IO操作是最耗时间的，只要控制它的次数就能最好的提高查询效率。所以每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级，就是我们索引设计的目标。

能够实现上面目的就是B+树，B+树的详细可以参考：[二叉树、红黑树、B树、B+树 合集-----妈妈再也不用担心我的“树”拉！\_沙滩的流沙520的博客-CSDN博客][B_B_ _-----_520_-CSDN]，看下面的一个B+树图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70][]

一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇。如磁盘块1包含数据项16和36，包含指针P1、P2、P3，P1表示小于16的磁盘块，P2表示在16和36之间的磁盘块，P3表示大于36的磁盘块。最后一层是叶子节点，保存真实数据。

**查询过程：**

*  （1）若要查找20，首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定20在16和36之间，锁定磁盘块1的P2指针；
 *  （2）通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，20在27和26之间，锁定磁盘块3的P2指针
 *  （3）通过指针加载磁盘块8到内存，发生第三次IO，内存中找到20，结束查询，总计三次IO。

**通过上面的查询过程我们有以下总结：**

1.  这里顺便在解释下索引的必要性，若是不用索引，每个数据项都要发生一次IO，数据量达到百万千万级别时，会大大增加查询成本。
2.  **索引字段要尽量的小：**通过上面的分析，我们知道IO次数取决于B树的高度h或者说层级，所以要降低树的层级，在总数据量一定时，那就需要每个磁盘存储的数据尽可能的多，也就需要每个数据单元尽可能的小，这样每个磁盘块就能尽可能多装数据，树就会变得越“矮胖”，层级就会更低，IO的次数就会更低。

若是基于B+树的索引，查找起来更方便，因为B+树的叶子节点包含所有父节点的数据且叶子节点由链表连接，所以查询到叶子节点层级时还可以借助链表进行查找，实现某一范围查询。

### 1.4.2 基于Hash ###

除了B树实现的索引外，还有基于Hash实现的索引。

Hash实现的索引利用了哈希表hash table ，它就是把Key通过一个固定的算法函数既所谓的哈希函数转换成一个整型数字，然后就将该数字对数组长度进行取余，取余结果就当作数组的下标，将value存储在以该数字为下标的数组空间里。而当使用哈希表进行查询的时候，就是再次使用哈希函数将key转换为对应的数组下标，并定位到该空间获取value，如此一来，就可以充分利用到数组的定位性能进行数据定位。

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些：

1.  **Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。**由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。
2.  **Hash 索引无法被用来避免数据的排序操作。**由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
3.  **Hash 索引不能利用部分索引键查询**。对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
4.  **Hash 索引在任何时候都不能避免表扫描**。Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
5.  **Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。**对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下

## 1.5  索引分类 ##

上面说了索引的作用、原理，下面说下分类，基本的可以分为：

**普通索引**：仅加速查询

**唯一索引**：加速查询 + 列值唯一（可以有null）

**主键索引**：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

**组合索引**：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

**全文索引**：对文本的内容进行分词，进行搜索

--------------------

除了这些基础的索引，因为叫法不同，研究背景不同，还有下面一些索引，这些概念容易混淆，做好理解：

**主索引**：主索引就是主键索引

**辅助索引**：就是根据业务需要，自己设置的普通的非主键的索引。这个在MyIsam里面区别不大，但是在Innodb的时候差别很大

**聚集****索引**： 聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据。一个表只能有1个聚簇索引，因为表数据存储的物理位置是唯一的。聚簇索引的value存的就是真实的数据，不是数据的地址。主索引树里面包含了真实的数据。key是主键值，value值就是data，key值按照B+树的规则分散排布的叶子节点，聚簇索引的顺序就是数据的物理存储顺序，所以一个表最多只能有一个聚簇索引，因为物理存储只能有一个顺序。正因为一个表最多只能有一个聚簇索引，所以它显得更为珍贵，一个表设置什么为聚簇索引对性能很关键。Innodb的主索引采用的是聚簇索引。

**非聚集****索引**：索引和表数据是分离的，索引的value值指向物理的存储地址。MyIsam主索引和辅助索引都采用的是非聚簇索引。

**覆盖索引**： SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据，一个索引中包含所有需要查询字段的值。如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。使用覆盖索引的一个好处是：覆盖索引不包含整行记录的所有信息，只包含组成该索引的部分字段信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作

**最左匹配特性**：主要针对组合索引，从数据块的左边开始匹配，再匹配右边的。比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

## 1.6 各种索引的应用场景 ##

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表  
有下列字段：  
会员编号 INT  
会员姓名 VARCHAR(10)  
会员身份证号码 VARCHAR(18)  
会员电话 VARCHAR(10)  
会员住址 VARCHAR(50)  
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY  
会员姓名 如果要建索引的话，那么就是普通的 INDEX  
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

\#除此之外还有全文索引，即FULLTEXT  
会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。  
用于搜索很长一篇文章的时候，效果最好。  
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。  
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

\#其他的如空间索引SPATIAL，了解即可，几乎不用

## 1.7  索引的创建/删除 ##

**创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)**。

create unique index 索引名 on 表名(列名1,列名2……)

> ** 主键索引:**  
> 创建的时候添加:  添加索引的时候要注意,给字段里面数据大小比较小的字段添加,给字段里面的数据区分度高的字段添加.  
> 聚集索引的添加方式  
> 创建的是添加  
> Create table t1(  
> Id int primary key,  
> )  
> Create table t1(  
> Id int,  
> Primary key(id)  
> )
> 
> 表创建完了之后添加  
> Alter table 表名 add primary key(id)  
> 删除主键索引:  
> Alter table 表名 drop primary key;

> **唯一索引:**  
> Create table t1(  
> Id int unique,  
> )
> 
> Create table t1(  
> Id int,  
> Unique key uni\_name (id)  
> )
> 
> 表创建好之后添加唯一索引:  
> alter table s1 add unique key  u\_name(id);  
> 删除:  
> Alter table s1 drop index u\_name;

> **普通索引:**  
> 创建:  
> Create table t1(  
> Id int,  
> Index index\_name(id)  
> )
> 
> 表创建好之后添加普通索引:  
> Alter table s1 add index index\_name(id);  
> Create index index\_name on s1(id);
> 
> 删除:  
> Alter table s1 drop index u\_name;  
> DROP INDEX 索引名 ON 表名字;

创建索引小结：

1.  一定是为搜索条件的字段创建索引，比如select \* from s1 where id = 333;就需要为id加上索引
2.  在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快  
    比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。  
    建完以后，再查询就会很快了。因此建议建表时就建立索引。
3.  尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(\*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
4.  最左前缀匹配原则（详见第八小节），非常重要的原则，对于组合索引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的顺序可以任意调整。索引是有个最左匹配的原则的，所以建联合索引的时候，将区分度高的放在最左边，依次排下来。

> ps：创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?
> 
> MySQL提供了**explain命令**来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe\_key,key,key\_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度。

> 那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?
> 
> 1.  使用不等于查询
> 2.  列参与了数学运算或者函数
> 3.  在字符串like时左边是通配符.类似于'%aaa'
> 4.  当mysql分析全表扫描比使用索引快的时候不使用索引
> 5.  当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引

## 1.8 explain用法和结果含义 ##

通过EXPLAIN，我们可以分析出以下结果：

>  *  表的读取顺序
>  *  数据读取操作的操作类型
>  *  哪些索引可以使用
>  *  哪些索引被实际使用
>  *  表之间的引用
>  *  每张表有多少行被优化器查询

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 1][]

### 1.8.1 各字段含义 ###

**（1）id**

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

*  id相同，执行顺序由上至下；
 *  id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

**（2）select\_type**

分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

*  SIMPLE 简单的select查询，查询中不包含子查询或者UNION
 *  PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
 *  SUBQUERY 在SELECT或WHERE列表中包含了子查询
 *  DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
 *  UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
 *  UNION RESULT 从UNION表获取结果的SELECT

**（3）table**

指的就是当前执行的表

**（4）type**

type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：

从最好到最差依次是：system > const > eq\_ref > ref > range > index > all ，一般来说，得保证查询至少达到range级别，最好能达到ref。

*  system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
 *  const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。首先进行子查询得到一个结果的d1临时表，子查询条件为id = 1 是常量，所以type是const，id为1的相当于只查询一条记录，所以type为system。

![70][]

*  eq\_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
 *  ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
 *  range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
 *  all Full Table Scan 将遍历全表以找到匹配的行

**（5）possible\_keys 和 key**

possible\_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key 实际使用的索引，如果为NULL，则没有使用索引。查询中若使用了`覆盖索引`（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中

**（6）key\_len**

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key\_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key\_len是根据表定义计算而得，不是通过表内检索出的。

**（7）ref**

显示索引的哪一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

**（8）rows**

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好。

**（9）Extra**

包含不适合在其他列中显式但十分重要的额外信息。

*  Using filesort（不好）：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
 *  Using temporary（更不好）：使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
 *  Using index（最好）：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
 *  Using where：表明使用了where过滤
 *  sing join buffer：表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
 *  impossible where：where子句值总为false，不能获取任何元组，如：SELECT \* FROM t\_user WHERE id = '1' and id = '2'
 *  select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(\*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
 *  distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

## 1.9 **什么时候要创建索引？** ##

（1）表经常进行 SELECT 操作

（2）表很大(记录超多)，记录内容分布范围很广

（3）列名经常在 WHERE 子句或连接条件中出现

## 1.10 **什么时候不要创建索引？** ##

（1）表经常进行 INSERT/UPDATE/DELETE 操作

（2）表很小(记录超少)

（3）列名不经常作为连接条件或出现在 WHERE 子句中

（4）列的区分度不高，比如性别列

> #  二、事务 #

## 2.1 概念 ##

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

*  在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
 *  事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
 *  事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：。

>  *  **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
>  *  **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
>  *  **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
>  *  **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

ps： 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

## 2.2  事务控制语句 ##

*  BEGIN 或 START TRANSACTION 显式地开启一个事务；
 *  COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
 *  ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
 *  SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
 *  RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
 *  ROLLBACK TO identifier 把事务回滚到标记点；
 *  SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

## 2.3  MYSQL 事务处理主要有两种方法 ##

1、用 BEGIN, ROLLBACK, COMMIT来实现

*  **BEGIN** 开始一个事务
 *  **ROLLBACK** 事务回滚
 *  **COMMIT** 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

*  **SET AUTOCOMMIT=0** 禁止自动提交
 *  **SET AUTOCOMMIT=1** 开启自动提交

## **2.4  事务的并发问题** ##

1.  **脏读**：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2.  **不可重复读**：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
3.  **幻读**：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，可重复读可能发生在update,delete操作中，而幻读发生在insert操作中，解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

## 2.5  事务隔离级别 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 2][]

*  **读未提交(Read Uncommitted)**：解决更新丢失问题。如果一个事务已经开始写操作，那么其他事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现，即事物需要对某些数据进行修改必须对这些数据加 X 锁，读数据不需要加 S 锁。可能产生脏读问题。
 *  **读已提交(Read Committed)**：解决了脏读问题。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。这可以通过“瞬间共享读锁”和“排他写锁”实现， 即事物需要对某些数据进行修改必须对这些数据加 X 锁，读数据时需要加上 S 锁，当数据读取完成后立刻释放 S 锁，不用等到事物结束。可能产生不可重复读问题。
 *  **可重复读取(Repeatable Read)**：解决了不可重复读。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。Mysql默认使用该隔离级别。这可以通过“共享读锁”和“排他写锁”实现，即事物需要对某些数据进行修改必须对这些数据加 X 锁，读数据时需要加上 S 锁，当数据读取完成并不立刻释放 S 锁，而是等到事物结束后再释放。但是有时可能出现幻读数据。
 *  **串行化(Serializable)**：解决了幻读的问题的。提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

MySQL默认的事务隔离级别为repeatable-read（可重复读），不能解决幻读的问题。

下面开始分别介绍几种隔离级别，开始盗图：

### 2.5.1 读未提交（产生-脏读） ###

（1）打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表account的初始值：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 3][]

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 4][]

（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 5][]

（4）一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 6][]

（5）在客户端A执行更新语句update account set balance = balance - 50 where id =1，lilei的balance没有变成350，居然是400，是不是很奇怪，数据不一致啊，如果你这么想就太天真了，在应用程序中，我们会用400-50=350，并不知道其他会话回滚了，要想解决这个问题可以采用读已提交的隔离级别

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 7][]

### 　　2.5.2  读已提交（产生-不可重复读） ###

就是只能读取已经提交的，未提交的读不到。

（1）打开一个客户端A，并设置当前事务模式为read committed（读提交），查询表account的所有记录：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 8][]

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 9][]

（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新但未提交的数据，解决了脏读问题

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 10][]

（4）客户端B的事务提交

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 11][]

（5）客户端A执行与上一步相同的查询，结果与上一步不一致，即产生了不可重复读的问题

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 12][]

### 　2.5.3  可重复读 （产生-幻读） ###

（1）打开一个客户端A，并设置当前事务模式为repeatable read，查询表account的所有记录

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 13][]

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 14][]

（3）在客户端A查询表account的所有记录，与步骤（1）查询结果一致，没有出现不可重复读的问题

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 15][]

（4）在客户端A，接着执行update balance = balance - 50 where id = 1，balance没有变成400-50=350，lilei的balance值用的是步骤（2）中的350来算的，所以是300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 16][]

### 2.5.4  串行化 ###

（1）打开一个客户端A，并设置当前事务模式为serializable，查询表account的初始值：

（2）打开一个客户端B，并设置当前事务模式为serializable，插入一条记录报错，表被锁了插入失败，mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

**事务隔离级别小结：**

1.  事务隔离级别为读提交时，写数据只会锁住相应的行
2.  事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
3.  事务隔离级别为串行化时，读写数据都会锁住整张表
4.  隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

> # 三、MySQL中的锁 #

数据库对于数据的操作无非两种类型，读和写，针对这个特点，目前有两种数据库的锁，乐观锁和悲观锁，乐观锁和悲观锁的区别在于 **是否认为并发问题一定会存在**。

## 3.1 锁的分类 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 17][]

*  **按锁的粒度划分**（即，每次上锁的对象是表，行还是页）：表级锁，行级锁，页级锁
 *  **按锁的级别划分**：共享锁、排他锁
 *  **按加锁方式分**：自动锁（存储引擎自行根据需要施加的锁）、显式锁（用户手动请求的锁）
 *  **按操作划分**：DML锁（对数据进行操作的锁)、DDL锁（对表结构进行变更的锁）
 *  **最后按使用方式划分**：悲观锁、乐观锁

MySQL用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁,这些锁都可以归类为悲观锁(Pessimistic Lock)。

**表级锁：**开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。   
**行级锁：**开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。   
**页面锁：**开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

## 3.2 存储引擎和锁 ##

不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

### 3.2.1 **InnoDB锁** ###

在mysql 的 InnoDB引擎支持行锁，分为两种：

> 共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
> 
> 排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

**语法**

> 上共享锁的写法：lock in share mode
> 
> 例如： select  \*  from 表 where  条件  lock in share mode；
> 
> 上排它锁的写法：for update
> 
> 例如：select \*  from 表  where 条件 for update；

**insert ，delete ， update在事务中都会自动默认加上排它锁。**

## InnoDB行锁实现方式 ##

行锁在 InnoDB 中**基于索引实现**，如加锁没用索引，退化为表锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

> # 四、存储引擎  #

## 4.1  **什么是存储引擎？** ##

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

例如，如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力。

**这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。**

MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

## 4.2  **存储引擎分类** ##

### 4.2.1  **MyISAM** ###

它不支持事务，也不支持外键，但是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。

### 4.2.2  **InnoDB** ###

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版，InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择：

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。  
2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。  
3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。  
4.外键约束。MySQL支持外键的存储引擎只有InnoDB。  
5.支持自动增加列AUTO\_INCREMENT属性。

一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

### 4.2.3  **MEMORY** ###

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。

虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。

一般在以下几种情况下使用Memory存储引擎：

1.  目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max\_heap\_table\_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。
2.  如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
3.  存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。

### 4.2.4  **MERGE** ###

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。Merge存储引擎的使用场景。

对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

###  4.2.5 **ARCHIVE** ###

Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

场景：由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。

> # 五、表结构设计 #

##  5.1 为什么要给表加上主键？ ##

1.  一个没加主键的表,它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐.
2.  一个加了主键的表,并不能被称之为「表」。如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,并且是「平衡树」结构,换句话说,就是整个表就变成了一个索引。没错,再说一遍,整个表变成了一个索引,也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键,一个表只能有一个「聚集索引」,因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。变成树查找就简单了，不然要全表扫描。
3.  业务需要：表中每一行都应该有可以唯一标识自己的一列（或一组列），唯一标识业务。

##  5.2 **主键使用自增ID还是UUID**？ ##

> **自增**
> 
> 优点：
> 
> 1.  字段长度较uuid小很多，可以是bigint甚至是int类型，这对检索的性能会有所影响。我们平时数据库一般用的都是innodb引擎的表，这种表格检索数据的时候，哪怕走索引，也是先根据索引找到主键，然后由主键找到这条记录。所以主键的长度短的话，读性能是会好一点的
> 2.  因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降
> 
> 缺点：
> 
> 1.  很容易被别人知晓业务量，受网络爬虫侵害
> 2.  高并发的情况下，竞争自增锁会降低数据库的吞吐能力
> 3.  数据迁移的时候，特别是发生表格合并会很麻烦

> **UUID**
> 
> 优点：
> 
> 1.  唯一的guid，不会冲突
> 2.  可以在应用层生成，提高数据库吞吐能力
> 
> 缺点:
> 
> 1.  与自增相比，最大的缺陷就是随机io。这一点又要谈到我们的innodb了，因为这个默认引擎，表中数据是按照主键顺序存放的。也就是说，如果发生了随机io，就会频繁地移动磁盘块。当数据量大的时候，写的短板将非常明显。
> 2.  读取出来的数据也是没有规律的，通常需要order by，其实也很消耗数据库资源

综合起来，一般推荐使用自增ID,不要使用UUID。

##  5.3 超大分页 ##

###   5.3.1 传统的limit ###

在MySQL中limit可以实现快速分页：

select * from product limit 866613, 20;   # 37.44秒

1）limit语句的查询时间与起始记录的位置成正比  
2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

limit可以实现快速分页，但是如果数据到了几百万时limit必须优化才能有效的合理的实现分页了，否则可能会很慢。 有下面几个解决方案：

###  5.3.2  方案一：覆盖索引 ###

利用表的覆盖索引来加速分页查询，我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：

这次我们查询最后一页的数据（利用覆盖索引，只包含id列），如下：

select id from product limit 866613, 20  # 0.2秒

相对于查询了所有列的37.44秒，提升了大概100多倍的速度。

### 5.3.3  方案二：**用id优化** ###

先找到上次分页的最大ID,然后利用id上的索引来查询,类似于select \* from user where id>1000000 limit 100.  
这样的效率非常快,因为主键上是有索引的,但是这样有个缺点,就是ID必须是连续的,并且查询不能有where语句,因为where语句会造成过滤数据.

### 5.3.4 方案三：**利用缓存** ###

解决超大分页,还可以靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可。

## 5.4  三个范式 ##

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。所以这里就只记录三范式相关的知识。

> 1NF:字段不可分;   
> 2NF:有主键，非主键字段依赖主键;   
> 3NF:非主键字段不能相互依赖;   
>   
> 解释:   
> 1NF:原子性 字段不可再分,否则就不是关系数据库;   
> 2NF:唯一性 一个表只说明一个事物;   
> 3NF:每列都与主键有直接关系，不存在传递依赖;

**第一范式（1NF）**

即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF。数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息。

**第二范式（2NF）**

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要我们设计一个主键来实现(这里的主键不包含业务逻辑)。

即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个字段。（涉及到表的拆分）

**第三范式（3NF）**

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。我们为了满足第三范式往往会把一张表分成多张表。

即满足第二范式前提，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。 通俗解释就是一张表最多只存两层同类型信息。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 18][]

但是有些时候一昧的追求范式减少冗余，反而会降低数据读写的效率，这个时候就要反范式，利用空间来换时间。

> # 六、慢查询 #

## 6.1 什么是慢查询？ ##

MySQL的慢查询，全名是**慢查询日志**，是MySQL提供的一种日志记录，用来记录在MySQL中**响应时间超过阀值**的语句。

具体环境中，运行时间超过long\_query\_time值的SQL语句，则会被记录到慢查询日志中。long\_query\_time的默认值为10，意思是记录运行10秒以上的语句。慢查询日志支持将日志记录写入文件和数据库表。

一条语句执行10秒以上，肯定影响用户体验啊，慢查询记录这些耗时的sql，就给优化响应时间提供了方向。

## 6.2 怎么开启慢查询？ ##

默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。

当然，**如果不是调优需要的话，一般不建议启动该参数**，因为开启慢查询日志会或多或少带来一定的性能影响。

默认情况slow\_query\_log的值是OFF，表示慢查询日志是禁用的，可以通过设置slow\_query\_log的值来开启:

mysql> show variables  like '%slow_query_log%';
     +---------------------+-----------------------------------------------+
     | Variable_name       | Value                                         |
     +---------------------+-----------------------------------------------+
     | slow_query_log      | OFF                                           |
     | slow_query_log_file | /home/WDPM/MysqlData/mysql/DB-Server-slow.log |
     +---------------------+-----------------------------------------------+
     2 rows in set (0.00 sec)
     
    mysql> set global slow_query_log=1;
     Query OK, 0 rows affected (0.09 sec)

使用 set slow\_query\_log=1 开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件（其它系统变量也是如此）。my.cnf要增加或修改参数，如下所示：

slow_query_log = 1
    slow_query_log_file = /tmp/mysql_slow.log

最后重启MySQL服务器。slow\_query\_log\_file指定了慢查询日志的存放路径，缺省情况是host\_name-slow.log文件.

## 6.3 常见参数 ##

除了slow\_query\_log\_file、slow\_query\_log还有很多和慢查询相关的配置参数。

### 6.3.1 long\_query\_time ###

默认情况下long\_query\_time的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。关于运行时间**正好等于**long\_query\_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long\_query\_time，而非大于等于。

首先SQL判断一个语句是不是慢查询语句，用的是语句的执行时间。他把语句执行时间跟long\_query\_time这个系统参数作比较，如果语句执行时间比它还大，就会把这个语句记录到慢查询日志里面，这个参数的默认值是10秒。当然在生产上，我们不会设置这么大，一般会设置1秒，对于一些比较敏感的业务，可能会设置一个比1秒还小的值。

### 6.3.2 log\_output ###

log\_output参数指定日志的存储方式。log\_output='FILE', 表示将日志存入文件，默认值也是'FILE'。log\_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow.log表中。同时也支持两种日志存储方式，配置的时候以逗号隔开即可，如：log\_output='FILE,TABLE'。

日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源。因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么**建议优先记录到文件**。

### 6.3.2 log-queries-not-using-indexes ###

该系统变量指定**未使用索引的查询**也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用`full index scan`的SQL也会被记录到慢查询日志。

## 6.4  mysqldumpslow工具 ##

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活。MySQL提供了日志分析工具mysqldumpslow。

> # 七、存储过程 #

## 7.1  概念 ##

MySQL 5.0 版本开始支持存储过程。存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

为什么要用存储过程？

①将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用

②批量处理：SQL+循环，减少流量

③统一接口，确保数据的安全

相对于oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少。

### 优点 ###

*  存储过程可封装，并隐藏复杂的商业逻辑。
 *  存储过程可以回传值，并可以接受参数。
 *  存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
 *  存储过程可以用在数据检验，强制实行商业逻辑等。

### 缺点 ###

*  存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
 *  存储过程的性能调校与撰写，受限于各种数据库系统。

## 7.2 创建一个简单的存储过程 ##

1、创建存储过程的简单语法

create procedure 名称()
    begin
    .........
    end

2、创建一个简单的存储过程

create procedure testa()
    begin
        select * from users;
        select * from orders;
    end;

3、调用存储过程

call testa();

![20200615203555141.png][]    ![20200615203613905.png][]

> # 八、binlog #

## 8.1 什么是binlog？ ##

binlog记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志binlog可以说是MySQL最重要的日志，MySQL的二进制日志是事务安全型的。

DDL（Data Definition Language 数据定义语言）：主要的命令有create、alter、drop等，ddl主要是用在定义或改变表(table)的结构,数据类型，表之间的连接和约束等初始工作上，他们大多在建表时候使用。

DML（Data Manipulation Language 数据操纵语言）：主要命令是slect,update,insert,delete,就像它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

binlog日志包括两类文件：

1.  二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件。
2.  二进制日志文件(文件名后缀为.00000\*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

## 8.2 binlog使用场景 ##

1.  mysql主从复制：mysql replication在master端开启binlog,master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
2.  数据恢复：通过mysqlbinlog工具来恢复数据。所谓恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。

## 8.3 binlog的开启 ##

在my.inf主配置文件中直接添加三行：

# 打开binlog日志
    log_bin=ON
    # binlog日志的基本文件名，后面会追加标识来表示每一个文件
    log_bin_basename=/var/lib/mysql/mysql-bin
    # binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
    log_bin_index=/var/lib/mysql/mysql-bin.index
    # 随机指定一个不能和其他集群中机器重名的字符串，如果只有一台机器，那就可以随便指定了
    server-id=123454

有了上述的配置之后，我们就可以重新启动我们的mysql了，重启命令：

service mysqld restart

启动成功之后，我们可以登陆查看我们的配置是否起作用：

show variables like '%log_bin%'

![Center][]

# 九、**数据库的主从复制** #

## 9.1 主从复制过程 ##

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5rKZ5rupZGXmtYHmspk_size_17_color_FFFFFF_t_70_g_se_x_16][]

*  步骤一：主库db的更新事件(update、insert、delete)被写到binlog
 *  步骤二：从库生成**两个线程**，一个 I/O 线程，一个 SQL 线程；I/O 线程去请求主库的 binlog，并将得到的 binlog 日志**写到** relay log(中继日志) 文件中；
 *  步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库
 *  步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
 *  步骤五：从库的SQL线程，从relay log里面读取内容，从**Exec\_Master\_Log\_Pos**位置开始执行读取到的更新事件，将更新内容写入到slave的db

当从服务器连接主服务器时，主服务器会创建一个 log dump 线程，用于发送 binlog 的内容。在读取 binlog 的内容的操作中，会对象主节点上的 binlog **加锁**，当读取完成并发送给从服务器后解锁。从节点 SQL 线程负责读取 realy-log 中的内容，**解析**成具体的操作执行，最终保证主从数据的一致性。

## 9.2 主从复制实战操作 ##

### 准备工作 ###

*  主从数据库版本最好一致
 *  主从数据库内数据保持一致
 *  主数据库：10.168.1.248 从数据库：10.168.1.249

### 配置主库Master ###

找到主数据库的配置文件my.cnf，一般在`/etc/my.cnf`

vi /etc/my.cnf

在\[mysqld\]部分插入

[mysqld]
    log-bin=mysql-bin #开启二进制日志
    server-id=1 #设置server-id，必须唯一

配置说明

>  *  log-bin：设置二进制日志文件的基本名；
>  *  log-bin-index：设置二进制日志索引文件名；
>  *  binlog\_format：控制二进制日志格式，进而控制了复制类型，三个可选值
>  *   -STATEMENT：语句复制
>  *   -ROW：行复制
>  *   -MIXED：混和复制，默认选项
>  *  server-id：服务器设置唯一ID，默认为1，推荐取IP最后部分；
>  *  sync-binlog：默认为0，为保证不会丢失数据，需设置为1，用于强制每次提交事务时，同步二进制日志到磁盘上。

### 创建用户 ###

重启服务，创建用于同步的用户

打开mysql会话shell

mysql -uroot -p
    复制代码

创建用户并授权：用户：repl密码：repl'

# 创建用户
    create user 'repl'@'10.168.1.249' identified by 'repl';
    
    # 授权，只授予复制和客户端访问权限
    grant replication slave on *.* to 'repl'@'10.168.1.249';#分配权限

### 配置从库Slave ###

找到主数据库的配置文件my.cnf，在`/etc/my.cnf`

vi /etc/my.cnf

在\[mysqld\]部分插入

[mysqld]
    server-id=2 #设置server-id，必须唯一

### 执行同步SQL语句 ###

重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

mysql> CHANGE MASTER TO
        -> MASTER_HOST='10.168.1.248',
        -> MASTER_USER='repl',
        -> MASTER_PASSWORD='repl',
        -> MASTER_LOG_FILE='mysql-bin.000001',
        -> MASTER_LOG_POS=1416;
    Query OK, 0 rows affected, 2 warnings (0.02 sec)

### 启动slave同步进程 ###

mysql>start slave;

### 查看slave状态 ###

mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.168.1.248
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 1917
                   Relay_Log_File: sl249-relay-bin.000003
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
              				......
    复制代码

当Slave\_IO\_Running和Slave\_SQL\_Running都为YES的时候就表示主从同步设置成功了。

### 验证 ###

## 9.3 主从复制分类 ##

### 形式上分为： ###

*  一主一从
 *  一主多从
 *  多主一从
 *  双主复制（互为主从）
 *  级联复制

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5rKZ5rupZGXmtYHmspk_size_16_color_FFFFFF_t_70_g_se_x_16][]

一主一从和一主多从是我们现在见的最多的主从架构，使用起来简单有效，不仅可以实现 HA，而且还能读写分离，进而提升集群的**并发能力**。

多主一从可以将多个 MySQL 数据库**备份**到一台存储性能比较好的服务器上。

双主复制，也就是可以互做主从复制，每个 master 既是 master，又是另外一台服务器的 salve。这样任何一方所做的变更，都会通过**复制**应用到另外一方的数据库中。

级联复制模式下，部分 slave 的数据同步不连接主节点，而是连接**从节点**。

因为如果主节点有太多的从节点，就会损耗一部分性能用于 replication ，那么我们可以让 3~5 个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以**缓解**主节点的压力，并且对**数据一致性**没有负面影响。

### 流程上分为： ###

*  异步复制
 *  同步复制
 *  半同步复制
 *  延迟复制

**【异步复制】：**

一个主库，一个或多个从库，数据**异步同步**到从库。

这种模式下，主节点**不会主动推送数据**到从节点，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理。

这样就会有一个问题，主节点如果崩溃掉了，此时主节点上已经提交的事务可能并没有传到从节点上，如果此时，强行将从提升为主，可能导致新主节点上的数据不完整。

**【同步复制****】：**

在 MySQL cluster 中特有的复制方式。

当主库执行完一个事务，然后所有的从库都复制了该事务并**成功执行完**才返回成功信息给客户端。

因为需要等待所有从库执行完该事务才能返回成功信息，所以全同步复制的性能必然会收到严重的影响。

**【半同步复制】：**

在异步复制的基础上，确保任何一个主库上的事物在提交之前**至少有一个从库**已经收到该事物并日志记录下来。

主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待**至少一个从库接收到并写到** relay log 中才返回成功信息给客户端(只能保证主库的 Binlog 至少传输到了一个从节点上)，否则需要等待直到**超时时间**然后切换成异步模式再提交。

相对于异步复制，半同步复制提高了数据的**安全性**，一定程度的保证了数据能成功备份到从库，同时它也造成了一定程度的延迟，但是比全同步模式延迟要低，这个延迟最少是一个 TCP/IP 往返的时间。所以，半同步复制最好在**低延时**的网络中使用。

半同步模式不是 MySQL 内置的，从 `MySQL 5.5` 开始集成，需要 master 和 slave 安装插件开启半同步模式。

**【延迟复制】：**

在异步复制的基础上，人为设定主库和从库的数据**同步延迟时间**，即保证数据延迟至少是这个参数。

配置主要要点如下：

# 如果在双主复制结构中没有设置ID的话就会导致循环同步问题
    server_id=1
    
    # 即日志中记录的是语句还是行更新或者是混合
    binlog_format=mixed
    
    # 在进行n次事务提交以后，Mysql将执行一次fsync的磁盘同步指令。将缓冲区数据刷新到磁盘。
    # 为0的话由Mysql自己控制频率。
    sync_binlog=n
    
    # 为0的话，log buffer将每秒一次地写入log file中并且刷新到磁盘。
    # mysqld进程崩溃会丢失一秒内的所有事务。
    # 为1的话，每次事务log buffer会写入log file并刷新到磁盘。（较为安全）
    # 在崩溃的时候,仅会丢失一个事务。
    # 为2的话，每次事务log buffer会写入log file，但一秒一次刷新到磁盘
    innodb_flush_logs_at_trx_commit=0
    
    # 阻止从库崩溃后自动启动复制，给一些时间来修复可能的问题，
    # 崩溃后再自动复制可能会导致更多的问题。并且本身就是不一致的
    skip_slave_start=1 
    
    # 是否将从库同步的事件也记录到从库自身的bin-log中
    # 允许备库将重放的事件也记录到自身的二进制日志中去，可以将备库当做另外一台主库的从库
    log_slave_update 
    
    # 日志过期删除时间，延迟严重的话会导致日志文件占用磁盘
    expire_logs_days=7

## 9.4 主从复制可能出现的问题 ##

### 问题一：主从延迟 ###

当主库的 TPS 并发较高的时候，由于主库上面是多线程写入的，而从库的SQL线程是单线程的，导致从库SQL可能会跟不上主库的**处理速度**。

*解决方法*：

*  网络方面：尽量保证主库和从库之间的**网络稳定**，延迟较小，尽量是在同一局域网种；
 *  硬件方面：从库**配置更好**的硬件，提升随机写的性能；
 *  配置方面：尽量使 MySQL 的操作在**内存中完成**，减少磁盘操作。或升级 MySQL5.7 版本使用并行复制；
 *  建构方面：在事务中尽量对**主库读写**，其它非事务的读在从库。消除一部分**延迟**带来的数据库不一致。**增加缓存**降低一些从库的负载。

### 问题二：数据丢失 ###

当主库宕机后，数据可能丢失。

*解决方法*：

使用**半同步**复制，可以解决数据丢失的问题。

## 9.5 主从复制的作用 ##

主从复制带来了很多好处：

1.  高可用HA ：当我们的主服务器出现问题，可以**切换**到从服务器；
2.  可以进行数据库层面的**读写分离**；
3.  可以在从数据库上进行日常**备份；**
4.  数据更安全：做了**数据冗余**，不会因为单台服务器的宕机而丢失数据；
5.  性能大大提升：一主多从，不同用户从不同数据库读取，**性能提升**；
6.  扩展性更优：流量增大时，可以方便的**增加**从服务器，不影响系统使用；
7.  负载均衡：一主多从相当于分担了主机任务，做了**负载均衡；**
8.  实时数据可以在 Master 节点上创建，而分析这些数据可以在 Slave 节点上进行，并且不会对 Master 节点的性能产生影响。

MySQL 主从复制集群功能使得 MySQL 数据库支持**大规模高并发读写**成为可能，同时有效地保护了物理服务器宕机场景的**数据备份**。

# 补充一：Cardinality 与 建索引时字段的高选择性 #

建索引时是否有想过该选择哪个或者哪些字段作为索引？是不是只要在查询条件中出现的字段就要加上索引呢？加了索引怎么验证这个索引是否必要呢？这些都和Cardinality有关联。

首先，并不是在查询条件中出现的字段都要加上索引。对于什么时候添加B+树索引，一般的经验是选择区分度高的字段，对于地区字段、性别字段、类型字段，他们的可取值范围很小，也就是选择性低。比如：

SELECT id FROM student WHERE sex = 'F'

性别只有'M'和'F'， 这个查询结果可能是50%的数据，这时就是选择性低，添加B+树索引就不必要了。相反若某个字段取值范围很广，几乎是唯一的，那就是这个字段选择性高，适合添加B+树索引，通过这个索引只能从数据库找出少量的行记录，这时的索引更有意义，例如学号字段，没有重复的。

还有一个问题：怎么判断创建的索引是否是高选择性的呢？可以通过show index from table结果中的Cardinality来判断：

![20200724194742708.png][]

Cardinality的值表示索引中不重复记录数量的预估值，若值比较小则说明可选择性低，没有加索引的必要。

Cardinality（可以翻译为“散列程度”）,它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度,那么索引就基本失效了，没有加索引的必要，即使建立了索引优化器也可能不使用这个索引。

Collation: 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。

# 补充二：关于联合索引你要知道的事 #

indx\_a\_b (a，b) 这个indx\_a\_b就是联合索引，联合的列为（a，b）。

联合索引本质上也是B+树，但是不同的是它的键值的数量不是1，而是大于等于2：

### ![2020072421524465.png][] ###

但是第一个键值也是排序的，按照（a，b）的顺序进行了存放，对于 SELECT \* FROM TABLE WHERE a=xxx AND b=xxx 是可以使用indx\_a\_b这个联合索引的；对于 SELECT \* FROM TABLE WHERE a=xxx 也是可以使用indx\_a\_b索引的；但是对于SELECT \* FROM TABLE WHERE b=xx用不到这个索引，因为b的值是不排序的。

**联合索引的底层存储结构长什么样?**

定义联合索引(员工级别，员工姓名，员工出生年月)，将联合索引按照索引顺序放入节点中，新插入节点时，先按照联合索引中的员工级别比较，如果相同会按照是员工姓名比较，如果员工级别和员工姓名都相同 最后是员工的出生年月比较。可以从图中从上到下，从左到右看，第一个B+树的节点 是通过联合索引的员工级别比较的，第二个节点是 员工级别相同，会按照员工姓名比较，第三个节点是 员工级别和员工姓名都相同，会按照员工出生年月比较。

![format_png][]

**具体的：**

现在建一个联合索引： `create index idx on t1(b,c,d);` InnoDB会按照`b,c,d`三个字段排序，建立一棵B+Tree，如下图：

![format_png 1][]

在叶子节点，记录每条数据主键的值，非叶子节点只存储了`b,c,d`字段的值。

根据`b,c,d`找到主键后，拿着主键到主键索引上去找，最终找到具体数据。

# 补充三：优化器什么时候不会使用索引？ #

在有些时候explain命令执行sql时，发现优化器并没有使用索引去查找数据，而是通过扫描聚集索引，也是就是进行权标扫描来查找数据。这种情况一般发生于范围查找、JOIN连接操作等情况下。

# 补充四：左匹配？ #

例如组合索引（a,b,c），相当于创建了（a）、（a，b）、（a，b，c）3个索引

组合索引的生效原则是 ：

从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用。比如：

where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；

where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果

where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；

where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

where a=3 and b>7 and c=3;(范围值就算是断点)

a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

where a>4 and b=7 and c=9;

a用到了 b没有使用，c没有使用

where a=3 order by b;

a用到了索引，b在结果排序中也用到了索引的效果，a下面任意一段的b是排好序的

where a=3 order by c;

a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort

where b=3 order by a;

b没有用到索引，排序中a也没有发挥索引效果

# 补充五：exists  与 in 谁的效率更高 #

in是在内存里遍历比较，而exists需要查询数据库，所以当B表数据量较大时，exists效率优于in。当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用。

> 1、select \* from A where id in (select id from B);  
> in()只执行一次，它查出B表中的所有id字段并缓存起来。之后，检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表的所有记录。
> 
> 可以看出，当B表数据较大时不适合使用in()，因为它会B表数据全部遍历一次  
> 如：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历10000\*1000000次，效率很差。  
> 再如：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000\*100次，遍历次数大大减少，效率大大提升。
> 
> **结论：in()适合B表比A表数据小的情况 **

> 2、select \* from A where exists (select 1 from B where A.id=B.id);  
> exists()会执行A.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回false，非空则返回true。
> 
> 当B表比A表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行。  
> 如：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。  
> 如：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。  
> 再如：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000\*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快。
> 
> **结论：exists()适合B表比A表数据大的情况**

# 补充六：如何不插入重复数据？ #

### 1、insert ignore into ###

当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。例如：

INSERT IGNORE INTO user (name) VALUES ('telami')

这种方法很简便，但是有一种可能，就是插入不是因为重复数据报错，而是因为其他原因报错的，也同样被忽略了～

### 2、on duplicate key update ###

当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。

例如，为了实现name重复的数据插入不报错，可使用一下语句：

INSERT INTO user (name) VALUES ('telami') ON duplicate KEY UPDATE id = id

这种方法有个前提条件，就是，需要插入的约束，需要是主键或者唯一约束（在你的业务中那个要作为唯一的判断就将那个字段设置为唯一约束也就是unique key）。

### 3、insert … select … where not exist ###

根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：

INSERT INTO user (name) SELECT 'telami' FROM dual WHERE NOT EXISTS (SELECT id FROM user WHERE id = 1)

这种方法其实就是使用了mysql的一个临时表的方式，但是里面使用到了子查询，效率也会有一点点影响，如果能使用上面的就不使用这个。

### 4、replace into ###

如果存在primary or unique相同的记录，则先删除掉。再插入新记录。

REPLACE INTO user SELECT 1, 'telami' FROM books

这种方法就是不管原来有没有相同的记录，都会先删除掉然后再插入。

#  **补充七：**为什么我使用了索引，查询还是慢？ #

我们看下 `explain select * from t;`的KEY结果是NULL

![图片][73fb3da54c52f77d67241bd9d531ec91.png]

（图一）

`explain select * from t where id=2;`的KEY结果是PRIMARY，就是我们常说的使用了主键索引

![图片][fed9f6b392a11f932976103fcb18aeee.png]

（图二）

`explain select a from t;`的KEY结果是a，表示使用了a这个索引。

![图片][53562f1d112ea67dd237ec31bed03efb.png]

（图三）

虽然后两个查询的KEY都不是NULL，但是最后一个实际上扫描了整个索引树a。

假设这个表的数据量有100万行，图二的语句还是可以执行很快，但是图三就肯定很慢了。如果是更极端的情况，比如，这个数据库上CPU压力非常的高，那么可能第2个语句的执行时间也会超过long\_query\_time，会进入到慢查询日志里面。

所以我们可以得出一个结论：**是否使用索引和是否进入慢查询之间并没有必然的联系。使用索引只是表示了一个SQL语句的执行过程，而是否进入到慢查询是由它的执行时间决定的，而这个执行时间，可能会受各种外部因素的影响。换句话来说，使用了索引你的语句可能依然会很慢。**

##  ##

## **全索引扫描的不足** ##

什么叫做使用了索引？

InnoDB是索引组织表，所有的数据都是存储在索引树上面的。比如上面的表t，这个表包含了两个索引，一个主键索引和一个普通索引。在InnoDB里，数据是放在主键索引里的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 19][]

可以看到数据都放在主键索引上，如果从逻辑上说，所有的InnoDB表上的查询，都至少用了一个索引，所以现在我问你一个问题，如果你执行`select from t where id>0`，你觉得这个语句有用上索引吗？

![图片][50e56678b9854f2c53238deacedafd4e.png]

我们看上面这个语句的explain的输出结果显示的是PRIMARY。其实从数据上你是知道的，这个语句一定是做了全面扫描。但是优化器认为，这个语句的执行过程中，需要根据主键索引，定位到第1个满足ID>0的值，也算用到了索引。

所以即使explain的结果里写的KEY不是NULL，实际上也可能是全表扫描的，**因此InnoDB里面只有一种情况叫做没有使用索引，那就是从主键索引的最左边的叶节点开始，向右扫描整个索引树。**

你可以用全表扫描来表示一个查询遍历了整个主键索引树。

而select \* from t where id=2这样的语句，才是我们平时说的使用了索引。他表示的意思是，我们使用了索引的快速搜索功能，并且有效的减少了扫描行数。

# 补充八、**在关系型数据库中如何描述多对多的关系?** #

在关系型数据库中描述多对多的关系,需要建立第三张数据表。比如学生选课,需要在学

生信息表和课程信息表的基础上,再建立选课信息表,该表中存放学生Id和课程Id。

# 补充九、**varchar和****char****的区别** #

Char是一种固定长度的类型，varchar是一种可变长度的类型。

如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个

空格，varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余

的空格，而varchar是不需要的。

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

找。

[B_B_ _-----_520_-CSDN]: https://blog.csdn.net/weixin_41231928/article/details/106652325
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200614225007699.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200721224757176.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[70]: https://img-blog.csdn.net/2018052018171447?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2020061513431947.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200615140831320.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200615140904602.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200615140937548.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200615140958865.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200615141025452.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200615141214867.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200615141234913.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200615141318476.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20200615141335611.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/2020061514143293.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20200615141631799.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/2020061514164999.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/20200615141724617.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 16]: https://img-blog.csdnimg.cn/20200615141845960.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 17]: https://img-blog.csdnimg.cn/20210804193332241.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 18]: https://img-blog.csdnimg.cn/20200615202523665.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[20200615203555141.png]: https://img-blog.csdnimg.cn/20200615203555141.png
[20200615203613905.png]: https://img-blog.csdnimg.cn/20200615203613905.png
[Center]: https://img-blog.csdn.net/20170708134158963?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQva2luZ19rZ2g=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5rKZ5rupZGXmtYHmspk_size_17_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/20211002190611314.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5rKZ5rupZGXmtYHmspk=,size_17,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5rKZ5rupZGXmtYHmspk_size_16_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/20211002194402641.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5rKZ5rupZGXmtYHmspk=,size_16,color_FFFFFF,t_70,g_se,x_16
[20200724194742708.png]: https://img-blog.csdnimg.cn/20200724194742708.png
[2020072421524465.png]: https://img-blog.csdnimg.cn/2020072421524465.png
[format_png]: https://img-blog.csdnimg.cn/img_convert/e19f07972dc3713f2465cd98daed4d44.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/006eaa9e72d4d539a46d9703aea00a97.webp?x-oss-process=image/format,png
[73fb3da54c52f77d67241bd9d531ec91.png]: https://img-blog.csdnimg.cn/img_convert/73fb3da54c52f77d67241bd9d531ec91.png
[fed9f6b392a11f932976103fcb18aeee.png]: https://img-blog.csdnimg.cn/img_convert/fed9f6b392a11f932976103fcb18aeee.png
[53562f1d112ea67dd237ec31bed03efb.png]: https://img-blog.csdnimg.cn/img_convert/53562f1d112ea67dd237ec31bed03efb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA_size_16_color_FFFFFF_t_70 19]: https://img-blog.csdnimg.cn/20210807190157615.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTIzMTkyOA==,size_16,color_FFFFFF,t_70
[50e56678b9854f2c53238deacedafd4e.png]: https://img-blog.csdnimg.cn/img_convert/50e56678b9854f2c53238deacedafd4e.png