关于MySQL阅读小林coding和极客时间笔记

MySQL执行流程

MySQL分为：server层 和 存储引擎层

• server层：负责建立、分析和执行SQL，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等，所有的内置函数和所有跨存储引擎的功能都在 Server 层实现。

• 存储引擎层：负责数据的存储和提取

一、连接器

先TCP三次握手建立连接，也分为长连接和短连接，和HTTP一样

并且连接时长有限制，由参数控制，超出时常连接器自动断开

连接数也有限制，不能超过最大连接数

解决长连接占用内存问题：

1、定时断开长连接

2、客户端主动重置连接，在执行缓存很大的操作后，调用mysql_reset_connection()重置连接，会恢复到刚创建完时的状态

二、查询缓存

先查询缓存，没有没有在缓存命中才会向下执行

缓存以key-value形式保存在内存中，key就是指令，value为返回值

并且由于更新表后会清空缓存，所以命中率低；8.0版本后被删掉了

三、解析器

1、词法分析

2、语法分析

四、预处理器

1、查询SQL查询语句中的表或字段是否存在

2、将select* 中的 * 符号替换为所有列

五、优化器

优化器主要负责将 SQL 查询语句的执行方案确定下来

基于查询成本的考虑。决定使用哪个索引，如主键索引或覆盖索引

六、执行器

与存储引擎交互

1、主键索引

优化器的访问类型为const，存储引擎找到符合条件的语句返回执行器，执行器判断是否满足，满足直接返回客户端，由于执行器查询是while循环，但优化器访问类型为const，下一次返回永远指向-1函数（主键查询唯一，只有一条记录），结束循环。

注：客户端等查询语句完成后，才会显示所有记录

2、全表扫描

优化器的访问类型为all，存储引擎全表扫描，直到把表中所有记录读完

3、索引下推

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了。

如索引1，索引2

不用索引下推时：当满足索引1，存储引擎返回执行器，执行器判断索引2是否满足条件，不满足跳过

使用索引下推：当满足索引1，存储引擎先不执行回表操作，判断索引2是否成立，如果成立返回执行器

MySQL存储一行数据

表空间由：段、区、页、行组成

其中数据是按照行来存储的，但是是按照页来读取的

InnoDB行格式：compact

compact行格式：

分为记录的额外信息和记录的真实数据

额外信息：由于varchar是变长的，要存放数据的大小

并且null值和变长字段长度是按照逆序存放的，其中null表为0代表不是null，1代表为null

另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

记录头信息中：有delete_mask，要删除记录是置1，还有下一条记录的位置（指向真实数据和记录头信息中间），也因此是null值和变长度字段是逆序存放，向左读额外信息，向右读真实信息

真实信息：trx_id和roll_ptr是MVCC机制，row_id是没有主键时的隐藏字段

行溢出：如果一页存储不了一条记录，多的数据就会存放到溢出页中，真实数据处会存储指向溢出页的地址

索引分类

索引和数据存放在存储引擎中

数据结构分类：B+tree索引、Hash索引、Full-text索引。

B+tree：是一种多叉树，叶子节点存放数据，非叶子结点存放索引，并且每个节点中数据按主键顺序存放的

InnoDB的B+树特点：每个叶子节点有两个指针，形成一种双向链表，这样范围查找不用从头节点重新查找

优点：查询效率高，数据大的情况下层数也很低，因此查询磁盘I/O次数低

物理存储分类：主键索引、二级索引

主键索引和二级索引默认用B+tree索引，其中主键索引叶子节点存放整张表的记录数据，二级索引叶子节点存放主键

因此二级索引查到对应叶子节点后需要回表，再通过主键索引找到对应数据

除非查的数据是二级索引能查询到如主键值，只需查一次B+树操作，这种二级索引叫覆盖索引

B+tree对比

1、和Btree对比：B+树只有叶子节点放数据，B树非叶子节点也放数据，所以B+树相同I/O操作可以查到更多节点，且B树叶子节点不是双向链表。B树的查找效率可能更快一些，但是删除数据时，B+树只删除叶子节点，B树的删除操作可能还需要重新平衡树

