体系结构

server层

负责建立连接、分析和执行 SQL

连接器：与客户端进行TCP三次握手；校验用户名和密码；读取权限。
查询缓存：key-value缓存在mysql8.0后默认将其关闭；高级版本默认采用页缓存。
解析器：词法分析 + 语法分析。
预处理器：检查 SQL语句中的表或者字段是否存在；将select * 中的 * 扩展为表上的所有列。
优化器：依据执行成本，指定最优的执行计划。
执行器：与存储引擎进行交互，调用引擎的相关接口，并获取查询结果。

存储引擎层

负责数据的存储和提取。

SQL语句

重难点为高级查询，建议通过做10道题来巩固理解。
查询后排序：
select * from … order by col1 asc（查询结果按col1升序排序）
select * from … order by col1 asc col2 desc（查询结果按col1升序排序，col1相等时按col2降序排序）
分组查询：
select … from … group by … having …
联表查询：
select … from table1 inner join table2 on table1.colA = table2.colB
select … from table1 left join table2 on table1.colA = table2.colB
select … from table1 right join table2 on table1.colA = table2.colB
内连接：只取两张表有对应关系的记录
左连接：在内连接的基础上保留左表没有对应关系的记录
右连接：在内连接的基础上保留右表没有对应关系的记录

索引

索引的作用

相当于一本书的目录，作用是加快查找。
若没有索引或者没有按照索引进行查询，则会进行全表扫描，可能会导致业务崩溃（长时间无响应）。

B+树

B+树的一个结点就是页，在innodb中大小为16K，此值可以修改。
数据页内有文件头，页头，目录项，数据区等字段；数据按槽分组，槽搜索使用二分，槽内数据以单链表的形式组织，需要顺序遍历。
非叶子结点存放索引和磁盘地址信息，叶子结点存放“”数据信息“”，具体是什么数据信息由索引类型决定。
一个索引对应一个B+树。
B+树的树高，决定了磁盘IO次数（每找一个数据页，需要一次磁盘io）。

聚集索引

一个叶子结点存放若干个（主键 + 一整行数据）。

辅助索引

一个叶子结点存放若干个（key +主键）。
回表查询：先走辅助索引，再走聚集索引。
若要查找的字段为索引的key或者主键，则无需回表，此时索引又称为覆盖索引。

覆盖索引

是一个相对/动态概念。
聚集索引一定是覆盖索引。
其他索引（又称二级索引/辅助索引）可能是覆盖索引也可能不是，取决于select查询的字段是否在此索引内。

组合索引

组合索引的第一个字段为B+树排序的key，其余字段无序。
组合索引属于辅助索引的一种。

最左匹配原则

对于组合索引，从左到右依次匹配，遇到 > < between like 就停止匹配。

索引下推

MySQL 5.6推出优化策略，在存储引擎层实现，将部分检测条件满足的工作放到了存储引擎层。
针对组合索引的范围查询（> < between）和模糊查询（like）。
目的是减少查询时的回表操作。

常见的索引失效

使用组合索引查询时，没有使用第一个字段。
对索引使用左模糊匹配或左右模糊匹配。
索引参与了表达式或函数运算。
where语句中 or 中存在非索引列。

索引设计原则

对查询频次较高的字段建立索引。
使用短索引：key‘越小，一个结点中存贮的key越多，B+树高度越小，查询效率越高。
自己创建整数且自增的主键；整数是为了快速比较，自增是为了减少页分裂。
尽量扩展已有索引而不创建新的索引。
设置合理的组合索引，实现索引覆盖，减少回表操作。
尽量避免使用select*：减少网络传输；避免回表操作。
索引列设置为空，防止使用not null。
开启索引下推（默认开启）。

如何找出效率低的sql语句。

htop top查看cpu 和磁盘的占用率。
sql-slow-loh（慢查询日志，保存执行时间超过10s的sql语句，这个时间可以修改）。
发布后，用户反馈。

事务

概念

事务由一条或多条sql语句组成，为一个程序执行单元。

事务的状态和控制语句

BEGIN命令或start transaction命令：开启事务但未启动事务。
BEGIN命令 + 一条sql语句：开启事务并启动。
start transaction with consistent snapshot命令：开启事务并启动。
COMMIT命令：提交事务，对数据库的所有修改持久化。
ROLLBACK命令：回滚事务，结束事务并撤销所有未提交的修改。
执行单条语句的情况下：mysql默认在语句执行前开启事务，语句执行后提交事务。

commit与数据落盘

执行DML语句后但未提交：修改的数据会作用到缓存上，变为未提交的脏页，是不会被落盘的。
执行DML语句并进行提交：缓存数据由未提交的脏页变为已提交的脏页，是需要被落盘的，但并不是commit操作后旧立刻执行落盘（具体机制由buffet cache + page cache机制决定）。

ACID特性

