Mysql

由于本人水平很有限，如有什么错误点，欢迎各位大佬指点。

MySQL

mysql架构

连接器：负责跟客户端进行连接，获取权限、维持和管理连接。连接分为长连接和短连接

分析器：Mysql把输入的sql字符串进行识别

优化器：优化策略RBO规则 CBO成本 在具体执行前，需要进行优化 决定使用那个索引、多表关联的顺序

执行器：执行sql语句

Mysql索引

mysql索引：为了加快查询速度 底层使用的是B+树的结构

mysql 的数据是存储在磁盘上的 要减少IO的次数 ，索引会占据磁盘的空间，索引虽然会提高查询效率，但是会降低更新表的效率。

索引的数据结构

hash索引：Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。

无论是二叉树还是红黑树，都会因为数的深度过深而造成IO的次数变多，影响数据读取的效率

B树 所有的键值分布在整棵树中，搜索有可能在非叶子节点结束，每个节点最多拥有m个子树，根节点至少两个子树，所有叶子节点都在同一层，分支节点至少拥有m/2颗子树，每个节点最大可以有m-1个K

每个节点都有K，同时也包含V值，而每个页的存储空间是有限的，如果data 比较大的话会导致每个节点存储的k数量变少
当存储的数据量很大的时候会导致树的深度较大，增大查询时磁盘的IO次数，进而影响性能

4阶B树

B+树：

4阶B+树

B+Tree 是在B树的基础上做出了一些优化：

B+Tree每个节点可以包含更多的节点，可以降低树的高度，将数据范围变为多个区间，区间越多数据检索越快
非叶子节点不保存data只存储K，叶子节点存储K和数据
叶子节点两两指针相连，顺序查询性能更高

索引类型

主键索引：自增，为了方便在B+树 直接插入到后边 免得插入在中间位置
唯一索引：索引列中的值必须是唯一的，但是允许为空值。不需要回表
普通索引：需要回表，需要遍历两边B+树
组合索引：多列组成一个 索引 例如：name age 组合，要遵循 最左匹配原则
全文索引：检索某些对应的关键字 MyISAM支持，InnoDB5.6后支持 (通常不用，使用es)

索引不是越多越好的 索引越多 磁盘空间越大 检索起来 越费劲

存储引擎区别

MyISAMInnoDB索引类型非聚簇索引聚簇索引支持事务否是支持表锁是是支持行锁否是支持外键否是支持全文索引是是（5.6后）适合操作类型大量select大量insert update delete
聚簇非聚簇 ：索引和数据 是否分开

回表、索引下推、索引覆盖

例子：select * from 表 where name = “ ” and age = 18

回表：就是 索引 name 找出主键 去 id 树 查找出name符合的内容 再去比较age （首先会查到普通索引的位置，取出id值，再拿id值取表中取数据）

索引覆盖：name age 建立组合索引了 直接拿出来 不需要回表 （查询语句的执行只用从索引中就能够取得）

索引下推：在回表的过程中 检索的时候 把 age 先进行判断 （简单说就是在索引内部进行了判断）

Mysql事务

数据库中的事务是指对数据库执行一批操作，这些操作最终要么全部执行成功，要么全部失败

事务特性ACID

• 原子性（A）：事务是最小单位，不可再分
• 一致性（C ）：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
• 隔离性（I）：事务A和事务B之间具有隔离性
• 持久性（D）：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

---

• 开启事务：Start Transaction
• 事务结束：End Transaction
• 提交事务：Commit Transaction
• 回滚事务：Rollback Transaction

---

事务隔离级别

数据库的事务隔离级别有四种，分别是读未提交、读已提交、可重复读、序列化，不同的隔离级别下会产生脏读、幻读、不可重复读等相关问题，因此在选择隔离级别的时候要根据应用场景来决定，使用合适的隔离级别。
隔离级别脏读不可重复 读幻读READ- UNCOMMITTED（读未提交）√√√READ-COMMITTED（读已提交）×√√REPEATABLE- READ（可重复读）××√SERIALIZABLE（序列化）×××
SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 事务的修改，即使没有提交，对其他事务也都是可见的。事务能够读取未提交的数据，这种情况称为脏读。
• READ-COMMITTED(读取已提交)： 事务读取已提交的数据，大多数数据库的默认隔离级别。当一个事务在执行过程中，数据被另外一个事务修改，造成本次事务前后读取的信息不一样，这种情况称为不可重复读。
• REPEATABLE-READ(可重复读)： 这个级别是MySQL的默认隔离级别，它解决了脏读的问题，同时也保证了同一个事务多次读取同样的记录是一致的，但这个级别还是会出现幻读的情况。幻读是指当一个事务A读取某一个范围的数据时，另一个事务B在这个范围插入行，A事务再次读取这个范围的数据时，会产生幻读
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

