mysql千万级别的数据库优化

khler 2012-11-26 12:00:59

我们现场生产数据一个月产生千万条级别的数据，时间长了查询就很慢了，有什么优化办法？
1、系统监控多台设备，每台设备每天产生大量数据，放在一张实时数据表里；
2、页面每天统计当天的数据，显示一个统计结果；
3、页面调用的是存储过程实现统计计算，显示计算结果。

现在数据库的这张表已经有9千多万条数据了，页面显示变得非常的慢！
考虑过几个分表策略：
1、按每个设备一张实时数据表，这样就不用所有设备放在一张表里了，这样可以大幅缩小实时表的数据量，但是时间一长，比如两年以上，任然存在上述问题；
2、按每月一张实时表横向分表，这样不存在数据量累加的问题，但是历史查询会很麻烦。

各位元芳，你们怎么看？
如上方式有何看法？或者有什么新想法？

...全文

1139 32 打赏收藏转发到动态举报

写回复

用AI写文章

32 条回复

切换为时间正序

请发表友善的回复…

发表回复

khler 2014-06-25

打赏
举报

好多大牛啊，学习了！真的是得益匪浅，再次感谢各位！！！

little_sand 2013-02-04

打赏
举报

学习了

飞翔地猴子 2012-12-27

打赏
举报

引用 27 楼 prolee750607 的回复:

引用 25 楼 kissgxd 的回复:如果有条件的话建议还是做mysql集群吧，读写分离个人测试过，如果不能把被查询表合理地分布在更多的机器上，使查询真正的分布，查询集群比单机并不会快，而且再加上复制所带来的消耗，其实会更慢。 23楼提到的列式数据库如果是放在内存中的MonetDB、VectorWise这种，对查询来说是的确很快，当然写入的确比行式慢（我测试导入一……

额，班门弄斧了

ACMAIN_CHM 2012-12-27

打赏
举报

和银行系统一样，每天上千万条交易日志。一般的做法是，把一定时间以上的记录ARCHIVE到历史表中，毕竟你的90%的查询仅是在最近的一定周期内，很少会去查询一年前的记录。（这个时间自己根据实际应用而定，有些系统是一周以上的数据就很少用了) 然后对历史记录做数据仓库类似的整理，即事先把每天的数据合并整理，以加快历史报表的速度。比如你可以先每年的交易额统计在一张 dailyTransaction表中 select date,sum() ...这样当查询历史记录时直接从这个表中抽取。只有当真的需要去查历史明细时才去看 archive表中的历史详细记录。

飞翔地猴子 2012-12-26

打赏
举报

如果有条件的话建议还是做mysql集群吧，读写分离

代码誊写工 2012-12-26

打赏
举报

引用 25 楼 kissgxd 的回复:

如果有条件的话建议还是做mysql集群吧，读写分离

个人测试过，如果不能把被查询表合理地分布在更多的机器上，使查询真正的分布，查询集群比单机并不会快，而且再加上复制所带来的消耗，其实会更慢。 23楼提到的列式数据库如果是放在内存中的MonetDB、VectorWise这种，对查询来说是的确很快，当然写入的确比行式慢（我测试导入一个1Gb左右的CSV文件，MySQL内存表需要20秒，MonetDB需要70秒）。创建了合理索引的行式数据库并不比列式慢。目前我觉得列式唯一的好处就是不用自己创建索引。个人意见还是优化索引，对经常被查询的数据采用内存引擎冗余加载，对于经常被运算查询的定时汇总到冗余表中。如果数据量真的非常大的时候再考虑集群甚至hadoopdb、Hbase这一类。

lemon柠檬 2012-12-26

打赏
举报

坐看云起时，等待结果。

代码誊写工 2012-12-25

打赏
举报

引用 23 楼 zuoxingyu2012 的回复:

分区不一定能够带来性能的改善。楼主可以试试infobright,对于各种统计很不错。

