mysql数据库存取原理及性能优化方案_MySQL性能优化

一、Mysql的系统架构图

 二、Mysql存储引擎

　　Mysql中的数据是通过一定的方式存储在文件或者内存中的，任何方式都有不同的存储、查找和更新机制，这意味着选择不同的方式对于数据的存取有效率的差距。 这种不同的存储方式在 MySQL中被称作存储引擎。

　　存储引擎是Mysql数据库系统的底层组件，数据库管理系统通过这些组件来进行创建、查询、更新和删除数据，它操作的对象是表。存储引擎类型在肉眼上体现为表的类型，Mysql的存储引擎类型可以通过运行命令” SHOW ENGINES;  “获得，目前Mysql8支持的存储引擎有如下几种：

• FEDERATED：将数据存储在远程数据库中，用来访问远程表的存储引擎。
• MRG_MYISAM：是一组MyISAM的组合，他将MyISAM引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyISAM引擎的表中，但是可以直接进行查询、删除更新等操作。
• MyISAM：是MySQL早期默认的存储引擎，拥有较高的插入、查询速度，表锁设计，支持全文索引，但不支持事务和外键。
• BLACKHOLE：充当一个“黑洞”，接受数据，但将其扔掉，不存储数据，类似于Linux系统中的/dev/null文件。主要用于查找和存储引擎无关的其他方面的性能瓶颈。
• CSV：会在MySQL安装目录data文件夹中，和该表所在数据库名相同的目录生成一个.CSV文件，它可以将CSV类型的文件当做表进行处理，相比其他存储引擎的文件内容，可以直接查看和编辑。
• MEMORY：数据保存在内存中，表结构保存在磁盘上。如果数据库重启或者发生崩溃，表中的数据都将消失，非常适用于存储临时数据的临时表。
• ARCHIVE：用于数据存档的引擎，仅仅支持最基本的插入(insert)和查询(select)两种功能。Archive拥有很好的压缩机制，比MyISAM、InnoDB存储引擎更加节约存储空间。
• InnoDB：是MySQL当前版本默认的存储引擎，支持事务安全表（ACID），支持行锁定和外键。要求实现并发控制，需要频繁的更新、删除操作的数据库，那选择InnoDB有很大的优势。
• PERFORMANCE_SCHEMA：MySQL数据库系统专用引擎，用户不能创建这种存储引擎的表。

　　查看当前默认的存储引擎：

SHOW VARIABLES LIKE "default_storage_engine%";

　　修改默认存储引擎(临时生效，重启后恢复InnoDB)：

SET default_storage_engine=< 存储引擎名 >

　　修改单个数据表的存储引擎：

ALTER TABLE <表名> ENGINE=<存储引擎名>;

　　查看数据表的存储引擎：

 SHOW CREATE TABLE tablename G

　　修改表的默认存储引擎，要想永久生效，需要在配置文件my.cnf中  [mysqld] 后面加入：

default-storage-engine=存储引擎名称

　　如何根据业务场景来选择合适自己的存储引擎呢?这需要先了解几种存储引擎的特性，以下是几种存储引擎的特性表，可做参考：

 三、InnoDB存储引擎存取原理

　　InnoDB是最常用的存储引擎，我们的业务系统在使用Mysql作为数据库的时候，一般都选择InnoDB作为存储引擎，原因是该引擎支持行锁、索引、事务安装、主外键，而这些特性正是业务系统数据所需要的。

　　数据在InnoDB中将被划分为若干个页，页的大小一般为16KB，以页作为存取数据的单位，前一节提到InnoDB的数据是存到磁盘上的，而程序代码需要的数据是在内存中的，因此存取数据的操作实际上是磁盘和内存之间数据交换的过程。

 　　以下是Mysql数据页的结构图，不同的部分代表着不同的功能，其中：

• File Header：表示文件的头部，存储了一些页的通用信息。
• Page Header：表示页面的头部，存储一些有关数据页的信息。
• Infimun+supremum：表示两个虚拟的行记录(最小记录和最大记录)。
• UserRecord：真正用于存储数据行的内容。
• FreeSpace：空闲部分。
• PageDirectory：页目录，记录了某些记录的位置。
• FileTrailer：页尾，用于校验页的完整性。

 　　UserRecord这部分空间是可变的，当向数据库中插入记录时，数据会被存到这个位置，UserRecord空间被增加，同时FreeSpace容量会变小，直到空闲空间FreeSpace被用完，如果还有新的数据插入，则引擎会申请新的数据页再进行存储。