事务隔离机制的实现基于锁机制和并发调度(MVCC多版本并发控制)。通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

Mysql日志

存储引擎默认是 InnoDB

日志文件 redolog undolog 在存储引擎 binlog服务端

---

redolog InnoDB存储引擎：当发生数据修改的时候，innodb引擎会先将记录写道redolog中，并更新内存、此时更新就算完成了，同时innodb引擎会在合适的时机将记录操作到磁盘中。

先写到缓存空间 redolog里，下一次启动的时候会根据redolog日志文件来更新数据库，保证数据持久性。redolog是完整的数据就不会丢失

MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1

---

undolog： 回滚日志：是为了实现事务的原子性，在Mysql数据库InnoDB存储引擎中，还用undolog来实现MVCC

在操作任何数据之前，首先将数据备份一份（undolog），然后进行数据修改如果出现了错误或者用户执行了ROLLBACK，系统可以利用undolog中的备份数据恢复到事务开始之前的状态

undolog ： 当delete一条记录 undolog中 对应 insert，insert对应delete，update对应update相反

---

binlog： 默认关闭

他是server层的日志，主要是做mysql功能层面的，binlog中会记录所有的逻辑，并且采用追加写的方式，定期执行备份

恢复数据过程：

​ 1、找到最近一次的全量备份数据
​ 2、从备份的时间点开始，将备份数据的binlog提取出来，重新恢复到那个时刻

---

数据更新流程

执行流程：

​ 1、执行器先从执行引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回
​ 2、执行器拿到数据后先对数据进行更改，然后调用引擎接口重新写入数据
​ 3、引擎将数据更新到内存，同时写数据到redolog中，此时处于prepare阶段，并通知执行器执行完成，随时可以提交事务
​ 4、执行器生成这个操作的binlog，并把binlog写入到磁盘
​ 5、执行器调用引擎的事务提交接口，引擎把刚写完的redolog改成commit状态，更新完成了

redolog 和 binlog两阶段提交

我们先假设没有两阶段提交时，可能会有以下两种情况：

redo log 提交成功了，这时候数据库挂掉导致 binlog 没有成功写入。数据库重启之后通过 redo log 把数据恢复回来，但是 binlog 没有成功写入，导致我们在做主从复制或者数据恢复的时候，数据不一致。
binlog 提交成功了，这时候数据库挂掉导致 redo log 没有成功写入。数据库重启之后，无法恢复崩溃之前提交的那个事务，这部分数据更改在主库缺失。但是 binlog 已经成功写入了，从库反而有了该事务的改动，导致数据不一致。
综上我们知道，redo log 和 binlog 必须同时成功或同时失败，才能保证数据一致性。
上图的 右侧 时刻

假设在上图的时刻1，redo log 处于 prepare 之后，写 binlog 之前，数据库挂掉了。由于此时 binlog 还没有写，redo log 也还没有提交，所以崩溃恢复后，这个事务会回滚。这时候 binlog 还没写，所以也不会传到备库。
假设在上图的时刻2，写 binlog 之后，redo log 还没有 commit 前发生 crash。那么崩溃恢复时，MySQL 会做以下判断：
如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
a. 如果是，则提交事务；
b. 否则，回滚事务。

---

Mysql的组提交

​ 组提交（group commit）是mysql处理日志的一种优化方式，主要为了解决写日志时频繁刷磁盘的问题。组提交伴随着mysql的发展，已经支持了redo log和bin log的组提交。

redo log组提交

​ WAL(Write-Ahead-Logging)是实现事务持久性的一个常用技术，基本原理是在提交事务时，为了避免磁盘页面的随机写，只需要保证事务的redo log写入磁盘即可，这样可以通过redo log的顺序写代替页面的随机写，并且可以保证事务的持久性，提高了数据库系统的性能。虽然WAL使用顺序写替代了随机写，但是，每次事务提交，仍然需要有一次日志刷盘动作，受限于磁盘IO，这个操作仍然是事务并发的瓶颈。

