MySQL45讲第1篇-第14篇读书笔记

EyeDropLyq

于 2023-05-10 20:16:56 发布

阅读量153

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/lyq1525761478/article/details/130608956

版权

MySQL45讲读书笔记

第一天

今天简单的学习了两篇文章

第一篇：基础架构：一条SQL查询语句是如何执行的？

先来说一下看第一篇文章的一个读书笔记：

在这篇文章中，我学到了一条简单的查询语句的执行流程，虽然我之前在盖哥的指南中学习过，但是这次系统读书嘛，所以还是要写一下自己的读书笔记的。MySQL数据库可以分为两层第一层是server层，另一层就是插件式结构的存储引擎层。

核心是server层，在这个层中有连接器、分析器、查询缓存、优化器、执行器

连接器：管理客户端发过来的连接，维护和管理这个连接，以及做权限管理，当我们连接的时候需要输入用户名和密码，然后根据这个用户名和密码去权限表中查询你拥有的权限，然后这个权限会用在下面的所有操作中。

在这里要注意一下长连接和短链接：长连接就是客户端持续有请求，多个查询都是用的这一个连接，不会重复建立连接，建立连接的过程是很消耗资源的。

短链接的话就是每次执行完很少的几次查询之后就断开连接，下次查询就建立一个新的连接。所以为了提高性能的话，应该使用长连接而不是短链接。

但是使用长连接也会有一个问题就是可能会导致MySQL异常重启，那么为什么会发生这个异常重启呢？因为使用长连接的话我们的临时内存会管理在这个长连接中，这个长连接如果执行的操作比较多，那么内存累积的就会很大，会被系统强行杀掉（OOM），所以发生了MySQL的异常重启。

为了解决这个问题可以定期关闭长连接，然后重写建立一个长连接，主要是释放这个长连接中积累的内存。如果使用的是MySQL5.7或者更高的版本的话，可以在执行一个比较大的操作之后，执行mysql_reset_connection来重新初始化连接资源。

查询缓存：可以理解为将查询语句作为key，将结果作为value来保存这个key-value键值对。但是查询缓存的命中率很低，查询语句中甚至多了一个空格都会认为是不同的语句。而且一旦我们对一个表进行了更新操作，那么我们之前缓存的这个表的查询语句都会被清空了。现在在MySQL8.0之后这个功能被移除了，很鸡肋。

分析器：对我们客户端发过来的sql语句进行一个词法和语法的检查。我们的sql语法错误异常的发生都是出自这里，都是在这里被发现的。通过分析器MySQL可以知道我们要干啥，所以才能进行后面的优化以及执行。注意如果没有某列的话也是分析器这里发现的。

优化器：如果我们这个表中有多个索引或者我们的语句中涉及到了多表联查的join语句的话，是通过这个优化器进行一个优化选择，选择使用哪个索引，以及选择多表联查中各个表的一个查询顺序。优化器最后会生成一个执行计划，然后将这个执行计划交给执行器去执行。

执行器：在执行之前会先进行一个权限判断，判断你是不是有对应的操作权限。如果有的话会去调用存储引擎的读写接口。如果没有的话被报错。在实际的工程中，如果我们一个SQL语句过来后查询缓存命中了，那么在将这个结果返回之前做一个权限认证，有权限才给返回，没有的话不返回。查询也会在优化器之前进行权限的判断，其实在执行器之前就会进行一个权限的判断。

第二篇：日志系统：一条SQL更新语句是如何执行的？

在这篇文章中，主要学到了MySQL中的两种日志，第一种是redo log日志第二种是binlog日志

redo log日志：这个日志会讲更新的操作先记录在redo log日志中，而不是直接进行更新数据，主要是为了提高速度的，如果我们每一次更新都是直接操作数据库的话，那样子的话会每次都去磁盘中先查到这个数据，然后再进行一个更新，相对来说这个速度是很慢的，开销也是很大的。但是如果我们先把更新记录在redo log日志中，然后在相对比较空闲的时候去数据库中更新数据的话，那么我们系统的性能和开销是不是就会小点。

redo log也是有刷盘策略的，建议把刷盘策略调整为1，意思是每次提交事务的时候都会讲redo log日志中的内容刷到磁盘中进行一个持久化。这样的话即使没有刷盘的时候宕机了，那么我们也只会损失一个事务的数据。这个参数是innodb_flush_log_at_trx_commit

而且也要注意redo log日志是只有innodb引擎有的，记录在哪个数据页做了什么修改。

还要讲一下redo log进行写数据的过程，redo log的大小是固定的，是一个结构，有一个write pos：写入点，还有一个check point检查点。在write pos和check pint之间的空间就是空闲的空间。那么既然是一个环形结构而且是固定大小的，那么肯定会写满的，当写满之后check point会后移，后移之前先将数据进行持久化操作，然后后移。但是如果脏页占用内存太大的话也会后移。

check point 后移的时机

binlog日志是所有的mysql引擎都有的，因为它是存在于server层的。binlog日志中记录了语句的原始逻辑。binlog的刷盘策略也建议设置为1，代表每次提交事务的时候进行一个刷盘，这样的话即使发生了宕机，也只会损失一个事务的数据。这个参数是sync_binlog=1

下面说一下一个简单的更新语句，在MySQL中是如何执行，以及如果写入这两个日志中的。首先我们客户端发过来一个SQL语句

update T set c=c+1 where ID=2

首先分析器去进行词法和语法的合法性分析。—》优化器对语句进行一个优化，包括使用哪个索引等—》执行器执行这个执行计划去调用存储引擎的对应的接口，这里会先去调用存储引擎的查询接口，因为我们的ID是一个索引，所以会通过树搜索进行查找，如果这个ID=2的数据本来就在内存中的话，直接返回给执行器。如果不在内存中的话，去磁盘中读入内存，然后返回给执行器。执行器拿到存储引擎给的数据之后，将c+1,然后调用存储引擎的写入接口。—》存储引擎将这个更新操作写入到内存中，然后将这个更新操作写入到redo log日志中，然后redo log变成prepare状态，告诉执行器，随时可以进行一个提交。—》执行器在将刚才的操作写入binlog日志中----》执行器去调用存储引擎的事务提交接口–》binlog日志写入磁盘，redo log日志写入磁盘，redo log日志变成了commit状态。这个过程中涉及到了两阶段提交。

两阶段提交就是保证redo log和binlog日志同时进行一个刷盘。如果redo log和binlog不是同时刷盘的话可能会有很多问题。比如：

1.如果先写入binlog刷盘，然后再写入redo log刷盘：如果redo log还没有刷盘的时候宕机了，那么MySQL重启之后读取redo log日志发现没有更新事务，所以读出来的c是0，但是我们的binlog日志已经记录了更新语句，所以在用binlog进行数据恢复的时候会发生多出来一个事务的问题。

2.如果先写入redo log日志刷盘，然后再写入binlog 日志刷盘:如果binlog日志还没有刷盘的时候宕机了，那么我们的这次事务的结果就不会在binlog中保存，那么我们使用binlog进行数据库恢复的时候会丢失一个事务。

在这个部分中，日志会涉及到数据的一个恢复，那么数据恢复的时候需要进行一个备份，所以这就会涉及到数据备份的时间的选择，备份的时间可以是一天一备，或者一周一备份。备份的频率越快那么我们进行数据恢复就越快，但是相对来说使用的内存就会越多。那么我们这个数据的备份时间的选择对应我们系统中的什么指标呢？对应的就是RTO(恢复目标时间)

第二天

第三篇：事务隔离：为什么你改了我还看不见？

在这篇文章中主要讲解了MySQL中的事务，MySQL中的事务隔离级别有这样的几类：读未提交、读已提交、可重复读、串行化

读未提交：当前这个事务进行了操作，还没有进行事务的提交，这个更新就可以被其他的事务看到

所以会发生脏读的问题。即：当前这个事务对数据进行了一个更改，另一个事务读取到了这个更改的数据，但是第一个事务没有提交进行了回滚，那么第二个事务读到的这个数据就是一个脏数据。

读已提交：只有当一个事务提交之后，这个数据进行的更改才会被其他的事务看到。如果没有提交之前做的所有的操作其他事务是看不到的。这个隔离级别可以防止脏读问题，但是不能防止不可重复读和幻读问题。

不可重复读就是当前这个事务在开始和结束之前读到的数据不一致。比如一个事务刚开始读取到的数据是c=10,此时另一个事务进行了操作将c改成了c=20，并且将事务提交了，那么第一个事务再次读取的时候，读取到的数据就是c=20了，就和刚开始不一样了，所以出现了不可重复读的问题。

