Mysql事务与索引

​ 需求：先从数据库中读取摸一个字段的值，然后在该字段上的基础上进行修改，由于操作不是原子性，无法保证线程安全，也就是当A线程读取到1时，想+1，赋值为2。而B线程已经将数据修改为2了，那么A线程应该是在2的基础上+1，赋值为3。但实际中可能该值都到8了，A又给人家改回2了。所以我们该如何解决数据安全问题？

1. 本能想到利用JVM层面的锁机制，保证每次只有一个线程进行操作，但这个性能太差，而且JVM锁无法在分布式中生效。
2. 利用数据库事务操作，下文我们主要讲Mysql事务的知识。

数据库事务

​ 在讲事务之前，我们需要明确Mysql中只有Inndb支持事务，如果是其他存储引擎则无法使用事务，所以我们应先检查自己的存储引擎是否正确。

1. 查看数据库支持的存储引擎：show engines
2. 查看自己的表使用的引擎：show table status from 数据库名 where name = '表名';
3. 修改表的存储引擎：ALTER TABLE 表名 ENGINE=引擎名称;
4. 查看是否是自动提交：show variables like 'autocommit';
5. 关闭自动提交: SET AUTOCOMMIT = 0;
6. 打开自动提交：set AUTOCOMMIT = 1;
7. 事务的隔离级别有哪些：
   1. Read Uncommitted（读取未提交内容）
      • 别人开启事务，修改数据，但还未提交，你这边就读取到了修改的数据，存在脏读现象
   2. Read Committed（读取提交内容）
      • 存在不可重复读现象，意思就是当A在开启事务后，先查询了一次数据，在这期间B也开启事务，并commit了一条数据，当A再查询的时候数据发现查询到的数据不一致了，
   3. Repeatable Read（可重读）
      • 当A开启一个事务后，读取的数据是锁死的，不会因为外界的修改。但会有幻读的可能
   4. Serializable（可串行化）
      • 隔离级别最高，事务必须一个个进行，相当于Java中的synchronize，但是效率很低。安全性最高
8. 查看数据库支持的事务隔离级别：SELECT @@global.tx_isolation;
9. 修改事务的隔离级别：（seesion表示当前作用域下，如果想改变全局作用域使用：global，这里修改只是临时操作，要永久操作需要在配置文件中修改）
   1. set session transaction isolation level repeatable read; 设置会话隔离级别为可重复读
   2. set session transaction isolation level read uncommitted; 设置会话隔离级别为读未提交
   3. set session transaction isolation level read committed; 设置会话隔离级别为读已提交
   4. set session transaction isolation level serializable; 设置会话隔离级别为可串行化

各个隔离级别存在问题的概念

1. 脏读：好比我们写作文打草稿，写好了再上传提交，应只能看到最后提交的作品，现在是你打草稿我们都能看到了。
2. 不可重复读：写作文，我们打草稿的时候，实在不会写，上网翻翻别人提交上传的优秀作文，抄一会儿不小心刷新页面了，发现尼玛怎么和之前不一样了。原来是别人又修改了一下，然后从新上传了。不可重复读重点在update和delete操作，官网写的
3. 可重复读：与上面相对应，当我打草稿抄作文的时候，不管原作者怎么提交修改，我这边只能看到最初我打草稿那时看到的。相当于我为了保证数据不变，我截了个图。只看这张图。
4. 幻读：侧重点在于数据的增加，比方说作文上传后，需要单独放在一张桌子上，我在打草稿前看了一眼，发现桌子上是空的，等我写的差不多了，我再看一眼桌子还是空的，但等我去放作文的时候，发现明明我看不到有东西，但显示已经有一个别人的作文了。仿佛自己出现了幻觉。幻读侧重于insert语句

锁分类

共享锁：

​ 也称为读锁，多个事务间共享同一把锁，都可以获取到数据，但不能修改数据，否则可能造成死锁问题。

加锁：select * from student where id = 1 lock in share mode

释放锁：commit/rollback

排他锁