2、和二叉树比：数据量很大时，二叉树的高度就会很高，需要很多次I/O操作才能找到目标数据

3、和hash比：hash不适合范围查找，适合等值查找

字段特性分类：主键索引、唯一索引、普通索引、前缀索引

主键索引：PRIMARY，只能有一个，不能是NULL

唯一索引：UNIQUE，可以有多个，索引列值必须唯一，可以是NULL

字段个数分类：单列索引、联合索引

联合索引：

联合索引存在最左匹配原则：如（a, b, c）当a确定范围时，b是有序的，c是无序的。只能a相同时找b，b相同时找c

b和c是局部有序，全局无序，必须先匹配a，再匹配b，再匹配c

联合索引范围查询：并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，

如a > 10, b = 1；其中b没有用到联合索引，因为a>10时，b时无序的，如a = 11, b = 3, a = 12, b = 1

而 a >= 11，b = 1中，当a = 11时，b用到了联合查询，可以从a = 11，b =1这条语句开始查询

索引使用场景

创建索引可以提升查询效率，但是建立索引会占用物理空间，并且增删查改还要维护B+树

使用索引：1、字段有唯一性 2、经常用WHERE查询条件 3、和order_by / group_by配合使用，不用再排序（索引后自动排好序）

无需索引：1、字段频繁更新 2、大量重复字段（性别） 3、数据很少

优化索引

1、主键设置为自增的，这样插入新数据时，数据库就会根据主键插入到对应叶子节点中，无需重新移动数据

2、索引一个大字符串时，可以使用前缀索引，减少索引项的大小，查询速度快

3、使用覆盖索引，不需要查询出整行信息，减少大量I/O操作

如需要名称、价格 索引出商品ID再回表找到整行信息；而先创建联合索引ID、价格、名称，则索引时只出来名称、价格、ID

就是二级索引可以查询到想要的记录，避免回表操作

4、索引最好设置为NOT NULL：如果是允许NULL，那行格式会用1字节存储NULL值列表 ；且优化器做索引选择更复杂

什么时候索引失效

1、联合索引要通过最左优先方式进行索引匹配

2、不能对索引进行计算、函数操作

3、当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

事务

开启事务操作后统一提交，如果中途发生错误，那么数据库就会回滚到事务发生前的状态

特征：

1、原子性：事务中的事件要不都完成，要不都不完成

2、隔离性：多个事务可以并发执行，不会相互影响

3、一致性：操作前和操作后，数据库保持一致性状态（A-200, B+200）

4、持久性：事务处理结束后，对数据库的修改是永久的

并行事务会发生的问题：脏读、不可重复读、幻读

在 MySQL 有两种开启事务的命令，分别是：

• 第一种：begin/start transaction 命令；

• 第二种：start transaction with consistent snapshot 命令；

这两种开启事务的命令，事务的启动时机是不同的：

• 执行了 begin/start transaction 命令后，并不代表事务启动了。只有在执行这个命令后，执行了增删查改操作的 SQL 语句，才是事务真正启动的时机；

• 执行了 start transaction with consistent snapshot 命令，就会马上启动事务。

事务的隔离级别

隔离级别：读未提交 < 读已提交 < 可重复读 < 串并行(对记录加上读写锁)

先介绍read_view:

有四个字段：创建该事务的id，活跃且未提交事务的id、活跃且未提交事务中最小id、应该交给下一个事务的id（最大id+1）

脏读

如果事务B读到了事务A中更新后但还没有提交的数据，就是脏读（A随时会回滚）

通过读已提交可以避免脏读

读已提交：在每次读取数据时，更新read_view

A先 和 B后开启事务，如果事务A改变了数据，B读取数据发现在活跃列表中，则不可读，通过undo_log链找上一个版本的记录，之后A提交，B再读取数据时更新read_view，发现数据中的trx_idA小于min_trx_id，则可读取

