高性能MySQL（一）

MySQL架构与历史

逻辑架构

1. 客户端并发连接服务层
2. 服务层内部对连接/线程处理，连接到解析器或查询缓存
3. 解析器连接优化器
4. 最后连接存储引擎

连接管理与安全性

每个连接的查询都在单独的线程中，该线程由线程池维护；客户端连接认证基于用户名/密码/主机信息，也可使用SSL

优化与执行

解析查询时，创建内部数据结构（解析树），重写查询、决定表读取顺序、选择合适索引

并发控制

两个层面，服务器层和存储引擎层。

读写锁

共享锁（读锁）和排他锁（写锁），读锁相互不阻塞，写锁阻塞其它读锁和写锁

锁粒度

提高资源并发性，让锁定的对象更具选择性，只锁定需要修改的部分，锁定的数据越少，并发程度越高，但锁消耗资源（获得锁、检查锁、释放锁）

锁策略，在锁的开销和数据安全性之间寻求平衡，一般使用行级锁

表锁table lock

MySQL最基本的锁策略，开销最小的策略

行级锁row lock

最大程度支持并发处理，同时也有最大的锁开销，InnoDB/XtraDB都实现了行级锁，并且行级锁只在存储引擎层实现

事务

除非通过严格的ACID测试，否则空谈事务的概念是不够的

原子性，一个事务的整个操作，要么全部提交成功，要么全部失败回滚，不能执行其中的某一部分

一致性，数据库总是从一个一致性的状态转换到另外一个一致性的状态

隔离性，一个事物所作的修改在提交前，其他事务是看不到的

持久性，一旦事务提交，则其所做的修改就会永久保存到数据库中

隔离级别

SQL标准制定了四种隔离级别，规定事务的修改对其它事务是否可见

READ UNCOMMITED 未提交读，未提交也可见，又称脏读

READ COMMITED 提交读，只有提交才可见，大多数DBMS默认隔离级别都是这个，MySQL不是，也称不可重复读

REPEATABLE READ 可重复读，多次重复读取结果一致，MySQL默认这个级别，解决脏读问题，但存在幻读问题（某个事务读取记录时，另一事务插入了新纪录，原事务再读取记录时产生幻行），InnoDB/XtraDB通过多版本并发控制MVCC解决幻读问题

SERIALIZABLE 可串行化，最高隔离级别，强制事务串行执行，完全没有并发性能

死锁

两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而恶性循环

DBMS一般通过死锁检测/死锁超时等机制处理，InnoDB通过将持有最少行级排他锁的事务进行回滚来解决死锁

事务日志

事务日志先写日志再异步刷入磁盘，即使系统奔溃，存储引擎重启后也能自动回复修改的数据

MySQL中的事务

两种事务型存储引擎，InnoDB和NDB Cluster，另外第三方XtraDB和PBXT也支持

自动提交

MySQL默认采用自动提交模式，SET AUTOCOMMIT = 1;，1表示启用，0表示禁用，修改该值对非事务型的表没有任何影响

MySQL设置隔离级别，SET TRANSACTION ISOLATION LEVEL READ COMMITED;，MySQL识别所有四个级别，InnoDB支持所有隔离级别

事务中混合使用引擎

事务由下层存储引擎实现，在同一事务中使用不同引擎室不可靠的，尤其是在需要回滚时，非事务型表的变更无法撤销

隐式和显式锁定

InnoDB使用两阶段锁定协议，即在事务执行过程中，随时可以锁定，但只有在执行COMMIT或ROLLBACK时同时释放所有锁，锁定都是隐式锁定，InnoDB根据隔离级别自动加锁

InnoDB也支持显式锁定，SELECT ... LOCK IN SHARE MODE或SELECT ... FOR UPDATE，这些语句不属于SQL规范

MySQL也支持LOCK TABLES和UNLOCK TABLES语句，不过这两句是在服务器层实现的，与存储引擎无关，不能替代事务

多版本并发控制MVCC

不同存储引擎实现的方式不同，典型的有乐观并发控制和悲观并发控制

InnoDB在每行记录后增加两个隐藏列来实现，一个保存创建版本，一个保存删除版本，值会递增

1. 查询：只查找版本早于（小于等于）当前事务版本的数据记录，行的删除版本要么未定义，要么大于当前事务版本
2. 插入：为新插入的行保存当前版本
3. 删除：为删除的行保存当前版本
4. 更新：插入新纪录，保存当前版本，并将当前版本保存到原来的行作为删除版本

MySQL的存储引擎

MySQL将每个数据库（schema）保存为目录下的一个子目录，创建表时，在该子目录下创建与表同名的.frm文件来保存表的定义，大小写敏感性与平台相关

通过SHOW TABLE STATUS LIKE [name]显示表相关信息

InnoDB

默认的事务型引擎，最重要最广泛，用来处理大量短期事务

历史

早期InnoDB plugin是Oracle 创建的下一代InnoDB引擎，MySQL默认集成了旧的InnoDB引擎，后来Oracle收购了Sun公司，从MySQL开始用InnoDB plugin完全替代旧版本InnoDB

这个plugin支持一些新特性，利用排序创建索引、删除或增加索引不需要复制全表数据、新的支持压缩的存储格式、新的大型列值如BLOB的存储方式、文件格式管理

概览

InnoDB数据存储在表空间，表空间由InnoDB管理，由一系列的数据文件组成，采用MVCC来支持高并发，实现四个隔离标准级别，默认是可重复读，通过间隙锁策略防止幻读的产生

表基于聚簇索引建立，其索引结构有很大不同，对主键查询有很高性能，但是其二级索引必须包含主键列，所以若索引较多，主键应尽可能小