​ 也叫做写锁，写锁与其他锁并不能共存，也就是说与共享锁，和其他排他锁都是互斥的。如果一个事务获取了一个数据行的排他锁，其他事务就也不能再获取这行的锁了，获取到锁的事务可以进行读和写

加锁：

​ 自动：在delete/update/insert中默认加锁

​ 手动：select * from student where id = 1 for update;

释放锁：commit/rollback

意向锁（表级锁）

​ 意向锁是由数据引擎自己维护的，用户无法自己操作意向锁，

IS意向共享锁

​ 一个数据行加入共享锁之前必须先获取到该表的IS锁

IX意向排他锁

​ 一个事务在一个数据行加他排他锁之前需要获取到IX锁

上面的意向锁到底有什么用？

一个事务成功给一个表加上表锁的前提是什么？

​ 肯定是这个表中任意数据行没有加上别的锁，那我当想给一个表加表锁时，难倒只能一行一行去判断有没有加锁操作？效率太低，且当你判断完第一个没问题，去判断第二个时，第一个被一个事务加锁了，你也不知道啊，所以使用意向锁，可以提高加表锁的效率，只要某个数据行有了意向排它锁，就说明不能加表锁

提问环节：

1. 行锁锁的是什么？ 是一行数据？是一个字段？还是索引？
2. 当我们不给表加索引时，表是否含有主键索引？

这里有现成的实验：https://blog.csdn.net/weixin_43935927/article/details/109438506

Record Lock：记录锁

条件：唯一性索引（唯一、主键）等值查询，精准匹配。

select * from t2 where id = 4 for update;:锁住id = 4的数据

Gap Lock：间隙锁

条件：记录不存在，当我查询并加锁时，发现并没有击中索引，而是落入在一个索引的区间内，那么就会加一个间隙锁。那既然没数据我加间隙锁能干嘛？

防止插入，当你有一个间隙锁的时候，就不能再给这个间隙加数据，等等。。那这不就保证了不会在我看不见的情况下，别人突然insert一条数据了嘛。。。幻读的解决似乎在这里能找到解决的办法

Next-Key Lock：临键锁

条件：范围查询，包含记录和区间。这里要注意临键是左开右闭的，(x,y]

​ 所以当有这几个锁的时候解决了什么问题呢？那就是解决了在repeatable Read事务隔离级别下，幻读的影响，当有了间隙锁，这个区间虽然你看不到，但你相当于已经对这个范围都加锁了，其他数据无法进行insert，所以最后的结论是下图：

这里还有一点需要注意：select也分为快照读和当前读,这里做出一个解释

• 快照读简单理解就是：select * from goods;,在RR隔离级别下，每次读取到的数据都是一样的，当其他事务插入一条数据时，通过快照读读取到的内容还是不变。这保证了可重复读，但是解决不了幻读。

• 当前读简单理解就是：每次读取的内容都是最新的数据，并对其加锁，保证其他事务不会影响数据本身。具体使用比如：

  • select * from goods lock in share mode; 加入共享可读锁
  • select * from goods for update; 加入排他锁
  • insert , update ,delete默认是先进行当前读，对索引加上排他锁

ACID的实现原理

atomicity原子性

定义我们都知道，一个事务操作内，要么都成功，要么都失败，那Mysql是如何保证的？

​ 维护了一个undo.log文件，也就是我们在事务操作前都会记录一条反向操作SQL，当commit时，则undo.log标志该事务已经结束。如果出现异常，则进行rollback操作，通过undo.log文件执行反向操作，进行数据回滚。比如：

当我们执行：update user set name = "李四" where id = 1; *---修改之前name=张三*

undo文件记录：update user set name = "张三" where id = 1;

durability持久性

定义：如果一个事务提交后，数据的修改是永久性的。

​ 维护一个redo.log文件，背景是我们知道磁盘IO是一个很慢的操作，所以Mysql也维护了一份Buffer缓冲区，操作都会先到缓冲区，最后同步到磁盘中。但缓冲区最大的问题就是数据的不安全，所以引入了redo.log文件，数据会先同步到redo log buffer中，redo log buffer 也是redo.log的缓冲区，然后事务提交后按照我们的约定再刷盘到redo.log文件中。

