Mysql基础知识（三）索引、Mysql架构、日志、锁、主从复制、读写分离

文章目录

• • 一、Mysql索引
  • • 1.1 基础
    • 1.4 回表和索引下推
    • 1.5 索引维护
  • 二、Mysql架构
  • 三、Mysql日志
  • 四、锁
  • 五、主从复制
  • 六、读写分离

一、Mysql索引

1.1 基础

  磁盘预读(预读的长度一般为页（page）的整数倍)

页是存储器的逻辑块，操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页大小通常为4k），主存和磁盘以页为单位交换数据。

  索引是帮助 MySQL 高效获取数据的数据结构，存储在文件系统中。Mysql的 执行：

• 索引的文件存储形式与存储引擎有关

 不同的存储引擎，数据文件和索引文件存放的位置是不同的，因此有了分类：

1. 聚簇索引
    表数据和文件放在一起(innodb)。其中.frm存放的是表结构，.ibd存放的是数据文件和索引文件。Mysql的innodb默认引擎会把所有的数据文件放到表空间中，不会为每一个单独的表保存一份数据文件，如果需要将每一个表单独使用文件保存，设置如下属性：

set global innodb_file_per_table = on;

2. 非聚簇索引
    数据和索引单独一个文件保存(MyISAM)。其中.frm文件存放表结构，.MYI文件存放索引数据，.MYD存放实际数据。

1.1 索引文件的数据结构

  索引文件的结构：hash、二叉树、B树、B+树。

• 1、哈希表
   本质上是个数组，往里面存元素时，先通过哈希函数求得一个int值，再想该int值转换为数组中对应的位置，然后将元素插入。哈希表的数组中其实都是存的k-v键值对，key是真正存放的数据，value一般是某个固定值。
   哈希表可以完成索引的存储，每次在添加索引的时候需要计算指定列的哈希值，取模运算后计算出下标，将元素插入下标位置。适合场景：

等值查询
表中的数据是无序数据（范围查找的时候比较浪费时间，需要挨个进行遍历操作）

 哈希表在使用的时候，需要将全部的数据加载到内存，比较耗费内存空间，不是很合适。

• 2、B树和B+树
   平衡二叉搜索树（Self-balancing binary search tree）又被称为AVL树（有别于AVL算法），且具有以下性质：它是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。当插入数据次数过多时，旋转次数较多，影响性能。
   红黑树是基于AVL树的一个升级，损失了部分查询性能，来提升插入的性能，在红黑树中最低子树和最高子树之差小于2倍即可，在插入的时候，不需要进行N多次的旋转操作，而且还加入了变色的性能，来满足插入和查询性能的平衡。
   但是二叉树以及N多的变种都不能支撑索引，原因是树的深度过深，导致IO次数变多，影响数据读取的效率。所以此时需要用多叉树来实现索引。
   B树示例：
  
   B树的特点：

1、所有键值分布在整棵树中
2、所有有可能在非叶子节点结束在关键字全集内做一次查找，性能逼近二分查找
3、每个节点最多拥有m个子树
4、根节点至少有2个子树
5、分支节点至少拥有m/2颗子树（除根节点也叶子节点之外都是分支节点）
6、所有叶子节点都在同一层、每个节点最多可以拥有m-1个key，并且以升序排列。

 在上面的例子中，假设每个节点占用一个磁盘块，一个节点上有两个升序排列的关键字和是哪个指向子树根节点的指针，指针存储的是子节点所在磁盘块的两个地址。两个关键词划分成的三个范围域对应的是三个指针指向的子树的数据的范围域。以根节点为例，关键字为16和34，P1指针指向的子树的数据范围为小于16，P2指针指向的子树的数据范围为16-34，P3指针指向的子树的数据范围为大于34。
 查找关键字过程：

1、根据根节点找到磁盘块1，读入内存（磁盘IO第一次）
2、比较关键字28在区间（16，34），找到磁盘块1的指针P2
3、根据P2指针找到磁盘块3，读入内存（磁盘IO第二次）
4、比较关键字28在区间（15,31），找到磁盘块3的指针P2
5、根据P2指针找到磁盘块8，读入内存（磁盘IO第三次）
6、在磁盘块8中的关键字列表中找到关键字28

 B树的缺点：

