MySQL
记录MySQL从基础架构、常用sql、索引、优化、引擎、事务、锁、主从架构等原理
Last updated
记录MySQL从基础架构、常用sql、索引、优化、引擎、事务、锁、主从架构等原理
Last updated
[TOC]
连接器：负责跟客户端建立连接、获取权限、维持和管理连接。登录进去后修改权限，默认是将在下一次登录后生效
查询缓存：MySQL接收到查询请求后会先查询缓存，key是查询语句，value是查询结果，之后经过执行器的权限判断再返回，如果查不到则往后走。不建议使用，因为若有更新操作，会删除对应表的缓存，可能导致缓存命中低，可以设置query_cache_type=demand，默认不使用缓存，需要在查询时显示指定。MySQL8.0删除此功能
分析器：对SQL语句进行分析，词法分析判断各个字符串代表的含义（包括列是否存在），语法分析判断SQL的语法是否正确，这一层操作之后，MySQL就知道你要做什么了
优化器：决定是否要使用索引，使用哪个索引，决定表的连接顺序
执行器：先判断是否有对该表的操作权限，之后判断要使用哪个引擎提供的接口
引擎：对数据进行具体操作的执行者，事务和索引都是在这层做的，但具体需要引擎支持，例如MyISAM不支持事务，InnoDB支持
关于物理日志和逻辑日志：物理日志记录每一个page具体存储的值，在这个数据页上做了什么修改，比如redo log；而逻辑日志记录每一个page中数据的变动过程，比如undo log、bin log、relay log；
比如一个page页中一个数据从 1 改到 2 ，再改到 3，物理日志记录最后一个值是 3 ，逻辑日志记录 1 -> 2, 2->3 的过程。
undo log回滚日志：InnoDB独有，逻辑日志，主要用于事务失败时的回滚，以及MVCC中的版本数据查看。当事务被提交后，并不会马上被删除，而是放到待清理链中，等到没有事务用到该版本信息时才可以清理。
undo log和数据页的刷盘策略是一样的，都需要通过redo log保证持久化，Buffer Pool中也有undo 页，对undo页的修改会记录到redo log中，跟着redo log刷盘一起刷盘；
同时，如果在内存中修改undo页，需要更新记录对应的redo log。
redo log重做日志：InnoDB独有，物理日志，记录这个页做了什么改动，本质上记录了对某个表空间的某个数据页的某个偏移量修改了哪几个字节的值，具体修改的值是什么，一条redo log也就几个字节到十几个字节，格式是 日志类型，表空间ID，数据页号，数据页偏移量，具体修改的数据。
使用二阶段提交保证两份日志逻辑一致。当有日志要写入时，先写到redo log buffer后状态是prepare，开始写bin log cache，bin log 写完后，事务提交，redo log 改为commit状态，redo log写完，此时事务就算完成；这里描述的写redo log和bin log都只写在了缓冲区，何时写进磁盘，是根据innodb_flush_log_at_trx_commit和sync_binlog配置决定，用于实现数据持久化，以及宕机恢复数据。
之所以要使用二阶段提交，是为了保证redo log和bin log的数据一致性，对于处于 prepare 阶段的 redo log，即可以提交事务，也可以回滚事务，这取决于是否能在 binlog 中查找到与 redo log 相同的 XID，如果有就提交事务，如果没有就回滚事务，这样就可以保证 redo log 和 binlog 这两份日志的一致性。
二阶段提交虽然保证了redo log和bin log的数据一致性，但是会提升磁盘IO的次数，每次事务提交都要刷两次盘，一次redo log，一次bin log；此外，为了保证在多事务场景下，两个日志提交顺序一致，还需要锁来保证事务的顺序，可能导致锁竞争激烈。
后面引入了组提交机制解决这两个问题，组提交将commit阶段拆分成三个阶段，并使用三个队列，当有多个事务提交时，会将多个bin log 刷盘操作合并成一个，从而减少磁盘IO，该机制只针对commit阶段。
WAL机制：执行事务时，将表数据写入内存和日志(redo log)，事务就完成了，此时表数据可能还没写入磁盘，InnoDB会在合适的时机将内存里的数据（Buffer Pool里的脏页）刷入磁盘。WAL机制主要是解决表数据写入时，CPU和磁盘速度的差异问题，也因为是先把数据写进内存，再写入磁盘，才需要redo log + 二阶段提交来解决崩溃数据丢失的问题。
在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。
bin log归档日志：属于server层的日志，逻辑日志，记录所有逻辑操作，追加写入，不会覆盖以前的日志。bin log有两种模式：statement 格式的话是记sql语句； row格式会记录行的内容，一般使用row，记录行变化前和变化后的数据，缺点是日志变大。用于主从复制和数据备份恢复；
redo log prepare、commit 的XID与bin log的XID实现关联，通过XID的关联，就能知道两份日志是否完整，从而实现crash-safe。
执行一条更新语句的执行流程：
执行器负责具体执行， 调用存储引擎的接口，通过主键索引找到那一行的记录，如果这条记录在Buffer Pool中，返回给执行器，如果不在Buffer Pool中，就从磁盘读入到Buffer Pool中，返回给执行器；
执行器拿到记录后，判断更新前后是否一致，如果一致，就不进行后续流程直接返回了；如果不一致，就把更新前和更新后的数据传给InnoDB，由存储引擎执行真正的更新操作；
开启事务，数据修改前，先把老版本的数据写入undo log，undo log也会写入 Buffer Pool 的undo页中，当在内存中修改该 undo 页，需要更新记录对应的redo log；
InnoDB开始更新记录，更新Buffer Pool里该行的数据，同时标记成脏页，然后把新数据先写进redo log buffer，状态是prepare，然后是写入 bin log cache，事务提交，redo log更新为commit状态，整个更新操作就算完成。数据写入Buffer Pool时，一条记录就算更新完成了，事务提交，一条更新语句才算执行完成；（二阶段提交）
redo log 和 bin log 都是先写进对应的cache，再刷盘，所以，写入到对应的log文件，并不意味着写入了磁盘，此时可能只写入到了操作系统内核的Page Cache，操作系统再在合适的时机写入磁盘。
当innodb_flush_log_at_trx_commit=1和sync_binlog=1时，事务完整提交前，需要刷两次盘，一次 redo log，一次 bin log。
innodb_flush_log_at_trx_commit在不同值的作用：
=1：表示每次事务完成后，立即调一次 fsync 方法将 redo log buffer 中的数据持久化到Page Cache，再调用一次flush 方法持久化到硬盘。（建议）
=0：表示每秒调用 fsync 方法将 redo log buffer 中的数据持久化到Page Cache，再调用 flush 刷盘，机器停电或崩溃可能会丢失一秒的数据；
=2：表示每次事务完成后，立即调一次 fsync 方法将 redo log buffer 中的数据写到Page Cache，然后每秒调一次 flush 方法持久化到硬盘，机器停电或操作系统崩溃可能会丢失一秒的数据。
redo log buffer默认是16MB，redo log 数据内容是记在 redo log block 里的，写满一个就存到redo log buffer里，然后写下一个，直到 buffer 满了，此时会强制刷盘。
redo log 是固定大小的，比如有一组4个文件组成的“环形队列”，环形写入，一个文件写完就写下一个，4个轮转，作用是redo log刷盘完成之后这部分内存就能重新利用；首位指针表示当前记录的位置和当前擦除位置。当前擦除位置是一个叫 LSN（log sequence number）的检查点，表示在LSN之前的数据（缓冲池里的数据页、索引页）都已经持久化到磁盘了，redo log文件里在LSN检查点之前的内容才允许被覆盖，擦除或覆盖之前一定会刷到磁盘。
redo log buffer 除此之外还有几种场景会强制写磁盘：redo log buffer满了、正常关闭服务器、redo log环形写入到达check point时。
sync_binlog 在不同值的作用：
=1：表示每次事务提交后bin log都会持久化到磁盘；(建议)
=0：表示由系统判断何时刷盘；
=N，表示每次事务提交都会写入到Page Cahce，但是会累计提交 N 个事务后才把bin log的数据持久化到磁盘，一般设置范围是100~1000，对应的风险是，如果机器宕机，会丢失最近 N 个事务的bin log日志；
InnoDB在空闲的时候才真正的将Buffer Pool中已更新的表数据刷新到对应的page页，写入磁盘中，此时数据才真正落盘。（WAL机制）
因为redo log格式固定，可以通过redo log buffer实现顺序写入磁盘，顺序IO写入速度快，而将表数据写入磁盘，需要更新redo log日志中的内容到表数据对应的page页，涉及到分页或合并等操作，属于随机IO写入，比较费时，所以才先写redo log日志，再把表数据写入磁盘。虽然WAL机制也实现了顺序写，但是因为它是先写在内存，存在丢失风险，才需要redo log一起配合；
关于奔溃恢复：
可以只使用redo log来实现崩溃恢复，保证恢复后不会出现主从不一致的情况，由MySQL内部实现，但无法只使用bin log，原因是 InnoDB使用WAL机制，如果此时数据库崩溃，要依赖日志来恢复数据页，但是bin log并没有记录数据页的更新细节，而redo log因为环形写入的问题，无法对所有记录进行归档，仅仅只能实现崩溃恢复；
崩溃重启后，扫描redo log文件，如果碰到prepare状态的redo log，就会拿着它的 XID，去bin log里查询，如果bin log里能找到，就提交事务，如果找不到，就回滚事务，从而保证 redo log 和 binlog 这两份日志的一致性；
只有当innodb_flush_log_at_trx_commit=1和sync_binlog=1，数据库才具备crash-safe的能力。
备份时间的长短会影响日志文件的大小，文件的完整性，从而影响到恢复速度和恢复效果；
参考：MySQL中的日志机制
update 表名 set 列名1=值1, 列名2=值2... where 列名3=值3
insert into 表名 (列名1, 列名2, ...) values (值1, 值2, ...)
在count(*)不带条件在MyISAM里查询比较快，因为MyISAM会存储总条数，不带条件查询的时候直接用就行，而InnoDB带了事务，支持MVCC，因此每次count(*)时都会扫表
以下归纳基于InnoDB，count会返回满足条件的结果集的总行数，它会使用存储引擎进行全表扫描获取结果，比如count(1)会直接返回1，count(主键)会获取主键，返回给server层，由server层进行计数，因此按效率排序是：count(字段) < count(主键id) < count(1) ≈ count(*)
count（列）会计算列或这列的组合不为空的计数；
count(*) 跟 count(1) 的结果一样，都包括对NULL的统计，而count([列名]) 是不包括NULL的统计；
对于计数，也可以通过创建列为表名、total的表进行计数，利用事务能力，一般是先insert再update，理由是并发进行total值的更新时，是会上行锁的，如果先update total值可能会导致事务处理时间过长；
对于大表count，可以使用近似值，比如使用 show table status 或 explain select语句来查询近似值；
having一般需要搭配 group by 使用，在group by之后，order by之前
having一般配合聚合函数使用，而where后面不能加聚合函数
where是对表的字段进行条件过滤，having是对select出来的字段进行条件过滤
可以想成 查询一些字段，先通过where进行一次过滤，group by进行一次分组，having对分组后的结果再过滤一次，having后的字段必须出现在select中
常见一点的sql，比如有如下表，这里为了方便理解以中文的形式表示字段
查询 除了D网站外 各个网站的点击数 大于100 的 网站名称 和 点击数 并 降序 表示
全字段排序：查询条件是索引，但是order by 条件不是，会先遍历索引，再回表取值，每次取到数据就丢sort_buffer，完了之后在sort_buffer里根据order by条件排序 （利用sort_buffer + 临时表），会根据数据量采用内存排序或者外部归并排序；
rowId排序：如果select的字段太多，超过设置的最大长度max_length_for_sort_data，就会只取主键和order by的条件丢进去sort_buffer里进行排序，最后再回表根据主键取出其他select的字段；
如果order by的条件正好是索引顺序，就不需要使用sort_buffer进行排序了，直接使用索引顺序即可；
order by rand()，随机排序，使用内存临时表，使用rowId + 随机数进行排序；
不带查询条件进行order by，就算order by条件是索引，是不一定会走索引进行排序，原因是如果MySQL优化器判断走索引后要去回表数量太大，就不会走；
带limit的order by，mysql会采用堆排；
默认的临时内存表是16M，由tmp_table_size设置；
group by一般是使用在select + 聚合函数的情况，如果select 没有聚合函数，语义（即按照group by后的字段进行分组，相同的分组只返回一行）与distinct是类似的，性能也一样
SQL语句的执行顺序是 from > where > group by ＞ having > order by > limit，join 和on和and的组合属于from范围
group by实际上会进行排序操作的，先根据group by后的字段进行排序，再聚合，最后select出需要的字段返回，这个过程会用到内存临时表(可能会退化为磁盘临时表)
无法这样使用select * from table group by column
select distinct 列1, 列2 from 【表名】
distinct 接多个列，会对多个列的不同组合都列出来
无法这样使用select 列1, distinct 列2 from 【表名】
由于有时优化器会选择错误的驱动表，使用 straight_join 则可以让MySQL默认使用左边的表作为驱动表；
NLJ算法进行join操作：当有A、B两表，是一个1对1的关系，逻辑外键在B表带有索引，使用join进行关联查询，小表驱动大表(这样扫描的行数较少)，每扫一行B，通过外键索引，在另一个表A找对应的行数据，合并，返回，总共执行1条语句，扫描len(A) + len(B) 行。但是如果不使用join查，而是先查出B表所有数据，再根据B的id查回A的数据，虽然扫描的行数一样，但是却执行了len(B) + 1条SQL语句，显然是使用了join的方式性能强，前提是B上带了索引。
BNL算法进行join操作：如果B上关联键没有使用索引，则算法是这样的，先对B表进行全表扫描存进内存，再对A表进行全表扫描，每扫一行A，就在内存（join buffer）里的B进行匹配，返回；如果内存太小，则分块对表B进行加载，全表扫描A，匹配后将结果集返回，清空内存，再分块加载剩下的B，再次全表扫描A，匹配返回，循环处理，直至表B加载完成。分块加载会导致重复扫描表A。
对BNL的优化，一种是在业务端查回两张表的数据，在通过hash匹配组合，另一种是查join查询前，先创建临时表，创建索引，查询被驱动表的数据，插入临时表中，与临时表进行join操作，将BNL转为NLJ；
驱动表走全表扫描，被驱动表最好走索引扫描，使用NLJ算法，如果被驱动表是全表扫描，则使用(S)BNL算法；即被驱动表有索引，使用NLJ，被驱动表没索引，使用BNL，都是为了扫描更少的行数；
select * from a join b on a.id=b.id，A是驱动表，B是被驱动表；如果是 select * from a, b on a.id=b.id，则驱动表和被驱动表由MySQL自己决定。
小表驱动，小表指的是行数相对少，或者select时表的数据量相对少的表；
所以，NLJ算法是从表B每查一行就根据索引去表A匹配一行，MRR优化就是在NLJ基础上，回表的时候根据有序的索引一批一批查，这样就快一点；而BNL算法是同时去查表A和表B的所有行，再进行匹配关联；
left join，使用left join时，左边的表不一定是驱动表，如果要使用left join语义，不能把被驱动表的字段放在where条件里做等值和不等值判断，必须放在on里，原因是MySQL会先用on作为条件进行过滤，完了才使用where进行过滤，放在on里能让过滤出来的条数少，要注意两者表达的语义还是有些不同的；
delete和truncate只删除表数据不删除表结构
速度上 drop > truncate > delete
drop和truncate是ddl语句，操作是立即生效，原数据不放到rollback segment中，不能回滚，而delete是dml语句，该操作会放在rollback segment中，事务提交后才生效
不需要表时使用drop，删除某些行时使用delete，保留表但清空表的数据时使用truncate
limt M [offset N]，从第N条记录开始，返回M条记录，比如limit 5, 10，表示返回6-15行
当limit后面只跟一个参数时，表示返回最大的记录行数目，比如limit 5，表示只返回前5行
初始偏移量是0
curdate()函数：得到今天的日期，格式： 年-月-日
now()函数：得到今天的日期和时间，格式：年-月-日 时:分:秒
两个datetime类型的字段相减，得到的单位跟日期的格式有关，如果格式有到秒，那减出来就是多少秒，如果格式只到日，那减出来就是多少日
UNIX_TIMESTAMP(datetime类型的字段) 将datetime类型的字段转换为时间戳，要注意时间戳是以1970 年 1 月 1 日开始算的
DATE_SUB(date, INTERVAL expr type) 函数：从日期减去指定的时间间隔
date_format(date字段, ‘%Y%m%d %H:%i:%s') 函数：日期格式化函数，
可以利用这些来查询最近多少天的数据如
查询 近一小时的数据 where date字段 >= DATE_SUB(now(), INTERVAL 1 Hour) and date字段 < now()
查询 昨天的数据 where date字段 >= DATE_SUB(CURDATE(), INTERVAL 1 Day) and date字段 < CURDATE()
查询 近7天的数据 where date字段 >= DATE_SUB(CURDATE(), INTERVAL 7 Day)
查询 本月的数据 where date_format(date字段, ‘%Y%m') = date_format(curdate() , ‘%Y%m')
查询 上个月的数据 where period_diff(date_format(now() , ‘%Y%m') , date_format(date字段, ‘%Y%m')) =1
查询 今年的数据 where YEAR(date字段)=YEAR(NOW())
查询 去年的数据 where YEAR(date字段)=year(date_sub(now(),interval 1 year))
查询本季和上一季的跟 查年的差不多 ，把 YEAR函数 换成 QUARTER函数 即可
char是固定长度，varchar是可变长度，varchar(50)和varchar(200)存储字符串 “hello” 所占空间一样，但后者在排序时会消耗更多内存，因为order by采用fixed_length计算字段长度。
MySQL中除了TEXT、BLOBs外，其他所有列占用的字节长度加起来不能超过65535个字节，即一行的长度不会超过65535字节，64kb；公式：如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535字节。
varchar(n)中的n代表最多能存储的字符数量，并不是字节大小，所以还要看数据库的字符集用的是上面编码
有符号的整型范围是-21474836482147483647；无符号的整型范围是04294967295；
int(20)表示能显示的宽度是20，比如id的值是10，那MySQL就会在前面加0，自动补全到20位，仍然占4个字节存储，存储范围也不变。
保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间
与时区无关
和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年
它和时区有关，每个时间戳在不同时区所代表的具体时间不同
默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间
MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，
提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳
NULL跟任何值执行等值判断和不等值判断的结果都是NULL
记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次I/O操作）只能处理一行数据，效率会非常低。因此InnoDB将数据划分为若干个页面，不论读一行，还是读多行，都是将这些行所在的页进行加载。即 数据库管理存储空间的基本单位是页（Page）。
B+树叶子节点，双向链表，叶子节点，以及数据页的格式，User Records从Free Space里转换而来，当Free Space用完，就会创建下一页；
索引页，即非叶子节点也是类似上面的结构，只是行记录那里存的是指向下一层页的指针，而不是具体的行数据。
一个页中可以存储多个行记录（Row），同时在数据库中，还存在着区（Extent）、段（Segment）和表空间（Tablespace）。大小排序与包含关系：行 -> 页 -> 区 -> 段 -> 表空间。
页（Page）是基本的存储单位，按类型分可分为 数据页（B+树节点，链表结构，逻辑上连续）、系统页、Undo页和事务数据页等，每个页默认大小是16KB。意味着数据块每次读写都是以16KB为单位。
区（Extent）是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB=1MB。传统链表每个相邻节点的物理位置并不一定是连续的，MySQL为了能够顺序IO，就通过区来实现，当表中数据量大的时候，为某个索引分配空间不再以页为单位分配，而是按照区，每个区大小为1MB，对于16KB的页来说，连续的64个页会被划分为一个区，使得链表中相邻的页的物理位置页相邻，即B+树叶子节点链表相邻的页的物理位置相邻，能够顺序IO；
段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。索引段，存放B+树的非叶子节点的区集合；数据段，存放B+树叶子节点的区集合；回滚段，存放回滚数据的区集合；
表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。对应的文件是 [表名].ibd 的文件，在这个文件里，会被分成很多数据页，每一份是16K。
记录的额外信息
变长字段长度列表：存储数据的时候，也要把数据占用的大小存起来，存到「变长字段长度列表」里面，读取数据的时候才能根据这个「变长字段长度列表」去读取对应长度的数据，按照列的顺序逆序将列的长度存储到这个字段里；字段如果是NULL值不会存在这；
之所以要逆序存放，可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时放到CPU Cache Line里，提高CPU Cache 的命中率；
当数据表的字段没有变长字段时，行格式里就不会记录 变长字段长度列表 了
NULL值列表：至少一个字节，跟列的数量有关，比如如果是9个列，那就是两个字节；该字段会记录该行所有列是否是NULL值，以二进制位按列的顺序逆序排列，二进制位=1，表示NULL，=0表示不为NULL，列的数量不足的，高位补零。
当数据表的字段都定义成NOT NULL时，行格式里就不会记录 NULL值列表 了；
记录头信息：只列举重要的，比如 delete_mask，标识此条记录是否被删除；next_record，指向下一条记录的指针（记录与记录之间是链表，下一条记录的「记录头信息」和「真实数据」之间的位置）；record_type，当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录；
记录的真实数据
row_id：当表没指定主键时，且也没有不包含NULL值得唯一列，就会用到这个作为隐藏主键
trx_id：事务id，表示这个数据由哪个事务产生，用于MVCC
roll_ptr：记录上个版本的指针，用于回滚，MVCC
MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是
16KB，也就是16384字节，而一个 varchar(n) 类型的列最多可以存储65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。
InnoDB使用了Buffer Pool来提高数据库读写性能；MySQL启动时，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页，默认配置下 Buffer Pool 只有 128MB 。
Buffer Pool会缓存索引页、数据页、undo页、插入缓存、自适应哈希索引、锁信息等；
使用三种链表来管理：
Free list链表：关联空闲的页，以便快速找到空闲页；
Flush list链表：关联脏页，以便将脏页刷盘；
LRU List链表：管理脏页和干净页；
作用：
当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
查询一条数据时，会把这个页的数据都加载到Buffer Pool中，因为通过索引只能定位到磁盘中的页，而不能定位到页中的一条数据，将页加载到Buffer Pool后，再通过页里的页目录去定位到具体的行记录；
MySQL加载数据时，会进行预读，但是这些提前被加载进来的数据页，可能并不会被访问到，造成预读失效；
使用LRU算法提升缓存命中率，将LRU链表划分为两个区域，前半段是young区，后半段是old区，比例大概是64：37，预读的页会先加入到old区的头部，当页被真正访问时，才会插入young区的头部，如果预读的页一直没有被访问到，就会从old区移除，这样就不会影响young区的热点数据，解决预读失效导致缓存命中率低的问题；
当一个SQL语句需要扫描大量的数据，在Buffer Pool空间比较有限的情况下，把所有页都替换出去了，导致大量热点数据被淘汰，造成缓存污染，等到这些热点数据被访问时，由于缓存未命中，就会产生大量磁盘IO。
数据加入young区条件增加门槛，只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部，解决 缓存污染的问题；
另外，为了防止young区节点并频繁移动到头部，前1/4被访问不会移动，后3/4被访问才会移动；
当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘，触发脏页刷盘的时机：
redo log日志满时，主动触发脏页刷盘；
Buffer Pool空间不足，此时需要淘汰一部分数据页 ，如果淘汰的是脏页，就要刷盘；
MySQL 空闲时，定期适量将脏页刷盘；
MySQL正常关闭，会把所有脏页刷盘；
聚簇索引：InnoDB中的主键索引，每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据。一个表只能包含一个聚簇索引，聚簇索引通常提供更快的数据访问速度。
因为表的数据都是存放在聚集索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个，而二级索引可以创建多个。
非聚簇索引：表中行的物理顺序与键值的逻辑顺序不匹配，查到记录对应的主键值 ，再使用主键的值通过聚簇索引查找到需要的数据，这个过程也称为回表；即先通过普通索引找到主键，在通过主键找到数据。
要细分的话可以分为普通索引，唯一索引，组合索引，全文索引这些。
注：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。
稠密索引：每个索引对应一个值
稀疏索引：每个索引对应一个存储块
覆盖索引：要查询的字段只需要去查询索引表就可以
组合索引（联合索引）：最左匹配，建立一个组合索引等于建立多个索引，能达到覆盖索引的目的，效率高；例如有组合索引(a，b，c)，则同时得到了索引（a），(a，b)，（a，b，c）
MySQL5.6 后有个索引下推，当查询条件带a，b的时候，会先查找索引树匹配a，再判断b，然后才回表找数据，从而减少回表次数，如果没有索引下推，MySQL是先查找索引树匹配a，拿到id回表查数据，判断是否匹配b，这样回表次数就太多了。
组合索引之所以是最左匹配跟B+树有关，也是类似Order by的过滤，根据索引依次排列数据的，如Order by a,b,c 则先排a，a相同再排b，b相同再排c
联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引，比如 a > 1 AND b = 2，这种只会用到联合索引的（a）。注意，对于 >=、<=、BETWEEN、like 前缀匹配 的范围查询，并不会停止匹配，比如 a >= 1 AND b = 2，会用到索引（a，b），因为此时在进行扫描时，可以是以 a=1 AND b=2 为条件找到第一条数据，再继续往下匹配的；
自适应哈希索引，由InnoDB自行决定是否建立，自适应哈希索引只保存热点数据，不会保存全表数据，所以数据量不会很大，使用的 InnoDB Buffer Pool进行缓存；相当于 索引的索引，降低对二级索引树的频繁访问，快速定位到叶子节点；无法进行范围查询；
索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间，但主键索引的重建会导致整个表重建，一般可以使用alter table T engine=InnoDB来达到重建主键索引的效果
需要建立的列：经常需要搜索的列、主键列、外键列、排序的列、经常在where后面出现的列
避免进行数据库全表的扫描，大多数情况，只需要扫描较少的索引页和数据页，而不是查询所有数据页。而且对于非聚簇索引，有时不需要访问数据页即可得到数据。
聚簇索引可以避免数据插入操作，集中于表的最后一个数据页面。
在某些情况下，索引可以避免排序操作
加速表与表之间的连接
在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
增，删，改会带来不小性能开销
关于普通索引和唯一索引
在查询上，唯一索引和普通索引的区别是，唯一索引在查找到结果后就不会继续往下查了，但其实性能跟普通索引差别不会很大，但是更新的时候唯一索引由于用不上change buffer机制，更新的性能比较差。
在更新上，InnoDB会先判断更新的数据是否在Buffer Pool，如果在就直接更新，如果不在，就先把更新操作写到change buffer，等之后数据加载到Buffer Pool后，合并这两部分Buffer，更新Buffer Pool里的数据。
change buffer只适用普通索引上的更新操作，因为唯一索引需要先读取所有数据，判断索引是否重复后再插入，如果此时数据没有被读进Buffer Pool，需要磁盘随机IO读取，最终导致更新变慢。
另外，为了保证更新操作的稳定性，实际上在写change buffer的过程中还会把相关操作记录按顺序写进redo log（redo log也会刷盘），才算真正完成更新操作。查询的时候其实可以直接查Buffer Pool里的数据，或者先把磁盘里的数据读到内存，再配合change buffer就能得到更新后的数据了。
B类树都是为了磁盘或其他辅助存储设备而设计的一种数据结构，目的是为了在查找数据的过程中减少磁盘I/O次数，树存储在磁盘中，树的高度等于每次查询数据时磁盘的IO操作次数。
B+树：表的数据为叶子节点，非叶子节点为索引，有两条路径，一条是树，一条是各叶子相连
N叉树的N在MySQL5.6后可以通过page大小来间接控制，叶子节点是数据页(page)，页与页之间组成双向链表
一个数据页(page)可以包含多个行(记录)，行按(记录)照主键顺序，行与行之间组成单向链表；每一个数据页中有一个页目录，方便按照主键查询行；
页目录中通过槽把行(记录)分成不同小组，每个小组内包含多条行(记录)，按照主键搜索页中行(记录)时，使用二分法查找，从槽开始依次往下找；
B+树的插入可能会引起数据页的分裂，删除可能会引起数据页的合并，二者都是比较重的IO消耗，所以比较好的方式是顺序插入数据，这也是我们一般使用自增主键的原因之一；
每个结点就算一个page，数据以Page为单位在内存和磁盘间进行调度，每个Page的大小决定了相应结点的分支数量，每条索引记录会包含一个数据指针，指向一条数据记录所在文件的偏移量；
B-树：也称B树，不限制出度的个数，所有节点为表的数据，只有一条路，从根节点开始
B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，比B树更矮胖，相对IO读写次数就降低了，而且，在没定位到要找的记录时，加载别的数据行进内存并没有什么用，因为只需要索引来判断查找得对不对。
B+树的查询效率更加稳定：B+树深度比较平均，由于非叶子点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，任何关键字的查找必须走一条从根结点到叶子结点的路，即所有关键字查询的路径长度相同，因此每一个数据的查询效率相当。而B树查询的深度比较随机，比较适合定点查询，单次查询效率高。
由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，顺序IO，只需要扫一遍叶子结点即可；但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，带来更多的随机IO。所以B+树更加适合在区间查询的情况。
B树不适合遍历数据，B树比较合适查询单一记录，常用与NoSQL的索引结构，NoSQL一般是Key-value形式的存储，文档性一般是Json存储。
插入和删除时，B树会导致平衡发生改变，节点会上移或下沉，B+树因为存在冗余节点，删除时不会发生复杂的树变形，插入时虽然也可能存在页分裂，但也只涉及到树的一条路径，因此在插入和删除时，B+树效率更高；
AVL树的出度为2，而且AVL树要严格保持平衡，但旋转保持平衡比较耗时，适合用于插入删除次数比较少，但查找多的情况
二叉查找树在查找最大值或最小值的时候，二叉查找树就完全退化成了线性结构了
其他缺点同下面
红黑树出度为2，B+树出度不止2，因此红黑树的高度会比B+树高，查找的次数也多了。（红黑树不是严格的平衡二叉树，旋转次数相对少，高度比平衡二叉树的低些）
B+树在降低磁盘I/O操作数方面占优势
为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，速度会非常快。
操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。
红黑树的特性: （1）每个节点要么是黑色，要么是红色。 （2）根节点是黑色。 （3）每个叶子节点（NIL）是黑色。（注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点） （4）如果一个节点是红色的，则它的子节点必须是黑色的。 （5）从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。
失效的本质是MySQL判断查询时走什么路径所花费的随机I/O和顺序I/O更多，比如需要扫描超过表的30%数据，就有可能失效。
在使用到索引列的情况下
对索引列作运算如 + - * / !
索引类型出错，比如该索引列是字符串，但是写时候没有加``号，字符串和数字比较，MySQL会把字符串转为数字，无法转成数字的字符串都会被转换成0；或者是字段的字符编码格式不同，比如有两张表，它们有外键关系，但是由于一张表是UTF-8另一张表是UTF-8mb4，也会导致索引失效；它们的原因都是MySQL在进行比较的时候，会使用函数对字段进行转换
模糊查询 like ‘%keyword%` 查询不能有前置的%，如果是 like ‘keyword%’ 这样还是可以用到索引的
索引列里有字段为null，null值不会加入到索引中
使用or连接条件，如果or连接的条件中有一个不是索引，会失效，可以改成使用union all来连接两条sql语句
组合索引没有体现最左匹配
is null / is not null 对索引作判断
索引上使用 != 或者 <> 还有not in
索引的值只有几种情况，如性别只有男和女，这种情况虽然也会用索引，只是意义不大
表的量级较小，存储引擎判定使用全表扫描更快
有一case：1.select * from T where k in(1,2,3,4,5)；2.select * from T where k between 1 and 5，k为索引，但是推荐使用方法2，因为方法1会导致树查5次，而2是1次
对索引字段使用了函数进行计算，可能会导致MySQL不使用该索引，或者进行了全索引扫描，无法用到索引进行快速定位
如果查询的值的长度是否大于索引定义的长度，如果大于，虽然也会走索引，因为MySQL是先把查询值的长度截断成跟索引定义的长度一致去遍历索引，但是它还要再回表得到数据进行比较，所以查询会很慢
注意区分度，计算索引最优长度，使用这个计算 select count(distinct left(列名, 索引长度)) / count(*) from {table名}，区分度越高越好，另外使用前缀索引虽然会减少索引存储空间，但是可能会增加扫描次数或者覆盖索引不生效
当表的字符集编码或者属性不同时，需要想办法把函数加再索引对应的值上，而不是索引字段上，或者去掉函数，使用其他方法替代
当要充当索引的字段在某些长度的区分度太小时，可以增加一个字段，采用索引字段的倒序存储或者hash的方法来充当索引，缺点是无法使用索引进行范围查询，而hash更是只能支持等值查询，查询时需要进行额外的计算，也是一种性能消耗
由于MySQL在选择索引的时候会根据 索引区分度 和 索引对应的预估扫描行数（包括回表），但是有可能预估的结果是不准的，如果通过explain命令发现rows的值与想象中的偏差较大，可以执行analyze table [tableName]来重新统计索引信息，或者使用force index([索引名称])来强制使用索引，或者重写SQL，引导MySQL使用正确的索引
注意索引的最左前缀原则，如果在设置联合索引时，可以通过调整顺序来达到少维护一个索引，拿这个顺序就可以优先考虑，另外一个要考虑的就是索引的大小
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
启用MRR优化：在MySQL 5.6之后，使用MRR进行优化，需要设置set optimizer_switch =' mrr = on, mrr_cost_based = off, batched_key_access = on';
在没使用MRR优化前，通过索引回表是一行一行去查的，每次通过普通索引查到后的主键id是无序的，多次回表查性能不太好，而MRR优化指的是，如果主键索引有序，记录递增插入，则会先根据索引找到所有的id，再对id进行排序，再回表查。
使用临时表，如果是InnoDB引擎，那创建的临时表是写磁盘的，如果是Memory引擎，则是写内存的。创建语句是 create temporary table ···engine=xxx，用法跟普通表类似，但仅在当前线程可见，show tables不显示临时表，当当前线程处理完成后，临时表会被清空，但会保留表结构，允许在不同线程间重名，如果bin log格式是statement或mix，操作临时表的记录也会记录在bin log，备库也会跟着操作。
内部临时表常用于不带索引的 join、分库分表时的联合查询、union查询(对两个查询结果求并集并去重，union all则不去重)、group by查询(order by和group by子句不一样时也会)、distinct查询时加上order by、from 中的子句查询，explain查询指向计划结果的extra列。
当查询的数据量比较大时，默认会先使用内存临时表，发现太大后才转成磁盘表，因此如果数据量太大，可以使用SQL_BIG_RESULT（ select SQL_BIG_RESULT 字段 from 表 group by xxx）告诉MySQL强制使用磁盘临时表
一般情况下还是使用内存表快些，通过调大tmp_table_size来加大内存临时表的大小，默认是16M，这种临时表是由MySQL查询算法决定使用的
group by 或order by多个字段时，需要为这多个字段建组合索引，不然也是全表扫，注意 order by 无法使用前缀匹配索引；
索引最好设置为 NOT NULL，NULL值的存在会导致优化器做索引选择更复杂，索引统计时，count会省略值为NULL的行，另外，NULL值无意义，但会占物理空间，至少1字节存储 NULL值列表；
关于大数据量时的分页，优化的思路也是尽可能的使用索引，比如
select * from table where id > (页数*页面大小) order by id limit M， order by 使得结果稳定；
select * from table where id > (select ID from table order by id limit M, 1) order by id limit n，先利用子查询把id查出来，依靠id上的顺序，外层效果跟上面的类似
select * from table where id in (select id from table limit M, N)，同样也是利用id上的索引，覆盖索引，只查主键时，效率很高。
普通查询返回是会等整个结果集都查询完毕才会返回，可能会导致客户端直接OOM，so 可以采用流式返回，边查边返回，客户端可以根据查到的结果先进行业务处理，及时回收内存，避免OOM；但是流式查询返回有个问题就是MySQL并不清楚客户端什么时候会关闭，所以MySQL会持续占用这条连接和使用额外的内存或磁盘来存储流式查询的结果，持续的查询也可能导致CPU消耗，消耗较大；两者在查询上的耗时是一样的，只是对客户端友不友好而已；
explain + SQL语句，给出该SQL语句的分析结果，看看查询的类型，有没有用到索引，是不是全表扫描
比较重要的字段：
select_type：查询类型，如 SIMPLE（简单查询）、PRIMARY（当存在子查询时，最外面的查询被标记为主查询）、UNION（联合查询，当一个查询在UNION关键字后就会出现）、SUBQUERY（子查询）、DERVIED（构造表，一个查询语句形成的结果集）等；
type：访问类型， 从左到右，查询性能依次减弱，一个好的SQL起码得达到range级；
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
NULL：MySQL在优化阶段分解查询语句，在执行阶段用不着再访问表和索引，比如explain SELECT 5*7这种单纯做计算的，一般用在已建立索引的情况下，查找索引的最大值和最小值，因为MySQL直接查找B+树最边上的叶子节点就能知道了，比如EXPLAIN SELECT MAX(id) FROM student；
const：表示通过索引一次就找到了，一般是查询条件是主键或者唯一索引的场景下；
eq_ref：联表查询，按连表的主键或唯一键联合查询，当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型；
ref：表示单表扫描或者表间的连接匹配条件时用到普通索引或者唯一索引的前缀匹配；
index_merge：索引合并， 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引；
5.0后才有的功能，使得MySQL可以在一次查询种使用多个索引，但是使用场景比较局限，多发生在查询条件涉及多个and和or的场景
range：索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
index： 索引表全表扫描，通常比 All 快。因为索引文件通常比数据文件小，即 all 和 index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘读的，比如 count 查询；
all：全表扫描；
possible_keys：能使用哪个索引找到行，查询涉及到字段上若存在索引，则该索引将被列出，但不一定被查询使用；
key：索引列的名称，如果没有使用索引，显示为NULL；
key_len：使用到索引的长度，可以看出索引是否使用得充分，比如虽然用到了联合索引，但是只用到了第一个字段作为索引，说明没有使用到所以索引，索引使用不充分；
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows：扫描的行数
filtered：查询出的行数占表的百分比
extra：额外信息说明
using index：使用到了覆盖索引；
using temporary：对查询结果排序时使用了临时表，查询效率比filesort还低，常见于order by和group by，查询效率不高；
using filesort：当查询语句包含 group by，且无法利用索引完成排序操作时，会使用外部排序，通过磁盘和内存交换数据来排序，查询效率不高；
using index condition：使用了索引下推；
using join buffer（block nested loop）：使用join连表，并使用了缓冲区；
using union：使用索引并取并集；
using sort_union ：先对取出的数据按rowid排序，然后再取并集；
using intersect ：索引取交集；
show processlist，此命令用于查看目前执行的sql语句执行的状态，比如当CPU使用率飙升时，可通过该命令查看哪些SQL语句在执行
show status，查看数据库运行的实时状态，比如查询运行期间SQL的执行次数、连接数、缓存内的线程数量、连接数等，具体看 mysql SHOW STATUS 变量
show variables，查看系统参数，一些静态参数，比如开启慢查询，设置索引缓冲区大小，具体参考：Mysql show variables命令详解
performance_schema和sys系统库
MySQL启动前需要设置performance_schema=on，但是性能会比off少10%；
查询sys.schema_table_lock_waits、sys.innodb_lock_waits表可以知道那条语句在占用锁；
查看 information_schema.innodb_trx表可以看到事务具体的状态；
使用show engine innodb status查看数据库请求情况
使用show status like 'innodb_row_lock%';查询行锁竞争情况
使用show status like 'table%';查询表锁竞争情况
select trx_id,trx_state,trx_started,trx_wait_started,trx_operation_state,trx_tables_in_use,trx_rows_locked,trx_rows_modified,trx_query from information_schema.innodb_trx;查询当前事务情况
select * from information_schema.innodb_lock_waits;查看锁等待对应关系
select * from information_schema.innodb_locks;查看当前出现的锁
当有语句执行过久或有语句一直被阻塞时，可以kill掉它
使用kill query/connection + 线程id终止语句或连接，但kill不是直接终止线程，只是告诉该线程这条语句不用继续执行了，MySQL会在执行逻辑上打上断点标记，线程执行到该位置，才会判断状态是否是处于被kill状态，然后进行收尾工作，比如释放掉之前持有的锁
慢查询分析
打开：set global slow_query_log='ON'，临时开启，无需重启，永久开启则在my.cnf文件里设置
设置日志存放位置：set global slow_query_log_file='/usr/local/mysql/data/slow.log';
设置超过x秒就会记录到慢查询日志中：set global long_query_time=x;
查看慢查询相关设置：show variables like 'slow_query%';
慢查询日志分析工具：mysqlsla、mysqldumpslow
慢查询日志中，rows_examined字段，表示某个语句执行过程中扫描了多少行
设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。
提供了大量的特性，包括压缩表、空间数据索引等。
不支持事务。
不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。
可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。
如果指定了 DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。
是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻读。
主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。
关于有时更新或者查询时突然变慢的原因：首先，InnoDB更新采用WAL机制，更新redo log和表数据时都是先写到buffer，再flush到磁盘，原因就是出在flush磁盘上，当redo log满了，或者内存满了，脏页太多，都会将内存里的数据flush到磁盘，以腾出空间，如果要刷的数据特别多，那消耗的时间就长。
解决方法：
设置到 innodb_io_capacity 参数，该参数会告诉InnoDB机器的磁盘能力，可以使用fio工具测出 ；
控制脏页的比例，设置 innodb_max_dirty_pages_pct 的值，默认是75%，达到了就会刷；
. 刷新脏页时是否会递归检测隔壁数据页是否也是脏页，如果是会连着一起刷，通过 innodb_flush_neighbors=1表示采用这种机制，=0表示只会刷自己，这个机制对机械硬盘关系比较大，SSD则不会；
关于InnoDB的删除：
innodb_file_per_table=OFF：表示表数据放在系统共享表空间，=on表示各个表空间放在独立文件下，后缀名是 .ibd ，一般设置=on，便于管理。
当我们使用delete删除数据时，InnoDB实际上是把数据页上的该数据标记为删除，表示该位置可以进行复用，此时磁盘上的文件并不会变小，当数据随机插入时会因为页分裂，分裂后的页可能存不满数据，就会标记某些位可复用，导致页的利用率不高，当有大量的增删时，会导致数据页存在大量空洞，为了压缩空间，此时的解决办法是重建表，一般使用alter table [tableName] enging=InnoDB达到重建的目的，MySQL会自动创建临时表，进行数据转存，交换表名，删除旧表，此时会阻塞，阻止增删改，5.56版本后使用onlineDDL机制，解决了这个问题，解决方法是使用redo log记录新插入的数据 + MDL读锁(写锁会退化) + IO + CPU
四大特性：
插入缓冲：使用change buffer，对insert、delete、update、purge都有提升；
双写机制：InnoDB在将Buffer Pool中的dirty Page脏页刷到磁盘时，会先将dirty page刷到InnoDB tablespace中的一个区域，这个区域称为 Double Write Buffer，该区域大小 2MB ，每次写入 1MB，128个页，每个页16k，其中120个页为后台线程批量刷Dirty Page，还有8个也是为了前台起的single page flash线程，用户可以主动请求使用，迅速提升空余的空间，在向Double Write Buffer写入成功后，第二步是将数据分别刷到一个共享空间和真正应该存在的位置；
Buffer Pool：解决查询性能问题，作为磁盘的缓存，避免每次查询都从磁盘里读取，提升读写性能；MySQL启动时，InnoDB会为Buffer Pool申请一片连续的内存空间，按照默认的16KB大小划分出一个个页作为缓存页；
读取数据时，先读Buffer Pool，没有才去读磁盘；写数据时，先修改Buffer Pool中数据所在的页，设置为脏页，由后台线程将脏页写入磁盘（或配合使用change buffer机制）；
自适应哈希索引：InnoDB会监控对表上辅助索引页的查询，如果发现建立hash索引可以提升性能，就会在缓冲池建立hash索引，提升查询性能；
预读：用异步将磁盘中的页读取到Buffer Pool中，预读请求的所有页集中在一个范围内；
menory引擎的内存表不同于InnoDB的内部临时表，内存表是库内全局可见，写内存的，而innodb的内部临时表是写磁盘的，同一线程内可见，线程结束就会清空
默认是hash索引，数据单独存放，索引上保存数据的位置，这种形式称为推组织表，而InnoDB那种 B+树 数结构的则是索引组织表
InnoDB数据存放是有顺序的，因此有可能会产生空洞，而memory则是有空位就可以存放，因此当数据位置发生变化时，innoDB只需要修改主键索引，而内存表需要修改所有索引（哈希表扩容导致）
内存表不支持变长数据类型，只能固定字符串长度，即varchar(N)会退化为char(N)
内存表的索引地位是相等的，而Innodb表分成了主键索引和普通索引
由于hash索引并不适合索引范围查询，范围查询实际上是全表扫描，如果要支持索引范围查询，需要建立B+树索引
内存表仅支持表级锁，粒度较大，并发度低
重启会清空内存表的特性会影响主备复制
MyISAM是非事务安全；InnoDB是事务安全型
MyISAM的锁是表锁；InnoDB支持行锁
MyISAM支持全文索引；InnoDB 5.6版本后才支持
MyISAM适合 SELECT 密集型的表；InnoDB适合 INSERT 和 UPDATE 密集型的表；MyISM可以直接定位到数据所在的内存地址，直接找到数据，而InnoDB需要维护数据缓存，查询过程中先定位到行所在的数据块，在从数据块中定位到要查找的行，所以查询性能上差于MyISM
MyISAM表是保存成文件形式，跨平台转移方便
InnoDB表比MyISAM表安全
MyISAM对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用
MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢；InnoDB因为有redo log，支持安全恢复
MyISAM不支持外键；InnoDB 支持外键，InnoDB 支持在线热备份
MyISAM是非聚集索引，使用 B+树 作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针（数据的物理地址），所以还要再查一次才能得到数据；
InnoDB是聚集索引，数据文件是和（主键）索引绑在一起的，即索引 + 数据（数据本身，不是指针） = 整个表数据文件，通过主键索引到整个记录，必须要有主键；辅助索引是以建索引的字段为关键字索引到主键，查询时先查到主键，再通过主键查询到数据；
即MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针；InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；
原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）
原子性：事务中的所有操作要么全部提交成功，要么全部失败回滚（undo log保证）
一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态（由AID原则保证）
隔离性：一个事务所做的修改在最终提交以前，对其他事务是不可见的（MVCC + 锁机制保证）
持久性：一旦事务提交，则其所做的修改将永久保存到数据库（redo log保证）
脏读：如有事务A和B，A读取了B未提交的数据
丢失更新：如有事务A和B，AB均写入数据，A写入的数据被B覆盖
不可重复读：如有事务A和B，A负责读取，B负责写入，A连续读的过程中B写入了一次，A前后两次读出来的数据不一样
幻读：如有事务A和B，A修改表内数据的过程中，B向表内插入了一条数据，A修改完后发现数据并没有被全部修改完，或者是在RR隔离级别下，事务A内前后两条相同的SQL带“当前读”查询查回来的数据数不一致；
另外一种理解是，在RR隔离级别下，事务A，快照读查到数据1不存在，此时事务B插入数据1，事务A继续插入数据1失败，但是查询数据1又不存在，此时也可以说事务A出现幻读；
还有一种理解是，在RR隔离级别下，事务A，先快照读查询得到两条数据，此时事务B插入数据后，事务A使用当前读，就会读到3条数据了，此时也可以说事务A出现幻读；
原因是事务A一开始查询时没有带 当前读 导致，因为如果事务A一开始就使用范围当前读，事务B是插不进去的数据1的；
不可重复读和幻读的区别：不可重复读侧重于update，而幻读侧重于insert和delete。不可重复读是在一个事务内前后两次读取的数据不一致，此时数据数量没有变化，重复读取得到的数据不一致，所以叫不可重复读；而幻读是在一个事务内前后两次读取的数据不一致，读的数据量变多或者变少了，这些多了的数据或少了的数据就像幻觉，所以叫幻读
隔离级别就是为了解决上述并发时候带来的问题
DEFAULT：默认隔离级别，即使用底层数据库默认的隔离级别；
READ_UNCOMMITTED：未提交读，一个事务未提交时，它的变更可以被其他事务看到；
可能出现 脏读、不可重复读、丢失更新、幻读；
READ_COMMITTED：提交读，一个事务提交之后，它做的变更才会被其他事务看到，保证了一个事务不会 读 到另一个并行事务已修改但未提交的数据；每次select语句都会生成一个新的Read View；
避免了“脏读”，可能出现不可重复读、丢失更新；
Oracle默认隔离级别；
REPEATABLE_READ：可重复读，一个事务在执行中看到的数据，总是跟这个事务在启动时看到的数据一致，保证了一个事务不会 修改 已经由另一个事务读取但未提交（回滚）的数据；事务开始时生成一个Read View，整个事务期间都是用整个Read View；
避免了脏读、不可重复读取、丢失更新，可能存在幻读；
MySQL默认是此隔离级别；
针对快照读，即普通的select语句，MySQL通过MVCC解决幻读问题；
针对当前读，即带for update的select语句，MySQL通过next-key lock解决幻读问题；
SERIALIZABLE：序列化,最严格的级别，事务串行执行,即一个事务要等待另一个事务完成才可进行
效率最差，但也解决了并发带来的那4种问题；
例子：
启动事务，查询得到值1
启动事务
查询得到值1
将1改为2
查询得到的值v1
提交事务B
查询得到的值v2
提交事务A
查询得到值v3
在不同隔离级别下的答案
未提交读：v1=2，v2=2，v3=2
提交读：v1=1，v2=2，v3=2
可重复读：v1=1，v2=1，v3=2
串行：v1=1，v2=1，v3=2，且直到事务A提交后，事务B才可以继续执行
一般避免使用长事务，即在一个事务里做过多操作，长事务会导致回滚日志变大，也会占用锁资源
显式启动事务，使用begin或strart transaction启动事务，commit提交事务，rollback回滚；
set autocommit=0，关掉自动提交，任何语句执行都需要显式的提交(主动commit或rollback)才算执行完成；
set autocommit=1，执行任意一条语句都会默认开启单次事务执行完成后隐式提交，事务也可以显式开启，直到显示使用commit、rollback或断开连接。一般是使用set autocommit=1，开启事务，再commit提交事务，执行commit work and chain则提交事务并开启下一次事务；
根据范围，可以分为全局锁、表级锁、行锁，当多种锁同时出现时，必须得所有锁不互斥，才能并行，否则就得等。
全局锁：对整个数据库实例加锁，命令： Flush tables with read lock，让整个数据库变成只读，禁止任何ddl、dml语句
一般用于全库逻辑备份，但有可能造成主从库数据延迟或者业务停摆，不用的话又会导致数据不一致问题，一般这种方式是给不支持 可重复读 事务的引擎使用的，像InnoDB可以在可重复读隔离级别下开启事务读数据，利用MVCC来保证在此期间数据一致，可以不用这种锁；
表级锁：
一种需要显示启动，比如lock tables t1 read, t2 write; 表示线程在执行unlock tables之前，只能读t1，读写t2，其他操作做不了。
另一种是 MDL(metadata lock)，不需要显示使用，在访问一个表时自动加上，作用是保证读写正确性，当对一个表内数据做CRUD时，加MDL读锁，当对一个表做结构变更时，加MDL写锁；
主要用于保护表的元数据，比如字段名等，比如查询和修改表字段的语句同时出现，如果没有MDL锁，就会导致查出来的数据列名有问题。
MDL锁和表锁时可以同时出现的，比如MyISAM表上更新一行，会加上MDL读锁和表锁；
读锁间不互斥，读写、写写间互斥，MDL会在事务提交后释放。当需要对热点表做结构变更时，最好在变更语句上加等待时间，避免出现死锁导致整个表无法读写，或者确定没有长事务执行后，再上MDL写锁；
意向共享锁 - IS锁：表级锁，表示事务持有表中行的共享锁或者打算获取行的共享锁，仅表示意图，不阻塞其他操作，当事务在获取表中的共享行锁时，需要先获取表中的意向共享锁；
意向排他锁 - IX锁：表级锁，表示事务持有表中行的排他锁或者打算获取行的排他锁，仅表示意图，不阻塞其他操作，当事务在获取表中的排他行锁时，需要先获取表中的意向排他锁；
意向锁的作用主要为了解决遍历整个表来寻找行锁的情况，快速判断表里释放有记录被加锁，比如在alert语句修改表结构的时候使用：
事务A想修改表T的行R，A获得行R的排他锁，锁住了行R，事务B使用Alter Table语句修改表T的结构，此时需要获取表T的共享锁，由于它不知道表T是否存在行锁，只能去遍历，当表有行锁时，只能等行锁释放才能修改表结构，因为遍历很耗性能，所以需要意向锁来解决这个问题，事务A在获得行R的排他锁时，需要先上表T的共享排他锁，事务B在Alter 表T时就可以直接判断该表是否被上行锁了。
行锁：在InnoDB事务中，采用二阶段锁协议，行锁是在事务结束后才释放，在事务过程中，即使一开始用了后面没用到也不会被释放，因此，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，减少锁的影响时间；行锁是统称，主要可以细分为共享锁和排他锁这些。
记录锁：行锁，对索引进行锁定，如果是主键索引就会锁一条，但如果是二级索引，就会锁所有匹配的记录。
共享锁 - S锁（SQL + lock in share mode）：行锁，事务T对数据A加上共享锁，其他事务只能对A添加共享锁，不能加排他锁，获取共享锁的事务只能读不能写。
排他锁 - X锁（SQL + for update）：行锁，事务T对数据A加上排他锁，则其他事务不能再对A加任何类型的锁。获得排它锁的事务即能读数据又能修改数据。
与乐观锁和悲观锁的差别：乐观锁和悲观锁是针对数据操作的一种思想，并不是数据库实际存在的锁，只是可以用上面的锁进行实现，乐观锁适合读多写少的场景，悲观锁适合写多读少的场景；
间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙锁。
主要是为了解决幻读问题，只在可重复读隔离级别下有效，一般与行锁一同出现。
间隙锁也会导致死锁，比如两个事务同时在一段范围内的数据加入间隙锁（事务间的间隙锁不会冲突，可以加成功），又insert或update在这个范围内的数据，就会导致死锁。
比如有数据 (1, a)(3, c)(4, d)(5,e)，事务A更新c，间隙锁范围为(a, d)，事务B更新e，间隙锁范围为(d, 正无穷)，如果此时事务A插入f，事务B插入b，就会导致死锁。
另外，"间隙" 是 由这个间隙右边的记录定义的，也就是说，如果 有索引 c 的值为0，5，10，15，先有事务A查询select c from t where c > 5 lock in share mode，加锁范围是(5, 10], (10, 15], (15, max]，然后有事务B更新update t set c = 1 where c = 5，执行成功，此时c的值有 0，1，10，15，再执行update t set c=5 where c=1;会执行不成功，被blocked住，原因是间隙锁的间隙变成了 [1, 10]，更新失败。
Next-Key 锁 = 间隙锁(gap lock) + 行锁(record lock)，在可重复读隔离级别下，InnoDB在查找和扫描索引时，都会使用Next-Key锁来防止幻读的发生。
Auto-Inc 锁：自增锁，表级锁，比较特殊，当设置键为自增时使用，比如自增主键，在生成自增id时，会先获取相关表的 Auto-Inc 锁，阻塞其他事务的插入操作，保证自增的唯一性。不遵循二阶段锁协议，因为它并不是在事务提交时释放，而是在inset语句执行后释放，即使是在回滚时，自增值也不会减一。
涉及的参数是 innodb_autoinc_lock_mode：
=1时，普通insert语句会执行完之后释放，批量insert时会等到所有批量insert的SQL都结束的时候才释放锁，原因是如果bin log不是row，备库在复制时产生的行的id可能跟主库的不一致问题；
=2时，自增id锁每次获取完就会释放，不必等待插入语句执行完，所以如果此时bin log的格式不是row，就会出现主从不一致。
如果选择=1，在批量insert时性能就会很差，MySQL的优化是让insert的语句不使用连续的自增id，不过这样就会让自增id不连续了，所以一般的操作是选择=2，每条insert就释放，且bin log格式要=row；
多版本并发控制（MVCC）：用来解决读-写冲突的无锁并发控制，为事务分配一个单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。MVCC 在语境中倾向于 “对多行数据打快照造平行宇宙”，而 CAS 一般只是保护单行数据而已
MVCC只在 提交读 和 可重复读隔离级别下有效，保证在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。 对于可重复读，查询只承认在事务启动前就已经提交完成的数据； 对于提交读，查询只承认在语句启动前就已经提交完成的数据;
MVCC的版本快照，指的是什么呢，而且它是基于整个库的，总不能保存多个版本的库的所有数据吧？
实际上MVCC得到的快照是逻辑上的数据，是推测出来的，通过当前值，利用事务id和undo log日志，根据日志"回滚"得到各个版本的数据，事务id可以简单理解为对该行数据进行更改时产生的id，当然一个事务内可以对多条数据进行操作，这多条数据的事务id都是相同的
例子：
假如一个值1被按顺序改为2、3、4，每一次更改都会记录在回滚日志(undo log)里，如：将2改为1 -> 将3改为2 -> 将4改为3，当前的值是4，在查询这条记录的时候，不同时刻启动的事务会有不同的视图，比如有视图A（将2改成1）里看到的值是1，即同一条记录在数据库中存在多个版本
插入意向锁（II Gap）：特殊间隙锁，只有在插入时使用，表示插入的意向，属于行级锁，但不与行级锁冲突，而是与 间隙锁 和 Next-Key锁 冲突，当插入前需要获取插入意向锁，此时会与 Next-Key 锁冲突而阻塞，从而防止幻读。
唯一索引（主键）的索引树:
index
19
21
22
20
39
非唯一索引的索引树：
id
1
15
5
10
20
查询过程中扫描到的行才会加锁，锁的基本单位是next-key lock（左开右闭），间隙锁是左开右开；
唯一索引（主键）等值查询
记录存在，退化成行锁
记录不存在，会在找到该等值的 前后 遇到的第一个存在的数据的这段范围加上间隙锁（左开右开）；
唯一索引（主键）范围查询：会对每个扫描到的索引加next-key锁，遇到下面情况会退化成间隙锁或者行锁；
大于，对扫描到的索引加next-key锁（左开右闭）
比如有记录id：1，5，10，15，20，查询id>15，记录存在，加锁区间是 (15, 20], (20, +∞]
记录不存在，查询 id > 14，加锁区间是 (10, 15], (15, 20], (20, +∞]
大于等于（此时不管条件里有没有小于），等值的记录加行锁，大于该值的记录会进行扫描，扫描到第一个不满足条件的值（也是对应存在的行，不管小于），加next-key lock（左开右闭）
比如有记录 id：1，5，10，15，20，查询 id>=10 and id <11，加锁区间是 [10], (10, 15]
记录存在，查询 id >= 15，加锁区间是 [15], (15, 20], (20, +∞)
记录不存在，查询 id >= 14，加锁区间是 (10, 15], (15, 20], (20, +∞]
小于或小于等于，且记录不存在，先扫描到的记录，还是加next-key锁，扫描到终止范围查询的记录时，该记录的索引的next-key锁会退化成间隙锁（左开右开）
比如有记录 id：1，5，10，15，20，查询 id<6，锁的区间是 (-∞, 1], (1, 5], (5, 10)
小于，且当记录存在时，next-key lock会退化成间隙锁（左开右开）
比如有记录 id：1，5，10，15，20，查询 id<5，锁的区间是 (-∞, 1], (1, 5)
小于等于，且记录存在时，扫描到终止范围查询的记录时，加next-key锁
比如有记录 id：1，5，10，15，20，查询 id<=5，锁的区间是 (-∞, 1], (1, 5]
非唯一索引等值查询
记录存在，需要访问该等值的左边到右边第一个不满足条件的值，这个范围加上间隙锁（左开右开，中间包含该等值）；然后还要对这些扫描到的非唯一索引记录对应的主键索引加锁；
比如有非唯一索引：19，20，21，22，39，查询 index=22的，锁的区间是 (21, 22], (22, 39)
记录不存在，需要访问该等值的左边到右边第一个不满足条件的值，这个范围加上间隙锁（左开右开），因为不存在满足查询条件的记录，所以不用对主键索引加锁；
虽然主键索引没加锁，但是，左右两边的值，还是会存在插入失败的情况，因为插入语句在插入一条记录之前，需要定位到该记录在B+树的位置，如果插入的位置的下一条记录的索引上有间隙锁，会发生阻塞；
此时对应区间左右两边的值，虽然是开区间，但是在插入时，要判断插入的主键索引，是否在非唯一索引间隙锁对应的主键的范围内；
比如有非唯一索引：19，20，21，22，39，查询 index=25，锁的区间是 (22, 39)，假如index=22对应的主键索引为10，index=39对应的主键索引为20，此时如果要插入index=22，id=3的记录，会插入失败，因为id=3的下一条是id=10，同理，插入index=22，id=12的也会失败，因为下一条id=20
非唯一索引范围查询
范围查询都需要访问到第一个不满足条件的值为止，在这个范围加next-key锁（左开右闭），然后还要对这些扫描到的非唯一索引记录对应的主键索引加锁；
比如有非唯一索引：19，20，21，22，39，查询 index>=22，锁的区间是 (21, 22], (22, 39], (39, +∞]，同时，会对index=22，39的记录加行锁；
锁是加在索引上的，先对where条件上的用到的索引加锁，再对该行上存在的索引依次加锁。比如有表A，索引为id、a、b，当update时的条件是where a=xx时，会对索引a、id、b的顺序加锁；间隙锁只会加在where条件中的索引上，对于该索引定位到的行加的是行锁，但对于主键索引加锁的时候，只有满足查询条件的记录才会对它的主键索引加锁；
select加的锁，如果查询的列刚好是满足覆盖索引，且覆盖索引不包括其他索引，则只会锁where条件上的索引；
以上规则需要组合起来使用，InnoDB会对扫描过的行都加上行锁和间隙锁，所以如果查询条件不是索引，就会全表扫描，并对扫到的行主键上锁，表现出来就是锁表了。
在InnoDB中，一般设置innodb_thread_concurrency的值在64~128之间，=0表示不限制并发线程数量，这里的并发线程数量指的是并发查询数量，并发连接数量可以有上千个，但是并发查询数量不能太多，否则太耗CPU资源，另外，在查询进入锁等待时，并发查询数量会减一，不计入并发查询数量里，select sleep(秒)则会计入。
对于update或delete语句，可以设置参数 sql_safe_updates=1 此时，语句只有使用了where，且where条件中必须包含索引列，或者使用了limit，或者同时使用where和limit（此时where不要求有索引条件），才允许执行，如果优化器最终还是选择扫描全表，就需要使用fore index([index名称])强制使用索引了。
当线程出现循环资源依赖，导致多个线程互相等待的状态称为死锁，解决方案：
设置超时时间 innodb_lock_wait_timeout，在InnoDB中默认是50s
死锁检测，当发现死锁后，主动回滚死锁链条中的某一个事务，设置innodb_deadlock_detect=on，默认值是on
一般使用第二种，但死锁检测会消耗大量CPU资源，主要发生在对同一行进行更新的检测上，其算法是O(n)，虽然在同一行更新不会造成死锁，但是当并发很高时进行检测时就会消耗大量CPU资源，解决方案有两种，一种是在中间件或者MySQL server层上，增加对同一行更新的判断，进行排队，或者将那一行改为逻辑上的多行，来分散压力，因此对同一组资源，尽量以相同的顺序访问。
加字段：alter table {表名} add column {列名} {类型} default {默认值} comment {注释} after {排在哪个字段后}
加索引：alter table {表名} add index {索引类型，比如unique、primary} {索引名} (字段1, 字段2)
以加字段为例子：
MySQL5.6以前的给表加字段时会锁表，在此期间只能读，加字段的步骤：
对原始表加MDL写锁，禁止读写；
按照原始表和执行语句的定义，重新创建一个空的临时表；
为临时表添加索引；
将原始表种的数据逐条copy到临时表中；
当原始表的所有数据都被copy到临时表后，将原始表删除，临时表更名为原始表表名，允许读写；
如果5.6之前的版本要加字段，可以使用pt-osc来实现，本质上也是创建临时表，然后copy数据的过程中通过触发器触发临时表上数据的修改，最后删除原始表并更名而已。
MySQL5.6之后新增Online DDL功能，使得表不可用的时间减少，或者说让表在被修改时仍然能正常操作，加字段时的步骤：
对原始表加MDL写锁，禁止读写；
按照原始表和执行语句的定义，重新创建一个空的临时表，并申请rowlog空间，然后便允许读写；
拷贝原始表的数据到临时表，此时表数据的CUD操作都会放在rowlog中，此时客户端仍然可以进行操作；
原始表数据全部拷贝完成后，会将rowlog中的改动同步到临时表中，此时客户端不可操作；
当原始表中的数据都被copy到临时表后，并且copy期间客户端的所有CUD操作都同步到临时表后（同步copy期间的操作禁止读写），原始表删除，临时表更名为原始表表名；
所以其实Online DDL也会锁表的，只是锁表的时间特别短，几乎可以全程允许读写。
要注意当表的数据量比较大时，不要设置default值，如果字段带有default值，MySQL会在执行完Online DDL后，对整个表该新增字段更新默认值，导致锁表。
在对索引进行添加和删除时也是类似的步骤，只是不是使用copy的方式，而是改成了in-place方式，不需要复制数据，而是将copy的操作改成按照聚簇索引的顺序查询数据，找到需要的索引列数据，排序后插入新的索引页中。
5.6版本之后copy和in-place就都对增删字段、索引时都可用。
参考：https://segmentfault.com/a/1190000041149544
在可重复读的隔离级别下，事务A开启，操作数据D，此时会创建一个当前数据D的视图(MVCC)，但此时又刚好有事务B，已经操作到这条数据，并给这条数据加了行锁，对这条数据进行操作，事务B操作完成后，释放数据D的行锁，那之前的事务A在最终修改操作数据D时，数据D的值是什么呢？
假如一开始k的值是1，autocommit=1，即单独一条SQL执行本身就是一个事务，会自动提交
另外，事务中的begin / start transaction命令，是以执行它们之后的第一个sql语句为启动开始事务，而start with consisten snapshot是以这条命令为起点开启事务
start transaction with consisten snapshot;
start transaction with consisten snapshot;
update t set k = k + 1 where id = D
update t set k = k + 1 where id = D;
select K from t where id = D;
select k from t where id = D;
commit;
commit;
上面这道题的答案是 事务A查到k的值分别是1，事务B查到k的值是3
原因：事务A之所以查到的值是1，是因为事务A开启时，事务B和C还没开启，此时的快照k=1，因此得到的值是1。事务B查到k的值是3，虽然事务C是在事务B之后开启的，感觉看不到事务C修改后的值，但是由于更新操作是先读后写的，此时的读是当前读（当前读总是读该已提交的数据的最新版本），而当前读的值是2，因此更新后k的值是3，如果不这么做，就会导致事务C更新丢失，而在同一个事务内读值，是可以读到由当前事务修改的值的，所以事务B读到的值是3。普通select语句，在可重复读情况下，为了实现一致性读，是通过读undo log实现的，如果undo log太长（可能因为更新次数太多），会导致查的很慢
题外话，如果事务A读的时候加锁，就会变成当前读，例如将事务A的select语句后面加上lock in share mode（共享锁）或者for update（排他锁），那么查到的值就是3了。如果事务B在执行更新前先select了，查到的值也是1。如果事务C是显式启动事务，在事务B select后commit前才执行commit操作，就会触发二阶段锁协议，两条更新语句同时更新一行数据，先执行的语句会对这条数据加行锁，所以事务B需要等到事务C提交后，才能执行更新操作
这个问题的关键在于要理解 MVCC原理，更新操作前的当前读，事务的隔离级别，一致性读、行锁
参考：
当表的数据量很大，并且TPS也很高时，建立索引或者修改表结构会很慢，甚至出现死锁。
方案1：创建一张与原表结构相同的新表，在新表上创建索引，将原表改名，新表改成原表的名字，让新表承担业务，然后为老表新增索引，完了之后再交换表名，再将新表产生的数据导入到原表中。
但是这种方式有个问题是如果要对老表的数据进行删改，会产生数据丢失，此过程可以通过建立触发器解决，有Percona Toolkit工具就可以自动完成上述过程，并且保证数据删改不丢失。
方案2：在业务空闲的时候，在从库上的表建索引，等待主库和从库同步位点一致，切换主从即可。
大量删除时，会锁表，业务高峰时还会CPU暴涨，导致数据库性能下降
方案1：删除大表的多行数据时，会超出InnoDB block table size的限制，要做的就算减少锁表的时间，可以选择不需要删除的数据，存到一张同名的空表里，重新命名原始表，新表使用原表名，删除原始表
方案2：将批量删除的任务拆分成N个小删除任务，异步执行
方案3：删除表上的索引，再删除数据，提升删除效率
慢查询导致CPU飙升，QPS不高，查询效率低，需要扫描大量数据才能出结果导致，这种时候一般是show processlist查看当前执行的查询，kill掉慢查询对应的id
慢查询，磁盘故障，不合理的配置+大量增删改导致IO升高，一般结合performance_schema查看MySQL各项指标，查看刷盘相关的参数配置是否合理，比如sync_binlog之类的
主要是为了解决数据量太大导致查询慢（一般可以分表，主从分离），还有高并发问题（一般是分库）。
分表时一般分为水平拆分（对行，即数量，比如根据id哈希分散到不同的表）和垂直拆分（对列，即拆字段，或者直接将某些表独立成库）
时间分片、范围分片，但容易产生热点问题
哈希分片，针对某一列做哈希取模，平均分配到各个分表中，如果要扩容，由于模会变，导致数据要重新哈希，停机迁移数据，这样是不行的，因此一开始要设计好，例如使用一致性哈希算法，减少迁移的数据量；
或者分表的时候取2的n次方，这样扩容的时候也以2的n次进行扩容，这样原来的key重新取模是在原来的位置或者原来的2倍；或者换个数据库了，像上面大表优化里面提到的那样。
查表法，先通过分片映射表查到要查询的分片，再到对应的分片进行查询，但是要二次查询，即使上缓存，查询性能也是一般。
一般是使用最常用的查询条件做分片key；
当分库分表遇到Sharding Key冲突时，只能选择冗余数据了，或者通过将多个列组合成一个新的列来标识，或者建立映射表；
分片之后，count之类的计数就只能用一张表单独存或者记录在Redis中了，不然就只能所有分片扫一次；
MySQL一般的集群架构是 有两台MySQL实行双Master部署，进行主备复制，注意要把备用的master的主备复制关闭，避免循环复制，另外会部署一些机子作为从库，以其中一台master为主库进行部署
只要redo log 和 bin log能够持久化到磁盘，就能确保MySQL异常重启后，数据就可以恢复
bin log的写入机制：事务执行过程中，先把日志写进bin log cache，事务提交时，再把bin log cache写进bin log文件（先写到文件系统的page cache，再进行持久化）中，然后把bin log cache清空。
bin log cache每个线程自己维护，bin log的写入是一个顺序操作；
bin log cache的大小通过binlog_cache_size控制，如果超过就暂存到磁盘；
redo log写入机制：原理与bin log类似，但是它是二阶段提交，有状态，事务执行过程中，redo log先是prepare状态，写入redo log buffer，再写bin log，提交事务，变为redo log commit状态；
redo log buffer全局共用，与bin log cache不同；
一般会把sync_binlog和innodb_flush_log_at_trx_commit都设置为1，即一个事务完整提交前，会刷两次盘。另一种设置是让sync_binlog=1000和innodb_flush_log_at_trx_commit=2，一般是在主备复制存在很大延迟时，为了让从库的备份速度跟上主库；
为了提高刷盘效率，MySQL一般会让多个事务在一段时间内完成，或尽量让page cache里的redo log和bin log组合在一起提交，减少刷盘次数；
一般从库设置为read only，可以避免主从切换过程的双写，实现的是最终一致性。
利用MySQL中的bin-log二进制文件，该文件记录了所有sql信息，主数据库会主动把bin-log文件发送给从数据库，在从数据库的relay-log重放日志文件中利用这些信息进行恢复。
bin log分为三种格式
statement：记录每次执行的SQL，但由于索引选择问题或者SQL语句使用聚合函数，有可能会导致主从不一致的问题
row：记录的是事件，表示每条SQL语句执行后的数据信息，比如delete操作后，会记录delete事件和delete删除的行的所有字段(可设置为记录所有字段或者只记录主键)；update操作会记录行数据前后的记录；insert操作会记录insert的所有字段信息，缺点是占空间，但对数据的恢复有利
mix（混合上面两种）：由MySQL自己判断，如果会出现主从不一致，就使用row，否则使用statement
bin log上会记录每台机子的server id，用于避免循环复制
每个MySQL数据库上都有这三个线程，默认是主服务器写完bin log后就算事务成功，bin log复制是异步执行。
binlog 线程 ：负责将主库上的数据更改写入bin log，之后事务线程提交事务，响应成功给客户端。
I/O 线程 ：负责从主库上读取bin log，并写入从库的重放日志（relay log）中，主库也会创建一个log dump线程来异步发送bin log给从库；
SQL 线程 ：在从库里，负责将读取到的relay log日志并重放其中的 SQL 语句。MySQL 5.6前只支持单线程，5.6后改为多线程，SQL线程分为container对事务进行分发，调度不同的worker线程进行执行，分发策略：1、更新同一行的两个事务，必须被分发到同一个worker；2、同一个事务不能被拆开，必须在同一worker中执行
从库在同步bin log时，必须保证bin log的顺序，才能确保数据一致性。
默认情况下，由于是异步复制，无法保证数据第一时间复制到从库上，但如果采用同步复制，即等从库复制完主库的bin log后才响应给客户端，性能就太差了。
在MySQL 5.7后的版本，增加半同步复制(Semisynchronous Replication)，事务线程不用等到所有都复制成功才响应，只要一部分复制响应回来后即可响应给客户端，比如一主二从，等一从成功即可成功。配置rpl_semi_sync_master_wait_no_slave表示至少等待多少个从库复制成功才算成功。rpl_semi_sync_master_wait_point表示主库执行事务的线程是提交事务前等待复制(默认)，还是提交事务之后等待复制
当备库重放日志的速度小于主库产生bin log的速度，会出现主备延迟，可能的原因：
主备机器配置不一致，备库机器性能较差
备库压力大，比如在备库上进行SQL分析、大量查询、大表的DDL，主库上的长事务操作等，消耗大量CPU资源导致
默认情况下，异步复制也会带来的读延时问题，可以采取 一主多从，主写从读，分散压力；利用好缓存中间件；持久化层的处理。
在MySQL 5.6后的版本，可以设置slave_parallel_workers来决定从库在进行重放时工作的线程数，一般设置在8~16，以通过并行重放的目的加快主从复制速度。
当MySQL集群搭建采用一主多从时，最好采用GTID模式来实现一主多从的切换。
可靠性优先：前提，备库是只读的，首先，备库持续判断与主库同步间的延迟时间，如果小于可接受的值，主库改为只读，主库等待备库同步延迟时间降为0，备库改为可读写，业务切换到备库，业务不可写的时间取决于主库等待备库同步数据的延迟时间
可用性优先：步骤与上面的类似，只是主库改为只读后，不等待备库同步完数据，就切到备库，此时数据会不一致，后面再自己根据bin log手动调整更正
MySQL的高可用（通过主库发生故障时切到从库），是依赖主备复制的，主备延迟时间越小，可用性越高
将一张表里的数据导出到文件，再写回原表，以下两种方式都可以跨引擎
使用mysqldump，将表里的数据转成insert语句
导成CSV文件，在select语句后面加上into outfile `filepath` ，使用load data infile `filepath/filename` into table $db.$table，该语句也会被传到备库，如果备库没有该文件就会报错，因此在执行完该语句后，还要再执行load data local infile `filepath/filename` into table $db.$table，另外，该语句不会导出表结构，表结构需要另外导出
主数据库负责写，从数据库负责读，从而缓解锁的争用、节约系统开销，提高并发量
读写分离常用代理方式来实现，应用层不需要感知后端的MySQL集群部署结构，直接访问代理层，代理层接收应用层传来的读写请求，然后决定转发到哪个MySQL
由于主从库之间可能发生主备延迟，导致在查从库的结果会慢于主库，解决方法：
将查询请求分类，对查询结果严格的请求直接发到主库上
查询前先进行sleep操作（性能不好）
等主库位点方案：在从库使用命令 select master_post_wait(file, pos[ , timeout])，参数file和pos指主库上的文件名和位置，timeout表示等待时间，正常的返回结果是一个正整数M，表示从命令开始执行到应用完file和pos表示的bin log位置，执行了多少事务，即如果M >=0 ，表示从库已与主库同步，可以接受查询，如果等待超过了timeout时间，就去查主库
GTID方案：与等主库位点方案类似，都是要知道命令执行过程中事务执行的数量，使用命令 select wait_for_executed_gtid_set(gtid_set, 1)，参数gtid_set表示从库执行的事务是否包含该gtid_set，包含返回0，说明主从已同步过，可以执行查询，超时返回1，超时则查主库。该方案的难点在于gtid_set的获取，需要修改MySQL代码，让其在事务提交后返回gtid值
分区表
建立分区表语句，demo是以范围做分区的，也可以使用hash分区、list分区
分区表会按分区存储在对应的文件里，第一次访问这张表时，MySQL会对所有分区表的文件进行打开操作，但是打开文件的个数是有限制的，如果分区表太多，会导致SQL语句无法执行
分区表对于server层来说是一张表，DDL操作时会对所有分区表上锁，导致后面落到具体分区表的语句阻塞
分区表对于引擎层来讲是多张表，因此在进行一些DML语句时，只会在对应分区表加间隙锁，不会影响其他分区表
alter table $tableName drop partition xxx用于删除分区表，与drop 整张普通表类似，但是速度快，因为只删除了部分数据
在跨分区查询数据时，会比普通表慢
当MySQL的查询并发数满了之后，会导致不可用，后面的操作都会被阻塞
查询检测：在系统库里建一张表，比如叫health_check，里面只放一个数据，然后定期执行 select * from mysql.health_check; 判断执行的SQL语句是否不可用，当语句超时则表示不可用
更新判断：由于当磁盘满了之后，MySQL仍然可读，但是bin log却写不进去，导致更新语句和事务 commit会被阻塞，在上面表的基础上，增加一个timestamp字段，每台MySQL的serverId作为主键插入表中，MySQL每次执行把当前时间更新到对应的行上，update mysql.health_check where server_id=xx set t_modified = now(); 主备都需要开启检测。当语句执行超时时表示不可用
由于每个执行请求都有可能获得IO资源，所以有时检测请求执行成功了，但是此时系统资源即将被耗尽了，已经可以进行主备切换了，但是仍然要在下次检测才能知道，因此需要判断多每次IO请求的时间，通常是检测(select)performance_schema表的信息
MHA是一套保证MySQL实现故障切换和主从提升的高可用解决方案，故障切换时能在30s内自动完成，最大程度的保证数据一致，支持跨存储引擎，对原有MySQL库无性能影响，整个故障转移过程对应用程序完全透明。
MHA里有两个角色一个是MHA Node（数据节点）另一个是MHA Manager（管理节点）。
MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。
MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。
MHA方案采用半同步复制的方式，所以MySQL的版本要求至少是5.5，半同步复制机制保证master出问题的时候，至少有一台slave的数据是完整的，但在超时的情况下也会临时切换异步复制，保障业务的正常使用，直到一台slave追上同步进度，再继续切换成半同步复制模式。
MHA还可以修复多个slave之间的日志差异，使得所有salve的数据保证数据最终一致性，保证至少可以从中选出一个master。
MHA验证复制设置以及确认当前master状态
监控master，检测到master宕机
再次验证slave的配置
开始恢复一台新master
从宕机的master节点上通过SSH保存其bin log到Manager
根据配置文件来选举出新的master，或者将含有数据同步最新位点的slave提升为master
根据老master bin log生成差异日志，应用到新master上
将其他的slave连接到新的master进行复制
MHA验证复制设置以及确认当前master状态
确认新的master
停止当前master的写操作
等待其他slave追上当前master，直到数据复制进度无延迟
确保新master可写
让其他slave指向新master
自增id不一定是连续的，可能会产生空洞；比如 插入操作出现唯一键冲突，自增值也会+1；事务回滚时，自增值不会回滚；
MyISAM自增id的下一个值是存在表结构里的，InnoDB是放内存的，在MySQL 8.0以前，自增值并不会持久化到磁盘，每次重启后，自增值会被清空，在第一次读表的时候会把最大id给读出来+1，达到恢复原来的自增值；8.0后是记录在redo log里，重启后通过持久化的值来恢复；
自增的两个重要参数auto_increment_offset和auto_increment_increment都是系统参数，默认值为1；
理论上自增id是无限的，但是因为字段的类型已经限定了最大的位数，比如如果id使用unsigned int是4个字节，上限就是2^32 -1，当达到上限后，自增值不变，就会导致报重复主键的错，所以如果表的上限需要比较大，需要设置成unsigned bigint；
如果没有明确设置主键，innoDB会默认给一个row_id，虽然实际上是一个unsigned bigint，但是只用到了6个字节，所以长度是0 ~ 2^48-1，子增值达到上限后的下一个值是0，此时再insert会覆盖原有的行；
当产生唯一键冲突时，除了会报错，还会加锁（主键+行锁，普通索引+间隙锁），然后在回滚时才释放，当出现唯一键冲突时，如果有多个事务同时插入，容易造成死锁（双方都在等待对方的间隙锁释放导致）；
insert into ... on duplicate key update ... ，表示插入一行数据，如果出现唯一键冲突，就执行后面的update语句，该更新语句只会修改跟第一个索引冲突的行；
Xid是 server层维护，表示一个事务id，存在bin log中，可以作为bin log和redo log中同一事务的关联id，让innoDB事务和server层做关联。
由全局变量global_query_id赋给Query_id，Query_id+1后赋值给Xid，作为事务开始的第一条语句的id。
由于global_query_id是内存变量，MySQL重启后会被清零，但是重启后会生成新的bin log，所以同一bin log不会出现两个相同的Xid
global_query_id达到上限2^64 - 1后，会从0开始计数，所以同一数据库里可能同时存在相同的Xid
trx_id不同于Xid，trx_id是由InnoDB维护，用在事务可见性方面的，比如MVCC，视图一致性
普通只读语句不分配trx_id（而是临时算的，会比较大，主要是为了区分读写语句），读写语句才会分配 trx_id，由max_trx_id + 2(至少，比如update，实际上要先当前读在update的，所以是+2)
max_trx_id会持久化，重启不会变0，上限是2^48-1，然后从0开始计数
系统保存全局变量thread_id_counter，每新键一个连接，thread_id_counter + 1后赋给thread_id，作为线程id
上限是2^32-1，达到上限后重置为0
SUN的 JDBC 是一套接口，而实现是各个数据库厂商的驱动包，因此使用了桥接模式
DriverManager注册驱动包，com.mysql.jdbc.Driver类中的static块会创建驱动实例，因此只需要把驱动字节码加载到JVM里即可，Class.forName(“com.mysql.jdbc.Driver”);
Connection conn = DriverManager.getConnection(url, username, password)获取连接
Statement stmt = con.createStatement(); 之后使用stmt的方法执行SQL语句即可，返回ResultSet
ResultSet下标从1开始
使用Connection类的setAutoCommit(false) 方法来实现事务，以这个开始，Connection类的commit()方法提交，Connection类的rollback()方法回滚
最后关闭ResultSet、Statement和Connection
链表实现，在使用连接对象之前，先创建好一定数量的连接对象，以链表的形式连接，从端首取，用完回到段尾。
当池子中没有连接对象可取时，就让其先等待，如果等待超时还没有回获取到连接对象，就新建一个连接对象让其使用，用完后销毁该创建的对象
连接池负责管理、监控和维护这些连接对象
连接池单例
连接池需要保证线程安全
后记
极客时间 - MySQL实战45讲真的是质量很高的讲MySQL的课程，非常推荐
