MySQL实战学习(五)

一、MySQL内存、redo log、磁盘

1.介绍

内存:MySQL读取数据时,找到相应数据页,然后将数据读到内存中。

redo log:孔乙己例子中,店家的小黑板。

磁盘:孔乙己例子中,店家的账本。

注:更新数据时,更新了内存中的数据并且写了redo log后,直接返回成功,此时这就是一个脏页。

2.脏页与干净页

脏页:当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。
干净页:内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”
注:不论脏页还是干净页都在内存中。
flush:把内存里的数据写入磁盘的过程

3.InnoDB 刷脏页时机

a.redo log 写满时。此时系统会停止所有更新操作,来推进 checkpoint,就需要将两个点之间的日志,对应的所有脏页都 flush 到磁盘上。

b.内存不够用时。

注:内存不够用时,可以直接丢掉脏页(因为redo log中有记录,下次读数据时可以从磁盘读入数据页,然后拿 redo log 出来应用),但是并没有采取这种策略,而是采用将脏页的数据进行flush,这是方便下次读数据时,可以读入内存后直接返回(不用再根据redo log计算),可以更高效。

c.系统空闲的时候。

d.MySQL正常关闭时。

二、order by工作流程

1.全字段排序

select city,name,age from t where city=‘杭州’ order by name limit 1000  ;假设city字段有普通索引。则执行流程为:

  1. 初始化 sort_buffer,确定放入 name、city、age 这三个字段;
  2. 从普通索引找到第一个满足条件的主键 id;
  3. 到主键 id 索引取出整行,取 name、city、age 三个字段的值,存入 sort_buffer 中;
  4. 从普通索引city 取下一个记录的主键 id;
  5. 重复上述3、4过程;
  6. 对 sort_buffer 中的数据按照字段 name 做快速排序;
  7. 按照排序结果取前 1000 行返回给客户端。

注:MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成。但如果太大,则使用外部排序。

2.rowid 排序

如果需要返回的字段太长,则可能会使用rowid排序,具体流程为:

只取需要排序的字段(即上面的name)和主键id,找到所有满足条件的数据后,根据name排序,然后再根据主键id取相应的记录limit返回(全字段排序是取出对应的字段,然后排序后直接从sort buffer返回,而rowid是找到符合条件的主键id,再查询一遍返回)。

MySQL实战学习(四)

一、事务隔离

表t:
mysql> CREATE TABLE t (
  id int(11) NOT NULL,
  k int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);
 
事务查询时,事务id(100,101,102)、低水位(99)、高水位示意图:

事务A查询看到的k为1,事务B查询看到的k为3。如果事务B在update之前有一条查询,则查到的k为1,因为update用到了“当前读”这条规则(update时候会先读后写,读的值为实际的值,也就是2,并不是根据trx_id(事务id)和undoLog计算出来的快照(也就是1))。

更新数据都是先读后写的,而这个读,只能读当前的值,称为“当前读”(current read)。

二、快照

快照并不是保存当前的视图,而是根据trx_id和undoLog计算得出。

begin/start transaction 命令并不是一个事务的起点,在执行到它们之后的第一个操作 InnoDB 表的语句(第一个快照读语句),事务才真正启动。使用 start transaction with consistent snapshot 这个命令可以马上启动一个事务
下面这两个 select 语句,就是分别加了读锁(S 锁,共享锁)和写锁(X 锁,排他锁)。
mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update; 

三、change buffer

1.介绍

当需要更新一个数据页时,如果该数据页没有被读到内存中就将操作缓存在change buffer中(减少从磁盘中将该数据页读入内存的IO操作,减少读磁盘),如果内存中已经有,就直接更新。

2.merge

后续查询如果需要访问该数据页,则将change buffer中的操作应用到原数据页,该过程叫做merge,。
注:除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作。
注:虽然名字叫作 change buffer,实际上它是可以持久化的数据。也就是说,change buffer 在内存中有拷贝,也会被写入到磁盘上。

3.索引选择

唯一索引的更新不能使用 change buffer(唯一索引需要判重,需要读入数据页),实际上只有普通索引可以使用
如果数据页不在内存中:插入的数据是主键时,需要磁盘IO将数据页读入内存,插入数据;插入的数据上有普通索引时,将更新记录在 change buffer,语句结束。

4.change buffer参数设置

change buffer 用的是 buffer pool 里的内存,可以通过参数 innodb_change_buffer_max_size 来动态设置,参数代表占用 buffer pool 的百分比。

5.change buffer适用场景

change buffer适用于写多读少的情况(往数据页写入了很多的数据,然后读该数据页的时候触发写磁盘),比如账单类、日志类系统。

6.带change buffer的更新过程

 insert into t(id,k) values(id1,k1),(id2,k2);
 
