大体来说，MySQL 可以分为 Server 层和存储引擎层两部分
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎
查询语句执行流程
- 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接
- 查询缓存：之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，使用SQL_CACHE 显示指定使用查询缓存，mysql8.0已经官方删掉了查询缓存功能
select SQL_CACHE * from T where ID=10； - 分析器：先做词法分析然后语法分析
- 优化器：在表里边有多个索引的时候决定用哪个索引，或者在进行多表关联的时候，决定各个表的连接顺序
- 执行器：执行sql语句，优先判断对要查询的表有没有执行查询的权限
redo log(重做日志) InnoDB引擎层特有的日志
binlog(归档日志) server层自己的日志
redolog 与binlog区别
- redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
- redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”
- redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志
WAL技术（Write-Ahead-Logging）关键点是先写日志后写磁盘
具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DYz7pifD-1617249263086)(/Users/sunqi/Library/Application Support/typora-user-images/image-20210306111422575.png)]
write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件
write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。
更新语句执行流程
大体流程与查询类似
update T set c=c+1 where ID=2;
- 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
- 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
- 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
- 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
- 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
两阶段提交，为了解决redo log和binlog一致性问题，防止写完其中一个redo log写完后mysql崩溃然后binlog和redolog数据不一致
innode_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘
sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘
事务隔离
事务是什么？
事务首先是一系列操作组成的工作单元，该工作单元内的操作是不可分割的，即要么所有操作都做，要么所有操作都不做，这就是事务
事务ACID
- atomicity 原子性
- consistency 一致性
- isolation 隔离性
- durability 持久性
SQL标准事务隔离级别
- 读未提交 read uncommitted：一个事务还没提交时，它做的变更能被别的事务看到
- 读提交 read committed：一个事务提交后，它做的变更才会被其他事务看到
- 可重复读 repeatable read：一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据一致的，未提交变更对其他事务是不可见的
- 串行化 serializable：对于同一行记录，写会加写锁，读会加读锁，当出现读写锁冲突的时候，后边的事务必须等待前一个事务执行完成，才能继续执行
多个事务同时执行的时候可能会出现
- 脏读 dirty read：事务A正在访问一个数据，并对数据进行了修改，但是还没有提交到数据库，然后事务B也使用了这个数据，相当于事务B读到的数据不对（读未提交级别）
- 不可重复读 non-repeatable read：事务A正在访问一个数据，事务B访问并修改了这个数据，造成事务A读到的数据前后不一致（读未提交，读提交）
- 幻读 phantom read：事务A修改了全表的数据，事务B插入了一行新数据，事务A发现还没有修改的数据（读未提交，读提交，可重复读）
事务和视图的关系
- 读提交 每次sql执行前都会获取记录的新值
- 读未提交 不需要视图，每次使用最新记录的值
- 可重复度 视图在事务执行前生成
show variables like 'transaction_isolation'查看事务隔离级别
MVCC多版本并发控制
事务隔离的实现：每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。
索引
索引的出现就是为了提高数据查询的效率
索引常见模型
- 哈希表：只适用于等值查询的场景，范围查询不可使用
- 有序数组：支持范围查询和等值查询，但是修改数据较慢，因此有序数组索引只适用于静态存储引擎，存储不会修改的数据
- 搜索树：二叉搜索树，父节点左子树所有节点的值都小于父节点，右子树所有节点的值都大于父节点。实际数据库中用于索引的树多是N叉树，“N 叉”树中的“N”取决于数据块的大小
InnoDB索引模型
InnoDB使用了B+树索引模型，所有索引数据都是存放在B+树中
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ug81Af4g-1617249263088)(/Users/sunqi/Library/Application Support/typora-user-images/image-20210306173143591.png)]
根据叶子节点的内容，索引类型分为主键索引和非主键索引
- 主键索引的叶子节点存储的是整行数据。在InnoDB中，主键索引也被称为聚簇索引
- 非主键索引的叶子节点内容是主键的值，在InnoDB中，非主键索引也被称为二级索引
主键索引查询和非主键索引查询的区别
- 主键索引查询。根据ID就可以查找到记录
- 非主键索引查询，先找到主键ID，然后根据主键索引查到记录，这个过程也称为回表
覆盖索引
在查询里边，索引已经覆盖了查询需要的所有字段，就称为覆盖索引
最左前缀原则
不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
索引下推 index condition pushdown
我们说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。这时，你可能要问，那些不符合最左前缀的部分，会怎么样呢？
MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
如果你要重建索引 k，你的两个 SQL 语句可以这么写：
alter table T drop index k;
alter table T add index(k);
如果你要重建主键索引，也可以这么写：
alter table T drop primary key;
alter table T add primary key(id);
对于上面这两个重建索引的作法，说出你的理解
重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。
uname string字段有索引
like ‘j’ 或 ‘j%’ 可以使用索引，并且快速定位记录， like ‘%j’ 或 ‘%j%’，只是在二级索引树上遍历查找记录，并不能快速定位（扫描了整棵索引树）
InnoDB会把主键字段放到索引定义字段后面
所以，当主键是(a,b)的时候，
定义为c的索引，实际上是（c,a,b);
定义为(c,a)的索引，实际上是(c,a,b)
你看着加是相同的
ps 定义为(c,b）的索引，实际上是（c,b,a)
全局锁和表锁
数据库锁设计的初衷是处理并发问题，做为多用户共享的资源，当出现并发访问的时候，数据库需要合理的控制资源的访问规则，而锁就是用来实现这些访问规则的重要数据结构
- 全局锁
- 表锁
- 行锁
全局锁
对整个数据库实例加锁，MySQL提供了一个全局读锁（Flush Tables with read lock）可以让整个库处于只读的状态，会阻塞一下语句
- 数据更新语句
- 数据定义语句
- 更新类事务的提交语句
典型使用场景：全库逻辑备份，就是把整个库的每个表都select出来存成文件
mysqldump：在可重复读隔离级别下开启一个事务，使用参数 --single-transaction 导数据之前会启动一个事务，确保拿到一致性视图
single-transaction 方法只适用于所有的表使用事务引擎的库
全局读锁与global readonly
- 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大
- 二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高
表级锁
- 表锁
- 元数据锁（meta data lock）
表锁
语法：
- lock tables … read/write
- unlock tables …
可以自动关闭也可以客户端断开连接后主动释放
在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表
元数据锁MDL（meta data lock）
当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
- 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查
- 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行
加读锁则所有线程可正常读元数据，不影响增删改查操作，只是不能修改表结构
加写锁则只有拥有锁的线程可以读写元数据，也就是修改表结构，其它线程不能执行任何操作，包括修改表结构与增删改查
MDL会在事务提交之后才会释放
行锁
MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行
行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新
两阶段锁协议在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就乐刻释放，而是要等到事务结束时才释放。
如果事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的锁尽量往后放
死锁和死锁检测
死锁：并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待状态
出现死锁以后两种策略：
- 等待，知道超时，超时时间可以通过参数 innodb_lock_wait_timeout设置
- 发起死锁检测，发现死锁后，主动回滚链条中的某一个事务，让其他事务可以继续运行。将参数 innodb_deadlock_detect设置为on，表示开启死锁检测
begin/start transaction 命令并不是一个事务的起点，在执行到他们之后的第一个操作InnoDB表的语句，事务才真正启动，一致性视图在执行第一个快照读语句时创建
通过 start transaction with consistent snapshot马上启动一个事务，一致性视图在执行时创建
mysql 视图概念
- view。一个用查询语句定义的虚拟表，在调用的时候执行查询语句并成生成结果
- InnoDB在实现MVVC时用到的一致性读视图，即consistent read view，用于支持RC（ReadCommitted 读提交）和RR（Repeated Read 可重复读）隔离级别的实现
InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。
数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。
事务可重复度的能力怎么实现的？
可重复读的核心就是一致性读（consistent read），事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就进入锁等待
读提交的逻辑和可重复读的逻辑类似
- 可重复度隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都公用这个一致性视图
- 读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图
InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。
- 对于可重复读，查询只承认在事务启动前就已经提交完成的数据
- 对于读提交，查询只承认在语句启动前就已经提交完成的数据
当前读，总是读取已经提交完成的最新版本
普通索引与唯一索引如何选择
- 对于普通索引，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到一个不满足条件的记录
- 对于唯一索引，由于索引定义了唯一性，查到第一个记录后，就会停止继续检索
查询时对性能的影响微乎其微，因为每次引入内存一页的数据，下一条记录大概率在内存中了
change buffer
当需要更新一个数据页时，如果数据页在内存中就直接更新，如果数据页不在内存中的话，在不影响数据一致性的前提下，innodb 会将这些更新操作缓存在change buffer，这样就不需要从磁盘中读入这个数据页了。下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作
将change buffer 中的操作应用到原始数据页，得到最新结果的过程称为merge。
- 正常访问数据页会触发merge
- 后台线程会定期merge
- 数据库正常关闭也会merge
唯一索引更新已经将数据页读入到内存了，直接更新数据页相比更新change buffer 更快，就没必要使用了，因此唯一索引不会使用change buffer
普通索引可以使用change buffer
插入一个记录
- 记录要更新的目标页在内存中
- 对于唯一索引来说，找到 索引之间的位置，判断到没有冲突，插入这个值，语句执行结束
- 对于普通索引来说，找到索引插入这个值，语句执行结束
- 记录要更新的目标页不在内存中
- 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束
- 对于普通索引来说，则是将更新记录在 change buffer，语句执行结束
将数据从磁盘读入内存设计随机io访问，是数据库成本最高的操作。change buffer 减少了随机磁盘访问，提升更新性能
- 写多读少的场景更适用于使用change buffer
redo log主要节省的是随机写磁盘的io消耗change buffer主要节省的是随机读磁盘的io消耗
给字符串添加索引
前缀索引
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本
前缀索引字段无法使用覆盖索引， 每次都需要回表查询
区分度无法完全区分情况，使用其他方式建立前缀索引
-
倒序存储
mysql> select field_list from t where id_card = reverse('input_id_card_string'); -
使用hash字段
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'索引长度变小
相同点不支持范围查询
- 空间消耗差不多
- reverse函数消耗cpu资源会小写
- hash查询方式更稳定
脏页： 内存数据页跟磁盘数据页内容不一致的时候，这个内存页为脏页
InnoDB 缓存池 buffer pool中，内存页三个状态
- 还没有使用
- 脏页
- 干净页
要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用
引发数据库刷脏页的场景
- 正常关闭数据库
- 数据库后台线程自动刷脏页
- redo log写满
- buffer pool 内存写满
flush： 刷脏页
purge：清undo log
merge：应用change buffer
数据库表空间的回收
一个InnoDB表包含两部分
- 表结构定义
- 数据
innodb_file_per_table
- off：表的数据放在系统共享表空间，也就是数据字典放在一起
- on：表数据存储在一个以
.idb为后缀的文件中
建议设置为on：一个文件更容易管理，并且drop table 命令，系统就会直接删除这个文件。如果是放在共享表空间中，即使表删了，空间也是不会回收的
数据记录复用
- 删除一条记录后，该记录就会被标记为删除，以后有数据插入且满足索引条件时，可以复用删除记录的空间
数据页复用：
- 一个数据页的数据都被删除了，该页可以被复用到任何位置
- 相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另一个数据页就被标记为可复用
- delete 命令把整个表的数据都删除时，所有数据页都会被标记为可复用，但在磁盘上文件不会变小
如果数据是按照索引顺序插入的，索引是紧凑的
数据是随机插入的，就可能造成索引数据页分裂
更新索引上的值也是会造成数据页分裂
重建表
- 使用结构相同的表，主键自增顺序重新排列
- alter table A engine=InnoDB
online DDL 在线 重建表流程
-
建立一个临时文件，扫描A主键的所有数据页
-
用数据页中表A的记录生成B+树，存储在临时文件中
-
生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态
-
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态
-
用临时文件替换表 A 的数据文件
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ISaKZYc6-1617249263090)(/Users/sunqi/Library/Application Support/typora-user-images/image-20210310182404542.png)]
DDL之前要拿MDL写锁，在alter语句启动前会拿MDL写锁，但是拷贝前会退化成MDL读锁，以便其他人可以对该表进行增删改。同时也可以保护自己，禁止其他线程队这个表同事做DDL
online和inplace
待查看
- alter table：重建表
- analyze table：重新统计表的索引信息
- optimize table： 重建+修改索引信息
COUNT计数
count(*)、count(主键 id)、count(字段) 和 count(1) 等不同用法的性能
-
count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数
-
count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数
- 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加
- 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加
- count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作
count(字段)
- 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加
- 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加
count(字段)<count(主键 id)<count(1)≈count(*)
答疑
两阶段提交
在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象。
- 如果在图中时刻 A 的地方，也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库
- 时刻 B，也就是 binlog 写完，redo log 还没 commit 前发生 crash，
- 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
- 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
- a. 如果是，则提交事务；
- b. 否则，回滚事务。
怎么判断binlog完整
- statement 格式的 binlog，最后会有 COMMIT
- row 格式的 binlog，最后会有一个 XID event
- binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现
redo log 和 binlog 是怎么关联起来的?
它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：
- 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
- 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务
处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?
在时刻 B，也就是 binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。
正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？
实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况
- 如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系
- 在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态
redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？
在一个事务的更新过程中，日志是要写多次的。比如下面这个事务
begin;
insert into t1 ...
insert into t2 ...
commit;
事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里
所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的
order by
执行计划计划 中的Using filesort表示的就是需要排序，mysql会给每个线程分配一块内存用于排序，称为
sortbuffer
全字段排序
要查询的字段和要排序的字段一次都取出来，然后放到sort buffer，按照排序字段进行排序
排序动作可能在内存中完成，也可能需要使用外部排序，取决于排序所需要的内存和参数 sort_buffer_size
如果需要排序的数据量小于参数设置，则排序就在内存中完成，否则内存放不下，则不得不利用磁盘临时文件辅助排序
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6EeU38Cc-1617249263094)(/Users/sunqi/Library/Application Support/typora-user-images/image-20210311150722076.png)]
rowid排序
新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。
排序的结果就因为少了 city 和 age 字段的值，不能直接返回了
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WsHUfFAd-1617249263094)(/Users/sunqi/Library/Application Support/typora-user-images/image-20210311151023662.png)]
对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘，所以mysql会选择使用rowid排序内存表
每个引擎用来唯一标识数据行的信息
- 对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID
- 对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的
- MEMORY 引擎不是索引组织表
order by rand() 使用了内存临时表。内存临时表排序的时候使用了rowid排序方法
磁盘临时表
tmp_table_size 配置限制了内存临时表的大小，默认值是16M，如果临时表大小超过了16M，那么内存临时表就会转为磁盘临时表
磁盘临时表 默认使用的引擎是InnoDB
SQL语句逻辑相同，性能却差异巨大
CREATE TABLE `tradelog` (
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
`operator` int(11) DEFAULT NULL,
`t_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tradeid` (`tradeid`),
KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
条件字段函数操作
如果对字段做了函数计算，就用不上索引了，这是 MySQL 的规定。
select count(*) from tradelog where month(t_modified)=7;
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引 t_modified，优化器对比索引大小后发现，索引 t_modified 更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified
隐式类型转换
select * from tradelog where tradeid=110717;
交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。
隐式字符编码转换
字段字符集不同知识表象原因，主要是连接过程中要求在被驱动表的索引字段加函数操作，直接导致对被驱动表做全表扫描
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
lock in share mode 是当前读，获取字段当前值
CREATE TABLE `table_a` (
`id` int(11) NOT NULL,
`b` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `b` (`b`)
) ENGINE=InnoDB;
有 100 万行数据，其中有 10 万行数据的 b 的值是’1234567890’
select * from table_a where b='1234567890abcd';
-
在传给引擎执行的时候，做了字符截断。因为引擎里面这个行只定义了长度是 10，所以只截了前 10 个字节，就是’1234567890’进去做匹配；
-
这样满足条件的数据有 10 万行；
-
因为是 select *， 所以要做 10 万次回表；
-
但是每次回表以后查出整行，到 server 层一判断，b 的值都不是’1234567890abcd’;
-
返回结果是空。
幻读
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行
在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现
280
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
