MySQL select语句执行

prepare 阶段，检查查询语句中的表活字段是否存在，将 * 拓展为表上的所有列。
optimize 阶段，优化器决定使用哪个索引。
execute 阶段，执行器，索引下推。

表空间文件结构

表空间 -> 段 -> 区 -> 页 -> 行

页：InnoDB的数据按页为单位读写，默认每个页大小为16KB，意味着一次最少从磁盘读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。表中的记录存储在数据页中。B+树的每个节点都是一个数据页。

区：数据量大的时候，索引分配空间则是按区为单位分配，每个区的大小为1MB，对于16KB的页来说，连续64个页会被划分到一个区，这样，B+树中节点所构成的链表中相邻页的物理位置也相邻，便能顺序IO。

段：索引段、数据段、回滚段。

索引段：存放B+树非叶子结点的区的集合
数据段：存放B+树叶子结点的区的集合
回滚段：存放的是回滚数据的区的集合，MVCC利用回滚段实现了多版本查询数据。

行格式

Redundant
Compact，一条完整的记录分为“记录的额外信息”和“记录的真实数据”，额外信息中又分为变长字段长度列表、NULL值列表和记录头数据。变长字段长度列表和NULL值列表都是倒序保存，并且不是必须的，只要没有变长字段或NULL值字段即可。8个字段值可以为NULL，那么NULL值列表空间为1字节，9字段则2字节。
Dynamic
Compressed

Varchar(n) n最大取多少？

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。注意，是一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和「NULL 值列表」所占用的字节数的。

行格式溢出后，数据存放到溢出页。

B树和B+树

B树（多路的平衡搜索树），节点可以存储多个数据，并且每个节点存放索引和数据，一个节点的关键字等于该节点子节点个数减一。查询的最好情况是O（1），一般高度为3。
B+树，非叶子节点关键字个数等于子节点个数
1. 叶子节点保存数据（数据页），非叶子节点保存索引，查询固定为O（logn），并且由于这个特点更适合外部存储。
2. 叶子节点有指向下一个叶子节点的指针，双向链表，可顺序访问，因此区间访问的性能较好。
3. 存在重复元素，因为非叶子结点不保存数据。

与红黑树的比较，B+树的优势：

更少的查询次数，因为B+树的树高一般小
利用计算机的预读特性，因为B+树具有类似链表的特点，因此相邻的节点也能被预先载入

为什么不使用跳表：MySQL 一次数据页加载都需要一次磁盘IO。并且磁盘IO的次数和树高有关系，又因为跳表的高度一般比B+树高，所以查询速度会大大降低，因此不适合。

为什么 Redis 使用了跳表：因为 Redis 不存在磁盘IO。重点在于磁盘IO次数。

MySQL索引

给表添加索引时，是会对表加锁，因此在生产环境中不能直接添加索引。

数据结构维度：

B+树索引，适合范围查询，复杂度为O（logn）。
哈希索引，能以O（1）时间进行查找，但失去有序性，无法用于排序和分组，只支持精确查找（等值查找）。
全文索引，使用倒排索引实现，记录关键词到所在文档的映射，一般在text, varchar上创建。
R-Trees索引，MyISAM支持的索引类型，和空间地理数据有关。

物理存储维度（InnoDB）：

聚簇索引，叶节点存放一整行记录。一个表只能拥有一个聚簇索引。
非聚簇索引（二级索引），叶节点只存放主键信息，所以一般需要回表查询。

逻辑维度

主键索引，不允许空值
普通索引，没任何限制
联合索引，多个字段创建的索引，使用时遵循左前缀原则。
唯一索引，索引列中的值必须是唯一的，但可以为空值。
空间索引

索引失效？主要看是否回表或者是查询的数据量过大

不满足最左匹配（但也需要看是否是覆盖索引，例如对于联合索引(col1,col2,col3)，查询语句SELECT col1,col2,col3 FROM test WHERE col2=2;也能够触发索引，并且索引截断的情况可以会通过索引下推解决）
尽可能明确查询列，而不是select *，即使不满足最左匹配（可以用于性能优化）
索引列参与运算
索引列使用函数
错误的like使用(当模糊匹配的占位符位于条件的首部，并且要看数据库中的字段)
类型隐式转换（参数类型和字段类型不匹配，例如 where no = 1，但no实际上是varchar类型的）
错误的or使用（切记两个条件都要添加索引，否则会导致索引失效，or两边同时使用 < 和 >，也会失效）
两列做比较
错误的 <> 和 != 使用（查询结果集占比较大时索引会失效）
is not null(is null 走索引)
not in(条件列是主键时走索引)
not exists
orderby（部分会失效）
范围过大的 between and

