简单记录下mysql相关知识，由过往笔记整理而得，如果本文所有的概念你都有相当见地，那说明你得水平比本人要高的多基本迈向DBA。我把DBA水平定为高级，因而本文定为中级。抽出大量时间写这个是为差缺补漏，如果认定某些知识点两三年都不会错乱那么就不会记录，此为备忘录。

大概分为五个部分，索引，索引优化，七种日志，锁和mvcc，事务。

绝大部分的知识和概念都起源于这三本书：
《高性能MySQL》
《MySQL技术内幕 InnoDB》
《深入浅出MySQL》
当然书也是通过解读官方文档加上直接经验而来的。

必不可少的mysql服务器构造

以下所有知识点都是基于此图展开。

1、Mysql索引

可以看到mysql有很多索引，但是常用的无非就两个，MyISAM和InnoDB。MyISAM在许多老项目中仍然处于维护状态。
索引用一句话来说就是排序好的数据结构。
画个表格对比一下：

全文索引的意思是全文搜索引擎ElasticSearch那种倒排索引，即属性值来确定记录–value来找key。
MyISAM和InnoDB都是用的B+树，区别在于数据是否和索引(主键索引(专有名词RowID)，非主键索引要回表)绑定在一起。InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。MyISAM要通过MYI找到MYD(额，我自动翻译为myindex和mydata)。
由此可知InnoDB表必须有主键，最好是整形自增的主键，如果用随机生成的字符串作为主键的话，还要多一步字符串比较，也不能有序的(自左而右的)在B+树上分裂自增。如果没有主键会找一列没有重复数据的做主键，如果都没有就自己生产。
MyISAM在select count（*）是具有极大优势，至于为什么有优势大多数人讲的含糊不清大概意思是InnoDB维护很多额外信息(比如mvcc的rtx_id，row_id，架构的操作等)。
至于开发者所说的高性能Mysql的高性能来自何处，那就是InnoDB了，所以我不对MyISAM再做研究。

索引怎么就加快了查询速度？

最主要的是B+树减少了磁盘IO次数。
每一次IO就是要去寻道，寻点再拷贝到内存，这是很复杂的一件事(其时我电脑上没有机械硬盘，而固态硬盘寻址和内存相似但又不同，比如非线性的寻址映射的损耗均衡算法)，为了优化系统做了预读的操作，即把相邻的数据也读取到内存的缓冲区，每一次IO读取一页数据，一页为4K或者8K具体看你操作系统几位。同时页（Page）也是 InnoDB 存储引擎用于管理数据的最小磁盘单位但InnoDB的页默认大小是16K(为啥是16K？因为A4纸就是16kヽ(°◇° )ノ)。常见的页类型有数据页、Undo 页、系统页、事务数据页等。要注意InnoDB 存储引擎保证了每一页至少有两条记录，如果一页当中的记录过大，会截取前 768 个字节存入页中，其余的放入BLOB Page。

为什么不选择hash？

并不是hash碰撞，这方面已经处理的很好了，最大的问题在于hash不支持范围查找。
这里有必要补充一下B+树在InnoDB的体现，索引(RowID)是和数据绑在一块的都坠在叶子节点上，即这个叶子节点上存储着data，那叶子节点的大小应该是多少？查了一下，一般是一页或者是页的整倍，因为读取至少读一页或者读取整倍数。
叶子节点是双链表的形式(应该不是循环双链表)，也就是一页一页的连起来之间用两根线穿起来，而页内的结构为单链表把文件头页头等等穿起来，为了方便在页内查找数据又为这个页内链表做了页目录(设计者狗头天才，这就是一本线订书啊。页目录的目录再组成页目录层层套娃就有了B+树)有了页目录就可以很方便的二分查定位到相应的槽，再遍历该槽对应分组中的记录即可快速找到指定的记录。
还是加个图吧：

页的设计以及槽相关估计记不住。

基于此应用下的B+树做个简单的计算题：
如果有1G的空间存储索引，那么则存在65535个页，只需要256个二级目录，16个三级目录，4个四级目录，两个五级目录，一个六级目录一共279个额外页空间，279*16=4464KB≈4MB，约4MB的空间就可以为1G的数据提供检索。

那么InnoDB除了下面会提到的自适应hash，InnoDB内能手动创建显式hash吗？
当然不能但是能创建一个伪hash索引(天才的自问自答)，伪在于以哈希值而非键本身来进行索引查找。应用场景比如url，又臭又长，可以先对其通过hash把url映射成4个字节，再以此哈希值作为索引，这样将大大减少索引占用。

索引为了加速查询做了哪方面设计？