　　多条数据被存到UserRecord这个位置，那他们之间的物理位置有什么关系呢？这里牵涉到两种主要关系，一种是同一数据页之间的数据记录位置关系，另一种是不同数据页之间的记录的位置关系。要谈记录的位置关系，我们要了解每条记录的存储结构。

　　每条记录都包含我们存储的真实字段数据和记录额外的信息数据，一行记录可以以不同的行格式存在，目前有Compact、Redundant、Dynamic、Compressed等几种格式，以下是Compact行格式的图解：

 其中，每个属性说明如下：

 　　记录是以单向链表的方式存储的，因此：

• 当我们插入一条记录时，新的记录会记录下它在堆中的位置并更新相邻的前一记录的next_record值为它的heap_no，更新它的next_record值为它的相邻位置后一记录的heap_no值。
• 当我们删除记录时，该记录的delete_mask会被标记为1，同时相邻的前一记录的next_record值被更改为它的下一记录的heap_no值。

 　　思考一个问题：如果一个数据表的列字段很多或者存储的值太多，一个数据页存不完怎么办？在Compact行格式中，对于占用存储空间很大的列，在记录真实数据的地方只存储一部分数据，把剩余的数据分散存储在其它页中，通过在记录数据的尾部存储指向其它页数据的地址来关联这些分散的数据。

四、InnoDB索引原理

　　我们知道，数据库使用索引的目的是为了加快查询速度，那么MySQL是如何实现索引的呢？

　　对于数据行的插入，默认情况下是顺序存储(按主键排序)的，查询的时候将按照插入的顺序显示结果。当一个数据页存储字段较少且字段值内容较少的时候，很有可能这个数据页就能够同时存储上千行记录，前一节我们说过，数据是以单向链表的方式存储的，这种存储方式对于插入来说比较快，而对于查询来说就比较慢，如果我们需要使用where查询条件从上千行记录中获取满足条件的记录，如何才能加快查找速度呢?

　　InnoDB引擎为我们提供了一个叫做PageDirectory的页目录，这个概念在前面介绍数据页结构的时候提到过，它用于为数据页的行记录提供目录索引，类似书籍的章节目录，能够帮助我们快速定位数据记录所在的分组，从而加快查询速度，其实现原理是：将页内所有非删除的记录划分为N个组，每个组里最后一条记录(主键最大的记录)的n_owned属性记录了组内的记录数量，将这条记录的偏移地址取出按序从File Trailer位置开始向前写入形成PageDirectory，其中偏移地址称为＇槽＇，图示如下：

 　　根据上图所示．如果我们要获取id=4的记录，可以先通过二分法从目录页中快速找到这一记录所在的页，然后在快速定位数据记录所在的位置。

.　　通过上面的解析，我们理解了从同一个数据页中快速查找记录的方式，再来想一想，如果数据行较多，分散存储到了多个数据页里，那又如何快速的确定数据在哪一个数据页的哪一个分组呢？

　　InnoDB为我们提供了”数据页”概念的同时，也给我们提供了一个叫做”目录页”的概念，目录页用于存储数据页的页号和页对应的记录的最小主键(相当存了一组{key，value}键值对集合，key主键，value为页号)。因此在执行条件查询时，先通过主键确定记录所在的页，在根据页内的页目录定位(通过主键定位)到分组，从而快速获取结果。结构图示如下：

 　　分析结构图，发现结构是一棵B+树，B+树是一种树数据结构，通常用于数据库和操作系统的文件系统中。B+树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+树元素自底向上插入，这与二叉树恰好相反。B+树的叶子节点用于存储真实数据，非叶子节点用于存储主键值和指针(页码)。

　　前面我们提到数据在插入时是按照主键的顺序来进行排序存储的，所以这就是为什么我们在设计表时建议设置主键自增的原因(新增记录时不会对之前的数据进行重新排列，这会加快插入的速度)。其实一个数据行字段除了我们自己创建的字段之外，还存在三个隐藏的字段row_id(行ID)、transaction_id(事务ID)、roll-pointer(回滚指针)，如果我们设计表时没有设置主键，也没有设置唯一索引，那么它会自动以隐藏的字段row_id来作为自增主键进行排序存储。

