高性能的Mysql-mysql的架构和历史

MySQL

Mysql架构和历史

简述

mysql可以在不同场景中应用并发挥好的作用，他并不完美，却足够灵活，能适应高要求的环境。MySQL既可以嵌入到应用中，也可以支持数据仓库，内容索引和部署软件，高可用的冗余系统，在线事务处理系统等各种应用类型。
MySQL的存储引擎架构将查询处理和其他任务系统和数据的存储/提取相分离。

MySQL逻辑架构图

存储引擎

• 存储引擎负责MySQL中数据的存储和提取。
• 服务器通过API与存储引擎通信，屏蔽了不同存储引擎之间的差异，使得差异对上层的查询过程是透明的。
• 存储引擎不会解析Sql，不同的存储引擎之间不会通信，只是简单响应上层的服务请求

连接管理与安全性

• 每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个线程中执行，该线程只能轮流在某个CPU和CPU核心中运行。服务器会负责缓存线程，不需要为每一个新建的链接创建或销毁线程。

优化与执行

MySql 会解析查询，并创建内部数据结构(解析树),然后对其进行各种优化，比如重写查询，表读取顺序，索引选择等。优化器不关心表使用什么存储引擎，她会请求存储引擎提供容量或某个具体操作的开销信息，以及表的统计信息等。对于select语句，在解析查询之前，服务器会先查缓存，如果能找到服务器就不在解析查询，而是直接返回缓存中的结果集。

并发控制

• 在处理并发读或者写的时候，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种锁被称为共享锁和排他锁，也叫读锁和写锁。
• 读锁是共享的，或者说是相互不阻塞的。写锁是排他的，一个写锁会阻塞其他的写锁和读锁。
• 一种提高共享资源并发性的方式就是让锁定的对象更具有选择性，尽量只锁需要修改的部分数据而不是所有资源，甚至只对修改的数据片进行精确的锁定，但是这样可能会更加消耗系统的资源，使得大部分时间用在管理锁上，而不是存储数据。
• 所谓锁策略就是在锁的开销和数据安全性之间寻求平衡。
• 表锁是mysql中最基本的锁策略，并且是开销最小的策略。
• 写锁可以插入到写队列中读锁的前面，反之则不行。
• 行级锁可以最大程度的支持并发处理，而且只在存储引擎中实现，服务器层没有实现。

MySQL事务

事务就是一组原子性的sql查询，或者说是一个独立的工作单元。如果数据库引擎能够成功的对数据库应用该组查询的全部语句，那就全部执行，如果有一条语句执行失败，那么所有语句都不会执行。

• 事务ACID特性是指事务的原子性，一致性，隔离性和持久性。
• 具体参照 MySQL事务
  

  通常来说，一个事务所做的修改再最终提交前，对其他事务是不可见的。
  对于一些不需要事务的查询类应用，选择一个非事务类型的存储引擎，可以获得更高的性能。用户可以根据是否需要事务处理，选择合适的存储引擎。

隔离级别

• 较低级别的隔离通常可以执行更高的并发，系统开销也更低。
  1、READ UNCOMMITTED(未提交读),事务中的修改即使没有提交，对其他事务也都是可见的。事务可以读取到未提交的数据，称为脏读(Ditty Read)，实际用的少
  2、READ COMMITTED(提交读):大多数的数据库默认的隔离级别，一个事务从开始到提交之前，所作的任何事情对其他事务来说是不可见的，也叫不可重复读(nonrepeatble read)，因为执行两次同样的查询，结果可能不一样。
  3、REPEATABLE READ(可重复读), 解决了脏读的问题，该级别保证了在同一事务中多次读取同样的纪录结果是一致的。幻读是指当一个事务B读取某一个范围内的纪录，事务A在该范围内又插入了新的纪录，当B事务再次读取该范围的记录时，产生幻行。InnoDB通过多版本并发控制解决幻读问题。
  可重复读是MySQL的默认隔离级别。
  4、SERIALIZABLE (可串行化) 是最高的隔离级别，会在读取每一行的数据上都加锁，所以导致大量的超时和锁争用的问题。只有在非常需要确保数据的一致性可以接受没有并发的情况下。

隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

Yes: 可能出现 No: 不会出现

死锁

死锁是指两个或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务以不同的顺序锁定资源时就会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。
死锁的产生有双重原因：有些是因为真正的数据冲突，有些完全是因为存储引擎的实现方式导致的。
死锁发生以后只有部分或者全部会滚一个事务，才能打破死锁。

事务日志

存储引擎在修改表数据的时候只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次将修改的数据本身持久到硬盘。
事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域的顺序I/O,不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志相对来说要快很多。
事务日至持久化后，内存中被修改后可以后台慢慢的刷回到磁盘。修改数据需要写两次磁盘。

自动提交(autocommit)