那这里也会存在问题：

1. redo log buffer 文件能否保证数据不丢失?

Mysql中数据操作单元是一个数据页，16k

​ redo.log中则记录的是对磁盘上的某个表空间的某个数据页的某一行数据的某个字段做了修改，修改后的值为多少，它记录的是对物理磁盘上数据的修改，因此称之为物理日志。

​ redo log buffer是在commit之前就记录了，如果在commit之后再记录就不可能保证数据安全，因为commit后一瞬间挂机不就完了。所以当commit时，我们可以根据innodb_flush_log_at_trx_commit 来决定将redo log buffer什么时候刷盘到redo.log文件中。

• 设置为0的时候，表示每次事务提交时不进行刷盘操作，等待主线程每秒刷盘
• 设置为1的时候，表示每次事务提交时都同步进行刷盘操作（默认值，因为够安全）
• 设置为2的时候，表示每次事务提交时都异步，只把redo log buffer内容写入page cache

2. 为什么宁可写到redo.log文件中，而不是直接写到数据库文件中？

redo.log使用的顺序IO，只追加修改的数据，而从buffer为随机IO，写入数据文件，是按照一个数据页写入的。

isolation隔离性

隔离性保证有两种：

1. 想Java中一样加入锁，每个事务在操作时，只有一个事务获取到锁并操作，效率低
2. MVCC多版本并发控制（Mysql中对RC和RR的默认管理）

MVCC

select读取分为：快照读和当前读

快照读就好像是照了张相，但肯定是以前了，数据不一定准确。

当前读表示读取到的就是最新的数据，比如：update，insert，delete就是当前读，会先获取最新数据，加入行锁。如果想让select也为当前读：select * from table for update || select * from table lock in share mode

MVCC是基于快照读的。但又有不同，我们下面来看一下RC和RR底层到底有什么不同？

首先MVCC的关键在于版本链，ReadView视图，事务ID来区分。可以看这里的图：本地链接

至于RC与RR的区别则在于生成ReadView时机不同，在RC中每次select 都会生成一个readView，所以数据也在不断更新

而RR则是以事务为单位，当第一个select时生成ReadView，后面再次select就共用一个。所以叫可重复读

而为什么说幻读也可解决：这里分为当前读与快照读

当前读：默认加入了间隙锁，保证这一段区间内读的时候也加锁了，别人无法操作

快照读：则是外界的修改，事务内只读取一次，所以也不影响。

​ 外界对幻读的定义也并不是统一的。这里我的理解是，幻读是当事务执行后，虽然读取不到该数据，但是却可以修改或删除该数据，就像幻觉一样，而修改与删除是因为使用的当前读所以可以获取到最新的数据

consistency一致性

ADI特性是为了保证数据的一致性。

索引底层：B+树

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 动态图显示

我们都知道Mysql索引底层是B+树，那为什么使用B+数呢？能不能想HashMap那样使用索引？

1. Hash结构

​ 元素插入与访问都非常的快，如果Hash值碰撞，也可以使用链表。但当数据量太多就查询太慢了，就好像HashMap超过边界变为红黑树。所以Hash的缺点有两个：

• 在数据量大时，查询效率太低
• Hash值无序，在我们使用索引时经常遇到范围查找，Hash没法操作

2. 平衡二叉树

​ 平衡二叉树的特点就是二叉树结构，高度不会超过1层，会有自平衡。但随着树高度的增加，查找就越慢，而且如果我们查找范围数据时，需要不断回旋搜索。比如说select * from table where id >= 10,当我们在树中找到10这个节点，剩下节点需要回旋搜索

3. B树

多个节点合在一起，这样树的高度就降低了，查询速度是要比平衡二叉树解决了，但仍然存在回旋查找的问题

4. B+树

解决了回旋查找的问题，整体结构相当于B树的形成，然后每个节点形成一个链表的关系，这样就没有了回旋查找的问题

这里我是看了上面动态图插入流程体会到的，可能有些错误，还是需要有时间了解具体实现。

