第五讲-数据库
1.sql执行步骤
image.png
1.数据已经在内存中（buffer pool），直接查询。如果所在页不在内存中而且buffer pool空间不足了，会淘汰最久没用到的数据页，如果此数据页是脏块，会强制刷盘。这个时候会看到查询sql变慢
2.mysql执行计划可能选错，比如mysql5.6版本针对order by limit的字段会选为索引字段进行查询，但是该字段的索引并不一定是最合适的。编写sql的时候如果用到了order by字段建议强制提示使用XX索引
2.索引
2.1索引是一个B+树，B+树相对平衡树高度低（IO少），相对B-树（非叶子节点存储数据）支持叶子节点范围查询。
2.2 适合创建索引的字段：经常查询的区分度高字段，排序字段，外键
2.3 索引失效：左前缀法则，索引列进行函数运算或隐式转换，使用不等于条件不能走索引，字符串不加单引号，少用or
2.4 聚簇索引和非聚簇索引，覆盖索引规避回表
2.5 自增主键索引的好处：1.新增不会触发索引页分裂（分裂影响效率和空间利用率），只是在后面追加，申请新的页。2.自增主键值比较小，普通索引存储起来节省空间
2.6 唯一索引和普通索引选择。 普通索引可以使用change buffer（update的时候，如果数据没在内存，从磁盘读，修改，写回磁盘。如果使用change buffer，直接内存记录变更，不读磁盘和写磁盘，下次读取的时候从磁盘读和变更记录合并就是这条记录的最新数据），唯一索引不可用使用。在更新很多读很少的表，不建议建立唯一索引，建立普通索引即可。redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。
2.7 索引选择是基于代价，扫描行数，排序字段等。有可能选择错误
2.8 前缀索引，节约空间，但是会增加扫描次数，不能用覆盖索引
3.order by优化
1.规避filesort，排序字段非主键的时候，先根据排序字段查出主键id，再根据主键id查询其他字段
2.一定要filesort，调优sort_buffer_size字段，规避数据进行分段排序。max_length_for_sort_data字段，尽量使要排序的字段和需要查询的字段一次查出，而不是排序好排序字段后再回表。
4.隔离级别
读未提交，读已提交（不可脏读），可重复读（同一事务中多次读取同样的记录结果是一致的，重复读），可序列化（在读取的每一行数据上加锁，强制事务串行执行，不可幻象读，幻读是重复读一个区间，多出来了一些数据）
事务隔离的实现：加锁和MVCC
4.1锁类型
4.1.1 自增锁
每次插入动作都要获取自增锁，严重影响性能，mysql5.1之后对于普通insert语句自增锁每次申请完成后立即释放
4.1.2 共享锁和排他锁
事务拿到某一行记录的共享锁，才能够读取，提高读并发
事务拿到某一行记录的排他锁，才能修改，保证数据强一致
4.1.3 意向锁
未来某个时刻，事务要加共享/排他锁，提前声明个意向
意向共享锁（IS）:事务有意向加共享锁
意向排他锁（IX）: 事务有意向加排他锁
各个锁兼容逻辑：
image.png
4.2 锁算法
4.2.1 记录锁
单个行记录上的锁，锁住索引记录。查找或者更新的应该是唯一的一行
4.2.2 间隙锁
锁定一个范围，但不包含记录本身（针对RR及以上隔离级别），解决幻读。mysql默认隔离级别是RR，因为RR会加间隙锁，可以保证statement语句的binlog日志同步到从库不会导致主从不一致。因为RC隔离级别删除id<5的记录不会加间隙锁，事务1先删除不提交事务，事务2插入id=3的记录可以成功提交事务，事务1最后提交。最终主库无id=3的记录，从库有。
4.2.3 临键锁
是记录锁和间隙锁的组合。
为了避免幻读，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）
4.2.4 插入意向锁
是间隙锁的一种，专门针对inset。同一索引同一区间范围内插入记录，插入的位置不冲突，不会阻塞彼此，提高插入并发。
插入意向锁经常和间隙锁引发死锁问题，死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象
4.2.5 死锁
死锁检测非常消耗cpu，并发更新某一行的的时候，关闭死锁检测比较有效
如何规避死锁
1.已固定顺序访问表和行
2.大事务拆小，锁尽快释放
3.降低隔离级别，比如RR改为RC
4.表添加合理索引，更新或者删除的条件一定要有索引
4.2.5 元数据MDL锁
对表数据操作的时候会加mdl读锁，对表结构修改的时候会加mdl写锁。读读不互斥，读写和写写互斥。这样会导致一个问题，修改表结构的时候，如果有大事务正在执行（加了读锁），修改表结构的事务会阻塞（需要获取写锁）。其他事务来读数据的时候，需要获取mdl读锁，前提是无等待的mdl写锁，发现有mdl写锁于是等待表结构变更完成后再读。
4.2.6 加锁规则
image.png
关于原则1：加锁next-key其实是先加间隙锁，后加行锁。有可能间隙锁加成功了，但是行锁失败了。很多死锁原因是两个事物都加间隙锁成功了，但是行锁失败了。
原则2：查找过程主键索引树可能不会加锁，覆盖索引的时候不会访问到主键索引树。
5.MVCC
多版本并发控制，主要用于快照读，不需要读取当前最新的数据。用在RR和RC隔离级别
快照读实现原理，数据库创建一个视图，访问的时候以视图的结果为准。可重复读的时候，视图是事务启动的时候创建的，整个事务都使用这个视图。读提交的时候，视图是sql语句开始执行的时候创建的。串行化是加锁规避并发访问，无视图。
视图创建的原理，依赖回滚段
image.png
如果有大事务，会导致很大的回滚段，数据库压力过大
一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
1.版本未提交，不可见；
2.版本已提交，但是是在视图创建后提交的，不可见；
3.版本已提交，而且是在视图创建前提交的，可见。
6.binlog日志
statement-base：记录的是sql，日志文件较小，隔离模式只能是RR
row-base: 记录所有变更的数据，日志文件大。准确性高。隔离模式可以改为RC
binlog 与redo log区别：
redo log是innoDB独有，binlog是mysql server的实现，所有引擎都可以使用
redolog 是循环写，binlog是追加不覆盖
两阶段提交，保证redo log 和binlog都写入成功，保证主库和从库数据始终一致性。
redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证MySQL异常重启之后数据不丢失。
sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失。
QA
1.一个查询查出来的数据要放入buffer pool中，buffer pool满了之后需要淘汰一些最久未使用的缓存，如果发现缓存是一个脏块，得刷到磁盘。刷到磁盘的过程会阻塞查询。
- count() 需要从引擎读到一行行数据，server层进行统计。 但是不是读真正的数据，就是一行数据一个标记。 count(1)与count()类似，也不读数据。count(字段)，需要读数据了，解析不为空才累加。性能：count(字段)<count(主键id)<count(1)≈count(*)
- 主从不一致如何解决，1.强制读主库 2.等待主库点位，主库执行完事务之后查询点位，从库查询相应点位是否已执行，执行完成后进行sql执行。
4.join 操作驱动表是小表，被驱动表有索引，走Index Nested-Loop Join(NLJ)无性能问题。如果被驱动表无索引，走Block Nested-Loop Join(BNL)，性能会比较差，因为两个表的数据都需要加载到内存，如果内存不够（参数join_buffer_size控制），需要分块处理。
5.临时表，a.union会利用临时表上创建唯一索引去重，所以尽量用union all。b.group by的字段无索引会使用临时表（tmp_table_size，默认16M）做计次，然后按照统计字段排序返回，如果不需要排序order by null，如果数据量的确很大，不使用内存临时表，直接使用磁盘临时表hit提示SQL_BIG_RESULT，不让mysql会傻傻的先放内存，内存放不下才使用磁盘临时表。sql执行计划最好没有Using temporary 和 Using filesort
6.自增主键不连续：1.唯一索引冲突插入失败，但是id已经申请完成。2.事务回滚。3.批量插入sql语句，每执行一个insert会申请倍数的id，比如第一个sql执行完成执行第二个的时候申请2个id，第三个sql执行完成后申请4个id，会造成id浪费从而不连续。 - 自增锁，innodb_autoinc_lock_mode 0语句执行完成才是否自增锁，1.普通insert语句执行完成立刻释放，insert....select语句执行完成后才释放（保证数据一致性，因为binlog日志如果是statement，语句还没执行完成就释放会导致同一行主从id不一致）2所有语句都是执行完成就释放
8.自增id用完之后的表现：
表的自增id达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
row_id达到上限后，则会归0再重新递增，如果出现相同的row_id，后写的数据会覆盖之前的数据。
