mysql水平分割怎么查 mysql水平分表后如何查询

mysql表水平分割怎么实现

一条语句没法实现的... create table table2 select * from table1 order by id limit 1,100 create table table3 select * from table1 order by id limit 100,100 create table table4 select * from table1 order by id limit 200,100

目前创新互联建站已为上1000家的企业提供了网站建设、域名、雅安服务器托管、网站托管维护、企业网站设计、白朗网站维护等服务,公司将坚持客户导向、应用为本的策略,正道将秉承"和谐、参与、激情"的文化,与客户和合作伙伴齐心协力一起成长,共同发展。

亿级用户大表如何作水平拆分?

一般面对几千万级的数据,刚开始可能都是把数据放在MySQL的一个单库单表里,但往往这么大量级的数据到后期,数据库查询速度就很慢,因为数据量级太大,导致表的索引很大,树的层级很高,进而导致搜索性能下降,而且能放内存缓存的数据页是比较少。

所以推荐MySQL单表数据量不超过1000w,最好是在500w内,若能控制在100万以内,那是 最佳选择,基本单表100万以内的数据,性能上不会有太大的问题,前提是,只要你建好索引就行,其实保证MySQL高性能通常没什么特别高深的技巧,就是控制数据量不要太大,另外就是保证你的查询用上了索引就行。

针对该问题,就能分库分表,可选择将该用户大表拆分为比如100张表,那么此时几千万 数据瞬间分散到100个表里去,类似

这样的100个表,每个表也就几十万数据而已。

其次,可将这100个表分散到多台数据库服务器上去,此时要分散到几台服务器呢?你要考虑两个点,一个是数据量有多少个GB/TB,一个是针对用户中心的并发压力有多高。

互联网公司对用户中心的压力不会高到离谱,因为一般不会有很多人同时注册/登录或同时修改自己的个人信息,所以并发不是大问题。

数据量层面,一般1亿行数据,大致在1~几GB之间的范围,和具体你一行数据有多少个字段也有关,所以说你几千万的用户数据,往多了说也就几个GB。这点数据量,对服务器存储空间没压力。

综上,此时你完全能给他分配两台数据库服务器,放两个库,然后100张表均匀分散在2台服务器。分的时候,注意指定一个字段来分,一般就指定userid, 根据用户id进行hash后,对表进行取模,路由到一个表里去,这就能让数据均匀分散。

至此搞定用户表的分库分表,只要给系统加上数据库中间件技术,设置好路由规则,即可轻松对2个分库上的100张表crud。平时针对某个用户增删改查,直接对他的userid进行hash,然后对表取模,做个路由,就知道到哪个表里去找这个用户的数据了。

但是这里可能会出现一些问题:用户在登录时,可能不是根据userid登录,而是根据username、手机号,此时你就没有userid了, 怎么知道去哪个表里找这个用户的数据判断是否能登录? 对此。常规方案是建立一个索引映射表,搞个表结构为 (username, userid)的索引映射 表,把username和userid一一映射, 然后针对username再做一次分库分表,把这个索引映射表可拆分为比如100个表分散在两台服务器。然后用户登录时,就能根据username先去索引映射表里查找对应userid,比如对username进行hash,然后取模路由到一个表里去,找到username对应的userid,接着根据userid进行hash再取模,路由到按照userid分库分表的一个表里,找到用户的完整数据。但这种方式会把一次查询转化为两个表的两次查询,先查索引映射表,再根据userid去查具体的数据,性能有损耗,不过有时为解决分库分表问题,也只能用这种方案。

另外就是若公司运营团队里,有用户管理模块,需要对公司的用户按照手机号、住址、年龄、性别、职业等各种条件搜索,这咋办?

没啥好办法,基本上就是要对你的用户数据表进行binlog监听,把你要搜索的所有字段同步到ES,建立好搜索的索引。然后你的运营系统就可以通过ES进行复杂的多条件搜索,ES是适合干这个事儿的,然后定位到一批userid,通过userid回到分库分表环境里去找出具体的用户数据,在页面上展示出来即可。