其时在你建表后插入数据时，mysql就已经创建好索引了，会用你指定的递增主键维护起一颗B+树(平衡m叉排序查找树)。试想如果没有这颗B+树那么你的所有操作都是全部扫描。根据我上面所讲，这棵树无论你有没有主键都会维护起来。
这时候你发现

select * from student where id = 1; 

可以轻松查到，但是

select * from student where name = "flitsneak"

仍是全表扫描
如果在name字段建立索引，那么会维护一张name字段的B+树。如果要通过name查询所有相关信息就会产生回表操作，也就是根据name索引找到主键去和mysql自动建立的索引表做匹配。而这种方式其时是一种加速查询的优化策略，即聚集索引+二级索引(辅助索引)，name索引即是辅助索引。

记录一下InnoDB架构

![InnoDB架构]
由buffer pool就知道，mysql不会直接去修改磁盘数据，而是先改内存（存放于bufferpool中），记录redolog，等有空了再去刷新磁盘，如果内存没有数据就去磁盘load。bufferpool的数据结构是页链表，缓存淘汰策略为LRU。
工作流程为：当你要修改页不在内存中，就先记到change buffer的地方，同时记录redolog，然后把数据加载到内存，load过来后再把changebuffer的里的记录修改，应用到bufferpool中，此为merge，反之把内存数据刷到磁盘叫purge。（简单理解就是，要修改一条数据但是数据不在内存中，那么就把修改的这条语句和逻辑存到changebuffer中，等下次需要查询这条数据的时候就把数据页读到内存中结合之前存到changebuffer的修改语句修改后提交，changebuffer其实是可以持久化的数据可以复制到内存也可以存储到磁盘）
Adaptive Hash Index 自适应哈希索引。这个就是哈希索引，为了在内存中快速定位到数据，这个索引的建立是InnoDB自己判断是否值得的。
logbuffer就是放了很多redolog，最后被刷到磁盘中。
Operating System Cache顾名思义这是操作系统的东西高速缓存。
Tablespace 表空间，从图中看出表空间有五类，我们平时建表的数据会随机存放在The System Tablespace,File-Per-Table Tablespace,General Tablespace三者中的任何一个地方。
Doublewrite buffer就是保证数据页的可靠性，mysql在刷数据到磁盘的时候未了避免意外情况数据丢失，会先把数据写到Doublewrite Buffer即备份一下，如果crash则利用此备份来还原数据。
这大体是InnoDB的架构图了，之前有在公开课上看到过大佬现场设计一个存储引擎的，就是基于此架构。

2、索引优化

索引优化是处于最底层的优化也是效果最明显的优化，能减轻服务器的负担减少经济花销。
一般sql性能下降，建立索引就能解决绝大部分问题，没有解决那么就可能是索引没有用到，或者sql写的太烂，再者就是你表结构设计的一塌糊涂不符合系统需求，再者即mysql服务器需要进一步优化但是这种优化帮助不大，万能的最终解决方案为换最大的处理器最快的电脑。

索引的优劣

advantage：提速；
disadvantage：占用空间，动态维护拖慢节奏。

索引的原则

需要：
1、频繁查询的字段要建立索引。
2、外键关系
3、高并发下组合索引
4、排序字段，统计或分组字段
不要：
1、频繁修改
2、数据大量重复

如何快速定位sql问题？

只从理论上来说，可能explain+慢查询日志+show profile就能定位分析问题了。执行顺序应该是show profile看看你cpu io等性能，如果你内存不够，cup核心少频率低肯定没法弄，然后开启慢查询定位，然后执行计划分析。但是实际上并不是这样的，我们后端开发人员有自己的方法，DBA又是另外的方法还有更加专业的工具。作为后端的，记录别人分享的一个优化器（optimizer）追踪方法。
查看优化器状态

show variables like 'optimizer_trace';

会话级别临时开启

set session optimizer_trace="enabled=on",end_markers_in_json=on;

设置优化器追踪的内存大小

set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

执行自己的SQL

select host,user,plugin from user;

information_schema.optimizer_trace表

SELECT trace FROM information_schema.OPTIMIZER_TRACE;

导入到一个命名为xx.trace的文件，然后用JSON阅读器来查看（如果没有控制台权限，或直接交由运维，让他把该 trace 文件，输出给你就行了。）。

SELECT TRACE INTO DUMPFILE "E:\\test.trace" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

注意：不设置优化器最大容量的话，可能会导致优化器返回的结果不全。

explain 执行计划

sql调优目的就是尽最大可能的使用最快的索引。

