mysql分区和表哪个好?
一、什么是mysql子表和分区
什么是子表?表面上是把一个表分成N个以上的子表。详见mysql子表的三种方法。
什么是分区?分区就是把一个表的数据分成N个块,可以在同一个磁盘上,也可以在不同的磁盘上。
首先说一下为什么要分表。
当一条数据达到百万时,查询一次就要花你更多的时间。如果有联合查询,我觉得可能会死在那里。表拆分的目的是减轻数据库的负担,缩短查询时间。
根据个人经验,mysql执行一条sql的过程如下:
1,sql收到;2.将sql放入队列中;3、执行sql4.返回执行结果。在这个实施过程中,你在哪里花的时间最多?第一,排队等待时间,第二,sql的执行时间。其实这两个是一回事。等待时,必须有sql在执行。所以我们需要缩短sql的执行时间。
mysql中有一种机制就是表锁和行锁。为什么会出现这种机制?是为了保证数据的完整性。我举个例子。如果两个sql都要修改同一个表中相同的数据,这时候该怎么办?两个sql可以同时修改这些数据吗?显然,mysql对这种情况的处理是这样的:一个是表锁(myisam存储引擎),一个是行锁(innodb存储引擎)。表锁意味着你们谁也不能操作这个表,必须等我操作完这个表。行锁定也是如此。其他sql必须等我完成对这个数据的操作,才能对这个数据进行操作。如果数据太多,执行时间太长,等待时间会更长,这就是为什么我们需要划分表。
二、分表
1,做mysql集群,比如:用mysql集群,mysql代理,mysql复制,drdb等等。
有人会问mysql cluster,根表有什么关系?虽然不是实际意义上的子表,但是起到了子表的作用。作为一个集群的意义是什么?减轻一个数据库的负担,说白了就是减少sql队列中sql的数量。例如,有10个sql请求。如果将它们放在数据库服务器的队列中,它们将不得不等待很长时间。如果将这10个sql请求分布到五个数据库服务器的队列中,那么一个数据库服务器的队列中只有两个。这是不是大大减少了等待时间?已经很明显了。所以我把它列在子表里,我做过一些mysql集群:
linux mysql代理的安装、配置和读写分离
mysql复制的安装和配置,以及数据同步。
优点:扩展性好,多表分割后没有复杂的操作(php代码)。
缺点:单个表的数据量没有变化,一次操作花费的时间还是那么多,硬件开销大。
2.对数据量大,访问频繁的表进行预估计,分成几个表。
这种估计还不错,论坛发布的帖子列表时间长了会很大,几十万或者几百万都有可能。聊天室里的信息表,几十个人一起聊一晚上,需要很长时间,这个表的数据一定很大。像这样的情况还有很多。所以这种可以预测的大数据表,我们会提前分n个表,这个n是什么,要看实际情况。以聊天信息表为例:
我提前建了100个这样的表,message _ 00,message _ 01,message _ 02..........消息_ 98,消息_ 99。然后根据用户的ID,我们就可以判断用户的聊天信息放在哪个表中。可以通过哈希得到,也可以通过余数得到。方法有很多,大家想一想。下面使用哈希方法来获取表名:
查看副本打印?
& lt?服务器端编程语言(Professional Hypertext Preprocessor的缩写)
函数get_hash_table($table,$userid) {
$ str = crc32($ userid);
if($str & lt;0){
$hash = "0 "。substr(abs($str),0,1);
}否则{
$hash = substr($str,0,2);
}
返回$table。"_".$ hash
}
echo get_hash_table('message ',' user 18991 ');//结果是message_10。
echo get_hash_table('message ',' user 34523 ');//结果是message_13。
& gt
说明上面的方法告诉我们,user18991的消息记录在message_10表中,user34523的消息记录在message_13表中。阅读时,只需从各自的表格中阅读即可。
优点:避免了表中的数百万数据,缩短了sql的执行时间。
缺点:当一个规则确定后,打破这个规则会很麻烦。在上面的例子中,我使用的哈希算法是crc32。如果我现在不想用这个算法,改用md5后,同一个用户的消息会存储在不同的表中,数据会混淆。扩展性非常差。
3.使用合并存储引擎实现表拆分。
我觉得这种方法比较适合那些没有提前考虑,但是已经过得很好,数据查询比较慢的人。这时候如果把现有的大数据表拆分出来是痛苦的,最痛苦的就是改代码,因为程序里的sql语句已经写好了,现在一个表要拆分成几十个甚至上百个表。这个sql语句要重写吗?比如我很喜欢举一个小孩。
mysql & gt展示引擎;你会发现mrg_myisam实际上是merge。
查看副本打印?
mysql & gt如果不存在,则创建表` user1 `(
-& gt;` id ' int(11)NOT NULL AUTO _ INCREMENT,
-& gt;` name ' varchar(50)默认为NULL,
-& gt;` sex` int(1) NOT NULL默认为' 0 ',
-& gt;主键(` id `)
-& gt;)ENGINE=MyISAM默认CHARSET = utf8 AUTO _ INCREMENT = 1;
查询正常,0行受影响(0.05秒)
mysql & gt如果不存在,则创建表“用户2 ”(
-& gt;` id ' int(11)NOT NULL AUTO _ INCREMENT,
-& gt;` name ' varchar(50)默认为NULL,
-& gt;` sex` int(1) NOT NULL默认为' 0 ',
-& gt;主键(` id `)
-& gt;)ENGINE=MyISAM默认CHARSET = utf8 AUTO _ INCREMENT = 1;
查询正常,0行受影响(0.01秒)
mysql & gt插入到` user1` (`name`, `sex `)值中('张颖',0);
查询正常,1行受影响(0.00秒)
mysql & gt插入' user2` (`name`, `sex `)值(' tank ',1);
查询正常,1行受影响(0.00秒)
mysql & gt如果不存在,则创建表` alluser `(
-& gt;` id ' int(11)NOT NULL AUTO _ INCREMENT,
-& gt;` name ' varchar(50)默认为NULL,
-& gt;` sex` int(1) NOT NULL默认为' 0 ',
-& gt;索引(id)
-& gt;)TYPE=MERGE UNION=(user1,user 2)INSERT _ METHOD = LAST AUTO _ INCREMENT = 1;
查询正常,0行受影响,1警告(0.00秒)
mysql & gt从所有用户中选择id、姓名、性别。
+ - + - + - +
身份证|姓名|性别|
+ - + - + - +
| 1 |张颖| 0
| 1 |坦克| 1 |坦克
+ - + - + - +
集合中的2行(0.00秒)
mysql & gt插入到` alluser` (`name`, `sex `)值中(' tank2 ',0);
查询正常,1行受影响(0.00秒)
mysql & gt从用户2中选择id、姓名、性别
-& gt;;
+ - + - + - +
身份证|姓名|性别|
+ - + - + - +
| 1 |坦克| 1 |坦克
| 2 |坦克2 | 0 |
+ - + - + - +