注：真实数据中有trx_id和roll_pointer，roll_pointer是一个指针，指向每个旧版本记录

注：MVCC（基于多版本的并发控制协议）

如果trx_id大于max_trx_id，说明在当前事务之后又开启了一个新事务，并且新事物对该记录做了修改，那么这个改动对当前查询事务是不可见的；

如果trx_id小于min_trx_id，说明该版本对应的事务已经提交，那么该版本记录是可见的；

如果在中间，则还要找活跃列表

不可重复读

如果事务A读同一个数据，但两次结果不一样（中途被另一个事务更新后提交了），就是不可重复读

通过可重复读解决

可重复读：启动事务时生成一个read_view，然后事务执行期间都用这个read_view

A先开启事务（trxid51）， B后开启事务(trx1d52, 活跃列表为51-52)，如果事务A改变了数据，并且提交，但是由于事务B的read_view是在事务开始时生成的，所以事务A即使提交了，判断记录中的trx_id没有更新（51还在活跃列表中），还是会通过undo_log找到上一个版本的记录

注：可读数据的trx_id一定是小于自己id，且不在活跃列表中

幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。 如一开始A查询到5条，但事务B又中途插入1条，再查询就变成了6条，类似不可重复读

区别：不可重复度读取的数据不一致，幻读读取的记录数量不一致（第二次读取多了一行）

解决：

1、针对快照读（select）MVCC的可重复读可以解决

2、针对当前读（select...for update）：通过加间隙锁+记录锁的方式，如A先select...for update id > 2的记录，然后B插入id = 5的记录，这时由于A加id范围为2 到 正无穷 的间隙锁，B的插入语句被阻塞，会插入一个生成一个插入意向锁并处于等待状态，直到A提交事务

注：当前读会先查询最新版本的数据（因为当前都可能要对数据更改，万一被删除了，所以要更新）且对数据进行修改前都需要先读后写

可能还会发生的场景：

1、A快照读，B再插入，A查询不出这个记录，但是如果A更新了记录，所以要读的记录的trx_id变成A，因此A可读新增的记录

2、A先快照读，B再插入，A再当前读就会出现幻读

因此在开启事务之后，立刻执行当前读，对记录加间隙锁

全局锁

执行后，整个数据库就处于只读状态了

应用场景：给数据库逻辑备份

缺点：在备份期间，业务只能读数据，不能更新数据，造成业务停滞

解决：开启可重复读的隔离级别，在备份数据库前先开启事务，这样就可以同时更新数据库

表级锁

表锁

表级别的读写锁

lock tables ... read/write

加了读锁后，不能执行写操作

加了写锁，并不能进行读写操作

元数据锁（MDL锁）

无需显式使用，保证读写的正确性

1、对表做CRUD(增删查改)操作时，加MDL读锁

2、对表做结构变更操作时，加MDL写锁

MDL锁在事务结束后释放

问题：如果一个长事务A对表进行CRUD操作，持有了MDL读锁，后续的线程的CRUD操作不会阻塞，然后线程C想写表操作就堵塞，导致后续其他线程的CRUD操作都阻塞，导致线程很快爆满，因为阻塞后形成一个阻塞队列，且写锁获取优先级高于读锁

意向锁

表级锁

意向锁的目的是为了快速判断表中是否有被加锁

在加入独占锁前要加入意向独占锁

在加入共享锁前要加入意向共享锁

如果没有意向锁，在加入独占表锁前要一行一行判断有无独占锁，效率慢

有意向锁后，在加入独占锁前先加入表级别的意向独占锁，这样后续加入独占表锁时，直接可以查看表中有无意向独占锁

AUTO-INC锁

表级锁

在插入数据时，通过AUTO-INC锁实现主键的自增，可以不指定主键的值

方法：在插入数据时，会加一个表级AUTO-INC锁，执行完插入语句后释放

但是针对大量插入数据，效率较低，可以通过设置参数，采用轻量级锁实现自增

方法：在插入数据时，会加一个轻量级锁，给主键赋值一个自增的值后释放锁