一般是用explain进行参数分析。本图来说sql调优是在执行器之前分析器(parser)和优化器(optimizer)之间完成的。
分析sql是否需要优化还要排除缓存的干扰，因为缓存存在无论怎么查询都会很快，而实际生产中，更新等操作都会刷新缓存导致缓存失效，sql速度随之大打折扣。mysql8.0以后不用关注此问题。
explain翻译为执行计划，explain+sql 可以得到类似下图：

id：操作顺序，id相同则按照顺序从上到下，id值越大优先级越高。
select_type：表的读取顺序
simple：简单查询；
primary：如果包含子查询，最外层被标记为此；
subsquery:在select或where列表中包含了子查询；
derived：在from列表中包含了子查询被标记为衍生；
union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived。
union result：从union表获取结果的select。
type：访问类型
从最好–>最差：
system->const->eq-ref->ref->range->index->all
system意思是表只有一行记录相当于系统表，也是const的特例；
const是通过索引一次找到；
eq-ref为唯一性索引扫描，对于每个索引键，表中只有一条记录匹配，主键或唯一索引会产生；
ref为非唯一性索引扫描，返回匹配某个单值的所有行，也可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体；
range值检测给定范围的行。一般在where，between，in，<,>中出现；
index全索引扫描，比all快，因为索引文件一般比数据文件小；
all全表扫描。
possible_keys:可能用到的索引。
key：实际用到的索引。
key_len：索引中使用的字数，可通过此计算查询中使用的索引的长度，越短越好。
ref：显示索引的那一列被使用了，或为常数。
rows：大致估算的找出所需记录需要读取的行数。
extra：
using filesort：（文件内排序）mysql使用了外部索引排序，而不是按照表内索引；
using temporary：使用了临时表保存中间结果，常见于orderby和groupby；
using index:表示相应的select操作使用了覆盖索引。即索引包含了所有所需信息。
using where表明索引被用来执行索引键值的查找，没有出现则是说明索引用来读取数据而非执行查找动作。

sql失效以及如何应对

1、在索引上使用了表达式或者函数导致失效：

select studentid from student where studentid+1=66

优化：简单可得的结果最好不要计算。

2、隐式类型转换

select studentid from student where studentid = 66

如果studentid键为varchar类型，一定要加引号’66’，否则mysql底层会进行函数运算把数字转为字符类型。
优化：一定要加’'防止巨大灾难。

3、隐式编码转换

DEFAULT CHARSET=utf8和DEFAULT CHARSET=utf8mb4这种两张表字符编码格式不一致如果进行联合查询会使utf8转成他的超集utf8mb4
优化：convert（列 using utf8）进行转换。

4、order by导致的全表扫描

select * from student order by age desc

age虽然有索引，但是需要回表，因为用了*，mysql会认为回表的代价比全表扫描的代价大。
优化：使用覆盖索引，或者加上limit条件。
5、不符合最左前缀法则（一般来说and顺序会被自动优化）
6、范围条件右边的列索引失效
7、！=或者<,>会使索引失效
8、is null，is not null无法使用索引
9、like通配符%最好写在右边。
10、or连接会失效。
索引设计规则遵循 三星索引：
1、where后面参与查询的列可以组成单列索引或联合索引；
2、避免排序。若出现order by col，那么取出来的结果集就已经是按照col排列好的，不需要再生成临时表。
3、select对应的列应尽量是索引列，尽量避免回表。
in和exists：

select * from A where id in (select id from B)

当B数据集小于A如上则in更优。
反之如下操作

select * from A where exists (select 1 from B where B.id=A.id)

人为的建表优化出来上面所说的伪索引，InnoDB还支持前缀索引。当字符串很长的时候，为了避免索引又臭又长，所以使用前缀索引。这样就要考虑截取到的用来做索引的字符串如何避免重复，因此涉及到一个概念索引选择性，即不重复的索引值（基数，cardinality）和数据表的记录总数的比值，比值越高代表索引的选择性越好，比如最好不过惟一索引的比值1。
SHOW INDEXES FROM table 可以查看每个索引的cardinality。

select count(distinct left(city,6))/count(*) as sel6 from citydemo

这样可以截取city字段6的长度下不重复的记录数比上总记录数然后推断该截取几位。
然后

alter table citydemo add key(city(6))

前缀索引无法使用order by更不用说group by也无法使用前缀索引做覆盖扫描，可能会因为前缀相同的太多增加扫描行数。如果前缀大多数相同则推荐字符串翻转。

select field_list from t where id_card = reverse('idcard')

前缀索引也要注意避免索引选择性良好但是个别前缀密度过大的情况。