幻读：通俗来说也是一个事务在两次读取的时候读取到的数据不一致，第二次读取多出来数据。比如：第一个读取到的数据是10行，同一个事务中第二次读取到的数据有20行，数据变多了，就发生了幻读。

可重复读可以解决脏读和不可重复读的问题，但是不能解决幻读的问题。

串行化可以解决脏读、不可重复读、幻读的问题。串行化“读”会加锁，“写”会加锁。当出现读写锁冲突的时候，后访问的事务必须等待前面的事务执行完成之后才可以继续执行。

这四种隔离级别是逐渐严格的，隔离级别越严格对应的性能越差。所以我们在设计一个系统的时候，我们要取一个中庸之道。级别既不会太高，性能也不会太差。

事务是在存储引擎层支持的，比如MyISAM这个存储引擎就不支持事务。Innodb这个存储引擎是支持事务的。

下面再来说一下事务隔离的底层的实现原理。

事务隔离底层是通过视图或者锁实现的。对于读未提交和可重复读这两种隔离级别来说是通过视图实现的，对于串行化这种事务隔离级别是通过锁来实现的。读未提交是没有视图或者锁实现的，因为它本身就无法避免任何问题的出现。

基于视图来实现事务隔离功能的在实现上会创建一个视图，访问的时候以视图的逻辑结果为准。对于读已提交这个事务隔离级别，视图是在每条sql执行的时候创建的。对于可重复读这个隔离级别，视图是在每个事物开始的时候创建的，一个事务使用一个视图。

串行化这种事务隔离级别是通过锁来实现的。

事务隔离级别的参数设置 transaction_isolation

说一个典型的可重复读这个隔离级别的场景：数据校验场景。我们可能需要对前一个月或者前一段时间的数据进行一个校验，但是不能妨碍新的操作执行，所以我们就可以使用可重复读这个隔离级别，我们可以一边进行数据校验，一边进行新的操作。

文章中还提到了可重复读这个事务隔离级别的实现流程。下面来说一下：

我们每条记录在更新的时候都会同时记录一条回滚记录。在这个记录中，通过回滚记录可以回滚到前一个位置。

同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制MVCC。

我们通过上面的图也会看到在查询这条记录的时候，不同时刻启动事务有不同的read-view。那么有一个问题了，就是我们的回滚记录不能一直存在吧，那么什么时候会进行删除呢？当系统判断没有事务再需要用到这些回滚记录的时候，这些回滚记录就会被删除。

但是系统是如何判断没有事务需要用到这些回滚记录的呢？就是当系统中没有比这个回滚记录更早的read-view的时候。

所以如果我们使用了一个长事务的话，那么我们这个事务中涉及到的所有的回滚记录都会保留，所以可能会占用大量的内存。有时候这种问题不是我们程序员有意导致的，可能是无意导致的，因为有些框架默认使用的是非自动提交，需要我们显示的提交才会将这个事务提交。

事务的启动方式

1、显示启动：begin 或者 start transaction 配套的提交语句是commit 回滚语句是rollback

2、set autocommit=0，这个命令会关闭自动提交，每次都需要我们显示的手动执行commit命令才会将这个事务进行一个提交。

3.如果set autocommit=1，这个命令会开启自动提交，当我们执行命令的时候会自动开启一个事务，当这个SQL语句执行完毕的时候会自动执行commit命令，将这个事务进行一个提交。

如果我们的一个业务需要频繁的使用事务的话，为了减少语句的交互次数，我们可以使用commit work and chain语法，这个语法可以提交事务并且开启下一个事务。

我们在实际的开发中应该尽量避免使用长事务，那么我们应该如何进行避免呢？

==从应用开发层来说：==

1.首先我们应该判断当前这个框架是使用的 set autocommit=0还是set autocommit=1，这里的判断我们可以在测试环境中开展，把general log日志开启来，然后随便跑一个业务逻辑，通过general log我们就可以确定这个值了，如果是0的话，我们要改成1，让它进行自动提交。

2.确实是否有不必要的只读事务。有些框架会将我们执行的所有的SQL都是用begin/commit框起来，如果我们只是执行了一些查询语句的话，我们就可以不将他们放在一个事务中了。

3.业务连接数据库的时候，根据业务本身的评估，通过SET MAX_EXECUTION_TIME命令来设置一个语句执行的最长时间。避免一个语句执行的时间太长。

从数据库端来看：

1.监控infomation_schema.Innodb_trx表，设置长事务阈值，超过就报警或者kill

2.进行kill的时候可以使用Percona的pt-kill工具

3.在业务功能测试阶段开启general log，便于发现问题

4.如果是MySQL5.6或者更新的版本的话，把innodb_undo_tablespaces设置为2.如果真的发生了大事务导致回滚段过大的话，这样会让清理起来更方便。

第四篇：深入浅出索引（上）

在这篇文章中主要讲解了索引，索引简单的来说就相当于是一个书的目录，可以帮助我们快速的检索到对应的数据。

现在的关系型数据库中常用的索引就是B+树，因为B+树的高度更小，所以进行的磁盘I/O次数更少。在MySQL中索引是存储引擎层实现的。

下面讲一下Innodb存储引擎中的索引：

Innodb中根据叶子结点的类型可以分为主键索引和非主键索引两类。主键索引也叫做聚簇索引，主键索引的叶子节点中存储的是整行数据。非主键索引中的叶子节点中存储的是数据在主键中的值，如果要去查找具体的数据的话还需要进行一次回表操作。

在Innodb存储引擎中，非主键索引又叫做二级索引。

基于主键索引和非主键索引在进行查询的时候有什么区别呢？

如果是基于主键索引进行查询的话，找到的数据是整行数据，所以你可以直接得到最后想要的结果。但是如果是非主键索引的话，我们会先找到对应的主键索引，然后进行一次回表操作，在主键索引中查询到对应的真正数据。

基于上面的描述，我们知道了使用非主键索引进行查找的话会多一次回表的操作，所以我们应该尽量使用主键索引来进行一个查询。

为什么我们会使用自增主键？使用自增主键可以保证主键是递增的，这样在维护这个主键索引的时候会直接追加到最后，不会发生叶子节点的分裂问题，这样可以降低我们叶子节点的写入成本。如果我们使用了一个业务数据作为主键的话，那么在进行插入的时候可能不是有序的，所以可能会触发叶子节点的分裂，降低空间的利用率，成本较高。另外还有一个原因，如果主键的长度越小，对应的非主键的长度就越小，因为非主键中存储的是主键的值。这样的话我们可以存储更多的索引。

综上所述，从性能和存储空间两个角度来说，我们使用自增主键作为索引都是更加合理的选择。

但是什么情况下适合使用业务字段作为主键呢？当表中只能有一个索引，该索引必须是唯一索引。比如K-V场景。

为什么要进行索引的重建？

索引可能会因为删除或者是插入导致页发生分裂或者合并，会造成空间的浪费，我们进行索引的重建，会创建一个新的索引，把数据按照顺序进行插入，这样页面的利用率更高，也就是索引更紧凑、更省空间。

如果我们通过alter语句重建一个普通索引是完全ok的，然后如果我们重建了普通索引之后又通过alter语句重建了主键索引的话，因为重建主键索引的时候会导致我们的整个表发生一个重建，那么我们之前重建的普通索引相当于白重建了。

第五篇：深入浅出索引（下）

在这篇文章中，重点讲解了覆盖索引、联合索引以及最左匹配原则。

覆盖索引：

在一个查询中，索引中已经包含了我们要查找的需求，我们称之为覆盖索引。使用覆盖索引可以有效的减少了回表的次数，显著提高性能，所以使用覆盖索引是一个常用的性能优化手段。

使用覆盖索引的话索引的维护是有代价的，我们可能会需要通过建立冗余索引来维护我们的覆盖索引。

联合索引、最左匹配原则：

B+树这种结构就会使用索引的最左前缀来定位记录。

如果我们有一个索引是(a,b)，根据最左匹配原则，我们就没有必要去维护一个（a）这样的单独索引了。对于这种情况下，我们可以通过使用联合索引来减少维护一个索引，那么这个顺序往往就是需要优先考虑使用的。

建立联合索引的顺序原则的另一个原则就是空间了。比如我们有一个表中有a,b两个字段，a这个字段的长度比b大很多，我们一个查询中可能会涉及到b的一个单独使用，那么我们可以建立这样的联合索引(a,b)和（b）的索引，这样我们使用b的时候可以使用（b）这个单独索引，而且b的长度也是比a小的。

最左匹配原则中的索引下推：

下面举个栗子

如果我们的sql语句是这样子的

select * from tableName where name like("张%") and age=10;

在MySQL5.6之前没有索引下推，会去检索到第一个满足name是张开头的数据，也就是ID3开始，然后再去主键索引中进行回表然后再判断age是不是=10，这样得话会进行很多次的回表操作。