​ 将多个事务redo log的刷盘动作合并，减少磁盘顺序写。Innodb的日志系统里面，每条redo log都有一个LSN(Log Sequence Number)，LSN(日志的逻辑系列号)是单调递增的。每个事务执行更新操作都会包含一条或多条redo log，各个事务将日志拷贝到log_sys_buffer时(log_sys_buffer 通过log_mutex保护)，都会获取当前最大的LSN，因此可以保证不同事务的LSN不会重复。

---

bin log组提交

Flush 阶段

• 首先获取队列中的事务组
• 将Redo log中prepare阶段的数据刷盘
• 将binlog数据写入文件，当然此时只是写入文件系统的缓冲，并不能保证数据库崩溃时binlog不丢失
• Flush阶段队列的作用是提供了Redo log的组提交
• 如果在这一步完成后数据库崩溃，由于协调者binlog中不保证有该组事务的记录，所以MySQL可能会在重启后回滚该组事务

Sync 阶段

• 这里为了增加一组事务中的事务数量，提高刷盘收益，MySQL使用两个参数控制获取队列事务组的时机：

​ binlog_group_commit_sync_delay=N：在等待N μs后，开始事务刷盘

​ binlog_group_commit_sync_no_delay_count=N：如果队列中的事务数达到N个，就忽视binlog_group_commit_sync_delay的设置，直接开始刷盘

• Sync阶段队列的作用是支持binlog的组提交
• 如果在这一步完成后数据库崩溃，由于协调者binlog中已经有了事务记录，MySQL会在重启后通过Flush 阶段中Redo log刷盘的数据继续进行事务的提交

Commit 阶段

• 首先获取队列中的事务组
• 依次将Redo log中已经prepare的事务在引擎层提交
• Commit阶段不用刷盘，如上所述，Flush阶段中的Redo log刷盘已经足够保证数据库崩溃时的数据安全了
• Commit阶段队列的作用是承接Sync阶段的事务，完成最后的引擎提交，使得Sync可以尽早的处理下一组事务，最大化组提交的效率

MVCC

MVCC：Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

当前读和快照读

• 当前读 像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
• 快照读 像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

---

当前读、快照读、MVCC关系

​ MVCC多版本并发控制指的是维持一个数据的多个版本，使得读写操作没有冲突，快照读是MySQL为实现MVCC的一个非阻塞读功能。MVCC模块在MySQL中的具体实现是由三个隐式字段，undo日志、read view三个组件来实现的。

MVCC解决的问题

数据库并发场景有三种，分别为：

1. 读读：不存在任何问题，也不需要并发控制
2. 读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读
3. 写写：有线程安全问题，可能存在更新丢失问题

多版本并发控制（MVCC）是一种用来解决

读-写冲突

的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以 MVCC 可以为数据库解决以下问题

​ 1、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
​ 2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

MVCC实现

mvcc的实现原理主要依赖于记录中的 三个隐藏字段，undolog，read view来实现的。

• DB_TRX_ID(当前操作该记录的事务ID) 6 byte，最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID
• DB_ROLL_PTR(回滚指针，配合undo日志指向上一个版本) 7 byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）
• DB_ROW_ID(数据库默认为该行记录生成的唯一隐式主键) 6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引
• 实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了

---

undolog

undolog 回滚日志，表示在进行 insert update delete的时候需要生成方便回滚的日志
insert操作时，产生的undolog只在事务回滚的时候需要，事务提交之后可以丢弃
update，delete操作时，不仅在事务回滚的时候需要，在进行快照读的时候也需要，所以不能随便删除，只有在快照都或事务回滚不涉及该日志的时候，对应的日志才会被purge线程统一清除。

undolog生成 看图

有一个事务插入第一记录，当前状态 隐式主键ID 事务ID 回滚指针

第二条 ，把name 改为 tom

在事务2进行修改行记录的时候，数据库会先对改行加排他锁
然后把改行数据拷贝到undolog中，作为旧记录
拷贝完毕后，修改该行name，并且修改事务的ID ，回滚指针指向拷贝到undolog的副本记录
事务提交后，释放锁

假设有来了第三个事务 对age 进行修改，如上图一样

事务3修改的时候，对该行加锁
然后数据拷贝到undolog…