mysql本身也有很多优化,记一个广为人知的叫索引下推(index condition pushdown)：就是当你使用非主键索引查询的时候难免会回表，回表很麻烦，索引下推对回表次数做了优化。对于学生的姓名年龄建立索引，主键索引是学生id，如果查询名字中带sneak的like “sneak%” AND age=21，mysql5.6之前是找出所有名字匹配的数据进行回表，结果可能是flitsneak 32岁；wangsneak 99岁等mysql5.6之后则用索引下推对其他列条件进一步判断筛选，这样就大大减少了回表操作。

3、七种日志

是只有七中日志吗？肯定不是，这是最重要的几个日志，别的零零碎碎的日志要么不全要么意义不大，所以只谈这七种。
对照上文的sql优化来说除了expain还有两种日志可以作为参考。
日志大多是默认关闭的因为会产生很多额外消耗，查看日志状态大多是

show variables like '%log_name%'

1、general_log 普通日志

叫普通查询日志又叫全局日志，记录的东西很多很全包括你执行的所有sql。
查看日志是否开启:

show variables like 'general_log'

开启日志功能:

set global general_log=on

全局日志搭配show profile可以查看具体sql性能，show profile 就是针对某几条sql或者最近15条sql显示相关的cpu，IO参数。

SHOW profile CPU,BLOCK IO io FOR query 2

2、slow_query_log 慢查询日志

开启：

set global slow_query_log=1

通过

show variables like 'long_query_time'

可以看到系统默认为10s
±----------------±----------+

| Variable_name | Value |

±----------------±----------+

| long_query_time | 10.000000 |

±----------------±----------+

set global long_query_time=3

这样可以改为3s，新开会话或者查询时加上global可以看到改为了3s。

show variables like 'log_queries_not_using_indexes'

这句顾名思义，建议开启：

set global log_queries_not_using_indexes=1

重点是mysqldumpslow 可以加 --help会显示后缀选项
-s, 是表示按照何种方式排序
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；
比如:
得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

3、redo log 和 undo log 和 binlog

redo log undo log 和 binlog是很有必要放在一起说的。
redo log(物理日志，InnoDB独有)关注的是事务的持久化，undo log(逻辑日志)关注的是事务的原子性，binlog(逻辑日志，mysql自带)是二进制日志。
redo log vs undo log
两者都是InnoDB引擎的日志，redo log是为了crash-safe设计的。之前的InnoDB架构图可以看到修改数据流程是从磁盘读取到内存中，即在bufferpool对数据修改后会生成redo log存在log buffer中，此时内存中这一页修改后的数据就和磁盘上那一页未修改的数据不一致了，内存的这一页就叫脏页，需要把脏页刷到磁盘上，这个刷是时不时的偷偷刷，空闲刷，事务提交刷，超过一半缓存即8M的时候刷，mysql关闭刷也可以配置相应策略刷(innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘，innodb_flush_log_at_timeout默认是1s刷一次，还有checkpoint规则)。redo log记录了在这页上做了什么，本来要刷到磁盘时突然数据库crash，那么可以根据redo log进行数据恢复，redo log是顺序IO，体积很小写入到磁盘的速度很快(redo log 由log block组成每个block 512B，多个redo log逻辑上成为log group)。
undo log 主要作用有两个----回滚和mvcc。logbuffer中同时存了redo log和undo log，可以理解为存储的是反向操作，和redo log不同，undo log同时存着前一个版本信息。详细的undo log比如回滚段，结构体等就不谈了，有幸看过秃头DBA的解析，有点致郁。有关mvcc以及事务的prepare和commit在之后细说。

redo 和 undo可以完美配合，假设有A、B两个数据，值分别为1,2，开始一个事务，事务的操作内容为：把1修改为3，2修改为4，那么会如此操作：
A.事务开始.
B.记录A=1到undo log.
C.修改A=3.
D.记录A=3到redo log.
E.记录B=2到undo log.
F.修改B=4.
G.记录B=4到redo log.
H.将redo log写入磁盘。
I.事务提交

redo log vs binlog
binlog 是mysql service层日志文件，感觉上就是更高一层的日志，其为记录了表结构变更以及表数据修改的二进制日志文件，像select，show这样的操作不会记录。binlog有三个用途—恢复，复制，审计。恢复就是根据日志文件恢复被删的库，复制常用于主从复制(master和slave的故事)，以及后端canal监听。审计据说是通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

