一、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 数据库请求的信息,无论请求是否正确执行