问题：轻量级锁搭配binlog格式为statement使用时，会发生主从数据库不一致问题，

如A插入1、2，B插入3，A插入4、5，主库是这样记录的，但从库格式为statement时，会顺序记录，先记录A的insert语句后再记录B

如果从库格式改为row，就会按照主库的自增值来执行，这样就不会发生不一致问题

记录锁（Record lock）

分为X锁（排它锁）和S锁（共享锁），当事务提交后，释放锁

其中只有S锁和S锁兼容，读读兼容

作用：锁定一条记录

注：当前读 会和 未提交事务的写冲突，未提交事务写完后锁没有释放，当前读也需要加锁，会被阻塞

间隙锁(Gap lock)

前开后开

只存在于可重复读隔离级别，防止幻读现象

间隙锁是兼容的，可以允许共同间隙范围内的间隙锁

锁定一个范围的记录

X和S类型区别不大

记录+间隙锁（Next-key lock）

前开后闭/前闭后开

临键锁，锁定一个范围并且锁住记录本身

由于包含了记录锁，因此X型的记录锁不兼容，第二个锁会阻塞

但是（x, 正无穷] 可以兼容

插入意向锁

当A插入一条记录时，首先判断插入位置是否被插入了间隙锁，如果有的话会生成一个插入意向锁，并设置为等待状态，直到事务B提交

属于行级别锁，并不是意向锁，是一种特殊的间隙锁

insert时加的就是隐式锁，碰到间隙锁时，生成插入意向锁；碰到唯一键冲突时，给记录加上S型记录锁

加锁规则

原则2：如覆盖索引时，只给普通索引加锁，不会给主键索引加锁

只有等值查询才会优化，范围查询不优化，注：>= 10 ，先等值查询10，在范围查询（10， 20）

删除时候加入limit限制，可以缩小锁的范围，本来会查找到不符合条件的值，退化为间隙锁，如果有limit满足条件则不加锁了

死锁

1、行锁导致死锁

A事务中 要对id = 1记录写，对id = 2记录写，再提交

B事务中 要对id = 2记录写，对id = 1记录写，再提交

这样A占有1的行锁，B占有2的行锁，造成死锁

2、间隙锁导致死锁

由于间隙锁兼容，如A和B的间隙锁都是（1， 5）

当A要在间隙区间内插入4，B也要插入4

那么A遇到B的间隙锁 ，B遇到A的间隙锁，形成死锁，都无法插入

注：next-key lock 是先加间隙锁，然后加行锁

解决死锁办法

1、设置事务等待锁的超时时间，当超过超时时间时，触发回滚，参数为lock_wait_timeout

2、主动检测死锁，当事务主动检测到死锁时，就对这个事务进行回滚，让其他事务得以执行

由于超时时间不好设置，因使用死锁检测，但是当很多事务更新同一行时，死锁检测会消耗大量的CPU资源，时间复杂度为O(N)

因此对于一定不会出现死锁的场景关闭死锁检测

或者控制并发度，在进入引擎时，让客户端进入一个等待队列

或者对修改的数据增加数据量（a = b + c + d），随机挑一个数据进行更新，减少冲突

日志--保证数据不丢失

redo_log(回滚日志)

当记录需要更新的时候，InnoDB先把记录写到redo_log中，并且InnoDB会在合适的时间将操作更新到磁盘中

redo_log是固定大小，checkpoint当前需要擦除位置，write_pos是当前记录位置，

write向后循环到checkpoint之前代表log中空的部分，如果write追上check那么就停下来擦除记录

有了redo_log，数据库异常重启那么之前提交的记录都不会丢失，这个能力成为crash_safe

注：redo_log是InnoDB特有的，server层自己的日志为bin_log

bin_log(归档日志)

和redo_log的区别：

1、redo_log是InnoDB特有的，binlog是MYSQL的server层实现的，所有引擎都能用2

2、redo_log是物理日志，记录了修改，bin_log是逻辑日志，记录了语句的原始逻辑

3、redo_log是循环写的，固定大小；bin_log是追加写的，文件写完后切换到下一个文件