图中的relaylog就是中继日志了。binlog有三种格式statement，row，mixed精确度不一样，row精确用row。
binlog记录了表结构的修改以及各种修改sql，而redo log记录的是物理修改内容，逻辑和物理的区别很大，redo log注重的局部恢复，binlog相对更整体性。redo log刷到磁盘就消逝了没有历史版本，binlog会追加记录的形式持续记录。顺序上来说，先写入redo log，undo log事务提交后才写入binlog。

会不会有binlog记录了XID envent后redo log没有记录或者相反的情况出现呢？
此图很详细的解答了问题：

4、relay log 中继日志

上面的主从复制图可以参考。relay log是复制过程中产生的日志，很多方面都跟binary log差不多，区别是: relay log是从库服务器I/O线程将主库服务器的二进制日志读取过来记录到从库服务器本地文件，然后从库的SQL线程会读取relay-log日志的内容并应用到从库服务器上。还有一些参数就不记了，咱也不是DBA，如果内卷到中继日志再补上。

5、err log 错误日志

默认情况下，错误日志是开启的，且无法被禁止。默认情况下，错误日志是存储在数据库的数据文件目录中，名称为hostname.err，其中，hostname为服务器主机名。
错误日志记录的不仅是错误信息，如mysql是如何去初始化存储引擎的过程记录，mysql调度启动一个计划任务时，在从服务器上启动从服务器进程时，当然主要记录的还是各种错误信息。

4、锁和mvcc

锁和mvcc保证了事务的隔离机制。怎么理解呢？你要操作数据，mysql系统为了防止脏读不可重复度或者幻读需要加各种锁来满足特定的隔离级别。
先说锁是如何归类的：
粒度分为：表锁，页锁，行锁；
兼容性分为：共享锁，排它锁；
表级锁：表级锁，元数据锁，意向锁，自增锁；
并发控制机制：乐观锁，悲观锁，mvcc；
InnoDB行锁算法：记录锁，间隙锁，临键锁，插入意向锁；
InnoDB基本锁：共享锁，排它锁；
InnoDB意向锁：意向共享锁，意向排它锁，插入意向锁；

MyISAM的是表级锁（table-level locking），有表共享读锁 （Table Read Lock）和表独占写锁 （Table Write Lock），读锁会阻塞写，但是不会阻塞读，写锁则会阻塞读和写，表级锁不会产生死锁，粒度大，冲突高，不支持高并发。

SHOW STATUS LIKE 'Table%'

俩参数可以查看表锁的争用情况。
表级锁是MySQL SQL layer层实现。

InnoDB的锁是行锁搭配表锁其实现了两种行锁–共享锁（S）和排它锁（x），为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks）表锁–意向共享锁（IS）和 意向排他锁（IX）。行级锁（row-level locking）开销大，加锁慢，会出现死锁。
行级锁是基于InnoDB索引实现。

下面我从表锁和行锁细谈，页锁是另一个数据库引擎的叫BDB，我也是头次听说不谈了。

表级锁（table-level locking）

读写表锁，意向锁，元数据锁，自增锁。

1、读写表锁：
MyISAM的表锁也就是那俩了，默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。同时，一些需要长时间运行的查询操作，也会使写线程“饿死” ，应用中应尽量避免出现长时间运行的查询操作（在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行）。MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，过程是自动的，其会一次性获取全部锁，所以不会出现死锁问题。

2、意向锁（Intention Locks）：
意向锁是一种不与行级锁冲突表级锁，其由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。意向锁分为两种：
1.意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;

2.意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

其实很简单的概念，事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。
附上更详细的流程：
事务 A 先获取了某一行的排他锁，并未提交：
SELECT * FROM users WHERE id = 6 FOR UPDATE;
事务 A 获取了 users 表上的意向排他锁。
事务 A 获取了 id 为 6 的数据行上的排他锁。

之后事务 B 想要获取 users 表的共享锁：
LOCK TABLES users READ;
事务 B 检测到事务 A 持有 users 表的意向排他锁。
事务 B 对 users 表的加锁请求被阻塞（排斥）。

最后事务 C 也想获取 users 表中某一行的排他锁：
SELECT * FROM users WHERE id = 5 FOR UPDATE;
总结说：
意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

3、元数据锁（metadata lock）
为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁（metadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。对于引入MDL，其主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。
如例子：

若没有MDL锁的保护，则事务2可以直接执行DDL操作，并且导致事务1出错，5.1版本即是如此。5.5版本加入MDL锁就在于保护这种情况的发生，由于事务1开启了查询，那么获得了MDL锁，锁的模式为SHARED_READ，事务2要执行DDL，则需获得EXCLUSIVE锁，两者互斥，所以事务2需要等待。
支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响
这个深入的研究是秃头DBA的事了，知道概念已经够了。

