mysql 频繁插入删除优化

转载

mob64ca141275de 2024-10-09 09:17:34

文章标签 mysql 频繁插入删除优化数据库索引 mysql java 文章分类 MySQL 数据库

概述

自从学习 MySQL 以来，我们一直听到或者看到很多优化建议，比如说不要用 select * 查询，用什么字段就查什么字段；建议用自增主键来作为表的主键，等等。这些建议听得很多感觉都成了 MySQL 开发的常识了，但是对于这些优化建议，我们有没有想过为什么要这么做呢？这篇博文我们从 MySQL 的原理出发，来解释下为什么有这些优化建议？

预备知识

B+ 树索引

MySQL 的默认存储引擎 InnoDB 使用 B+ 树来存储数据的，所以在分析优化建议之前，我们有必要了解 B+ 树索引的基本原理。

上图是一个 B + 树索引示意图（B+ 树的定义可以看这里），每个节点表示一个磁盘块，也可以理解为数据库中的页。我们来分析下 B+ 树索引的查找过程，如果我要查询主键为 35 的数据，索引会怎么走呢？首先会判断 35 小于根节点 37 ，继续查询左子树，判断 35 大于 22 和 33 那么进入其右子树，找到了叶子节点 33 ，继续遍历找到了 35 ，最后取出其 data 即可。在索引的情况下，查询 35 只用了3次 IO 操作，这是非常高效的。在真实的场景下，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。上图中也是体现了只要维持树的高度足够低，IO 操作就会足够少，IO次数少，查询性能就会高。

Explain 执行计划

mysql 频繁插入删除优化_mysql 频繁插入删除优化

执行计划

上图就是一个 explian 执行计划，先看看上面各个字段的含义是什么？

id: Query Optimizer 所选定的执行计划中的查询编号。
select_type: 所使用的查询类型，主要有几种查询类型：

类型名称	说明
SIMPLE	除子查询或者UNION查询之外的其他查询。
PRIMARY	子查询中的最外层查询，注意并不是主键查询。
UNION	UNION语句中第二个 SELECT 开始的后面所有 SELECT，第一个 SELECT 为 PRIMARY。
DEPENDENT UNION	子查询中的 UNION，且为 UNION 中从第二个 SELECT 开始的后面所有 SELECT ，同样依赖于外部查询的结果集。
UNION RESULT	UNION 中的合并结果。
SUBQUERY	子查询内层查询的第一个 SELECT，结果不依赖于外部查询结果集。
DEPENDENT SUBQUERY	子查询内层查询的第一个 SELECT，结果依赖于外部查询结果集。
DERIVED	驱动表，就是主表
MATERIALIZED	子查询的结果被保存为虚拟临时表
UNCACHEABLE SUBQUERY	结果集无法缓存的子查询
UNCACHEABLE UNION	结果集无法缓存的 UNION 查询

table: 显示执行这一步所访问的数据库中的表的名称。
partitions: 查询分区表匹配的分区，非分区表显示 NULL 。
type: 查询表所使用的方式，类型如下：

类型名称	说明
all	全表扫描。
const	读常量，最多只有一条记录匹配，由于是常量，所以实际上只要读一次。
system	系统表，表中只有一条数据，它是特殊的 const 类型。
eq_ref	最多只会匹配一条结果，一般是通过主键或者唯一索引来访问。
ref	Join 语句中被驱动表的索引查询
full_text	使用 full_text 索引
ref_or_null	与ref唯一的区别就是在使用索引查询之外再增加一个空值查询。
index_merge	查询中同时使用两个（或者多个）索引，然后对索引结果进行merge之后再读表数据。
unique_subquery	子查询中的返回结果字段组合是主键或者唯一索引
index_subquery	子查询中的返回结果字段组合是索引，但是不是主键或者唯一索引
range	索引范围扫描，经常出现在比较条件中 ,如：<, > ,BETWEEN 等
Index	全索引扫描

他们的性能由好到差依次是：system > const > eq_ref > ref > full_text > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > all 。

possible_keys: 查询可能用到的索引。
key_len: 用到的索引长度。
ref: 展示将那些列或者常量与命中的索引比较。
rows: 执行这次查询扫描的行数。
filtered: 过滤行数百分比，最大值是100，当显示100时候，表示没有过滤行， rows显示了检查的估计行数，乘以过滤百分比将显示与下表连接的行数。例如，如果行数为1000，过滤条件为50.00（50％），则与下表联接的行数为1000×50％= 500。
extra: 执行查询额外的条件，详细的条件可以查看这里。

掌握了前面这些前置知识，我们来用这些知识分析下经常建议我们的那些MySQL优化建议。

