目录
在本文将会介绍四部分的内容，分别为：mysql的设计规范，mysql索引，mysql事务以及mysql8新特性。
一 设计规范
在这一部分内容会介绍mysql设计时遵循的范式，mysql字段的基本介绍。
1 范式与反范式
范式简称（NF），他可以理解为是一张数据表的表结构所符合的设计标准的级别。目前关系数据库有六种范式，但是在设计时一般只要满足前三范式就可以了。
第一范式：数据项不可分，第一范式的详细要求是：每一列属性都是不可再分的属性值；两列的属性相近或相似时，尽量合并成一列；单一属性的列为基本数据类型构成。例如我们在进行数据库设计时，一张表中需要保存姓名，性别这些信息，那么我们不能将这些信息存在一个字段中，将它分为两个字段，即不要出现nameSex这种符合形式的字段，而是设计成name，sex这种单一功能的字段。
第二范式：第二范式必须先满足第一范式，要求实体的属性完全依赖于主关键字。所谓完全依赖指的是不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来行程一个新的实体。
第三范式：第三范式必须先满足第一范式，要求一个数据表中不包含已在其它表中包含的非主关键字的信息，数据不能存在传递管理。例如想在有一张订单表，订单表中可以有产品的主键Id信息，而不应该出现产品的描述信息，通过订单表中的产品主键Id去查询产品的描述信息。
但是我们在平常的表设计中，有的时候是不会完全按照三范式进行设计，有时会在表中故意添加冗余字段，为了查询时效率更高。这种方式就是反范式设计。一般我们在进行表设计的时候，会根据实际情况将范式和反范式组合使用。
我们看一下范式和反范式的对比：
| 范式 | 反范式 | |
| 更新效率 | 快 | 慢 |
| 数据重复度 | 低 | 高 |
| 内存占用 | 小 | 大 |
| 表关联 | 多 | 少 |
| 索引命中 | 少 | 多 |
2 字段类型
在我们进行表设计的时候不可避免的要进行字段选择，那么我们在字段选择的时候首先要遵循以下三条原则：
1.数据类型越简单越好
2.字段长度越小越好，因为越小它的操作效率越高，因为他们占用更少的磁盘、内存和cpu缓存；
3.尽量避免使用null，在字段设计的时候，尽量设置为not null，出现项目需要或者该字段为非重要字段，因为mysql对null很难进行优化，并且当值为null时会用到更多的存储空间，尤其是设置为NULL的列是索引时，需要一个额外的字节对其进行记录。
完成字段类型选择以后，在进行字段命名的时候，最好遵循以下原则：
1.可读性原则，要做到见名知意，不要使用缩写；
2.字段名称禁用数据库保留字，例如desc，code，match等；
3.表名，字段名尽量不要使用负数词；
4.表名，字段名的名字尽量都使用小写数字，两个词之间使用"_"分割，不要使用驼峰命名。并且修改字段名以及表名的代价很大，不能灰度发布还需要进行代码调整，所以最好定了就不要再变动。还有一个重要的问题是在windows中不区分大小写，但是linux是区分大小写的(默认情况，可以通过修改配置文件进行调整，变成不区分大小写)。
5.索引的命名，我们常用的索引有主键索引，唯一索引以及普通索引，在命名的时候最好最好分别使用以下名称作为开头，pk_，uk_，idx_。
上面介绍了字段类型的选择原则以及字段的命名规则，那么接下来我们对各个字段类型进行一个简单的介绍。
1）整数
整数在数据库中有以下几种：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT，他们分别占用1,2,3,4,8个字节，也就是8,16,24,32,64位存储空间。当字段类型设置为整数时，还有一个UNSIGNED属性，它是用来控制是否允许存储负数，默认是允许的，如果明确知道这个字段不会出现整数，那么可以设置为不允许，这样存储的范围大约可以扩大一倍。
当设置为整数时是可选一个长度的，例如int(7)，这种设置对于mysql内部的存储和计算是没有意义的，还是使用32位存储空间。还有一点说一下，因为数据库字段的值是可以为null的，所以我们的java程序中实体类的对应字段是使用包装类型而不是使用基本类型，原因就是包装类型可以为null。
2）实数
实数也就是带有小数点的数，mysql中有有以下三种类型：FLOAT，DOUBLE，DECIMAL。但是他们的本质是不一样的，FLOAT，DOUBLE使用更小的存储空间，分别为4,8字节，也就是32,64位。但是DECIMAL底层是采用字符串存储的，可以存储65个数字。在使用的时候他们也有所区别，如果追求效率不追求精度，可以选择FLOAT，DOUBLE；如果追求精度不追求效率，可以选择DECIMAL。
如果既要效率又要精度，我们应该怎么办？我们可以将实数乘10的倍数的方法，变为整数，然后使用整数进行存储，在计算完毕以后，再除10的倍数。
3）字符串
字符串类型主要有两种char和varchar，他们区别是char是定长字符串，varchar是可变字符串。一般情况下varchar的使用场景要比char多，但是为什么还会有char呢？原因是varchar在进行数据存储的时候，会额外使用1或2个字节进行字段长度的存储，字段长度255以下使用一个字节，255以上则使用两个字节。因此如果字符串长度是固定长度，并且长度比较短的时候，使用char的效率会更高。
varchar是可变字符串类型，对于varchar(10)和varchar(100)存储"csdn"时使用的长度是一样的，但是字段长度要越小越好，原因是mysql会划分固定的内存块来保存内存值。还有一点需要注意的是，对于变长字符串进行更新的时候，除了更新字段值以外，还要更新字符串的长度，因此会对效率存在影响。对于一些极端的情况，还会产生页分裂的情况。
4）时间类型
首先需要明确，mysql的时间精度为秒。我们一般使用的是DATETIME以及TIMESTAMP，他们之间的区别是：DATETIME存储的时间范围是1001年到9999年，与时区无关；TIMESTAMP存储时间的范围是1970年到2038年，并且与时区有关。
5）TEXT和BLOB
这两个字段类型是用来存储大数据量的字符串的，TEXT使用字符存储，有字符集以及排序规则；BLOB使用二进制存储。mysql会将TEXT和BLOB当成独立的对象进行处理，在innodb引擎中，当TEXT和BLOB的值过大时，会将值存在指定的存储区域中，然后再表中存储一个指针。
尽量表面使用TEXT和BLOB类型，如果要使用的话，尽量将其放在一个单独的表中。尽量不使用该字段进行数据检索。
6）枚举
该类型在实际项目中很少使用(仅限于本人参与的项目)，做一个了解即可。在创建枚举列的时一般会定义一系列的预定义集合。在表中真实存储的数据不是枚举中定义的字符串，而是保存其对应的索引值。例如ENUM('dog', 'cat', 'pig')，在数据库不是真的存储其中的字符串，而是1,2,3整数。
二 索引
在这一部分会介绍索引的数据结构，执行计划的使用与分析，创建索引时注意的问题。
1 数据结构
mysql的索引使用的是B+tree的数据结构，那么为什么使用B+tree呢。接下来我们看一下不同数据结构的一个对比。
首先我们看一下hash，他存在一系列的hash桶，通过散列算法将数据分配到不同的hash桶上，当发生hash冲突的时候，采用拉链法进行处理。下图大致描述了一下hash做索引时的存储结构：
当我们使用hash作为数据库索引时，它的查询效率是比较高的，但是对于很多场景他是不能满足要求的。hash索引无法进行范围查找；hash索引无法进行排序；hash索引没办法支持联合索引；针对目前越来越大的数据量，hash索引会经常出现hash冲突，对插入和查询的效率都会有很大的影响。目前hash索引还是有使用的，memory引擎采用的就是hash索引。
接下来我们介绍一下B+树，B+树是从平衡二叉树演化而来的，为什么不采用平衡二叉树作为索引的数据结构呢？很简单，如果采用平衡二叉树的话，针对目前的数据量，树深度会很大，那么检索效率会很低。
B+树的每个节点都可以有多个子节点，而不仅限于两个，这样就能大大减少了树的深度。mysql的数据是存储在磁盘中的，那么减少磁盘的IO次数就能提高mysql的检索效率。mysql每次读取的时候不是严格的按需读取，而是进行预读，也就是每次都会读取页的整数倍（硬件中的存储单位，一般为4k）。在innodb中，每个B+树的节点大小为16k，假如每个key的大小为8字节，那么一个节点可以存放大约1000个key。及时每次读取一个节点也能获取到1000条数据。innodb读取磁盘数据的时候，是16k的倍数。这里就涉及到为什么采用B+树而不是B树，原因就是B树的每个节点都可以存储数据，而B+树只有叶子节点才会存储数据，这样就会导致mysql每次读取非叶子节点的数据时，B+树能够读取到更多的数据。接下来我们看一下B+数的数据结构。我们借助一个在线工具来生成B+树（B+ Tree在线生成工具）。
在B+树中进行检索的时候，采用的是二分查找的理念，提高了检索效率。只在叶子节点上面存储数据，当进行数据检索时，每次拉取的数据量要大于B树，这样就提高了检索效率。在mysql的B+树，叶子节点之间的链表是一个双向链表（B*树非叶子节点之间也有相互的指针指向，Oracle中的索引使用的是B*树）。
2 索引类型
索引类型主要分为两大类，聚簇索引和非聚簇索引。非聚簇索引中又包含了普通索引，联合索引以及全文索引，这里说的索引指的innodb引擎中的索引，不同的存储引擎其索引是有差别的。innodb是我们平常使用中最常用的存储引擎。
1）聚簇索引
聚簇索引指的使用表的主键构建的B+树索引，如果表没有主键则会使用唯一索引，如果唯一索引也不存在，mysql会创建衣隐藏的ROWID来做主键。每个表只能存在一个聚簇索引。
聚簇索引的非叶子叶子节点中都是主键信息，不包含数据信息，在叶子节点中保存数据信息。它的数据结构如下
2）非聚簇索引
我们在平常的sql语句中，其实使用主键作为查询条件的时候是很少的，此时就需要创建除主键以外的其它索引，这些索引就是非聚簇索引，也称为二级索引/辅助索引。我们针对表创建的每一个索引都是一个B+树。一般一条简单的sql语句只会命中一个非聚簇索引。
非聚簇索引主要是针对一个列的普通索引以及多个列的联合索引。全文索引是很少使用的，在mysql5.6之前是不支持全文索引的。虽然现在的版本支持了全文索引，但是整体上退全文索引的支持并不是很好，如果要使用全文索引这种场景，建议将数据存储到es等搜索引擎中进行使用。
普通索引和联合索引其实是很相似的，联合索引的数据结构是在普通索引上的进一步处理，接下来我们先看一下普通索引的数据结构。
我们可以看到针对普通索引，在叶子节点中存储的该表的主键数据，而不是详细数据，如果我们select的数据不只是索引数据，而需要除索引以外的其它数据，就需要根据叶子节点中的主键值再去这张表的聚簇索引中获取其详细数据，这种操作就是回表操作。回表操作是会影响效率的，在一些场景下其效率可能还不如全表扫描，尤其是在表数据量不大时。
接下来我们看一下联合索引的数据结构，它和普通索引的数据结构类似，不同点也是在叶子节点上面进行体现。
我们在创建联合索引的时候，都是针对多个列的，其中主要使用联合索引中的第一列进行B+树的构建，然后在叶子节点中补充联合索引的其它列的数据以及主键数据。需要注意联合索引的排序规则也是按照创建联合索引时列的顺序进行的排列。当我们使用联合索引进行查询时，查询的值也是该联合索引所包含的列时，这时是不需要进行回表操作的，而是直接从叶子节点中获取，这种操作成为索引覆盖。索引覆盖的效率较高，也是我们推荐一种方式，如果联合索引中的值能够满足查询要求，则不要使用select *进行查询。
3 索引的创建与使用
在我们进行表设计的时候，索引的创建也是一个重要的设计点。我们在进行创建索引的时候，需要注意以下几点：
1、索引列的数据类型尽可能的小，在上面我们介绍B+树的时候说过，mysql每次从磁盘中获取16k的整数倍的数据，索引的数据类型越小，每次获取到的数据量越大，IO次数也就越少，那么检索的效率也就越高。这一条建议主要针对的是聚簇索引，我们一般采用自增列作为主键；
2、索引列数据的离散性（非重复数据/总数据，范围为0-1，值越大，离散性越好）要大一些，如果它的离散性很小的话，那只能过滤很少一部分数据，那么这个索引的意义就不是很大。例如我们一般不会给性别添加索引，因为性别的离散型0.5左右，他能够过滤的数据会很少，性价比不高；
3、针对很长的字符串数据，例如TEXT，BLOB，varchar(2000)这种，如果直接使用整列作为索引，每个key值就很大，那么会使IO次数增多。针对这种情况，我们可以进行创建前缀索引的方式来减少每个key的大小。创建语句如下：
-- n为前缀索引的长度
Alter table tableName add index (column(n));但是需要注意，前缀索引不能进行order by和group by操作，并且也不能使用索引覆盖操作。
4、创建索引的时候一般只使用where条件中常用的列进行索引创建，select的数据一般不需要进行考虑，除非必须使用到的覆盖索引才需要去考虑select的数据；
5、在创建联合索引的时候一定要严格限制它的顺序，它遵循最左匹配原则，会先根据最左测的列进行排序并构建B+tree的主索引树，然后在叶子节点根据第二个索引列进行排序，以此类推，可以参考上面的联合索引的B+树结构示意图；
我们衡量索引的创建是否是高性能的，我们有一个三星标准，当然这三星标准也只是一个参考：
第一星：索引扫描范围越小越好，也就是能够过滤的数据越大越好，权重占比为27%；
第二星：当查询语句进行排序分组时，其排序条件和分组条件是能够命中索引的，这样就避免了二次排序的问题，权重占比为23%；
第三星：索引中的列能够满足select的数据，也就是索引覆盖，这样就无须进行回表操作，权重占比为50%。
在上面我们介绍了一些索引的创建原则，当索引创建完成以后，我们在进行索引使用的时候也有一些注意点：
1、在where条件中，不对索引列进行函数操作，不然索引不生效；
2、针对联合索引，我们尽量进行全字段匹配，当进行全字段匹配的时候，其实当我们的字段顺序与联合索引的顺序不一致时也会生效，因为mysql的优化器会替我们进行sql优化，但是还是建议按照顺序使用，因为我们无法操作mysql的优化器，可能会出现问题；如果我们的where条件不能全值匹配的时候，我们要遵循最左匹配原则，也就是如果联合索引为('a','b','c')，我们在使用的时候只有('a')，('a','b')，('a', 'c')，('a','b','c')这四种情况联合索引才会生效，但是('a', 'c')这种方式，我们其实只用到了联合索引中的a；还有就是返回查询的时候，只有a的范围查找才能命中索引，即使我们按照顺序使用，也只有最左字段会命中索引；
3、在进行查询是，如果索引覆盖能满足要求，则使用索引覆盖，不要使用select *，减少回表操作以及磁盘IO;
4、不要使用不等于（!=, <>），它会导致索引失效；
5、慎用is null/not null，对于没有覆盖索引的操作，is not null会导致全表扫描。如果字段不允许为null的情况，mysql会返回永远不存在（后续再介绍explain的时候会介绍）；如果允许为null，会命中索引。但是当触发索引覆盖操作时，则会触发索引，is not null的效果会好一些；
6、在使用like时，左侧不能添加通配符，否则无法命中索引；
7、在where条件中一定要注意好字段类型，如果出现数据类型转化的话，则无法命中索引，例如字段a为数值类型并建立了索引，如果写a='1'，那么索引将不会生效；
8、or的使用，例如现在对字段a和字段b分别创建了索引，在where条件中使用a=1 or b=10，其实相当于使用了union all进行了两次查询；
9、当使用order by排序时，如果是联合索引，要遵循最左匹配原则。不能ASC和DESC混用，如果order by的列不在同一个索引中，索引也不会生效；
下面还有一些与索引无关的注意点：
1、在使用count进行数据统计的时候，如果单个字段，会排除值为null的数据；
2、limit分页优化，如果偏移量很大的时候，mysql会扫描很大的数据量，我们可以添加上一些必须会满足的条件来减少数据扫描量；
3、null的特殊说明，在mysql的innodb引擎中，通过对innodb_stats_method的值的设置，来对null的含义进行设置。nulls_equal认为所有null都是相等的，也是innodb默认值；nulls_unequal认为null不相等；nulls_ignored忽略null。
4 执行计划explain
我们平常写的查询sql，我们可以在sql语句之前添加expalin来查询其执行计划。通过查询计划我们可以知道它是否命中索引，命中了哪个索引，扫描了所少数据行等，有助于我们对于慢sql进行进一步分析其原因。
在介绍explain之前，我们先介绍一个概念，慢查询。mysql中慢查询的日志是默认关闭的，我们需要将其进行打开，还有一系列的相关设置：
-- 查询是否打开慢查询日志，默认关闭
show VARIABLES like 'slow_query_log';
-- 打开慢查询日志，1-打开，0-关闭
set GLOBAL slow_query_log=1;
-- 慢查询的阈值，当一条sql语句的执行时间超过该阈值时，被定义为慢查询，默认为10s
show VARIABLES like '%long_query_time%';
-- 设置慢查询的阈值
set global long_query_time=10;
-- 查询是否记录未使用索引的sql，默认为不记录
show VARIABLES like '%log_queries_not_using_indexes%';
-- 记录未使用索引的sql，1-记录，0-关闭
set global log_queries_not_using_indexes=1;
-- 获取慢查询的日志位置
show VARIABLES like '%slow_query_log_file%';所谓慢查询，也就是花费大量时间的查询，那么造成慢查询的原因是什么呢？主要有两个：
1、访问了大量的数据行，但是这些数据行中有部分数据是无效数据，一种情况我们使用limit时，它会查出所有结果集，然后只返回有效结果集；一种情况是使用select *的情况，当使用非聚簇索引时，永远都是回表操作，不能执行索引覆盖；
2、mysql在获取指定数据行时，扫描了大量数据。这个与上面的区别是，上面是mysql已经返回了大量的无效数据；而这一点是mysql返回的数据都是有效数据，但是在获取这些有效数据时，它扫描了大量的无效数据行，例如未使用索引直接进行了全表扫描。
简单衡量查询开销的指标有三个：
1、响应时间，包含服务时间以及等待时间，服务时间是mysql处理这个sql真正花费的时间，而等待时间是等待某些操作或者资源时消耗的时间；
2、扫描的行数
3、返回的行数
在进行数据分析时，可以通过分析扫描的行数以及返回的行数，来判断本次效率的高低，值越低，证明效率越高，最好的情况就是扫描行数=返回行数。
当我们获取到慢查询sql时，我们要如何进行分析呢？这就要用到explain，执行计划了，它的使用方式很简单就是在我们的查询语句之前添加explain即可。
我们可以通过这些字段对我们的sql进行进一步的分析，接下来对每个字段进行说明。
id：在一个查询sql中，每一个select都对应一个唯一的id。需要注意的是，当我们使用union时，在执行计划中会出现一个没有id的执行计划数据。
还有一种特殊情况，当我们使用子查询时，两个执行计划的id的值可能一样，这是mysql内部的优化器对sql语句进行了优化。
table：表名，会记录每个select语句查询的表的名字，对于union查询其对应的table是<union1,2>，如上图；
partitions：匹配的分区信息，这个值不用过多关注，大部分情况为NULL；
type：针对单表的访问方法，这是在进行sql分析的时候一个重要的指标，我们下面只介绍常用的值，他们代表的结果从好倒换为system->const->eq_ref->ref->range->index->all。system就不进行介绍了，它在实际情况下是不会出现的，只有当数据引擎的统计数据是准确的，例如MYISAM时，并且表中只有一条记录。
1)const是我们在实际场景中能够达到的最好效果，当我的where条件使用了主键或者唯一索引进行查询时，它的type就是const；
2)ep_ref是针对联合查询时，如果被驱动表是通过主键或者唯一索引进行查询时，被驱动表的type是eq_ref，那么什么是被驱动表呢？在关联查询时肯定是以其中一个表作为基础轮询表，然后通过轮序该表的数据去查询另一个表的数据，这个基础轮询表则是驱动表，而另一个表这是被驱动表；
3)ref使用普通的二级索引进行数据查询时，其type为ref；
4)range使用索引获取范围区间的记录，例如in，between，<等；
5)index当我们使用覆盖索引，但是需要扫描全部的索引记录；
6)all全表扫描。
在我们进行sql优化时，一般最低要到range级别，才是一个可接受范围。
possible_keys：可能用到的索引
key：实际用到的索引
key_len：实际使用到的索引长度，使用到的索引列的字段长度，对于固定长度的索引列，其最大长度为固定值，例如varchar(50)，使用utf8编码，它的长度为50*3，如果允许为空，在多一个字节，如果是变长字段，则会有2个字节存储其长度。例如现在有一个组合索引('a', 'b')，a为int，b为varchar(30)，允许为空，并且这个联合索引全部命中，则key_len的长度为4+30*3+1=95。
ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows：预估需要读取的记录条数
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比
extra：额外信息，例如当我们使用覆盖索引时，这里会显示Using index；如果全表扫描时，使用where条件，会显示Using where；如果字段不为空，但是要查询该字段为空的数据，则会显示Impossible WHERE。
三 事务
在这一部分会介绍mysql的事务隔离级别以及mysql的锁。事务相信大家都已经了解了，事务有四大特性：
1、原子性，一个事务是一个不可分割的执行单元，只能有成功和失败两种状态，不能出现部分成功，部门失败的情况；
2、一致性，数据库从一种一致性转为另一种一致性的状态，事务开始之前和结束之后数据库中数据的完整性不会被破坏；
3、隔离性，两个事务之间是相互隔离的，不能互相干扰；
4、持久性，一旦事务提交，则其所作的修改就会永久保存到数据库中，不会丢失。
1 隔离级别
在介绍隔离级别之前，我们先介绍几种如果数据库没有进行任何特殊处理，发生并发以后，可能出现的问题。
1、脏读，指一个事务读取到了另一个未提交事务的中间值
2、不可重复读，一个事务内发生了两次读取，在这两次读取的中间，另一个事务对该数据进行了更新操作，那么两次读取获取的值就是不一致的；
3、幻读，一个事务内发生了两次读取，在这两次读取的中间，另一个事务进行了数据新增（insert）操作，这时两次读取的数据是不一致的，幻读和不可重复读十分类似，他们的区别是，幻读针对的是insert操作，不可重复读针对的是update操作；
数据库为了解决上述问题，制定了隔离级别来解决上述问题，然后数据库根据这些隔离级别进行具体的功能实现，隔离级别有四种，如下：
| 脏读 | 不可重复读 | 幻读 | |
| read uncommit(读未提交) | 出现 | 出现 | 出现 |
| read commit(读已提交) | 不出现 | 出现 | 出现 |
| repeatable read(可重复读) | 不出现 | 不出现 | 出现 |
| serializable(串行化) | 不出现 | 不出现 | 不出现 |
这是SQL标准，mysql默认的隔离级别是repeatable read，但是mysql自己进行了一定的优化，解决了大部分的幻读问题，只有一些特殊情况的幻读问题会出现，mysql解决幻读问题的方式是mvcc以及间隙锁，在后面会有相关的讲解。可以通过以下的语句进行隔离级别的查看以及修改。
-- 查询当前数据库的隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';
-- 全局范围修改隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 会话范围修改隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 下一个事务修改隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;上面介绍了事务的隔离级别以及可能出现的问题，那么事务应该如何使用呢？可以通过一下语句开启，提交或者回滚事务。
-- 事务开始
begin;
start transaction;
-- 事务提交
commit;
-- 事务回滚
rollback;这里有一点需要注意，开启一个事务以后并不是必须通过commit或者rollback才会结束事务，mysql中存在隐式提交的情况，有以下几种情况会触发隐式提交(数据库没有设置默认提交)，需要注意是在一个session中：
1、执行DDL语句，在一个事务执行过程中执行DDL语句则会触发隐式提交；
2、在一个事务执行过程中，又开启另一个事务，那么前一个事务会触发隐式提交；
3、在事务执行过程中执行，执行一些特殊语句，例如LOAD DATA批量导入数据；START SLAVE，CHANGE MASTER TO等一些主从数据库的相关语句；CREATE USER，GRANT等操作mysql系统表的语句。
在我们事务的执行过程中还可以设置SAVE POINT，它的作用是在进行事务回滚的时候，不会将所有操作全部回滚，而是回滚到指定的SAVE POINT，可以保留部分操作结果，它的语法如下：
-- 开启事务
begin;
-- 设置第一个save point
insert ...
savepoint a1
--设置第二个save point
update ...
savepoint a2
insert ...
-- 回滚到a1
rollbak to savepoint a12 mvcc(多版本控制)
mvcc-Multi-Version Concurrency Control，即多版本发控制，它主要是解决在并发读写时，读数据时不进行加锁，提高查询性能。在介绍mvcc之前，需要先介绍一个重要概念，版本链。
对于innodb存储引擎的聚簇索引的记录中都会包含两个伪列：1）trx_id：事务id，每个事务有一个属于自己唯一ID，同一事务的trx_id是同一个；2）roll_pointer：指针，我们在进行数据修改时，会将将旧版本的数据写入undo日志中，这个指针就是指向旧版本记录。
下面用图示的方式进行一个说明，假设现在有一张表，然后进行如下的操作：
-- 第一个事务
begin;
insert into table values(1, 'a');
commit;
-- 第二个事务
begin:
update table set name = 'b' where id = 1;
commit;
-- 第三个事务
begin:
update table set name = 'c' where id = 1;
update table set name = 'd' where id = 1;
commit;针对上述操作就会出现如下的一个版本链：
针对一条数据，会形成如上图所示的一条版本链，版本链的头结点就是当前记录的最新值。
在介绍mvcc如何解决脏读，可重读以及幻读问题之前还有一个概念要进行介绍，innodb中ReadView(视图的概念)，针对ReadView有四个重要的参数：
m_ids：在生成ReadView的时候当前系统中活跃的事务id列表；
min_trx_id：表示生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值；
max_trx_id：生成ReadView时系统应该分配给下一个事务的id值，这里需要注意的是它不是m_ids中的最大值；
creator_trx_id：表示生成该ReadView的事务id。
接下来我们根据上面的版本链以及ReadView来看一下它是如何解决脏读，不可重复读以及幻读的。在数据库的隔离级别中read commit和repeatable read都解决了脏读问题，但是read commit存在不可重复读的问题，其实就是read commit和repeatable read生成ReadView的时机不同。read commit在一个事务中每次查询的时候都会生成一个ReadView，但是repeatable read在事务开始时第一次查询会生成ReadView，后续查询时都会使用该ReadView。 、
我们先看一下在read commit中情况，我们以上面那个版本链为例，现在有一个事务开启然后进行查询。
第一次查询，事务10和15都未提交，那么此时的ReadView:m_ids:[10, 15],min_trx_id:10,max_trx_id:16,creator_trx_id:0，在进行数据读取的时候去查询目前的版本链，第一条数据事务id为15，是活跃事务(未提交事务)，继续往下，事务id还是15，再往下，事务Id为10，还是属于活跃事务，在往下，事务id为为6，不是活跃事务，则读取该数据，获得的值为a。
第二次查询，事务5未提交，那么此时的ReadView:m_ids:[15],min_trx_id:15,max_trx_id:16,creator_trx_id:0，在进行数据读取的时候去查询目前的版本链，第一条数据事务id为15，是活跃事务(未提交事务)，继续往下，事务id还是15，再往下，事务Id为10，不是活跃事务，则读取该数据，获得的值为b。
从上面这两次查询中，我们会发现，它每次读取的值都是已经提交的事务的值，解决了脏读的问题，但是由于每次读取都会生成一个ReadView，两次生成的ReadView是不同的，所以出现了不可重复读的问题。
接下来我们在看一下repeatable read的ReadView生成过程，还是以上面的例子说明。现在开启一个查询事务，第一次读取生成的ReadView为m_ids:[15],min_trx_id:15,max_trx_id:16,creator_trx_id:0，读取到的值为a，在进行第二次读取的时候，有repeatable read只在第一次查询时生成视图，后续查询时都会继续使用第一次生成的这个视图，因此获取到的值还是a，也就解决了不可重复读的问题。
注意：如果当前事务的creator_trx_id的值如果相同，在当前事务中修改了要查询的值，那么读取到的值是当前事务修改的值；小于ReadView中min_trx_id值的事务id，其事务在生成ReadView前就已经提交了。
在上面解释隔离级别解决的问题时，提到过标准的repeatable read是无法解决幻读问题的，但是mysql的repeatable read是能够解决部分幻读问题的，它的解决办法就是通过mvcc解决的。只要生成了ReadView以及版本链，幻读问题也就解决了，其流程和解决不可重复读问题是一样的。但是需要注意他解决幻读问题的两个必要条件：ReadView和版本链。
现在有一个场景，事务1第一次查询id = 2的数据，此时生成ReadView，但是版本链不存在，查询完以后事务2往里面插入了一条id=2的数据并提交，事务2提交以后，事务1又去更新id=2的数据，在去查询id=2的数据，此时是可以查到的，因为事务1中的creator_trx_id是一样的，所以第二次读取的时候是可以读取到更新以后的数据。
mvcc解决的是普通读时的数据安全问题，防止事务对读产生影响，这是不用加锁的方式，效率较高，当然我们也可以采用加锁的方式来保证数据的准确性，但其效率会受到影响。
3 锁
锁其实也是用来解决并发时的数据一致性问题，mvcc主要针对的是读操作，写操作使用的这是锁，当然读操作也可以采用加锁的方式保证数据一致性，但是其效率会受到很大的影响。
锁不同的维度会有不同的划分：共享锁和排它锁是一个维度，表锁和行锁是一个维度。
1）共享锁和排他锁
共享锁：简称S锁，共享锁一般是出现在读取操作中，要读取数据时使用锁的方式保证数据一致性的话，则需要先获得该记录的共享锁，共享锁可以让两个及以上的事务获取，但是目前存在共享锁，则该记录不能添加；
排他锁(独占锁)：简称X锁，如果一个事务要改动某条记录，则必须获取到它的排他锁，当该事务获取到该记录的排他锁以后，该记录的锁在释放之前，不能再被其它事务添加任何锁，只能进行等待。
-- 共享锁
select * from table where id = 1 lock in share mode;
-- 排他锁,当事务提交时，锁会取消，在该事务提交之前，其它事务如果想要获取该锁，需要进行等待
select * from table where id = 1 for update;不同的写操作其加锁的过程是不同的，insert语句一般是不需要进行加锁的，innodb会通过一隐式锁来保护这条插入的记录在事务提交之前不被别的事务访问。delete语句就是获取要删除数据的X锁。
下面着重说一下update语句，update语句更新时有三种情况：1）数据的键值不发生变更并且储存空间也不发生变化，那么只需要获取该记录的X锁即可；2）数据的键值不发生变更但是存储空间发生变化，那么则获取该数据的X锁，然后删除该数据，在插入一条新数据；3）数据的键值发生变更，获取该数据的X锁，然后删除该数据，再插入一条数据。
行锁和表锁
根据锁的粒度进行划分的话，可以分为行锁和表锁。需要注意，当一个事务获取到表的S锁以后，其它事务可以获取这个表的S锁或者表中某些数据的S锁，但是其它事务不能获取到数据该表的任何X锁；如果一个事务获取到该表的X锁，那么其它事务将不能获取到与这张表有关的任何锁。
现在有一个场景，比如事务1获取到A表中id=1的行级X锁，此时事务2要获取该表的表锁，如果按照一般流程，事务2检查该表有没有表锁，发现没有，就需要遍历该表的每条数据，判断其有没有锁，如果真的按照这种流程进行处理的话，将会十分耗时，效率就会很低。因此，就有了意向锁。
意向锁并不是一把真的锁，可以把它理解成一种标识，我们尽量不要手动添加意向锁，当我们触发锁操作时，mysql内部会自动添加对应的意向锁。意向锁的作用其实就是避免事务进行数据的遍历来判断是否具有行级锁。需要注意意向锁是针对表而言的而不是针对数据行的。意向锁分为两种：1）意向共享锁，IS；2）意向排他锁，IX。下面看一下S,X,IS,IX的兼容关系：
| X | S | IX | IS | |
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
| S | 不兼容 | 兼容 | 不兼容 | 兼容 |
| IX | 不兼容 | 不兼容 | 兼容 | 兼容 |
| IS | 不兼容 | 兼容 | 兼容 | 兼容 |
在mysql除了innodb以外，其它引擎是没有行级锁的，行级锁属于innodb引擎的独有。先介绍一下innodb中的表锁，其实我们一般情况下是很少会用到表级的S锁或者X锁的。再执行select、insert、update、delete时，innodb引擎是不会添加表锁的。如果一个事务先执行DDL语句，另外一个事务再执行DML语句时，会发生阻塞，等待执行DDL的事务执行完成再执行DML；反之亦然，一个事务先执行DML语句，另一个事务再执行DDL语句，也会发生阻塞。这个过程其实是server采用了一种元数据锁(metadata Locks)实现的。
当表中存在auto_increment的列时，在我们插入数据时，会出现两种加锁方式来实现递增：
1）采用AUTO_INC锁，它是一个表级锁，在插入数据时先获取该表级锁，然后为插入数据分配递增值，插入完毕以后，释放该锁，使用场景为不确定插入的条数；
2）使用轻量级锁，当我们能确定插入多少行时，会先获取该轻量级锁，然后获取到需要分配的自增值，就释放该轻量级锁，无需等待语句执行结束。
在mysql中通过设置innodb_autonic_lock_mode的值来决定采用哪种策略，0代表使用AUTO_INC锁，2代表使用轻量级锁，1代表AUTO和轻量级锁混合使用。mysql5.7的默认值是1。
-- 查看当前数据库真多auto_increment使用哪种锁
show variables like 'innodb_autoinc_lock_mode' ;接下来着重介绍一下innodb的行级锁，需要注意的是，mysql只能给索引列添加行级锁，如果sql语句的执行不能命中索引的话，它是没办法进行使用行级锁的，只能使用表级锁。只有执行计划能够命中索引，才能使用行级锁，mysql本身是存在优化器的，有时即使where语句中命中索引，但是mysql执行时认为全表扫描的效率更高，它就不会使用行级锁，所以一定是在执行计划中命中索引才能使用行级锁。
间隙锁
mysql采用的隔离级别是repeatable read，但是它解决了部分幻读问题，如果没有锁的情况，使用的mvcc解决幻读，如果使用锁的话，就是依靠间隙锁（gap locks）实现的。需要注意间隙锁也是针对索引而言的。它会将当前操作记录命中的索引与前后相邻的索引之间进行加锁，在这个范围之内，在上一个事务结束之前，是无法插入数据的。
死锁
死锁是发生在两个及以上事务才能触发的，多个事务彼此之间争夺彼此所占的锁而进行阻塞的状态。但是mysql会自动进行死锁处理，防止出现阻塞。
事务A目前占用a锁，事务B占用b锁，此时A先获取b锁，由于B占用，此时事务A阻塞，如果此时事务B要去a锁，mysql会检测到死锁情况，此时会将事务B结束并回滚，然后事务A继续运行。
-- 查看当前设置是否显示死锁信息，默认为OFF，不显示
show variables like 'innodb_status_output_locks';
-- 开启
set global innodb_status_output_locks = ON;
-- 查看死锁信息
show engine innodb status\G四 mysql8新特性
在这一部分会介绍mysql8中一部分常用的新特性，如果要进一步了解，可以去mysql官网进行查看。
1 账户创建
在8之前可以使用一条语句完成用户的创建与授权
grant all privileges on *.* to 'name'@'%' identified by 'pwd';但是在8之后需要用两条语句才能完成用户的创建与授权
create user 'name'@'%' identified by 'pwd';
grant all privileges on *.* to 'name'；在8之前身份认证插件使用的是mysql_native_password，我们使用客户端时可以直接进行验证登录，但是在8之后认证插件改为caching_sha2_password，如果使用的不是8以后的客户端，无法进行验证登录。当然我们可以采用修改配置文件的方式，将my.cnf文件中default-authentication-pligin=mysql_native_password打开即可，然后重启mysql。除了这种方式以外还可以通过sql动态修改其认证登录方式
alter user 'name'@'%' identified with mysql_native_password by 'pwd';在8之前，密码是不允许修改为以前的值的，但是在8之后针对这一规则进行了调整，默认是支持修改为以前的值，但是可以通过设置其参数，修改其策略。
-- 查看当前的密码规则设置
show variables like 'password%';
-- 修改的密码不能和最近5次一致（全局）
set persist password_history=5;
-- 修改的密码不能和最近5次一致（指定用户）
alter user 'name'@'%' password history 5;
-- 一天之内不允许密码重复
set persist password_reuse_interval=1
-- 校验旧密码（针对非root账户）
set persist password_require_current=on;2 索引增强
1）隐藏索引
我们知道每一个索引都是一个B+树，当对一个表进行索引创建时，是消耗性能的，因为它需要根据现有的数据创建B+树。并且我们也不能十分确定是否要建立这个索引，在8以后我们可以创建一个隐藏索引，在进行查询的时候该索引是不可见的，不可见也就不会被使用，不会对享有程序造成冲击。创建隐藏索引的sql语句：
create index index_name on table_name(clumon) invisible;需要注意的是，隐藏索引只是在查询的时候不可见而已，但是这张表新增数据时，同样需要维护这个隐藏索引。我们在进行sql的执行计划分析的时候需要先设置一个参数，才能观察到隐藏索引的执行计划：
-- 查看各种参数
select @@optimizer_switch\G;
-- 在会话级别设置查询优化器可以看到隐藏索引
set session optimizer_switch="use_invisible_indexes=on';执行完上述的语句以后，我们在通过explain查看执行计划，就会看到隐藏索引的执行计划。需要注意的是隐藏索引执行时出现在非聚簇索引上面。隐藏索引和非隐藏索引可以相互转换：
-- 将隐藏索引变为正常索引
alter table table_name alter index index_name visible;
-- 将正常索引变为隐藏索引
alter table table_name alter index index_name invisible;
2）降序索引
在8以后，mysql可以设置降序索引，需要在创建索引时添加desc关键字。需要注意的是，只有innoDB存储引擎才能支持降序索引。还有一点需要注意，在8.0以后mysql不再进行排序。
3）函数索引
在8.0以后引入了函数索引的概念，需要注意的是，这里的函数索引指的不是当对索引使用函数时，索引继续生效，而是在创建的时候直接创建对应的函数索引，创建语句：
-- 对指定字段创建一个大写函数的索引
create index index_name on table_name( (UPPER(column)) );详单与是会出现一个指定字段的大写的伪劣，然后用这个列构建B+树，这样我们就可以使用upper这个函数了。
3 窗口函数
窗口函数也被称为分析函数，它和聚合函数类似，都会生成一列统计数值，但是窗口函数比聚合函数更加的灵活。在我们使用聚合函数的时候，如果在select中除了聚合函数以外还需要额外的列，需要添加group by子句，将额外的列放在其后面，但是窗口函数时没有这个限制的，接下来我们通过两条sql来比较一下。
-- 聚合函数,根据a列进行分组，统计每组的总数量
select a, sum(number) from table group by a;
-- 窗口函数, 也是根据a分组，统计每组的总数量，但是窗口函数select子句可以添加任意字段
-- 并且无需group子句，窗口函数通过over子句实现，partition后面是需要进行分组统计的字段
select a, b, sum(number) over (PARTITION by a) as total from table;
-- 统计累计值
select a, b, number, sum(number) over (partition by a order by number rows unbounded preceding) and total_number fron table;除了我们常用的聚合函数以外，mysql8还提供了一些专有的窗口函数，如下：
1、序号函数，也就是给数据添加对应的排序序号，有三个：1）ROW_NUMBER,序号不会重复，值为1,2,3,4这种样式；2）RANK()，值会重复，但是排序是间断的，值为1,1,3,3,5这种样式；3）DENSE_RANK()，值会重复，但是排序是不间断的，值为1,1,2,2,3这种样式；可以使用以下这样的sql语句进行测试，看看其效果
select a, b, number, row_number() over (order by number) from table;
select a, b, number, rank() over (order by number) from table; 2、分布函数，1）PERCENT_RANK()，累积百分比，计算规则是：小于该条记录值的所有行记录/该分组的总行数-1；2）CUME_DIST()，累积分布值，分组值小于等于当前值的行数与总行数的比值；
3、前后函数，1）LAG(column, [N, [default]])，从当前行开始往前取N行，N的默认值为1，如果不存在前面的行，则返回默认值default，默认为null；2）LEAD(column, [N, [default]])，从当前行往后取值，用法同LAG；
4、头尾函数，1）FIRST_VALUE(column)，返回当前分组内截止当前行的第一个值；2）LAST_VALUE(column))，返回当前分组内截止当前行的最后一个值；
如果想要进一步了解，可以去mysql官网查看：窗口函数。
4 通用表达式
在8.0以后，mysql还是支持通用表达式（CTE, common table expression），使用with子句。我们看一下简单的例子：
-- 通过with子句创建一个中间表cte,它的名字命名没有限制
-- 小括号中的内容相当于递归
WITH recursive cte(n) as
( select 1
union ALL
select n+1 from cte where n<10
)
-- 查询这个中间表的值
select * from cte;5 其它
1、DDL语句支持原子操作，在8.0之前DDL语句是不支持原子操作的，比如同时操作了两个DDL语句，即使其中一个失败了，另外一个也不会进行回滚；
2、InnoDB存储引擎也进行了增强，在8.0之前的版本，自增计数器是保存在内存中的，但是在8.0以后将其写入了redo log中进行了持久化，解决了自增字段可能出现重复的问题；可以通过alter语句修改表空间；针对锁定语句可以添加额外选项，共享锁和排它锁开始支持NOWAIT(如果有锁立即返回)和SKIP LOCKED(返回结果集移除锁定行)。
3、JSON增强，mysql在8.0以后对JSON进行增强处理，可以创建JSON，并且提供一些相关操作，感兴趣可以去官网查看（JSON mysql8.0）。
869
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
