MySQL简言

SQL执行顺序

(6) SELECT [DISTINCT | ALL] col1, col2, agg_func(col3) AS alias
(1) FROM t1 JOIN t2
(2) ON (join_conditions)
(3) WHERE where_conditions
(4) GROUP BY col1, col2
(5) HAVING having_condition
(7) UNION [ALL]
    ...
(8) ORDER BY col1 ASC,col2 DESC
(9) OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;

储存引擎

MySQl5.5后默认使用InnoDB引擎，与此前的MyISAM相比，InnoDB支持事务，行锁，间隙锁，MVCC，外键；索引也采用聚簇索引

BufferPool

为提升读写性能，InnoDB不会直接读写磁盘而是引入BufferPool。

BufferPool基本思路

1. 减少磁盘IO次数：读先读缓存，写先写redo log再批量刷脏
2. IO合并为顺序IO：多次写操作合并后再顺序执行，将随机IO变为顺序IO，这能减少磁盘寻址的时间

CheckPoint

checkpoint是在redo log中指示脏数据（留在bufferpool未刷入盘的数据）位置的指针，根据种类，checkpoint会按照指定策略刷脏。

改进型LRU

实际上，MySQL 确实没有直接使用 LRU 算法，而是在 LRU 算法上进行了优化。这是因为全表扫描和预读机制中，MySQL的一次读取都可能带着很多其他数据页，这就可能把不久前刚读出来的数据页挤占出去。

改进型LRU则相当于冷热分离，其相比于LRU，新元素不从头部插入，而是从中间位插入。该中间位将LRU队列分为了old区和young区。新数据如果在1s（由innodb_old_blocks_time控制）内被访问就会晋升到young区，如果需要淘汰数据页也只会从old区淘汰。

日志

	bin-log	redo-log	undo-log
生成	MySQL Server	InnoDB	InnoDB
作用	主从复制/数据恢复	崩溃恢复	MVCC/事务回滚
类型	逻辑日志	物理日志	逻辑日志

主从复制

主节点的数据变更先记录在bin-log上，从节点的IO进程会主动向主节点的Log-Dump线程拉取bin-log的更新内容，拉取后从节点记录到自己的relay log中。从节点还有SQL进程，用于读取relay log并执行语句，进而实现主从同步。

主从复制有三种模式:

1. 异步模式：master提交事务后立刻返回结果给客户端，不关心slave是否已经接收到binlog并处理
2. 半同步模式：master提交事务后需等待至少一个slave接收到bin log并写到relay log才返回成功
3. 全同步模式：master提交事务后必须等待所有的slave都接收到bin log并写道relay log后才能返回成功

索引

B+树

InnoDB的索引结构为B+树，相比于B树有以下优势：

1. B+树只有叶子节点储存数据：B+树的非叶子节点体积要更小，而树的每一层占用一页，这导致一页能储存更多的节点，B+树整体会更为矮壮。那么高度越低，检索的时间复杂度也越低。
2. B+树叶子节点以双向链表连接：叶子节点可互相访问，这样范围查询的效率更高

聚簇索引

聚簇索引是一颗B+树，叶子节点储存索引列的数据。InnoDB会根据每张表的主键构造主键索引，索引的叶子节点存放的是完整的数据行。非主键上构建的索引称为辅助索引，辅助索引的叶子节点就只储存了数据行在索引列上的数据，以及数据行在主键索引的位置。

而非聚簇索引的主键索引和辅助索引结构一致，也都是B+树。叶子节点不储存数据，数据储存在独立的地方，而叶子节点储存一个指向真正数据的地址。

索引覆盖与回表

InnoDB的辅助索引的叶子节点只有索引列的数据，如果查询的SELECT列包含在了索引列中，本次查询就只需要经历一次辅助索引，称之为索引覆盖。否则，SELECT中额外的列需要回到主键索引中继续查询，该过程称为回表。

索引下推

在遍历索引的时候，先查询索引包含的字段，过滤掉不符合条件的记录，减少回表次数。

页

MySQL与磁盘交互的最小单位为页，页默认为16KB，页内储存着不定量的数据行。页内的数据行是根据主键升序排列的，因此如果插入了一个随机主键的数据，为保证有序性，页内数据就会迁移到合适位置，进而可能导致页分裂。其中涉及到的数据重排、新页申请都会影响插入效率。

MySQL查询主要流程：

1. 查询数据时，先通过索引定位到数据所在的页，
2. 然后将页内整体加载到缓存中，
3. 通过二分法在页中检索数据，定位数据到某两个slot之间
4. 最后从start slot 指向的记录向后查找，直到查到数据或者end slot的最后一条记录。

最左匹配原则

对于联合索引(a,b,c)，MySQL会从左至右依次匹配查询列，直至遇到范围查询。对于WHERE a=1 and b>2 and c=3，ab会利用上索引，c则不会。

索引失效场景

除了不符合最左匹配原则，有些场景会直接导致索引失效。

1. WHERE a+1=2 | WHERE a=‘1’ | WHERE a != 1 | WHERE a is not null | WHERE a like ‘%a’
2. or查询中，有一个条件的索引失效都会导致整个查询的索引失效

高效利用索引

