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
mysql相关文章
- MySQL数据库为什么索引使用B+树而不是B树
- MySQL 性能调优的10个方法详解数据库
- MySQL: 将日期时间戳转换为人类可读格式(mysql日期时间戳转换)
- MySQL数据库连接指南:快速开始(怎么连接mysql数据库)
- 份MySQL数据库热备份:确保数据安全的有效方式(mysql数据库热备)
- MySQL命令行下的出色之处(mysql命令行版本)
- 用MySQL存储图像:轻松实现图片上传(图片上传到mysql)
- MySQL 主从复制:实现大规模集群数据库的完美分担(mysql备与从)
- 「MySQL 数据库面试题大揭秘」(mysql数据库面试题)
- Ubuntu下编译MySQL,轻松搭建自己的数据库服务器(ubuntu编译mysql)
- MySQL在分布式数据库上的极致表现(mysql的分布式数据库)
- MySQL索引数量与性能优化(mysql索引个数)
- MySQL数据库升级:从旧库到新库的换算(mysql换库)
- 妙用MySQL:快速优化数据库性能(优化mysql数据库方法)
- MySQL数据库修复:有效恢复工具(mysql数据库修复工具)
- C语言从MySQL数据库中取值(c 从mysql取值)
- 便捷好用的个人办公利器MySQL应用浅析(mysql个人办公使用)
- MySQL计算两表字段差值实现数据分析(mysql 两表字段相减)
- Ajax实现从MySQL读取数据(ajax 读取mysql)
- MySQL删除完整表操作详解(mysql中删除整个表)
- MySQL数据库是否需要联网解决您的疑问(mysql一定要联网吗)
- MySQL数据库中一个库占用多少空间(mysql一个库多大)
- PHP 中使用 MySQLexec 函数进行数据库操作(mysql_exec)
- MySQL数据库禁止空值输入(mysql不允许空值)
- 选择正确的MySQL版本,下载更好的数据库管理工具(mysql下载哪个版本好)
- MySQL批量执行问题解决方案(mysql不能批量执行)