MySQL分区分表

博文大纲:数据数据越来越大,随之而来的是单个表中数据太多。以至于查询速度变慢,而且由于表的锁机制导致应用操作也搜到严重影响,出现了数据库性能瓶颈。
mysql中有一种机制是表锁定和行锁定,是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作,必须等我对表操作完才行。行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。当出现这种情况时,我们可以考虑分表或分区。分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。这些表可以分布在同一块磁盘上,也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的表名,然后去操作它。
将单个数据库表进行拆分,拆分成多个数据表,然后用户访问的时候,根据一定的算法(如用hash的方式,也可以用求余(取模)的方式),让用户访问不同的表,这样数据分散到多个数据表中,减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就在于此,减小数据库的负担,缩短查询时间。Mysql分表分为垂直切分和水平切分,具体区别如下:
垂直切分是指数据表列的拆分,把一张列比较多的表拆分为多张表 通常我们按以下原则进行垂直拆分: 把不常用的字段单独放在一张表; 把text,blob(binary large object,二进制大对象)等大字段拆分出来放在附表中;
经常组合查询的列放在一张表中; 垂直拆分更多时候就应该在数据表设计之初就执行的步骤,然后查询的时候用join关键起来即可。水平拆分是指数据表行的拆分,把一张的表的数据拆成多张表来存放。 水平拆分原则,通常情况下,我们使用hash、取模等方式来进行表的拆分 比如一张有400W的用户表users,为提高其查询效率我们把其分成4张表users1,users2,users3,users4 通过用ID取模的方法把数据分散到四张表内Id%4= [0,1,2,3] 然后查询,更新,删除也是通过取模的方法来查询 部分业务逻辑也可以通过地区,年份等字段来进行归档拆分; 进行拆分后的表,这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询。注:只有myisam引擎的原表才可以利用merge存储引擎实现分表。merge分表,分为主表和子表,主表类似于一个壳子,逻辑上封装了子表,实际上数据都是存储在子表中的。 我们可以通过主表插入和查询数据,如果清楚分表规律,也可以直接操作子表。举个栗子:分表注意事项:注:在上面创建主表时,指定的“insert_method=last”有三个可选参数,分别是:last:表示插入到最后一张表里面;first:表示插入到第一张表里面;NO:表示该表不能做任何写入操作,只作为查询使用。第一个子表部分数据如下:第二个子表部分数据如下:主表部分查询的部分数据如下:数据总行数如下:
注意:总表只是一个外壳,存取数据发生在一个一个的子表里面。 每个子表都有自已独立的相关表文件,而主表只是一个壳,并没有完整的相关表文件,当确定主表中可以查到的数据和分表之前查到的数据完全一致时,就可以将原来的表删除了,之后对表的读写操作,都可以对分表后的主表进行。上面三个表对应的本地文件如下:可以看出,能够查询到所有数据的主表的本地数据文件是非常小的,这也验证了,数据并没有存在这个主表中。可以看出,新增的两条数据都插入在了第二张表中,因为在创建主表的时候,指定的“insert_method”是last,也就是所有插入数据的操作都是对最后一张表里进行的,可以通过alter指令修改插入方法,如下:修改插入方法后,再自行对表进行插入数据的操作,可以发现所有的数据都写入了第一个表(我开发云主机域名这里插入了四条数据),查看如下:上面是新增了四条数据,可以发现都插入到了第一张表。若将插入方法修改为no,则表示这个表不能再插入任何数据,如下:。分区和分表相似,都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表,而分区是将数据分段划分在多个位置存放,分区后,表还是一张表,但数据散列到多个位置了。app读写的时候操作的还是表名字,db自动去组织分区的数据。分区主要有以下两种形式:
水平分区:这种形式分区是对表的行进行分区,所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。
举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。
垂直分区:这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应的行。
举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。MySQL 5.6之前,使用下面的参数查看当前配置是否支持分区(如果为yes则表示支持分区):在5.6及以后采用以下方式查看:返回的结果中,有以下字段(如果status列为“ACTIVE”,则表示支持分区):注:在上面创建的表中,当id列的值小于3将会插入到p0分区,大于3小于6的记录将会插入到p1分区,以此类推,所有id值大于12的记录都会插入到p4分区。可以看到数据是被分散存到不同的文件中的,本地的文件名都是“user#P#p0…”命名的,其中p0是自定义的分区名。1.添加分区:注意:由于在创建表的时候,指定的最后一个分区range是maxvalue,所以是无法直接增加分区的,如下:大意是:MAXVALUE只能在最后一个分区定义中使用但也不可以将最后定义了maxvalue的分区直接删除,因为删除分区的话,分区中的数据也会丢失,所以,如果需要新增分区的正确做法,应该是先合并分区,再新增分区,这样才可以保证数据的完整性,如下:上述命令的作用就是将最后一个分区分为两个分区,一个是自己所需要的分区,最后一个分区还是maxvalue(也必须是maxvalue),这样就完成了添加分区。本地表文件如下:查询新增分区中的数据如下:2.合并分区将p0、p1、p2、p3四个分区合并为p02:可以看到p02将整合了p0,p1,p2,p3三个分区的数据,如下:本地文件如下:注意:分区被删除后,分区中的数据也将被删除,删除分区p02的表中所有数据如下:———————— 本文至此结束,感谢阅读 ————————

相关推荐: SaltStack实战之配置管理-Jinja2模板

学习SaltStack1. Salt yaml配置文件使用Jinja2模板介绍1.1 File状态使用template参数1.2 模板文件里面变量使用{{ 名称 }}1.3 变量列表2. 实战应用jinja2官方网站:http://jinja.pocoo.or…

免责声明:本站发布的图片视频文字,以转载和分享为主,文章观点不代表本站立场,本站不承担相关法律责任;如果涉及侵权请联系邮箱:360163164@qq.com举报,并提供相关证据,经查实将立刻删除涉嫌侵权内容。

(0)
打赏 微信扫一扫 微信扫一扫
上一篇 06/06 19:46
下一篇 06/06 19:46

相关推荐

发表评论

您的电子邮箱地址不会被公开。