本篇博客是MySQL的学习笔记，若文章中出现相关问题，请指出！
所有博客文件目录索引：
- 其中包含了datadir(数据库中数据目录的位置)，pid-file为存放mysql运行的进程pid号文件通过使用cat命令即可查看进程号。获取到进程号之后可使用
kill pid 来关闭数据库。
两种修改配置文件方式：
-
4.4、使用索引的问题
4.4.1、索引究竟何时使用？
引言：索引十分重要，当我们的查询SQL开始变慢时，就应该考虑去建立索引，根据SQL的查询条件来定建立指定索引，可以是主键索引、普通索引、复合索引…，建立索引能够大大提升我们的查询速度，但是建立索引会占用额外的磁盘空间，并且对于插入、删除操作不仅要对原有数据进行修改，还要去维护对应的B+树索引。
对于使用没有建立索引的字段作为查询条件时会进行全表扫描，若是单表数据量特别大且查询条件结果较少的话，其查询性能会特别差，此时就应该建立索引。但也不是所有表你都需要建立索引，根据情况来建，因为建立索引也会带有坏的影响，例如影响更新速度，若是建立了索引查询速度并没有多大效果，却影响了原本的更新速度，那么就称为过度索引。
下面列出一些场景来作为你是否要建立索引的参考示例
(1)、适合建立索引场景
- 首先并不是建表时就直接建立索引，而是当你的查询SQL出现性能问题时才要去考虑建立索引。
- 与其他表关联字段可建立索引，如表中的外键。例如：
user表中的stu_id作为外键引用student表的id。(stu_id设置索引)
- 排序字段可建立索引。例如：
order by 字段1。(字段1设索引)
- 分组字段可建立索引，因为分组的前提是排序。例如：
group by 字段1。(字段1设索引)
(2)、不适合建立索引场景
- 需要频繁更新的字段不适合建立索引。
-
where条件中用不到的字段不适合建立索引。
- 表数据确定比较少(几百、千、万条)不需要建立索引。
- 对于数据重复且发布比较均匀的字段不适合建立索引，唯一性太差的字段不适合建立索引。例如：性别(男、女)，真假(true,false)，像这些字段在不同记录中重复情况过多也不建议建立索引。
- 参与列计算的字段不适合建立索引，索引会失效。例如：
where age+1=28，age索引字段就会失效，规避失效方式改为where age = 27。
- 若是在字段上进行计算，那么在索引树上也会进行计算那么不就破坏了索引树嘛，所以列一计算MySQL就会默认失效了。
①对于日志表之类不是给用户的就不需要建立索引，因为其既占用磁盘资源还会影响插入操作，自己写的忍下即可。
②一般在企业中是不会删数据的(数据很珍贵)，一般设置一个字段值del_flag作为删除标记,0表示没有删除，1表示删除。
4.4.2、索引不会包含有NULL值的列
为什么索引列无法存储null值？
- 由于索引是有序的，null值进入索引时，无法确定其应该放在哪里；还有就是就算你放在索引中，使用其null值作为条件搜索，null值是不确定的是无法找到对应索引树的叶子节点位置。
- 这也是为什么主键默认是非空字段的主要原因。
使用null作为查询条件查找的操作是什么？
- 若是查询null，就会在聚簇索引的B+树的叶子节点层进行全表查找。
案例：此时有个需求，要对某个null字段进行索引，如何设计？
-
解决过程：由于单独设置为null的字段为索引，其会自动失效，解决方法就是将该
null字段与另一个非null字段设置成一个复合索引即可！
说明：单列索引无法存储null值、复合索引无法存储全为null的值。
4.4.3、短索引使用
短索引：对串列进行索引，一般就是指定一个前缀长度。使用短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
场景1：例如针对于url字段，目的是区分是百度与新浪
只要设置url(20)为索引即可，将如下字段设置为索引即可
场景2：例如针对于url字段，目的是区别百度图片还是问题查询
如何设置短索引也就很明显了，索引位置为url(17)，到…baidu即可。
总结：视情况而定来建立短索引，对于一个字段中区分度高的部分来设置为短索引较好，短索引在进行索引时比较会更快。
前提说明：MySQL查询只使用一个索引！
where与order by合用情况：因此如果在where子句中已经使用了索引的话，那么之后若是有order by 字段1，那么该字段1列是不会使用索引的。
注意点：尽量不要包含多个列的排序，如若进行多个列排序，那么最好对多个列进行复合索引，这样建立索引效率会更高。
下面介绍了5种索引失效的情况：
①条件中有or那么就会造成索引的失效，除非or条件中的每个列都加上索引才会生效！
- 例如
where id = 1 or A = 1(条件：id为主键，A不是索引)：该条查询语句id会失效原因是有or，要想让其生效需要将or条件中的每个字段设置为索引，在该题中将A设置为索引之后，即可生效。
②MySQL优化器对SQL多个字段顺序进行优化后，若是不符合原本复合索引的最左匹配原则，那么复合索引就会不生效。
③like查询以%开头会造成索引失效。
- 例如
A like '%ab'(条件A为索引)，该%在前的话A索引失效，如何让其生效，将%放置在后，如A like 'ab%'，只要不放在开头即可生效。
④MySQL优化器有时候会使用全表扫描来代替索引(索引失效)，针对于这种情况是好事，能够提高效率。
若是全表扫描比索引快，则不使用索引。举个例子，例如表中数据有300万条数据，你想要查100万条数据，此时就算你使用索引去查询，MySQL优化器还是会将其优化为全表扫描，因为它认为使用全表扫描更快。
-
为什么呢？一般的话你想要查表中30%数据，优化器都会去使用全表扫描，因为其效率相对于使用索引就会更高，你想一想若是使用索引查的话每查询一条数据就需要进行回表操作，该操作也是需要耗费时间的，而且你查的数量这么多都要占全表数据的30%了，大量的回表操作消耗的时间也不少，所以优化器会进行优化让其不走索引，走全表查询反而效率更快。
⑤参与列计算的字段索引也会失效！
- 尽量让右边值进行运算。举个例子：如where A2=15，改为
where A = 根号15，这样才能让A索引生效。
介绍：使用explain可以模拟优化器执行sql查询语句，查询出来的是对应的分析字段描述，根据对应的字段查看SQL语句的执行计划。查看SQL语句是否使用到了索引，有没有做全表扫描。
应用场景：主要用于分析查询语句或表结构的性能瓶颈。
一般的执行结果都会有如下字段，接下来我们去了解一下每个字段的含义进而去分析你写的SQL语句：
1、id字段(三种情况)
①id相同情况时，即可表示分为一组。执行顺序：从上至下。
②id不相同时，id的值越大说明越先被执行。执行顺序：id越大先执行。
③id相同且不同时。执行顺序：先执行大的一组，组中按照从上到下顺序
总结：id不同情况会发生在子查询、union；id相同的情况发生在进行左联、右联、多表查。
(1) SIMPLE：简单SELECT查询语句，允许普通的左联右联，不使用UNION或子查询等。
(4)derived(DERIVED)：用于from子句中有子查询的情况，mysql会递归执行这些子查询，此结果集放在临时表中。
type：查询所使用的访问类型，代表着你的SQL查询语句的性能优劣。
一般了解知晓10种即可：
下面对10中常见的type字段进行说明描述：
①null(NULL)：MySQL在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
-
原理描述：不需要去数据库查，直接通过索引就能够得知是否有该字段。简而言之就是通过索引字段查询无任何结果。
-
简而言之：通过索引查其表查不到任何数据。
②system：表只有一行记录(等于系统表)，这是const类型的特列，平时不太会出现， 可以忽略。
③const：通过索引一次就找到了，用于比较主键或唯一索引，由于只匹配一行数据，速度很快。若是主键在where列表中，MySQL就能将该查询转换为一个常量。
-
约束条件：const必须使用唯一索引或者主键id，若是其他索引可能会找到多个就不是const了。
-
简而言之：通过唯一索引或主键查找到一条记录。
④eq_ref：唯一性索引扫描，对于指定索引键，索引表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
-
过程描述：首先执行
SELECT * from user进行全表查，接着通过使用主键id在索引表中查询唯一索引。
-
评价：性能也很高，根据另一张表的主键id去查询另一张表。
-
简而言之：在指定字段索引表中匹配指定索引行数据。
⑤ref：非唯一索引扫描，返回匹配某个单独非唯一索引值的非索引行，本质上也是一种索引访问，返回所有匹配某个单独值的行，可以去找多个符合条件的行，应该属于查找和扫描的混合体。
⑥ref_or_null：类似ref(非唯一索引)，但是可以搜索值为null的行。
-
问题描述：在
MySQL5.7.32中我依旧得不到ref_or_null的结果，可能也进行了优化。
⑦index_merge：表示使用了索引合并的优化方法。
-
问题描述：按情况应该是进行索引合并了，不过结果竟然是ALL，这两个字段都是单列索引。这里插一嘴，对于常使用的多个字段查询建议使用复合索引。
⑨range：索引范围找，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在where语句中出现between、<>、in等的查询，针对索引字段。
⑩index：全索引扫描，只遍历索引树，速度比ALL快。index是在索引中读取的，而all是从硬盘读的。
11、All：全表扫描，遍历全表找到所有行数据。
-
简而言之：就是对聚簇索引的叶子节点进行全表遍历查询，效率最低。
我们需要掌握了解下面几种类型：
- const：唯一索引的定制查。
- ref：普通索引(非唯一索引)的定制查询。
- range：索引范围查(任何索引)。
- index：全索引查询(索引全表)。
- ALL：全表遍历(聚簇索引叶子节点遍历)。
- 对于负向比较，如使用
!=会引发全表扫描。
- 如果你查询结果需要空值记录，不要使用!=查询(不会将空值行包含进来)，应该使用or条件，如
or name is null。
- or很有可能导致全表扫描，此时可以优化为
union查询。
- 建表时尽量加上默认值(default)，避免空值情况。
table：用来表示输出行所引用的表名
partitions：分区，创建插入表的时候可以创建分区，例如时间分区()，通过该字段能够告诉你数据是从哪个分区来的。
- 分区能够适当增加查询的效率。MySQL中使用不多，Oracle对于优化使用很多。
possible_keys：在这个SQL中，mysql可以使用这个索引去辅助查找记录，当查询涉及到的字段，都会被列出，但不一定被查询使用，只能表示可能会使用到的索引。
key：实际上使用的索引，如果为null表示没有使用索引。
key_len：表示索引字段最大的可能长度(使用的字节数)，根据该列计算查询中使用的索引长度，并非实际使用长度。在不损失精度的情况下，长度越短越好。该字段通过定义计算而得，不是通过表内检索出来的。
注意：key_len只指示了where中用于条件过滤时被选中的索引列，是不包含order by或group by这一部分被选中的索引列。
key len的长度还和字符集有关,latin1一个字符占用1个字节,gbk一个字符占用2个字节,utf8一个字符占用3个字节。
ref：用来显示使用哪个列或常数与key一起从表中选择相应的行。通常显示的是一个列的名字或者const(表示常量)，一般为null。
rows：mysql解析器认为执行此SQL时必须扫描的行数。该数值是一个预估值，不是具体值，通常比实际值小。
filtered：通常使用百分比显示，sql返回结果行数/所读到的行数(rows)的结果。
注意点：对于使用join时，前一个表的结果集大小直接影响了循环的行数。
Extra：表示的是对该sql语句的描述信息
-
using filesort：使用的外部索引排序，不是按照表内的索引顺序读取
-
distinct：一旦mysql找到了与行相联合匹配的行，就不再搜索了。
对几个比较重要的字段进行总结说明。
-
id：表示表达读取顺序。
-
select_type：查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。
-
type（重要）：查询所使用的访问类型，一共有10种，代表着你的性能优劣，一般有ref已经很好了，range也不错。通过该类别来看出是否是全表查，还是通过索引(唯一索引或普通索引)…，进行优化！！！
-
const：唯一索引的定制查。
-
ref：普通索引的定制查。
-
range：索引范围查(任何索引)。
-
index：全索引查询(索引全表)。
-
ALL：全表遍历(聚簇索引叶子节点遍历)。
-
重点主要看type、key、rows即可，就能够进行优化索引。还有对应的extra字段。
-
一般mysql查询不写select * from xx；一定会是全表遍历，一般都是写上对应的字段信息，因为若是单个字段或多个字段可能会走索引从而不进行全表扫描。
-
举个例子：你实际只想要获取name以及age，但是你使用
select * from xx；一定是全表遍历，若是使用select name,age from xx在不设置索引时可能依旧是全表遍历ALL，但是若是建立复合索引那么就是index。切记使用*一定会走全表遍历。
-
一般情况下你能够达到range，甚至说ref、const已经很不错了，若是实现不到range，实现index也可以。一定要规避ALL。
事务：一般使用事务来管理insert、update、delete操作，对于select也可以进行管理，不过一般针对于增删改，在MySQL中默认会开启事务，在执行每一条语句过程中都使用了事务。
目的：就是保证一个业务中的sql语句要么同时成功，一旦出现失败进行回滚。在实际的业务需求中，可能一个业务会操作多张表那么就需要使用到事务，若是事务中出现了异常等情况该业务就会进行回滚操作。
在MySQL中默认是开启事务的(自动提交)，也就是说每次执行一条SQL语句都会进行提交操作，也可以看作每一条SQL语句都是一个事务。
说明：一般情况下我们不会对该自动提交变量进行修改为不生效即为0，而是通过使用命令如begin或start transcation来开启事务，以及使用commit;提交或rollback回滚。只要你开启事务了，若是连接中断或者出现断电都能够进行回滚操作！
扩展：在数据库中实际上可以设置回滚点，一旦出现异常可以回滚到执行回滚点。savepoint 保存点；rollback to 保存点
5.2、事务的四大特征
一般来说事务满足四个特征，即为ACID，仅仅是针对于事务的概念
- 原子性(
Atomicity)：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个缓解。若是事务在执行过程中发生错误，就会进行回滚(rollback)到事务开启前的状态；若是事务过程中都正常执行，最终执行提交操作。
- 一致性(
Consistency)：在事务开启前后，数据库的完整性没有破坏，这表示写入的资料全部符合预设规则，这包含资料的精确度、串联性以及后续数据库可以自发地完成预定的工作。举例：A向B转了100，转成功之后A一定是-100，B+100。
- 隔离性(
Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以阻止多个事务由于并发执行、交叉执行而导致数据的不一致。一般数据库都会提供几个隔离级别之后会进行介绍。举例：A在对一张银行卡取钱，该过程B不能向该银行卡打钱。
- 持久性(
Durability)：事务处理结束之后，对数据的修改就是永久的，即使系统故障也不会丢失。
5.3、事务并发问题描述(4个)
何时发生事务并发问题？当多个事务同时操作同一个数据库的相同数据时。
-
脏读(Dirty read)：当一个事务正在访问数据并且对数据进行修改，该修改操作还没有提交到数据库时，这时另外一个事务也访问了这个数据，接着使用该数据。由于该数据是另外一个事务未最终提交的数据，则为"脏数据"，对于该脏数据进行的操作可能是不正确的。
-
modify)：指一个事务读取一个数据时，另外一个事务也访问了该数据，那么第一个事务修改该数据后，第二个事务也修改了该数据，接着都依次进行提交，那么其中的一个事务做的操作就会丢失，也就称为丢失修改。例如：事务1读取某表数据为A=20，事务2也读取A=20，接着事务1进行A=A-1操作，事务2页修改A=A-1操作，最终结果为A=19，事务1的修改被丢失。
-
不可重复读(Unrepeatableread)：指在一个事务内多次读同一个数据，在事务1还没有结束时，事务2也访问该数据。那么在事务1两次读数据之间，由于事务2的修改导致事务1两次读取的数据可能不一样，发生了在一个事务内两次读到的数据不一致，即为不可重复度。
-
幻读(Phantom read)：幻读与不可重复读类似。其在事务1中读取了几行数据，接着另一个并发事务2插入了一些数据。接着事务1进行查询过程中发现了原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
不可重复读与幻读的区别：
- 不可重复读关键在于修改，前后读取发现数据进行了更改；幻读的关键在于新增或删除，如第二次读取数据时发现记录增多或减少。
5.4、事务的特性—隔离性
事务特性：事务与事务进行隔离
- 在有些情况同时有多个客户端连接服务器并对同一张表进行操作，此时就需要进行事务隔离。一个事务的操作不会影响另一个事务。
5.4.1、查看与设置隔离级别
一般常使用会话隔离级别来进行测试，注意设置全局语句时前面需要大写。
注意注意：设置会话隔离级别后，查看全局隔离级别会不一致，需要查看会话隔离级别。一般在一个会话连接中设置会话隔离级别才会生效！！！
SQL 标准定义了四种隔离级别：
-
READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、不可重复读或幻读。
-
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据。解决了脏读问题，但是不可重复读或幻读仍有可能发生。
-
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果是一致的，除非数据本身是被自己的事务修改。解决脏读、不可重读问题，但是幻读仍有可能发生。
-
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从与ACID的隔离级别，所以的事务依次执行，这样事务之间就完全不可能产生干扰。解决脏读、不可重复度以及幻读。
可串行化实例演示：对于可串行化的隔离级别，其对于事务是依次执行的，若是同一时间多个事务同时执行，其中的增删改查都有可能会进入阻塞状态知道另外一个事务执行了提交操作结束。
扩展说明(来源于资料)
与SQL隔离级别标准的不同，InnoDB存储引擎在REPEATABLE-READ(可重复读)事务隔离级别下使用的是Next-Key Lock锁算法，因此该级别已经可以避免幻读的产生(达到了SQL标准的SERIALIZABLE(可串行化))，这与其他数据库系统是不同的。
为什么大多数数据库使用第二隔离级别READ-COMMITTED(读取已提交)？
- 因为隔离级别越低，事务请求的锁越少，主要是提升性能。对于InnoDB存储引擎默认使用的是可重读，其并不会有任何性能损失。
注意：InnoDB存储引擎在分布式事务情况下会使用到SERIALIZABLE(可串行化)隔离级别。
5.4.3、示例演示隔离级别解决的问题
问题描述：在并发下可能会出现在一个事务中多次读取到不相同内容的指定记录。
MySQL中的RR(可重复读)就解决了脏读、不可重复读，并且也解决了幻读(因为间隙锁的存在)。
上面的案例中包含了MVCC实际演示、第三级别避免的不可重复读。
引出锁：数据库是一个多用户使用的共享资源。当多个用户并发的存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若是对该类情况不加以控制可能就会读取与存储不正确的数据，破快数据的一致性。
如何实现对数据库的并发控制呢？通过加锁的方式来实现，当事务对某个对象进行操作前，先向系统发出请求，对其进行加锁，加锁后事务对该数据对象就有了一定的控制，在该事务释放锁之前，其他的事务就不能对次数据对象进行修改操作。
数据库锁机制：数据库为了保证数据的一致性，使各种共享资源在被访问时变得有序而设计的一种规则，例如串行化隔离级别。
MySQL中的锁机制比较简单显著的特点是不同的存储引擎支持不同的锁机制。
- InnoDB支持
行锁(有时会升级为表锁)。
- MyISAM和MEMORY存储引擎采用的是表级锁。
基本类型(两种)：排他锁(Exclusive Locks，即X锁)与共享锁`(Share Locks，即S锁)。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。
- 当数据对象被加上
排他锁时，其他的事务不能对它进行读取与修改。
- 当加上
共享锁时，数据对象可以被其他事务读取，但是不能修改。
按照锁的粒度划分：行锁、表锁、页锁
- 表锁：锁住表，特点就是开销小、加锁快，不会出现死锁。锁粒度大，发生锁冲突的概率小，并发度相对低。
- 行锁：锁住表中的行，特点就是开销大、加锁嘛，会出现死锁情况，锁粒度小，发生锁冲突的概率高，并发度高。
- 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
特点：锁住表中的行，特点就是开销大、加锁嘛，会出现死锁情况，锁粒度小，发生锁冲突的概率高，并发度高。
记录锁：封锁记录，也叫做行锁。
表如下：teacher_no为主键，其他初始状态下无索引
事务一：开启事务，通过name字段来更新teacher表的一条记录。
事务二：开启事务，在事务一更新好之后进行查询测试允许读，进行更新该表的其他字段进入阻塞状态
说明：右边的事务更改的是左边事务同一张表的其他行记录(进入到阻塞)，该案例说明了在事务中若是通过一个非索引字段去更改一个值，那么就会上表锁。
说明：首先执行左边事务的修改操作(通过非索引字段)，接着执行右边的修改操作该操作是对右边同一张表的同一条记录进行更改字段值同样也进入阻塞，在上面例子中已经能够得知其上的是表锁。
对唯一索引进行等值判断会上行锁！(查询结果始终为一个结果)
首先将该表的name字段设置为索引再进行测试。
事务一：开启事务，更新teacher表字段name为’liner’的行记录。
说明：由于对name进行设置索引，此时就会多出一个非聚簇索引树，左边先进行对name='liner’修改操作就会上锁，注意这里是对非聚簇索引树上锁，接着右边的事务对同表非同记录更新是可以的，对于同表同一记录则进入阻塞状态，说明上的是行锁。
根据对应字段去更新一张表的指定记录时，有下面两种情况根据是否设置索引来分：
- 若是该字段不是索引上的是
表锁，即整张表被锁住，其他事务根据该表是写锁还是读锁进行相应的业务操作(若是写锁，其他事务对该表啥都不能干一直阻塞；若是读锁对该表其他事务只能读，写会进入阻塞)，只有当事务1进行提交或回滚才能够释放锁，其他事务结束阻塞状态执行对应的事务。
- 若是该字段是索引上的是
行锁，本session能够进行读写操作，其他session只能够进行读操作，对于写操作会进入阻塞直至本session提交。
原理分析(自己分析，之后若有误会更正)：①若是你不建立指定索引，那么只会有一个聚簇索引树，根据指定非索引字段查询记录一定走的是全表遍历，此时你又开启了事务进行修改操作那么就会对该树进行上锁，即称为表锁，此时其他事务对于该表只能进行读，哪怕是同表其他行也是不能进行改操作的。
②若是你对某个字段建立索引，那么就会生成一个非聚簇索引树(存放索引字段及id)，此时你开启一个事务并根据其索引字段进行修改操作，那么就会对该非聚簇索引树进行上锁，你也可以将其看做是行锁，此时你还有个聚簇索引树所以其他事务能够对同表的其他行进行改操作，但是对于前一个事务修改的那行不能进行写操作。
在RR(Repeatable Read，第三种隔离级别)这个级别下，为了避免幻读，引入了间隙锁，该锁锁定的是记录范围，不包含记录本身，也就是不允许在范围内插入数据。
间隙锁的作用：保证某个间隙内的数据在锁定情况下不会发生任何变化。比如mysql默认隔离级别下的可重复读（RR）。
间隙范围：根据检索条件向下寻找最靠近检索条件的记录值A作为左区间，向上寻找最靠近检索条件的的记录值B作为右区间，即索引的间隙为(A，B)，注意了是开区间；
-
举例(重要)：例如id索引值依次为10,20,30,40,50，接着进行加锁查询判断如
select xxx from xx where id<30，看条件id<30，仅有10,20，左范围定为查询结果最小值向上找最临界的记录值，右范围为查询结果最大值向下找最临界记录值为30，所以间隙范围为**(?,30)**。
关于不同索引产生锁的情况说明：使用非索引的字段查询会上表锁
- 唯一索引：等值判断只会产生
记录锁，范围查询会产生间隙锁。
- 普通索引：等值判断也会产生
间隙锁。(因为普通索引也会有多条结果重复的情况查询结果可能会有多条，为了避免产生幻读会上间隙锁)
score无任何索引，根据后面案例来决定增加。
实例：仅仅是演示下间隙锁产生的效果
(1)、唯一索引等值判断只会产生行锁
条件：设置score为唯一索引。
(2)、唯一索引进行范围查产生间隙锁
条件：设置score为唯一索引。
(3)、普通索引等值判断产生间隙锁(范围查询也肯定会产生)
条件：设置score为普通索引。
下面可以很明显看到对普通索引进行定值查询会产生间隙锁，定值score=30，会根据30向上找到20作为左边临界值，向下找到50作为右边临界值，间隙范围为：(20,50)，若是在该范围中进行插入与更新操作都会进入到阻塞状态直到另一个事务提交。
临键锁：是记录锁与间隙锁的组合，它的封锁范围既包含索引记录，又包含索引区间。
实际举例：若是根据普通索引去更新某个值，会对该行记录上记录锁，会对这一范围上间隙锁，此时两者的结合为临键锁。
条件：score为普通索引。
下面就演示了记录锁以及间隙锁存在情况：
说明：实际上在MySQL中使用lock in share mode或增删改操作都会默认使用next-key锁实现，具体是什么锁根据sql语句来看。
1、行锁分为记录锁、间隙锁以及临键锁，临建锁实际上就是记录锁+间隙锁。
2、记录锁：指定值行。根据索引字段来修改某条记录时(结果集为1行)，会上行锁，本session能够进行读写操作，其他session对该表能够进行读操作，写操作会进入阻塞。
3、间隙锁：针对于范围结果集。对指定范围进行上锁，其他事务在间隙范围中无法进行增删改操作，避免了幻读。
有很大一部分情况都是临键锁！
6.2、表锁(含读、写锁)
表锁：对一张表进行上锁，
特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
场景：在编辑表或执行修改表操作时可以给表加上表锁这样就会很安全，避免不同步的事情发生，在并发情况下会很慢可能就是上表锁的原因。
- 场景一：有一张表300万数据，你现在要在一个事务中修改150万数据，这种情况下为了更快操作使用表锁，而不是使用行锁(使用行锁的话会有上大量锁的时间，效率不高)。
- 场景二：对于并发度低，一个事务涉及多表时也可以使用表锁。扩展说明一下MySQL中会监控死锁，使用行锁有可能会造成死锁情况，MySQL对于死锁情况会进行回滚，这样的话就会造成大量事务回滚。
自动上表锁：一个事务提交前，进行修改操作其条件字段是非索引的就会上表锁，在记录锁(1)中有案例。
手动上表锁：lock table 表名 read(write); ，你可以对表设置写锁或者读锁(会自动开启一个事务，我自己认为的)。
- 对于
begin为什么能解锁猜测原因：在上表锁的时候实际上已经开了一个事务了，接着若是再使用begin就相当于在一个session中再开启一个事务，在一个会话中若是开启第二个事务就会自动提交前一个事务，提交了事务也就相当于解锁了。
注意情况：手动上表锁时，使用commit、rollback命令并不会解锁。手动上表锁时会自动开启一个事务，本session中做的修改操作只有在表解锁之后才会提交！！！
读锁：本session对表上读锁之后，只允许读不允许写操作(报错)；其他session允许对该表读，进行写操作会进入阻塞状态，直至该表进行解锁。
案例介绍：在session1中给teacher表上读锁，使用读锁的本session只能查不能修改会有报错提示；其他session允许对teacher表查，对于修改操作会进入阻塞状态。
解锁操作：这是演示begin解锁。
写锁：只允许本session进行读写操作，其他session在该表锁未释放期间会进入阻塞，无法读写。
案例介绍：在session1中给teacher表上写锁，此时其他session无法对该表进行查询操作及修改操作。
发现情况：其他session对已经上的表锁进行查询时，只要teacher表不释放锁，那么其他session会一直阻塞等待下去，这与之前行锁进入阻塞有所不同，行锁阻塞的话会有个超时时长，一旦超时会结束本次查询并提示你重试。
接着案例进行解锁操作：这里使用unlock tables;解锁。
解锁之后，其他session即可对该表进行查询以及其他操作了。
1、表锁不会产生死锁问题，粒度大，在一次业务中对于单表进行大数据量更新或者并发度低时涉及大量多表操作可以使用表锁。
2、对于表锁可以自动上或者手动上，自动上表锁情况是根据某个无索引字段进行修改操作会上表锁。
3、手动设置表锁需要你设置读锁还是写锁(lock tables 表 write(read))，手动上表锁时会自动开启一个事务并且上锁，上锁之后进行的修改操作只有在解锁之后才会被提交，解锁命令有两个分别是unlock tables或begin，解锁就相当于提交事务并且对表解锁。
4、读锁与写锁的实际情况。
- ①session1对A表上读锁时，session1对A表只能进行读操作，不能对A表进行写操作(会报错误)；其他session对A表也可以进行读操作，但是不能进行写操作(进入阻塞状态)。
- ②session1对A表上写锁时，session1对A表能够进行读写操作；其他session对A表不能进行任何读写操作(都会进入阻塞状态)。
在InnoDB的锁类型中主要有读锁(共享锁)、写锁(排他锁)、意向锁和MDL锁。
读锁(共享锁，shared lock)：简称S锁，一个事务获取了数据行或表的读锁，其他事务能够获得该行或该表的读锁但不能获得写锁，也就是说一个事务在读取一个数据行时，其他事务可以读但是不能够对该行进行增删改的操作。
简而言之：同一时间允许多个事务读，但是只能一个事务写。
- 注意说明：session1对表上读锁时，session1本身也只能查，修改操作会报错。
读锁在select方式中的应用：
- 第一种是自动提交模式下(autocommit=1)的select查询语句，不需要加任何锁直接返回查询语句，这就是一致性非锁定读。
- 第二种通过
select ... lock in share mode 被读取的行记录或行记录范围上加一个读锁，其他事务可以读，但是想申请写锁(即写操作)就会进入阻塞。
-
lock tables 表名 read：该条语句会自动开启事务并且上读锁。
写锁(排他锁或独占锁，exclusive lock)：简称X锁，一个事务获取了一个数据行的写锁，其他事务就不能再获取该行的其他锁(读、写锁)。
-
注意：在MySQL中使用写锁，其他事务依旧能够进行读操作，因为MySQL通过MVCC解决了。
简而言之：同一时间只允许一个事务进行读写，其他事务本不能进行读写操作的，但在MySQL中是可以的因为通过了MVCC来解决。
-
update：普通更新操作。
-
lock tables 表名 write：该条语句会自动开启事务并对该表进行上写锁。
- meta：元数据，实际就是表结构。这个锁指的就是当你上了这个锁其他事务就不能更改你的表结构。
如何使用MDL锁？在会话A中表开启了查询事务之后，就会自动获得一个MDL锁，会话B就不可以执行任何DDL(对于表的增删改查)语句，例如不能为表中添加字段的操作。
目的：在事务进行中，防止其他事务修改表结构。
提交了事务之后，即可成功修改表结构！
在MySQL中的InnoDB引擎中，意向锁是表级锁，分为两种类型：
- 意向共享锁(IS)：指的是给一个数据行加共享锁前必须获取该表的意向共享锁。
- 意向排他锁(IX)：指的是一个数据行加排他锁前必须获取该表的意向排他锁。
介绍：IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
如何添加？在添加行锁之前会先拿到意向锁。
作用说明：当再向一个表添加表级X锁(排他锁，写锁)时。
- 如果没有意向锁的话，则需要遍历整个表是否有行锁的存在，以免发生冲突。
- 如果有意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了行级锁的存在或者即将有行级锁的存在。无需遍历整表即可获取结果。
1、读锁(S锁)，简而言之就是同一时间多个事务可以同时读，只有一个事务可以写。在MySQL 5.7版本实际测试对表上读锁只允许多个事务同时读，上锁的事务也不能写会报错。
2、写锁(X锁)，简而言之就是同一时间只能上锁的事务进行读写，其他事务不允许读写。在MySQL中使用写锁，其他事务依旧能够进行读操作，因为MySQL通过MVCC解决了。
3、MDL锁与意向锁都是为了在事务进行过程中，其他事务不能执行DDL(表结构修改)语句，防止出现数据不一致情况。
-
MDL锁：当开启一个查询事务之后即可开启一个MDL锁(表锁)，锁的是元数据，即表结构。
- 意向锁：两种IS、IX，在添加行锁之前添加。其存在作用是代表该表具有行级锁存在或即将有行级锁的存在，当添加表级X锁时仅需要与意向锁比较是否兼容即可！
6.4、其他角度对于锁的分类
乐观锁：始终以一种乐观的心态，总认为数据总是不会产生冲突，所以其是没有从数据库加锁的，一般使用版本字段来实现乐观锁，一旦发现冲突则让用户返回错误的信息，让用户来去决定。
具体实现方式：通过在一张表中加version字段，通过使用version字段值来保证每次事务操作的同一版本。
过程：即在一个事务开始之前先查询对应字段记录的version值，进行更新操作时会去拿version也作为更新条件，若是版本一致进行更新并且version+1，若是版本不一致说明出现了同步情况，则会认为是过期数据不进行执行进行回滚。
优点：通过程序实现，不会存在死锁问题，适用场景也相对乐观，阻止不了除了程序之外的数据库操作，效率也较高。
悲观锁：总是很悲观，每次操作数据总觉的别人会对该数据进行操作。依靠数据库提供的锁机制实现，上面举的所有锁都是悲观锁的例子(共享锁、排他锁等)，他阻止一切数据库操作。
注意：数据库的增删改查都会加排他锁，而查询不会加任何锁！
锁等待：当你给行或者表上锁时，其他事务进行查或写操作(实际按对应锁要求)就会进入阻塞，这个阻塞实际上就是锁等待的过程。
设置好了之后测试：这里update是写锁，并且检索字段为主键(唯一索引)即上的是行锁且是写锁
问题描述：执行指定行记录时在其他事务已经上锁，阻塞等待指定时长(默认50秒)仍未获得锁出现异常错误。
当事务1对指定行或行范围进行上锁时，其他事务若是对上锁进行更新操作会进入阻塞状态，若是指定时间内还在阻塞就会报出错误如下：只展示异常情况
结论：该条更新语句不执行，本次事务并没有回滚以及提交。也就是说仅仅是这条语句失效！
死锁：指的是两个或两个以上的进程在执行过程中，因为争夺资源而造成的一种互相等待的现象，就是所谓的锁资源请求产生了回路现象，即死循环。
造成死锁的后果：InnoDB引擎会自动检测死锁，一旦出现死锁就会报错误异常，并且将当前事务回滚。回滚操作会造成大量业务白做，所以死锁尽量不要出现。
假设有两个事务，事务1对A记录进行上锁，事务2对B记录进行上锁(这个过程视为同步进行的)，接着事务1想要对B记录进行操作，事务2想要对A记录做操作，此时就会出现死锁问题，即两个事务同时对各自事务锁进行阻塞等待。
结论：出现死锁时会默认一个事务报出异常并且回滚整个事务，另一个事务死锁sql会执行成功并且事务可以继续往下执行。
死锁的危害很大，会直接让检测出死锁的事务全部进行回滚操作，所以一定要避免出现死锁问题！！！
1、如果不同的程序会并发处理同一个表或者涉及多行记录，尽量约定使用相同顺序访问表，可以大大减少死锁的发生。
写事务时应该按照一定顺序写(上锁的行记录，即检索条件)，按照以下方式就不会出现死锁问题！！！
2、业务中尽量采用小事务，避免使用大事务，及时提交和回滚事务，可减少死锁产生的概率。
3、若是一个大事务，尽量做到一次锁定所需要的所有资源(也就是上表锁)，减少死锁发生的概率。
4、对于非常容易发生死锁的业务，可以尝试使用升级锁的力度，即行锁->表锁来减少死锁的发生。
表锁：自动上(非索引字段作为检索条件)；手动上(可上读锁、写锁)。
行锁：记录锁、间隙锁、临键锁。
- 实际上通过临键锁解决了幻读的问题，MySQL中第三隔离级别实际上就解决了幻读的问题。
保证事务进行过程中的表的不会被其他事务进行修改结构。
意向锁：获取行锁前，先获取意向锁。
读锁(S锁)：允许多个事务同时读，但是都不能写。
写锁(x锁)：一个事务能读能写。而在MySQL中其他事务可以读，因为有MVCC的存在。
乐观锁：很乐观，不会上锁。一般通过给表加version字段来保证版本一致，每次更新时会校验版本。
悲观锁：很悲观，总是认为其他事务会操作本事务目标数据。数据库实现锁，上面提到的锁都是悲观锁。
说明：各个锁的细节可去上面对应章节小总结部分找，很详细。
- 读锁是允许多个事务读，不能写。
- 写锁是按理说只允许一个事务进行读写，而在
MySQL中其他事务是允许读的(读到的是上一个版本记录)，因为其实现了MVCC。
问题提出：在实际生活中，一个网站读肯定是比写多，并且若是一篇文章在进行更新，几千人此时访问该文章此时都进入阻塞，那么用户体验就会极差，那么如何去解决一个事务在写时，其他事务依旧能够进行读操作呢？
解答：实际上MySQL已经为我们解决了该问题，其通过实现MVCC实现了在写锁情况下，其他事务也能够对该记录进行读操作。只不过读到的是上一个版本的而已。
- 查询到的记录实际上是undo log中保留的记录，即左边事务开始前的状态。
MVCC(Multi-Version Concurrency Control，多版本并发控制)：一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
目的：MVCC在MySQL的InnoDB引擎中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。
6.7.2、认识当前读与快照读
-
使用前提：隔离级别不是串行级别，串行级别下的快照读会退化成当前读。
- 执行方式：生成
ReadView，直接利用 MVCC 机制来进行读取，并不会对记录进行加锁。
- ReadView：事务开启时，当前所有事务的一个集合，这个数据结构中存储了当前Read View中最大的ID及最小的ID。
- 实现方式：
undo log + MVCC 。基于的是多版本并发控制(MVCC)，其也是行锁的一个变种
- undo log：是回滚日志，提供回滚操作。
- MVCC(Multi-Version Concurrency Control，多版本并发控制)：一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
- 目的：主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。
-
好处：使用快照读不与上锁的抢占资源，而是读的快照并不是进行上锁，提高了并发性。
-
注意：快照读可能读到的并不一定是数据的最新版本，可能是之前的历史版本。
当前读：读取的是最新版本，读取时还要保证其他并发事务不能修改当前记录，就会对读取的记录进行上锁。
- 实现方式：
next-key 锁(行记录锁+间隙锁)
数据库并发场景一般有三种：
-
读-读：不存在任何问题，也不需要进行并发控制。
-
读-写：有线程安全问题，可能会造成事务隔离性问题，可能会遇到脏读、不可重复读、幻读。
-
写-写：有线程安全问题，可能会存在丢失修改的问题，例如两个事务同时更新，一类更新会消失。
MVCC是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务链接戳关联，当事务1进行了修改操作，事务2对该记录进行读取操作时实际上读取的是事务1开始前的数据库快照。
解决问题：并发读写指定记录时，可以在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发的性能。同时还解决脏读、幻读(应该是间隙锁)、不可重复读隔离问题，但是不能解决更新丢失问题。
MVCC对应的组合：最高提高数据库并发性能，解决读写、写写冲突问题
-
MVCC+悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突。
-
MVCC+乐观锁：MVCC解决读写冲突，乐观锁解决写写冲突。
介绍MVCC依靠的三个隐藏字段(每条记录中)
MVCC实现主要是靠每条记录中的三个隐藏字段、undo日志以及read view实现：下面介绍三个隐藏字段
-
DB_ROLL_PTR(回滚指针)：7byte，回滚指针，指向这条记录的上一个版本。
-
DB_ROW_ID(隐式主键)：6byte，隐含的自增ID(隐藏主键)，如果数据表中没有主键，InnoDB会自动以DB_ROW_ID即该字段产生一个聚簇索引。
注意：在每条记录中还有一个删除flag隐藏字段，该字段用于表示该记录被更新或删除，而并不是代表真正删除。
undo log是什么？存储就旧记录(事务中执行的操作)
undo log：旧的数据放置在undo log中，真实的数据放置在表中，每条记录进行更改或删除操作等会不断增加一条undo log日志，通过回滚指针来指向上一个版本记录，产生的是一个数据链，沿着数据链就能够找到所有版本。并且通过undo log就能够实现事务的回滚，来回滚到之前的版本。
-
insert undo log：事务在insert新记录时产生的undo log，只在事务回滚时需要，并且在事务提交后可以立即丢弃。
-
update undo log：事务在进行update或delete时产生的undo log。该种log不仅在事务回滚时需要，在快照读时也需要，所以一般不随意删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除。
purge线程起到的作用？用来清理标记可安全删除的undo log记录
- 更新或删除仅仅是设置一下老记录的
deleted_bit，并不会直接将过时的记录删除。为了节省磁盘空间，InnoDB专门用purge线程来清理delete_bit为true的记录。purge线程本身也维护一个read
view，若是指定记录delete_bit为true，并且该记录的事务ID相对于purge线程的read view可见，那么该记录表示可以安全清除！
对于初始一条记录在数据库中，设DB_ROW_ID(隐式主键)为1，事务ID与回滚之前都为null。
接下来介绍事务中进行更新操作的执行流程：
- 修改该行数据时，数据库对该行加锁。(暂未修改)
- 将该行数据拷贝到
undo log中，先查看是否有旧记录，若是有，那么最新的旧数据(即新拷贝的记录)作为undo log中指定链表的表头，也就是插在该行记录的undo log最前面。(暂未修改)
- 进行修改操作在数据库表中，并且修改隐藏字段的中的事务ID进行+1，并且回滚指针指向刚刚拷贝到undo的副本记录。(修改)
- 事物提交，释放锁。之后purge线程会将标记可安全删除的记录进行清除。
注意：一旦begin了，就会产生一个事务id
读视图：该视图并不一定是最新的数据，可能是undo log中某个版本的数据。
啥是读视图，什么时候来的？若是在事务中使用了select(快照读)，就会产生一个读视图或称为快照，那么接下来事务中继续使用该select就会使用这个快照，就永远都不会变了直至事务提交结束。
- 该快照记录并维护系统当前活跃事务的ID，每个事务开启时都会分配一个ID，这个ID是递增的，最新的事务ID最大。
目的：帮助你在一个事务中读取到的数据是一样的。
- 也正因为是MVCC以及read view解决了不可重复读的问题。并且解决并发时读-写阻塞的问题！
与MVCC相关，一般是配合MVCC进行读取旧版本的日志(解决读写问题)。
undo log：旧的数据放置在undo log中，真实的数据放置在表中，每条记录进行更改或删除操作等会不断增加一条undo log日志，通过回滚指针来指向上一个版本记录，产生的是一个数据链，沿着数据链就能够找到所有版本。并且通过undo log就能够实现事务的回滚，来回滚到之前的版本。
-
insert undo log：事务在insert新记录时产生的undo log，只在事务回滚时需要，并且在事务提交后可以立即丢弃。
-
update undo log：事务在进行update或delete时产生的undo log。该种log不仅在事务回滚时需要，在快照读时也需要，所以一般不随意删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除。
purge线程起到的作用？用来清理标记可安全删除的undo log记录
- 更新或删除仅仅是设置一下老记录的
deleted_bit，并不会直接将过时的记录删除。为了节省磁盘空间，InnoDB专门用purge线程来清理delete_bit为true的记录。purge线程本身也维护一个read
view，若是指定记录delete_bit为true，并且该记录的事务ID相对于purge线程的read view可见，那么该记录表示可以安全清除！
redo log主要是存储记录(其中也包含内存、磁盘区域)，主要用来做宕机恢复。
redo log(物理日志)：叫做重写日志，记录了文件的变更，存储数据被修改的值。
数据是先加载到内存之后进行修改的，完成之后再刷新会磁盘，为了减少磁盘I/O一般会进行攒数据，攒够了来进行刷新到磁盘。但如果此时断了电，数据就会丢失，根据这个问题推出了redo log日志。
redo log日志由于是顺序写，其效率比数据库上操作要快的多，每插入一条数据之后就要插入一条redo log日志。在redo log日志中提供了一些策略，在redo log中也有内存也有磁盘，每次记录redo log日志时也是先记录在内存再往磁盘上刷，这主要是跟你选择什么策略决定？
- 若是选择1则表示一提交事务就刷新了保证数据一定完成(指的是redo log中完整保存记录值，以后恢复可以从redo log中进行恢复)，一旦宕机重启会自动去比对redo log磁盘中的数据是否比真实磁盘中的指定数据大(LSN)，若是磁盘的大说明已经是最新值，若小则将redo log磁盘中的数据刷新过去。为了安全调成1，为了性能调成0。
对于策略选择说明：对于日志记录可以设置为0，少一条几条无所谓。对于用户、订单一条都不能丢应当设置为1保证安全。
binlog(逻辑日志)：叫做二进制日志，存储了逻辑sql修改语句。
undo log：叫做重做日志，指的是旧的日志链(即事务中进行的修改、删除操作)，配合MVCC，解决读写问题，通过read view读视图来获取undo log中的日志。
redo log：叫做物理日志，在该日志中主要存储记录。
binlog：二进制日志，存储逻辑sql修改语句。
数据库优化：索引是很关键内容(避免不了问到)。小公司靠索引优化占7,80。大公司数据量特别大就要分库分表，读写分离。
[5]. 两个引擎底层都是使用B+树来存储数据，InnDB是将索引与数据都存储到一个文件中，而MyISAM将索引与数据分开存储为两个文件
我是长路，感谢你的耐心阅读。如有问题请指出，我会积极采纳！
欢迎关注我的公众号【长路Java】，分享Java学习文章及相关资料
Q群： 我们可以一起探讨学习
注明：转载可，需要附带上文章链接