1、每个节点都有key和data，而每个页存储空间是有限的，如果data比较大的话会导致每个节点存储的key数量变小
2、当存储的数据量很大的时候会导致深度较大，增大查询时磁盘IO次数，进而影响查询性能

 B+树是在B树的基础上做的一种优化，变化如下：

1、B+树每个节点可以包含更多的节点，这样做的原因有两个：一是为了降低树的高度，二是将数据范围变成多个区间，区间越多，数据检索越快
2、非叶子节点存储key，叶子节点存储key和数据
3、叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高。

 在B+树上有两个头指针，一个指向根节点，一个另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+树进行两种查找：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进随机查找。

 Innodb，B+树叶子节点直接放置数据的示例：

 Innodb是通过B+树结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id来作为主键。
 如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引来找到对应的记录，叫做回表。
 MyISAM，B+树和Inondb相比，差异之处就在于索引和表数据是分开存储的，看个例子：

1.2 索引种类

 mysql索引的五种类型：主键索引、唯一索引、普通索引和全文索引、组合索引。 通过给字段添加索引，可以提高数据的读取速度，提高项目的并发能力和抗压能力。

 普通索引：仅加速查询
 唯一索引：加速查询 + 列值唯一（可以有null）
 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
 全文索引：对文本的内容进行分词，进行搜索

1. 主键索引
    主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。主键一般情况可以设置为自增，主键自增时，插入数据时都是在最后追加，不会在表中间插入数据，索引方便维护。
2. 唯一索引
    索引列的所有值都只能出现一次，即必须唯一，值可以为空。
3. 普通索引
    基本的索引类型，值可以为空，没有唯一性的限制。（覆盖索引，简单来说select查询的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖）
4. 全文索引
    全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。
    MyISAM支持，Innodb在5.6之后支持。

用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