4、自增锁(AUTO-INC Locks)
AUTO_INC Lock是一种特殊的表锁，它在完成对自增长值插入的SQL语句后立即释放，所以性能会比事务完成后释放锁要高。由于是表级别的锁，所以在并发环境下其依然存在性能问题。
定义：在InnoDB中，每个含有自增列的表都有一个自增长计数器。当对含有自增长计数器的表进行插入时，首先会执行select max(auto_inc_col) from t for update来得到计数器的值，然后再将这个值加1赋予自增长列。我们将这种方式称之为AUTO_INC Lock。
从MySQL 5.1.22开始，InnoDB中提供了一种轻量级互斥量的自增长实现机制，同时InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，进而提高自增长值插入的性能。

行级锁（row-level locking）

行锁一般指InnoDB行锁，有记录锁（Record Locks），间隙锁（Gap Locks），临键锁（Next-Key Locks），插入意向锁（Insert Intention Locks）。

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。

按照行锁的范围是记录锁（Record Locks），间隙锁（Gap Locks），临键锁（Next-Key Locks）三种
记录锁（Record Locks）：仅锁住索引记录的一行，在单条索引记录上加锁，锁住的是该行的索引记录，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。当一条sql没有走任何索引时，那么将会在每一条聚簇索引后面加X锁，这个类似表锁，但原理上和表锁不同的。其他事务只能读被锁的记录，不能insert、update、delete。
举例：

-- id 列为主键列或唯一索引列
SELECT * FROM table WHERE id = 1 FOR UPDATE;

对于这条sql，id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

间隙锁（Gap Locks）：顾名思义锁的是索引的间隙不包括双端点的开区间。在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。如在1、2、3中，间隙锁的可能值有 (∞, 1)，(1, 2)，(2, ∞)，间隙锁可用于防止幻读，保证索引间的不会被插入数据。

SELECT * FROM table WHERE id BETWEN 1 AND 5 FOR UPDATE;

上边这条sql会锁住（1，5）区间内的记录行，所有id 为 2、3、4的数据行的插入会被阻塞，但是 1 和 5 两条记录行并不会被锁住。

临键锁（Next-Key Locks）：这是上面两种锁的组合，区间范围来说的话为左开右闭，默认情况下，innodb使用next-key locks来锁定记录。但当查询的索引含有唯一索引，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。具体来说如果唯一索引(Unique)精确匹配且记录存在则降级为记录锁，如果唯一索引精确匹配但是记录不存在则降级为间隙锁，如果是唯一索引范围匹配，则是记录锁配合间隙锁左开右闭。临键锁主要是为了避免幻读。如果把事务的隔离级别降级为RC，临键锁则会失效。
非唯一索引列，行与行之间是默认有临键锁的。在根据非唯一索引 对记录行进行 UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作时，InnoDB 会获取该记录行的临键锁 ，并同时获取该记录行下一个区间的间隙锁。

插入意向锁（Insert Intention Locks）：插入意向锁是一种Gap锁，不是意向锁，在insert操作时产生。当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待（允许并发insert）。如果在2和 8 记录间，有两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的（排他）锁前，都会获取（2，8）之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突（阻塞等待），插入意向锁不会冲突任何锁。InnoDB在Repeatable-Read 的事务隔离级别下，使用插入意向锁来控制和解决并发插入。

关于这四种锁的范围再进一步梳理一下：

聚集索引上：记录锁

select * from user where id=10 for update

唯一索引(非聚集索引)等值查询:
聚集索引上：记录锁
辅助索引(唯一索引)上：记录锁

select * from t where id = 10  for update; # id是唯一索引列

如果有并发的另外一个SQL，是直接通过主键索引id=30来更新，会先在聚集索引中请求加锁。如果只在辅助索引中加锁的话，两个并发事务之间是互相感知不到的。所以聚集索引也要加锁。

非唯一索引等值查询：
聚集索引：记录锁
辅助索引：临键锁（记录锁 + 间隙锁）

select * from user where name=‘b’ for update

无索引查询
(全表)聚集索引：临键锁（记录锁 + 间隙锁）
即，对全表每一条记录加记录锁，每一个间隙加间隙锁

记录不存在
相应索引：间隙锁
要保证没有其他人可以插入，所以锁住间隙

对于update和delete语句，会隐式加锁。

普通insert
(插入区间)聚集索引：插入意向锁
(插入记录)聚集索引：记录锁

先select后insert
(插入区间)聚集索引：插入意向锁
(插入记录)聚集索引：记录锁
(select的源表)聚集索引：共享的临键锁。这是为了防止主从同步出问题。

