mysql分表经验总结详解数据库

一、为什么要分表?

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。
根据个人经验,mysql执行一个sql的过程如下:
1、接收到sql;
2、把sql放到排队队列中;
3、执行sql;
4、返回执行结果。
在这个执行过程中最花时间在什么地方呢?第一,是排队等待的时间,第二,sql的执行时间。其实这二个是一回事,等待的同时,肯定有sql在执行。所以我们要缩短sql的执行时间。

mysql中有一种机制是表锁定和行锁定,为什么要出现这种机制,是为了保证数据的完整性,我举个例子来说吧,如果有二个sql都要修改同一张表的同一条数据,这个时候怎么办呢,是不是二个sql都可以同时修改这条数据呢?很显然mysql对这种情况的处理是,一种是表锁定(myisam存储引擎),一个是行锁定(innodb存储引擎)。表锁定表示你们都不能对这张表进行操作,必须等我对表操作完才行。行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。如果数据太多,一次执行的时间太长,等待的时间就越长,这也是我们为什么要分表的原因。

二、分表方案

具体的分表方案有很多,这里只介绍我使用的方案。车联网项目里,车辆轨迹的数据很大,所以将其分为若干个表,那事先建100个这样的表,trajectory_00,trajectory_01,trajectory_02……….trajectory_98,trajectory_99.然后根据GPS设备的ID来判断这个设备的轨迹数据放到哪张表里面(把设备id和这100张表建立关联,使得所有设备平均分配到100张表里,我用的是java里的hashcode),然后写个方法根据传入的设备id获得表名。

/**
* 根据设备id获取表名
* @param deviceid
* @return
*/
public static String getTableByDeviceId(String deviceid) {
return “trajectory_”+(Math.abs(deviceid.hashCode())+””).substring(0, 2);
}

优点:避免一张表出现几百万条数据,缩短了一条sql的执行时间。
缺点:①当一种规则确定时,打破这条规则会很麻烦,上面的例子中我用的hash算法是crc32,如果我现在不想用这个算法了,改用md5后,会使同一个设备的数据被存储到不同的表中,这样数据乱套了。扩展性很差。
②当要同时获取两个处在不同表里的设备数据时,要union一下,稍微麻烦点。

三、总结一下
现在项目运行下来,平均每个轨迹表trajectory有100W条数据,每张表里大约有100个设备,现在执行sql查询体验良好。其实优先应该考虑的优化方案是建合适的索引,其次才是分表,或者分库。

做什么事都有一个度,超过个度就过变得很差,不要一味的分表,分出来1000表,mysql的存储归根到底还以文件的形势存在硬盘上面,一张表对应三个文件,1000个分表就是对应3000个文件,这样检索起来也会变的很慢。

其实上面介绍的是水平分表的实施方法,还存在另一种方法叫做:垂直分表。

原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/4783.html

(0)
上一篇 2021年7月16日
下一篇 2021年7月16日

相关推荐

发表回复

登录后才能评论