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个文件，这样检索起来也会变的很慢。

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