《MySQL 性能调优与架构设计》

MySQL的基本架构：SQL Layer和Storage Engine Layer

 

SQL Layer模块：初始化模块，核心API，网络交互模块，Client&Servere 交互协议模块，用户模块，访问控制模块，连接管理、连接线程和线程管理，Query解析和转发模块，Query Cache模块，Query优化器模块，表变更管理模块，表维护模块，系统状态管理模块，表管理器，日志记录模块，复制模块，存储引擎接口模块

 

 

针对上面的这些特点和分析，我们可以对OLTP 的得出一个大致的方向。

虽然系统总体数据量较大，但是系统活跃数据在数据总量中所占的比例不大，那么我们可以通过扩

大内存容量来尽可能多的将活跃数据cache 到内存中；

虽然IO 访问非常频繁，但是每次访问的数据量较少且很离散，那么我们对磁盘存储的要求是IOPS 表

现要很好，吞吐量是次要因素；

并发量很高，CPU 每秒所要处理的请求自然也就很多，所以CPU 处理能力需要比较强劲；

虽然与客户端的每次交互的数据量并不是特别大，但是网络交互非常频繁，所以主机与客户端交互

的网络设备对流量能力也要求不能太弱。

基于OLAP 系统的各种特点和相应的分析，针对OLAP 系统硬件优化的大致策略如下：

数据量非常大，所以磁盘存储系统的单位容量需要尽量大一些；

单次访问数据量较大，而且访问数据比较集中，那么对IO 系统的性能要求是需要有尽可能大的每秒

IO 吞吐量，所以应该选用每秒吞吐量尽可能大的磁盘；

虽然IO 性能要求也比较高，但是并发请求较少，所以CPU 处理能力较难成为性能瓶颈，所以CPU 处

理能力没有太苛刻的要求；

虽然每次请求的访问量很大，但是执行过程中的数据大都不会返回给客户端，最终返回给客户端的

数据量都较小，所以和客户端交互的网络设备要求并不是太高；

此外，由于OLAP 系统由于其每次运算过程较长，可以很好的并行化，所以一般的OLAP 系统都是由多

台主机构成的一个集群，而集群中主机与主机之间的数据交互量一般来说都是非常大的，所以在集群中

主机之间的网络设备要求很高。

需求和架构及业务实现优化：55%

Query 语句的优化：30%

数据库自身的优化：15%

 

 

MySQL 各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定

 

 

一般来说，Query 语句的优化思路和原则主要提现在以下几个方面：

1. 优化更需要优化的Query；

2. 定位优化对象的性能瓶颈；

3. 明确的优化目标；

4. 从Explain 入手；

5. 多使用profile

6. 永远用小结果集驱动大的结果集；

7. 尽可能在索引中完成排序；

8. 只取出自己需要的Columns；

9. 仅仅使用最有效的过滤条件；

10. 尽可能避免复杂的Join 和子查询；

 

 

任何事物都是有两面性的，，Hash 索引也一样，虽然Hash 索引检

索效率非常之高，但是Hash 索引本身由于其实的特殊性也带来了很多限制和弊端，主要有以下这些：

1. Hash 索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；

由于Hash 索引所比较的是进行Hash 运算之后的Hash 值，所以Hash 索引只能用于等值的

过滤，而不能用于基于范围的过滤，因为经过相应的Hash 算法处理之后的Hash 值的大小关

系，并不能保证还和Hash 运算之前完全一样。

2. Hash 索引无法被利用来避免数据的排序操作；

由于Hash 索引中存放的是经过Hash 计算之后的Hash 值，而且Hash 值的大小关系并不一定

和Hash 运算前的键值的完全一样，所以数据库无法利用索引的数据来避免任何和排序运算；

3. Hash 索引不能利用部分索引键查询；

对于组合索引，Hash 索引在计算Hash 值的时候是组合索引键合并之后再一起计算Hash 值，

而不是单独计算Hash 值，所以当我们通过组合索引的前面一个或几个索引键进行查询的时

候，Hash 索引也无法被利用到；

4. Hash 索引在任何时候都不能避免表扫面；

前面我们已经知道，Hash 索引是将索引键通过Hash 运算之后，将Hash 运算结果的Hash 值

和所对应的行指针信息存放于一个Hash 表中，而且由于存在不同索引键存在相同Hash 值的

可能，所以即使我们仅仅取满足某个Hash 键值的数据的记录条数，都无法直接从Hash 索引

中直接完成查询，还是要通过访问表中的实际数据进行相应的比较而得到相应的结果。

5. Hash 索引遇到大量Hash 值相等的情况后性能并不一定就会比B-Tree 索引高；

对于选择性比较低的索引键，如果我们创建Hash 索引，那么我们将会存在大量记录指针信息

存与同一个Hash 值相关连。这样要定位某一条记录的时候就会非常的麻烦，可能会浪费非常

多次表数据的访问，而造成整体性能的地下。

 

不适合建立索引的情况：

较频繁的作为查询条件的字段应该创建索引

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；

更新非常频繁的字段不适合创建索引；

不会出现在WHERE 子句中的字段不该创建索引；

在MySQL 中，只有一种Join 算法，就是大名鼎鼎的Nested Loop Join，，Nested Loop Join 实际上就是通过驱动表

的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数

据，然后合并结果。

 

schema

适度冗余- 让Query 尽两减少Join

大字段垂直分拆- summary 表优化

大表水平分拆- 基于类型的分拆优化

 

优化数据类型提高性能的主要原理在于以下几个方面：

1. 通过选用更“小”的数据类型减少存储空间，使查询相同数据需要的IO 资源降低；

2. 通过合适的数据类型加速数据的比较；

 

 

MySQL 系统中，会对性能产生影响的MySQL 日志（不包括各存储引擎自己的日志）主要就是Binlog 了。

 

这一点和Oracle 的Redo 日志有点不一样，因为Oracle 的Redo 日志所记录的是数据文

件的物理位置的变化，而且里面同时记录了Redo 和Undo 相关的信息，所以同一个事务是否在一个日志中

对Oracle 来说并不关键。而MySQL 在Binlog 中所记录的是数据库逻辑变化信息，MySQL 称之为Event，

实际上就是带来数据库变化的DML 之类的Query 语句。

 

 

Innodb 存储引擎和MyISAM 存储引擎最大区别主要有四点，第一点是缓存机制，第二点是事务支持，

第三点是锁定实现，最后一点就是数据存储方式的差异