索引+mysql 锁

聚集(聚簇)索引：

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据。是一种数据结构。

带id为主键的索引，根据id找到数据，B树3层结构（不能重复，不能有空值）

非聚集索引：

不带主键id的，区别在于通过非聚集索引可以查到记录对应的主键值，然后通过聚集索引去查数据。
最终都会利用主键通过聚集索引来定位到数据，
但有一种例外：

覆盖（联合）索引

双字段（非主键）的索引
无需回表去查id，然后通过id去查想要的字段
（遵循最左前缀原则）

create index index_birthday_and_user_name on user_info(birthday, user_name);
sql为：select user_name from user_info where birthday = ‘1991-11-1’；
这样非聚集索引查找birthday等于1991-11-1的叶节点的内容，但是叶节点除了有主键Id的值以外，
还有user_name的子段值， 直接取得叶节点中user_name的值返回即可，这样不需要走聚集索引，大大提高效率。
在mysql 5.6后，有了索引下推，会自动匹配完查询条件的数据再进行回表。

最左前缀原则指：
在检索数据时从联合索引的最左边开始匹配
例如对表建立索引a,b
查询条件 where b = ‘’ 不走索引，
where a = ‘’ and b = ‘’ 走索引。

全文索引：

select * from fulltext_test where match(content) against(‘xxx xxx’); content是对应的索引列 （）
select * test where match(content) against(‘a*’ in boolean mode); 匹配a*的字段 反正比like%快很多

唯一索引：

用来建立索引的列的值必须是唯一的，允许空值,在sql中可以使用on duplicate key update来避免重复插入的问题

唯一索引和普通索引的区别就是查询的时候前者会查询到目标就停止，而后者会查到第一个不符合条件的数据才停止，
但二者的查询效率差距很小，因为InnoDB是按数据页为单位来读写的，所以当我们读取一条记录时，并不是将这个记录
从磁盘读出来，而是以页为单位，整体读入内存。

建索引的原则：

• 对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引
• 数据量小的表最好不要使用索引
• 对于列数据重复多的，数据范围不大的（区分度,基数高:重复度低），不建立索引，比如性别。

B+树的由来：

非平衡二叉树（二分查找，最坏查n次）
---->平衡二叉树（因为左右子树差不超过1，所以查询效率增加了，变为log2n,比较稳定，但是数据多查找还是很慢）
---->b-树（每个节点最多有n个子树，可以存多个值，所以树变得更矮胖，也就是IO次数更少，更快的查找到，但是范围查找时，io次数过多）
---->b+树（解决范围查找次数过多的问题，所有的数据都存在叶子节点上，这样范围查询时可以不用回去到根节点，树的深度没有增加，也就是查询速度没有变慢多少，但是它的范围查找时的速度是大大的提高）

MyISAM的索引原理：

MyISAM分别会存一个索引文件和数据文件， 它的主索引是非聚集索引。当我们查询的时候我们找到叶子节点中保存的地址，然后通过地址我们找到所对应的信息。

InnoDB的索引原理（mysql）

其数据文件本身就是索引文件。它的表数据文件本身就是按B+树组织的一个索引结构,叶子节点的data域保存了完整的数据记录。
这个索引的key通常是表的主键。这被称为聚簇索引。

• 如果定义了主键（ primary key ），则使用主键作为聚簇索引
• 如果没有定义主键，则选择第一个不包含 NULL的唯一键的列做聚簇索引。
• 如果都没有，则系统生成一个6字节的列叫rowid做聚簇索引
  

4种隔离级别

• 一致性是数据库总是从一个一致性的状态转换到另一个一致性的状态。（binlog）
• mysql 默认隔离级别为Repeatable
• Oracle 为 Read Committed、
• 读脏数据是读到了其他事务提交后回滚的数据。
• 重复读是在一个事务中读了两次某个数据，结果却不一样。（A在读的过程中B修改了）
• 幻读是发生在并行修改或者删除中，A修改或者删除某一类数据，B由给他修改回来或者加进来了，查的时候发现有没操作的数据！

mysql执行一条查询语句设涉及的模块：