InnoDB内部做了很多优化，从磁盘读取数据时采用的可预测性读、自动在内存中创建hash索引以加速读操作的自适应哈希索引、加速插入操作的插入缓冲区

支持热备份，MySQL其他引擎不支持热备份

MyISAM

在5.1以前是默认存储引擎，提供大量诸如全文索引、压缩、空间函数等特性，但不支持事务和行级锁，并且崩溃后无法安全恢复

存储

将表存储在两个文件中，一个索引文件，一个数据文件，分别是.MYI和.MYD，MyISAM表可以包含动态或者静态行，可存储记录数受限于可用的磁盘空间或操作系统单个文件的最大尺寸

特性

1. 加锁与并发：对整张表加锁，在读的同时也可以写入，又称并发插入
2. 修复：可以手工或自动执行检查和修复操作，与崩溃恢复是两个概念，修复操作很慢，可能丢失数据，通过CHECK TABLE [name]检查错误，通过REPAIR TABLE [name]修复
3. 索引特性：即使是BLOB和TEXT等长字段，也可基于其前500个字符创建索引
4. 延迟更新索引健：若指定了DELAY_KEY_WRITE选项，每次修改执行完成后不会立刻写入磁盘，而是写入内存的键缓冲区，崩溃时会损坏索引，需要修复

压缩表

如果表在创建并导入数据后不会再修改，适合使用压缩表，极大减少磁盘占用，减少磁盘IO，支持索引，压缩时记录独立压缩，读取时不需解压整个表和记录所在的页

性能

最典型的性能问题还是表锁，若所有查询长期处于locked状态，表锁罪魁祸首

内建的其它引擎

Archive

只支持INSERT和SELECT操作，在5.1以前不支持索引，缓存所有的写并利用zlib对插入的行进行压缩，比MyISAM磁盘IO更少，但每次SELECT都需要全表扫描，所以适合日志和数据采集类应用

支持行级锁和专用缓冲区，可实现高并发插入，不是事务型引擎，是一个针对高速插入和压缩做了优化的简单引擎

Blackhole

没有任何存储机制，丢弃所有插入的日志，服务器会记录Blackhole表的日志，可用于复制数据到备库或简单记录到日志，问题多

CSV

将普通的CSV文件作为MySQL表来处理，不支持索引，可以在运行时拷入或拷出，可用于数据交换

Federated

访问其他MySQL服务器的一个代理，会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据，默认是禁用的

Memory

所有数据保存在内存中，不需要磁盘IO，结构在重启后会保留，但数据会丢失，Memory表是表级锁，并发写入性能低

不是临时表，但可以作为临时表的引擎

Merge

MyISAM的一个变种，由多个MyISAM表合并而来的虚拟表，基本不用了

NDB Cluster

03年从索尼爱立信收购了NDB数据库，开发了集群存储引擎，作为SQL和NDB原生协议之间的接口

第三方存储引擎

OLTP类

Percona的XtraDB是基于InnoDB的一个改进版，改进集中在性能、可测量性和操作灵活性方面

PBXT，支持引擎级别复制、外键约束，对SSD提供适当支持，对较大值类型如BLOB做了优化，是一款社区支持的引擎

TokuDB使用分形树索引数据结构，与缓存无关，超过内存大小也不影响性能，是一种大数据存储引擎，拥有很高的压缩比，可在大数据量上创建大量索引

面向列的存储引擎

MySQL默认面向行，每行数据一起存储，查询也是以行为单位处理，在大数据量处理时，面向列方式可能效率更高

Infobright是最有名的面向列的存储引擎，在数十TB数据量时，工作良好，是为数据分析和数据仓库应用设计的，数据高度压缩，不支持索引，需要对MySQL服务器做定制

InfiniDB，在一组机器集群间做分布式查询

社区存储引擎

有很多，了解一下即可，不必深究

aria，原maria，MySQL创建者计划替代MyISAM的引擎，解决了崩溃恢复问题以及数据的缓存

选择合适的引擎

大部分情况下，InnoDB都是正确的选择，如果要用全文索引，使用InnoDB加上Sphinx的组合而不是MyISAM

1. 事务
2. 备份
3. 崩溃恢复
4. 特有的特性

转换表的引擎

1. ALTER TABLE [name] ENGINE = InnoDB，需要执行很长时间，按行将数据复制到新表中，会消耗掉所有IO能力，同时原表加上读锁，转换表引擎会失去所有原引擎的特性
2. 导入导出，修改文件中的CREATE TABLE语句的存储引擎，同时修改表名，否则会删表
3. 创建与查询，综合1、2特点，先创建一个新的存储引擎的表，然后分批处理数据段（INSERT SELECT）

时间线

了解MySQL版本

3.23(2001)，真正诞生的时刻，开始广泛使用

4.0(2003)，支持新的语法，UNION和多表DELETE，重写了复制，在备库使用两个线程实现复制，InnoDB成为标准配备，包括行级锁、外键等

4.1(2005)，引入更多语法，子查询、INSERT ON DUPLICATE KEY UPDATE，开始支持utf-8字符集

5.0(2006)，出现企业级特性，视图、触发器、存储过程、存储函数，老ISAM引擎代码彻底移除

5.1(2008)，Sun收购后首个发布版本，研发时间长达5年，引入分区、基于行的复制，移除BerkeyDB（最早的事务型引擎）

5.5(2010)，Oracle收购Sun后首个版本，改善性能、扩展性、复制、分区、对Windows的支持，InnoDB成为默认引擎，移除不建议使用的特性

开发模式

遵循GPL开源协议，全部源代码都开放给社区，只有服务器插件是不开源的

转载于:https://www.cnblogs.com/ikct2017/p/9529822.html