1.1 MySQL逻辑架构
- 最上层是连接和线程管理. 包含连接处理，授权认证，安全等等
- 第二层是服务层。 核心功能都主要集中在这一层，包括查询缓存，分析，优化，缓存，以及内置函数，跨存储引擎的功能都在这一层实现:存储过程，触发器，视图等等.
- 第三层是存储引擎。负责MySQL中数据的存储与提取。 存储引擎做了封装，对外提供了几十个底层函数,用于执行开始一个事务或者根据逐渐提取记录等操作
1.1.1 连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，而且只能使用单个CPU核心。服务器会负责缓存线程，因而不用每次都去创建或者销毁线程。当连接进来时，会做安全校验，用户名密码，特定操作的权限等
1.1.2 优化与执行
MySQL会解析查询，并创建内部数据结构(解析树),然后对其进行各种优化，包括重写查询，决定表的读取顺序，索引的选择等。
对于SELECT语句，解析查询时，会先检查缓存(Query Cache),如果命中则不必执行查询解析，优化，执行等过程。
1.2 并发控制
1.2.1 读写锁
- 共享锁
也叫读锁，多个客户在同一时刻可以同时读取同一个资源且互不干扰。
读锁的应用场景在于当数据查询是为了做更新或者别的操作，期间不希望有别人修改数据时使用读锁，该锁默认了出现并发的可能性比较小或者让别的连接能查询但是无法做更新。保证的是自身线程更新的数据是和读时是一致的。
抽象意义上也叫乐观锁，基本的实现方式是version 或者timestamp来做控制，当进行更新时要确认version还是原来读到的那个，否则更新失败。 - 排他锁
也叫写锁，当加了该锁以后别的线程无法去读数据，直到该线程提交了。
抽象意义上也叫悲观锁，使用该锁后可以防止别的线程获取到更新前的数据，但是会更容易造成程序崩溃。
写锁比读锁有更高的优先级，写锁请求可能会被插到读锁的前面.
1.2.2 锁粒度
一种提高共享资源并发性的方式就是放锁定对象更有选择性。尽量值锁定需要修改的部分数据，而不是所有的资源。像Innodb就可以使用行级锁，锁的粒度越大，就越容易发生冲突。
所谓锁策略，其实就是在锁的开销和数据的安全性之间寻求平衡。加锁本身也需要消耗资源，粒度越细，锁就越难管理
- 表锁
表锁是最基本的锁策略，并且开销最小。 - 行级锁
行级锁可以最大成都的支持并发处理，同时也带来了最大的锁开销。 InnoDB中行级锁实在存储引擎层实现的.
1.3 事务
所谓事务就是一组原子性的SQL查询，要么都成功，要么都失败。
- 原子性(atomicity)
一个事务必须必须被视为一个不可分割的最下工作单元。 - 一致性(consistency)
数据库总是从一个一致性状态转到另一个一致性状态中去。 - 隔离性(isolation)
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。 - 持久性(durability)
一旦事务提交，则所做的修改就会永久的保存到数据库中。
1.3.1 隔离级别
- Read Uncommitted(未提交读)
也就是别的线程可以读到当前线程执行了但是未提交了的数据，也称之为脏读(dirty read)。
从性能上来说好不了太多，但是缺乏别的太多好处。 - Read Committed(提交读)
大多数数据库默认的隔离级别都是Read Committed(MySQL不是)。
可以查询到事务期间别人已经提交了的数据.这个级别也叫作不可重复读(nonrepeatable read)，因为两次执行同样的查询，可能会得到不一样的结果 - Repeatable read(可重复读)
这个隔离级别解决了脏读的问题。保证了在同一个事务中多次查询同样记录得到的数据是一直的。但是无法解决幻读的问题。
所谓幻读，就是某个事务在读取某个返回内的记录时，另外一个事务在这个范围里插入了新的数据，原来的事务再次读取就会出现幻行。
InnoDB通过多版本并发控制(MVCC，multiversion Concurrency Control) 解决了幻读的问题.
可重复读是Mysql默认事务隔离机制 - Serializable(可串行化)
Serializable 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题. 简单来说就是 ，Serializable会在读取的每一行都加锁。
1.3.2 死锁
死锁是指两个或者多个事务在统一资源上互相占用，并请求对方锁定的资源。 从而导致恶性循环。
Innodb目前处理死锁的方法是: 将持有最少行级排它锁的事务进行回滚。
1.3.3 事务日志
事务日志可以帮助提高事务的效率。 使用事务日志，存储引擎在修改表的诗句只需要修改其内存拷贝，再把该修改行为记录到持久化到硬盘上的事务日志中，而不用每次都将修改的数据本身持久化到磁盘。 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快的多。 事务日志持久化后可以在后台慢慢的将数据刷到磁盘。目前大多数存储引擎都是如此，我们称之为预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘。
如果数据的修改已经记录到事务日志并且持久化了，但数据本身还没写回磁盘，此时系统崩溃，存储引擎能够在重启后自动恢复这部分的数据。
1.3.4 MySQL中的事务
MySQL提供了，两种事务型的存储引擎: InnoDB 和NDB Cluster。
-
自动提交(Autocommit)
MySQL默认采用自动提交模式。可以通过设置autocommit变量来启用或者禁用自动提交模式，show variables like 'autocommit'可以查看现在的提交状态。 -
设置隔离级别
mysql可以通过set session transaction isolation level read committed来设置当前session事务的隔离级别。
永久设置
[mysqld]
transaction-isolation = REPEATABLE-READ
transaction-read-only = OFF
支持的隔离级别如下
level: {
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}
-
在事务中混合使用存储引擎
如果在事务中混合使用了事务型和非事务型的表(如InnoDB和MyISAM表),在正常提交是不会有问题的。但是如果事务需要回滚，非事务型表上的变更会无法撤销，会导致数据处于不一致的状态。应尽量避免在事务中使用混合的表 -
隐式和显式锁定
InnoDB采用的是两阶段锁定协议(two-phase locking protocol)。在事务执行过程中，随时都可以执行锁定，只有在提交或者回退时才会释放，并且是同一时刻一起释放。这些都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。
另外也可以通过特定的语句进行显式锁定。
select .. lock in share modeselect ... for update
1.4 多版本并发控制
MVCC可以认为是行级锁的一个变种，但是在很多情况下避免了加锁操作，因此开销更低。
不同的存储引擎的MVCC实现是不一样的。典型的有乐观并发控制和悲观并发控制。
InnoDB中的实现
在innoDB中，是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的创建时间，一个保存了行的过期时间。这里存储的是系统版本号。
每开始一个事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号比较。
下面是一个在repeatable read隔离级别下的MVCC的具体操作。
- select
InnoDB会根据以下两个条件检查每行记录- InnoDB只查找版本号早于当前事务版本的数据行(<=事务的版本号),这样可以保证事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.
- 行的删除版本号要么未定义，要么大于当前事务版本号。这鸭子那个可以确保事务读取到的行，在事务开始之前未被删除。
- insert
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。 - delete
InnoDB为删除的每一行保存当前系统版本号作为行版本号 - update
InnoDB插入一行新纪录，保存当前版本号作为行版本号，同时保存当前系统版本号作为原来的行作为行删除标识。
保存这两个版本号，是的大多数读操作都不用加锁。这样设计是的读数据操作很简单，性能很好，不足的是每行记录都需要额外的存储空间，需要做更多的检查和维护工作。
MVCC只在repeatable read 和read committed 两个隔离级别下工作。
1.5 MySQL的存储引擎
在文件系统中，MySQL将每个数据库保存为数据目录下的一个子目录。创建表时MySQL会在子目录下创建一个和表同名的.frm文件保存表的定义。
可以使用show table status或者查看infomation_schema中对应的表显示表的相关信息。
1.5.1 InnoDB存储引擎
InnoDB采用MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是repeatable read，并且通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB表是基于聚簇索引建立的聚簇索引对主键查询有很高的性能。 不过他的二级索引中必须包含主键列，如果主键列很大的话会导致所有的索引都会变大。
InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index),以及能够加速插入操作的插入缓冲区(insert buffer)
1.5.2 MyISAM存储引擎
提供了大量的特性，如全文索引，亚索，空间函数(GIS)等。
MyISAM不支持事务和行级锁，另外就崩溃后无法安全恢复。尽管如此，对于只读的数据，或者表比较小，可以忍受修复操作，则仍然可以使用
- 存储
会将表存储在两个文件中，数据文件和索引文件，分别以.MYD和.MYI为拓展名。能存储的记录数受限于磁盘的可用空间，或者操作系统中单个文件的最大尺寸。 - 特性
- 加锁与并发
会对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时会加排它锁。但是在读取查询的同时，也可以往表中插入新的记录，也称为并发插入(concurrent insert) - 修复
修复可能会导致部分数据丢失。可以通过check table mytable来检查错误，通过repair table mytable进行修复。另外如果MySQL服务器已经关闭，可以通过myisamchk命令行工具进行检查和修复工作。 - 索引特性
即使是BLOB和TEXT等长字段，也可以基于其前500个字符创建索引。也支持全文索引，一种给予分词创建的索引，可以支持复杂的查询。 - 延迟更新索引键(Delayed Key Write)
如果创建表时，指定了DELAY_KEY_WRITE选项，则在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区(in-memory key buffer),只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大的提升写性能，但是在主机崩溃时需要执行修复操作。 - MyISAM压缩表
如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。
可以使用myisampack对表进行压缩。压缩表是无法进行修改的，除非先解压缩。压缩表可以减少磁盘I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。
压缩时表中的记录是独立压缩的，因此读取单行的时候不需要去解压整个表，甚至也不用解压行所在的整个页面。
- 加锁与并发
- 性能
MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。但是有一些服务器级别的性能拓展限制，比如对索引键缓冲区(key cache)的mutex锁，MariaDB基于段的索引键缓冲区机制来避免该问题。
但最典型的性能问题还是表锁的问题。
1.5.3 内建的其他存储引擎
- Memory引擎
如果需要快速的访问数据，并且这些数据不会被修改，重启后丢失也没关系，那么就用memory表。能比MyISAM快上一个数量级，因为所有的数据都保存在内存中。
应用场景:
- 用于查找或者映射表，如将邮编和州名映射的表
- 用于缓存周期性聚合数据的结果
- 用于保存数据分析中产生的中间数据
它是表级锁，因此并发写入的性能比较低。不支持blob或者text类型的列，并且每行的长度是固定的，所以即使制定了varchar列，实际上也会转为char，导致了部分内存的浪费。
MySQL在执行查询过程中需要使用临时表来保存中间结果，内部使用的临时表就是memory表。如果中间结果太大，超出了momery表的限制，或者含有blob或text字段，则临时表会转换为MyISAM表。
临时表是指使用create temporary table语句创建的表，它可以使用任何存储引擎。
1.5.5 选择引擎
大部分情况下InnoDB都是正确的选择,一句话就是，除非需要使用到InnoDB不具备的特性，而且没有其他办法代替，否则都应该优先选择InnoDB。
例如，如果要用到全文索引，优先考虑InnoDB加上Sphinx的组合，而不是直接用MyISAM。
除非万不得已，否则尽量避免混合使用多种存储索引。
选择的标准:
- 事务
- 备份(InnoDB支持热备份)
- 崩溃恢复
- 特有的特性
日志型应用
如果要将日志记录到MySQL，对数据的插入速度有很高的要求。可以尝试使用MyISAM或者Archive存储引擎,因为开销低，插入非常快。
但是在对记录的数据做分析报表时，SQL可能会导致插入效率变低。
一种解决方案是复制数据到备库，然后在备库上做耗时sql等查询。
另一种方案是在日志记录表上加入年月信息，水平分表。
只读或者大部分情况下只读的表
如果不介意崩溃恢复的问题的话，可以选用MyISAM。
不要轻易相信MyISAM比InnoDB快之类的经验之谈，现如今来说其实InnoDB的性能已经差不多了，尤其是可以使用聚簇索引，或者需要访问的数据都能放入内存时，速度让MyISAM望尘莫及。所以建议还是使用InnoDB，MyISAM在随着应用压力上升容易导致锁竞争崩溃等问题会陆续出现。
订单处理
如果涉及订单处理，那么支持事务就是必要选项。半完成的订单是无法用来吸引用户的。
大数据量
当数据量过大时，就需要建立数据仓库了，可以使用infobright(数据量千万时查询性能仍然很高，但是不能支持高并发)。
此外现在也有很多诸如elasticsearch,clickhouse,hbase等产品，更适合做这种大数据下的处理。
1.5.6 转换表的引擎
alter table
alter table可以修改表的引擎
alter table mytable engine=InnoDB;
上述语法可以改变存储引擎，但是会执行很长时间。MySQL会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的I/O能力，同事会在原表上加读锁。所以在繁忙的表上执行这个操作时要特别小心。
如果转换表的存储引擎，将会失去和原引擎相关的所有特性。例如如果将一张表从InnoDB转换为MyISAM在转回InnoDB，原InnoDB表上的外键将会丢失。
导入与导出
为了更好的控制转换的过程，可以使用mysqldump工具将数据导出到文件，然后修改文件中create table中的存储引擎选项，同时修改表名(mysqldump默认会自动在create table 前加上drop table语句)。
创建与查询
使用insert into ...select语法来导数据:
create table innodb_table like myisam_table;
alter table innodb_table engine=InnoDB;
insert into innodb_table select * from myisam_table
数据量不大时这样做工作的很好，如果数据量很大，可以考虑做分批处理，针对每一段数据执行事务提交操作。
假设有主键id,则可以重复执行以下语句:
start transaction;
insert into innodb_table select * from myisam_table
where id between min_id and max_id;
commit;
如果有必要，可以在执行的过程中对原表加锁，以确保新表和原表的数据一致。
Percona Toolkit 提供了online-schema-change的工具，可以比较简单的执行上述过程，避免手工误操作。
1.6 总结
本章主要讲了乐观锁和悲观锁，事务的ACID，事务的几种隔离级别，InnoDB用MVCC(多版本并发控制)实现了行级锁，用间隙锁(next-key lock)来防止幻读的出现。
网友评论