MySQL默认采用自动提交模式。 show variables like 'autocommit' 查看自动提交的状态。
set autocommit = 1; 语句来设置或者禁用自动提交,1代表启用，0代表禁用。

MySQL通过执行SET TRANSACTION IOSLATION LEVEL 来设置隔离级别，新的隔离级别在下一个事务开始时生效。

set session transaction isolation level read committed 设置当前会话的隔离级别，InnoDB支持所有的隔离级别。

事务中混合使用存储引擎

MySQL服务层不管理事务，事务是由下层的存储引擎实现的。
如果在事务中使用了事务型和非事务型的表，正常提交没有问题， 但是如果事务需要会滚，非事务表的变更无法撤销，事务最终结果无法确定。而且在非事务性的标上执行事务操作，MySQL一般不会发出提醒，也不会报错。有时候只有回滚的时候会发出警告。

隐式和显示锁定

1、InnoDB采用的是两阶段锁定协议。在事务执行的过程中，随时都可以执行锁定，只有在执行commit或者rollback后才会释放;
2、InnoDB也支持通过特定的语句显示的锁定，这是在服务器层实现的，和存储引擎无关。
3、建议是除了事务中禁用了autocommit，可以使用lock tables，其他都不要显示的执行lock tables，因为lock tables 和事务之间相互影响的话，情况会变得比较复杂。

多版本并发控制(MVCC)

• MySQL大多数的事务存储引擎实现的都不是简单的行级锁。可以任务MVCC是行级锁的一个变种，但是很多情况下避免了加锁操作，因此开销更低。虽然实现的机制有所不同，但大豆实现了非阻塞的读操作，写操作也只锁定必要的行。
• MVCC的实现，是通过保存数据在某个时间点的快照来实现的。不管需要执行多长时间，每个事务看到的数据是一致的。根据事务的开始时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
• 乐观(optimistic)并发控制和悲观(pessimistic)并发控制;
• InnoDB的MVCC，是通过在每行纪录后面保存两个隐藏的列来实现的。保存的是系统版本号。保存这两个系统版本号，使大多数读操作都不用加锁，使得读操作变得简单，性能很好，而且也能保证只会读到符合标准的行。不足之处是每行纪录都要额外的存储空间，需要做更多的检查工作，以及额外的维护的工作。
• MVCC只在READ COMITTED 和REPEATABLE READ两个隔离级别下工作。

MySQL存储引擎

不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理的。
可以使用 show table status like 'tables'\G sql显示表的相关信息。

InnoDB存储引擎

InnoDB是MySQL的默认事务存储引擎，也是最重要，使用最广泛的存储引擎。它被谁用来处理短期事务，短期事务大部分是正常提交，很少回滚。
InnoDB的性能和自动崩溃恢复，使得他在非事务存储的需求中也很流行。
InnoDB的数据存储在表空间(tablespace)中，表空间是由InnoDB管理的一个黑盒子，由一系列数据文件组成。
InnoDB采用MVCC来支持高并发，并实现了是个标准的隔离级别，默认级别是REPAEATABLE READ,通过间隙锁策略防止幻读的出现。间隙锁不仅仅锁定查询涉及的行，还会对索引中间隙进行锁定，防止幻影行的插入。
InnoDB表是基于聚簇索引建立的。聚簇索引对逐渐查询有很高的性能。
InnoDB内部做了很多优化，包括从磁盘读取数据采用可预测性读a子，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引。
InnoDB通过一些机制和工具支持真正的热备份。

MyISAM存储引擎

在MySQL5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了全文索引，压缩，空间函数等特性，但是不支持事务和行级锁，而且崩溃后无法安全恢复。

MyISAM把表存在两个文件，数据文件和索引文件，以.MYD和.MYI为扩展名。

MySIAM的特性

• 加锁和并发
  MyISAM对整张表加锁，读取时对需要读的表加共享锁，写入时加排他锁。但是在表有读取查询时，可以往表中插入新纪录，并发插入。

• 修复
  对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作。执行表的修复可能导致一些数据的丢失，而且修复比较慢,CHECK TABLE mytable检查表的错误，REPAIR TABLE mytable进行修复。

• 索引特性
  对于MyISAM表，即使是BLOB和TEXT字段，也可以基于其前500个字符创建索引。支持全文索引，基于分词创建的索引。
• 延迟更新索引
  创建MyISAM表，如果制定DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻把修改的索引数据写入磁盘，而是会写入到内存的键缓冲区。只有在清理缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。但数据库崩溃的时候会损坏索引，需要执行修复。

如果表在创建并导入数据后， 不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。使用 myisampack 命令对表进行压缩。压缩表不能进行修改，可以极大减少磁盘空间，减少I/O操作，提升查询性能。压缩表支持只读索引。