索引跳跃扫描

最左缀原则可以通过跳跃扫描的方式打破，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。

覆盖索引

指查询列被所建的索引覆盖，覆盖索引是 select 的数据列只需要从索引中就能取到，不必回表。

哪些情况下不适合建索引

数据量少
更新频繁
区分度低
where, groupby, orderby 后没有使用到的字段
联合索引中的索引

索引下推

指将部分上层（服务层）负责index filter的事情，交给了下层（引擎层）去处理。它能减少二级索引再查询时的回表查询次数，提高查询效率。只适用于二级索引。

EXPLAIN 命令的extra一栏中有Using index condition，表明使用了索引下推

举例：select * from table1 where b like '3%' and c = 3

5.6 之前：

先通过联合索引查询到开头为 3 的数据然后拿到主键
然后通过主键去主键索引里面去回表查询二级索引里面查询出来几个 3 开头的就回表几次

5.6 之后：

先通过二级索引查询到开头为 3 的数据然后再找到 c = 3 的数据进行过滤之后拿到主键
通过主键进行回表查询，减少了回表次数

建索引的原则

选择唯一性索引
为经常需要orderby，groupby操作的字段建立索引
经常作为查询条件的字段建立索引
限制索引数量
尽量使用数据量少的索引
尽量使用最左前缀匹配原则
使用区分度高的列作为索引
扩展索引而不是新建索引

事务的隔离

MVCC: Read view + undo log + 2个隐藏字段（版本链），为了解决读写冲突

Read View 就是一个快照，保存某个时刻的数据信息。其中也有四个字段，分别是creater_trx_id, m_ids, min_trx_id, max_trx_id。
undo log是存放更新前的数据（快照），保存了历史快照
隐藏字段：row_trx_id 和 roll_pointer，前者表示更新行数据的事务id，后者是只想改行数据上一个版本的undo log的回滚指针

MVCC(Multiversion Concurrency Control)的两种读：

快照读，不加锁，在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照，通过MVCC的方式解决幻读。实现的方式是启动事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的
当前读，加间隙锁（行锁）解决幻读，（FOR UPDATE）
- 查询条件不走索引，退化成表锁。
- 查询条件走普通索引，锁住查询条件附近的间隙，等于间隙锁
- 查询条件走唯一索引，只锁一条数据，等价于记录锁

三种问题：

脏读，指读取未提交数据
不可重复读，指在同一事务内读到的数据是不一致的
幻读，指事务在查询的过程中，有另外一个事务对范围内新增了记录，导致范围查询的结果条数不一致的现象。

SQL的四种隔离级别：

读未提交，可能出现脏读
读已提交，可能出现不可重复读
可重复读，可能出现幻读，但使用MVCC + 间隙锁能解决大部分幻读现象
串行化，对记录加上读写锁

MVCC + 间隙锁在RR的隔离级别下能解决大部分幻读情况

MVCC 快照读的情况下，多次查询获取的是同一个快照数据。
间隙锁，其他事务的插入操作会被阻塞。
解决不了的幻读情况：（MVCC只对于插入数据后的当前读，更新，删除等操作（不使用快照读），仍然存在幻读的问题）
1. 事务A快照读，事务B新增数据并提交，接着事务A修改事务B新增的数据并快照读，此时发生幻读。
2. 事务A快照读，事务B新增数据并提交，接着事务A当前读，此时发生幻读。

若要尽可能避免幻读现象的发生，尽量在开启时候后马上执行当前读。

WAL 机制

WAL(Write-ahead logging)指 MySQL 在执行写操作时先记录在日志中，后更新到磁盘中。

WAL 核心：将随机写（磁盘的写操作是随机IO，耗性能）转变成顺序写和组提交机制，降低客户端延迟，提高吞吐量。

三大日志binlog, redolog, undolog:

Buffer Pool是MySQL进程管理的一块内存空间，有减少磁盘IO次数的作用。
redo log重做日志是InnoDB存储引擎的一种物理格式的日志，用来实现事务持久性，主要有两部分文件组成，再内存中的重做日志缓冲（redo log buffer）以及磁盘中的重做日志文件（redo log），（循环写，数据会被覆盖）。使用场景：崩溃恢复，在发生故障的时间点，尚有脏页未写入磁盘，在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。
undo log回滚日志是InnoDB存储引擎的一种逻辑格式的日志，记录的是数据的逻辑变化，保证的是数据库的原子性，比如一条insert语句对应的是一条delete的undo log，在发生事务错误时，就能回滚到事务之前的数据状态；MVCC，事务未提交前，undo log 保存了未提交的版本数据，作为旧版本的快照数据，类似于做备份。
binlog是MySQL Server层的一种逻辑格式的日志，用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。使用场景：主从复制 ：在 Master 端开启 binlog ，然后将 binlog 发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致；数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将数据恢复到过去的某一时刻。

