文档章节

mysql使用MRG_MyISAM(MERGE)实现水平分表

风吹屁屁凉
 风吹屁屁凉
发布于 2015/03/04 18:11
字数 1882
阅读 216
收藏 2

在MySql中数据的优化尤其是大数据量的优化是一门很大的学问,当然其它数据库也是如此,即使你不是DBA,做为一名程序员掌握一些基本的优化信息,也可以让你在自己的程序开发中受益匪浅。当然数据库的优化有很多的方方面面,本篇主要讲,Mysql的水平分表技术,也可以说是其技术的其中之一。

在使用水平分表时,首先问下自己几个问题。

第一、为什么要水平分表?

第二、什么时候需要水平分表?

第三、怎样实现水平分表?

一、为什么要水平分表?

简而言之,当单表数据量过大时,无法对其进行有效的维护,以及查询速度严重变慢时,我们就需要对其时行水平分表

二、什么时候需要水平分表?

在数据库结构的设计中,需要充分考虑后期数据的增长量和增长速度,如果后期的数据增长量过快,以及后期数据量巨大,就需要使用水平分表。

三、怎样实现水平分表?

其实水平分表的方法,很多,但个人觉得结合程序的增删改查,本篇介绍的方法MRG_MySIAM存储引擎(MERGE存储引擎)个人觉得还是比较简单方便的,虽然性能方面与其它分表技术相比可能不是第一,但就使用程序对其的操控性来说,个人觉得还是很不错的。

MERGE存储引擎基本介绍和使用规范说明【以下截自MySql手册】:

MERGE存储引擎,也被认识为MRG_MyISAM引擎,是一个相同的可以被当作一个来用的MyISAM表的集合“相同”意味着所有表同样的列和索引信息。你不能合并列被以不同顺序列于其中的表,没有恰好同样列的表,或有不同顺序索引的表。而且,任何或者所有的表可以用myisampack来压缩。表选项的差异,比如AVG_ROW_LENGTH, MAX_ROWS或PACK_KEYS都不重要。

当你创建一个MERGE表之时,MySQL在磁盘上创建两个文件。文件名以表的名字开始,并且有一个扩展名来指明文件类型。一个.frm文件存储表定义,一个.MRG文件包含被当作一个来用的表的名字。这些表作为MERGE表自身,不必要在同一个数据库中。

你可以对表的集合用SELECT, DELETE, UPDATE和INSERT。你必须对你映射到一个MERGE表的这些表有SELECT, UPDATE和DELETE 的权限。

如果你DROP MERGE表,你仅在移除MERGE规格。底层表没有受影响。

当你创建一个MERGE表之时,你必须指定一个UNION=(list-of-tables)子句,它说明你要把哪些表当作一个来用。如果你想要对MERGE表的插入发生在UNION列表中的第一个或最后一个表上,你可以选择地指定一个INSERT_METHOD选项。使用FIRST或LAST值使得插入被相应地做在第一或最后一个表上。如果你没有指定INSERT_METHOD选项,或你用一个NO值指定该选项。往MERGE表插入记录的试图导致错误。

大致了解了MERGE存储引擎的基本介绍后,就让我们真正开始动手吧。

在分表的我们必须考虑如下问题:

1、根据什么样的规则来实现分表,即通过什么样的规则来插入不同的数据表?

2、即使分表成功,那么程序对其的处理是否简洁?

下面以下实例来说明,

假设我们有个邮件服务器,需要存储很多很多用户的邮件,为了解决后期数据量具大问题,我们就需要使用水平分表技术。

以什么样的规则来实现分表,分表数据如何确定?

首先我们必须大概估算以后的数据量会多大,分多少张表比较合适,从而来确定分表规则。

以我的情况为例,

我觉得以邮件的发送时间来计算,按天来划分,分为31张表比较合适。

那么我的分表规则,则如下设计,:

    email为主表,email_X为子表,先创建子表,结构与主表相同,在创建子表时注意要将ENGINE=MRG_MyISAM替换为ENGINE=MyISAM,否则会出现Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist的错误提示,不能对主表进行查询

CREATE TABLE `email` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `euid` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '帐号ID',
  `uid` char(50) NOT NULL COMMENT '邮件UID',
  `reciever` char(255) NOT NULL COMMENT '收件人',
  `sender` char(255) NOT NULL COMMENT '发送人',
  `sendTime` int(10) unsigned NOT NULL DEFAULT '0',
  `sendTitle` char(100) NOT NULL COMMENT '主题 ',
  `type` char(50) NOT NULL COMMENT '类型',
  PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM
 DEFAULT CHARSET=utf8 
UNION=(`email_1`,`email_2`,`email_3`,`email_4`,`email_5`,`email_6`,`email_7`,`email_8`,`email_9`,`email_10`,`email_11`,`email_12`,`email_13`,`email_14`,`email_15`,`email_16`,`email_17`,`email_18`,`email_19`,`email_20`,`email_21`,`email_22`,`email_23`,`email_24`,`email_25`,`email_26`,`email_27`,`email_28`,`email_29`,`email_30`,`email_31`);

