请点击输入图片描述(最多18字)
经常混迹于技术社区,频繁看到这个题目,今天干脆在自己博客重复一遍解决办法:
针对mysql,sqlserver等关系型数据库单表数据过大的处理方式
如果不是阿里云的分布式数据库 DRDS 那种多机器集群方案的话: 先考虑表分区 ;然后考虑分表 ;然后考虑分库。
这个题目是我所经历过的,我做的是GPS应用,早期版本就是选用的关系型数据库Sql Server。当时我选取的方案就是第一种:表分区。 表分区的优势是,如果表结构合理,可以不涉及到程序修改。也就是说,对程序来讲依然是单表读写的效果!
所有轨迹数据存入到一个巨大的表里。有多大呢?
最大存储量超过10亿行。具体数值应该是12亿多点,由于系统设计为只存储30天轨迹,所以线上期间最大存储只到这个数,再后来采用云架构,上云替换成非关系性数据库,获得了更高的写入性能和存储压缩能力。 每日写入量就超过1500万行。上下班交通高峰时候每秒写入量平均超过500行。也就是500iops,距离系统设计的压测指标3000还有一大截这张大型单表设计要点:(一个聚集索引用于写入,一个联合索引用于查询,没有主键,使用表分区)
明确主键用途:
真的需要查询单行数据时候才需要主键!
我采用无主键设计,用于避免写入时候浪费维护插入数据的性能。最早使用聚集的类似自增的id主键,压测写入超过5亿行的时候,写入性能缩减一半
准确适用聚集:
写入的数据在硬盘物理顺序上是追加,而不是插入!
我把时间戳字段设置为聚集索引,用于聚集写入目的设计。保证硬盘上的物理写入顺序,不浪费性能用于插入数据
职责足够单一:
用于精准索引!
使用时间+设备联合索引,保证这张表只有一个查询用途。保证系统只有一种查询目的:按照设备号,查询一个时间段的数据。
精确的表分区:
要求查询时候限定最大量或者最大取值范围!
按天进行表分区,实现大数据量下的高效查询。这里是本文重点,按照聚集索引进行,可以让目标数据局限在更小的范围进行,虽然单表数据上亿,但是查询基本上只在某一天的的几千万里进行索引查询
每张表会有各自的特点,不可生搬硬套,总结下我这张表的特点:
只增,不删,不改!
关于不删除中:每天使用作业删除超过30天的那个分区数据除外,因为要清空旧的表分区,腾出新的表分区!
只有一个业务查询:只按照设备编码查询某个时间段
只有一个运维删除:删除旧的分区数据
这张表,是我技术生涯中进步的一个大阶梯,让我我体会到了系统架构的意义。
虽然我的这张举行表看似只有4个关键点,但是这四个非常精准的关键点设计,耗费了我一个月之久!正是这么足够精准的表结构设计,才撑起了后来压测并发量超过3000的并发写入量!压测的指标跟数据库所在的硬盘有直接关系,当时选取的硬盘是4块10000转的SAS盘做了Raid10的环境
关于后来为什么没有更高的实际应用数值,是因为系统后来改版为云架构,使用了阿里云,更改为写入性能更高的非关系型数
您好,提问者:创建表的默认路径是:C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data
MySQL数据库的导入,有两种方法:1)
先导出数据库SQL脚本,再导入;
2)
直接拷贝数据库目录和文件。
在不同 *** 作系统或MySQL版本情况下,直接拷贝文件的方法可能会有不兼容的情况发生。
所以一般推荐用SQL脚本形式导入。下面分别介绍两种方法。
2.
方法一
SQL脚本形式
*** 作步骤如下:
2.1.
导出SQL脚本
在原数据库服务器上,可以用phpMyAdmin工具,或者mysqldump命令行,导出SQL脚本。
2.1.1
用phpMyAdmin工具
导出选项中,选择导出“结构”和“数据”,不要添加“DROP
DATABASE”和“DROP
TABLE”选项。
选中“另存为文件”选项,如果数据比较多,可以选中“gzipped”选项。
将导出的SQL文件保存下来。
2.1.2
用mysqldump命令行
命令格式
mysqldump
-u用户名
-p
数据库名
>
数据库名.sql
范例:
mysqldump
-uroot
-p
abc
>
abc.sql
(导出数据库abc到abc.sql文件)
提示输入密码时,输入该数据库用户名的密码。
2.2.
创建空的数据库
通过主控界面/控制面板,创建一个数据库。假设数据库名为abc,数据库全权用户为abc_f。
2.3.
将SQL脚本导入执行
同样是两种方法,一种用phpMyAdmin(mysql数据库管理)工具,或者mysql命令行。
2.3.1
用phpMyAdmin工具
从控制面板,选择创建的空数据库,点“管理”,进入管理工具页面。
在"SQL"菜单中,浏览选择刚才导出的SQL文件,点击“执行”以上载并执行。
注意:phpMyAdmin对上载的文件大小有限制,php本身对上载文件大小也有限制,如果原始sql文件
比较大,可以先用gzip对它进行压缩,对于sql文件这样的文本文件,可获得1:5或更高的压缩率。
gzip使用方法:
#
gzip
xxxxx.sql
得到
xxxxx.sql.gz文件。
提示输入密码时,输入该数据库用户名的密码。
3
直接拷贝
如果数据库比较大,可以考虑用直接拷贝的方法,但不同版本和 *** 作系统之间可能不兼容,要慎用。
3.1
准备原始文件
用tar打包为一个文件
3.2
创建空数据库
3.3
解压
在临时目录中解压,如:
cd
/tmp
tar
zxf
mydb.tar.gz
3.4
拷贝
将解压后的数据库文件拷贝到相关目录
cd
mydb/
cp
*
/var/lib/mysql/mydb/
对于FreeBSD:
cp
*
/var/db/mysql/mydb/
3.5
权限设置
将拷贝过去的文件的属主改为mysql:mysql,权限改为660
chown
mysql:mysql
/var/lib/mysql/mydb/*
chmod
660
/var/lib/mysql/mydb/*
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)