1. 连接器：每个客户端都会建立一个与服务器连接的线程，服务器会有一个线程池来管理这连接；如果客户端需要连接到 MYSQL 数据库还需要进行验证，包括用户名、密码、主机信息等
2. 解析器：解析器的作用主要是分析查询语句，最终生成解析树，查看缓存：如果缓存中有，直接返回，没有执行下一步（效率++）
3. 优化器：执行效率最好的方案（用哪个索引，先执行哪个条件）包括选择合适的索引，数据的读取方式
4. 执行器：验证权限，执行语句
   

bufferPool

Buffer Pool 是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的
工作流程：

Undo log 记录的是数据操作前的样子

• 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读(MVCC)

redo log 记录的是数据被操作后的样子

• 是InnoDB引擎特有的日志模块，记录了某个数据页上做了哪些修改。（持久性）当有数据需要更新时，先写入日志，后面找个时间再写入磁盘，不然每次都写入磁盘太麻烦了。

bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）

• binlog是Server层自带的日志模块，记录本次修改的原始逻辑，说白了就是SQL语句。

binlog日志文件的格式：

• statement，row，mixed。 statemet记录sql语句，row记录实际的数据变更
• mixed是 二者混合，row使用较多。(记录了修改数据库的操作，数据的一致性，用于主从复制)

注意：因为redo log 是循环写入的，它是不能持久化保存的，所以假如数据丢失了想要恢复的话，必须时redo log+丢失数据期间的bin log
(这或许就是归档日志的意思)来恢复数据！！会有两阶段提交来保存两个log的数据一致！（类似于事务）

innodb加载sql的流程:

innodb主要是有一个buffer pool，当服务层调用数据引擎层时，innodb作为搜索引擎，首先将从磁盘读取的页存放在缓冲池buffer pool中，将执行的数据写入到redolog中和undolog（记录事务发生之前的数据状态发生异常回滚，保证数据的原子性），将进行二阶段提交(2PC)，首先将redolog中的状态变为papare，然后将执行的操作写入到binlog，写入成功提交事务，将redolog中的状态变为commit，经过刷脏操作将数据写入到磁盘DBFile中

其中为什么要写入到redolog中不是直接写入到磁盘中，有以下原因：
由于写入到redolog时，是顺序I/O，数据按顺序存放，每次写入不用重新寻址，节约寻址时间，直接写入磁盘DBFile,

多版本并发MVCC：

(解决在默认隔离级别下，读的时候的幻读问题，间隙锁解决了修改/插入时幻读问题)
mvcc里面使用一个可读视图ReadView来辅助判断那些数据的版本是否可见，
而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

1. 每个事务都有一个事务ID,叫做transaction id(严格递增)
2. 事务在启动时,找到已提交的最大事务ID记为up_limit_id。
3. 事务在更新一条语句时,比如id=1改为了id=2.会把id=1和该行之前的row trx_id写到undo log里,
4. 再定一个规矩,一个事务要查看一条数据时,必须先用该事务的up_limit_id与该行的transaction id做比对,
   如果up_limit_id>=transaction id,那么可以看.如果up_limit_id<transaction id,则只能去undo
   log里去取。去undo log查找数据的时候,也需要做比对,必须up_limit_id>transaction id,才返回数据
5. MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个 隔离 级别 下 工作。

MVCC快照读需满足条件：

先明白两个概念：

快照读： 普通的 select… 查询都是快照读（只能读到当前版本的数据）

当前读： 读取的数据的最新版本，并且在读的时候不允许其它事物修改当前记录（更新修改都是当前读的数据，最新版本）

select… lock in share mode(读锁)
select… for update(写锁)

1. InnoDB 只 查找 版本 早于 当前 事务 版本 的 数据 行（ 也就是数据的 系统 版本号 小于 或 等于 事务 的 系统 版 本号）， 这样 可以 确保 事务 读 取的数据， 要么 是在 事务 开始 前 已经 存在 的， 要么 是 事务 自身 插入 或者 修 改过 的。
2. 数据的 删除 版本 要么 未定义，要么大于当前 事务 版 本号。