一个update语句实现流程：一定要两阶段提交事务，保证redolog 和 binlog的逻辑一致

1、找到ID所在数据行

2、给行数据中的一个值 + 1

3、将更新操作记录到redo_log，此时redo_log是prepared状态

4、生成这个操作的binlog，并将binlog写入磁盘

5、提交事务，将redolog改为commit状态

如果先redolog 后 binlog，如果redolog后数据库崩溃，则binlog没有这条语句（即使奔溃，redolog依旧能恢复数据）

如果先binlog后redolog，如果binlog后数据库奔溃，由于redolog没写，事务无效回滚，则binlog多了一条记录

事务A要得到1，必须从C开始一步一步回滚操作，得到1；

因此长事务会造成可能用到的回滚记录必须保存，造成大量占用存储空间

如B是个长事务进行了大量操作，那么A要得到1，必须从B的开始操作一直回滚。

回滚日志删除时机：当系统中没有比这个回滚日志更早的read_view时删除

如B事务提交后，没有更早的read_view就删除日志，如果B事务提交前有read_view且提交后还在，那么就不删除

redo_log 和 bin_log缺一不可

redo_log因为是循环写，历史日志没法保存，起不到归档的效果

bin_log没法支持崩溃恢复

binlog的写入机制

事务执行时把日志写入binlog_cache中，事务提交时将binlog_cache写入binlog文件中

由于一个事物的binlog是不能被拆开的，每个线程都分配了一个binlog_cache内存

然后在合适的时机将binlog文件持久化到磁盘中(fsync)

redolog的写入机制

事务执行过程中，将redolog写到redolog_buffer中

由于如果MYSQL重启的话，事务没有提交会回滚，因此不存在日志丢失的问题

事务提交时redolog写入策略：

1、redolog只留在redolog_buffer中

2、redolog持久化到磁盘(fsync)

3、redolog写到磁盘中的page_cache中，但没有持久化(write)

存储引擎的数据结构

1、哈希表只适合等值查询

对于一个范围来说，因为键值对应的hash_key不是一样的，需要全部扫描

2、有序数组

有序数组范围查询效率高，但是增加和删除数据麻烦，只适合静态存储引擎

3、BST树

BST树层数太高，I/O操作次数高，因为索引还要写入到磁盘上，查询时间慢

索引维护

主键自增问题

主键自增因为是追加操作，不涉及到挪动其他记录，不会触发叶子节点分裂

而有业务逻辑的字段作为索引，很难保证有序插入，成本较高，因此尽量使用自增字段作为主键

NOT NULL PRIMARY KEY AUTO INCREMENT

不适用主键自增的场景：KV场景，即只有一个索引且该索引是唯一索引，只需要一次查表操作，避免搜索两棵树

联合索引/最左前缀

因为联合索引有最左匹配原则，如(A, B)联合索引，那么如果有业务需要单独查找A，B的话，A可以用索引(A, B)，但还需要为B创建一个单独索引(B)

对于长字符串，可以使用索引(A, B)加前缀索引，就不用为长字符串单独创建索引

但是前缀索引只能是右模糊，不能左/左右模糊

索引下推

现在有索引（姓名，age），执行WHERE name like 顾% and age = 10;

如果不开启索引下推，那么InnoDB判断完姓名就会回表，不会查看索引age

而开启索引下推，那么InnoDB会查看age索引是否满足条件，避免回表

逻辑备份场景

在从库逻辑备份的时候，主库做了DDL，从库的现象

1、如果在show create table前（拿到表结构）DDL到达，则备份DDL后的表结构

2、如果在select * （导数据）时刻DDL达到，则报错，备份终止

3、如果在备份开启后到达，由于MDL读锁被mysqldump(备份)占有，主从延迟

4、如果在备份后到达，则备份得到的是DDL前的结构

加锁逻辑场景

把最容易造成锁冲突的锁往后放，因为锁是在事务提交后释放，这样的话占有这行数据的锁持有时间不长，不容易造成冲突

