索引原理
B+树结构
B+树的本质解决树的查询性能问题（深度、顺序），结构让树的深度小，减少磁盘IO，提高性能，同时叶子结点的双向链表的有序性对范围查询、limit等提供很好支持。
如上图所示，仅三层的树结构就可以存储超过2000W条数据。
比如以bigint为主键索引，其大小为8B，下一页索引地址大小为6B
那么16KB索引页可以存放 16K/(8+6) = 1170
另外假设数据内容大小为1K，叶子节点可以存16条数据，那么三层的索引叶可存储 1170 * 1170 * 16 = 2190W+条数据。
即3层的B+树索引结构就可以存储超2000W条数据
执行该命令，可以查询默认的Innodb数据页大小为16KB：
show global status like 'Innodb_page_size' -- 16KB
相比于B树索引，B+树索引的好处
- 结构区别：B树在每个结点都存储数据记录；而B+树仅在叶子结点存储数据记录且叶子结点是链表结构，是B树的加强版
- 叶深度不同，B+树只在叶子结点存储数据记录，索引所占硬盘空间小，自然可以存储更多索引，所以树的深度小
- B+树叶子节点是双向链表，对范围查询、Limit查询有更好的支持
相比于Hash索引，B+树索引的好处
- 在InnoDB存储引擎中，索引结构可以选择Hash结构，如果是等值查询，Hash索引性能更快
- 但Hash索引是无序的，B+树叶子节点是双向链表且有序，对范围查询、Limit等查询有更好的支持
Innodb与MyISAM
Innodb数据的磁盘文件
- xxx.frm（frame，即表定义信息）
- xxx.ibd（B+树的数据和索引文件）
MyISAM的磁盘文件
- xxx.frm（frame，即表定义信息）
- xxx.MYD（MyISAM-Data，数据内容数据）
- xxx.MYI（MyISAM-Index，B+树索引数据，叶子节点存储数据硬盘地址） MyISAM的主键索引虽然也是B+树结构，但不是聚簇索引，因为叶子结点仅存储了数据地址。
聚簇索引与非聚簇索引
聚簇（集）索引即叶子结点包含了数据的完整记录，索引跟数据记录保存在一起，比如Innodb的主键索引。
MyISAM主键索引、Innodb二级索引等都是非聚簇索引。
非聚簇索引一般要做一次回表的操作，需要根据非聚簇索引的叶子结点找到主键ID，然后根据主键ID去查找聚簇索引。但如果非聚簇索引可以查找出自己想要的结果，则不需要回表。
联合索引
联合索引结构如下图所示：
联合索引结构的有序性保证了联合索引的最左前缀原则。
回表：叶子结点存的是主键ID，然后根据主键ID再去主键索引查找数据内容
覆盖索引：如果覆盖索引中，叶子结点的数据已经满足查询需求，则不再需要回表查询。
Explain
id列
id列一定程度上表示了查询执行顺序，id越大的越先执行。id相等时，从上到下依次执行。
select_type列
表示对应行是简单还是复杂查询
- simple：简单查询
- primary：复杂查询中最外层的select语句
- subquery：包含在select中的子查询（不在from语句中）
- derived：包含在from中的子查询，mysql会将查询结果存放在一个临时表中，也称为派生表（derived的英文含义）
type列
这一列表示访问类型，即MySql决定如何查找表中的行，查找数据行的大概范围。
从优到差排序：system > const > eq_ref > ref > range > index > ALL
一般至少保证查询达到range级别以上。
- syste,const：mysql对部分简单查询优化成常量级别，比如primary key或unique key与常量等值查询最多只有一条结果。sysyem是const的特例，比如所查询目标表只有一条数据。
- eq_ref：联查时主键关联。simple查询主键不会返回该级别。
- ref：使用普通索引或索引前缀。
- range：索引范围查找。
- index：扫描索引来拿到结果，全表扫描，只不过扫描的是索引。比如仅查询二级索引就可以查找到结果，即覆盖索引，单它是遍历索引，将索引的叶子结点内容都查询出来，它的效率并不高。
- all：全表扫描聚簇索引，因为聚簇索引保存了数据所有字段，硬盘读取内容比index多，效率最低
possiable_keys与key列
即可能用到的key与实际用到的key
key_len
用到的索引字段长度，尤其是对于联合索引，比如用到联合索引最左前缀的多少长度。
还有些其他的列看字面意思或者感觉不重要就忽略了～
mysql组成
这部分了解就行～
存储引擎层数据处理步骤
当有数据写入请求时：
- 从硬盘文件加载数据到内存中（buffer-pool）
- 写入undo log，便于数据回滚
- 更新内存(buffer-pool)数据
- 准备提交事务，写入redo log日志到硬盘，保证crash-safe
- 准备提交事务，binlog日志写入硬盘
- 提交事务，写入commit标记到redo log。即所谓两阶段提交，保证redon log与binlog数据一致
- 系统空闲时，将buffer-pool中的数据回写到硬盘中
mysql日志模块
binlog
mysql server层归档日志，主要用于数据恢复、主从同步。
redo log
redo log是InnoDB日志。主要用于提高写性能以及crash-safe。
比如当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存（buffer-pool），这个时候更新就算完成了。同时InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面。
有了redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。
redo log保证了磁盘中的数据与内存（buffer-pool）中的数据保持一致，比如发生宕机重启时，redo log中的内容会重新刷新到buffer-pool中的数据。
之所以引入redo log机制，本质上是为了解决性能问题，通过buffer-pool与redo log的组合使得sql命令迅速得到相应。另外对于redo log的读写是顺序IO（顺序IO性能远高于随机IO，近似内存操作），对于数据文件的读写是随机IO。
redo log空间大小是有限制的。
undo log
事务版本链，用于回滚和事务隔离。
事务
事务隔离级别
- 读未提交，产生问题：脏读，事务A读取到事务B已经修改但尚未提交的数据。
- 读提交，产生问题：不可重复读，事务A在不同时刻查询同一内容时，结果不同，不符合隔离性。
- 可重复读，产生问题：幻读，事务A可以读取事物B读取新增的数据，比如导致count(*)数量前后查询不一致
- 串行
锁
锁分类
- 从性能上分为乐观锁（乐观锁由自己程序实现，比如加version字段）和悲观锁
- 从操作类型分为读锁和写锁。
- 从数据粒度分为表锁（很少用，一般用于整表数据迁移）、行锁和间隙锁
读锁与写锁
读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行，互不影响
- 示例
select * from table_a lock in share mode
使用场景：读出数据后其他事务不能修改，但自己也不一定能修改，因为其他事务也可以加读锁。主要用来保证数据在本事务内值不会变。但实际开发中用得比较少。
写锁（排他锁，X锁(eXclusive)）：当前写操作未完成前会阻塞其他写锁和读锁
- 示例
select * from table_a for update 以及 DELETE/UPDATE/INSERT（插入后未提交的数据也会加写锁）
使用场景：只有本事务才可以修改这些数据，其他事务加锁会阻塞。一般开发过程中大部分接触到的锁都是写锁。
间隙锁（Gap Lock）
间隙锁就是锁住两个值之间的空隙，在mysql默认的可重复读隔离级别下，某些情况下是可以解决幻读问题。
- 示例
update user set name = 'aa' where id >5 and id <10;
则其他事物没法在 (5,11]内修改，包括行和行之间的间隙。这里不必过多纠结了，没啥意思。。。
无索引时行锁会升级为表锁
InnoDB的行锁时针对索引加的锁
MVCC
Multi-Version Concurrency Control，多版本并发控制，InnoDB在读已提交、可重复读隔离级别下实现了MVCC机制。
undo回滚日志版本链，以及事务第一次查询会生成一致性视图。事务中的查询按照版本链依次与视图比较，确认是否已提交，跟事务ID有很大关系。
mysql实用命令
查询当前实例有多少连接，各连接正在处于什么状态：show processlist
关闭指定连接：kill 43，其中数字是processlist中的id字段
查询事务隔离级别：show variables like 'tx_isoation'
查询page_size：show global status like 'Innodb_page_size
行锁竞争情况：show status like innodb_row_lock%
查看死锁日志：show engine innodb status