乐观锁和悲观锁

悲观锁（Pessimistic Concurrency Control）PCC：就是很悲观的认为我去修改某条记录的时候肯定有别人也在修改。所以通过数据库加锁的方式防止有人抢占资源。
乐观锁(Optimistic Locking)OCC：乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。主要作用于高并发场景，比如超卖问题。常见于应用内设置，通常通过版本号时间戳或者库存数量进行控制，Java 中java.util.concurrent.atomic包下面的原子变量使用了乐观锁的一种 CAS 实现方式。

死锁（下文是书里的内容照抄）

死锁产生：
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁：
数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复：
死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：
发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能：
死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

InnoDB避免死锁：
为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT … FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
改变事务隔离级别
如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

两段锁协议（Two-Phase Locking――2PL）

数据库的两段锁协议是指所有事务必须分两个阶段对数据项进行加锁和解锁。扩展阶段加锁，收缩阶段解锁，引入2PL是为了保证事务的隔离性，保证并发调度的准确性，多个事务在并发的情况下依然是串行的。扩展阶段的获取锁并不是获取所有的锁因此会产生死锁问题。
封锁定理:
如果事务是良构的且是两阶段的，那么任何一个合法的调度都是隔离的；
2PL和2PC：
2PL,两阶段加锁协议:主要用于单机事务中的一致性与隔离性。主要是在MySql(仅限innodb)中使用的。
2PC，两阶段提交协议，用于分布式事务。
S2PL：
在实际情况下，SQL是千变万化、条数不定的,数据库很难在事务中判定什么是加锁阶段，什么是解锁阶段。于是引入了S2PL(Strict-2PL),即在事务中只有提交(commit)或者回滚(rollback)时才是解锁阶段，其余时间为加锁阶段。

MVCC(Mutil-Version Concurrency Control)

汉译就是多版本并发控制，即在执行select操作时解决在读已提交和可重复读两种隔离级别下读写的并发问题。不仅是MySQL, 包括Oracle,PostgreSQL等其他数据库系统也都实现了MVCC, 但各自的实现机制不尽相同。可认为MVCC是行锁的变种。
串行化下可以解决所有问题，但是都希望数据库可以多路更快的处理数据，因此除了银行可能会用到串行化级别，一般连RR级别都很难见到（MySQL的默认隔离级别），业界提倡 Weak Isolation。在安全的前提下并发且高效的进行事务一般通过两种方式实现：
1、LBCC（Lock Based Concurrency Control）
2、MVCC（Multi-Version Concurrency Control）

MVCC的实现方式有多种, 典型的有乐观(optimistic)并发控制 和 悲观(pessimistic)并发控制：
MVCC + 悲观锁（数据库层面的各种锁）
MVCC解决读写冲突，悲观锁解决写写冲突
MVCC + 乐观锁（程序应用层面的锁）
MVCC解决读写冲突，乐观锁解决写写冲突
MVCC + Next-Key Locks（gap锁和record锁搭配的锁技术）：解决了在RR级别下幻读的问题

罗列前提知识点：

当前读（current read）：如select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作为当前读，就是加锁来获取最新的信息。
快照读(snapshot read)：简单的select操作(不包括 select … lock in share mode, select … for update)为快照读，通过mvcc实现。
总的来说加了for update或者share mode的select操作是通过next-key解决幻读问题，可以获取实时数据，而纯粹的select通过mvcc来解决幻读问题，所获取的实时数据是依赖版本链进行相关判定的。

MVCC的实现总体上依赖三种机制，隐藏字段，视图，undo log。

隐式字段：
一般认为是加了三个隐藏字段实现的。
隐式字段的意思是你所看到的每行记录除了我们自定义的字段，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段。
DB_TRX_ID
6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
DB_ROLL_PTR
7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
DB_ROW_ID
6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

还有别的等如：
DATA_TRX_ID
记录最近更新这条行记录的事务ID，大小为6个字节
DATA_ROLL_PTR
表示指向该行回滚段（rollback segment）的指针，大小为7 个字节，InnoDB便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在undo中都通过链表的形式组织。

视图（read view）：这个视图并不是那个用查询语句定义的虚拟表，这个是InnoDB的一致性视图（consistent read view）。在事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID。
大概结构如下：

可以看出基本由undo log维系，也就是版本链组成。
Read View主要是用来做可见性判断的, 判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。判断依赖可见性算法。

