mysql实战笔记

 

笔记来源于mysql实战，却高于它

 

 

查询语句执行流程

• MySQL可以分为Server层和存储引擎层两部分。
• Server层：所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
• 而存储引擎层负责数据的存储和提取。支持InnoDB、MyISAM、Memory等多个存储引擎。InnoDB从MySQL 5.5.5版本开始成为了默认存储引擎。
• 不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。你可以先对每个组件的名字有个印象，接下来我会结合开头提到的那条SQL语句，带你走一遍整个执行流程，依次看下每个组件的作用。

 连接器：负责跟客户端建立连接、获取权限、维持和管理连接

 

 

 客户端连接超时由wait_timeout控制的，默认值是8小时。

修改命令

 SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
 SET GLOBAL wait_timeout=28800;

由该长连接造成内存占用过多解决方案：

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

2. MySQL 5.7以上版本，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存：以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果

多数情况不要使用，表更新，缓存清空然后又存进来。很少更新的静态表和动态表可以使用

静态表：字段有固定长

动态表：字段不定长

 

用缓存查询，不用缓存就写SQL_NO_CACHE

SELECT SQL_CACHE * FROM 表; 

 

windows的my.ini设置缓存类型. 0 for OFF, 1 for ON and 2 for DEMAND.

query_cache_type=2

查询缓存类型

SELECT @@query_cache_type;

SHOW VARIABLES LIKE '%query_cache%';

SHOW GLOBAL  STATUS LIKE '%Qcache%';

• Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，说明查询缓存中的内存碎片过多。
• Qcache_free_memory:表示Query Cache中目前剩余的内存大小。
• Qcache_hits:表示有多少次命中缓存。
• Qcache_inserts:表示多少次未命中缓存然后插入，意思是新来的SQL请求如果在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。
• Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移出查询缓存。
• Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
• Qcache_queries_in_cache:当前缓存中缓存的查询数量。
• Qcache_total_blocks:当前缓存的block数量。

flush query cache; – 整理，不删除缓存数据
reset query cache ; – 可删除缓存内容

 

 

备注：从MySQL 8.0版本以后直接取消了查询缓存的整块功能。

 

分析器：MySQL需要识别出里面的字符串分别是什么，代表什么

它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。

词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法

 

优化器：优化器是在表里面有多个索引的时候，决定使用哪个索引

 

执行器：调用InnoDB引擎接口取这个表的各行，重复相同的判断，如果不是则跳过，如果是则将这行存在结果集中；

慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。

 

 

核心日志模块

MySQL日志模块其中的三个核心日志，分别是redo log（重做日志）、undo log（回滚日志）、binlog（归档日志）。

crash-safe（字面意思：宕机安全）:MySQL 保证数据不会丢的能力主要体现在两方面：

1. 能够恢复到任何时间点的状态；-------可以通过重跑binlog实现
2. 能够保证MySQL在任何时间段突然奔溃，重启后之前提交的记录都不会丢失；------事务提交过程中任何阶段，MySQL突然奔溃，重启后都能保证事务的完整性，已提交的数据不会丢失，未提交完整的数据会自动进行回滚。这个能力依赖的就是redo log和unod log两个日志。

更新语句的执行流程：

1. 执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

 

 WAL(Write Ahead Log)：日志先行的技术，指的是对数据文件进行修改前，必须将修改先记录日志。保证了数据一致性和持久性，并且提升语句执行性能。

重做日志 redo log

redo log也称为事务日志，由InnoDB存储引擎层产生。记录的是数据库中每个页的修改，而不是某一行或某几行修改成怎样，可以用来恢复提交后的物理数据页（恢复数据页，且只能恢复到最后一次提交的位置，因为修改会覆盖之前的）。
MySQL在有事务提交对数据进行更改时，只会在内存中修改对应的数据页和记录redo log日志，完成后即表示事务提交成功，至于磁盘数据文件的更新则由后台线程异步处理。由于redo log的加入，保证了MySQL数据一致性和持久性（即使数据刷盘之前MySQL奔溃了，重启后仍然能通过redo log里的更改记录进行重放，重新刷盘），此外还能提升语句的执行性能（写redo log是顺序写，相比于更新数据文件的随机写，日志的写入开销更小，能显著提升语句的执行性能，提高并发量），由此可见redo log是必不可少的。

redo log是固定大小的，所以只能循环写，从头开始写，写到末尾就又回到开头，相当于一个环形。当日志写满了，就需要对旧的记录进行擦除，但在擦除之前，需要确保这些要被擦除记录对应在内存中的数据页都已经刷到磁盘中了。在redo log满了到擦除旧记录腾出新空间这段期间，是不能再接收新的更新请求，所以有可能会导致MySQL卡顿。（所以针对并发量大的系统，适当设置redo log的文件大小非常重要！！！）

 

回滚日志 undo log

undo log回滚的作用和多个行版本控制(MVCC)，保证事务的原子性。在数据修改的流程中，会记录一条与当前操作相反的逻辑日志到undo log中（可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录），如果因为某些原因导致事务异常失败了，可以借助该undo log进行回滚，保证事务的完整性，所以undo log也必不可少。