在MySQL5.6以及之后的版本中增加了索引下推，我们在联合索引（name,age）中会直接判断age是不是=10，如果不等于10不会进行回表操作，这样的话就减少了回表的操作，性能就更好了。

第三天

第六篇：全局锁和表锁：给表加个字段怎么有这么多阻碍？

这篇文章中主要讲解了MySQL数据库中的锁这个概念，主要讲解了全局锁和表级锁。

全局锁

MySQL中提供了一个加全局读锁的命令，Flush tables with read lock(FTWRL), 如果你使用了这个命令的话，给整个库加了一个全局读锁，数据的更新操作（增删改操作）、表结构的更改、更新类事务的提交语句都无法执行。全局读锁嘛，顾名思义只允许进行读操作，也就是只允许进行select查询语句。

那么什么情境下适合使用这个命令将数据库加上一个全局读锁呢？那就是数据备份的场景，首先数据备份的时候我们要保证数据的一致性，所以使用这个全局读锁是非常合适的。那么这个全局读锁是如何保证数据一致性的呢？我们举个反例，如果我们不适用全局读锁的话，我们进行一个购物系统的数据备份，比如我们先对用户的余额表进行了一个备份，然后紧接着用户购买了一个商品，然后我们又对商品表进行了一个备份，那么会发生什么情况呢？就是我们的用户的余额没有减少，但是用户购买的商品增多了，如果用这个数据进行恢复的话，那商家会亏钱的。不加锁的话，备份系统备份得到的库不是一个逻辑时间点，这个视图时逻辑不一致的。但是作为用户我倒是非常喜欢这样，哈哈

但是我们使用Flush tables with read lock这个命令其实效率是不高的，因为我们只能进行一个读取操作，那么我们的业务也相当于停摆了。另外多说一句，实际在进行数据备份的时候，应该选择系统访问量不大的时候，比如凌晨3-4点这种并发量很小的场景。

那么有什么更加高效的适合数据备份的命令吗？有，那就是使用官方自带的逻辑备份的工具mysqldump+single-transaction参数。注意：这个方案只适合进行备份的数据库中的表使用的存储引擎是支持事务的。如果不支持事务是不行的。使用这个方案会在导数据之前开启一个事务，来确保拿到一个一致性视图。而由于MVCC机制，我们的这个过程中是可以进行正常更新的。

另外可能有人会问，既然要全局只读，为什么不使用命令 set global readonly=true的方式? 作者给出了下面的两点解释：

1.在有些系统中，readonly的值会被用来做其他的逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响更大，不建议使用。

2.在异常处理机制上有差异。如果执行Flush tables with read lock命令之后由于客户端发生异常断开，那么MySQL对自动释放掉这个全局读锁。整个库回到可以更新的状态。如果使用的是set global readonly=true的方式，如果我们的客户端异常断开之后，数据库就会一直保持readonly状态，无法进行更新操作。风险很高。

表级锁

MySQL中表级锁有两种，一个是表锁，另一个是元数据锁。

表锁的语法是 lock tables…read/write ，可以用unlock tables 主动释放锁，也可以在客户端发生意外之后释放锁。需要注意的是lock tables除了会限制其他线程的读写操作之后，也会对本线程的读写有限制。举个栗子：如果我们使用了下面的语句

lock tables t1 read,t2 write;

其他线程写t1,读写t2都会阻塞，本线程中也只能执行对t1的读，对t2的读写。

另一个表级锁元数据锁 MDL

在MySQL5.5之后引入了MDL,使用MDL时不需要显示的使用，在访问一个表的时候会自动给我们使用MDL。如果我们对一个表进行增删改查操作的时候会默认使用MDL的读锁；如果我们对表进行更改表结构等操作的时候默认使用MDL写锁。

读锁之间不互斥，所以多个线程可以同时对一个表增删改查。

写锁与写锁之间，写锁与读锁之间是互斥的，用来保证表结构更改的安全性。

注意：MDL的执行流程是这样的，事务中的MDL锁在语句执行开始时申请，但是语句结束之后不会释放，而是等到整个事务提交之后才会释放。

所以会出现这样的一个问题，有时候我们对一个小表进行DDL更改表结构的时候，会导致整个库崩溃，那么是什么原因呢？结合下面的图来分析一下：

开始一个事务之后，会话A执行了查询操作，会话B执行了查询操作，等到会话C的时候执行了DDL操作，此时MDL还是一个读锁的状态，所以是不可以进行DDL操作的，所以会话C阻塞，会话D执行了查询操作，但是由于会话C阻塞了，所以D被C阻塞了，所以C后面的所有会话的操作都被阻塞了。由于客户端有重试机制，超时后开启一个新的会话，这个库的线程很快就爆满了，库崩溃了。

那么基于上面的这个问题我们应该如何安全的给一个小表进行一个DDL操作呢？

比较理想的机制是，在alter table语句里设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，如果拿不到MDL写锁的话也不要阻塞后面的操作，就先放弃。

MariaDB和AliSQL都可以执行下面的语句来保证上面的这个机制

alter table table_name nowait add column...
alter table table_name wait 时间 add column...

最后再补充一下，只有不支持行锁的存储引擎才会使用到表锁。如果你发现你程序中有lock tables这种命令你就要考虑升级存储引擎了。

提出一个问题：当备库使用mysqldump+single-transaction做逻辑备份的时候，如果从主库的binlog传过来一个DDL语句会怎么样？

具体的语句和情况如下：

开启事务的时候使用 with consistent snapshot确保这个语句执行完之后可以得到一个一致性的视图。

如果在时刻1的时候传过来了这个binlog的话，没有影响，备份的是DDL之后的数据。

如果在时刻2的时候传过来了这个binlog的话，执行Q5的时候会报错，Table definition has changed,please retry transaction,mysqldump终止。

如果在时刻3的时候传过来binlog的话，因为Q5的执行占用了MDL的读锁，所以binlog会被阻塞，出现主从延迟，直到Q6执行完毕

如果在时刻4的时候传过来binlog的话，没有影响但是备份的是DDL之前的数据。

第七篇：行锁功过：怎么减少行锁对性能的影响？

这篇文章主要讲了MySQL中的一个行锁、死锁、死锁检测

MySQL的行锁是在存储引擎层来支持的。如果存储引擎支持行锁的话，那么它的并发量就会多点，MyISAM不支持行锁，Innodb支持行锁，所以这也是为什么Innodb存储引擎替代MyISAM存储引擎的一个重要原因。

下面我们需要说一个概念：两阶段锁，其实这个概率和上一节中的MDL锁的执行流程有点类似

在Innodb事务中，行锁是在需要的时候才加上去的，但是使用完后并不会立即释放，而是等到事务结束之后才会释放。这就是两阶段锁的概念。

下面说一个我感觉对我非常非常有用的一个知识点！！！！！

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

比如下面的这个买电影票的交易场景：

1.从顾客的账户余额中扣除电影票价

2.给影院的账户余额中增加这个电影票价

3.记录一条交易记录。

对于上面的这个场景，因为是一个原子操作，所以我们应该将上面的三个操作放在一个事务中，但是如果此时有两一个用户又买了一个电影票的话，那么发生冲突的就是2这个步骤。每个顾客都有自己的一个账户，每条记录也都是不一样的，但是电影院是同一个账户也只有这一个，所以2这个步骤是最容易发生冲突的，也是限制并发量的根本所在。知道了问题的所在，我们应该将2这个步骤放到这个事务的最后一步去，这样可以最大程度的减少事物之间的锁等待，提升了并发度。

我之前的业务中，只考虑到了需要进行哪个操作，但是并没有考虑这个MySQL中行锁引发的并发度问题，所以感觉以后在开发业务的时候这里是一个很好的优化点。

下面再来说一个现象：

比如我们这个电影院搞活动，可以优惠买票，但是仅限今天一天。于是在活动刚开始的时候，我们的MySQL数据库崩溃了，我们上线一看发现CPU100%，但是数据库每秒执行不到100个事务，那是什么原因呢? 先说一下是因为死锁检测的原因。下面会详细说明。

在说死锁检测之前，先说一下什么是死锁，感觉只要是计算机专业的应该都知道死锁，简单的来说就是双方此时持有对方需要的资源，但是谁也不放下自己手上此时有的资源，都等待对方释放自己想要的资源，两个人就会一直等待，进入一个无线的等待之中。

这里我还想补充一下：当一个操作过来的时候如果它要加锁的行上面已经有了锁，他才会发生死锁的检测，而且发生死锁检测不是一定发生了死锁才会检测。一致性读不会发生死锁检测，因为读操作不用加锁。

