mysql优化指南之原理篇

之前碰到一个线上问题，在接手一个同事的项目后，因为工期比较赶，我还没来得及了解业务背景和大致实现，只是了解了上线发布的顺序和验证方式就进行了上线，在上线进行金丝雀的时候系统还没发生什么异常，于是我们进行了全量发布，全量完成后监控显示有一个接口在间歇性的显示耗时会比较长，然后我们在观察是否影响面和判断是否需要回滚中。另一方面在我们全量发布后大约10分钟后，其他一个业务线就进行了紧急告警，但是他们没有上线，在众多排查手段用了之后，dba排查到我们系统的库和他们的业务系统库用的是一个机器，所以告知我们，然后我们就执行了回滚步骤中的关闭业务开关，关闭后双方业务的监控表明问题消失了。

之后我们就进行解析问题，发现是在某些业务逻辑中操作的一个查询没有走索引导致的，然后在下一个窗口进行了重新上线，接口耗时和对方业务的告警都没有了。但是在上线一段时间后，在夜里2点到5点的时候，该系统仍然会显示一些实时接口间歇耗时，然后我们看了那段时间的系统运行情况，发现其中一个定时任务会那段时间运行，应该是两者争相使用数据库连接池导致的，最终在测试环境复现，在扩大数据库连接池后问题消失。

这次情况给我的启示就是一定要注重了解学会了解清楚怎样承接一个工程，和在数据库优化这块的知识，所以我就进行了一些mysql优化所学的整理，分享给大家。要了解清楚索引的使用情况、连接池情况和数据库的部署是混合部署还是独立部署。

关于数据库的优化，本次是想先讲明数据的一些原理，然后再进行一些优化的讲解。

一、mysql的原理

（一）mysql体系结构

首先我们来了解下mysql系统是怎样的，如下图：

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

1.网络连接层

客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。目前几乎支持所有主流

的服务端编程技术，例如常见的 Java、C、Python、.NET等，它们通过各自API技术与MySQL建立连接。

2.服务层（MySQL Server）

服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优

化器和缓存六个部分：

（1）连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个

连接。

（2）系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群

管理等

（3）SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结

果。比如DML、DDL、存储过程、视图、触发器等。

（4）解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步

检查解析树是否合法。

（5）查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计

划，然后与存储引擎交互。

select uid,name from user where gender=1;

选取--》投影--》联接 策略

1）select先根据where语句进行选取，并不是查询出全部数据再过滤

2）select查询根据uid和name进行属性投影，并不是取出所有字段

3）将前面选取和投影联接起来最终生成查询结果

（6）缓存（Cache&Buffffer）：

缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓

存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

3.存储引擎层（Pluggable Storage Engines）

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，

服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有

很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

4.系统文件层（File System）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储

层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

（1）配置文件：用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。

（2）数据文件：

db.opt 文件：记录这个库的默认使用的字符集和校验规则。

frm 文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会

有一个frm 文件。

MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个

.MYD 文件。

MYI 文件：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对

应一个 .MYI 文件。

ibd文件和 IBDATA 文件：存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种

表空间方式：独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据，且每一张

InnoDB 表对应一个 .ibd 文件。共享表空间使用 .ibdata 文件，所有表共同使用一个（或多

个，自行配置）.ibdata 文件。

ibdata1 文件：系统表空间数据文件，存储表元数据、Undo日志等 。

ib_logfifile0、ib_logfifile1 文件：Redo log 日志文件。

（二） sql执行流程

sql执行的流程一般是这样的，如下图：

1.建立连接（Connectors&Connection Pool），通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。

2.查询缓存（Cache&Buffffer）

这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在

查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询

缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。

缓存Select查询的结果和SQL语句

执行Select查询时，先查询缓存，判断是否存在可用的记录集，要求是否完全相同（包括参

数值），这样才会匹配缓存数据命中。

即使开启查询缓存，以下SQL也不能缓存

查询语句使用SQL_NO_CACHE

查询的结果大于query_cache_limit设置

查询中有一些不确定的参数，比如now()

show variables like '%query_cache%'; //查看查询缓存是否启用，空间大小，限制等

show status like 'Qcache%'; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

3.解析器（Parser）将客户端发送的SQL进行语法解析，生成"解析树"。预处理器根据一些MySQL