如图:假设为当前 k 索引树的状态,k1 所在的数据页在内存 (InnoDB buffer pool) 中,k2 所在的数据页不在内存中。
先写将id1写入page1(写内存),然后将id2写入change buffer(写内存) ,将上述两个操作写入redo log(写磁盘)。

7.change buffer 和 redo log的区别

redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
注:redo log是先写小黑板,等到一定程度再一起写账单;change buffer免去了读入数据页,在下次读入数据页的时候,做merge再一把更新。

8.merge过程

  • 从磁盘读入数据页到内存(老版本的数据页);
  • 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个),依次应用,得到新版数据页;
  • 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。
到这里 merge 过程就结束了。这时候,数据页和内存中 change buffer 对应的磁盘位置都还没有修改,属于脏页,之后各自刷回自己的物理数据。

MySQL实战学习(三)

一、MySQL锁

1.全局锁

flush table with read lock;    // 让整个库处于只读状态(其他增删改查都会失效),当该session关闭后,锁自动失效


set global read_only=true;  // 当前session依旧可以更新数据,其他session回变为只读

2.表级锁

MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。

a.表锁:

lock tables … read/write。
lock table user read;  // 给user表加读锁 (用table和tables都可以)(其他线程写会阻塞)
lock tables user write; // 给user表加写锁  (其他线程读、写阻塞)
unlock tables;      // 释放锁

b.元数据锁(MDL)

在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。(系统默认加的锁)

3.行锁

MySQL 的行锁是在引擎层实现的。 MyISAM 引擎不支持行锁。InnoDB 支持行锁。

在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束(commit)时才释放。这个就是两阶段锁协议

4.死锁检测

当出现死锁以后,有两种策略:
  • 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  • 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑。

 

MySQL实战学习(二)

一、MySQL索引

在 MySQL 中,索引是在存储引擎层实现的,所以不同存储引擎的索引的工作方式并不一样。

1.索引数据结构

  • 哈希表:哈希表这种结构适用于只有等值查询的场景,不适合做区间查询,因为哈希表是无序的,区间查询要全表扫描。
  • 有序数组:查询效率高,但是更新数据比较麻烦,往中间插入一个记录就必须得挪动后面所有的记录。所以,有序数组索引只适用于静态存储引擎
  • 二叉搜索树:查询和更新的时间复杂度都是 O(log(N)),但是存在树太“高”的问题。二叉树搜索效率最高,但是因为索引会存储在磁盘,树太高会导致磁盘寻址(找对应数据块)次数太多(查询一条数据需要多次磁盘寻址找节点)。
  • B+树(“N叉树”):为了让一个查询尽量少地读磁盘,所以就让树尽量“矮”,就要多分叉。
以 InnoDB 的一个整数字段索引为例,这个 N 差不多是 1200。这棵树高是 4 的时候,就可以存 1200 的 3 次方个值,这已经 17 亿了。考虑到树根的数据块总是在内存中的,一个 10 亿行的表上一个整数字段的索引,查找一个值最多只需要访问 3 次磁盘。其实,树的第二层也有很大概率在内存中,那么访问磁盘的平均次数就更少了。

2.InnoDB 的索引模型

InnoDB 使用了 B+ 树索引模型,所以数据都是存储在 B+ 树中的,每一个索引在 InnoDB 里面对应一棵 B+ 树
表T:ID为主键, k 上有索引。
mysql> create table T(
id int primary key, 
int not null
name varchar(16),
index (k))engine=InnoDB;
左边为主键索引(聚簇索引),主键索引叶子节点存储的是整行数据;右边为非主键索引(普通索引、二级索引),叶子节点存储内容是主键的值。使用主键索引查询可以直接获得数据,而使用普通索引需要先搜索得到主键的值,然后再根据主键查询主键索引得到数据(这个过程叫回表)。
也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询

3.索引维护

当更新或删除数据时,需要更新索引。当在一个已满的数据页插入数据时,则需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。页分裂将原本放在一个页的数据,分到两个页中,整体空间利用率降低大约 50%。

当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。
注:在 InnoDB 中,每个数据页的大小默认是 16KB。

4.为什么建表语句里一定要有自增主键

可以保证每次插入一条新记录,都是追加操作(递增),不会触发叶子节点的分裂,而用业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。此外,主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

二、覆盖索引及索引下推

1.覆盖索引

对于T中k作为普通索引,select * from T where k between 3 and 5  该语句会先查找k索引的B+树,找到满足条件的节点后依次回表,所以需要查找N次k的索引B+树,查找N-1次的主键索引B+树(比普通索引少一次)。
为了减少回表次数,对于高频的查询,可以建立联合索引(为了满足覆盖索引)来优化性能。假如(a,b)作为普通索引,如果有许多根据a查询b的需求,则可以建立该索引来提高性能,因为这个请求可以用到覆盖索引,不需要再回表查询整行数据。

2.索引下推

select * from user where name like '张 %' and age=10; 假如存在联合索引(name,age)。

