MySQL - 基本概念

MySQL逻辑架构可以分为Server层(连接器、查询缓存、分析器、优化器、执行器)和存储引擎层。

关于存储引擎

InnoDB存储引擎的特点:支持外键、行级锁、每个表单独位于一个表空间。

  • MyISAM和InnoDB的比较:
项目 MyISAM InnoDB
事务 ✔️
锁粒度 表级锁 表级锁&行级锁(锁的是索引对应的那一行)
外键约束 ✔️
表空间大小 相对小 相对大
全文索引 ✔️
关注点 性能(select) 事务(work)
数据文件 每张MyISAM表存放在三个文件中:
元数据:*.frm
数据文件:*.MYD
索引文件:*.MYI
(默认)共享表空间
其他 MySQL 5.5.5 以后的默认存储引擎
索引叶子节点中的数据 升序顺序存储数据,叶子节点保存对应数据行的地址 主键索引(聚集索引):叶子节点存的是整行数据
非主键索引(二级索引):叶子节点存的是主键的值
count(*) 行的总数存在磁盘上,可直接返回 自动匹配最优索引,返回行的总数(包括NULL)

日志系统

binlog redo log undo log
介绍 Server层的归档日志 InnoDB特有的 InnoDB特有的
内容 逻辑日志:“给ID=2这一行的C字段加1” 物理日志:“将a数据页的内容修改为xxx” 回滚日志:在执行更新语句时(MVCC)
作用 数据归档、主从复制 持久性,先写日志(和改内存)后写磁盘 原子性,所有操作要么全部做完 要么什么也不做
增长策略 “追加写”,不会覆盖以前的日志 “循环写”,固定空间用完之后,从头覆盖重写

事物隔离级别

隔离级别 脏读(Dirty Read) 不可重复读(NonRepeatable Read) 幻读(Phantom Read) 解释
读未提交(Read uncommitted) ✔️ ✔️ ✔️ 隔离级别最低,A事物修改的数据,还未提交,B事物就有可能看到,造成脏读。
读已提交(Read committed) ✔️ ✔️ 数据只有提交了,才会被其他事物看到。此种隔离级别下对于同一行数据是“不可重复读”的(因为是在第2次读之前,如果此数据被另一个事物修改了,两次的读到结果可能不一样)
【Oracle等多数数据库默认级别 】
可重复读(Repeatable read) ✔️ 可重复读。在同一个事务内的查询都是事务开始时刻一致的。有可能造成“幻读”(事物执行过程中看不到其他事物对数据的更新,或者查询到了上一次没有查询到的数据)
【InnoDB默认级别】
可串行化(Serializable ) 完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

MVCC

按照可重复读的定义:一个事物启动的时候,能够看到所有其他已经提交的事物的结果。但是之后,这个事物执行期间,其他事物的更新,对它不可见。以该字段对应的row trx_id相同的那个版本数据为准。

幻读

定义:A事物在第2次select时,读到了B事物update的信息。在第3次select时,读到了C事物insert的信息。

问题:此时如果A事物想update一些信息,要不要更新B、C事物提交上来的符合条件的数据行?

危害:有可能造成主从复制或者单独将binlog拿去克隆库时,最终数据不一致。

解决方案:

next-key lock(间隙锁 + 行锁),加锁规则:

  • 原则1:每个next-key lock锁住的是一个左开右闭区间。
  • 原则2:查找过程中访问到的对象才会加锁。
  • 优化1:索引上的等值查询,给唯一索引加锁时,next-key lock退化为行锁。
  • 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件时,next-key lock退化为间隙锁。
  • 一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

附:CentOS安装大法

  • yum –y install mysql
  • yum –y install mysql -server
  • service mysqld start //启动
  • /usr/bin/mysqladmin –u root password ‘root’ //设置密码
  • mysql –u root –p //进入
    • use mysql;
    • select host,user,password from user;
    • delete from user where password=’’;

1
2
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h vultr.guest password 'new-password'

本文标题:MySQL - 基本概念

文章作者:NibNait  

发布时间:2019年07月01日 - 12:07

最后更新:2019年08月21日 - 21:08

原始链接:https://tianbin.org/learning/mysql_basis/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

0%