高性能MySQL
第一章 MySQL架构
1.1 MySQL的逻辑架构
第一层,连接器,处理连接,认证授权、安全等
第二层,查询缓存、解析器、优化器,大多MySQL的核心功能都在这一层,包括查询解析、分析、优化、缓存一级所有的内置函数,和跨存储引擎的功能:存储过程、触发器、视图等
第三层,存储引擎层,负责MySQL中数据的存储和提取,服务器通过API与存储引擎层通信,这些接口屏蔽了不同存储引擎的差异
1.1.1 连接管理与安全性
每个MySQL客户端连接在服务器进程中拥有一个线程,这些线程被服务器集中管理,会被缓存,并不是每个新建连接都会创建或销毁线程。
客户端连接到MySQL服务器时,连接器会对其进行认证,认证基于用户名密码、SSL证书等,连接成功后,服务器会继续验证该客户端是否有对应某个特定查询的权限,即上述的处理连接、认证授权
1.1.2 优化与执行
MySQL首先通过解析器对SQL进行词法、语法的分析,通过词法语法分析后,优化器对SQL进行各种优化,例如重写查询、表的读取顺序、索引的选择等。我们可以通过explain请求优化器解释优化过程的各个参考因素,提供一个参考,便于用户重构和优化SQL。
优化器选择最优的SQL后,执行器会调用存储引擎的相关接口,执行SQL,并返回结果。
当然,如果该SQL在进行分析之前,已经命中缓存的话,便直接返回结果。
1.2 并发控制
多个查询在同一时刻修改数据时,便存在并发控制的问题。
1.2.1 读写锁
当并发读取邮箱数据,不涉及修改时,并发读取是不存在问题的。然而,某个客户正在修改邮件内容时,其他客户此时也在读取邮件,就可能出现数据错乱的问题。
这里的邮件,就是数据库中的一张表,读取和修改邮件对应着查询和更新表的内容。
解决此类问题的方法就是并发控制。处理并发读或写时,可以通过读写锁来解决。读写锁分为读锁和写锁,读锁又被称为共享锁,写锁又被称为排他锁。
读锁是共享的,互不阻塞的。写锁是排他的,写锁会阻塞其他写锁和读锁。实际的数据库系统中,每时每刻都在发生锁定,当某个用户修改某一部分数据时,MySQL会通过锁定防止其他用户读取同一数据。
1.2.2 锁粒度
刚才提到,并发修改邮件内容时,需要“lock整个邮件”来避免并发问题,但是为了提高效率,我们只想锁定被各自修改的某个部分,而不是所有资源。
任何时候,在给定的资源上,锁定的数据量越少,系统的并发程度就越高,只要互相之间不发生冲突即可。
然而加锁也需要消耗资源。锁的各种操作,例如获得锁,检查锁是否已解除,释放锁等,都会增加系统的开销。系统的大部分时间应该是存储数据,而不是对锁的管理上,所以我们需要合适的锁策略,也就是在锁的开销和数据的安全性之间寻求平衡,这种平衡也会影响到系统性能。
MySQL提供多种选择,每种MySQL存储引擎实现了自己的锁策略和锁粒度。将锁粒度固定在某个级别,能够为特定的场景提供更好的性能,然而却不适合其他的场景。
两种最重要的锁策略,
- 表锁 (table lock)
1 | 表锁会锁定整张表,同时只能有一个用户对表进行写操作(插入、删除、更新等)。 |
- 行级锁 (row lock)
行锁可以最大程度支持并发处理(同时也带来了最大的锁开销)。众所周知,在InnoDB和XtraDB,实现了行级锁。行级锁只在存储引擎层实现,并非服务层。
1.3 事务
使用START TRANSACTION语句开启事务,使用COMMIT提交事务将数据持久化,或使用ROLLBACK撤销所有的修改。
ACID是事务的四大特性
- 原子性(atomicity)
- 一致性(consistency)
- 隔离性(isolation)
- 持久性(durability)
一个提供事务,保证ACID的数据库,比不提供事务,不保证ACID的数据库需要更强的CPU处理能力、更大的内存和更多的磁盘。
1.3.1 隔离级别
SQL标准中定义了四种隔离级别,每种隔离级别都规定了一个事务所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的。较低的隔离通常可以执行更高的并发,系统的开销也更低。
四种隔离级别,
- READ UNCOMMITTED(未提交读)
- READ COMMITTED(提交读)
- REPEATABLE READ(可重复读)
- SERIALIZABLE(串行化)
ANSI SQL隔离级别
隔离级别 | 脏读可能性 | 不可重复读可能性 | 幻读可能性 | 加锁读 |
---|---|---|---|---|
READ UNCOMMITTED | YES | YES | YES | NO |
READ COMMITTED | NO | YES | YES | NO |
REPEATABLE READ | NO | NO | YES | NO |
SERIALIZABLE | NO | NO | NO | YES |
1.3.2 死锁
死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。
当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁。
假设两个事务同时处理StockPrice表,
事务1,
1 | START TRANSCTION; |
事务2,
1 | START TRANSCTION; |
如果碰巧,两个事务都执行了第一条UPDATE语句,更新了一行数据,同时也锁定了该行数据,接着两个事务都尝试去执行第二条UPDATE语句,却发现该行已经被对方锁定,便出现了死锁。
双方都持有各自需要的锁,却又在申请对方的锁,陷入死循环。除非外部因素介入,才能解除死锁。
因此,数据库系统实现了各种死锁检测和死锁超时机制。
InnoDB目前处理死锁的方式,便是将持有最少行级排他锁的事务进行回滚(相对简单的死锁回滚算法)。
死锁发生后,只有部分或完全回滚其中一个事务,才能打破死锁。
1.3.3 事务日志
事务日志帮助提高事务的效率。借助事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝,再把该修改行为记录持久化到硬盘的事务日志中,不用每次都将修改数据的本身持久化到硬盘。
事务日志采用追加的方式,因此写日志操作是磁盘一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多。事务日志持久化之后,内存中被修改得数据在后台可以慢慢得刷回磁盘。这种预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘。
如果数据的修改已经记录到事务日志并持久化,但数据本身还没有写回磁盘,此时系统崩溃,存储引擎在重启时能够根据事务日志自动恢复这部分丢失的数据。
1.3.4 MySQL中的事务
MySQL提供两种事务性的存储引擎,
- InnoDB
- NDB Cluster
以及第三方的存储引擎,XtraDB,PBXT等。
自动提交
MySQL默认采用自动提交模式。如果不是显式地开启一个事务,每个查询都被当作一个事务执行提交操作。
当前连接中,可以通过设置AUTOCOMMIT变量来启用或禁用自动提交模式,
AUTOCOMMIT对非事务型表,如MyISAM或内存表是不起作用的
另外,还有一些命令,在执行之前会强制执行COMMIT提交当前的活动事务。例如,
- DDL中,导致大量数据改变的操作,ALTER TABLE等
- LOCK TABLES
🐖,请检查对应版本的官方文件确认所有可能导致自动提交的语句列表。
隐式和显示锁定
InnnoDB 采用的是两阶段锁定协议(什么是i两阶段锁定协议)。
事务执行过程中,随时都可以执行锁定,锁只有在执行COMMIT或ROLLBACK时才会释放,并且所有的锁都在同一时刻被释放。上述描述的隐式锁定,InnoDB根据隔离级别在需要的时候自动加锁。
1.4 多版本并发控制
MySQL大多数事务型存储引擎都不是简单的行级锁,而是多版本并发控制(MVCC)来降低开销,提升效率。
MVCC实现了非阻塞的读操作,和锁定必要的行的写操作。
MVCC的实现,是通过保存数据在某个时间点的快照来实现的。特点,
- 无论事务执行多长时间,每个事务看到的数据是一致的
- 事务开始的时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的
不同存储引擎的MVCC实现是不同的,典型的有乐观并发控制和悲观并发控制。
让我们来看看,InnoDB的简化版MVCC是如何工作的。
1.5 MySQL的存储引擎
在文件系统中,MySQl将每个数据库保存为数据目录下的一个子目录。创建表时,MySQL会在数据库子目录下创建一个和表同名的**.frm**文件保存表的定义。
MySQL使用文件系统的目录和文件来保存数据库和表的定义,大小写敏感和具体的平台密切相关。Windows中,大小写不敏感,类Unix中则是敏感的。不同的存储引擎保存数据和索引的方式不同,但表的定义在MySQL服务层统一处理。
1 | mysql> USE mmall |
1.5.1 InnoDB存储引擎
InnoDB是MySQL的默认事务性引擎,被设计用来处理大量的短期事务,短期事务大部分情况是正常提交的,很少被回滚。
InnoDB采用MVCC来支持高并发,并且实现了四个标准的隔离级别。默认隔离级别是REPEATABLE READ,并且通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行,还会对索引中的间隙进行锁定,以防止幻影行的插入。
InnoDB表是基于聚簇索引建立的,聚簇索引对主键查询有着很高的性能。不过二级索引中是包含主键的,如果主键列很大的话,其他的所有索引都会很大。因此表表上的索引较多的话,主键应当尽可能的小。
1.5.2 MyISAM存储引擎
MySQL5.1及之前的版本,MyISAM是默认的存储引擎,支持全文索引、压缩和空间函数(GIS)等,但是不支持事务和行级锁,而且在崩溃后无法安全恢复。然而,尽管上述的一些缺陷,MyISAM并非一无是处。对于只读的数据,或者表比较小,可以忍受修改操作,那么可以选择该引擎。
存储
MyISAM会将表存储到两个文件中:数据文件和索引文件,分别以.MYD和.MYI为扩展名。
MyISAM表可以存储行的记录数,一般受限于可用的磁盘空间,或操作系统中单个文件的最大尺寸。
MyISAM压缩表
如果表在创建并导入数据以后,不会再进行修改操作,那么这样的表或许采用MyISAM压缩表。
可以使用myisampack
对MyISAM表进行压缩。压缩表是不能修改的,除非先解压,修改数据,再压缩。压缩表能极大减少磁盘空间占用,因此可减少磁盘I/O,从而提升查询性能。压缩表也支持索引,但索引也是只读的。
以现在的硬件能力,对于大多数场景,读取压缩表数据时的解压带来的开销影响并不大,而减少磁盘I/O带来的好处则要大得多。压缩时,表中得记录是独立压缩的,所以读取单行的时候不需要去解压缩整个表。
1.5.3 Memory
如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用Memory是非常有用的。
Memory比MyISAM表要快一个数量级,因为数据都保存在内存中,不需要进行磁盘I/O。
Mymory表支持Hash索引,查询操作非常快。Memory表是表级锁,并发写入性能较低。且不支持BLOB或TEXT类型的列,并且每行的长度是固定的,即使指定了VARCHAR列,实际存储时也会转换为CHAR。
MySQL在执行查询过程中需要使用临时表来保存中间结果,内部使用的临时表就是Memory表,但中间结果太大超出了Memory表的限制,或含有BLOB或TEXT字段,临时表便会转换为MyISAM表。
Memory表使用的场景,
- 用于查找或映射表,如将邮编和地名映射的表(字典)
- 用于缓存周期性聚合数据的结果
- 用于保存数据分析中产生的中间数据
1.5.5 选择合适的存储引擎
- 事务
- 备份
- 崩溃恢复
- 特有的特性
日志型应用
这一类应用对插入速度有很高的要求,数据库不能成为瓶颈,MyISAM或Archive存储引擎比较合适,开销低,插入速度非常快
订单处理
涉及订单处理,需要保证事务,选择InnoDB引擎
…