不同事务或者相同事务的 对同一记录的修改，会导致该记录在undolog中成为一条链，链首就是最新的旧记录，链尾就是最早的旧记录

---

Read View： 就是事务进行了快照读操作的时候产生的读视图，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID (当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大)

​ Read VIew 最大作用是用来做可见性判断的，当某个事务在指向快照读的时候，对该记录创建一个readview读视图，把它当作条件判断去判断当前事务能够看到那个版本的数据，可能是最小的数据，也有可能是改行记录的undolog里面的某个版本的数据。

​ Read View遵循的可见性算法主要是将要被修改的数据的最新记录中的DB_TRX_ID（当前事务id）取出来，与系统当前其他活跃事务的id去对比，如果DB_TRX_ID跟Read View的属性做了比较，不符合可见性，那么就通过DB_ROLL_PTR回滚指针去取出undolog中的DB_TRX_ID做比较，即遍历链表中的DB_TRX_ID，直到找到满足条件的DB_TRX_ID,这个DB_TRX_ID所在的旧记录就是当前事务能看到的最新老版本数据。

ReadView 可见性 三个全局属性

​ trx_list：一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID
​ up_limit_id：记录trx_list列表中事务ID最小的ID
​ low_limit_id：Read View生成时刻系统尚未分配的下一个事务ID

具体比较规则：

1. 首先比较DB_TRX_ID < up_limit_id,如果小于，则当前事务能看到DB_TRX_ID所在的记录，如果大于等于进入下一个判断
2. 接下来判断DB_TRX_ID >= low_limit_id,如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的，那么对于当前事务肯定不可见，如果小于，则进入下一步判断
3. 判断DB_TRX_ID是否在活跃事务中，如果在，则代表在Read View生成时刻，这个事务还是活跃状态，还没有commit，修改的数据，当前事务也是看不到，如果不在，则说明这个事务在Read View生成之前就已经开始commit，那么修改的结果是能够看见的。

整体流程

假设4个事务在执行 ，模拟一下
事务1事务2事务3事务4开始开始开始开始操作…操作…操作…修改且已提交进行中…快照读进行中…………
当事务2进行到快照读的时候，数据库为该行数据生成一个ReadView读视图，事务1 3 还在活跃状态，事务4在事务2快照读前已经提交更新，所以ReadView中记录了系统当前活跃的是 1 3，维护在一个列表上 trx_list

以下记录：最小id 活跃 尚未分配的

上述例子，4修改了该行记录，并在事务2进行快照读之前已经提交了事务，所以当前该行数据的undolog数据

因为事务2还没有修改字段，只是查询，所以最小数据的事务索引依然是4

undolog是事务4修改前的版本

当事务2在快照读该行记录的时候会拿着DB_TRX-ID 去跟up_limit_id,lower_limit_id和活跃事务列表进行比较，判断事务2能看到改行记录的版本是那个

​ 所以：先拿该条记录 DB_TRX_ID 字段记录的事务ID 4 去跟Read View的up limit id 比较，看4是否小于up limt id（1）,所以不符合条件，继续判断4是否大于等于 low limit id（5），也不符合，最后判断4是否处于 trx list中的活跃事务，最后发现事务ID为4的不在此列表中，符合可见性，所以事务4修改后提交的最新结果的事务2快照读是可见的，因此事务2读取到最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度上的最新版本

---

RR、RC级别InnoDB快照读有什么不同

因为ReadView 生成时机不同从而造成RC、RR级别下快照读的结果不同

• 在RR（可重复度）级别下的某个事务的对某条记录的第一次快照读会创建一个快照及ReadView，将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用同一个ReadView，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个ReadView，所以对之后的修改不可见。在RR级别下，快照读生成ReadView时，ReadView会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于ReadView创建的事务所作的修改均是可见的
• RC（读已提交）级别下，事务中 每次快照读都对新生成一个快照和ReadView，这就是在RC级别下的事务中可以看到别的事务提交的更新的原因

在RC隔离级别下，是每个快照读都会生成并获取最新的Read View,而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View。

Mysql锁机制

​ 锁是计算机协调多个进程或线程并发访问某一资源的机制。不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

​ 表级锁： 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
​ 行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

MyISAM表锁

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

​ 对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

MyISAM写锁阻塞读的案例：