普通索引和唯一索引的区别

查询过程

普通索引找到满足条件的记录后，查询下一个数据，不满足然后返回

唯一索引找到满足条件的记录后，返回

性能差距：微乎其微，因为引擎是按页进行读写的，普通索引只需要指针多一次计算

更新过程

如果是唯一索引的话，每次插入语句都要检查索引的唯一性，没有冲突插入这个值

如果是普通索引的话，直接找到索引的位置，然后插入这个值

所以，如果要更新的目标页如果在内存中，那么普通索引和唯一索引性能一样

如果不在，则唯一索引先要将数据页读入内存，然后判断冲突，插入这个值

而普通索引的话，直接将更新记录在change_buffer中，就结束。

change_buffer会在该数据页下次被访问读入内存的时候执行merge操作，将change_buffer中的操作应用到原数据页中

merge：访问数据页会触发merge，后台定期merge，数据库正常关闭也会merge

注：写入change_buffer中的记录，写入redo_log中，因此如果数据库宕机change_buffer中的数据可以从redo_log中找回

change_buffer应用场景：

1、当更新记录后马上对记录进行查询的话，那么反而会增加change_buffer的维护代价

2、其它场景应用change_buffer都会减少I/O操作

merge执行流程：

1、从磁盘中读入数据页到内存

2、从change_buffer中找到这个数据页的记录，然后更新

3、将change_buffer的变更 和 数据页的变更写入redo_log

怎么给字符串字段加索引

1、字符串作为索引

2、前缀索引

如果将整个字符串作为索引的话占据的空间较大，但是可以利用覆盖索引，并且读数据的次数会少

如果用前缀索引的话，不可以利用覆盖索引，并且需要多次读数据并回表检查字符串是否相等

前缀索引长度的选择：通过count(distince left(email, n)) as Ln，判断前n个字节的前缀索引的区分度

其他选择：

1、如身份证号，前面一样，可以将身份证号倒序存储，然后用前缀索引

2、使用hash字段，并创建索引，通过字符串计算出来的hash值作为索引

优化器选错索引

优化器会根据扫描行数，排序、使用临时表等因素综合判断使用哪个索引

如果优化器选错索引：

1、通过 analyze table t重新统计索引信息

2、强制使用索引 force index(a)

3、新建一个更合适的索引

MYSQL突然运行很慢

原因：mysql在刷脏页

刷脏页的场景：

1、redo_log写满了，需要更新check_point，将check和write中的脏页flush到磁盘

这时候会阻塞后续的更新，尽量避免

2、内存不足

这时候需要控制脏页的数量/比例，通过脏页比例的参数控制刷脏页的性能

根据innodb_io_capacity告诉innodb磁盘能力控制刷脏页速度

3、空闲时刷脏页

4、正常关闭时刷脏页

表数据删除后空间不变

表数据既可以存在共享表空间中，也可以存放在单独的文件中，最好存在单独文件中容易管理

数据删除流程：

删除记录后文件大小不缩小，用于后面复用

数据页删除后，也用于复用，或者合并之后复用（A数据合并到B中，复用A）

因此delete命令只是将记录或数据页标记为可复用，不回收表空间

造成空洞：

1、删除数据

2、插入数据，当数据页不够时申请一个新页保存数据，原来的数据页空洞（A，50 52，要插入51，申请B 51 52，原A52位置空洞）

因此大量CRUD后的表都可能存在空洞，需要重建表达到收缩表空间的目的

通过alter table A engine = innodb重建表

重建表：

有Online和 非Online

非Online时在重建过程中写数据会造成数据丢失

Online时写数据，会将数据写到row_log中，重建完成再将row_log中操作应用到临时文件，再替换表

Online一定是inplace操作，inplace和copy重建表都要占用临时空间，不是原地操作

表计数count

innodb由于支持事务，不能存储表行数的值

isam虽然返回count(*)很快直接返回表行数值，然是不支持事务

redis不支持分布式事务，无法拿到一致的试图

innodb将计数直接放到计数表中，通过事务进行计数，保证一致性