1. 高频查询的，内容简短的、区分度高的字段适合建立索引，更新频率高的字段不适合做索引
2. WHERE语句遵循最左匹配原则，SELECT要尽量是索引覆盖，覆盖不了也要保证索引下推
3. 避免索引失效场景

事务

ACID

1. 事务的原子性是通过 undo log 来实现的

2. 事务的持久性是通过 redo log 来实现

3. 事务的隔离性是通过 (读写锁+MVCC)来实现，读写锁解决写写冲突，MVCC解决读写冲突

4. 事务的一致性是通过 原子性、持久性和隔离性 来实现，总的来说，一致性是事务完成后的结果要与预期一致

持久性

MySQL的数据修改会先记录在redo log上，redo log再根据刷盘策略持久化到磁盘上。此外，InnoDB存储引擎有一个后台线程，每隔1秒，就会把redo log buffer中的内容写到文件系统缓存（page cache），然后调用fsync刷盘。

日志文件组

硬盘上存储的redo log日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。组件组将多个redo log环形排列，write pos标记当前记录位置，记录新数据则后移；checkpoint标记脏数据的开始位置，刷脏时后移

当write pos追上checkpoint就必须刷脏了。

原子性

当事务对数据进行修改时，undo log会记录修改的反向操作。当事务需要回滚时便可以利用undo log恢复到原状态。

隔离性

并发事务会存在脏读、不可重复读、幻读的问题，MySQL提供的读已提交、可重复读、可串行化隔离级别能够依次解决以上问题。

锁实现

读已提交：读操作不添加锁，写操作要求排它锁X

可重复读：读操作要求共享锁，读完就释放，写操作要求排它锁

可串行化：读操作要求共享锁，事务结束才释放，写操作要求排它锁

间隙锁

排它锁和共享锁并不能完全解决幻读问题，如果读操作是范围查询，就需要间隙锁将整个范围都锁住，共享锁只能锁住一行。

间隙锁有Gap Lock和Next-key Lock，Next-key Lock比起Gap Lock多锁住了记录行。

MVCC实现

MVCC通过版本控制来实现不加锁而达到读写分离的效果。InnoDB为每一行记录都创建了两个隐藏列，分别记录了行的创建版本和行的删除版本。Undo log中记录了数据行的版本变更，这样每一行都会有历史可追溯。

此外Read View用来屏蔽高于Read View版本的数据变更，这样如果要解决脏读，读事务会在每次读操作前更新Read View，保证每次读的都是最新版本的数据。如果要解决不可重复读，读事务只会在事务开始时生成Read View，保证读到的都是同一版本。

分库分表

数据库瓶颈

IO瓶颈

磁盘IO: 如果热点数据过多，数据库缓存被占满，每次查询会产生大量IO进而降低查询速度。此时可采用分库或垂直分表

网络IO：请求数据过多，网络带宽不足。此时可采用分库

CPU瓶颈

SQL：SQL查询包含了耗时操作如join、group by、索引不当等会增加CPU运算压力。此时多为优化表结构、SQL语句或是业务简化

单表数据过大：查询时扫描的行过多。此时可采用水平分表

分库分表策略

水平分库

以字段为依据，按照一定策略（Hash、Range、Tag等）将数据引流到不同库中。分库的库结构一样、库中数据不重复、所有库的数据的并集是全量数据。

水平分库在面对高并发量的场景能很好缓解IO和CPU压力

水平分表

以字段为依据，按照一定策略将数据引流到不同表中。

相比于水平分库，水平分表适用于并发量不高但单表数据过于庞大的情况。

垂直分库

以表为依据，按照业务归属不同，将数据引流至对应的库中。

垂直分库是以服务化的思想来看待，库与库之间的数据不具有相关性，每个库应该服务于单个或多个强相关的服务

垂直分表

以字段为依据，按照字段的活跃程度分离出热表（主表）和冷表（扩展表）。一般来说，冷热表应至少有一列交集，用于关联数据。

垂直分表适用于并发量不高，但表字段很多，且有明显的热点数据和非热点数据区别，单行数据的储存空间较大。

非Sharding Key查询

分片依据（Sharding Key）要求选择查询频率高，长度简短的列。当查询时不带Sharding Key，则需要想办法引入Sharding Key用于寻找表的位置。

映射法

另建查询列和Sharding Key的映射表，查询前先找到Sharding Key再定位到具体表

基因法

查询列经过转化生成一串基因,取出后x位(x取决于分布式数据库个数),将其代替掉分布式ID的后x位,得到的就是Sharding Key.

分库分表问题

1. 事务一致性问题，解决方案有：
   • 分布式事务
2. 跨库join查询，解决方案有：
   • 表冗余，全局表
   • 字段冗余
   • ER分片，有关联关系的表放同一个分片上，能有效避免跨分片join的问题
   • 业务层分两次查询然后组装（常用）
3. 跨节点分页、排序、函数问题，解决方案有：
   • 将不同分片的结果集进行汇总和再排序
4. 全局主键避重问题，解决方案有：
   • 分布式id，比如雪花算法、基于数据库实现的号段模式等。
5. 数据迁移、扩容问题，解决方案有：
   • 迁移历史数据、容量规划
   • Range + Hash取模 分组法