贵啊，免费版功能及其有限，infinidb免费版功能强点儿，列式数据库试用了一段时间，但由于其写入大量数据所带来的延时远远超过行式，所以一直没有正式用到项目里。

珠海-天堂 2012-12-25

打赏
举报

分区不一定能够带来性能的改善。楼主可以试试infobright,对于各种统计很不错。

代码誊写工 2012-12-24

打赏
举报

关于这个max_binlog_stmt_cache_size 参数，我最近发现如果你禁用了bin log，其实没有必要设置的。而且如果你不是做主从复制的服务器，强烈建议你禁用bin-log，这可以大大增加你的速度。禁用方法; 用root登录mysql 执行 reset master; 清空bin log,我的bin log超大，居然占有4个T的空间。然后编辑my.cnf 用#注释log-bin=mysql-bin和binlog_format=mixed #log-bin=mysql-bin #binlog_format=mixed

代码誊写工 2012-12-21

打赏
举报

如果你的内存够大，在linux中把/etc/sysctl中加上vm.swappiness=0（如果不能重启就echo 0 >/proc/sys/vm/swappiness），禁用换页，这样就能有效利用内存。

代码誊写工 2012-12-21

打赏
举报

如果可能，可以把热数据（经常查询的数据，比如当天的数据）加载到memory引擎中。如果你的查询每次都在变化，那就把query_cache_size设置到一个比较小的值，这个查询缓存对经常变化的数据没有多大意义。另外还有些参数 query_cache_type = 2 max_heap_table_size = 512M tmp_table_size = 256M bulk_insert_buffer_size = 1G myisam_max_sort_file_size = 10G default_storage_engine = MyISAM max_binlog_stmt_cache_size = 20G 其中： query_cache_type设置为2表示如果没有明确指定（select SQL_CACHE * from XXXX where XXXX），就不缓存查询结果。 max_heap_table_size 是每个内存表的最大值 tmp_table_size 是每个临时表的最大值 bulk_insert_buffer_size 因为我的数据是用load data infile导入，所以需要设置（导入时要alter table xxxx disable keys完成后再alter table xxxx enable keys，否则慢得无法想象） myisam_max_sort_file_size 重建MyISAM索引(在REPAIR TABLE、ALTER TABLE或LOAD DATA INFILE过程中)时，允许MySQL使用的临时文件的最大空间大小。（这个是网上抄来的） max_binlog_stmt_cache_size 这个值是因为load data导入超大的文件（10G以上）必须要加大的。如果你的数据库不是事务型的，建议用MyISAM作为默认引擎，查询会快点。如果性能对项目很重要，并且你不是在用mariadb（我用intel编译器编译从来没有成功过），可以考虑用intel重新编译mysql,并加入tmalloc(Google的一个项目，更高效地分配内存，但一定重新mysql才能体现效率)，最后不得已还可以考虑redis、memcache等手段。

shikou 2012-12-21

打赏
举报

进来学习学习。。。。

代码誊写工 2012-12-20

打赏
举报

引用 15 楼 khler 的回复:

引用 13 楼 prolee750607 的回复: 数据量不算大啊，不用自己分表，用patitoion range，如果需要再hash应该足以解决问题了，我的项目里一个小时就上亿记录，也无非是自己先按分析业务垂直分区，再按时间水平分区（按天），再在这个基础上用patition。我用Range分区，貌似速度没啥改观，据说分区后需要把不需要的数据卸载？如何操作？？？

分区并不会比创建了索引的表快很多，你需要修改my.cnf,优化你的索引key_buffer_size = 16G这个值由你的内存大小决定，我的服务器48G，所以设置成16G（内存的三分之一），另外分区和你的查询条件有很大关系，如果你的查询并不是把partition key（例如时间）作为where的主要条件，分区并没有什么作用，因为我的项目是报文分析，所有报文都有时间戳，而且我还把时间戳作为索引，所以分区取得的效果比较大。当然，最主要还是要从你的查询逻辑入手。