规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别

名，看看它们是否有歧义，最后生成新的“解析树”。

4.查询优化器（Optimizer）根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最

优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。

5.查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以

及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开

启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffffer）中，以后若有

相同的 SQL 语句执行则直接返回结果。

如果开启了查询缓存，先将查询结果做缓存操作，返回结果过多，采用增量模式返回

（三）sql语句的中关键字执行顺序

在编写一条查询语句时，习惯性的从头到尾开始敲出来，应该都是从select 开始吧，但似乎没太注意它们真正的执行顺序；既然要优化，肯定需要得知道一条SQL语句大概的执行流程，结合执行计划，目的就更加清晰啦；上一张一看就明白的图：

关键字简述：

• FROM：确定数据来源，即指定表；
• JOIN...ON：确定关联表和关联条件；
• WHERE：指定过滤条件，过滤出满足条件的数据；
• GROUP BY：按指定的字段对过滤后的数据进行分组；
• HAVING：对分组之后的数据指定过滤条件；
• SELECT：查找想要的字段数据；
• DISTINCT：针对查找出来的数据进行去重；
• ORDER BY：对去重后的数据指定字段进行排序；

• LIMIT：对去重后的数据限制获取到的条数，即分页；

（四） mysql使用到的硬件

mysql使用硬件主要的工作内容如下：

CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；

网络：结果数据传输、SQL请求、远程数据库访问（dblink）；

硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。

下面我们看下硬件资源的CPU、内存、硬盘、网卡的性能指标。

从图上可以看到基本上每种设备都有两个指标：

延时（响应时间）：表示硬件的突发处理能力；

带宽（吞吐量）：代表硬件持续处理能力。

从上图可以看出，计算机系统硬件性能从高到代依次为：

CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

（五）存储引擎

存储数据时，影响存储速度的主要是索引、唯一性校验、一次存储的数据条数等。存储数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有，MyISAM和InnoDB，下面来简单介绍下：

	MyISAM	InnoDB
物理文件构成区别	MyISAM表是独立于操作系统的，每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，分别是 “.frm”表元数据定义 “.MYD”数据存储 “.MYI”存储索引	Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在。基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
事务处理	MyISAM并不支持事务这样的高级数据库特性，但MyISAM类型的表强调的是执行性能。	InnoDB提供对事务的支持、外键约束。
锁	MyISAM存储引擎只支持表锁，锁的粒度较粗。	提供行锁，不过需要注意的是，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1  where name like “%aaa%”
索引的结构	MyISAM引擎使用B+Tree作为索引结构。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。	InnoDB引擎用的也是B+Tree作为索引结构。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。
DB的CRUD操作	MyISAM比较适合执行大量查询的操作，在筛选大量数据时候非常迅速是其的特点。	执行大量的insert或update，出于性能方面的考虑，应该使用InnoDB表。
场景	MyISAM管理非事务表，它提供高速存储和检索，以及全文搜索能力，适合需要执行大量的SELECT查询类似数据仓库这样查询频繁的应用。	InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

如上面表格中“索引的结构”一栏所述，MyISAM存储引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下面是MyISAM中索引的原理图：

MyISAM中索引检索的算法先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。这里我们假设这个表仅有三列，分别是Col1、Col2和Col3列。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。假设我们在Col2列上建立一个辅助索引则索引结构如下：

虽然InnoDB也是使用B+Tree作为索引结构，但是具体实现方式与MyISAM截然不同。InnoDB的索引结构如下图所示：

在上面索引结构的具体实现方式上有两个区别，第一个区别在于InnoDB的数据文件本身就包含了索引部分。而从上文两种存储引擎区别的表格中可以知道，MyISAM索引和数据部分是分离的，索引文件仅保存的是数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引示意图：

了解InnoDB的索引实现后，就容易明白为什么不应该使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再比如，用非单调的字段作为主键在InnoDB存储引擎中并不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

了解完上述内容，我们要进行mysql的优化，大致能做的就是：

合理安排资源、调整系统参数使MySQL运行更快、更节省资源。优化方面主要主要包括查询、表设计、服务器等。最终要达到的效果是减少系统瓶颈，减少资源占用，增加系统的反应速度。

待补充的磁盘阵列和hdd的磁盘读取方式