并不是每一次死锁检测都要扫描所有的事务。比如某个时刻，事务等待状态这样：

B在等待A D在等待C 现在来了一个事务E，发现E需要等待D，那么死锁检测的时候只会去扫描D等待C这个事务，而不会去扫描B等待A这个事务。

如果发生了死锁的问题，那么有什么死锁的解决方案吗？

1.设置一个超时时间，到达这个超时时间之后就不等待了，这个超时时间可以通过参数innodb_lock_wait_timeout来设置，这个参数的默认值是50s也就是等待50s后才不等待了，但是在实际的业务中这个时间可能是不能接受的，但是如果我们将这个时间设置的太小的话，可能不是死锁只是还没有等到锁的时候就不等待了，可能会导致误判。所以这个方案不是很好。

2.第二种方案就是发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他的事务可以继续执行。可以通过参数innodb_deadlock_detect=on来开启，默认就是开启的。这个死锁检测在发生死锁检测的时候才会进行。是能够快速发现并进行处理的，但是也会又额外的负担的。

每个新来的但是被阻塞的线程都会进行检测，检测是不是因为自己的加入导致了死锁，如果同时有1000个线程对同一行进行一个更新操作，那么这个死锁检测的时间复杂度为100w级别，虽然最后发现没有死锁，但是会占用特别多的CPU资源来进行死锁检测。这就是为什么MySQL因为CPU100%崩溃了，但是每秒执行的事务不多的原因。

那么对于这种由于热点行更新导致的性能问题该如何解决？

1、临时将死锁检测关闭，但是会有一个很大的问题就是可能会导致大量的超时，对业务有损失。

2、另一个思路就是控制并发量，而且要在数据库服务端进行一个并发控制。因为客户端可能有非常多，即使控制了每个客户端的并发量最后汇总起来依然会很高，所以我们应该在数据库服务端进行并发控制。

那么如果在数据库服务端进行并发控制呢？基本的思路就是：对于相同行的更新，在进入引擎之前排队。

方案有 1、修改MySQL源码 2、使用中间件

讲一下使用中间件的方案吧：

1、可以使用阿里巴巴的Seata来对数据库的请求进行拦截和处理来实现对数据库的并发控制

2、可以使用rabbitmq这类消息队列来异步处理数据库请求，从而减少数据库的并发量。

提问一个问题：如果你要删除一个表里面的10000行数据，有以下三种方法可以做到：

第一种：直接执行delete from T limit 10000;

第二种：在一个连接中循环执行20次delete from T limit 500;

							==第三种：在20个连接中同时执行delete from T limit 500;==

这个问题其实挺简单的，最好的方案肯定是第二种。如果选择第一种的话，单个语句占用时间太长，锁的时间比较久，而且大事务会导致主从延迟。

如果是第三种的话，就会人为的造成锁冲突。

第四天

第八篇：事务到底是隔离的还是不隔离的？

先说明一下，这篇文章我看了两遍才慢慢理顺了一点，但是感觉还是不够，只是将现在的感受写一下读书笔记吧。

这篇文章中主要讲述了三个知识点：一致性读、当前读、行锁。主要是将这三个知识点串联起来了。下面就来说说

前面我们已经学过了可重复读，感觉一个在可重复读隔离级别下的事务，与世无争，不受外界的影响。但是如果一个事务拿着一行数据的行锁，另外的事务来操作这行的时候就不会与世无争了，就要等着另一个事务将这个行锁释放掉，不然就一直是一个等待的状态。

下面来举个例子：  此时数据库中有这样的数据(id,k) ->  (1,1)  (1,2)

在这里我们特别说明一下start transaction with consistent snapshot这个命令，这个命令可以立刻获得一个一致性视图。

如果我们使用begin/start transaction命令的话，事务不会立即开启，当你执行第一个快照读命令的时候才会真的去开启一个事务获得一个一致性视图。

在MySQL中有两种视图，第一个的话就是view，这个就是执行查询语句的时候定义的一个虚拟表。另一个是innodb中实现MVCC的一致性读视图。支持读已提交和可重复读两种隔离级别的实现。

视图的作用就是：事务执行期间用来定义，我能看到什么数据。

下面我就来详细讲讲一致性读。

在可重复读隔离级别下，事务在启动的时候就拍了一个快照，注意这个快照是基于整个数据库的。那么可能有人会有疑问，如果一个数据库中的数据有100g是不是每次开始一个新的事务的时候就要将这100g的数据全部查询出来然后去生成一个快照呢？

当然不是，是有一个数据版本可见性规则的，只会将你能看到的数据放到这个快照中，看不到的数据是不会放到快照中的。下面会讲解什么数据看得到，什么数据看不到。

Innodb中每个事务都有一个唯一的id，即transaction id，这个id是严格递增的。而MVCC机制会让每个数据保留多个版本。当一个事务对一个数据进行一个更新的时候，会将这个事务的id赋值给这个数据版本的事务id，叫做row trx_id。同时，旧的数据要保留，并且在新的数据版本中，能够有信息可以得到它。

也就是说，一个数据可以有多个版本，每个版本都有自己的row trx_id，这个id对应着修改这行数据的事务的id。

其中v1、v2、v3都是虚拟的view，U1,U2,U3是我们之前说过的undo log，最新的记录可以通过undo log得到之前的版本数据。

一个事务启动的时候，可以看到此时已经提交的所有的事务的结果。但是在这个事务执行期间，其它事务没有提交的操作是看不到的。在实现上，innodb会使用一个数组来保存这个事务启动瞬间，当前启动了但是还没有提交的所有的事务的id。innodb会利用这个数组中的事务id来做一些操作。innodb会利用这个数组中的最小的事务id作为下水位，然后将当前系统中已经创建过的事务ID的最大值加一作为高水位。

这个视图数组和高水位就组成了事务的一致性视图。

当一个事务启动的时候，一个数据的row trx_id可能有这几种情况

1.落在绿色部分，是当前事务启动之前就已经提交的了，是小于下水位的，对于当前这个事务来说就是可见的。

2、落在橙色部分。当当前这个事务启动的时候，此时的事务还没有启动所以肯定看不到之后的事务，不可见。

3、落在黄色部分，又分为两种情况

1）、如果row trx_id在视图数组中，说明是开启了事务但是没有提交的事务，所以肯定看不到

2）、如果 row trx_id不在视图数组中，说明是已经提交的事务生成的数据，所以可见。

简单总结来说就是：在一个事务启动的瞬间，如果一个数据没有提交，不可见。如果在事务启动之后提交的数据也不可见。如果是在创建事务之前提交的数据的话就是可见的了。

一致性视图就会根据这个规则在一个事务启动的瞬间，基于整库生成一个快照，快照中只包含能看到的数据。所以你就知道不是扫描整个数据库的所有数据来生成一个一致性视图快照了。

当前读：

	当前读就与一致性读又不一样了。当前读是发生在更新逻辑上的。如果我们在执行一个更新操作的时候，如果我们按照一致性读的逻辑的话，在当前事务启动之后，另一个事务开启修改了数据并提交了，我们当前事务看不到，等我们在更新的时候是在原始版本上更新的，那么我们另一个事务做个更新就会被我们覆盖了，这就会产生大问题了。

所以简单来说，执行更新逻辑的时候，要读取到此时的最新的数据。其实这个更新逻辑是分为了两步进行的，先读再改。去数据库中读取此时的最新的数据，然后基于这个最新的数据再改。

当然除了更新操作之外，如果我们select语句加了锁也是当前读，比如下面的两个sql语句

select k from t where id=1 lock in share mode;
select k from t where id=1 for update;

下面来举两个实例，看一下是如何执行的。

1、假设初始值（1，1）

在这个流程中，事务A先开始一个事务，所以后面的事务B和事务C都是在A启动之后才开始的，所以对于A来说都看不到B和C

事务B开启之后，事务C开始，在事务B的update之前事务C先update并且提交了。所以B继续更新的时候是当前读所以B可以看到C改后的最新的数据。所以事务C将k设置为了2.然后B将数据改成了3，然后事务B再查询的时候因为是自己这个事务进行的更改，所以查询出来的K是3。

初始值(1,1)

依然是事务A先开始，所以对于事务A来说事务B和事务C的更改它是看不到的。

然后事务B开始，然后事务C开始。与上面的区别是事务C手动commit了。当事务C进行update之后，锁住了id=1的这行数据，然后事务B又进行了update操作，希望去拿id=1的这行数据的锁，但是此时C没有提交，所以C还拿着锁，B拿不到锁，所以B就阻塞在这里等待C提交释放锁之后在进行一个更改。所以C先将k改成了2，B将k改成了3，然后B查询出来是k=3,A查询出来k=1。所以这里就是出现了行锁问题，至此，一致性读，当前读、行锁就连起来了。