undo log：上面有讲过，这里再加工一下，undo log根据行为不同分为Insert和Update两种。insert是加排它锁，操作记录仅对当前事务本身可见，因此insert undo log可以在事务提交后直接删除而不需要purge（purge主要是把标记为delete的数据删除另外也会批量回收undo pages）。而update或delete（另一种意义上的update）操作会对已经存在的记录产生影响，因此不能在事务提交时就进行改删，而是放在history list上，等待purge线程进行最后的删除操作。

RR下ReadView的生成：

在RR隔离级别下，每个事务touch first read时（本质上就是执行第一个 SELECT语句时，后续所有的SELECT都是复用这个ReadView，其它update, delete, insert语句和一致性读snapshot的建立没有关系），会将当前系统中的所有的活跃事务拷贝到一个列表生成ReadView 。
对比一下两图差别：
1、事务之前生成readview：

2、事务之后生成readview：

RC下ReadView的生成：

在RC隔离级别下，每个SELECT语句开始时，都会重新将当前系统中的所有的活跃事务拷贝到一个列表生成ReadView。二者的区别就在于生成ReadView的时间点不同，一个是事务之后第一个SELECT语句开始、一个是事务中每条SELECT语句开始。

ReadView中是当前活跃的事务ID列表，称之为m_ids，其中最小值为up_limit_id，最大值为low_limit_id，事务ID是事务开启时InnoDB分配的，其大小决定了事务开启的先后顺序，因此我们可以通过ID的大小关系来决定版本记录的可见性，具体判断流程如下：

1、如果被访问版本的trx_id小于m_ids中的最小值up_limit_id，说明生成该版本的事务在ReadView生成前就已经提交了，所以该版本可以被当前事务访问。

2、如果被访问版本的trx_id大于m_ids列表中的最大值low_limit_id，说明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。需要根据Undo Log链找到前一个版本，然后根据该版本的DB_TRX_ID重新判断可见性。

3、如果被访问版本的trx_id属性值在m_ids列表中最大值和最小值之间（包含），那就需要判断一下trx_id的值是不是在m_ids列表中。如果在，说明创建ReadView时生成该版本所属事务还是活跃的，因此该版本不可以被访问，需要查找Undo Log链得到上一个版本，然后根据该版本的DB_TRX_ID再从头计算一次可见性；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

4、此时经过一系列判断我们已经得到了这条记录相对ReadView来说的可见结果。此时，如果这条记录的delete_flag为true，说明这条记录已被删除，不返回。否则说明此记录可以安全返回给客户端。
举例：

当事务 A 未提交时，事务 B 进行查询，假设事务 B 的事务 ID 为 300 ，此时生成 ReadView 的 m_ids 为 [200，300]，而最新版本的 trx_id 为 200 ，处于 m_ids 中，则该版本记录不可被访问，查询版本链得到上一条记录的 trx_id 为 100 ，小于 m_ids 的最小值 200 ，因此可以被访问，此时事务 B 就查询到值 10 而非 20 。
待事务 A 提交之后，事务 B 进行查询，此时生成的 ReadView 的 m_ids 为 [300]，而最新的版本记录中 trx_id 为 200 ，小于 m_ids 的最小值 300 ，因此可以被访问到，此时事务 B 就查询到 20 。
在 RR 下生成 ReadView 是在事务开始时，m_ids 为 [200,300]，后面不发生变化，因此即使事务 A 提交了， trx_id 为 200 的记录依旧处于 m_ids 中，不能被访问，只能访问版本链中的记录 10 。
RC 、 RR 两种隔离级别的事务在执行普通的读操作时，通过访问版本链的方法，使得事务间的读写操作得以并发执行，从而提升系统性能。 RC 、 RR 这两个隔离级别的一个很大不同就是生成 ReadView 的时间点不同， RC 在每一次 SELECT 语句前都会生成一个 ReadView ，事务期间会更新，因此在其他事务提交前后所得到的 m_ids 列表可能发生变化，使得先前不可见的版本后续又突然可见了。而 RR 只在事务的第一个 SELECT 语句时生成一个 ReadView ，事务操作期间不更新。

5、事务

事务四大特性ACID，事务的四种隔离机制，面对的四个问题Lost Update，Dirty reads，Non-repeatable reads，Phantom read。
事务的复杂性决定了表设计，系统优化，sql优化。

事务有本地事务和分布式事务的区别，主要谈分布式事务。

暂时就到这里吧不再细谈。

总结一下：写了三天，每天三个小时，真的很多。关于mysql我还有系统优化没有写其时根据上面的InnoDB引擎以及MySQL构造也能知道要优化哪个方面。还有分库分表的操作，就两个中间件，都说mycat搞的跟邪教似的，大家用的最多的还是shardingjdbc。

Q.E.D.