003MySQL性能调优002存储引擎

1 逻辑架构

1.1 总体概览

1.1.1 说明

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。

主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。

这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.1.2 架构图

架构如图：

1.2 执行流程

MySQL的查询优化流程如图：

流程说明：

MySQL客户端通过协议与MySQL服务器建立连接，发送查询语句。

服务器先检查查询缓存，如果命中了缓存，立即返回缓存中的数据。如果没有命中缓存，则进入下一阶段。

服务器对查询语句进行一系列预处理，再对SQL语句进行解析并创建一个内部的数据结构，然后进行各种优化，由优化器生成对应的执行计划。

根据优化器生成的执行计划，调用存储引擎的API来执行查询。

将结果返回给客户端。

2 模块分层

2.1 分层说明

2.1.1 连接层

最上层是一些客户端和连接服务，包含本地Sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。

主要完成一些类似于连接处理、授权认证、及相关的安全方案。

在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.1.2 服务层

第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是查询语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

2.1.3 引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。

不同的存储引擎具有的功能不同，这样可以根据自己的实际需要进行选取。最常用到的是MyISAM和InnoDB。

2.1.4 存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

2.2 模块说明

2.2.1 Connectors

指的是不同语言中与SQL的交互。

2.2.2 Management Serveices & Utilities

系统管理和控制工具。

2.2.3 Connection Pool

管理缓冲用户连接，线程处理等需要缓存的需求。负责监听对MySQL服务器的各种请求，接收连接请求，转发所有连接请求到线程管理模块。

每一个客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责与客户端的通信，接受客户端的命令请求，传递服务端的结果信息等。线程管理模块则负责管理维护这些连接线程，包括线程的创建，线程的缓存等。

2.2.4 SQL Interface

接受用户的SQL命令，并且返回用户需要查询的结果。

2.2.5 Parser

SQL命令传递到解析器的时候会被解析器验证和解析。

将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。

如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。

2.2.6 Optimizer

查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。

根据查询语句和数据库中的统计信息，在一系列算法的基础上进行分析，得出最优的策略，告诉后面的程序如何取得查询语句的结果。

2.2.7 Cache & Buffer

查询缓存，主要功能是将请求的返回结果集缓存到内存中，与查询语句的一个哈希值做一个对应。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，键缓存，权限缓存等。

2.2.8 Pluggable Storage Engines

MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。

MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。

注意：存储引擎是基于表的，而不是数据库。

2.2.9 File System

文件存储系统，将对数据做的改动永久保存到本地磁盘。

2.2.10 Files & Logs

文件和日志，MySQL会在执行SQL的时候记录执行的日志，用于进行数据恢复等操作。

3 存储引擎

3.1 是什么

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

3.2 查看

MySQL支持多种类型的数据库引擎，可分别根据各个引擎的功能和特性为不同的数据库处理任务提供各自不同的适应性和灵活性。

MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

在MySQL中，可以使用“show engines;”语句来显示可用的数据库引擎和默认引擎：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>

Support列的值表示引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。

使用“show variables like 'storage_engine';”命令可以查看当前环境下使用的引擎。

3.3 种类

3.3.1 InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期事务。此外，InnoDB还支持行级锁，适合高并发情况。

除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

3.3.2 MyISAM存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数等，但不支持事务和行级锁。

MyISAM拥有较高的插入、查询速度，是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

3.3.3 Memory存储引擎

Memory将数据存储到内存中，可以快速地访问数据，但不支持修改，并且重启以后数据会丢失。

Memory至少比MyISAM要快一个数量级，使用专业的内存数据库更快，如Redis数据库。

3.3.4 Archive存储引擎

Archive只支持插入和查询操作，在MySQL 5.1之前不支持索引。

Archive适合低访问量大数据等情况，比如日志和数据采集类应用。

3.4 选择

为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。不同的存储引擎都有各自的特点，以适应不同的需求，如表所示：

可以根据以下的原则来选择MySQL存储引擎：

1）如果要提供提交、回滚和恢复的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个很好的选择。

2）如果数据表主要用来插入和查询记录，则MyISAM能提供较高的处理效率。

3）如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的Memory中，MySQL中使用该引擎作为临时表，存放查询的中间结果。

4）如果只有插入和查询操作，可以选择Archive，支持高并发的插入操作，但是本身并不是事务安全的。

提示：使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求，使用合适的存储引擎将会提高整个数据库的性能。

3.5 对比

对比InnoDB和MyISAM：