2022.12.4 pm

【MySQL实战45讲】

第三讲：

MySQL的默认隔离级别是“可重复读”。

四种隔离级别在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”(Repeated Read)隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”(Read Committed)隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。而在”读未提交”(Read Uncommitted)隔离级别下是不用视图的，直接返回记录中的最新值。“串行化”(Serializable)隔离级别下直接用加锁的方式避免并行访问。

建议不使用长事务，长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

建议使用set autocommit = 1, 通过显式语句的方式来启动事务。在autocommit为1的情况下，用begin显式启动的事务，如果执行commit则提交事务。如果执行commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行begin语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

另外来解释一下事务的四大特性：①原子性（事务是一个不可分割的工作单位，事务中的操作要不就全都成功，要不就全都失败），②一致性（事务要按照预期生效，数据的状态是预期的状态，比如我给你转了100元，我转出去了你没有收到，这就没有达到一致性），③隔离性（多个用户并发访问数据库时，数据库为每一个用户开启的事务不能被其他事务的操作所干扰，多个并发事务之间要相互隔离，这也产生了四种隔离级别），④持久性（一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响）。

第四讲：

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。那么索引的常见模型有：哈希表、有序数组、搜索树。在MySQL中索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

根据叶子节点的内容，索引类型分为主索键引和非主键索引。主键索引的叶子节点存的是整行数据。非主键索引的叶子节点存的是主键的值。在InnoDB中，主键索引也被称为聚簇索引，非主键索引也被称为二级索引。

主键查询方式只需要搜索主键索引这一棵B+树，非主键查询方式则需要先搜索非主键索引树，得到主键的值，再到主键索引树搜索一次，这个过程称为回表。 也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此在应用中应该尽量使用主键查询。

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。自增主键的插入数据模式，每次插入一条新记录都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。并且主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

不过有些场景是适合用业务字段直接做主键的：①只有一个索引，②该索引必须是唯一索引。这就是典型的KV场景。由于没有其他索引所以也就不用考虑其他索引的叶子节点大小的问题。这时候我们就要优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

第五讲：

一些索引优化的方法：覆盖索引、前缀索引、索引下推。

如果执行的语句是select ID from T where k between 3 and 5（ID是T的主键），这时只需要查ID的值，而ID的值已经在k索引树上了（因为非主键索引的叶子节点存的是主键的值），因此可以直接提供查询结果，不需要回表。也就是说在这个查询里面索引k已经“覆盖了”我们的查询需求，称为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。当然索引字段的维护总是有代价的，因此在建立冗余索引来支持覆盖索引时就需要权衡考虑。

最左前缀原则：B+树这种索引结构，可以利用索引的“最左前缀”来定位记录。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。因此如果通过调整顺序可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。那么如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、 (b) 这两个索引。这时候我们要考虑的原则就是空间了。比如name字段比age字段大，那就建议你创建一个(name,age)的联合索引和一个(age)的单字段索引。

MySQL 5.6 引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。在MySQL 5.6 之前，只能从索引中（可能先从索引中通过最左前缀原则等方法过滤了一些记录）一个个回表，到主键索引上找出数据行，再对比字段值。

第六讲：

根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁就是对整个数据库加锁。MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令。之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是做全库逻辑备份，也就是把整库每个表都select出来存成文本。

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数--single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。但前提是引擎要支持这个隔离级别（可重复读）。比如对于MyISAM这种不支持事务的引擎，如果备份过程中有更新，只能取到最新的数据，那么就破坏了备份的一致性。这时我们就需要使用FTWRL命令了。所以--single-transaction方法只适用于所有使用支持事务引擎的表。如果有的表使用了不支持事务的引擎，那么备份就只能通过FTWRL方法。

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（MDL）。

表锁的语法是lock tables …read/write。与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables除了会限制别的线程的读写，也限定了本线程接下来的操作对象。在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

在MySQL 5.5 版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁。当要对表做结构变更操作的时候，加MDL写锁。MDL不需要显式使用（默认使用），在访问一个表的时候会被自动加上。MDL的作用是保证读写的正确性。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。另外，事务中的MDL锁在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。因此在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

【刷贪心题目】

1.分发糖果（135题）：没通过。（反方向遍历的时候写的有点问题。）

正向遍历，右边比左边评分高就让右边的多一个糖果。反向遍历，左边比右边评分高的话，这时就有两个选择了，一个是candys[i + 1] + 1（从右边这个加1得到的糖果数量），一个是candys[i]（之前比较时右边大于左边得到的糖果数量），要取二者的最大值。

2.柠檬水找零（860题）：通过。

3.根据身高重建队列（406题）：没通过。（大致思路有，细节没写好。）

首先遇到两个维度权衡的时候，一定要先确定一个维度，再确定另一个维度。首先按照身高从大到小排序之后，新建一个数组，优先按身高更高的人的k值来插入，后序插入节点也不会影响前面已经插入的节点。

不过用vector频繁插入是很费时的，所以可以用list。注意找插入位置时候while (position--) {it++;} 用迭代器。