索引失效问题

select 前加入 explain 可显示是否使用了索引，其中属性值：

id:选择标识符 select_type:表示查询的类型。 table:输出结果集的表 partitions:匹配的分区 type:表示表的连接类型（ ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）） possible_keys:表示查询时，可能使用的索引 key:表示实际使用的索引 key_len:索引字段的长度 ref:列与索引的比较 rows:扫描出的行数(估算的行数) filtered:按表条件过滤的行百分比 Extra:执行情况的描述和说明

索引失效的情况网上有许多测试数据，可以直接查找，我们这里从底层说明为什么索引会失效。下图是对a字段和b字段加入联合索引，索引底层B+树的样子

通过上面的图我们能发现几个规则：

1. a的值是有序的，b的值是无序的
2. 当a相同时，b是有序的，这在我们多个排序条件时，如果第一个相同，再排第二个是一致的

分析情况来说明

​ like "%i" || like "i%" || like "%i%" 之间的不同

• like '%i' 表示前面什么都可以，以i结尾，根据上图，当a不同时，b是无序的，所以无法使用索引
• like 'i%' 表示以i开头，根据上图a相同时，b是有序的，所以可以用到索引
• like '%i%' 和情况1一样，无法使用索引

​ 索引失效的最本质原因就是：索引在底层的存储一定是有序的，如果要使用索引也应该有序，当使用联合索引时，应保证索引从左至右。这条规则对普通索引也有效。比如说对name加索引，有三个值，abc,acb,aaa，在添加索引时先看第一位，发现都是a，再看第二位，发现不一样了，这时也是按顺序排序的，最终一定是aaa,abc,acb,这里最简单的方式：我们已经知道索引的结构是B+树，通过上面的网址自己插入这几个值，看是怎么排序的。

MyISAM 和 Innodb的索引区别

MyIsam 数据和索引是分为两个文件存储的，左边的图为索引，右边为数据，索引的key为主键id，value为数据的地址，

不管是主键索引还是普通索引都是通过地址去查找数据

Innodb 的索引，也叫聚簇索引，索引与数据班是在一起的

​ 左边的图，每个节点上的key是唯一主键ID，值就是这一整行的数据，当通过Id查询时速度最快

​ 右边的图，以name为索引，每个节点的key是name，value则是主键ID，再通过ID去主键索引中获取数据（这里如果我们没有设置主键索引，Innodb会找唯一自增索引去当做主键索引，如果这个也没有，会使用隐藏字段生成一个主键）

​ 索引：分为主键索引和普通索引，主键索引直接拿数据，普通索引找主键索引找数据（性能相对比MyISAM低），

百万级数据索引优化

1. 索引失效：

   • 计算：如：+ ,- ,* ,/ ,!= ,<> ,is null ,is not null, or
   • 函数：如：sum(),round()等等
   • 手动/自动类型转化，如: id = "1"，id本身是数字，给写成字符串了（这里我用mysql8发现也可以使用索引）

2. 尽量少使用select * 因为这会导致无法使用覆盖索引，覆盖索引的意思是 select name where name = '' and age = ''，name在查询条件的覆盖范围内.select * 也会查询到许多不必要的字段，导致网络传输的性能损耗。

3. like %操作应注意，应避免%开头，但如果是覆盖索引的话，是可以用到索引的！！！, 也就是说使用%i,在使用覆盖索引的情况下，是可以使用索引查询到的

4. Order by如果使用覆盖索引的话也可以被优化用索引，否则数据会被存入内存中的一个空间，再进行排序，而不是利用索引进行排序

5. 用Java操作，要比数据库本身操作快的多

联合索引与单个索引的区别

​ 联合索引遵从最左前缀法则，当我们给a,b,c加入联合索引时，在a || a,b || a,b,c查询时都会使用到索引，联合索引相当于对多个列建索引，并且只建一次。

​ 单个索引时，给a ,b ,c单独加入索引时，当查询a,b,c时，只有最前面的索引被使用，而且如果多个字段添加独立索引，也会导致资源的浪费。