两阶段提交：

prepare阶段：先将数据修改写入redo log，并将其标记为 prepare 状态，将相应的 sql 语句写入 binlog。
commit阶段：将 redo log 标记为 commit 状态，然后根据 sync_binlog 参数的设置，决定是否将 binlog 刷盘。

如果发生崩溃，可以根据redo log恢复数据页的状态（让InnoDB存储引擎拥有了崩溃恢复能力），也可以根据 binlog 恢复sql语句的执行（保证了MySQL集群架构的数据一致性）。通过两阶段的流程可以保证任何时刻 redo log 和 binlog 在逻辑上一致。

为什么需要两阶段提交？

以基本的事务为单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以redo log 与 binlog 的写入时机不一样。为了解决两份日志之间的逻辑一致问题，将redo log的写入拆成了两个步骤 prepare 和 commit，即两阶段提交。如果只有 redo log 或者只有 binlog，那么事务就不需要两阶段提交。

执行顺序

from
on
join
where
group by
having
select
distinct
order by
limit

每一步执行时都会产生一个虚拟表，都会被用作下一个步骤的输入。

锁

乐观锁、悲观锁

乐观锁：不能解决脏读问题
悲观锁：select … for update

全局锁、表级锁、行锁

全局锁：对整个数据库实例加锁，处于只读状态，其命令是Flush tables with read lock（FTWRL），用于全库逻辑备份。
表级锁：开销小，加锁快，不会出现死锁，发生锁冲突的概率最高，并发度最低。适合以查询为主。
- 表锁，可以添加表级别的共享锁或排他锁，锁的粒度较粗。
- 元数据锁（MDL），CRUD时，数据库自动给表加上MDL读锁；变更表结构时，自动加MDL写锁。事务提交后才会释放MDL锁。
- 意向锁，在执行插入、更新、删除需要加独占锁之前，需要对表先加上意向独占锁，其目的是快速判断表里是否有记录被加锁。
- AUTO-INC锁，自增锁，插入数据时获取该锁，插入语句完成后，释放锁。
行锁：开销大，加锁慢，会出现死锁，发生锁冲突的概率最低，并发读也高。只有InnoDB引擎支持。行锁依赖于索引，如果加锁操作没有使用到索引，那么会退化成表锁。
- 记录锁：锁定一条记录，存在 S 型记录锁和 X 型记录锁。加了 S 型记录锁后可以继续加 S 型记录锁，但不能加 X 型记录锁；加了 X 型记录锁后不能加 S 型或 X 型记录锁。存在于包含主键索引的唯一索引中。
- 间隙锁：锁定一个范围，但不包含记录本身，只存在于可重复读隔离级别，为了解决可重复读隔离级别下幻读的现象。存在于非唯一索引中。插入意向锁是一种表明插入意向的间隙锁。
- 临键锁：锁定一个范围，前开后闭，存在于非唯一索引中。

共享锁（读锁）、排他锁（写锁）

共享锁：读锁，其他事务可以读，但不能写，select lock in share mode
排他锁：写锁，其他事务不能读，也不能写，select for update

mysql 数据实时同步到Es

常见的数据同步方案主要有以下三种：

同步调用。在实现增删改的同时，通过调用ES所在服务提供的接口。
异步调用。增删改服务和搜索服务分别通过MQ进行发送和监听消息，有效降低业务的耦合度，但较为依赖MQ的性能；
binlog监听。给MySQL开启binlog功能，搜索服务基于canal监听binlog变化，但开启binlog会增加数据库负担、同时实现复杂度较高。

但也有异步调用和binlog监听结合在一起的例子，用canal作slave节点。

分库/分表

分为 水平切分（又称为 Sharding） 和 垂直切分。

切分策略

范围切分，例如每一千万条数据一张表
哈希切分，对分表键进行哈希运算，主流方法
映射表，将分表键和数据库表对映射关系记录在表上

分布式ID

建议使用雪花算法，其中序列号一直保持递增，不会因为时间戳的不同而归零，防止被猜测上下文ID。

MySQL知识点汇总