对于该语句,MySQL 5.6 之前,找到满足条件的name后(不查看普通索引B+树中age的值),依次回表。而在MySQL 5.6 引入了索引下推优化(index condition pushdown), 对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数(也就是在普通索引B+树中判断age是10的记录,才会回表)。

三、MySQL索引选择

1.基数(cardinality)

一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。

理论上来说假设该索引上没有重复的数据,则基数就是数据的行数,但是实际并不是。因为全部扫描后统计太消耗性能,所以InnoDB采用采样统计,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。

注:使用show index from table命令可以看到索引的基数。当数据行数的修改超过一定值后,会再次重新统计基数。

2.索引选择原则

InnoDB选择索引的时候,MySQL优化器会根据预计扫描的行数来判断选择哪个索引。而预计扫描的行数则根据基数来确定。

注:获取预计扫描行数用explain select …可以看到。

3.重新统计索引信息

如果发现 explain 的结果预估的 rows 值跟实际情况差距比较大,可以使用analyze table xxx 命令来重新统计索引信息。

 

四、前缀索引(适用于给字符串字段加索引)

1.语句:

alter table SUser add index index2(email(6)); // email前六个字节作为索引
注:使用前缀索引会用不上覆盖索引对查询性能的优化。

2.前缀索引长度选择

需要找到一个合适的区分度,可以使用如下语句判断应该定义多长的前缀索引。

select 
  count(distinct left(email,4))as L4,
  count(distinct left(email,5))as L5,
  count(distinct left(email,6))as L6,
  count(distinct left(email,7))as L7,
from SUser;
注:使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。

MySQL实战学习(一)

一、MySQL基本架构示意图

连接器:负责客户端的连接,使用用户名密码登陆时,连接器会到权限表里面查出该账户拥有的权限。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。

连接分为长连接和短连接。长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。建议尽量减少建立连接的动作,也就是尽量使用长连接。

注:长连接建立太多可能会发生OOM,所以解决方法如下:定期断开长连接。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后要查询再重连。

如果你用的是 MySQL 5.7 或更新版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。

查询缓存之前执行过的语句及其结果可能会以 key-value 对的形式,被直接缓存在内存中。key 是查询的语句,value 是查询的结果。
注:当该表数据有更新时,会将该表上的所有缓存清空。
执行器:开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返回没有权限的错误 (如果命中查询缓存,会在查询缓存返回结果的时候,做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

 

注:一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限。修改完成后,只有再新建的连接才会使用新的权限设置

二、数据库更新

1.redo log和binlog

  • redo log 是 InnoDB 引擎特有的,而binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用
  • redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
  • redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。

2.innoDB引擎处理流程

举孔乙己赊账的例子,店铺赊账用的粉板和账本,先写粉板,等不忙的时候再写账本。粉板相当于是redo log,账本是磁盘(如果直接写磁盘IO成本和查找成本都太高,写磁盘要先找对应的那条记录)。WAL技术(Write-Ahead Logging),它的关键点就是先写日志,再写磁盘

3.redo log和binlog协助处理流程

使用两阶段提交。先写redo log为prepare,后写binlog,然后更新redo log为commit状态。

  • 引擎查找需要修改的行返回给执行器,执行器修改该行然后调用引擎接口写数据;
  • 引擎将数据更新到内存中,同时写redo log,redo log处于prepare状态;
  • 写完后通知执行器,执行器写binlog日志;
  • 执行器调用提交事务接口,引擎将redo log状态修改为commit状态。

4.redo log细节

InnoDB 的 redo log 是固定大小的,比如可以配置为一组 4 个文件,每个文件的大小是 1GB,那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写,写到末尾就又回到开头循环写,如下面这个图所示。

write pos 是当前记录的位置,一边写一边后移,写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间的是“粉板”上还空着的部分,可以用来记录新的操作。如果 write pos 追上 checkpoint,表示“粉板”满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把 checkpoint 推进一下。
有了 redo log,InnoDB 就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe

三、事务隔离

1.事务隔离级别

  • 若隔离级别是“读未提交”,则另一事务修改后就可见;(不存在视图一说)
  • 若隔离级别是“读提交”,则另一事务提交后才可见;(在每个SQL执行时创建视图)
  • 若隔离级别是“可重复读”,则只要该事务执行期间,查询到的值都是一样的,不管另一事务事务已提交;(通过在事务启动时创建视图实现
  • 若隔离级别是“串行化”,则事务是按顺序执行的,不存在并发问题;(通过加锁实现)

2.事务隔离的实现

事务隔离通过回滚日志(回滚操作)实现。

假设一个值从 1 被按顺序改成了 2、3、4,在回滚日志里面就会有类似下面的记录。
同时你会发现,即使现在有另外一个事务正在将 4 改成 5,这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

注:查找持续时间超过 60s 的事务语句:

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60