MySQL逻辑架构

图片说明

连接管理和安全性

  • 每个客户端连接都有一个线程，线程轮流在CPU中运行。服务器会缓存线程，不用为每个连接新建线程。
  • 服务器基于用户名、主机信息和密码对客户端认证，连接成功继续验证客户端是否具有执行某个特定查询的权限。

优化和执行

  • 解析查询，创建解析树，对其优化，包括重写查询、决定表的读取顺序、选择合适的索引。
  • 请求优化器解释（explain）优化过程的各个因素。
  • select语句解析查询之前，服务器会先检查缓存（query cache）。

并发控制

读写锁

  • 共享锁（读锁）、排他锁（写锁）

锁粒度

  • 只锁定需要修改的部分数据，可以提高资源并发性。但并发性导致的加锁也需要消耗资源。锁策略需要在锁开销和数据安全性之间寻求平衡。

表锁

  • 最基本、开销最小的策略。锁定整张表，对表的写操作需要获得写锁，阻塞其他用户的写操作。读锁之间不阻塞。
  • 写锁比读锁具有更高的优先权。

行级锁

  • 最大程度支持并发。InnoDB和XtraDB存储引擎都实现了行级锁。而MySQL服务器层没用实现。

事务

  • 事务是一组原子性的SQL查询。全部执行成功或全部失败。
  • 原子性（atomicity）： 事务被视为不可分割的最小工作单元。
  • 一致性（consistency）： 数据库从一个一致性转换到另一个一致性状态。数据没有错误损失。
  • 隔离性（isolation）： 一个事务修改在最终提交之前，对其他事务不可见。
  • 持久性（durability）： 事务提交，所做的修改会永久保存到数据库中。

隔离级别

  • READ UNCOMMITED 未提交读： 读取未提交的数据（脏读）
  • READ COMMITED 提交读： 只能看见提交事务所做的修改（不可重复读）
  • REPEATABLE READ 可重复读： 同一事务多次读取同样记录的结果是一致的，会有幻读的问题。当某个事务在读取某个范围的记录时，另一个事务又插入了新的记录，之前的事务再次读取该范围的记录时，会产生幻行。
  • SERIALIZABLE 串行化： 最高级别。强制事务串行执行，每一行数据上加锁。

死锁

  • 两个或多个事务在同一资源上相互占用，请求锁定对方占用的资源，导致恶性循环的现象。多个事务同时锁定同一个资源时，也会产生死锁。
  • InnoDB存储引擎能检查死锁的循环依赖，立即返回错误。处理方法： 将持有最少行级排他锁的事务进行回滚。

事务日志

  • 预写式日志： 修改表数据时，只需要修改内存拷贝，再把行为记录在事务日志上。

MySQL事务

  • 自动提交 AUTO COMMITED： 默认采用自动提交模式。
  • 在事务中混合使用存储引擎是不可靠的。
  • InnoDB采用两阶段锁定协议： 事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT和ROLLBACK时才会释放。
  • 除了事务中禁用了AUTOCOMMIT，可以使用LOCK TABLES之外，其他时候不要显式执行LOCK TABLES。

多版本并发控制 MVCC

  • 通过保存数据在某个时间点的快照实现。不管需要执行多长时间，每个事务看到的数据是一致的。根据事务开始时间不同，每个事务对同一张表看到的数据可能是不一样的。
  • InnoDB的MVCC通过在每行记录后保存两个隐藏列实现。保存了行的创建时间和过期时间（系统版本号）。每开始一个新事务，版本号自动递增。在REPEATABLE READ和READ COMMITED工作。
    • SELECT： INNODB只查询版本早于当前事务版本的数据行，确保事务读取的行在事务开始时已经存在或事务自身插入或修改过。行的删除版本或未定义或大于当前事务版本号。
    • INSERT： InnoDB为新插入行的每一行保存当前系统版本号作为行版本号。
    • DELETE： InnoDB为新插入行的每一行保存当前系统版本号作为删除标识。
    • UPDATE： InnoDB为插入一行新纪录，保存当前版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

MySQL存储引擎

InnoDB存储引擎

  • MySQL默认事务型引擎，被设计用来处理大量的短期事务，大部分情况是正常提交的，很少会回滚。
  • InnoDB的数据存储在表空间（tablespace）中，表空间是InnoDB管理的一个黑盒子，由一系列数据文件组成。
  • 默认隔离级别是可重复读，通过间隙锁防止幻读，对索引中的间隙进行锁定，防止幻影行的插入。
  • 基于聚簇索引建立的。二级索引（非主键索引）中必须包含主键列，如果主键列很大的话，所有索引都会很大。
  • 优化： 从磁盘读取数据时采用可预测性预读、自动在内存中创建hash索引加速读操作的自适应hash索引、加速插入操作的插入缓冲区（insert buffer）。

MyISAM存储引擎

  • MySQL 5.1及之前版本的默认存储引擎。特性： 全文索引、压缩、空间函数等，不支持事务和行级锁。
  • 将表存储在两个文件中： 数据文件（.MYD）和索引文件（.MYI）。表可以包含动态或静态（固定长度）行。
  • 特性： 加锁（对整张表加锁，读取时加共享锁，写入时加排他锁） 修复（手工或自动执行检查和修复操作） 索引特性（支持全文索引、复制索引） 延迟更新索引键（修改执行完成时，不会立刻修改的索引写入磁盘，而是会写到内存区的键缓冲区，清理缓冲区或关闭表时才写入磁盘） 压缩表 性能主要受表锁影响。

其他存储引擎

  • Archive引擎： 只支持INSERT和SELECT 日志和数据采集应用
  • Blackhole： 丢弃所有插入数据。
  • CSV： 将CSV作为MySQL表处理，不支持索引。
  • Federated： 访问其他MySQL服务器的一个代理。
  • Memory： 保存在内存中，不需要磁盘IO。

选择合适的存储引擎

  • 事务、备份、崩溃恢复、特有特性
  • 日志型应用： MyISAM/Archive
  • 只读或大部分情况下只读： MyISAM，但不要低估崩溃恢复问题
  • 订单处理： InnoDB
  • BBS和论坛： MyISAM可能会更快
  • CD-ROM应用： MyISAM 表之间隔离并在不同介质上拷贝
  • 大数据： Infobright/TokuDB