5. 组合索引
    多列值组成一个索引，专门用于组合搜索。
    如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：
    假设存在组合索引(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即要想使用c2列进行查找，必需出现c1等于某值。
    这就是最左匹配原则。简单来说，在两个列上的组合索引，有个前后顺序（c1，c2），在查询c1时可以使用该组合索引，在同时查询c1、c2时也可以使用该索引，但只查询c2时不能使用该索引。

1.3 存储引擎分类

  两种存储引擎的简单对比：

1.4 回表和索引下推

 什么是回表？简单来说就是数据库根据索引（非主键）找到了指定的记录所在行后，还需要根据主键再次到数据块里获取数据。比如：

select * from test where name = 'a' and age =10 

 查询时要要先找到name为"a"的列，再根据id去查找age为10的数据，这个过程就是回表。

 索引条件下推(Index Condition Pushdown),简称ICP。MySQL5.6新添加，用于优化数据的查询。
 索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。
 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

1.5 索引维护

 索引在插入新的值的时候，为了维护索引的有序性，必须要维护，在维护索引的时候需要需要分以下集中情况：

1. 如果插入一个比较大的值，直接插入即可，几乎没有成本
2. 如果插入的是中间的某一个值，需要逻辑上移动后续的元素，空出位置
3. 如果需要插入的数据页满了，就需要单独申请一个新的数据页，然后移动部分数据过去，叫做页分裂，此时性能会受影响同时空间的使用率也会降低。

 尽量使用自增主键作为索引。

二、Mysql架构

• 1、客户端
   客户端可以是JDBC代码，也可以是可视化客户端。
• 2、 Server
   图中最大的块是Mysql的Server，代表MySQL实例，真正提供数据存储和数据处理功能的MySQL服务器进程。
• 3、 连接器
   连接器负责跟客户端建立连接，获取权限、维持和管理连接：
  1. 用户名密码验证
  2. 查询权限信息，分配对应的权限
  3. 可以使用show processlist查看现在的连接
  4. 如果太长时间没有动静，就会自动断开，通过wait_timeout控制，默认8小时

 连接可以分为两类：

长连接：推荐使用，但是要周期性的断开长连接
短链接

• 4、 查询缓存
   当执行查询语句的时候，会先去查询缓存中查看结果，之前执行过的sql语句及其结果可能以key-value的形式存储在缓存中，如果能找到则直接返回，如果找不到，就继续执行后续的阶段。
   不推荐使用查询缓存：

1、查询缓存的失效比较频繁，只要表更新，缓存就会清空
2、缓存对应新更新的数据命中率比较低

• 3、 分析器
   词法分析：Mysql需要把输入的字符串进行识别每个部分代表什么意思

把字符串 T 识别成 表名 T
把字符串 ID 识别成 列ID

 语法分析：根据语法规则判断这个sql语句是否满足mysql的语法，如果不符
合就会报错“You have an error in your SQL synta”。

• 4、 优化器
   在具体执行SQL语句之前，要先经过优化器的处理

当表中有多个索引的时候，决定用哪个索引
当sql语句需要做多表关联的时候，决定表的连接顺序

 不同的执行方式对SQL语句的执行效率影响很大

RBO:基于规则的优化
CBO:基于成本的优化（用的比较多，性能较好）

三、Mysql日志

3.1 Redo日志和Undo日志

 这两种日志是存储引擎层面的日志。

• 1、Redo日志
   Redo日志，innodb存储引擎的日志文件。
   当发生数据修改的时候，innodb引擎会先将记录写到redo log中，并更新内存，此时更新就算是完成了，同时innodb引擎会在合适的时机将记录操作到磁盘中。
    Redolog是固定大小的，是循环写的过程。
   有了redolog之后，innodb就可以保证即使数据库发生异常重启，之前的记录也不会丢失，叫做crash-safe。
   Redo保证了持久性（隔离性是通过锁来实现）。
  
• 2、Undo日志
   Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用Undo Log来实现多版本并发控制。
   在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

 注意：undo log是逻辑日志，可以理解为：

当delete一条记录时，undo log中会记录一条对应的insert记录
当insert一条记录时，undo log中会记录一条对应的delete记录
当update一条记录时，它记录一条对应相反的update记录

3.2 binlog

  binlog是Server层面的日志，主要做mysql功能层面的事情。
 binlog与redo日志的区别:

1. redo是innodb独有的，binlog是所有引擎都可以使用的
2. redo是物理日志，记录的是在某个数据页上做了什么修改，binlog是逻辑日志，记录的是这个语句的原始逻辑
3. redo是循环写的，空间会用完，binlog是可以追加写的，不会覆盖之前的日志信息

 Binlog中会记录所有的逻辑，并且采用追加写的方式。一般在企业中数据库会有备份系统（用于应付数据丢失等情况），可以定期执行备份，备份的周期可以自己设置。
 恢复数据的过程：

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

3.3 数据更新的流程

 执行流程：

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

 Redo log为什么两阶段提交？

• 1、先写 redo log 后写 binlog
   假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
• 2、先写 binlog 后写 redo log
   如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

 可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

四、锁

4.1 MySQL锁的基本介绍

 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
 相对其他数据库而言，MySQL的锁机制比较简单，其最 显著的特点是不同的存储引擎支持不同的锁机制。比如：
  1. MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
  2. InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

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

 从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适。
 仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

 OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。

 OLTP 系统强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；OLAP 系统则强调数据分析，强调SQL执行市场，强调磁盘I/O，强调分区等。

4.2 MyISAM表锁

 MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
 对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的。
 假设有以下数据：

CREATE TABLE `mylock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('4', 'd');

• 1、写锁阻塞读
   ​当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作。其他线程的读写操作都会等待，直到锁释放为止。
   ​在命令行可以这样给表加上写锁：lock table mylock write。加上该写锁之后，该session可以正常操作，别的session对该表的查询会阻塞。只有释放该锁：unlock tables后，别的session查询才能正常进行。
• 2、读锁阻塞写
   ​一个session给表加读锁，这个session可以锁定表中的记录，但更新和访问其他表都会提示错误，同时，另一个session可以查询表中的记录，但更新就会出现锁等待。
   ​当session1给表加上读锁：lock table mylock read后，session1可以查询该表记录，session1不能查询没有锁定的表，session1插入或者更新表会提示错误。
   ​session2可以查询该表记录，可以查询或者更新未锁定的表，插入数据会等待获得锁。当session1释放锁unlock tables后，session2获得锁，更新成功。

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

• 3、MyISAM的并发插入问题
   ​MyISAM表的读和写是串行的，这是就总体而言的，在一定条件下，MyISAM也支持查询和插入操作的并发执行。
   ​假如session1可以通过lock table mylock read local获取表的read local锁定，session1不能对表进行更新或者插入操作，session1不能查询没有锁定的表，session1不能访问其他session插入的记录。此时session2可以查询该表的记录，session2可以进行插入操作，但是更新会阻塞,。
   ​然后session1通过unlock tables释放锁资源，session1可以查看session2插入的记录，session2获取锁，更新操作完成。

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

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 352   |
| Table_locks_waited    | 2     |
+-----------------------+-------+

 ​如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

4.3 InnoDB锁

 ​事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性：原子性、一致性、隔离性、持久性。
 相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一些问题：

• 1、脏读
    一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读” 。
• 2、不可重复读
   一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了，这种现象叫做“不可重复读”。
• 3、幻读
   一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读” 。

 上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。
 数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别。

|                  | 脏读 | 不可重复读 | 幻读 |
| :--------------: | :--: | :--------: | :--: |
| read uncommitted |  √   |     √      |  √   |
|  read committed  |      |     √      |  √   |
| repeatable read  |      |            |  √   |
|   serializable   |      |            |      |

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

mysql> show status like '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的行锁模式及加锁方法

• 1、共享锁
   又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
• 2、排他锁
   又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务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…查询数据，因为普通查询没有任何锁机制。

 InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，Oracle是通过在数据块中对相应数据行加锁来实现的。
 InnoDB这种行锁实现特点意味着：InnoDB只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

• 1、在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁
   比如创建一张表：

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

 session1可以通过select * from tab_no_index where id = 1 for update命令，只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

• 2、创建带索引的表进行条件查询，innodb使用的是行锁
   比如创建一张表：

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

 此时当一个session对一个行加锁时，不影响另一个session对别的行的操作。

4.4 总结

• 1、MyISAM的表锁总结

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

• 2、InnoDB表总结

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

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

1. 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
2. 选择合理的事务大小，小事务发生锁冲突的几率也更小；

• 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；

3. 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
4. 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
5. 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

五、主从复制

 为什么需要主从复制？

1. 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
2. 做数据的热备
3. 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

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

5.1 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语句逐一执行;

 需要注意的是：

1、master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。
2、slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。
3、Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。
4、Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）。
5、master和slave两节点间时间需同步。

 主从复制的具体步骤：

1. 从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave。
2. 从库的IO线程和主库的dump线程建立连接。
3. 从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。
4. 主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。
5. 从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中。
6. 从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge。

5.2 mysql主从形式

 有以下五种形式：一主一从、主主复制、一主多从、多主一从、联级复制。

5.3 mysql主从同步延时分析

 mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高，slave的sql thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随机的，不是顺序，所以成本要高很多，另一方面，由于sql thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL thread所能处理的速度，或者当slave中有大型query语句产生了锁等待，那么延时就产生了。
 解决方案：
  1、业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。
  2、单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
  3、服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。
  4、不同业务的mysql物理上放在不同机器，分散压力。
  5、使用比主库更好的硬件设备作为slave，mysql压力小，延迟自然会变小。
  6、使用更加强劲的硬件设备
 MySQL 5.7可以使用并行复制原理解决复制延迟问题。

六、读写分离

 MySQL读写分离基本原理是让master数据库处理写操作，slave数据库处理读操作。master将写操作的变更同步到各个slave节点。
 MySQL读写分离能提高系统性能的原因在于：
  1、物理服务器增加，机器处理能力提升。拿硬件换性能。
  2、主从只负责各自的读和写，极大程度缓解X锁和S锁争用。
  3、slave可以配置myiasm引擎，提升查询性能以及节约系统开销。
  4、master直接写是并发的，slave通过主库发送来的binlog恢复数据是异步。
  5、slave可以单独设置一些参数来提升其读的性能。
  6、增加冗余，提高可用性。

本文地址：https://blog.csdn.net/m0_37741420/article/details/111968329