​ 当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作。其他线程的读写操作都会等待，直到锁释放为止。
session1session2获取表的write锁定
lock table 表名 write;当前session对表的查询，插入，更新操作都可以执行
select * from 表名;
insert into 表名 values(data);当前session对表的查询会被阻塞
select * from 表名；释放锁：
unlock tables；当前session能够立刻执行，并返回对应结果
MyISAM读阻塞写的案例：

​ 一个session使用lock table给表加读锁，这个session可以锁定表中的记录，但更新和访问其他表都会提示错误，同时，另一个session可以查询表中的记录，但更新就会出现锁等待。

​ MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁，上例中的加锁时为了演示效果。

MyISAM的并发插入问题

MyISAM表的读和写是串行的，这是就总体而言的，在一定条件下，MyISAM也支持查询和插入操作的并发执行

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql>showstatuslike'table%';+-----------------------+-------+| Variable_name         |Value|+-----------------------+-------+| Table_locks_immediate |352|| Table_locks_waited    |2|+-----------------------+-------+--如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

InnoDB行锁

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql>showstatuslike'innodb_row_lock%';+-------------------------------+-------+| Variable_name                 |Value|+-------------------------------+-------+| Innodb_row_lock_current_waits |0|| Innodb_row_lock_time          |18702|| Innodb_row_lock_time_avg      |18702|| Innodb_row_lock_time_max      |18702|| Innodb_row_lock_waits         |1|+-------------------------------+-------+--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

InnoDB的行锁模式及加锁方法

​ 共享锁（s）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
​ 排他锁（x）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

1、在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁
2、创建带索引的表进行条件查询，innodb使用的是行锁
3、由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是依然无法访问到具体的数据

---

总结：

对于MyISAM的表锁：
（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。
（2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。
（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。
（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB：
（1）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
（2）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

• 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；
• 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

Mysql查询执行计划

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

执行计划中包含的信息

ColumnJSON NameMeaning[

id

]

select_id

The

SELECT

identifier[

select_type

]NoneThe

SELECT

type[

table

]

table_name

The table for the output row[

partitions

]

partitions

The matching partitions[

type

]

access_type

The join type[

possible_keys

]

possible_keys

The possible indexes to choose[

key

]

key

The index actually chosen[

key_len

]

key_length

The length of the chosen key[

ref

]

ref

The columns compared to the index[

rows

]

rows

Estimate of rows to be examined[

filtered

]

filtered

Percentage of rows filtered by table condition[

Extra

]NoneAdditional information

• id[SELECT]标识符。select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序 三种情况： 1、id相同：执行顺序由上至下2、id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行3、id相同又不同（两种情况同时存在）：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
• select_type查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询select_type 价值JSON 名称意义SIMPLE没有任何简单[SELECT]（不使用 [UNION]或子查询）PRIMARY没有任何最外层 [SELECT][UNION]没有任何中的第二个或以后的[SELECT]语句 [UNION]DEPENDENT UNION``````dependent( true)a 中的第二个或后面的[SELECT]语句 [UNION]，取决于外部查询UNION RESULT``````union_result的结果UNION。[SUBQUERY]没有任何首先[SELECT]在子查询中DEPENDENT SUBQUERY``````dependent( true)首先[SELECT]在子查询中，依赖于外部查询DERIVED没有任何派生表DEPENDENT DERIVED``````dependent( true)派生表依赖于另一个表UNCACHEABLE SUBQUERY``````cacheable( false)一个子查询，其结果无法缓存，必须为外部查询的每一行重新计算UNCACHEABLE UNION``````cacheable( false)[UNION] 属于不可缓存子查询的第二个或以后的选择1、SIMPLE：简单的select查询，查询中不包含子查询或者union 2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary 3、DERIVED：在from列表中包含的子查询被标记为derived（派生），mysql或递归执行这些子查询，把结果放在零时表里 4、UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived 5、UNION RESULT：从union表获取结果的select 6、DEPENDENT UNION:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响 7、SUBQUERY：在select 或 where列表中包含了子查询 8、DEPENDENT SUBQUERY：subquery的子查询要受到外部表查询的影响 9、UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存 10、uncacheable union:表示union的查询结果不能被缓存：sql语句未验证
• table对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集 1、如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名 2、表名是derivedN的形式，表示使用了id为N的查询产生的衍生 3、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id
• partitions查询将匹配记录的分区。该值适用NULL于非分区表
• type访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL****一般来说，好的sql查询至少达到range级别，最好能达到ref--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() --index_subquery：利用索引来关联子查询，不再扫描全表--unique_subquery:该连接类型类似与index_subquery,使用的是唯一索引--index_merge：在查询过程中需要多个索引组合使用，没有模拟出来--ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式--ref：使用了非唯一性索引进行数据的查找--eq_ref ：使用唯一性索引进行数据查找--const：表示通过索引一次就找到了--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现
• possible_keys显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
• key该key列指示 MySQL 实际决定使用的键（索引），如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
• key_len表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的。
• ref显示索引的那一列被使用了，如果可能，是一个常量const。
• rows根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好
• filtered该filtered列指示按表条件过滤的表行的估计百分比。最大值为 100，这意味着没有过滤行。从 100 开始减小的值表示过滤量增加。 rows显示检查的估计行数，rows× filtered显示与下表连接的行数。例如，如果 rows是 1000 并且 filtered是 50.00 (50%)，则要与下表连接的行数是 1000 × 50% = 500。
• Extra不适合在其他字段中显示，但是十分重要的额外信息--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找--using where:使用where进行条件过滤--using join buffer:使用连接缓存，情况没有模拟出来--impossible where：where语句的结果总是false

