Mysql之基本架构+执行原理+隔离级别+索引+全局锁表锁

mysql基本架构

• server层

  • 连接器：管理连接，权限验证，先查询缓存，命中则返回结果。

  • 分析器：词法分析，语法分析

    • 词法分析，识别关键字，转换成列表或列

    • 语法分析，判断是否满足语法规则

  • 优化器：执行计划生成，索引选择

    • join联表操作执行方法的使用

  • 执行器：操作引擎，返回结果

    • 取到满足条件的第一行，逐行判断是否满足条件

• 存储引擎

  • mysql5.5之后Innodb是默认引擎，可以在建表语句中engine=memory来制定内存引擎。

• 连接完成后如果客户端默认8小时没动静，则会断开。

• 长连接：太多长连接占用系统内存，导致重启。可以设置正在执行一个较大的操作后，执行mysql_reset_connection来重新出事连接资源。

• 查询缓存

  • 会看这条sql语句之前是否执行，若是，结果会议key-value的形式缓存在内存中，直接返回即可，因为查询失效非常频繁，所以一般不使用。

 

一条SQL语句是如何执行的

• redo log

  • WAL (write-ahead logging) 先写日志，再写磁盘

  • 当需要更新表中某一条记录的时候，先写到redo log离，更新内存，innodb会在适当的时候更新到磁盘。

  • redo log大小是固定的，有checkpoint擦除点和write pos写入点, 如果有0，1，2，3四块redo log，写完了记到磁盘擦除可以继续写，writepos是当前写的位置，checkpoint是删除的位置，当writepos在2中某点追上了checkpoint，则会停止写入，等待checkpoint擦除一些位置。

  • 如果数据库发生了异常重启，之前提交的记录都不会丢失。

• binlog

  • redo log是InnoDB引擎特有的日志，server层也有自己的日志binlog(归档日志)

• 两者不同点：

  1. redo log是innodb引擎特有的，binlog位与server层，所有引擎都可用

  2. redo log是物理日志，记录的是某个数据页做了什么修改，binlog是逻辑日志，记录逻辑关系，比如给某个字段的值加了1

  3. redo log是循环使用的，binlog是追加写的，会生成新的，不会覆盖原来的

• 执行update的内部流程

  1. 执行器取ID=2这一行，ID是主键，引擎用树搜索可以找到，如果所在的数据页存在于内存，直接返回给执行器，否则从磁盘读到内存再返回

  2. 执行器拿到行数据，把这个值+1，得到新数据，调用引擎接口写入新数据

  3. 引擎讲新数据更新到内存中，记录到redo log里，此时redo log处于prepare状态，告诉执行器已经完成了，随时可以提交事务。

  4. 执行器生成这个操作的binlog，并写入磁盘

  5. 执行器调用引擎提交事物的接口，引擎将redo log改成commit状态，更新完成。

• 为什么要有两次日志操作

  • 在分布式系统中，mysql很可能是主从复制的，如果在写入数据时，数据库发生了重启，binlog就会失效，等于只提交了redo log导致主从数据不一致，如果只提交binlog，slave数据库会多操作，而主数据库未操作。导致数据不一致

事物的隔离级别

• 读未提交：一个事物还没提交时，他做的变更就能被别的事物看到

• 读提交：一个事物提交之后，他做的变更才会被其他事物看到

• 可重复读：一个事物执行过程中看到的数据总跟这个事物在启动时看到的数据是一致的，在此级别下，未提交变更对其他事物也是不可见的。

• 串行化：对同一行记录，写会加写锁，读会加读锁，出现读写锁冲突的时候，后访问的事物必须等待前一个事物执行完成才会继续执行。

  • 例子：假设数据表只有一列，其中一行的值为1，下面按照时间顺序执行两个事物。

  • 

• 读未提交：v1= v2 = v3 = 2

• 读提交：v1 = 1, v2=v3=2

• 可重复读：（事物在执行期间看到的数据必须一致） v1=v2=1,v3=2

• 串行化：v1=v2=1,v3=2

 

• 事物隔离的实现，运用回滚日志完成，每次更新都会记录回滚操作。

• 尽量不要使用长事物：存在很多很老的事物视图，由于事物可能随时访问数据库里的任何数据，所以在提交之前，混滚记录都会保留，导致大量占用存储空间

• 事物启动方式

  • begin 或者 start transaction，提交用commit，回滚用rollback

  • set autocommit=0，会将线程的自动提交关掉，需要主动执行commit或rollback语句才会执行，或者断开连接。

 

InnoDb索引模型

• 根据叶子节点的内容，索引分为主键索引和非主键索引

  • 逐渐索引的叶子节点存的是整行数据，在innodb中主键索引也被称为聚簇索引

  • 非主键索引的叶子检点是主键的值，非主键索引也被称为二级索引

• 回表，找到二级索引再找聚簇索引

• 自增主键对于数据的添加，性能和存储空间都优于其他。

• 覆盖索引

  • 查询中，索引已经覆盖了我们的查询需求，称为覆盖索引，覆盖索引可以避免回表，减少了树的搜索次数，提升性能，是一个常用的优化手段

• 最左前缀

  • 快速定位并且向后遍历，知道不满足条件为止。可以是最左n个字段，也可以是n个字符

  • 如果(a,b)已经见了联合索引，一般不需要单独建a索引

• 索引下推

  • 在索引遍历中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

  • mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

  • 无索引下推

    • 

  • 索引下推

    • 

mysql锁

• MVCC，Multi-Version Concurrency Control，多版本并发控制。

• 全局锁（flush table with read lock）

  • 需要让整个库处于只读状态的时候，可以使用，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

  • 使用场景：做全库逻辑备份，把整库每个表都select出来做成文本

    • 如果在主库备份，期间不能执行更新，业务停摆

    • 在从库上做备份，不能执行主库同步过来的binlog，主从延迟

  • 对于不支持事物的引擎，只能ftwrl让全库进入只读状态

  • set global readonly=true作用

    • 用来判断一个库是主库还是从库

    • 如果客户端异常，数据库一直保持只读状态，不可写，ftwrl在客户端已尝试可以释放全局锁，保持正常更新

• 表级锁

  • 表锁，语法lock tables …… read/write

    • 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。

  • 元数据锁，metadata lock。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。MDL直到事物提交才会释放。

    • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

    • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

  • 例子

  • 我们可以看到session A先启动，这时候会对表t加一个MDL读锁。由于session B需要的也是MDL读锁，因此可以正常执行。 之后session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。 如果只有session C自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。前面我们说了，所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了。 如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session 再请求的话，这个库的线程很快就会爆满。

• DML 增删改数据 DDL加字段等修改表结构的操作

• 加字段：

  • 长事物不提交就会占用mdl锁，考虑暂停ddl或者kill长事物

  • 在alter table语句里设定等待时间，拿到锁就修改，拿不到放弃修改

• 思考题：备份一般都会在备库上执行，你在用–single-transaction方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？