MySQL 高级特性(三):数据表分区的应用与合并表简介

前面两篇讲述了数据表分区的概念,特性及可能的缺陷,本篇介绍数据表分区的应用。

优化查询

分区引入了的新的方式来优化查询(当然也会有一些坑)。最大的特性是优化器可以使用分区函数过滤掉无关的分区。这个特性就像是一个粗粒度的索引,使得查询时可以访问更少的数据。

因此在 WHERE 子句中指定分区条件十分重要,即便有可能是多余的。有了这个条件,优化器就可以过滤掉不需要的分区。如果不这么做的话,查询执行器引擎不得不访问数据表的全部分区,这会导致极其慢的查询速度。

可以通过 EXPLAIN PARTITION 指令来查看优化器是否过滤掉了分区,以下面的例子为例:

EXPLAIN PARTITIONS SELECT * FROM sales;
id: 1
select_type: SIMPLE
partitions: p_2019,p_2020,p_2021
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: 

如上面所见,这种情况会访问全部的分区,而如果在 WHERE 条件增加约束条件:

EXPLAIN PARTITIONS SELECT * FROM sales_by_day WHERE day > '2020-01-01';
id: 1
select_type: SIMPLE
table: sales_by_day
partitions: p_2020,p_2021

优化器对分区的优化很到位,将范围值转换为离散的值并会针对它们决定要查询哪些分区。然而,它并不是万能的,下面的查询看起来是可以优化的,实际却不会:

EXPLAIN PARTITIONS SELECT * FROM sales_by_day WHERE YEAR(day) > 2020;
id: 1
select_type: SIMPLE
table: sales_by_day
partitions: p_2019, p_2020,p_2021

MySQL 只能在分区函数对应的列上进行分区过滤优化,而不能基于一个表达式,即便是表达式的结果看起来是一样的。这和索引不能用在表达式的参数里是一样的道理。上面的查询可以用下面等效的方式进行优化:

EXPLAIN PARTITIONS SELECT * FROM sales_by_day 
WHERE day BETWEEN '2020-01-01' AND '2020-12-31';
id: 1
select_type: SIMPLE
table: sales_by_day
partitions: p_2020

由于 WHERE 条件直接引用了分区列的值,而不是表达式,优化器可以进行分区优化。这个特性的首要原则是我们可以使用表达式进行分区,但必须依据分区列进行搜索查询。

在查询过程中,优化器也足够聪明地进行分区优化。例如,如果分区表在联合查询的第二张表中,而联合查询的条件是分区键,那么 MySQL 会只在关联的分区中查找数据行(但 EXPLAIN 不会显示优化结果,这是因为这是在运行中优化的,而不是查询优化期)。

合并表

合并表的更早的、更简单的分区,存在不同的限制并且优化手段更少。由于数据表分区是处于严格的抽象层,并且不允许直接访问分区的物理隐藏表。而合并表则允许我们从合并的表中单独访问隐藏的物理表。随着未来分区的进一步集成化和优化器的改进,合并表很可能被废弃甚至被移除。

合并表实际上就是真实物理表的一个容器。可以通过一个特殊的 UNION 语法去创建多张表进行合并,下面就是一个例子:

CREATE TABLE t1(a INT NOT NULL PRIMARY KEY) ENGINE=MYISAM;
CREATE TBLE t2(a INT NOT NULL PRIMARY) ENGINE=MYISAM;
INSERT INTO t1(a) VALUES (1), (2);
INSERT INTO t2(a) VALUES(1), (2);
CREATE TABLE mrg(a INT NOT NULL PRIMARY KEY)
ENGINE=MERGE UNION=(t1, t2) INSERT_METHOD=LAST;
SELECT a FROM mrg;

执行下面的操作后,其实相当于从两张表同时取出了数据,结果是:

a
1
1
2
2

需要注意的是实际的表的字段名,数量和类型都相同,而且索引既在合并表上有,在各自的分表也有。这是创建合并表的要求。注意在每个分表在相同的列上都是相同的主键,但是合并表可以有重复的行。这是合并表的缺陷,合并表的每条数据看起来都是正常的,但合并表不会自己增加相应的约束,因此需要使用程序来约束。

INSERT_METHOD=LAST 指令告知 MySQL 会将对上一个表的插入数据同时发送到合并表中。我们只能指定 FIRST 或 LAST 操作,而且也可以直接对分表单独操作。这与分区表是不同的,分区表对数据的存储有更多的控制条件。

往合并表插入数据会同时在合并表和分区表中插入。

INSERT INTO mrg(a) VALUES(3);
SELECT a FROM t2;
a
1
2
3

合并表还有一些有趣的特性和限制,例如删除合并表或其中的一个分表。删除合并表不影响其分表,但是删除其中一个分表则根据操作系统不同而不同。例如,对于 Linux 系统,分表的文件描述符依旧保留并且数据表也存在,但只能通过合并表查询。

DROP TABLE t1, t2;
SELECT a FROM mrg;
a
1
1
2
2
3

下面是合并表的一些限制,这些需要记住,以避免踩坑:

  • 创建合并表的操作不会检查分表是否兼容,如果分表的定义存在不同,MySQL 可能创建一个不能用的合并表。而且,在创建一个有效的合并表后再单独修改一个分表会导致合并表无法继续使用。
  • REPLACE 操作对 合并表来说不可用,并且自增键也一样不会像预期那样工作。
  • 查询合并表会访问每个分表。这也会导致单行的查询相比单表查询更慢。因此,最好是限制分表的数量,尤其是这个表可能作为联合查询的第二张表时。每次访问的数据越少,合并表的性能影响会越大。
  • 通过提前访问分区表可以使得范围查询相比单独直接对合并表查询受到的影响更小。
  • 数据表扫描的速度对于合并表和正常表而言是相同的。
  • 唯一性字段和主键在一旦查询成功后就会停止查询,这种情况下,服务端会依次访问一张分表,直到获取到想要的数据。
  • 分表查询的次序是根据创建表时候的次序决定的,如果需要经常对数据指定访问次序,可以通过这个特性改善合并表的排序操作速度。
  • 由于合并表没有隐藏 MyISAM 的数据表,会提供一些数据表分区所没有的特性:一个 MyISAM 表可以是多个合并表的分表;可以直接在不同的服务器复制分表的.frm,.MYI 和.MYD 文件。在合并表定义好之后,还可以加入更多的分表。可以创建一个临时合并表,例如指定时间范围的数据,这是数据表分区没法做到的。可以从合并表中移除一个分表,以便进行备份、恢复、更改、修复或其他操作。而完成这些操作后又可以加入到同一张合并表。
  • 可以使用 myisampack 压缩某些分表。

而数据表分区则不同,所有的物理表被 MySQL 服务端隐藏了,只能通过分区表访问和控制。

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 216,544评论 6 501
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,430评论 3 392
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 162,764评论 0 353
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,193评论 1 292
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,216评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,182评论 1 299
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 40,063评论 3 418
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,917评论 0 274
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,329评论 1 310
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,543评论 2 332
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,722评论 1 348
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,425评论 5 343
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 41,019评论 3 326
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,671评论 0 22
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,825评论 1 269
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,729评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,614评论 2 353

推荐阅读更多精彩内容