mysql的一些知识点

mysql

一条SQL更新语句是如何执行的

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

redo log的写入有两个步骤,第一次是praper状态,事务commit后才会更新为commit状态。

redo log

redo log 是 InnoDB 引擎特有的, redo log 是物理日志,记录的是“在某个数据页上做了什么修改” ,会有专门的pure线程处理redo log

undo log

回滚日志,在 MySQL 中,实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值,通过回滚操作,都可以得到前一个状态的值。MVCC中,一个记录有多个版本,要得到某个版本的值,就是通过undo log计算到的。

事务与mvcc

Innodb实现事务隔离,是通过mvcc和锁实现的。一致性读的时候(就是快照读),不需要加锁,直接通过mvcc实现。

快照读(所有的select语句)

在实现上, InnoDB 为每个事务构造了一个数组,用来保存这个事务启动瞬间(事务启动是指第一次查询sql时,不是 start transtion),当前正在“活跃”的所有事务 ID。“活跃”指的就是,启动了但还没提交。这个数组中,最小值称为低水位,最大值加1称为高水位。读取一行数据得到 row trx_id,通过这个数组来判断是否可见,如果不可见,通过undo log得到上一个版本的数据。

当前读

如果是当前读,就必须是读最新的版本信息,就要加上行锁锁住这行。一直到当前的事务提交。当前读的情况有:update t set k=k+1 where id=1 ….

为什么要重建表

innodb经过大量的增删改之后,数据页会留下大量空洞,浪费磁盘空间,重建表可以解决这个问题。

页合并和页分裂

innodb删除某个记录,其实就是标记这个位置可复用。 当页中删除的记录达到MERGE_THRESHOLD(默认页体积的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。 当插入或者更新记录时,当前页已经满了,需要在这个页中间插入一个记录,产生页分裂,同时产生页空洞。

重建表

你可以使用 alter table A engine=InnoDB 命令来重建表。

联合索引的使用

index(name, age),先按照name排序,然后是age排序,索引上保留name和age的值

覆盖索引

查询的字段已经在索引上获取,不用回表查询。

最左前缀原则
索引下推

select * from tuser where name like ‘张%’ and age=10

会从索引找到张开头的数据,然后过滤age=10后,再去回表查询。就是说会尽力在索引中过滤完条件再去回表,而不是回表完在过滤其他where条件。

Order By使用索引

innodb的索引有两大功能,快速定位记录,还有就是排序功能。尽力保证order by的字段利用到索引

Change Buffer和redo log

change buffer作用,当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下(没有唯一索引),InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

写入或者更新一条数据

对于唯一索引来说,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;

对于普通索引来说,则是将更新记录在 change buffer,语句执行就结束了。(如果马上有读取这个记录,还是需要去读取这行记录到内存,然后merge,所以说如果说change bufer更适合在写多读少的情况,并且没有用到唯一索引)

redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

Mysql与客户端如何交互

我们查询一个大数据的时候,需要担心把mysql内存爆掉吗?

实际上,服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的:
1.获取一行,写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的,默认是 16k。
2.重复获取行,直到 net_buffer 写满,调用网络接口发出去(socket)。如果发送成功,就清空 net_buffer,然后继续取下一行,并写入 net_buffer。
3.如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK,就表示本地网络栈(socket send buffer)写满了,进入等待。直到网络栈重新可写,再继续发送。

也就是说,MySQL 结果是“边读边发的”,这个概念很重要。这就意味着,如果客户端接收得慢,会导致 MySQL 服务端由于结果发不出去,这个事务的执行时间变长。

如果net_buffer写满了,mysql执行show processlist显示Sending to client

mysql客户端接收服务端返回结果的方式有两种:

一种是本地缓存,也就是在本地开一片内存,先把结果存起来。如果你用 API 开发,对应的就是 mysql_store_result 方法。

另一种是不缓存,读一个处理一个。如果你用 API 开发,对应的就是 mysql_use_result 方法。

Mysql内部临时表

union

会有去重操作,所以会生成一个临时表保存数据,每次获取一行,需要判断是否存在。union all不需要去重,所以不会生成临时表,直接返回数据即可。

group by

select a,count(1) as num from t group by a

1.创建内存临时表,表里有两个字段 a 和 num,主键是 a; //group by 字段就是主键

2.从t中扫描数据(可能会选择一个索引),写入到内存临时表中。由于a是主键,所以写入第一条后,后面的只会修改聚合的字段,比如num+1.

3.遍历完后,再根据a字段排序,得到结果集返回客户端。 如果你的需求并不需要对结果进行排序,那你可以在 SQL 语句末尾增加 order by null

explain结果,extra字段包含,Using temporary; Using filesort

磁盘临时表

内存临时表的大小是有限制的,参数 tmp_table_size 就是控制这个内存大小的,默认是 16M。 这时候就会把内存临时表转成磁盘临时表,磁盘临时表默认使用的引擎是 InnoDB

group by 优化方法 – 索引

不论是使用内存临时表还是磁盘临时表,group by 逻辑都需要构造一个带唯一索引的表,执行代价都是比较高的。 首先看看临时表的作用,因为扫描的数据得到的a是无序的,所以需要构造一个主键为a的临时表,好随时修改对应主键的聚合值。如果扫描出来就是a有序的,就不需要临时表了。

explain 语句,extra显示,Using index,表示group by 索引字段,不用临时表,不用排序

group by 优化方法 – 直接排序

如果没有办法避开临时表和排序,可以估算数据,直接走磁盘临时表。不然会先放到内存临时表,插入一部分数据后,发现内存临时表不够用了再转成磁盘临时表”,看上去就有点儿傻。

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!