为什么建议使用自增主键

当我们每次建立表的时候都在考虑是用表的自增主键呢？还是用 UUID 呢？但是从性能考虑我们还是建议使用自增 Id,为什么呢？主要是由于 MySQL B+ 树索引性质决定的，数据的新增是要更新索引的，也就是要更新 B+ 树。换句话说，使用自增Id 和非自增 Id 哪种更新 B+ 树更快，成本更低，谁就是更优的选择。我们来模拟下自增 Id 插入和非自增 Id 插入情况。

自增Id 插入情况： 我们在一个已经有10条数据的 B + 数上插入2条数据，分别是10和11，我们看看树是如何变化的。

自增id

我们这里可以发现两个特点：

1、自增的数据插入影响的范围永远只有最右的子树，要么直接在子树插入节点，要么就是子树分裂，影响其父节点。

2、除了最右子树，其他子树的节点都是满的。

上面两个特点有什么影响呢？我们根据前面 B+ 树索引示意图可以知道，每个点都是一个磁盘块，操作每个节点相当于进行一次 IO,由于每次插入影响的节点只有最右子树，那么磁盘 IO 的范围就可控；最重要一点是除最右子树，其他子树的节点都是满的，这种情况，叶子节点数据的物理连续性会更好， 根据局部性原理,查询性能也会更高。

非自增 Id 插入情况：

mysql 频繁插入删除优化_数据库_02

非自增id

非自增 Id 插入特点对比自增 Id 插入我们很容易就能知道：

1、插入影响节点不可控，无法预知。

2、每个子树都存在叶子节点不满的情况。

按照之前的分析思路，我们也就知道了非自增 Id 插入有什么性能劣势了。由于插入数据影响节点不可控，导致节点分裂的情况就会更频繁，节点分裂也是 IO 操作，性能自然受到影响。子树的叶子节点不满，会导致叶子节点物理连续性不好。最后如果我们是UUID的话，Id 过长，会占用节点空间，每个页能存储的节点变少，页分裂变多，性能也会受到影响。这也是为什么建议使用自增主键的原因。

为什么不要使用 select * 查询

我们经常听到查询表，只要查询自己想要的字段，不需要的字段就不要查询，严禁使用 select *，我们能想到很直观的理由就是，数据库要帮你翻译成每个字段名去查询，接着查询多余的字段会占用内存，带宽等资源。这确实是一个理由，而且这个理由很重要，但是我这里想说的是另外一个原因，覆盖索引。我之前的一篇索引文章也介绍了覆盖索引。覆盖索引的意思是指查询使用联合索引覆盖了要查询的字段，这样数据库不用去进行回表，从而减少IO,提高性能。

这里我用MySQL官方给的示列数据进行一个实验，数据地址下载可以点击这里。

我选择 employees 表数据进行演示，默认数据是没有联合索引的，我们加上一个联合索引：

-- - employee表结构---------- -
+------------ + -------------- - +------ + ---- - +-------- - +------ - +
| Field | Type | Null | Key | Default | Extra |
+------------ + -------------- - +------ + ---- - +-------- - +------ - +
| emp_no | int(11) | NO | PRI | NULL |       |
| birth_date | date | NO |     | NULL |       |
| first_name | varchar( 14 ) | NO | MUL | NULL |       |
| last_name | varchar( 16 ) | NO |     | NULL |       |
| gender | enum ('M', 'F') | NO |     | NULL |       |
| hire_date | date | NO |     | NULL |       |
+------------ + -------------- - +------ + ---- - +-------- - +------ - +

ALTER TABLE employees.employees add Index ` idx_first_name_last_name ` (first_name, last_name);

查看该表索引情况：show index from employees.employees;

mysql 频繁插入删除优化_mysql_03

表已经成功创建了first_name 和 last_name的符合索引，我们开启 profiles 监控 SQL 执行的情况。

SET SESSION profiling = 1;

然后分别执行以下SQL

SELECT first_name,last_name  FROM employees.employees WHERE first_name='Eric';
SELECT *  FROM employees.employees WHERE first_name='Eric';

查看Profiles；

mysql 频繁插入删除优化_java_04

这里我们看到使用 select * 比 select 字段慢了4倍左右，为什么会这样呢？我们看看执行计划。

我们看到两者的执行计划几乎一样，只有 Extra 有区别，使用字段的 Extra 显示 using index，这就告诉你只使用索引就找到了想要的数据，因为你的索引就是用 first_name 和 last_name 建立的，而 select * 它还需要查询 gender和hire_date字段（主键字段不用额外查，辅助索引指向的就是主键）,所以它不能不进行回表查询其他字段，性能差异也是这里。