小结：

InnoDB的行数据有多个版本，每个版本有自己的row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图确定数据版本的可见性。

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；对于读已提交，查询只承认在语句启动之前就已经提交完成的数据。

当前读总是读取已经提交完成的最新版本。

为什么表结构不支持可重复读？因为表结构没有对应的行数据，也没有row trx_id，因此只能遵循当前读的逻辑。

第五天

第九篇：普通索引和唯一索引，应该怎么选择？

在这篇文章中，主要讲述了普通索引和唯一索引我们应该如何选择。讲述了在查询和更新这两种操作下的性能区别。

首先在说唯一索引和普通索引之前，我想先说一下，如果你的业务不能保证插入的数据一定是唯一的，或者你的业务就要求数据库中的数据是一个唯一的，那么我们不用管什么性能，就必须用唯一索引。但是如果我们的业务可以保证我们插入的数据是唯一的，那么我们才会考虑使用普通索引或者唯一索引带来的性能差异问题。

查询过程：

对于一个普通索引来说：我们查找一个数据的结尾是我们查找到这个数据了，我们还需要往后查找到第一个不符合要求的数据才结束。

对于一个唯一索引来说：只要找到我们要查询的数据就结束了。

但是你会发现两个索引的查询性能上无非就是多了一次查询，性能的差距微乎其微！

但是大家可能也会有一个问题那就是：InnoDB的数据是按数据页为单位来进行读写的。当需要读一条记录的时候，并不是将这个记录从磁盘中读取出来，而是以页为单位，每次将一页的数据从磁盘中读出来，这样可以减少与磁盘进行I/O的次数。这就会有人说，如果我们使用了普通索引，当好读到的数据是这一页中的最后一个数据，那么我要找到下一个不符合的数据的话就需要读取下一次，进行另一次磁盘I/O了，确实，这是事实，但是发生的概率太小了，所以我们忽略这种情况。

更新过程：

对于普通索引来说：在进行更新的时候不会直接去磁盘中读取这个数据然后再进行更新。而是有一个叫做change buffer的机制。下面就来说一下这个过程。

如果我们要进行更新的数据索引的数据页已经在内存中了，那就直接进行一个更新就完事了。但是如果此时这个数据没有在内存中，会先将这个更新操作放到change buffer 中，等到下次查询数据的时候，从磁盘中读取这个数据到数据页中，然后再将change buffer中的这个操作执行到这个数据上，这个过程叫做merge。然后再给用户显示。

对于唯一索引来说：在进行更新的都要先判断这个操作是否违反唯一性约束。先判断现在表中是否已经存在这个数据，所以必须要将数据页读入内存才能判断。所以就会有磁盘I/O的操作。

到这里就可以看出，对于更新操作来说，普通索引的磁盘I/O次数比唯一索引少。所以普通索引的性能高于唯一索引。

这里还要多说一下关于change buffer的内容，change buffer是可以持久化的数据，change buffer在内存中会有拷贝，也会被写入到磁盘上。

而change buffer中的数据进行merge的过程，除了访问这个数据的时候会触发，后台会有一个线程定期去merge。在数据库正常关闭的时候也会merge。同时要注意，change buffer可以减少读磁盘，数据读入内存是需要占用buffer pool的，所以这种机制可以避免占用内存，提高内存利用率。

change buffer这个机制只有普通索引可以使用，对于唯一索引是不可以使用的。

change buffer 机制会使用buffer pool内存，所以不能无限大，我们可以使用参数 innodb_change_buffer_max_size来动态设置。

注意change buffer这个机制主要是用于插入数据的时候的，下面我就来说一下在一张表中插入数据的一个流程：

第一种情况就是我们要更新的目标页在内存中：

对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束。

对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

第二种情况就是我们要更新的目标页不在内存中：

		对于唯一索引来说，需要去磁盘中读取目标页，然后放到内存中，然后判断到没有冲突，进行一个插入

对于普通索引来说，不用去磁盘读取目标页，只需要将更新记录保存在change buffer中，语句结束。

change buffer的适用场景：

对于写多读少的业务来说，页面在写完之后立即被访问到的概率很小，此时使用change buffer的效果最好，这种业务模式常见的就是账单类，日志类的系统。

反过来说，如果一个业务是插入数据后立即被访问到，那么写入change buffer中之后又立即触发了merge的过程，所以change merge和没有作用一样，反而增加了维护change buffer的开销。所以这种场景不适合使用change buffer机制。

当我们使用的是一个机械硬盘的话，我们使用普通索引+change buffer机制在进行更新和插入的时候性能是最好的。所以change buffer给我们提供了一个如果碰上了大量的插入数据慢，内存命中率低的时候，可以给你多提供一个排查思路。

对于归档类的业务是非常适合使用普通索引+change buffer机制的，比如我们一个数据只会保留半年，半年之后将这个数据放到档案库中，那么因为这个数据已经保证了唯一性，所以我们可以使用普通索引+change buffer机制将数据写入到档案库数据库中。

写道这里，可能有小伙伴对change buffer和redo log弄混了，我在学习的时候也弄混了，感觉两者都是弄个小本子先记录下操作来，然后在合适的时候再去操作啊，但是两者还是有区别的，下面来说一下两者的区别：

下面有一个插入语句来说一下change buffer和redo log的执行位置

insert into t(id,k) values(id1,k1),(id2,k2);

假设k1所在的数据页是在内存中的，k2所在的数据页不在内存中。

流程如下：

1、因为k1的数据页在内存中，直接进行一个插入就可以了

2、因为k2的数据页不在内存中，所以将操作写入change buffer中

3、将k1的插入操作和k2的插入操作，顺序写入到redo log中

图中的虚线是后台操作，不影响更新的相应时间。

简单来进行一个对比发现：redo log只要节省的是随机写磁盘的IO消耗（转成了顺序写）,而change buffer主要节省的则是随机读磁盘的IO消耗。

有个问题：如果某次写入使用了change buffer机制，之后机器异常重启，是否会丢失change buffer和数据？

不会，因为在一个事务提交的时候，会把change buffer中的操作写入到redo log中，又因为redo log在事务提交的时候会进行一个刷盘操作，所以下次崩溃恢复的时候，change buffer依然可以找回来。

下面来详细的讲解一下change buffer机制下的merge过程：

1、从磁盘读入数据页到内存

2、从change buffer里找到这个数据页的change buffer记录，依次应用，得到新版数据页。

3、写redo log，这个redo log包含了数据的变更和change buffer的变更。

第十篇：MySQL为什么有时候会选错索引？

这篇文章主要讲解了MySQL使用了错误的索引的问题，下面就来详细的讲解一下这个问题。

我们知道数据查询时使用的具体的索引是由优化器来决定的，那么优化器是根据什么来进行选择索引的呢？

一般就是根据下面三个条件来进行选取的索引的评估：1.检索的行数2.是否需要进行一个排序操作3.是否会使用一个临时表。其中最关键的一个判断标准就是扫描的行数

那么MySQL的优化器是如何判断扫描行数的呢？MySQL的优化器不是通过一条条的扫描所有数据然后精确得到实际扫描的行数的，它是通过索引的基数来来估计统计的信息，每个索引都有自己的一个基数，一个索引的基数越大说明它的区分度越高，不同的值越多越好进行区分。可以通过show index方法来查看每个索引的区分度，区分度是Cardinality这个字段。

那么MySQL是如何确定每个索引的区分度的呢？

利用了统计学中的抽样检验的方法来进行统计扫描的行数的。在采样的时候会默认选择N个数据页，统计这些页面的不同值，得到一个平均值，然后乘以数据页的页面数，就可以得到这个索引的基数。

数据表是会不断更新的，所以索引的统计信息也不是固定不变的，所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次采样统计。

MySQL中有不同的采样统计策略，我们可以通过参数innodb_stats_persistent来进行选择，当设置为on的时候，表示统计信息会持久化存储，这时，默认的N是20，M是10。设置为off的时候，表示统计的信息只存储在内存中，这时，默认的N是8，M是16

但是实际上，MySQL的优化器在进行判断扫描的行数的时候，还会统计回表的次数，所以容易发生误判的情况，一般MySQL的优化器选择错误了索引的时候根本的原因就是统计扫描行数的时候出现了错误。如果我们要修正这个统计信息的话，我们可以使用命令 analyze table t 就会重新统计扫描信息。

下面来详细讲解一下，如果我们发现一个语句执行的很慢，通过explain语法分析发现MySQL使用了错误的索引的话，我们如何解决这个问题。