• MyISAM引擎设计简单，数据以紧密格式存储，但是有一些服务器级别的性能扩展限制，最大问题性能还是表锁问题。
• 临时表是指用create temporary table 语句创建的表，只在单个连接中可见，连接断开，临时表不复存在。
• MySQL服务器，NDB集群存储引擎，以及分布式的，share-nothing的，容灾的，高可用的NDB数据库的组合，被称为MySQL集群。

其他的MySQL内建的存储引擎

• Archieve引擎：只支持insert和select操作，缓存所有的写并用zlib行压缩,适合日志和数据采集类应用，支持行级锁和专用缓冲区，可实现高并发的插入，在查询过程中回阻塞其他select的执行，实现读的一致性。
• Blackhole引擎:没有实现任何存储机制，丢弃插入数据，不做保存。用于复制数据库到悲苦，or简单的纪录到日志。
• CSV引擎：可把普通的csv文件作为MySQL的表来处理，但是不支持索引。可作为数据交换的机制。
• Federated引擎：是访问其他的MySQL服务器的一个代理，会创建远程到MySQL服务器的客户端链接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。
• Memory引擎：如果需要快速访问数据，并且这些数据不会修改，重启后丢失也无关，那么Memory非常有用。Memory表比MyISAM表快一个数量级，所有数据在内存中，无需磁盘I/O。重启后借口保留，数据丢失。
• Memory引擎表可用于查找映射表，用于缓存周期性的聚合数据结果，用户保存数据分析中产生的中间数据。
• Memory表 支持Hash索引，查找操作快。但是他是表级锁，并发写入的性能较低。不支持BLOB和TEXT的类型数据，每行长度固定。
• Merge引擎 由多个MyISAM表合并而来。
• NDB集群引擎 SQL和NDB原生协议之间的接口。

面向列的存储引擎

MySQL默认是面向行的，每一行的数据一起存储，服务器的查询也是以行为单位处理。而在大数据处理的时候，面向列的方式可能效率更高。如果不需要整行的数据，面向列的方式可以传输更少的数据。

• Infobright 是比较有名的列存储引擎。为数据分析和数据仓库应用设计，数据高度压缩，按块进行排序，每个块都对应有一组元数据。引擎不支持索引。块结构是一种准索引。
• InfiniDB 可以在一组机器集群间做分布式查询，但目前没有生产环境的应用案例。

除非万不得已，否则建议不要混合使用多种存储引擎，否则可能带来一系列复杂的问题，存储引擎层和服务器层交互已经很复杂了，而且混合存储对一致性备份和服务器参数配置都带来了一些困难。

存储引擎的选择

• 事务
  如果需要事务支持，InnoDB是目前最稳定且经过验证的选择。如果只需要select和insert操作，MyISAM是不错的选择
• 备份
  如果需要在线热备份，InnoDB是最基本的要求
• 崩溃恢复
  MyISAM崩溃后发生损坏的概率比InnoDB高很多，恢复速度比较也要慢.
• 特有的特性
  有些应用可能依赖一些存储引擎所独有的特性或优化，MySQL只有MyISAM支持地理空间搜索。一个存储引擎拥有一些关键的特性，同时缺乏一些必要的特性，就不得不折中处理。

日志型的应用用MyISAM存储引擎，开销低，插入速度非常快。MySIAM只将数据写入内存中，然后等待操作系统定期将数据刷出到磁盘上。InnoDB是订单类处理应用的最佳选择。如果要发布一个基于CD-ROM或者DVD-ROM并且使用MySQL数据文件的应用，可以考虑使用MyISAM表或压缩表，这样表之间可以隔离并且可以在不同的介质上相互拷贝。MyISAM压缩表比未压缩的表要节省很多空间，但是压缩表是只读的。

转换表的存储引擎

• alter table

  把表从一个引擎转换为另一个引擎， alter table mytable ENGINE=InnoDB; 如果转换表的存储引擎，将会失去愿引擎相关的所有特性，例如把一张InnoDB表转为MyISAM，然后在转回InnoDB，原InnoDB的外键将丢失.

• 手动的导出导入，

  修改创建表时的存储引擎选项，注意要修改表名，同一个数据库不能存在相同的表名，而且要注意mysqldump会默认加上drop table语句，不注意的话可能会造成数据丢失.

• 创建于查询
  创建一个新的引擎数据表，使用insert .... select语法来导数据,

mysql> crate table innodb_table like myisam_table;
mysql> alter table innodb_table engine=InnoDB;
mysql> insert into innodb_table select * from myisam_table;

数据量不大，还可以，如数据量很大，可考虑做分批处理，针对每一段数据做事务执行提交操作，避免大事务产生过多的undo。执行的过程中可以对原表加锁，确保数据一致。

总结

MySQL拥有分层的结构，上层是服务器层和查询执行引擎，下层则是存储引擎。理解MySQL在存储引擎和服务层之间处理查询时如何用API来回交互，就能抓住MySQL的核心基础架构和精髓。