注：应该先插入数据，再更新记录表，因为更新记录表涉及行锁的竞争，放后面快速释放锁

count(*)由于肯定不为null，进行了优化，按行累加

count(1)：不取值，判断是否为null，不为null累加

count(主键)：取出id，判断是否为null，不为null累加

count(字段)：读出字段，判断是否为null，不为null累加

order_by

有全字段排序 和 rowid排序两种方式

只有字段过大，内存不够的时候才会选择用rowid排序

因为rowid排序多一次回表操作，如当name字段过大，排序age的时候，先找出age和ID，然后排序age，最后通过ID回表找出ID+name+age返回

最好建立联合索引 + 覆盖索引，这样找name的时候，取出数据直接返回

逻辑相同，性能差距巨大的语句

1、对索引做函数计算

如果对索引做函数计算，那么mysql不会走索引，而只使用全表扫描

因为对索引做函数计算会破环索引的有序性

如 id + 1= 100，替换成 id = 100 - 1就可以，将函数计算加在参数上

2、隐式类型转换

如果索引 和 对应的值类型不匹配，走类型转换相当于做了函数计算，会进行全表扫描

3、隐式字符编码转换

如果两张表的字符串的编码方式不同，走联合索引就会隐式转换，走全表扫描

应该将函数计算加在参数上

只查一行的语句，执行很慢

1、等MDL锁

2、等行锁，另一个事务写操作但没提交

3、查询慢

如一个事务更新了1w次，有1w个回滚日志，然后提交；因为可重复读，查询的事务查询时向前回滚1w次找到原始数据

可以用当前读解决

MYSQL如何保证主备一致

通过将备库设置为readonly，只有同步更新线程有超级权限让备库写入主库传的binlog，保持一致

主要有两种结构，M - S 和 双M

M - S：A是B的备库或B是A的备库

双M：A和B互为主库和备库

binlog有三种格式：

1、statment :存储的是原SQL命令，但是当主备库用的索引不一致时，如命令带有limit，容易不安全

2、row：存储的是要执行命令的记录，和相应命令，直接在备库中找到记录并执行命令，安全

3、mixed：如果MYSQL判断可能会引起主备库不一致就用row，不会的话就用statment

row的缺点：占用空间大，优点：恢复数据快，如误删直接还原数据

---

因为AB互为主备库，如A给B传binlog，B再给A传binlog

解决：规定两个库的id不一样，然后备库接收到binlog时，要生成与原binlog相同id的binlog

这样在B传给Abinlog时，A判断binlog的id是自己的，就会丢弃binlog，解决循环复制问题

主备延迟和主备切换

主备延迟：A完成事务T1，传给备库T2，备库执行完事务T3，一般T3容易延迟

引起原因：

1、备库机器的性能比主库差，解决：做对称部署

2、人为让备库提供读能力，导致备库的压力大

3、大事务，由于主库提交大事务才会写入binlog，传给备库，因此备库执行大事务会延迟

所以不要一次性delete语句删除太多数据，应该分批次删除

4、对大表做DDL，也是大事务

主备切换

由于主备延迟问题，因此有不同的主备切换策略

1、可靠性优先策略

先判断备库的延迟小于某个值时，将主库改为只读状态，再判断备库延迟直到为0（等待主备数据同步），将从库改为可写，然后将业务切换到备库

如果不判断主备延迟的话，当执行主备数据同步时，此时当主库改为只读，会导致系统不可用，只能读

2、可用性优先策略

不等待主备数据同步，先将备库改为可写，并将业务切换到备库，系统就没有不可用时间

但容易导致主备库数据不一致问题，如A刚执行完插入语句，就切换主备库，导致A的数据没传给B

此时B又执行插入语句，如果是主键自增的，导致两边数据不一致，如A(4,4,4)，B(4,5,5)

解决：将binlog格式设置为row，当切换完主备库且两边数据不一致时，主备同步的线程会报错

因为row格式的binlog直接记录数据，然后相互传的时候会判断数据