这就是一套比较常规和完整的分库分表的方案。

mysql数据库要放1亿条信息怎样分表?

mysql数据库对1亿条数据的分表方法设计:

目前针对海量数据的优化有两种方法:

(1)垂直分割

优势:降低高并发情况下,对于表的锁定。

不足:对于单表来说,随着数据库的记录增多,读写压力将进一步增大。

(2)水平分割

如果单表的IO压力大,可以考虑用水平分割,其原理就是通过hash算法,将一张表分为N多页,并通过一个新的表(总表),记录着每个页的的位置。

假如一个门户网站,它的数据库表已经达到了1亿条记录,那么此时如果通过select去查询,必定会效率低下(不做索引的前提下)。为了降低单表的读写IO压力,通过水平分割,将这个表分成10个页,同时生成一个总表,记录各个页的信息,那么假如我查询一条id=100的记录,它不再需要全表扫描,而是通过总表找到该记录在哪个对应的页上,然后再去相应的页做检索,这样就降低了IO压力。

水平拆分

7.1 范围分片(range)

比如说t3表

(1)行数非常多,2000w(1-1000w:sh1 1000w01-2000w:sh2)

(2)访问非常频繁,用户顺序访问较多

cp schema.xml schema.xml.1

vim schema.xml

添加:

table name="t3" dataNode="sh1,sh2" rule="auto-sharding-long" /

vim rule.xml

tableRule name="auto-sharding-long"

rule

columnsid/columns

algorithmrang-long/algorithm

/rule

function name="rang-long"

class="io.mycat.route.function.AutoPartitionByLong"

property name="mapFile"autopartition-long.txt/property

/function

vim autopartition-long.txt

0-10=0

10-20=1