Mysql主存复制与读写分离

​ MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

---

主从复制原理

原理：

​ （1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

​ （2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

​ （3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

也就是说：

• 从库会生成两个线程,一个I/O线程,一个SQL线程;
• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
• 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

mysql主从形式

主从复制延时问题

​ mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高，slave的sql thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序，所以成本要高很多，另一方面，由于sql thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL thread所能处理的速度，或者当slave中有大型query语句产生了锁等待，那么延时就产生了。

mysql5.7之后使用MTS并行复制技术，永久解决复制延时问题

---

MTS
​

-- 查看并行的slave的线程的个数，默认是0.表示单线程showglobal variables like'slave_parallel_workers';-- 根据实际情况保证开启多少线程setglobal slave_parallel_workers =4;-- 设置并发复制的方式，默认是一个线程处理一个库，值为databaseshowglobal variables like'%slave_parallel_type%';-- 停止slave
stop slave;-- 设置属性值setglobal slave_parallel_type='logical_check';-- 开启slavestart slave
-- 查看线程数showfull processlist;

并行复制，就是在中间添加了一个分发的环节，也就是说原来的sql_thread变成了现在的coordinator组件，当日志来了之后，coordinator负责读取日志信息以及分发事务，真正的日志执行的过程是放在了worker线程上，由多个线程并行的去执行。

coordinator在进行分发的时候，需要遵循的策略是什么？

1、不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker中。
2、同一个事务不能被拆开，必须放到同一个worker中。

​ 按库分发：key值是数据库的名字，这个比较简单

​ 按表分发：key值是库名+表名

​ 按行分发：key值是库名+表名+唯一键

读写分离

MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。

​ MySQL读写分离能提高系统性能的原因在于：

​ 1、物理服务器增加，机器处理能力提升。拿硬件换性能。

​ 2、主从只负责各自的读和写，极大程度缓解X锁和S锁争用。

​ 3、slave可以配置myiasm引擎，提升查询性能以及节约系统开销。

​ 4、master直接写是并发的，slave通过主库发送来的binlog恢复数据是异步。

​ 5、slave可以单独设置一些参数来提升其读的性能。

​ 6、增加冗余，提高可用性。

分库分表->

Mycat和ShardingSphere

Mycat

​ 对于 DBA 来说，可以这么理解 Mycat： Mycat 就是 MySQL Server，而 Mycat 后面连接的 MySQL Server，就好象是 MySQL 的存储引擎,如 InnoDB，MyISAM 等，因此，Mycat 本身并不存储数据，数据是在后端的 MySQL 上存储的，因此数据可靠性 以及事务等都是 MySQL 保证的，简单的说，Mycat 就是 MySQL 最佳伴侣，它在一定程度上让 MySQL 拥有了 能跟 Oracle PK 的能力。

​ 对于软件工程师来说，可以这么理解 Mycat： Mycat 就是一个近似等于 MySQL 的数据库服务器，你可以用连接 MySQL 的方式去连接 Mycat（除了端 口不同，默认的 Mycat 端口是 8066 而非 MySQL 的 3306，因此需要在连接字符串上增加端口信息），大多数 情况下，可以用你熟悉的对象映射框架使用 Mycat，但建议对于分片表，尽量使用基础的 SQL 语句，因为这样能 达到最佳性能，特别是几千万甚至几百亿条记录的情况下。