这可以确保事务读取到的数据，在事务开始之前未被 删除。
MVCC解决了基于快照读下的幻读，事务 读 取的 行， 要么 是在 当前事务 开始 前 已经 存在 的， 要么 是 事务 自身 插入 或者 修 改过 的。
但是读不到其他事务的写操作 ！！！所以MVCC无法解决当前读下的幻读。（同一时刻其他事务造成的幻读）

再引入两个概念：

共享锁：

in share mode 加锁了只能读不能写 （一个门多把钥匙，能看不能动）

排他锁：

select for update 加锁了只有一个资源和对他读写，其他的事务动不能操作他 （相当于行锁，其他的都不能修改它，只能查看）
对于update,insert,delete语句会自动加排它锁的。

select for update 是为了在查询时,避免其他用户以该表进行插入,修改或删除等操作,造成表的不一致性.(排他锁，悲观锁)

锁加在索引上

https://blog.csdn.net/qq_40174198/article/details/111835482
InnoDB的行锁是通过给索引上的索引项加锁来实现的

• record lock 可以锁一个存在的索引项（锁着索引的！）
• gap lock 锁索引项之间的间隙，可以防止幻读（左开右开区间，间隙锁）
• next-key lock =record lock + gap lock ，innodb默认加锁单位（左开右闭区间）

更新操作加锁的情况是：
更新的字段有索引就在索引上加锁，没有索引就会把所有的数据都加锁!在rr（可重复读）的级别下，有间隙锁+mvcc，不加锁的读走的是快照读，而加锁的的读是当前读，当前读的话会对当前最新已提交的数据加锁，可是在加锁之后假设新插入了一条数据，这个数据就没加上锁，所以commit的时候看到的数据会多，这个就是幻读.所以rr没办法解决幻读的情况。

为什么read-committed 不能解决重复读的问题：（针对当前事务）

在同一个事务中读取两次数据不一致的情况（读了没提交，其他事务修改了，再去读发现数据变化了）

Read Committed 在事务中每次读操作都是读取最新的行数据版本，而这最新的数据行版本很可能是某个事务进行了修改操作后提交的，所以可能会发生多次读取同一行数据，但是前后读取的数据不一致的情况。这就是不可重复读现象，所以提交读不能避免不可重复读现象。

Repeatable Read怎么解决的重复读问题

Repeatable Read 在事务发生第一次读的时候选定所要读取的数据行的版本，整个事务都读取这一个版本的数据行，所以可以重复读，每次读取的数据都一致。

1. 快照读的情况下,rr不能更新事务内的up_limit_id,而rc(读提交)每次会把up_limit_id更新为快照读之前最新已提交事务的transaction id,所以rc会有重复读的问题
2. 当前读的情况下,rr是利用record lock+gap lock来实现的,而rc没有gap lock,所以rc会有重复读的问题

mysql死锁

死锁指的是两个或两个以上的事物在执行过程中争抢锁资源而造成相互等待的情况

表锁不会出现死锁，主要还是针对InooDB的行锁，可以看下面的例子：

解决死锁：

• 超时等待，事物超时自动回滚(innodb_lock_wait_timeout 默认50s)，太久了！

• 主动死锁检测，事物请求锁的时候采用 wait-for graph 等待图的方式进行死锁检测（innodb_deadlock_detect 默认on）

• 可以控制并发度来死锁检测，这样负担也不会很大
  发现死锁也可以人为 kill 进程

总结:

• MySQL锁分为全局锁、表级锁以及行级锁，不同的存储引擎支持锁的粒度有所不同，MyISAM 只支持到表级锁，InnoDB 则可以支持到行级锁，锁的粒度决定了业务的并发度，因此更推荐使用InnoDB
• InnoDB默认最小加锁粒度为行级锁，并且锁是加在索引上，如果SQL语句未命中索引，则走聚簇索引的全表扫描，表上每条记录都会上锁，导致并发能力下降，增大死锁的概率，因此需要为表合理的添加索引，线上查询尽量命中索引
• 行级锁默认加 next-key lock(间隙锁+record lock)，而根据不同的索引也有不同的加锁规则，我们可以根据加锁规分析加锁区间
• 锁粒度的减小提高了并发度的同时也增加了死锁的风险，查询应尽量考虑减少锁的范围