原子性：事务操作要么都做（提交），要么都不做（回滚）。回滚操作依赖undolog。undolog存放在共享表空间，记录的是事务每步的具体操作，当执行回滚时，倒序进行具体操作的逆运算。
隔离性：多个事务（线程）并发执行时，相互不影响。设置了不同程度的隔离级别（隔离级别越高，并发性能越低）隔离级别通过MVCC + 锁的方式实现。
持久性：事务提交后，相关的DML操作将会持久化（依赖redolog）；即使发生宕机故障，也能redolog进行数据恢复。
一致性：一个事务只有提交后才对其他事务可见。

隔离级别

读未提交：指一个事务还没提交时，它做的变更就能被其他事务看到。具体措施：读操作不做任何限制，直接读，读到的是最新的数据，写操作加排他锁。
读已提交：指一个事务提交之后，它做的变更才能被其他事务看到。具体措施：支持MVCC，读取最新的历史数据（最新提交），写操作加排他锁。
可重复读：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别。具体措施：支持MVCC，读取当前事务刚开始时的版本（最旧提交），写操作加排他锁。。
可串行化：在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。具体措施：读操作加共享锁，会导致事务串行化执行，丧失了并发性，因此具有最高的隔离级别。

MVCC实现原理

实现原理：undo版本链 + read view

在这里插入图片描述

（注：图片来自小林codong图解mysql）

某行记录版本链的更新：和undolog更新时机相同，针对对此记录执行的任何DML语句后都会产生一个版本，最新版本位于链表头，最旧版本位于链表末尾。
在这里插入图片描述

通过当前事务的read view来决定当前事务能够读取到的记录版本，分三种情况讨论：

若读取的记录的trx_id < read view中的min_trx_id，说明该记录在创建此read view前就被其他事务提交了，那么这条记录对当前事务是可见的。
若读取的记录的trx_id >= read view中的max_trx_id,说明此记录是创建此read view后由一个较新的事务修改的版本，那么这条记录对当前事务是不可见的，需要沿着roll_pointer向下查找，找到对应的版本。
若读取的记录的trx_id在min_trx_id和max_trx_id之间，说明此条记录是由较老且未提交的事务所修改的（对于read view而言），那么需要进一步判断此trx_id是否在此read view 的 m_ids中，如果在，那么依然不可见，需要沿着roll_pointer向下查找，找到对应的版本；如果不在那么可见。

可重复读和读已提交的实现由read view创建的时机决定：

对于可重复读：在启动事务前（不是开启！）会生成一个read view，然后再事务活跃期间都使用这个read view做版本判断。
对于读已提交：每次执行sql语句前都会生成一个read view，仅仅此sql语句使用这个read view做版本判断。

innodb的可重复读如何解决幻读

快照读：读取快照中规定版本的数据。当前读（直接读）：读取最新的数据。
分为快照读和当前读两种情况。对于快照读使用MVCC来避免幻读，对于当前读使用锁来避免幻读。
在可重复读前提下，普通select语句使用的是快照读，开启事务后的第一条select范围查询语句前会生成一个快照，期间其他事务可以执行插入操作，但对当前事务不可见，因为之后的select范围查询语句都使用的之前的快指，所以这种情况下避免了幻读。
除了普通select语句，DML语句和select … for update/in share mode使用的是当前读，不使用MVCC的快照机制，而是通过加next-key锁来解决幻读，next-key锁属于行锁，当前事务结束才会释放。执行当前读后对某些记录加上next-key锁，之后其他事务在特定范围内执行insert语句尝试插入时会被阻塞（插入意向锁和间隙锁冲突）。因此解决了幻读。

锁

表锁：需要手动使用lock命令添加，分为共享表锁和独占表锁。
行锁：无需使用显式的lock，分为共享行锁和独占行锁、记录锁（共享行锁或独占行锁）、间隙锁、next-key锁。在可重复读的隔离级别下，普通select语句不使用锁而使用快照读，而select…for update语句和DML语句默认加next-key锁。
意向锁：作用是为了快速处理行锁和表锁之间的兼容性判断，不能手动添加，加任何行锁之前都会加意向锁，分为意向共享锁和意向独占锁。只在表锁和行锁同时使用时起作用，大部分情况下不起作用，因为innodb很少使用表锁，绝大部分情况下使用行锁。
有关意向锁和表锁之间的兼容性：共享表锁与独占表锁和独占意向锁冲突；独占表锁与共享表锁和共享意向锁冲突；共享表锁之间不冲突，共享表锁与共享意向锁之间不冲突；任何意向锁之间都不冲突。
插入意向锁：本质上不属于意向锁，而是一种特殊的间隙锁，插入操作时会默认添加。
插入意向锁和间隙锁是冲突的，这也是死锁产生的一大原因之一。
锁的释放时机：除了自增锁是语句执行完释放，其余锁都是事务结束（提交 or 中止）后释放。