方案1：我们可以通过 force index 强行指定使用什么索引，但是这个方法的灵活度很低，而且如果发生了数据库的迁移的话，可能会导致不兼容。而且代码一点不优雅，记住，作为一个程序员，一定要让代码优雅！！！！

方案2：可以考虑修改SQL语句，引导MySQL使用我们的索引。这种方案比较适合刚入职的新手，可能本来我们的查询只需要耗时10ms，一个新手入职后不会使用索引，导致MySQL使用了错误的索引或者没有使用索引，会让我们原来的10ms的查询语句变成了10s。

方案3：在有些场景下，我们可以通过新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

小结：

对于统计信息出现错误导致的MySQL使用了错误的索引的问题，我们考虑使用analyze table t 来结局。

对于其他问题导致的MySQL使用了错误的索引的问题，我们可以使用上面的三种方案来使用正确的索引。

追加一个问题：在下图中的这个执行流程中，为什么MySQL使用了错误的索引

在经过上面的这个流程之后，我们的explain语句就发现结果不对了，这是什么原因呢？

我们可能会认为sessionB中先将数据都删除了，然后又通过调用存储过程将这10w行数据恢复了，看上去是覆盖了原来的10w数据。但是，sessionA开启了事务并没有提交，所以sessionB的删除是没办法删除的，sessionB的操作也就会达到一个效果就是之前数据每一行数据都有两个版本，旧版本是delete之前的数据，新版本是标记为deleted的数据。

但是有人可能会有疑问，那么我们的主键上的数据也不能删，那使用explain语句进行扫描的行数为什么还是10w左右呢？因为主键是直接按照表的行数来估计的，而这个表的行数的估计是通过命令 show table status来得到的。

第六天

第十一篇：怎么给字符串字段加索引？

这篇文章主要讲解了如何给字符串字段建立合理的索引。

文章是通过一个邮箱的例子来进行行文的，给邮箱字段加索引，应该如何加？

首先我想说一下MySQL是支持前缀索引的，也就是对于字符串类型的字段，我们可以利用这个字段中的某些前缀作为索引。如果我们对这个邮箱的字符串类型的字段建立索引的时候没有指定前缀的长度，默认就是使用整个字符串字段建立索引。下面是两种方式的建立索引的语句。

alter table User add index index1(email);
alter table User add index index2(email(6));

第二个索引就是使用email这个字段的前6位字符作为索引。

简单的来想一下也能想到，我们使用前缀索引的话，索引占用的空间肯定是小的，但是也会带来问题，如果某些数据的这个前缀是相同的话需要更多的扫描次数，所以会增加扫描次数。

下面来看一个查询语句在上面的两个索引下是如何执行的

select id,name,email from User where emial='zhangssxyz@xxx.com';

在index1的索引下的流程是这样的：

1.从index1索引树中找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取到对应的主键id为ID2

2.到主键上查询主键值是ID2的行，判断email的值是正确的，将这个记录放到结果集中

3.取index1中的下一条记录，发现不满足索引值为’zhangssxyz@xxx.com’了，查询结束，将结果返回。

在index2的索引下的流程是这样的：

1.从index2索引树中找到满足索引值’zhangs’的这条记录，然后找到对应的主键值为ID1

2.到主键索引中查询主键值为ID1的行，判断出email的值不是’zhangssxyz@xxx.com’,这行记录丢弃

3.取index2上刚刚查到的位置的下一条记录，发现仍然是‘zhangs’，取出ID2,再到主键索引中查询ID2的主键，然后再进行判断，这次值对了，将结果放到结果集中，

4.重复上一步，直到index2上取到的值不是’zhangs’

很明显的可以看到使用前缀索引index2，查询次数变多了很多

那么使用前缀索引，除了可能会增加查询次数外，还有别的问题吗？有，那就是会让覆盖索引失效

如果我们的查询语句是这样的

select id,email from User where email='zhangssxyz@xxx.com';

如果我们使用的是index1这个索引的话，我们在index1索引树中查找到记录之后就不用进行一个回表操作了，可以直接返回了，这就是利用了覆盖索引。

但是如果我们使用的是index2这个前缀索引的话，系统并不确定前缀索引的定义是否截断了完整信息，所以依然会进行回表操作。也就是说使用前缀索引的话会导致覆盖索引失效。

那么是不是说我们的前缀索引只有坏处没有好处呢？当然不是，如果只有坏处那么这种索引就不会存在了。现在就来说一下前缀索引的好处。

如果我们选取的前缀长度合适的话，进行的查询的次数会少很多，而且索引使用的空间会小很多，节省空间，索引树就会存放更多的索引。

那么话说回来了，我们的这个前缀索引选取多大的长度才是合适的长度呢？这个时候我们就需要通过统计索引上有多少个不同的值来判断要使用多长的前缀。因为对于一个索引来说，区分度越大越好。

我们可以通过 count函数以及distinct、left来看看不同长度的前缀索引的不同值有多少

select count(distinct email) as L from User;
select count(distinct left(email,4)) as L1 from User; 
select count(distinct left(email,5)) as L2 from User; 
select count(distinct left(email,6)) as L3 from User; 
select count(distinct left(email,7)) as L4 from User;

使用前缀索引可能会损失区分度，所以你需要预先设定一个可以接受的损失比例。

那么对于那种前缀的区分度不好的情况，我们应该怎么办？

比如我们存储身份证的话，前面的很多位可能非常多的人都是一样的，所以使用简单的前缀索引效果是不好的。

对于前缀区分度不好的情况，如果我们简单的使用前缀索引的话，其实还不如使用简单的索引性能好，那么有什么方案来解决吗？

方案1：使用倒序存储：由于身份证号的后6位重复很少，所以可以利用这6位建立一个前缀索引，可是这6位是在最后的怎么建立前缀索引呢，那就要对身份证号这个字段进行倒序存储了，可以使用下面的语句。

select filed_list from t where id_card=reverse('input_id_card_string')；

方案2：使用hash字段：在表上额外建立一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

alter table t add id_card_crc int unsigned,add index(id_card_crc);

每次插入新数据的时候到通过crc32()函数得到效验码然后存到这个字段上，可能会发生冲突，所以查询语句的where部分要判断id_card的值是否精确相同。

select field_list frim t where id_card_crc=crc32('intpu_id_card_string') and id_card='input_id_card_string'；

下面来说一下方案1和方案2的异同点

同：都不支持范围查询

异：

1、从占用的空间上来说，使用hash字段的方案需要额外创建一个字段，所以占用空间更多

2.从CPU消耗方面来说，倒序存储方案会使用一个reverse函数，而hash字符方案会使用一个crc32函数，从CPU消耗来说crc32函数对CPU的消耗更大

3.从查询效率上来看，使用hash字段更稳定，因为crc32函数得到的效验码会发生冲突，但是概率非常小，所以可以近似理解为扫描行数为1，但是我们使用倒序存储的方案其实还是前缀索引，可能会增加扫描的行数。

提出一个问题：

如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号@gmail.com", 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。

系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？

解决方案：因为这个登录名的前面六位和后缀都是固定的，所以不管我们是使用前缀索引还是倒序存储都不能达到很好的效果，那么我们应该怎么办呢？我们可以将入学年份和顺序编号这9位做为一个索引，利用hash字段的思想，hash的规则不是crc32函数了，而是简单的字符串转数字。

第十二篇：为什么我的MySQL会"抖"一下？

首先需要说明一下，这一节不难理解，但是在性能优化应用上很重要！！所以是个很好的干活。

这篇文章从一个场景入手：一条SQL语句，正常执行的时候特别快，但是有时候不知道怎么回事，它就会变的特别慢，并且这样的场景很难复现，它不只随机，而且持续时间很短。这就是所谓的MySQL抖了一下。文章主要讲解了一下什么情况下会出现MySQL抖一下，以及如果处理。

在讲导致MySQL抖的场景之前，我想先讲一个概念，那就是“脏页”以及“干净页”:

脏页：当内存数据页和磁盘数据页中的内容不一致的时候，我们称这个内存页为脏页

干净页：内存中的数据写入到磁盘上之后，内存中的数据和磁盘中的数据一致了，此时内存上的数据页叫做干净页。而将内存中的数据刷盘到磁盘上的过程是比较浪费时间的。

什么情况导致MySQL突然“抖”一下：

场景1：如果我们的redo log日志满了，我们的check point需要往后移动，那么就需要进行一个刷盘，将内存中的脏页刷盘到磁盘上。然后此时check point才会后移。

场景2：内存不够用了，此时需要进行内存淘汰策略淘汰掉一些内存，此时如果淘汰掉的内存中的数据页是脏页的话，需要进行一个刷盘。