​ 对于架构师来说，可以这么理解 Mycat： Mycat 是一个强大的数据库中间件，不仅仅可以用作读写分离、以及分表分库、容灾备份，而且可以用于多 租户应用开发、云平台基础设施、让你的架构具备很强的适应性和灵活性，借助于即将发布的 Mycat 智能优化模 块，系统的数据访问瓶颈和热点一目了然，根据这些统计分析数据，你可以自动或手工调整后端存储，将不同的 表映射到不同存储引擎上，而整个应用的代码一行也不用改变。

​ Mycat 的原理并不复杂，复杂的是代码，如果代码也不复杂，那么早就成为一个传说了。

---

何为数据切分？简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机） 上面，以达到分散单台设备负载的效果。

垂直切分：一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同 的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：

系统被切分成了，用户，订单交易，支付几个模块。

​ 优点： • 拆分后业务清晰，拆分规则明确；
​ • 系统之间整合或扩展容易；
​ • 数据维护简单。

​ 缺点： • 部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度；
​ • 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高；
​ • 事务处理复杂。

由于垂直切分是按照业务的分类将表分散到不同的库，所以有些业务表会过于庞大，存在单库读写与存储瓶 颈，所以就需要水平拆分来做解决。

---

水平切分： 相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中 包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分 到一个数据库，而另外的某些行又切分到其他的数据库中，如图：

优点： • 拆分规则抽象好，join 操作基本可以数据库做；
• 不存在单库大数据，高并发的性能瓶颈；
• 应用端改造较少；
• 提高了系统的稳定性跟负载能力。

缺点： • 拆分规则难以抽象；
• 分片事务一致性难以解决；
• 数据多次扩展难度跟维护量极大；
• 跨库 join 性能较差。

每种切分方式都有缺点，但共同的特点缺点有：

• 引入分布式事务的问题；
• 跨节点 Join 的问题；
• 跨节点合并排序分页问题；
• 多数据源管理问题。

ShardingSphere

​ Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款相互独立，却又能够混合部署配合使用的产品组成。 它们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

sharding-JDCB

定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

• 适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC；
• 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, HikariCP 等；
• 支持任意实现 JDBC 规范的数据库，目前支持 MySQL，PostgreSQL，Oracle，SQLServer 以及任何可使用 JDBC 访问的数据库。

ShardingSphere-Proxy

定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。 目前提供 MySQL 和 PostgreSQL（兼容 openGauss 等基于 PostgreSQL 的数据库）版本，它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端（如：MySQL Command Client, MySQL Workbench, Navicat 等）操作数据，对 DBA 更加友好。

• 向应用程序完全透明，可直接当做 MySQL/PostgreSQL 使用；
• 适用于任何兼容 MySQL/PostgreSQL 协议的的客户端。

sharding-sidecar

定位为 Kubernetes 的云原生数据库代理，以 Sidecar 的形式代理所有对数据库的访问。 通过无中心、零侵入的方案提供与数据库交互的啮合层，即

Database Mesh

，又可称数据库网格。

Database Mesh 的关注重点在于如何将分布式的数据访问应用与数据库有机串联起来，它更加关注的是交互，是将杂乱无章的应用与数据库之间的交互进行有效地梳理。 使用 Database Mesh，访问数据库的应用和数据库终将形成一个巨大的网格体系，应用和数据库只需在网格体系中对号入座即可，它们都是被啮合层所治理的对象。

三个组件对比

混合结构

ShardingSphere-JDBC 采用无中心化架构，与应用程序共享资源，适用于 Java 开发的高性能的轻量级 OLTP 应用； ShardingSphere-Proxy 提供静态入口以及异构语言的支持，独立于应用程序部署，适用于 OLAP 应用以及对分片数据库进行管理和运维的场景。

Apache ShardingSphere 是多接入端共同组成的生态圈。 通过混合使用 ShardingSphere-JDBC 和 ShardingSphere-Proxy，并采用同一注册中心统一配置分片策略，能够灵活的搭建适用于各种场景的应用系统，使得架构师更加自由地调整适合于当前业务的最佳系统架构。

---

Mycat和shardingSphere 都是分布式数据库中间件。