高性能MySQL 笔记 第一章 MySQL架构与历史
MySQL逻辑架构
最上层的服务并不是MySQL所独有的,大多数基于网络的客户端/服务端的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等
第二层是MySQL的核心服务功能,包括查询解析、分析、优化、缓存以及所有内置函数(日期、时间、数学等等),所有跨存储引擎的功能都在这一层实现:储存过程、触发器、视图等。
第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API与存储引擎进行通信,这些接口屏蔽了不同存储引擎之间的差异。
优化与执行
MySQL会解析查询,并创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询、决定表的读取顺序,以及选择合适的索引等。用户可以影响它的决策过程,也可以请求优化器解释优化过程的各个因素。
存储引擎对于优化查询是有影响的。
对于SELECT语句,在解析查询之前,服务器会先检查查询缓存,如果能够在其中找到对应的查询,服务器就不必再执行查询解析、优化和执行的整个过程,而是直接返回查询缓存中的结果集。
并发控制
读写锁
MySQL通过两种类型的锁组成的锁系统来解决问题:
共享锁(读锁):是共享的,或者说不是阻塞的,多个客户在同一时刻可以同时读取同一个资源,而互不干扰。
排它锁(写锁):是排他的,也就是说一个写锁会阻塞其它写锁和读锁。
锁粒度
理想的锁定方式:只对会修改的数据片进行精确的锁定,而不是所有资源。
大多数商业数据库一般都是在表上施加行级锁。
MySQL则提供了多种选择,每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。
表锁
表锁是MySQL中最基本的锁策略,并且是开销最小的策略。
在特定的场景中,表锁也可能有良好的性能,比如READ LOCAL表锁支持某些类型的并发写操作。
写锁比读锁有更高的优先级。
行级锁
行级锁可以最大限度的支持并发处理,但有最大的所开销。
事务
事务就是一组原子性的SQL查询,或者说一个独立的工作单元。
事务内的语句,要么全部执行成功,要么全部执行失败。
一个运行良好的事务处理系统,必须具备ACID:
原子性(Atomicity):一个事务必须被视为一个不可分割的最小工作单元,整个事务中的 所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中一部分操作。
一致性(Consistency):数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性(Isolation):一个事务所做的修改在最终提交之前,对其他事务是不可见的。
持久性(durability):一旦事务提交,则其所做的修改就会永久的保存到数据库中。
隔离级别
未提交读(READ UNCOMMITTED):在此级别中,事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称为脏读。从性能上讲,该级别并不会比其他的级别好太多,而且会缺乏其他级别的好处。实际应用中一般很少使用
提交读(READ COMMITTED):大多数数据库系统默认的隔离级别都是该级别(MySQL不是)。在该级别中,当一个事务开始时,只能看见已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别 有时候也叫不可重复读。
可重复读(REPEATABLE READ):该级别解决了脏读的问题,保证了在同一个事务中多次读取同样记录的结果是一致的。但该级别无法解决幻读,所谓幻读,就是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行。InnoDB和XtraDB存储引擎通过多版本并发控制解决了幻读的问题。
可串行化(SERIALIZABLE):该级别为最高隔离级别。它通过强制事务串行执行,避免了前面说的幻读问题。简单来说,该级别会在读取的每一行数据上加锁。
死锁
死锁是指两个或者多个 事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。
当多个事务试图以不同的顺序锁定资源时,就会产生死锁。多个事务同时锁定同一个资源时,也会产生死锁。
死锁的产生有双重原因:有些是因为真正的数据冲突,这种情况通常很难避免,但有些则完全是由于存储引擎的实现方式导致的。
死锁发生以后,只有部分或者完全回滚其中一个事务,才能打破死锁。
事务日志
事务日志就是在事务真正对数据库进行操作前,先把操作数据的行为写到事务日志中并储存在内存中,然后再把这些行为记录写到硬盘上的事务日志中,最后通过磁盘上的行为记录对数据库进行操作。而不用每次都将修改的数据本身持久到磁盘。
事务日志可以帮助提高事务的效率。事务日志采用的是追加的方式,因此写日志的操作是磁盘上的一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多。
MySQL中的事务
MySQL提供了两种事务型的存储引擎:InnoDB和NDB Cluster。
自动提交
MySQL默认采用自动提交模式。就是如果不是显式地开始一个事务,则每个查询都被当作一个事务执行提交操作。
可以通过设置AUTOCOMMIT变量来启动或者禁用自动提交模式。
通过执行SET TRANSACTION ISOLATION LEVEL来设置隔离级别。
在事务中混合使用存储引擎
在同一个事务中,使用多种存储引擎是不可靠的。
隐式和显式锁定
InnoDB采用的是两阶段锁定协议,会根据隔离级别在需要的时候自动加锁,锁只有在执行COMMIT或者ROLLBACK的时候才会释放,并且所有的锁是在同一时刻被释放,这是隐式锁定。
InnoDB支持通过特定的语句进行显式锁定:
-
SELECT … LOCK IN SHARE MODE
-
SELECT … FOR UPDATE
可以通过LOCK TABLES和UNLOCK TABLES语句加锁和解锁表,这是在服务器层实现的,和储存引擎无关,并不能代替事务处理。如果需要用到事务,还是应该选择事务型存储引擎。
多版本并发控制
多版本并发控制(MVCC)是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低。
MVCC是通过保存数据在某个时间点的快照来实现的。也就是说,不管执行多长时间,每个事物看到的数据都是一致的。根据事务开始的时间不同,每个事务对同一张表、同一时刻看到的数据可能是不一样的。
MVCC典型分为乐观并发控制和悲观并发控制。
InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列,一个保存了行的创建时间,一个保存行的过期时间(或删除时间)。存储的并不是真实的时间,而是系统版本号。
MVCC只在RPEATABLE READ和READ COMMITTED两个隔离级别下工作。
选择合适的引擎
大部分情况下,InnoDB都是正确的选择。
除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎。如果不需要用到InnoDB的特性,同时其他引擎的特性能够更好地满足需求,也可以考虑其他引擎。
除非万不得已,否则建议不要混合使用多种存储引擎,否则可能带来一系列复杂的问题,以及一些潜在的bug和边界问题。
转换表的引擎
ALTER TABLE
最简单的方法是使用ALTER TABLE语句。但需要很长时间的执行时间。
ALTER TABLE mytable ENGINE = InnoDB;
如果转换表的存储引擎,将会失去和原引擎相关的所有特性。
导出与导入
可以使用mysqldump工具将数据导出到文件,然后修改文件中的CREATE TABLE语句的存储引擎选项,注意同时修改表名。还应注意mysqldump默认会自动在CREATE TABLE语句前加上DROP TABLE语句,不注意这一点可能会导致数据丢失。
创建与查询
先创建一个新的存储引擎的表,然后利用INSERT…SELECT语法来导数据:
CREATE TABLE innodb_table LIKE myisam_table;
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;
数据量不大,该方法很好;数据量很大,可以考虑做分批处理,针对每一段数据执行事务提交操作,以避免大事务产生过多的 undo。
START TRANSACTION;
INSERT INTO innodb_table SELECT * FROM myisam_table
WHERE id BETWEEN x AND y;
COMMIT;
;数据量很大,可以考虑做分批处理,针对每一段数据执行事务提交操作,以避免大事务产生过多的 undo。
START TRANSACTION;
INSERT INTO innodb_table SELECT * FROM myisam_table
WHERE id BETWEEN x AND y;
COMMIT;
操作完成后,新表是原表的一个全量复制。