【数据库 | MySQL】MySQL 实战教程笔记

序言

感谢林晓斌老师，感谢他的教程：https://funnylog.gitee.io/mysql45/

MySQL 的基础架构

主要分为两层

服务层

• 连接器：管理连接，验证权限。尽量使用长连接
• 查询缓存：对一个表的更新会清空这个表上的所有查询缓存。（8.0去掉缓存了）
• 分析器：词法分析和语法分析
• 优化器：选择索引
• 执行器：

存储引擎层

现在默认使用InnoDB

日志系统

redo log 重做日志

为了效率，MySQL采用WAL（write-ahead logging），先写日志再写入磁盘。

redo log是InnoDB存储引擎的概念，具体来说就是在更新记录时，先记录redo log，更新内存，这个时候就算更新成功了，然后再空闲的时候将操作记录更新到磁盘。

InnoDB的redo log是固定大小的，通过记录write pos和checkpoint进行循环读写。write pos是日志当前要写入的位置，checkpoint是当前要擦除的位置，当然，在擦除之前要将记录更新到数据文件。

innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘，这样可以保证MySQL异常重启之后数据不丢失，实现crash-safe的能力。

binlog 归档日志

binlog是MySQL服务层的概念，与redo log的区别在于：

• binlog是逻辑日志，记录语句的原始逻辑，有两种方式：记录sql语句或者记录更新前后的状态。redo log是物理日志，记录在某个数据页上做了什么修改。
• binlog可以追加写入，不会覆盖之前的。redo log是循环写的。

redo log的两阶段提交

更新数据时，redo log采用 两阶段提交 是为了保持两份日志的逻辑一致。
具体步骤为：

• 【执行器】取行数据
• 【存储引擎】返回行数据（如果在数据页内存中直接返回，否则从磁盘中取）
• 【执行器】修改行数据，调用引擎接口写入行数据
• 【存储引擎】更新行数据到内存，将操作记录更新到redo log，此时redo log处于prepare状态，告知执行器完成
• 【执行器】生成binlog，将binlog写入磁盘，调用引擎的提交事务接口，将redo log改为commit状态，更新完成。

如果两份日志不一样，会出现的问题，例如：

• 在数据恢复时，通过数据库全量备份+binlog重放来恢复到某个时刻的数据，如果两个日志不一致，就会导致数据与原库不同。
• 扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用binlog来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

事务隔离

事务的支持是在引擎层实现的。

事务的隔离级别

• 读未提交。一个事务未提交时，它所作的变更就会被其它事务看到。
• 读已提交。一个事务提交后，它所做的变更才会被其它事务看到。
• 可重复读。一个事务执行过程中看到的数据总是和事务开始时的一致。
• 串行化

实现时，数据库会创建视图，访问时以视图的结果为准。

• 可重复读级别的视图在事务开始时创建
• 读已提交级别的视图在每个SQL语句开始执行时创建
• 读未提交级别没有视图概念，直接读取记录的最新值
• 串行化通过加锁实现

隔离的实现

以可重复读为例，不同时刻的事务有不同的read-view。同一条记录在系统中存在多个版本，就是数据库的多版本并发控制（MVCC）
每条记录在更新时都会记录回滚操作，通过回滚日志可以得到之前状态的值。

注意，只有在系统中不存在更早的read-view时，对应的回滚日志才会删除，因此长事务会导致回滚日志占用大量空间。
通过显式启动事务语句可以避免无意中导致的长事务，也就是begin-commit-rollback。

索引

索引的模型

• 哈希表：没有顺序，只能等值查询
• 有序数组：可以等值和范围查询，但是更新麻烦，适用于静态存储引擎
• 二叉树/多叉树

数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景。

InnoDB的索引

在创建或设置主键时，mysql会自动添加一个与主键对应的唯一索引，不需要额外的操作。

在InnoDB中，表都是根据主键顺序以索引的形式（B+树）存放的，这种称为索引组织表。

• 主键索引叶子节点存放的是行数据，在InnoDB中又称为聚簇索引；
• 非主键索引叶子节点是主键的值，在InnoDB中又称为二级索引；

不难想到，主键查询方式只需要查询一棵树，而普通索引查询方式需要先查普通索引的树，得到主键的值，再查主键索引的树。
因此，应尽量使用主键查询。

在创建表时，KEY 和 INDEX 是同义词，都用来创建索引。

索引的维护

注意考虑以下几点：

• 维护索引时可能会导致数据页的分裂与合并，而自增主键只涉及追加操作，不会导致页分裂；
• 普通索引的叶子节点会存放主键索引的值，因此主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

覆盖索引

在查询过程中，需要回到主键索引树进行搜索获取数据的过程称为回表。

而如果一个索引中包含了要查询的数据，无需回表就可以直接满足查询需求，则称之为覆盖索引。
显然，使用覆盖索引可以减少搜索次数，提升查询速度，是一种优化手段。

考虑到覆盖索引的好处，那么就会想到建立更多的覆盖索引，而由多个字段组成的联合索引可以很好地满足各种查询的需求。
不过索引的维护有代价，需要权衡。

例如，下表有了name_age索引，就可以直接通过name查到age的值。

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

在创建联合索引时，索引项是根据定义中的字段顺序进行排序的，因此具有最左前缀原则。
也就是说，只有查询条件中包含第一个（最左）字段，才可以使用联合索引。

联合索引不仅可以通过最左前缀原则定位记录，还可以通过索引下推过滤记录（MySQL 5.6），减少回表次数。
在索引遍历过程中利用索引中包含的字段先做判断，过滤掉不满足条件的记录。
例如，name_age索引可以利用age字段做过滤，减少回主键索引搜索的次数。

锁

MySQL里面的锁包括：全局锁，表锁和行锁。

全局锁

对整个数据库加锁，其它线程的数据更新语句（增删改）、数据定义语句（建表、修改表结构等）和更新类事务的提交语句会被阻塞。

加全局读锁的方法：Flush tables with read block(FTWRL)

使用场景：做全库逻辑备份。防止备份的时候不同表的逻辑时间点不一致。

但是让全库只读可能存在问题：

• 备份主库，期间不能更新；
• 备份从库，不能执行主库binlog，导致主从延迟。

解决方法：使用官方逻辑备份工具mysqldump的-single-transaction参数，会启动一个事务确保一致性视图，依靠MVCC的支持，在整个过程中数据是可以正常更新的。但是要求所有表使用支持可重复读隔离级别的引擎，而MyISAM就不支持。

另外，使用 set global readonly=true 也可以让全库只读，但是 readonly 有其他用途，修改 global 变量的影响更大，不建议使用。而且如果使用FTWRL后客户端异常断开，MySQL会自动释放全局锁。

表级锁

MySQL 中表级别的锁有两种，分别是表锁和元数据锁（Meta data lock, MDL）。

表锁：

使用方法：lock tables ... read/write，可以用 unlock tables 主动释放锁。

对于使用 InnoDB 这种支持行锁的引擎，一般不使用表锁来控制并发。

MDL：

不需要显式使用，对表增删改查时自动加 MDL读锁，对表结构变更时，自动加 MDL写锁。

• 读锁之间不互斥；
• 读写锁之间、写锁之间互斥。

问题：如果一个申请写锁的请求被阻塞，之后所有申请读锁的请求也会被阻塞，使整个表不可读写。

解决方法：上面的问题在于长事务，事务不提交就会一直占着MDL锁。所以，

• 手动 kill 长事务或者暂停当前的 DDL
• 在 alter 语句中设置等待时间，超时后放弃

原文地址：http://www.cnblogs.com/tofengz/p/16728319.html