针对这个场景2面试官可能会问这样的问题：为什么在进行内存淘汰策略的时候不能直接淘汰，然后当我们再访问到这个数据的时候从磁盘上读取到数据页中然后利用redo log中的日志进行一个merger操作，得到最新的数据。

其实使用场景2这个方案是从性能上考虑的，如果刷脏页一定会写盘，就保证了每个数据页有两个状态：

1.在内存中存在，内存里就肯定是正确的结果，直接返回

2.另一种是内存里没有数据，就可以肯定数据文件上一定是正确的结果，读入内存后返回。这样效率更高。

场景3：如果此时MySQL认为系统是空闲的状态，它就会去进行一个刷盘

场景4：MySQL正常关闭的时候，内存中的脏页是需要进行一个刷盘的。

那么上面这四种场景对性能的影响：

场景3和场景4都是正常的情况，所以不用考虑性能问题。只需要对场景1和场景2考虑性能问题。

对于场景1来说，这种场景是innodb需要避免的，因为如果redo log写满的话，check point需要后移然后刷盘，此时数据库中的更新操作会被阻塞。从监控上看到更新数会跌到0。

对于场景2来说，这种情况是不可避免也是最常见的，Innodb用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态

1、没有被利用

2、已经被利用了，此时是干净页

3、已经被利用了，此时是脏页

如果根据内存淘汰策略淘汰的是一个干净页的话，不需要刷盘可以直接使用。如果是一个脏页的话，不能直接使用需要先刷盘才可以继续使用。如果脏页发生下面两种情况的话是会明显影响性能的。

1、一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长

2、日志写满，更新全部堵住，写性能跌为0，这种情况对于敏感业务来说是不能接受的。

对于Innodb刷脏页的优化策略

所以为了提高性能，我们应该有对innodb刷脏页的控制策略，下面就来讲解一下，以及相关的参数。

首先我们应该告诉Innodb我们机器的IO性能，这样Innodb才能知道需要全力刷脏页的时候，可以刷多块。这就需要使用innodb_io_capacity这个参数了。

这个参数的值我们应该根据我们机器的磁盘的IOPS来确定，磁盘的IOPS可以通过第三方工具来测。有很多问题的发生就是由于innodb_io_capacity这个参数设置的不合理导致的，比如我们的机器是SSD磁盘，我们没有设置这个参数，默认是300，那么innodb就会认为我们机器的IO很差，这就会导致刷盘很慢，导致脏页急速累计。

如果我们自己来设计一个策略控制刷脏页的速度的话，会考虑哪些因素呢？

影响innodb刷盘速度的因素主要有两个，分别是1.脏页比例 2.redo log写盘速度

参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。

下面来说一下innodb是如何得到刷盘速度的：首先innodb会根据当前的脏页比例（假设为M），算出一个范围在0到100之间的数字，计算这个数字的伪代码类似如下：

F1(M){
if M>=innodb_max_dirty_pages_pct then
	return 100;
return 100*M/innodb_max_dirty_pages_pct;
}

Innodb每次写入的日志都有一个序号，当前写入的序号跟check point对应的序号之间的差值，我们假设为N。InnoDB会根据这个N算出一个范围在0到100之间的数字，这个计算公式可以记为F2(N)。F2(N)算法比较复杂，你只要知道N越大，算出来的值越大就好了。

根据上述算得的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度了

对应的流程图就是

平常我们也应该多关注脏页比例，不要让它接近75%.

其中我们可以通过下面的语句来得到对应的脏页比例

select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME='Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME='Innodb_buffer_pool_pages_total';
select @a/@b;

还有一个有趣的策略：

MySQL中的一个机制可能会让我们的查询变得更慢，那就是当我们查询一个页面的时候，这个页面需要进行一个刷盘，但是紧挨的相邻的数据页也是一个脏页的时候，会将我们相邻的这个脏页也一起进行刷盘，而且这个机制是会传递的，所以可能会将连在一起的多个脏页一起刷盘，所以让查询时间变得更慢了。在Innodb中有参数来控制这个策略，那就是innodb_flush_neighbors 当这个参数为1的时候就是开启上述的“连坐“机制，为0的时候关闭。

对于机械硬盘推荐开始这个功能因为会减少随机IO，但是对于SSD磁盘不推荐开启，而且MySQL8之后的这个参数默认是0。

提个问题：

一个内存配置为128GB、innodb_io_capacity设置为20000的大规格实例，正常会建议你将redo log设置成4个1GB的文件。

但如果你在配置的时候不慎将redo log设置成了1个100M的文件，会发生什么情况呢？又为什么会出现这样的情况呢？

解答：如果我们机器的IO性能非常好，但是我们的redo log设置的很小，那么会导致的一个问题是redo log很快就被写满了，就会将更新操作阻塞，让check point后移，进行一个刷盘。所以会出现磁盘压力很小，但是数据库出现间歇性的性能下跌。

第七天

第十三篇：为什么表数据删掉一半，表文件大小不变？

在这篇文章中主要解答了一个问题，那就是为什么将表中的数据删除了一半，但是表占用的空间没有变化？

下面就来展开讲解一下。

数据表中的数据存储的位置，既可以存在于共享表空间中，也可以是单独的文件。这个行为是由一个参数innodb_file_per_table来决定的，如果我们的这个参数设置为OFF，那么会将表中的数据存放在系统共享表空间中。如果设置为ON，就会生成一个以.ibd为后缀的文件。所以建议将这个参数设置为ON，数据存放在一个单独的文件中方便管理，而且在MySQL5.6.6版本开始，这个参数默认为ON了。

下面来说一下删除数据的一个流程是什么？

我们的删除表中数据的操作其实只是将这个数据标志为了“可复用”的状态。比如上图中我们将R4这个记录删除，我们会将R4这个记录标记为删除，但是不会真正的删除，如果后续进行插入的时候，一个数据的ID在300和600之间的话，会“复用”这个位置。所以磁盘中的大小不会变，因为本质上没有删除。只是标志为了“可复用”。对于一个数据页也是一样的，我们将整个数据页中的数据删除其实也是标记为了“可复用”，当有数据插入到这个数据页上的时候直接复用。

所以删除数据会造成一个“空洞”问题，什么是空洞呢？简单的来说就是标记为了“可复用”状态，但是一直没有被利用。不止是删除数据会导致“空洞”，插入数据也会，如果我们的数据是根据索引递增插入的，那么索引还是紧凑的，但是如果数据是随机插入的，那么可能会造成索引的数据页分裂，分裂之后空出来的没有被利用的空间就是空洞。如下图中的pageA中R4后面没有被利用的空间。这也是为什么要求数据主键ID要递增也不是随机的原因。

另外，更新数据，其实是删除原来的旧值，然后插入新值，也是会造成“空洞”的。

说了这么多，那么如果达到删除表中的数据也会变化表文件大小呢？

那就是进行重建表。

使用命令

alter table t engine=Innodb;

下面再来介绍一下重建表的一个流程，在不同的MySQL版本中，这个流程是不一样的。

在MySQL5.5版本之前的流程是这样的：

首先新建一个和原始表结构一样的表作为一个临时表，然后根据主键ID递增的顺序，将原始表中的id一行行的从原始表中读出来然后插入到新表中。这样原始表中的空洞就不会在新表中存在了。显然新表中的数据页更加紧凑，然后再用新表去替换旧表就实现了重建表的目的。但是要注意，在这个流程中我们在重建表的过程中对原始表进行的更新和插入操作时被拒绝的，所以这个原始表不能有更新，会被阻塞，所以被称这个DDL不是online的。这就会让性能下降很多。注意上面的这个流程中创建的临时表是在server层的。为了解决这个问题从MySQL5.6之后进行了优化，将这个DDL变成了可以Online的DDL。下面会详细说的。

上图是MySQL5.6之后的一个优化的流程。你会发现在这个流程中多了一个row log日志，会将重建表到新表过程中对原始表的更新操作记录到这个日志文件中，然后重建完了之后将这个row log日志中的操作更新到新表中。所以称这个DDL 为online的。而且上述流程中产生的临时表是存放在innodb内部的，不是在server层。而且你还要注意，如果在重构时进行的操作也是可能造成也分裂和空洞的

但是在这里我要多说一些文中没有提到的东西，那就是数据丢失。如果我们在重构表的过程中row log中的操作还没有更新到新表中我们发生了宕机，那么对原始表的操作就会丢失。另外我们重构表是会将原始表删除然后用新表进行覆盖的，所以我们一定一定要对原始表进行一个备份，因为如果删除了原始表新表还没有覆盖突然宕机了，那么我们的数据就没了。那你就g了！！

而且对原始表进行一个备份还有一个好处，那就是我们可以利用原始表和新表进行一个数据对比，将原始表中因为其他原因没有复制到新表中的数据复制过去，防止操作新表的时候找不到数据。