[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "select * from taobao.t3"

[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "select * from taobao.t3"

7.2 取模分片

1%3 1

2%3 2

3%3 0

4%3 1

5%3 2

任何正整数数字和N(正整数)取模,得的值永远都是 0~N-1

id % 分片数量取模

N % 5 = 0-4 idx

取余分片方式:分片键(一个列)与节点数量进行取余,得到余数,将数据写入对应节点

vim schema.xml

table name="t4" dataNode="sh1,sh2" rule="mod-long" /

vim rule.xml

property name="count"2/property

创建测试表:

mysql -S /data/3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat

mycat restart

[root@db01 ~]# mysql -uroot -p123456 -h 10.0.0.52 -P8066 --default-auth=mysql_native_password

use TESTDB

insert into t4(id,name) values(1,'a');

insert into t4(id,name) values(2,'b');

insert into t4(id,name) values(3,'c');

insert into t4(id,name) values(4,'d');

insert into t4(id,name) values(6,'x'),(8,'y'),(10,'z');

分别登录后端节点查询数据

mysql -S /data/3308/mysql.sock -e "select * from taobao.t4"

mysql -S /data/3307/mysql.sock -e "select * from taobao.t4"

7.3 枚举分片 (区域、zone)

t5 表

id name telnum

1 bj 1212

2 sh 22222

3 bj 3333

4 sh 44444

5 bj 5555

sharding-by-intfile

vim schema.xml

table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" /

vim rule.xml

function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap"

property name="mapFile"partition-hash-int.txt/property

property name="type"1/property

/function

vim partition-hash-int.txt 配置:

bj=0

sh=1

DEFAULT_NODE=1

mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat

mycat restart

mysql -uroot -p123456 -h10.0.0.52 -P8066 --default-auth=mysql_native_password

use TESTDB

insert into t5(id,name) values(1,'bj');

insert into t5(id,name) values(2,'sh');

insert into t5(id,name) values(3,'bj');

insert into t5(id,name) values(4,'sh');

insert into t5(id,name) values(5,'tj');

mysql -S /data/3308/mysql.sock -e "select * from taobao.t5"

mysql -S /data/3307/mysql.sock -e "select * from taobao.t5"

7.4 Mycat全局表

a b c d .....

join

t

a

id name age

1 zs 18 sh1

id addr aid

1001 bj 1

1002 sh 2

2 ls 19 sh2

id addr aid

1001 bj 1

1002 sh 2

t

id addr aid

1001 bj 1

1002 sh 2

使用场景:

如果你的业务中有些数据类似于数据字典,比如配置文件的配置,

常用业务的配置或者数据量不大很少变动的表,这些表往往不是特别大,

而且大部分的业务场景都会用到,那么这种表适合于Mycat全局表,无须对数据进行切分,

要在所有的分片上保存一份数据即可,Mycat 在Join操作中,业务表与全局表进行Join聚合会优先选择相同分片内的全局表join,

避免跨库Join,在进行数据插入操作时,mycat将把数据分发到全局表对应的所有分片执行,在进行数据读取时候将会随机获取一个节点读取数据。

vim schema.xml

table name="t_area" primaryKey="id" type="global" dataNode="sh1,sh2" /

mysql -S /data/3307/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat

mycat restart

mysql -uroot -p123456 -h10.0.0.52 -P8066 --default-auth=mysql_native_password

mysql -S /data/3308/mysql.sock -e "select * from taobao.t_area"

mysql -S /data/3307/mysql.sock -e "select * from taobao.t_area"

7.5 E-R分片

a

join

b

on a.xx =b.yy

a

id name

1 a sh1

3 c

2 b sh2

4 d

b

id addr aid

1001 bj 1 sh1

1002 sh 2

1003 tj 3 sh2

1004 wh 4

为了防止跨分片join,可以使用E-R模式

table name="a" dataNode="sh1,sh2" rule="mod-long"

childTable name="b" joinKey="aid" parentKey="id" /

/table

select * from a join b on a.id = b.aid

例子:

mysql -S /data/3308/mysql.sock -e "use taobao;create table a (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock -e "use taobao;create table b (id int not null primary key auto_increment,addr varchar(20) not null ,aid int );"

insert into b(id,addr,aid) values(1001,'bj',1);

insert into b(id,addr,aid) values(1002,'sj',3);

insert into b(id,addr,aid) values(1003,'sd',4);

insert into b(id,addr,aid) values(1004,'we',2);

insert into b(id,addr,aid) values(1005,'er',5);

========

后端数据节点数据分布:

mysql -S /data/3307/mysql.sock -e "select * from taobao.a"

mysql -S /data/3307/mysql.sock -e "select * from taobao.b"

mysql -S /data/3308/mysql.sock -e "select * from taobao.a"

mysql -S /data/3308/mysql.sock -e "select * from taobao.b"

mysql水平分表和分区有什么区别?

一,什么是mysql分表,分区

什么是分表,从表面意思上看呢,就是把一张表分成N多个小表,具体请看mysql分表的3种方法

什么是分区,分区呢就是把一张表的数据分成N多个区块,这些区块可以在同一个磁盘上,也可以在不同的磁盘上,具体请参考mysql分区功能详细介绍,以及实例

二,mysql分表和分区有什么区别呢

1,实现方式上

a),mysql的分表是真正的分表,一张表分成很多表后,每一个小表都是完正的一张表,都对应三个文件,一个.MYD数据文件,.MYI索引文件,.frm表结构文件。

Sql代码

[root@BlackGhost test]# ls |grep user

alluser.MRG

alluser.frm

user1.MYD

user1.MYI

user1.frm

user2.MYD

user2.MYI

user2.frm

简单说明一下,上面的分表呢是利用了merge存储引擎(分表的一种),alluser是总表,下面有二个分表,user1,user2。他们二个都是独立的表,取数据的时候,我们可以通过总表来取。这里总表是没有.MYD,.MYI这二个文件的,也就是说,总表他不是一张表,没有数据,数据都放在分表里面。我们来看看.MRG到底是什么东西

Sql代码

[root@BlackGhost test]# cat alluser.MRG |more

user1

user2

#INSERT_METHOD=LAST

从上面我们可以看出,alluser.MRG里面就存了一些分表的关系,以及插入数据的方式。可以把总表理解成一个外壳,或者是联接池。

b),分区不一样,一张大表进行分区后,他还是一张表,不会变成二张表,但是他存放数据的区块变多了。