首先创建一张MERGE存储类型的主表,

然后再批量创建31张MyISAM存储类型的数据表。

OK,此时创建完成后,我们需要做的是什么?

当然,第一步肯定是写入数据。此时我们的分表规则就有了用武之地了。

$sql = "INSERT INTO email_{$ruleNum}(....) VALUES(.....);"

此时完全可以正确的写入,并且在Email表中也会存在,是不是很OK。

但别高兴太早,我们要做的远远不止这些。

首先,因为ID是Auto_Increment,你完全可以不用管,因为每次插入不同的数据表都会有不同的ID,但问题是当你在EMAil这个Merge类型表中查看时你会发现,会有很多重复的ID,因为每张表的ID在email表中展现可能会有大量重复。这对我们修改和删除会有极大的影响,如果没有惟的ID,默认修改是根据排序来分别的,当然不可以。

所以在数据写入时,我们必须还要手动增加ID,来保证整个数据的ID都是惟一的。

方法当然有很多种,简单介绍下我的做法,

我直接新建了一张表就一个字段:


在每次新增完成数据后,都会使用触发器自动将此表中的数据值+1,而在每次读取时,先读取此表,获取下一个ID,这样就能保证数据ID永远惟一。

PS:也可以将此ID值存入文件,前提是在不会丢失的情况下。或其它都OK。

写入问题解决后,就剩下UPDATE,DELETE,SELECT了,这些现在都已不是问题,我们直接操作Email这个Merge类型表即可,(Mysql手册也有详细的介绍,可自行查看)

INSERT:

SELECT * FROM eamil where ($where) limit 20,10;

UPDATE:

UPDATE email SET username='$username' WHERE id=10

DELETE:

DELETE FROM email WHERE id=11

这只是一种MySql的水平分表方法,如果数据表较少的话,也可以使用

union 联合查询来实现数据表分表联合查询。

其它方法,网上也有很多,可自行查看。

本文转载自:http://crcms.cn/html/webserver/mysql/201408/15/122.html

共有 人打赏支持
风吹屁屁凉
粉丝 12
博文 49
码字总数 11095
作品 0
海淀
程序员
私信 提问
Mysql 之 添加innodb支持

在对mysql进行编译安装时,当安装完成后有时会发现不支持innodb存储引擎,这是因为编译安装时缺少支持innodb的参数: 然而,那些参数都是在编译时应选的,对现在的问题也于事无补;下面介绍如...

王爵nice
2015/07/13
0
0
Mysql 分区 分表相关总结之方案选择

[TOC] 引述 前段时间项目需要,一直在研究mysql sharding,看了一些这方面的资料,也亲自实验测试了一些数据。在此,做个概括的笔记,方便以后回顾知识,其实大多是借鉴网络上各位前辈的,然...

-悟空-
2014/12/07
0
12
面试宝典系列-Mysql引擎Innodb和MyISAM区别

InnoDB表的行锁也不是绝对的,假如在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”   两种类型最主要的差别...

suyain
2018/07/18
0
0
MySQL存储引擎——MyISAM与InnoDB区别

  InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的...

一零贰IV
2018/05/30
0
0
MyISAM InnoDB 区别

MyISAM 和 InnoDB 讲解 InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型...

菜到没谱
2012/09/12
0
0

没有更多内容

加载失败,请刷新页面

加载更多

rabbitmq安装教程

RabbitMQ有Windows与Linux版本的,这里先写Windows版本的安装。 以前安装软件总是在百度上找某某安装教程,结果能按照教程安装好的软件真的不多。想起先前以为大牛说的一句话,去官网按照官网...

em_aaron
1分钟前
0
0
Android 贝塞尔曲线实践——波浪式运动

一、波浪效果如下 贝塞尔曲线自定义波浪效果的案例很多,同样方法也很简单,大多数和本案例一样使用二次贝塞尔曲线实现,同样还有一种是PathMeasure的方式,这里我们后续补充,先来看贝塞尔曲...

IamOkay
4分钟前
0
0
Nmap之防火墙/IDS逃逸

选项 解释 -f 报文分段 --mtu 指定偏移大小 -D IP欺骗 -sI 原地址欺骗 --source-port 源端口欺骗 --data-length 指定发包长度 --randomize-hosts 目标主机随机排序 --spoof-mac Mac地址欺骗 ...

Frost729
12分钟前
0
0
带你搭一个SpringBoot+SpringData JPA的环境

不知道大家对SpringBoot和Spring Data JPA了解多少,如果你已经学过Spring和Hibernate的话,那么SpringBoot和SpringData JPA可以分分钟上手的。 其实我在学完SpringBoot和SpringData JPA了之...

java菜分享
17分钟前
0
0
Chocolatey 在Window搭建一个开发环境

在看了(利用 Chocolatey 快速在 Windows 下搭建一个开发环境)后,准备从零开始 一、准备工作 1、用管理员权限启动:powershell,执行错误请参考(PowerShell因为在此系统中禁止执行脚本的解...

近在咫尺远在天涯
30分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部