MySql热点知识回顾

ACID:是指数据库管理系统(DBMS)在写入或更新资料的过程中,为保证事务(transaction)是正确可靠的,所必须具备的四个特性:原子性(atomicity,或称不可分割性)、一致性(consistency)、隔离性(isolation,又称独立性)、持久性(durability)。

InnoDB预设是行锁,但是行锁是加到索引上的,如果一个查询没有使用索引,就会触发全表锁。MVCC通过间隙锁(锁定范围行)解决幻读问题。InnodeDB使用聚集索引。

MYISAM 是表级读写锁,读写互相阻塞,但读与读不会互相阻塞。MyISAM使用非聚集索引。

聚集索引与非聚集索引:

聚集索引在B+TREE中的叶子节点数据包含了记录中的全部数据(索引即数据),而非聚集索引的叶节点只包含记录的数据地址,指向对应的数据块。

聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。
聚集索引对于那些经常要搜索范围值的列特别有效。

非聚集索引在叶子节点只包含记录的数据地址,查到节点还需要去对应的文件读取数据。

MySql的刷盘时机:

InnoDB 存储引擎为 reod log 的刷盘策略提供了 innodb_flush_log_at_trx_commit,支持三种策略:
0:每次事务提交时,日志只写入redo buffer
1:每次事务提交时,立即执行完整的刷盘过程(默认)
2:每次事务提价哦吼,将log buffer 内容写入操作系统page cache

显然,0和2在服务器物理宕机时数据是会丢失的,而0在MySql服务宕机后就会丢失。

Innode存储引擎的还有一个后台线程,每隔1秒,就会自动将redo log buffer完整刷盘一次。

MySql如何保证数据安全
binlog: binlog是MySQL Server层级的逻辑日志,binlog只在数据每次提交修改后从cache写入磁盘。用于灾后数据恢复和主从复制。它可以记录原始sql语句,也可以记录原始数据值。但是记录sql会导致now()函数时间错误,记录原始数据值会消耗更大的空间。因此一般采用混合记录模式mixed,系统自行判断。

undolog 和 redolog 是InnoDB引擎层的机制,InnoDB通过这两个日志保证CrashSafe:

  1. 所以已经提交的事务数据仍然存在
  2. 所有没有提交的事务数据自动回滚

undolog: undolog 是InnoDB引擎层的日志,用来实现多版本并发控制(MVCC)。它是在操作数据之前,现将数据备份到UndoLog,然后进行数据修改,回滚是通过UndoLog中的备份数据将数据恢复。具体实现是记录一条相反测操作,当事务执行完毕之后,放到清理链表中,判断没有其他事务使用到相关信息时才清理。

redolog: redolog 也是InnoDB引擎层的日志。机制是每当事务发生数据更新操作时将更新操作记录在 redo log buffer中,提交时,将buffer持久化到磁盘。

整体的执行逻辑是:

  1. 事务执行前,先将数据备份到undolog
  2. 事务发生数据更新时,写入redolog buffer, 然后进入prepare状态,然后将redo log 刷盘。
  3. 等待其他分布式服务的prepare都执行成功,开始写binlog, 写好binlog后,将redolog 改为已提交,执行commit,然后等待清除undo log。
  4. 如果分布式事务失败,执行rollback,不需要写binlog,清除redolog, 执行undolog。

MySql 8.0+ 执行计划耗时分析:
analyze 关键字可以真实执行后面的SQL,并显示出每一步执行计划的实际耗时。用于SQL调优。

explain analyze select * from tb where tem=1;

一般的调优思路:
普通的表,5千万行数据一般2G内存左右
建立索引;
内存充足的情况下,可以考虑将超大表的engine从InnoDB 改为Memory,需要考虑Memory是表锁,不支持事务;或考虑双写,先写InnoDB再写Memory表;或允许数据库触发器时,使用触发器监听InnoDB表的变动,自动同步。

硬件方面,机械磁盘改为SSD,CPU需要高主频大核心;

MySql 文档索引 —— JSON类型
MySql 5.7存在Json类型的数据类型,与其他int,bigint,varchar,text等类型一样,但JSON可以直接被解析并建立文档字段索引,并在sql中引用。

select * from document where extra->'$.xxx_id'='12312_12312';

由于上述语句无法使用表索引,会导致全表扫描,因此引入虚拟列概念,修改建表结构,自动根据json建立虚拟查询列,该虚拟字段为只读列,并且可以建立索引:
`sql
alter table document
ADD COLUMN v_xxx_id varchar(32)
GENERATED ALWAYS AS (json_uniquote(json_extract(extra, _utf8mb4’$.xxx_id’))) VIRTUAL NULL;

CREATE INDEX idx_v_xxx_id on document(v_xxx_id);
`
json_extract从指定值字段中导出;json_uniquote解除关联,避免每次执行都导出;VIRTUAL NULL声明为虚拟字段,且可以为空;


转载请注明来源,欢迎对文章中的引用来源进行考证,欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论,也可以邮件至 using1174@foxmail.com

文章标题: MySql热点知识回顾

文章字数: 1,290

本文作者: Jun

发布时间: 2022-03-14, 15:41:00

最后更新: 2022-04-17, 22:49:42

原始链接: http://yoursite.com/2022/03/14/MySql热点知识回顾/

版权声明: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

目录
×

喜欢就点赞,疼爱就打赏