　　思考一个问题：如果一个列的值占用空间较多，会发生什么？当一行记录存储的数据较多时，意味着要使用更多的数据页，更多的数据页意味着更多的目录页，这会增加” B+结构的高度 “，这也会隐形的降低查询效率。

　　到这里我们已经知道，对于主键字段来说，它会通过页目录和目录页的方式来增加数据查询速度，但是在实际开发中，我们可能还需要通过其他字段的查询条件来筛选数据，那么InnoDB引擎又是如何通过什么方式来增加查询速度的呢？

　　其实，我们也可以为非主键字段来创目录页，这些目录页同样组成了B+树结构，只不过它的叶子节点存储的是主键值，当通过非主键字段查询记录时，首先会通过非主键目录页B+树结构查找到主键，再去调用主键的目录页B+树，去查找真实数据。我们知道，除了可以为单独为非主键字段的某一个字段创建索引，还可以使用联合多个字段来创建一个索引，这两种方式的实现方式都是相同的。

　　根据前面的分析和探究，我们需要知道如下几点：

• InnoDB引擎在磁盘和内存之间进行数据传输时是以“ 数据页 ”为基本单位传输的。
• 数据行记录是以单向链表的方式存储的，特点是插入快，更新和查询慢。
• 目录页能够帮助引擎快速定位记录所在的数据页。
• 页内目录能够帮助引擎快速定位记录所在的分组。
• 为某个字段创建索引就相当于为这个字段创建了一个B+树结构的目录页。
• 以多个字段为一体创建了一个索引就相当为这些字段组合创建了一个B+树结构的目录页。
• 如果定义表时指定了主键和唯一索引，则存储时将以主键和唯一索引的逻辑顺序进行物理存储，这种索引中键值的逻辑顺序决定了表中相应行的物理顺序，这种索引称为聚集索引，反正称为非聚集索引。
•  如果定义表时没有指定主键和唯一索引，则InnoDB会以隐藏的字段row_id为主键进行数据存储。
• 综上前两点证明，建表时会产生一个聚集索引(每张表值只有一个)，聚集索引产生的B+树结构的叶子节点存储的是真实数据(完整数据)，非叶子节点存储的是主键和页码指针。
• 非聚集索引是手动创建的，用于在聚集索引之外创建目录页B+树，叶子节点存储的是索引列和主键值(不是指针)，非叶子节点存储的是索引列和页码指针。
• 为已经存在大量数据的表创建和修改索引时，可能需要长时间的等待操作，甚至导致数据库崩溃。

五、表的扫描

　　我们已经了解了索引的原理，知道了可以为表建立多种索引方式，来加快我们的查询速度，但是不同的索引类型的查询效率是有区别的，mysql有几种表的扫描机制：

• 全表扫描：遍历整个主键索引的B+树，并且需要读叶子节点数据。
• 全索引扫描：遍历整个二级索引的B+树。
• 回表查询：对于非聚集索引的查询，会先查非聚集索引的B+树定位主键值，再用这个值去聚集索引的B+树上查找数据。

在进行数据查询是应该尽量使用最小的扫描代价去获得结果，这是SQL性能优化的核心思想，需要注意的是并非避免全表扫描就能获得最佳效果，反之在具有多个索引的中，过多的索引组合反而可能导致效果不如全表扫描实在，这就是在创建索引时并非越多越好的原因。

　　Mysql给我提供了一个关键字explain用于分析和测试sql语句的性能，在SQL语句前增加该关键字，运行后会返回SQL执行计划的信息，通过这些信息，可以帮助我们更好的选择索引和优化SQL语句，例如运行如下语句得到结果如图：

 其中，结果集的每个字段和可能的值说明如下：

六、数据库优化

　　我们已经了解了索引的原理，明白了数据页的存储结构是一棵B+树，同时目录页存储结构也是一棵B+树，能够通过索引定位数据所在的父节点，减少表的扫面，从而快速找到记录。但是当存在的索引越多时，查找数据的实现方案就越多，那如何才能尽量让我们编写的SQL尽量使用最优的方案呢？