Sql代码

[root@BlackGhost test]# ls |grep aa

aa#P#p1.MYD

aa#P#p1.MYI

aa#P#p3.MYD

aa#P#p3.MYI

aa.frm

aa.par

从上面我们可以看出,aa这张表,分为二个区,p1和p3,本来是三个区,被我删了一个区。我们都知道一张表对应三个文件.MYD,.MYI,.frm。分区呢根据一定的规则把数据文件和索引文件进行了分割,还多出了一个.par文件,打开.par文件后你可以看出他记录了,这张表的分区信息,根分表中的.MRG有点像。分区后,还是一张,而不是多张表。

2,数据处理上

a),分表后,数据都是存放在分表里,总表只是一个外壳,存取数据发生在一个一个的分表里面。看下面的例子:

select * from alluser where id='12'表面上看,是对表alluser进行操作的,其实不是的。是对alluser里面的分表进行了操作。

b),分区呢,不存在分表的概念,分区只不过把存放数据的文件分成了许多小块,分区后的表呢,还是一张表。数据处理还是由自己来完成。

3,提高性能上

a),分表后,单表的并发能力提高了,磁盘I/O性能也提高了。并发能力为什么提高了呢,因为查寻一次所花的时间变短了,如果出现高并发的话,总表可以根据不同的查询,将并发压力分到不同的小表里面。磁盘I/O性能怎么搞高了呢,本来一个非常大的.MYD文件现在也分摊到各个小表的.MYD中去了。

b),mysql提出了分区的概念,我觉得就想突破磁盘I/O瓶颈,想提高磁盘的读写能力,来增加mysql性能。

在这一点上,分区和分表的测重点不同,分表重点是存取数据时,如何提高mysql并发能力上;而分区呢,如何突破磁盘的读写能力,从而达到提高mysql性能的目的。

4),实现的难易度上

a),分表的方法有很多,用merge来分表,是最简单的一种方式。这种方式根分区难易度差不多,并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。

b),分区实现是比较简单的,建立分区表,根建平常的表没什么区别,并且对开代码端来说是透明的。

三,mysql分表和分区有什么联系呢

1,都能提高mysql的性高,在高并发状态下都有一个良好的表面。

2,分表和分区不矛盾,可以相互配合的,对于那些大访问量,并且表数据比较多的表,我们可以采取分表和分区结合的方式(如果merge这种分表方式,不能和分区配合的话,可以用其他的分表试),访问量不大,但是表数据很多的表,我们可以采取分区的方式等。

mysql水平分表和分区有什么区别

分区

分区就是把一个数据表的文件和索引分散存储在不同的物理文件中。

mysql支持的分区类型包括Range、List、Hash、Key,其中Range比较常用:

RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。

LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

分表

分表和分区类似,区别是,分区是把一个逻辑表文件分成几个物理文件后进行存储,而分表则是把原先的一个表分成几个表。进行分表查询时可以通过union或者视图。

分表又分垂直分割和水平分割,其中水平分分割最为常用。水平分割通常是指切分到另外一个数据库或表中 。

当前文章:mysql水平分割怎么查 mysql水平分表后如何查询
标题来源:https://www.cdcxhl.com/article22/hjpdcc.html

成都网站建设公司_创新互联,为您提供网站维护静态网站全网营销推广品牌网站制作动态网站Google

广告

声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联

h5响应式网站建设