SQL的执行
一条查询语句的执行过程
MySQL功能模块执行流程：
总体而言，MySQL分为Server层和存储引擎层。
- server层： 连接器、查询缓存、分析器、优化器、执行器，涵盖MySQL多数核心服务功能以及内置函数（时间、日期、数据、加密函数等），跨域存储引擎的功能也在这一层实现，比如存储过程、触发器、视图等。
- 连接器： 进行TCP握手后，验证身份权限。长连接指连接成功后客户端持续有请求，则一直使用同一个连接(耗费内存，内存占用过大会OutOfMemory，MySQL异常重启)；短连接指完成几次查询后就断开连接，下次再重新建立。
- 查询缓存： 之前执行过的语句及结果可能会以key-value对形式直接缓存在内存中。key查询语句，value是查询结果，若查询命中缓存，那么MySQL不需要执行后面的流程直接返回结果，效率更高。但查询缓存弊大于利，因为缓存失效频繁，更新操作会导致所有查询缓存清空。MySQL8清除了查询缓存的功能。
- 分析器： 解析词法，分析出是查询语句，得到词法分析结果；语法分析再根据语法规则判断是否满足MySQL语法(SQL是否正确)。
- 优化器： 有多个索引时选择使用哪个索引；多表关联时决定表的连接顺序。
- 执行器： 先判断对表是否有查询条件，若没有返回权限错误，有就会打开表继续执行。执行器根据表的引擎定义使用引擎提供的接口执行查询。
- 存储引擎： 负责数据的存储和提取，架构模式是插件式的，支持InnoDB和MyISAM等，默认存储引擎是InnoDB。
- InnoDB： 支持事务，行锁，外键。
- MyISAM： 表锁，不支持事务，崩溃后数据恢复困难，读数据速度快（为什么呢？这是一个值得思考的问题）
一条SQL更新语句是如何执行的？
执行流程和查询是一样的，不过分析器会通过词法和语法解析分析出是一条更新语句。优化器使用ID索引查询到这一行，然后更新。与查询不同的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog （归档日志）。
InnoDB特有的日志：Redo Log
WAL技术：Write-Ahead Logging，先写日志，再写磁盘（提高写操作的性能）。
- 记录更新时，InnoDB引擎会先将记录写到redo log并更新内存，然后再系统比较空闲时将操作记录更新到磁盘里面。
- InnoDB的redo log大小固定，从头开始写，写到末尾就又回到开头循环写。
- write pos 会记录当前的位置，一边写入一边后移，写到3号文件末尾后回到0号文件开头。check point是当前要擦除的位置，向后推移并循环，擦除记录前要把记录更新到数据文件。
redo log 使得InnoDB 即使数据库发生异常重启，之前提交的记录也不会丢失，即crash-safe能力。
重要日志模块： BinLog
与 redo log 的区别：
- binlog 是Server层的归档日志，MySQL的所有引擎都可以使用；redo log是InnoDB引擎特有的。
- binlog是逻辑日志，记录了这个语句的原始逻辑，如“给ID=2这一行的c字段加1”；redo log是物理日志，记录“某个数据页做了什么修改”
- bin log是追加写的，文件达到一定大小会切换下一个；redo log是循环写的，固定空间写完后从头覆盖之前的日志。
InnoDB更新语句的执行流程
执行器 和 InnoDB 引擎在执行简单的update语句时的流程：
update t set c=c+1 where ID=2;
- 执行器从InnoDB引擎中找到ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2所在的数据页本来就在内存中，就直接返回给执行器；否则先从磁盘读入内存，然后返回。
- 执行器将拿到的行数据，将新值赋给指定字段，得到新的一行数据，再调用引擎接口写入这行新数据。
- 引擎将这行新数据更新到内存中，同时将这条更新 记录到redo log里面，此时redo log 处于prepare状态。然后引擎告知执行器执行完成了，随时可以提交任务。
- 执行器生成这个操作的binlog，并把binlog写入磁盘。
- 执行器调用引擎的提交事务接口，引擎把刚写入的redo log改成提交(commit)状态，更新完成。
最后三步，InnoDB引擎操作redo log时拆分了两个步骤：prepare和commit，这就是“两阶段提交”。
两阶段提交
redo log和binlog是两个独立的逻辑，两阶段提交是为了保证日志的一致性。 现在我们从两个方面来分析，先写redo log后写binlog 以及 先写binlog后写redo log 中间发生系统崩溃时的问题：
- 先写redo log 后写binlog：假设情形 写完redo log后，binlog还没写完，MySQL进程异常重启，此时binlog中没有update记录。
MySQL进程恢复后，使用redo log进行了数据恢复，此时执行了redo log中记录的 update 内容。再使用binlog进行数据修复，binlog中没有 update 内容，与原库的值不同。 - 先写binlog 后写redo log：假设情形 写完binlog后，redo log还没写完，MySQL进程异常重启。MySQL恢复后，redo log的 update 事务是无效的，然后binlog恢复数据时进行了事务操作，与原库的值不同。
不使用“两阶段提交”，那么 数据库的状态 有可能 和 用它的日志恢复出来的库 状态不一致(redolog和binlog日志不一致)
事务的隔离性
事务的四大特性：ACID（原子性、一致性、隔离性、持久性）
- 原子性：事务的操作要么全成功，要么全部回滚。
- 一致性：事务执行前后，数据的完整性保持一致。
- 隔离性：并发事务执行过程中，相互之间不会影响。
- 持久性：事务一旦结束，数据就持久到数据库中。
多个事务同时 执行时，可能出现脏读、不可重复读、幻读 的问题，事物的隔离级别可以解决这些问题。
- 读未提交：事务还没提交时，它的变更 就能被其它事务看到；
- 读已提交：一个事务提交之后，它的变更才能被其它事务看到；
- 可重复读：一个事务执行过程中看到的数据，总是和事务开始时的数据是一致的。
- 串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当读写锁冲突时，后访问的事务必须等前一个事务提交后，才能继续执行。
| 事务A | 事务B |
|---|---|
| 启动事务，查询得到值 1 | 启动事务 |
| 查询得到值1 | |
| 将1修改成2 | |
| 查询得到值V1 | |
| 提交事务B | |
| 查询得到值V2 | |
| 提交事务A | |
| 查询得到值V3 |
如上表，在不同的事务隔离级别下，事务A查询到的V1、V2、V3是不同的：
1. 读未提交：V1 = 2，V2 = 2，V3 = 2； 事务A可以 随时看到 事务B修改后 的值。
2. 读已提交：V1 = 1，V2 = 2，V3 = 2； 事务A只能看到 事务B提交后 的值。
3. 可重复读：V1 = 1，V2 = 1，V3 = 2； 事务A提交后，才能看到事务B提交的值。
4. 串行化：V1 = 1，V2 = 1，V3 = 2； 事务B执行“将1修改成2”时，会被锁住无法执行，直到事务A提交，事务B才继续执行。
可以验证：按照表格中的命令顺序，依次执行事务A、B，验证结果是否符合。
/**修改MySQL默认的 事务隔离级别（默认 REPEATABLE-READ可重复读）*/
-- 5.7.?? 版本之前，查看命令
select @@tx_isolation;
-- 5.8 版本的 查看命令(transaction_isolation)
show variables like 'transaction%';
-- 修改 MySQL事务隔离级别的语法。global-全局，session-本次会话。
set [global|session] transaction isolation level [read uncommitted|read committed|repeatable read|serializable];
- 事务A
begin;
-- 第一次查询得到值
select * from t where id = 1;
-- 查询V1
select * from t where id = 1;
-- 查询V2
select * from t where id = 1;
-- 提交事务A
commit;
-- 查询V3
select * from t where id = 1;
- 事务B
begin;
-- 查询
select * from t where id = 1;
-- 修改
update t set name = 2 where id = 1;
-- 提交事务B
commit;
验证 串行化 时对 串行化执行顺序 的理解：事务A、B同时开启事务
- 若事务A、B都只是读操作，那么互不影响。
- 若事务A、B都执行了读操作后，任意一个事务再执行写操作时都会被锁住，必须等另一个事务被提交后才会继续执行。
- 事务A首先执行了写操作，此时事务B的读or写操作都会被锁住无法执行，直到A事务提交。
深入浅出索引
- 索引是为了提高查询的效率。
- 索引是在 存储引擎层实现的，所以并没有U统一的索引标准，不同存储引擎的索引并不相同，索引的底层实现也不相同。
- 这里介绍三种比较常见并且还简单的数据结构：哈希表、有序数组、搜索树：
- 哈希表：插入元素速度快，区间索引的速度非常慢，只适合等值查询的场景。
- 有序数组：查询非常快，但是写入比较慢，只适合静态存储引擎。
- 二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子。按照这个顺序搜索的时间复杂度是 O(log(N))。同时为了维持查询的时间复杂度，需要保持这棵树是平衡二叉树。为了实现这个，写入的时间复杂度也是O(log(N))。
由于数据库不仅存在内存中，还会写到磁盘上，因此数据库存储并不使用二叉树(查询时读磁盘比较慢)。而是使用N叉树，可以减少磁盘读取次数。
InnoDB的索引模型
InnoDB的索引结构是B+树，每一个索引在InnoDB中对应一棵B+树。
InnoDB使用B+树原因：B+树能很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。
- 主键索引/聚簇索引： 主键索引的 叶子节点是 整行数据。主键查询时，只需要搜索 ID 这棵 B+ 树，就可以找到ID对应的整行数据。
- 非主键索引/二级索引/普通索引： 非主键索引的 叶子结点 是 主键ID (主键ID长度越小，叶子节点就越小，普通索引占用空间就越少)，根据非主键查询时，只能查到整行数据的ID，然后再到ID索引树搜索一次查到整行数据。这个过程称为回表。
即非主键索引的查询要多少扫描一棵索引树，因此应尽量使用主键索引。
索引维护
数据写入时，索引树也需要维护，以便维持有序。
- 如果插入的数据比较大，直接在最后一个记录后面插入一个新记录。否则，需要逻辑上挪动后面的数据，空出一个位置存放新记录。
- 如果当前数据页满了，根据B+数算法，需要申请一个新的数据页，然后挪动部分数据到新数据页。这个过程称为页分裂，会影响性能和数据页的利用率。
- 当相邻两个页由于数据删除，利用率很低之后，会将数据页合并，是页分裂的逆过程。（页合并）
由上可见：主键索引最好是有序递增的，并且长度要小。(AUTO_INCREMENT最优于做主键)
索引查询
下面的SQL查询，需要执行几次树的搜索操作？要扫描几行？
/** 建表与初始化 */
create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
/**查询*/
select * from T where k between 3 and 5;
现在来分析这条SQL查询语句的执行流程：
- 先在K索引树上，找到 K=3的记录，取得ID=300；
- 再到ID索引树查到ID=300对应的数据行。
- 在K索引树上取下一个值 K=5，对应ID=500；
- 在ID索引树查到ID=500对应的数据行。
- 在K索引树上取下一个值 K=6，不满足条件，循环结束。
在K索引树上查询了3次，回表了两次。 由于查询的字段只有主键索引上有，因此必须回表。
覆盖索引
一个索引中包含了所有需要查询的字段，就是覆盖索引。即 扫描非主键索引树时无需进行回表。
eg：将上面的SQL查询语句：
select * from T where k between 3 and 5;
更换为 select id from T where k between 3 and 5;
如此，就实现了覆盖索引，直接在K索引树上即可查询到ID字段，无需回表。
联合索引
即复合索引，虽然是多个字段，但仍然是一个索引树。创建复合索引语句：
CREATE INDEX index_name ON table_name (column_list);
通过构造联合索引，可以实现覆盖索引：
当创建(a,b,c)联合索引 index_a_b_c 时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引(本质上是一个索引树)。
此时，通过a字段，查询b、c字段，不需要回表；通过a、b字段，查询c字段，不需要回表。通过a、b、c字段查询ID主键，不需要回表。
最左前缀原则
索引字段顺序会影响查询字段的顺序，InnoDB的B+树结构遵循最左前缀原则。
索引下推优化
联合索引中，会进行索引下推，在联合所引树中，按照最左前缀原则判断筛选多个条件（前提是筛选的字段在联合索引中），满足条件后才进行回表。
全局锁、表级锁、行锁
数据库锁： 并发访问时，合理的控制数据资源的访问。根据加锁范围，MySQL中分为三类：全局锁、表级锁、行锁。
全局锁
加锁后，整个数据库处于只读状态，写操作全部被阻塞。主要应用于 全库的逻辑备份。
表级锁
表级锁有两种：表锁，MDL元数据锁（metadata lock）。引擎不支持行锁时尅使用。
- 表锁：
/** 查看 是否有表锁。 */
show open tables where in_use >=1;
/** 加 读/写 锁
加读锁后，所有的连接都不允许写入，只允许读操作。
加写锁后，本次连接允许读写，其他连接 读操作会阻塞，写操作会报错。 */
lock tables table_name read/write;
/** 解锁 */
unlock tables;
- MDL元数据锁 （MetaData Lock）：作用是 保证读写的正确性。MDL 不需要显式使用，在访问一个表的时候会被自动加上。
MDL特点
- 事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
- 读写锁之间、写锁之间 互斥。如下例所示：
-- Session A
begin; -- 开启事务；
select * from t limit 1;
-- Session B
select * from t limit 1;
-- Session C
alter table t add f int; -- 被阻塞
Session D
select * from t limit 1; -- 被阻塞
- Session A先启动，表t加一个MDL读锁。
- 由于Session B也是MDL读锁，因此可以正常执行；
- Session C的表修改 被阻塞，是因为Session A的MDL读锁还未释放，而Session C需要写锁，因此只能被阻塞。
- 之后的读操作，都需要申请MDL读锁，但此时MDL的读锁被Session C阻塞，因此Session D读操作被阻塞。
如果某个表的查询语句频繁，而且客户端有重试机制，即阻塞超时后会再起一个session请求，那么库的线程很快就会爆满。
行锁：怎样减少对性能的影响？
行锁是引擎实现的，InnoDB支持，MyISAm不支持（只能用表锁）。行锁针对的是数据表中的行记录。
两阶段锁协议
| 事务A | 事务B |
|---|---|
| begin; update t set k=k+1 where id = 1; update t set k=k+2 where id = 2; | |
| begin; update t set k=k+2 where id = 1; | |
| commit; |
事务A执行commit之前，事务B一直被阻塞。
也就是说，在InnoDB事务中，行锁是在需要的时候才加上的；但不需要了不会立刻释放，而是等事务结束时才释放。即 两阶段锁协议。
若事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
例如 顾客A到影院买票，需要如下三个步骤，为了保证原子性，要把三个步骤放在一个事务中。那么如何安排三条语句的执行顺序呢？：
- 顾客A账户余额扣除票价；
- 影院账户余额增加票价；
- 记录一条交易日志。
首先分析，找出最可能造成锁冲突的语句，是第2句(此时若顾客C也来买票，那么影院账户的写就会有冲突的可能)。因此可以按照 3，1，2 的顺序。
死锁和死锁检测
并发系统中不同线程出现循环资源依赖，涉及的线程进入无限等待其它线程释放资源的状态，成为死锁。
MySQL死锁示例：
| 事务A | 事务B |
|---|---|
| begin; update t set k=k+1 where id=1; | begin; |
| update t set k=k+1 where id=2; | |
| update t set k=k+1 where id=2; | |
| update t set k=k+1 where id=1; |
- 事务A获取到id=1的行锁；事务B获取到id=2的行锁；
- 事务A等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。
- 进入死锁状态。需要分析怎样可以解决死锁的问题：
- 直接进入等待，直到超时。超时时间可以设置 innodb_lock_wait_timeout(InnoDB默认50s) 。
- 发起死锁检测，发现死锁后，主动回滚死锁链条中的某个事务，让其他事务继续执行。将参数 innodb_deadlock_detect 设置为 on(默认 on 开启)，表示开启这个逻辑。（推荐使用）但是死锁检测要耗费大量CPU资源。
（如果能确保业务一定不会出现死锁，可以临时把死锁检测关掉。但是存在风险：关闭死锁检测可能会出现大量超时，有损业务）
（控制并发。修改MySQL源码，对于相同行的更新，在进入执行引擎之前，进行排队，就可以避免InnoDB内部的大量死锁检测工作）
行锁下的事务隔离
可重复读隔离级别中，事务T启动后会创建一个视图read-view，之后事务T执行期间，即使其他事务修改了数据，事务T的数据仍然和启动时的一样。好像与世无争，不受影响。
而上一个章节中的行锁，一个数据要更新一行，若此时另一事务持有行锁，那么当前事务会进入等待。问题是，等待状态的事务，获取到行锁后，它读到的值是多少？我们通过例子来说明：
CREATE TABLE `t` (`id` int(11) NOT NULL,`k` int(11) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);
事务A、B、C执行流程：
| 事务A | 事务B | 事务C |
|---|---|---|
| start transaction with consistent snapshot; | ||
| start transaction with consistent snapshot; | ||
| update t set k=k+1 where id=1; | ||
| update t set k=k+1 where id=1; select k from t where id=1; | ||
| select k from t where id=1; commit; | ||
| commit; |
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。
第一种启动方式，一致性视图在执行第一个快照读语句时创建；
第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建。
事务A查到的K=1；事务B查到的K=3。 要知道原因，就要先知道快照在MVCC中是怎么工作的。
事务C没有显示的使用begin/commit，表示这个update语句本身就是一个事务，语句完成时会自动提交。事务B更新行之后查询；事务A在一个只读事务中查询，并且时间顺序是在事务B查询之后。
在MySQL中有两个“视图”概念:
- 用于查询语句定义的续表 view，调用时执行查询语句并生成结果。通过 create view创建，查询方法和表一样。
- InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed 读提交）和RR（Repeatable Read 可重复读）隔离级别的实现。
“快照”在MVCC里是怎么工作的？
MVCC：多版本并发控制，提高InnoDB线程的并发度。
一般的 select * from … where … 语句都是快照读。
当前读（ 搜索时 会 加锁 ）：
select * from … where … for update
select * from … where … lock in share mode
update … set … where…
delete from … where …
在可重复读隔离级别下，事务启动时会 基于整个库 “拍个快照”。
- InnoDB中每个事务都有唯一的事务ID，即 transaction id 。是事务开始时向InnoDB的事务系统申请的，按照申请顺序严格递增。
- 每行数据在被更新时，都会生成一个新的数据版本，并把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时旧数据版本保留，并且在新数据版本中可以获取到。
即 每行数据都有多个版本，每个版本都有row trx_id。
实例,一行数据的4个版本变更情况：
- V4表示版本4，最新版本。
- k=22，是被 transaction id 为25的事务更新的，因此它的row trx_id也是25。
- 语句更新会生成undo log（回滚日志），三个虚线箭头表示。如果要回滚到V2，那么从V4一次执行U3、U2即可。
按照可重复读的定义，事务启动时能看到所有已提交的事务结果，但是事务执行期间，其它事务的更新是不可见的。具体实现是：
- InnoDB为每个事务构造了一个数组，保存事务启动瞬间，当前“活跃”（启动但未提交）的所有事务ID。
- 将数组中 事务ID最小值记为低水位（已经提交的事务），将当前系统中已经创建过的事务ID的最大值再加1，记为高水位（当前事务开始时，尚未被创建的事务，即未来的事务）。此时视图数组和高水位就组成了当前事务的一致性视图（read-view）。
对于当前事务启动的瞬间，一个数据版本的row trx_id有以下几种情况：
- 在 已提交事务 中，表示事务已经提交，或者是当前事务自己生成的，可见。
- 在 未开始事务 中，表示这个版本是将来启动的事务生成的，不可见。
- 在 未提交的事务集合 中：
- 若 row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；
- 若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。
- 综上，若事务的低水位是18(row rtx_id=18)，那么当前事务访问一行数据时，该行数据的 row trx_id 小于18的最大的一条，会被访问到。
而“快照”只记录数据行的当前版本，从而InnoDB 利用“所有数据都有多个版本”的特性，实现了“秒级创建快照”的能力。
了解了快照，再分析一下为什么 事务A查询到的K=1？
- 设事务ABC开始前，系统中存在一个活跃事务，ID为99；
- 事务A、B、C的事务ID分别为100,101,102。三个事务开始前，(1,1)行数据的 row trx_id =90。
- 如此，事务A的视图数组是[99,100]，事务B的视图数组是[99,100,101]，事务A的视图数组是[99,100,101,102]，
- 首选事务C修改成k=2，此时行数据的版本id变更为事务C的ID 102.
- 事务B修改成k=3，此时行数据的版本id变更为 101，102成历史版本。
- 事务A查询时，事务B还没提交，但是事务B生成的 行数据版本 对事务A不可见。此时事务A [99,100] 读取数据的流程是这样的：
- 找到(1,3)，row trx_id=101，比高水位大，不可见；
- 找上一个版本，row trx_id=102，比高水位大，不可见；
- 再上一个版本，row trx_id=90，比低水位小，可见，k=1。
更新逻辑，分析事务B为什么能查到K=3？
事务B的update语句，如果按照一致性读，读到的应该是K=1（事务C修改后row trx_id=102，高于事务B的高水位，对事务B不可见）。那为什么事务B更新后能查到K=3呢？
当事务B更新之前查询一次数据，返回K=1，但是在更新时，是不会在历史版本上更新的，否则事务C的更新就会丢失。因此事务B的set k=k+1 是在(1,2)基础上操作的。这是一条原则：更新数据都是先读后写的，读只能读当前的值，称为当前读（current read）
因此事务B在更新时，当前读拿到的数据是(1,2)，更新后生成了新的版本的数据(1,3)，新版本的row trx_id是101。后面select操作发现 (1,3)的版本在事务B的可见范围内，因此 查到K=3。
事务的可重复读是怎么实现的？
可重复读的核心就是 一致性读。事务更新数据时，只能使用当前读，如果当前读的记录行锁被其他事务占用时，那么更新操作的当前读就进入锁等待。
已提交读的逻辑和可重复读类似，主要有以下区别：
- 可重复读 在事务开始时创建一致性视图，之后事务中的其他查询都共用这个一致性视图。
- 已提交读 每个语句执行前都会重新算出一个新的视图。
对于可重复读，查询只承认 事务 启动前就已提交完成的事务；
对于已提交读，查询只承认在 语句启动前 就已经提交完成的事务。
而当前读，总是能够读取行数据最新的版本。
MySQL实践
普通索引与唯一索引，应该怎么选择？
MySQL索引分类介绍：
- 主键索引/聚簇索引：不能为null，可以做外键，一张表只有一个。
- 非主键索引/普通索引：基本索引，没有限制。
- 唯一索引：索引列的值必须唯一。
- 全文索引：FULLTEXT索引，只有InnoDB和MyISAM支持。
普通索引与唯一索引的读写区别
查询过程：
- 普通索引 查询到满足条件的第一个记录后，需要继续查找下一个记录，直到出现第一个不满足条件的记录。
- 唯一索引 查找到第一个满足条件的记录后，就停止继续检索。
查询过程的性能差距是 微乎其微 的。InnoDB的数据是按照数据页为单位从磁盘读到内存中，每个数据页默认16KB。但找到满足条件的记录时，记录所在的数据页就都在内存中了，对普通索引而言，查找和判断下一条记录，只需要一次指针寻找和一次计算。
更新过程： change buffer机制介绍：
change buffer： 更新数据页时，若数据页在内存中就直接更新，否则InnoDB会将更新操作缓存到 change buffer 中，而不需要从磁盘将数据页读入到内存中。直到下次查询需要访问这条数据页时，才将数据页读入内存，然后执行change buffer中缓存的更新操作（merge）。 从而保证数据逻辑的正确性。（Change Buffer在内存中有拷贝，也会被写入到磁盘上，即它是可持久化的）
将change buffer中的操作 应用到原数据页，得到新结果的过程 成为 merge。除了访问数据页会触发merge外，系统有后台线程会定期merge，在数据库正常关闭(shutdown)过程中也会执行merge操作。
merge流程：
- 从磁盘读入数据页到内存中；
- 将change buffer中的记录 更新 相关数据页的行记录；
- 写 redo log。这个redo log包含了数据的变更与change buffer的变更。merge结束。
- 此时 磁盘中的数据页还没有修改，属于脏页，之后会刷回自己的物理数据。
change buffer使用的是 buffer pool 里的内存，大小有限，可以通过参数 innodb_change_buffer_max_size 动态设置。若设置为50，表示change buffer最多占用 buffer pool 的50%。
向表中插入一条记录，InnoDB的处理流程：
- 这条记录所在的数据页在内存中：
- 唯一索引，找到插入的位置，判断到没有冲突，插入值，语句执行结束。
- 普通索引，找到插入的位置，插入这个值，语句执行结束。
- 这条记录所在的数据页不在内存中：
- 唯一索引，将数据页读入内存，判断到没有冲突，插入值并执行结束。
- 普通索引，将更新记录到change buffer，执行结束。
change buffer 使用场景：
- 唯一索引中，所有的更新操作都要先判断是否会违反唯一性约束，通过将数据页读入内存再判断。既然已经将数据页读入到了内存，那么直接更新内存会更快，就不必使用 change buffer了。
- 普通索引中，一个数据页在merge操作之前，change buffer 记录的变更越多，收益就越大。因此 对于写多读少的业务 来说使用效果最好。但是对于写完后立即读取的操作，刚记录到 change buffer中就要出发merge过程，那么change buffer反而影响性能。
索引的选择和实践
普通索引与唯一索引在查询能力上没有差别，主要考虑更新性能的影响，尽量选择 普通索引。
如果所有更新操作后，马上伴随着查询，那么建议关闭 change buffer。其他场景下 change buffer都能提升性能。
redo log 与 change buffer
执行插入或更新操作时：
- 若数据页在内存中，则直接更新内存；
- 否则 先在内存的 change buffer 中记录 操作信息（将新行更新到内存中）。
- 完成后再将操作写入 rodo log，并处于prepare阶段。
之后，再执行读取操作时：
- 若数据页在内存中，则直接从内存中返回；
- 否则，先将数据页从磁盘读入到内存中，然后将 change buffer 的记录 merge 到 数据页中，生成正确的版本后再返回结果。
MySQL为什么有时候会选错索引？
MySQL一张表支持多个索引，在查询时，并不会主动指定使用哪个索引，这个是由MySQL决定的。但MySQL有时会选错索引。
人工选择索引：
- force index——缺点：需要修改SQL（如后期修改了索引），不够敏捷。
select * from t force index(index_a) where a between 10000 and 20000 ;
- 修改语句，引导MySQL使用我们期望的索引。
- 新建一个 更加合适的索引，供优化器选择，或删掉被误用的索引。
怎么给字符串字段加索引？
前缀索引与全字符串索引
例如 邮箱字段，很多系统支持邮箱登录，因此邮箱字段需要建立合适的索引。有两种创建方式：
-- 索引中 包含了 Email整个字符串。 index1
alter table SUser add index index1(email);
-- 【前缀索引】索引中 包含了 Email前6个字节。索引占用空间更小。 index2
alter table SUser add index index2(email(6));
- 使用index1，查询执行的顺序：
- 从index1 索引树找到满足条件的记录，取得ID主键；
- 到主键上查到主键ID对应的行，判断email的值是正确的，将这行记录加入结果集中；
- 取index1 索引树上刚刚查到的位置的下一跳记录，不满足条件，循环结束。
整个过程只需 回主键索引取一次数据，系统认为至扫描了一行。
- 使用index2 前缀索引，查询执行顺序：
- 从index2 索引树上找到满足索引值的 记录，找到的第一个是 ID1；
- 到主键索引上找到ID1所在的行，判断email值是否满足条件，若不满足则丢弃（可能只满足了前n个字节，不满足全部字节）；
- 取 index2索引树的下一条记录，仍然满足，取出ID2，再到主键索引上取出整行进行判断，若匹配正确，则加入结果集。
- 取 Index2索引树的下一条记录，不满足前缀匹配，循环结束。
若index2 的前缀索引选择合适长度，避免掉相同的字符串，那么查询成本会再次降低，甚至等同于 放入整个字符串的索引。
综上，前缀索引 如果定义好长度，就可以做到既节省空间，又可以减少查询成本。（使用了前缀索引后，必定回表判断，会导致覆盖索引失效。）
其他索引方式——虚拟列
- 比如身份证号，同一个地市区的前几位一般是相同的。要使用前缀索引，可以在存储数据时 将身份证号码 倒序存储。查询时可以这么写（reverse取反）：
select * from table where id_card = reverse('110101199003071073');
- 使用Hash字段。添加创建一个新字段，存放 身份证字段 生成的整数校验码（crc32(‘110101199003071073’)函数）。由于校验码可能存在冲突，因此需要先查询 校验值，在查询身份证号码，以确保一致
select * from t where id_card_crc=crc32('110101199003071073') and id_card='110101199003071073';
区别：
- 存储空间消耗方面：hash校验方式需要增加一个字段，会消耗额外存储空间；倒序存储若前缀长度过长，那么消耗的额外存储空间差不多与hash校验方式一样。
- CPU消耗方面：倒序方式需要额外调用reverse函数，而hash字段方式需要额外调用一次crc32()函数。就两个函数计算复杂度而言，reverse消耗较小。
- 查询效率来看，使用hash字段方式查询性能更加稳定。crc32()虽然有冲突的可能，但是概率非常小。而倒序存储方式使用了前缀索引，会增加扫描行数。
字符串创建索引总结
- 直接创建完整索引，可能比较占用空间；
- 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
- 倒序索引，再创建前缀索引。用于绕过字符串本身前缀的区分度不够问题，不支持区间查询。
- 创建Hash字段索引，查询性能稳定，有额外的存储和计算消耗，。跟第三种方式一样，不支持区间查询。
为什么我的MySQL会“抖”一下？
一条SQL正常执行时特别快，但有时会变得特别慢，并且很难复现。就像抖了一下。
WAL机制（write ahead log）即：更新时，先更新完内存，后写redo log，此时内存与磁盘数据页内容不一致，内存页成为“脏页”。之后将内存中的数据页 flush 到磁盘中，此时数据页一致，内存也成为“干净页”。
当 内存数据页 跟 磁盘数据页 内容不一致的时候，我们称这个内存页为“脏页”。
内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
平时执行很快的更新操作，其实就是写日志和内存；而MySQL偶尔“抖”一下的瞬间，可能就是在刷脏页（flush）。
什么时候会触发刷脏页的事件呢？
- InnoDB的redo log写满了。此时会停止所有更新，将checkpoint向前推进，redo log留出空间可以继续写。
- 系统内存不足，需要更新内存页，就需要先将脏页更新到磁盘中。
- 系统空闲时，刷脏页。
- MySQL正常关闭时，会将内存中的脏页都flush到磁盘上。
刷脏页对性能的影响
其中 第三种情况是属于MySQL空闲时的操作，这时系统没什么压力；第四种情况是MySQL数据库要关闭了，这两种情况都不需要关注性能问题。影响性能问题的主要是前两种情况：
- redo log写满时刷脏页：这种情况是InnoDB要尽量避免的，因为此时整个系统就不能再接受更新了，所有更新都必须堵住。
- 系统内存不足时刷脏页：这种情况是常态。系统内存不足，就必须到缓冲池中申请数据页，这时会将最久不使用的数据页从内存中淘汰掉：若淘汰的是干净页，就直接释放复用；否则就先flush到磁盘变成干净页再复用。
综合以上情况，要避免以下两种情况对性能的影响：
- 日志写满，更新全部堵住，性能跌为0.
- 一个查询要淘汰的脏页个数太多，导致查询响应时间变长。
InnoDB刷脏页的控制策略与 innodb_io_capacity
首先，通过设置 innodb_io_capacity 参数，告知InnoDB 主机磁盘的IO能力（innodb_io_capacity 参数会影响刷脏页的速度 ），此时InnoDB才知道需要全力刷新脏页时可以刷多快。(建议设置成磁盘的IOPS每秒读写次数，IOPS可以通过fio工具测试)。
影响刷脏页速度的因素
（首先考虑如果脏页flush比较慢会有什么结果？ 首先是 内存中脏页太多，其次是 redo log写满。）
- 脏页在内存中的比例。 可以通过修改 innodb_io_capacity 参数控制脏页最大刷新能力。 也可以通过 Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total 来获取脏页所占比例。
- redo log写盘的速度。
innodb_flush_neighbors 参数：若参数值为1，刷一个脏页时，若脏页的旁边也是脏页，那么“邻居”数据页也会被刷掉（将影响查询速度）。为0表示 不刷邻居。MySQL8.0中默认是0。
为什么表数据删掉一半，表文件大小不变？
InnoDB表包含两部分：表结构定义 和 数据。表文件主要是表数据。
（MySQL8.0之前 表结构存储在.frm后缀文件；MySQL8.0之后 表结构定义在系统数据表中，因为表结构定义占用的空间很小。）
innodb_file_per_table参数设置ON
表数据 可以存放在共享表空间，也可以是单独的文件。是通过这个参数控制的。
- ON：每个InnoDB表的数据存储在一个以.ibd为后缀的文件中。默认/推荐。
- OFF：表的数据放在系统共享表空间，即与数据字典放在一起。
数据删除流程
delete 删除表记录、整个数据页，整个表，也不能回收表空间。delete命令实际上只是把记录的位置标记为了“可复用”。 而没被复用的空间看起来就像是“空洞”。 （truncate命令清空表数据 实际上是 drop + create）
不止删除数据会造成空洞，插入数据也会：如果数据按照索引递增插入，那么索引是紧凑的；但如果数据随机插入，那么就可能造成索引的数据页分裂，数据页分裂可能会产生 空洞。（更新可以理解为删除旧值，插入新值，所以也会造成空洞）。
如果可以把空洞去掉，就能达到收缩表空间的目的。
重建表
重建表：按照主键ID递增顺序，将表A数据读出来插入到表B临时表，用表B替换表A，从效果上看，就起到了收缩表A空间的作用。
/** MySQL5.5版本之前，执行这个命令可以重建表。
临时表B不需要创建，
MySQL会自动完成转存数据、交换表名、删除旧表的操作。
*/
alter table A engine=InnoDB;
重建表流程：
- 创建一个临时表tmp，并将表A数据插入到临时表中；
- 表名交替，A表给为tmp表，tmp表更名为A表；完成。
但是向临时表插入数据的过程，若有新数据要写入到表A的话，会造成数据丢失。因此整个DDL过程，表A中不能有更新，即这个DDL不是OnLine的。MySQL5.6引入了Online DDL。
Online DDL重建表流程：
- 建立临时表，扫描表A主键的所有数据页；
- 用数据页中表A的记录生成B+树，存储到临时文件tmp_file中；
- 生成临时文件的过程中，将所有对A的操作记录到一个日志文件（row log）中；
- 临时文件生成完毕，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件；
- 用临时文件代替表A的数据文件。
OnLine 和 Inplace
重建表 第一种方法 的临时表 是server层创建的；
Online DDL中临时文件tmp_file是InnoDB内部创建的，对于server层来说没有把数据挪动到临时表，是一个“原地”操作，即 InPlace。
-- 强制拷贝表，对应 重建表的过程
alter table t engine=innodb,ALGORITHM=copy;
-- OnLine DDL 一定是 inplace的
alter table t engine=innodb,ALGORITHM=inplace;
count(*)这么慢，我该怎么办？
不同的MySQL引擎，count(*)实现方式不同：
- MyISAM引擎把表的总行数记录在磁盘上，可以直接返回。但是如果有过滤条件，就不能返回这么快。
- InnoDB引擎需要把数据一行一行从引擎里读出来，然后累积计数。（不支持将表的总行数记录到磁盘上，是因为要支持事务）
MyISAM引擎虽然count(*)快，但是不支持事务；
show table status 虽然返回快，但是不准确；
InnoDB表直接遍历全表，虽然结果准确，但是会导致性能个问题。
用缓存系统保存计数
Redis计数加1；数据表插入一行数据R。
- 方法：用一个Redis缓存，保存表的总行数，表被插入就加1，被删除就-1。
- 优点：读和更新操作都很快。
- 缺点：缓存系统可能丢失更新。（Redis异常重启，即便Redis正常工作，计数逻辑也不精确——表中插入数据后，Redis计数还没有加1，就被程序读取Redis计数。）
数据库保存计数
计数表字段加1，数据表插入一行数据R。
- 方法：把计数直接存放到一张单独的计数表中。
可以利用事务的特性，解决 缓存保存计数 的缺点问题。
不同Count的用法
count()是一个聚合函数，对返回的结果集进行判断，如果值不是NULL，累计值就加1.最后返回累计值。
- count(主键)： InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
- count(1)： InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
- count(字段)：
- 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不可能为 null，按行累加；
- 如果这个“字段”定义允许为 null，那么执行的时候，判断有可能是 null，要把值取出来再判断一下，不是 null 才累加。
- count(*)例外： 不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，建议 尽量使用 count(*) 。
“order by”是怎么工作的？
SQL中有order by时，explain解析出来的 Extra 一般会包含 Using filesort，表示需要排序。这时MySQL会给每个线程分配一块内存用于排序，这块内存成为 sort_buffer。
全字段排序
-- city有索引
select city,name,age from t where city ='杭州' order by name limit 1000;
通常情况下，这个语句执行流程如下：
- 初始化sort_buffer，确定放入name、city、age三个字段；
- 从 city的索引树 找到第一个满足city="杭州"的主键ID；
- 到ID主键索引树取出整行，取name、city、age三个字段的值存入sort_buffer内存中。
- 从city索引树 取下一个满足条件的 主键ID；
- 重复步骤3、4，直到city的值不满足查询条件位置；
- 对sort_buffer 中的数据按照字段name做快速排序；
按照排序结果，取前1000行 返回给客户端。
其中 按照字段name做快速排序，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。
sort_buffer_size 是MySQL为排序开辟的内存sort_buffer的大小。如果排序数据量小于sort_buffer_size，排序就在内存中完成，内存放不下就只能利用磁盘临时文件来辅助排序。
确定一个排序语句是否使用了临时文件：（通过查看 OPTIMIZER_TRACE 的结果来确认的，可以从 number_of_tmp_files 中看到是否使用的临时文件数量）
-- 打开 optimizer_trace，只对本线程有效
SET optimizer_trace='enabled=on';
-- @a 保存 Innodb_rows_read 的初始值
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
-- 执行语句
select city, name,age from t where city='杭州' order by name limit 1000;
-- @b 保存 Innodb_rows_read 的当前值
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
-- 计算 Innodb_rows_read 差值
select @b-@a;
number_of_tmp_files 表示：MySQL将需要排序的数据分成12份，每份单独排序后存储在这些临时文件中，然后把这12个有序文件合并成一个有序的大文件。number_of_tmp_files 为0 就表示排序可以直接在内存中完成。越大就说明sort_buffer_size太小，需要很多临时文件。
rowid排序
全字段排序 算法有一个问题，如果查询要返回的字段很多，那么sort_buffer 中要存放很多字段，会需要更多的空间（内存占用更大或临时文件更多），排序性能会很差。
当MySQL认为排序的单行长度太大的话，就会更换算法(RowID排序)。
-- 单行长度 可以通过参数 max_length_for_sort_data 设置
SET max_length_for_sort_data = 16;
新的算法中，MySQL只会把要排序的列（name字段）和主键ID 两个 字段 放入 sort_buffer 中。这时执行流程就变了：
- 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
- 从city索引树找到第一个满足条件的主键ID；
- ID索引中取出整行，取name、id两个字段放入sort_buffer中；
- 从city索引树取下一个记录的主键ID；
- 重复步骤3、4直到不满足查询条件；
- 对 sort_buffer 中的数据按照字段 name 进行排序；
- 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name、age三个字段返回给客户端。
RowID排序多了一个 访问表t主键索引 的步骤。实际查询中，第七步 是从排序后的sort_buffer中依次取出id，然后到原表查到city、name和age，然后直接返回给客户端，而不是存储到内存中一个结果集后一下子全部返回。
全字段排序 VS RowID排序
如果内存够，就多利用内存，尽量减少磁盘访问。
内存够大，就使用全字段排序，否则就只能使用 rowid排序，rowid排序回表多，会造成磁盘读，因此不会被优先选择。
并不是所有的order by语句都需要排序，其原因是原数据都无序的。
联合索引 优化order by排序
如果能保证从city索引树上取下来的行，直接就是按照 name 递增排序的，那就可以不用再排序了。可以通过联合索引实现。
alter table t add index city_name(city,name);
在 city_name 索引树中，会先按照city排序，city相同则按照name安排，这样 取下来的行 直接就是按照 name排序的了；执行流程为：
- 从 city_name 索引树 找到第一个满足条件的主键ID；
- 到 ID索引树 取出整行，取name、age和city是三个字段的值，作为结果的一部分直接返回；
- 从 city_name 索引树 取下一个记录的主键ID；
- 重复步骤2、3，直到不满足条件，或者够1000条记录，循环结束。
小问题
如果使用了联合索引进行优化，这个SQL语句需要排序么？怎么避免排序呢？
select * from t where city in (“杭州”," 苏州 ") order by name limit 100;
联合索引 对 单个city内部，name是递增的。但是现在从两个city中查询，满足条件的name 就不是递增的，因此需要排序。
怎样避免排序
方案一：
- 执行 select * from t where city=“杭州” order by name limit 100; 这个语句是不需要排序的，客户端用一个长度为 100 的内存数组 A 保存结果。
- 执行 select * from t where city=“苏州” order by name limit 100; 用相同的方法，假设结果被存进了内存数组 B。
- 现在 A 和 B 是两个有序数组，可以用归并排序的思想，得到 name 最小的前 100 值，就是最后的结果了。
这个方案有一个明显的损失，就是从数据库返回给客户端的数据量变大了。所以，如果数据的单行比较大的话，可以考虑只查询 id,name 字段，将 ID 排好序后再根据 ID 查询所有记录。
如何正确地显示随机消息？
从表中随机选出三行，怎样才能性能最优？
-- 建表
CREATE TABLE `words` (`id` int(11) PRIMARY KEY AUTO_INCREMENT,`word` varchar(64) DEFAULT NULL);
-- 调用存储过程，插入数据
-- 更改 结束符号为 两个分号
delimiter ;;
create procedure iwords()
begin
declare i int;
set i=0;
while i<10000 do
insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
set i=i+1;
end while;
end;;
-- 存储过程接触，恢复 结束符号 然后调用存储过程。
delimiter ;
call iwords();
-- 查看 存储过程
show procedure status like 'iwords' \G;
-- 删除 存储过程
drop procedure iwords;
order by rand() 内存临时表
-- 随机查询（order by rand()可以通过随机排序，实现随机查询）
select word from words order by rand() limit 3;
解释这条语句，发现使用了 临时表
mysql> explain select word from words order by rand() limit 3;
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-------------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-------------------------------+
| 1|SIMPLE |words|NULL |ALL |NULL |NULL|NULL |NULL|4614| 100.00|Using temporary; Using filesort|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-------------------------------+
1 row in set, 1 warning (0.07 sec)
对于内存表（memory引擎），回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。因此MySQL在这里使用的是 rowid排序。
执行流程：
- 创建临时表，临时表使用的是memory引擎，表里有两个字段，第一个是double类型，记为字段R，第二个是varchar(64)类型，记为字段W。这个表没哟索引；
- 从words表中，按主键顺序取出所有的words值。对于每个word值，调用rand()函数生成一个大于0小于1的随机小数，并把随机小数和word分别存入临时表的R和W字段中。到此，扫描行数是10000。
- 现在临时表有10000行数据，接下来要在这个没有索引的内存临时表上，按照字段R排序。
- 初始化sort_buffer，sort_buffer中有两个字段，一个double类型，一个整型。
- 从内存临时表中按行读取R值和 “位置信息” ，分别存入sort_buffer中的两个字段。这个过程叫做 对内存临时表做全表扫描 ，此时扫描行数增加10000，一共扫描了20000行了。
- 在sort_buffer中根据R值排序，由于没有涉及表操作因此不会增加扫描行数。
- 排序完成后，取出前三个结果的位置信息，依次到内存临时表取出word值，返回给客户端。此时又扫描了3行。
位置信息：MySQL的表是用 rowid 来定位 一行数据 。
- 有主键的InnoDB表，rowid就是主键ID；
- 没有主键的InnoDB表，rowid是系统生成的；
- MEMORY引擎不是索引组织表。在这里可以认为是一个数组，rowid是数组的下标。
以上执行过程，一共扫描了20003行数据。
order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。
磁盘临时表
内存临时表大小是由 tmp_table_size 限制的，默认16M。如果临时表大小超过了，那么内存临时表会转成 磁盘临时表。
磁盘临时表 默认使用 InnoDB引擎，由参数 internal_tmp_disk_storage_engine 控制。当使用磁盘临时表时，对应的是一个没有显示索引的InnoDB表的排序过程。
随机排序方法
先把问题简化一下，如果只选择1个word值，可以怎么做？
算法1：
- 取得表的主键ID的最大值M和最小值N；
- 用随机函数生成一个 最大值与最小值之间的数 X=( M - N ) * rand() + N;
- 取不小于X的第一个ID的行。
select max(id),min(id) into @M,@N from t;
set @X = floor( (@M - @N +1) * rand() + @N );
select * from t where id >= @X limit 1;
存在问题：ID之间可能有空洞，不连续，概率不均匀。
算法2：
- 取得整个表的行数，记为C；
- 取得Y=floor(C * rand() ).floor函数在这里 取整数部分；
- 再用limit Y,1 取得一行即可。
select count(*) into @C from words;
set @Y = floor(@C * rand());
set @sql = concat("select * from words limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;
存在问题：limit Y,1 要将前Y行记录读出来，然后丢掉，再把下一个记录作为结果返回。第一步扫描C行，第二步扫描Y+1行，执行代价比算法1的代价要高。
但是也比 直接使用 order by rand() 执行代价要小很多。
解决了算法1中 ID空洞不连续的问题（概率不均匀）
现在 按照算法2 的思路，获取三个word值：
- 取得整个表的行数，记为C；
- 根据相同的随机方法得到Y1，Y2，Y3；
- 执行三次 limit Y,1 ，得到三行数据。
select count(*) into @C from words;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from words limit @Y1,1;
select * from words limit @Y2,1;
select * from words limit @Y3,1;
如此 总的扫描行数就是 C + (Y1 + 1) + (Y2 + 1) + (Y3 + 1)。实际上还可以继续优化：取Y1、Y2和Y3中最大的一个数记为M，最小的数记为N，然后执行下面的SQL，共扫描了 C+M+1行。
select * from words limit N,M-N+1;
也可以先获取id值，在应用中确定了ID值后在执行三次主键索引查询也很好。
为什么有些SQL语句逻辑相同，性能却差异巨大？
案例一：条件字段函数操作 导致性能低
-- 建表语句，交易记录表
CREATE TABLE `tradelog` (
`id` int(11) primary key NOT NULL, `tradeid` varchar(32) DEFAULT NULL,
`operator` int(11) DEFAULT NULL, `t_modified` datetime DEFAULT NULL,
KEY `tradeid` (`tradeid`), KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 查询语句
select count(*) from tradelog where month(t_modified)=7;
查询语句中，对索引字段做了函数操作，可能会破坏索引值的有序性，因此优化器会决定放弃树搜索功能，导致全索引扫描。（不是放弃使用索引，而是选择遍历主键索引、或者t_modified索引）。
案例二：隐式类型转换 导致性能低
select * from tradelog where tradeid=110717;
交易编号tradeid字段本来是有索引的，但是explain却显示要全表扫描。因为 tradeid 字段是varchar类型的，而输入参数却是整型，所以需要类型转换。优化器放弃树搜索功能。
但如果是主键ID 使用了varchar类型条件查询，仍然会走主键索引。
案例三：隐式字符编码转换 导致性能低
表格的字符集不同，导致无法使用索引
-- 交易 详情表，使用编码 utf8（交易记录表是 utf8mb4）
CREATE TABLE `trade_detail` (
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
`trade_step` int(11) DEFAULT NULL, /* 操作步骤 */
`step_info` varchar(32) DEFAULT NULL, /* 步骤信息 */
PRIMARY KEY (`id`),
KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 交易 记录
insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());
-- 交易详情
insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');
-- 查询 ID=2 的交易记录和交易详情
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
执行流程：
- 根据ID在tradelog中找到 id=2 这一行；
- 从 tradelog 中取出 tradeid 的字段值；
- 根据 tradeid 值到 trade_detail 中查询条件匹配的行。由下面的explain可知，这个过程是遍历主键索引，一行一行地判断tradeid值是否匹配。
mysql> explain select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | l | NULL | const | PRIMARY,tradeid | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | d | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | Using where |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
- 第一行显示 优化器会先在tradelog表查询，key=primary 表示使用了主键索引，rows=1表示只扫描了1行。
- 第二行 key=null，表示没有用上 trade_detail的tradeid索引，进行了全表扫描。
这里 是从 tradelog表中取出 tradeid字段，再去 trade_detail表中查询匹配字段。因此tradelog表称为***驱动表***，trade_detail称为***被驱动表***。
字符集utf8mb4是utf8的超集，当这两个字符串比较时，MySQL内部操作是 把utf8字符串转成 utf8mb4字符集，再做比较。
此时 查询的优化方法：
- 把trade_detail表的tradeid字段的字符集也改成utf8mb4
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
- 如果数据量大，或者业务暂时不能做这个DDL，可以修改查询SQL。就不会隐式转换编码了，也会使用索引。
select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
mysql> explain select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | l | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | d | NULL | ref | tradeid | tradeid | 99 | const | 4 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 2 warnings (0.00 sec)
总结
- 对索引字段 字段锁函数操作，可能会破坏索引值的有序性，导致优化器放弃使用 树搜索。
- 隐式 类型转换。
- 隐式 字符编码转换。
为什么只查一行的语句，也执行这么慢？
如果 MySQL 数据库本身就有很大的压力，导致数据库服务器 CPU 占用率很高或 ioutil（IO 利用率）很高，这种情况下所有语句的执行都有可能变慢，这个不属于我们今天的讨论范围。
情景模拟
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
-- 查看 存储过程
show procedure status like 'iwords' \G;
-- 删除 存储过程
drop procedure iwords;
查询长时间不返回
select * from t where id=1;
大概率是表t被锁住了。分析原因 一般都是先 执行 show processlist 命令查看语句处于什么状态。再针对每种状态分析产生原因、复现以及处理。
通过show processlist查到线程的ID后，kill id 可以结束。
等MDL锁
场景复现：加写锁
| Session A | Session B |
|---|---|
| lock table t write; | |
| select * from t where id=1; |
(unlock tables; 解锁)
使用 show processlist 命令查看 ，如果 state 状态是 Waiting for table metadata lock ，就表示有线程正在表上请求或者持有MDL写锁，把SQL语句堵住了。
mysql> show processlist;
+----+-----------------+----------------+------+---------+-------+---------------------------------+----------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+----------------+------+---------+-------+---------------------------------+----------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 23273 | Waiting on empty queue | NULL |
| 12 | root | localhost:7369 | test | Query | 0 | starting | show processlist |
| 13 | root | localhost:9115 | test | Query | 7 | Waiting for table metadata lock | select * from t where id=1 |
+----+-----------------+----------------+------+---------+-------+---------------------------------+----------------------------+
3 rows in set (0.00 sec)
处理方法：找到持有MDL写锁的线程，kill掉。
启动MySQL时，设置 performance_schema=on （相比于设置off 会有10%左右的性能损失），通过查询 sys.schema_table_lock_waits 表，可以直接找出阻塞的 process id ，把这个连接用kill命令断开即可。
select blocking_pid from sys.schema_table_lock_waits;
等flush
场景复现：
| Session A | Session B | Session C |
|---|---|---|
| select sleep(1) from t; | ||
| flush tables t; | ||
| select * from t where id=1; |
Session A中 sleep(1) 默认要执行10w秒，期间表t一直被session A“打开着”。Session B的flush tables t命令要关闭t，就要等待Session A查询结束；Session C再次查询，就被flush阻塞。
mysql> show processlist;
+----+-----------------+----------------+------+---------+-------+-------------------------+-----------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+----------------+------+---------+-------+-------------------------+-----------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 27677 | Waiting on empty queue | NULL |
| 12 | root | localhost:7369 | test | Query | 275 | User sleep | select sleep(1) from t |
| 13 | root | localhost:9115 | test | Query | 260 | Waiting for table flush | select * from t where id=1 |
| 17 | root | localhost:1211 | test | Query | 269 | Waiting for table flush | flush tables t |
| 18 | root | localhost:1830 | NULL | Query | 0 | starting | show processlist |
+----+-----------------+----------------+------+---------+-------+-------------------------+-----------------------------+
5 rows in set (0.00 sec)
处理这个阻塞 show processlist; kill id;
mysql> show processlist;
+----+-----------------+----------------+------+---------+-------+-------------------------+-----------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+----------------+------+---------+-------+-------------------------+-----------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 28151 | Waiting on empty queue | NULL |
| 13 | root | localhost:9115 | test | Query | 14 | Waiting for table flush | select * from t where id=1 |
| 17 | root | localhost:1211 | test | Query | 18 | Waiting for table flush | flush tables t |
| 19 | root | localhost:1877 | test | Query | 0 | starting | show processlist |
| 23 | root | localhost:2696 | test | Query | 21 | User sleep | select sleep(1) from t |
+----+-----------------+----------------+------+---------+-------+-------------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> kill 23;
Query OK, 0 rows affected (0.00 sec)
等行锁
复现步骤
| Session A | Session B |
|---|---|
| begin; update t set c=c+1 where id=1; | |
| select * from t where id=1 lock in share mode; |
显然，Session A启动了事务，占有写锁，还不提交，导致 Sessin B被阻塞。
mysql> show processlist;
+----+-----------------+----------------+------+---------+-------+------------------------+-----------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+----------------+------+---------+-------+------------------------+-----------------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 30762 | Waiting on empty queue | NULL |
| 25 | root | localhost:9009 | test | Query | 4 | statistics | select * from t where id=1 lock in share mode |
| 26 | root | localhost:9028 | test | Query | 0 | starting | show processlist |
| 28 | root | localhost:9080 | test | Sleep | 7 | | NULL |
+----+-----------------+----------------+------+---------+-------+------------------------+-----------------------------------------------+
4 rows in set (0.00 sec)
mysql> kill 28;
Query OK, 0 rows affected (0.00 sec)
MySQL5.7版本之前，可以通过sys.innodb_lock_waits表查到。
select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G
执行慢
小结
简单查询出现的 这些 被锁住、执行慢的例子，主要是涉及到了 表锁、行锁和一致性读的概念。实际使用中，场景会更复杂。
要先定位问题，然后才能解决。
幻读是什么，幻读有什么问题？
引入：
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
begin;
select * from t where d=5 for update;
commit;
这条SQL是怎么加锁的？加的锁什么时候释放？
这条语句会命中 d=5 这一行，对应id=5，查询执行完成后，id=5 这一行会加一个写锁 ，由于两阶段锁协议，写锁会在执行 commit 语句后释放。
***由于字段d没有索引，因此这条SQL会做全表扫描，那其他被扫描到的，但是不满足条件的记录，会被加锁么？***（会被加锁。下面通过反例来说明）
什么是幻读？
幻读：一个事务按照一定条件进行查询，期间另一个事务插入了相同搜索条件的新数据，导致当前事务按条件查询时出现了新的数据。
现象：select … for update 是当前读，因此读到了其他事务提交的数据。我们在这里假设一个如下表格的事务场景（事实上result并非如此）：假设 SQL做全表扫描，其他被扫描到的记录没有被加锁
| Session A | Session B | Session C | |
|---|---|---|---|
| T1 | begin; select * from t where d=5 for update;/*Q1*/ result:(5,5,5) | ||
| T2 | update t set d=5 where id=0; | ||
| T3 | select * from t where d=5 for update;/*Q1*/ result:(0,0,5),(5,5,5) | ||
| T4 | insert into t values(1,1,5); | ||
| T5 | select * from t where d=5 for update;/*Q1*/ result:(0,0,5),(1,1,5),(5,5,5) | ||
| T6 | commit; |
Session B的更新操作，使Session A查到了 之前没查到的值，但这个过程不能称为幻读。幻读仅限 插入 操作。
（不可重复读：一个事务按照一定条件查询，期间另一个事务删除了一些数据，导致当前事务查询时数据缺少。 ）
幻读的说明：
- 在可重复读（MySQL默认） 隔离级别下，普通查询即是快照读，看不到其他事务插入的数据。因此“幻读”只有在“当前读”下才会出现。
- 幻读仅专指“新插入的行”，修改数据导致查询到 新的数据，不能称为幻读。
幻读有什么问题？
- 破坏了加锁声明（for update当前读会加行锁）；
- 破坏了数据和日志的一致性。
（多个事务间的操作 由于 当前读 的存在，后提交的事务操作中可能先修改后提交，却可以看到先提交（修改在后，提交在前）的事务插入或修改的数据。但是binlog中记录的日志，只会按顺序记录提交的语句，这就容易导致 数据和binlog日志 不一致。以后数据恢复时，数据就会被修改。）
如何解决幻读——间隙锁
幻读产生原因：行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”，因此要解决幻读问题，InnoDB引入了 间隙锁（Gap Lock）
顾名思义，间隙锁，锁的是两个值之间的间隙。比如本小节开头 表t 的SQL中插入的6个记录，就产生了7个间隙。
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
产生的间隙：
当执行 select * from t where d=5 for update时，不止会给6个记录加上行锁，还会加7个间隙锁，确保无法再插入新的数据。
间歇锁 的唯一冲突：向间隙中插入一条数据。间歇锁之间 不互锁。
| Session A | Session B |
|---|---|
| begin; select * from t where c=7 lock in share mode; | |
| begin; select * from t where c=7 for update; |
这里 session B 并不会被堵住。因为表 t 里并没有 c=7 这个记录，因此 session A 加的是间隙锁 (5,10)。而 session B 也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。
读锁 与 写锁 的兼容关系
| 读锁 | 写锁 | |
|---|---|---|
| 读锁 |
兼容
|
冲突
|
| 写锁 |
冲突
|
冲突
|
间隙锁 与 行锁 合称为 next-key lock，每个next-key lock都是前开后闭区间。如果用select * from t for update 把整个表的所有记录锁起来，就形成了7个next-key lock，分别是 (-∞，0],(0,5],(5,10],(10,15],(15,20],(20,25],(25,+supermum]。InnoDB 给每个索引加了一个不存在的最大值 supremum来构造 前开后闭区间。
缺点：间隙锁的引入，可能会导致 语句锁住更大的范围，从而影响并发度。
小结
- InnoBD全表扫描时，给所有的行都加了行锁，也加了间隙锁。
- 行锁 比较直观，规则简单；间隙锁会影响系统的并发度，增加了锁分析的复杂度，但有章可循。
360
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