我还想多说一下重建表的一个很重要的问题，那就是我们新表虽然是按照主键id递增的顺序从原始表中将数据一行行的读出来插入到新表中，但是新表中的主键ID不会复用原始表中的数据，而是会重新生成一个新的聚簇索引，这个聚簇索引的数据页更加紧凑而且不会有原始表中的空洞问题。

但是上面的这个操作会产生一个问题，那就是我们的id改变了，那么我们业务中原来根据id进行的操作就会发生错误，那么为了解决这个问题我们可以使用一个工具pt-online-schema-change 。

在重建表时一定做好数据备份，既可以防止数据丢失也可以防止数据不一致。

看到这里可能有人会有疑问，那就是进行DDL的时候时需要获得DML写锁的。那么还可以叫做Online DDL吗？

确实在alter的时候需要获得DML写锁，但是在进行复制数据之前，这个DML锁退化成了读锁，就是为了不阻塞DDL的。但是为什么不直接解锁呢？是为了防止其他线程对这个表同时进行DDL。

Online DDL对于一个大表来说，拷贝数据到临时表其实是很消耗IO和CPU资源的，所以在生产环境中可以使用一个github开源的

gh-ost来安全操作。Online DDL一定要在业务低峰期使用。

下面又来说一下online和inplace

online就是MySQL5.6之间流程中临时表创建在server层。inpalce是MySQL5.6版本之后临时表创建在innodb内部，是一个原地操作。

那么一个小问题，如果你有一个ITB的表，磁盘空间是1.2TB，能不能做一个inplace的DDL呢？

答案是不行，因为tmp-file临时表也是要占用空间的，所以1.2TB是不够用的。

如果使用online的方式重建表可以使用下面的语句

alter table t engine=Innodb,ALGORITHM=copy;

如果使用inpalce的方式重建表可以使用下面的语句

alter table t engine=Innodb,ALGORITHM=inplace;

但是你还要注意，inplace和online的一个逻辑关系：

1.DDL过程如果是Online的，那么一定是inplace的。也就是MySQL5.6之后对应的流程图

2.如果inpalce的DDL不一定是Online的，比如添加全文索引和空间索引就是这种情况。这两个操作是会阻塞的。

最后再说一下optimize table、analyze table、alter table、Truncate的区别：

1、从MySQL5.6之后alter table就是上图有row log的那个流程了

2、analyze table其实不是重建表，只是对表的索引信息进行了重新统计，没有修改数据，这个过程加了MDL读锁。

3、optimize table相当于 recreate+analyze

4、Truncate相当于 drop+create

总后说一句，上面这个重建表来优化空间的方法要谨慎！！！谨慎！！

提一个问题：

假设现在有人碰到了一个“想要收缩表空间，结果适得其反”的情况，看上去是这样的：

一个表t文件大小为1TB；
对这个表执行 alter table t engine=InnoDB；
发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了1.01TB。

你觉得可能是什么原因呢？

解答：

出现上面的这种情况主要是有下面的两种情景：

1、本身的表就没有"空洞"，我们在重建表的过程中进行的更新操作，导致表产生了"空洞"。所以表就变大了。

2、Innodb还有一个机制。那就是我们在重建表的时候，innodb并不会把整张表占满，而是每页都会留出1/16给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。

例如下面这个场景：

1、将表t重建了一次

2、插入一部分数据，但是这部分数据使用了预留出来的空间

3、又重建了一次表t

第八天

第十四篇：count(*)这么慢，我该怎么办？

这篇文章中主要讲解了为什么count(*)的查询性能这么低下，以及如果遇到数据频繁的变更并需要统计表行数的需求我们怎么来设计。下面来开始详细讲解。

首先说一下count(*)在不同的存储引擎中有不同的实现。在MyISAM中count( * )的执行是很快的，因为它将当前表中有多少行提前存起来了，当我们查的时候直接取出来就可以了。Innodb中count( * )的性能就比较差了，因为innodb中去执行count( * )的时候会去数据表中一行行的进行查询然后将结果返回。那么可能有人会问，为什么Innodb不采用和MyISAM一样的机制来提前存储这个表中的记录数，然后直接返回呢。

这个原因还要从两个存储引擎的特点说起，我们知道MyISAM是不支持事务的，而innodb是支持事务的。所以innodb中有MVCC机制，正是因为有这个MVCC机制，所以innodb表应该返回多少行是不确定的，因为不同的视图看到的行数可能是不同的。所以必须要一行行的扫描然后判断最终返回。

下面来看个例子：

原始数据10000条

在这个例子中，不同的会话启动了不同的事务，也启动了不同的视图，每个视图最后返回的结果都是不同的，A返回10000，B返回10002，C返回10001

多说一句，别看innodb中count( * )的性能不高，这还是优化过的。我们知道在innodb引擎中，主键索引树的叶子节点存储的是真正的数据，而不同索引树叶子节点存储的是主键值。所以普通索引树比主键索引树小很多。因此，MySQL优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

如果你之前配置过MySQL集群，那你肯定用过一个指令

show table status\G;

这个语句执行结果中会返回一个TABLE_ROWS用来显示表中有多少行。可能有人会说我们的count(*)直接使用这个结果不可以吗？答案是不可以，因为这个TABLE_ROWS是基于我们之前说的索引区分度统计时采用的采样估计方法估计的，会有误差，出错率为40%-50%，所以不准确。

下面来说另一个问题，那就是开头提到的：如果遇到数据频繁的变更并需要统计表行数的需求我们怎么来设计

针对这个问题，我们只能自己来设计计数。

方案1：利用缓存系统保存计数（不可取）

这个方案是利用缓存系统比如redis来存储计数，当插入一条记录的时候让计数器自增1。但是这个方案有两个方面的问题。

第一个方面：会丢失数据。比如redis还没有持久化的时候宕机了，那么重启之后这个计数器就不准了。当然这个问题可以解决，那就是如果redis宕机了，每次启动的时候都去数据库中执行count(*)一行行的读出真正的计数。所以这个问题不是核心问题。

核心问题也就是第二个方面的问题就是数据不一致问题，因为缓存系统和数据库系统属于两个不同的系统，所以会有分布式事务问题，导致的问题就是出现数据不一致的问题。例如下面的两个场景。

在这个例子中，会出现redis读取出来的是原始的数据，但是MySQL查出来的是最新的数据，有一个刚插进去的。

在这个例子中，会出现redis读出了最新的数据，但是MySQL没有把最新的数据读出来，取出来的还是原始的老数据。

方案2：在数据库保存计数（可取）

将计数直接放到数据库里单独的一张表中。

为什么使用数据库保存计数这个方案可取呢，我们方案1分析了是因为”事务“的存在导致了数据不一致。那么我们可以

”以子之矛攻子之盾“ 我们就利用”事务“的特性来解决这个问题。

例如下面的例子：

你会发现我们将保存计数这个过程也放在了一个事务中，将插入数据和计数加一放在一个事务中。这个时候B看到的计数和最近的记录是一致的，因为当计数更新的时候数据一定插入。（可以理解为封装成了一个原子操作）

下面说一个延申的问题，我感觉也很重要，那就是count(1),count(*),count(主键id)，count(字段)这些函数的性能比较

首先说一下count()的语义。count()是一个聚合函数，对于返回的结果集，一行行的判断，如果count函数的参数不是null的话，累计值就加一，否则不加。最后返回累计值。

count(*)、count(1)、count(主键id)参数都不会为null,所以直接返回满足条件的记录行数。而count(字段),则表示返回满足条件的数据行里面，参数"字段"不为null的总个数。

总结的话性能是：count(*)≈count(1)>count(主键id)>count(字段)

count(*)是经过优化的，推荐使用count( * )

最后提一个问题：

在刚刚讨论的方案中，我们用了事务来确保计数准确。由于事务可以保证中间结果不被别的事务读到，因此修改计数值和插入新记录的顺序是不影响逻辑结果的。但是，从并发系统性能的角度考虑，你觉得在这个事务序列里，应该先插入操作记录，还是应该先更新计数表呢？

解答：先插入操作记录，再更新计数表。因为前面我们学过，对于容易发生行锁冲突的语句我们要尽量放到一个事务的最后处理，这样可以减少占用锁的时间。对于更新计数表这个操作对一行进行更新，所以很容易发生行锁冲突，所以我们要放到后面。

还没有持久化的时候宕机了，那么重启之后这个计数器就不准了。当然这个问题可以解决，那就是如果redis宕机了，每次启动的时候都去数据库中执行count(*)一行行的读出真正的计数。所以这个问题不是核心问题。

[外链图片转存中…(img-zHUS6ylh-1683720934263)]