归档日志 binlog
binlog在MySQL的server层产生，不属于任何引擎，主要记录用户对数据库操作的SQL语句（除了查询语句）。之所以将binlog称为归档日志，是因为binlog不会像redo log一样擦掉之前的记录循环写，而是一直记录（超过有效期才会被清理），如果超过单日志的最大值（默认1G，可以通过变量 max_binlog_size 设置），则会新起一个文件继续记录。但由于日志可能是基于事务来记录的(如InnoDB表类型)，而事务是绝对不可能也不应该跨文件记录的，如果正好binlog日志文件达到了最大值但事务还没有提交则不会切换新的文件记录，而是继续增大日志，所以 max_binlog_size 指定的值和实际的binlog日志大小不一定相等。
正是由于binlog有归档的作用，所以binlog主要用作主从同步和数据库基于时间点的还原。

那么回到刚才的问题，binlog可以简化掉吗？这里需要分场景来看：
如果是主从模式下，binlog是必须的，因为从库的数据同步依赖的就是binlog；
如果是单机模式，并且不考虑数据库基于时间点的还原，binlog就不是必须，因为有redo log就可以保证crash-safe能力了；但如果万一需要回滚到某个时间点的状态，这时候就无能为力，所以建议binlog还是一直开启。

 

 

 

 

 

事务隔离

 事务隔离级别和可能造成的问题https://www.cnblogs.com/tk55/p/6624968.html

 

 

 

• 若隔离级别是“读未提交”， 则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被A看到了。因此，V2、V3也都是2。
• 若隔离级别是“读提交”，则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以， V3的值也是2。
• 若隔离级别是“可重复读”，则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看， V1、V2值是1，V3的值是2。

 

实现隔离机制的方法主要有两种 ：
加读写锁，例如串行化，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行
一致性快照读，即 MVCC

mvcc：多版本并发控制

通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

MVCC优势：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。

事务可以修改这条记录，反正每次修改都会在版本链中记录。SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。

在Mysql的InnoDB引擎中就是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。

INSERT
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
DELETE
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE
InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

mvcc实现原理：

每一行记录都有两个隐藏列：TRX_ID 、 ROLL_PTR （如果没有主键，则还会多一个隐藏的主键列）

TRX_ID
记录最近更新这条行记录的 事务 ID ，大小为  6  个字节
roll_pointer
表示指向该行回滚段 （rollback segment） 的指针，大小为  7  个字节， InnoDB  便是通过这个指针找到之前版本的数据。该行记录上所有旧版本。
DB_ROW_ID
行标识（隐藏单调自增  ID ），大小为  6  字节，如果表没有主键， InnoDB  会自动生成一个隐藏主键，因此会出现这个列。

 

组织版本链：在多个事务并行操作某行数据的情况下，不同事务对该行数据的 UPDATE 会产生多个版本，然后通过回滚指针组织成一条  Undo Log  链。

比如现在有个事务id是60的执行的这条记录的修改语句

 

undo日志中就存在版本链

 

 

 

ReadView

已提交读（RC）和可重复读（RR）的区别就在于它们生成ReadView的策略不同

RC：每次读取数据前都生成一个ReadView

RR：在第一次读取数据时生成一个ReadView

readview包含4个重要概念

• m_ids：表示在生成readview时，当前系统中活跃的读写事务id列表；
• min_trx_id：表示在生成readview时，当前系统中活跃的读写事务中最小的事务id，也就是m_ids中最小的值；
• max_trx_id：表示生成readview时，系统中应该分配给下一个事务的id值；
• creator_trx_id：表示生成该readview的事务的事务id；

• 有了readview，在访问某条记录时，按照以下步骤判断记录的某个版本是否可见
• 1、如果被访问版本的trx_id，与readview中的creator_trx_id值相同，表明当前事务在访问自己修改过的记录，该版本可以被当前事务访问；
• 2、如果被访问版本的trx_id，小于readview中的min_trx_id值，表明生成该版本的事务在当前事务生成readview前已经提交，该版本可以被当前事务访问；
• 3、如果被访问版本的trx_id，大于或等于readview中的max_trx_id值，表明生成该版本的事务在当前事务生成readview后才开启，该版本不可以被当前事务访问；
• 4、如果被访问版本的trx_id，值在readview的min_trx_id和max_trx_id之间，就需要判断trx_id属性值是不是在m_ids列表中？
• 如果在：说明创建readview时生成该版本的事务还是活跃的，该版本不可以被访问
• 如果不在：说明创建readview时生成该版本的事务已经被提交，该版本可以被访问；

 

 

 

 

 

 索引

 

 

 

 

 

 

 

 

 

--------待后续补充----------

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

参考https://mp.weixin.qq.com/s/5i9wmJs4_Er7RaYfNnETyA

       https://baijiahao.baidu.com/s?id=1629409989970483292&wfr=spider&for=pc