　　文章头部的Mysql架构图中我们可以看到，我们编写的SQL语句在经过分析器进行语法分析后，传递给优化器，优化器帮助我们选择索引并生成执行计划，然后再交给执行器获取操作结果，但是优化器并非万能的，它只能为我们执行一些可控的优化。我们还需要从减少数据的碎片存储、不同类型数据的索引效率、表的扫面机制等方面考虑，特此总结出如下几个优化和设计规则：

• 控制字段长度：在设计表的字段的时候，根据业务需求，尽量控制字段的长度空间不浪费，比如存储身份证号的字段不超过18个字符，手机号的字段不超过11位。
• 控制字段类型：根据mysql提供的字段类型特性，来选择合理的字段类型存储业务数据，比如存状态0和1使用boolean类型而不使用字符类型CHAR(1)。
• 尽量满足表设计的三大范式，建立合理的数据库结构，但同时也要考虑需求本身。
• 控制热表数据量：结合业务需求，尽量让热表的数据量偏小，必要时采用分库分表的方式，分表包括垂直分表和水平分表，具体根据业务需求来定。
• 尽量控制事务尽快提交和回滚，以免造成长时间锁表锁行。
• 尽量减少SQL参与过多的计算逻辑，比如createtime字段不做时间格式转换处理。
• 禁止使用外键来约束数据的一致性，如需约束请在业务代码中实现。
• 尽量使用自增INT/BIGINT类型作为主键，使用CHAR和UUID作为主键会导致数据的存储顺序离散，产生磁盘碎片。
• 统一库、表、存储过程的字符集，字符集的不同会导致隐式转换或报错，或导致无法使用索引，InnoDB通常推荐使用utf8mb4。
• 索引会影响插入性能，单表索引数量不建议超过5个，必要时可以建立合适的多列索引并注意其顺序。
• 核心涉密数据加密后存储，这主要是为了数据泄露安全考虑。
• 使用INT类型来替代浮点型，避免使用浮点型这种效率较低的类型。
• 遇到BlOB、TEXT等大对象类型的数据，尽量拆分为单独表，然后使用主键做关联。
• 字符类型尽量使用灵活高效的varchar来代替char类型，尽量减少字符串类型的加长更新操作。
• 日期类型尽量选择datetime类型，因为timestamp类型只能存储日期时间为1970-2038年，而char类型查询效率比datetime低。
• 多表JOIN时，条件列的数据类型要一致，否则可能导致无法使用索引。
• 多表JOIN时，将过滤后结果集较小的表作为驱动表。
• SELECT时尽量不读取不使用的列，这可以减少IO代价。
• 减少LIKE  “% xxxx %”格式的使用，该语句会导致查询不使用索引，必要时可以使用LIKE  ” xxxx %”格式，它会使用前缀索引。
• 尽量主要” != “条件的使用，这有可能导致全表扫描，当然并非一定会全表扫描，具体情况的具体分析。
• 当能确定返回结果数量时，最好加上LIMIT N，当查询到指定数量结果后就会立即结束扫描。
• 优先使用UNION ALL代替UNION，因为UNION会产生临时表，前者还不会产生去重的代价。 

七、SQL的慢查询分析

　　SQL的执行总是有一个执行时间的，我们可以给数据库设置一个时间节点参数long_query_time，当SQL执行超过这个值时，便认为本次查询是慢查询，将被记录到慢查询日志中，有利于我们进行性能排查。默认的long_query_time默认值为10s。

开启慢查询日志：

• 在my.cnf中配置：

[mysqld]
log-slow-queries=/data/mysqldata/slow-query.log  #慢查询日志的位置
long_query_time=10   #表示查询超过10s就作为慢查询

• 命令临时开启：

set global slow_query_log="ON";  #开启慢查询

set global slow_query_log_file="/usr/local/slow.log"; #指定日志文件路径

set global long_query_time=10;  #自定慢查询的时长　　

八、常用的SQL算法设计

• 使用 WITH RECURSIVE tmp AS 语法实现递归查询，比如获取数结构的所有子节点Id：

with RECURSIVE  tmp as
(
    select * from work_order_question where id=4
    union all
    select c.* from work_order_question as c,tmp t where c.parent_id=t.id
)
select id from tmp