mysql学习记录

一、SQL执行流程

二、索引

聚集索引：索引键值的逻辑顺序跟表数据的物理存储顺序一致的（顺序查找）（主键索引）

辅助索引：除主键索引外的索引（普通索引）

上述图中，从辅助索引到主键索引的过程称为回表

a. 如查询 select *from 表 where name = 'Q'，会先在辅助索引查找name ='Q'值，然后再根据主键查询主键索引对应的叶子节点，即回表

b. 若select name from 表 where name = 'Q'，则只需要在辅助索引里查询，不用在主键索引查询，这种方式叫做覆盖索引（查询的字段全部在辅助索引里）

b. 如果只根据主键查询，则只会查询主键索引

InnoDB以主键索引来组织的

疑问：可能没有聚集索引？

答：不可能，如果没有；

a.会选择不包含null的unique key作为聚集索引

b.选择隐藏的rowid作为聚集索引

不建议以离散度低的字段为索引

复合索引（联合索引）：从左开始匹配，如果首个没有，则不走索引，如下：

建立index(user,age)索引

a. select * from 表 where user = 'a' and age = '16'  走索引

b. select * from 表 where age = '16' and user = 'a'  走索引（优化器会优化成a式）

c. select * from 表 where user = 'a'  走索引(最左匹配)

d. select * from 表 where age = '16'  用不到

三、索引采用的数据结构

演进：二叉查找树（左子树<根节点，右子树>根节点），缺点：会成斜树，高度过高

           平衡二叉树，缺点:单个节点存储的数据太少了

            多路平衡查找树（B树）

            加强版多路平衡查找树（B+树），和B树的区别是，B+树的数据放在叶子节点

innoDB索引选择的数据结构为B+树，其优点:扫库、扫表更强，磁盘读写更强，排序更强（叶子接节点有指向下个节点的指针），每次查询的效率更加稳定（每次IO次数稳定）

四、mysql事务

A  原子性  如银行转账要么全部成功，要么全部失败；通过undolog解决

C  一致性   如银行A转出1000，银行B转入500，不一致；其余三种最终目的是为了解决一致性问题

I   隔离性   并发，多个事务操作同一张表，不存在干扰；通过加锁（LBCC）和MVCC解决

D  持久性   永久的保存在磁盘上；redolog和双写缓冲

事务并发三大问题

脏读               事务一读，事务二更新未提交

不可重复读    事务一读，事务二已提交（更新、删除操作）

幻读               事务一读，事务二insert，且已提交（InnoDB的临键锁解决该问题）

事务隔离级别的支持程度

   

事务隔离级别解决方案

1.加锁

2.生成快照，即：Multi Version concurency control（MVCC）,原理每行数据新增两个字段DB_TRX_ID（插入或更新行的最后一个事务ID），DB_ROLL_PTR（回滚指针），利用这两个字段判断，即只能查询到创建时间小于等于当前事务ID的数据和删除时间大于当前事务ID的行（未删除）

InnoDB锁（或者说是锁的算法）

共享锁和写锁（更新、插入和删除自动加上）

记录锁（record lock）、间隙锁（gap key lock）（区间左开右开）、临键锁（next-key lock）（区间左开右闭）

锁是作用是解决并发访问数据的问题

锁是锁住索引（主键索引，辅助索引）

四种事务隔离级别与事务并发带来的问题的关系

RU    =>  脏读，不加锁

RC    => 不可重复读，解决RU

RR    =>幻读 ,解决RC

Serializable  解决所有

五、几个相关日志

a. redo log

     解决持久性问题，即：避免系统反生奔溃带来的数据丢失问题，在存储引擎层，将随机IO换成顺序IO，提升效率

b. undo log

     解决原子性问题，即：可恢复事务前的一个版本，在存储引擎层

c.  bin log

     实现主从数据库之间的数据同步以及数据库的全数据恢复，在server层

注：redo log 需要两段式提交，即prepare 和 commit，在binlog刷盘前，将redo log的事务状态设置为prepare，在binlog刷盘后将其事务状态设置为commit。保证binlog和redolog的数据一致性。

疑问一：为什么要两段式提交？

答：目的就是保证数据的一致性，redolog保证主库数据丢失后的恢复，binlog保证主从复制后从库的数据。假设mysql宕机后恢复，查看redolog是否从刷盘，如果失败则回滚，如果redolog刷盘了但是binlog没有内部事务XA的XID，代表binlog还没刷盘，则还是回滚。如果binlog有内部事务XA的XID，则代表两者都成功刷盘了。

疑问二：为什么redo log两段式而不是bin log两段式？

猜测：bin log要是两段式那么要进行整个分布式回滚就不好办了

疑问三：操作数据刷盘时，为了提升性能，增加了buffer pool，为何还要再增加一个redo log，redo log 不也要刷盘吗？

答: 增加一个redo log和刷盘时带来的消耗并不矛盾，增加redo log的好处是系统奔溃时可以恢复数据，提高系统的健壮性。而redo log也要刷盘，但它的刷盘时顺序IO而不是随机IO。

其中server层还有其他的日志，比如：

• 错误日志（error log）：错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录，能帮助定位 MySQL 问题
• 慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化
• 一般查询日志（general log）：一般查询日志记录了所有对 MySQL 数据库请求的信息，无论请求是否正确执行