mysql千万级别的数据库优化

Question

数据量不算大啊，不用自己分表，用patitoion range，如果需要再hash应该足以解决问题了，我的项目里一个小时就上亿记录，也无非是自己先按分析业务垂直分区，再按时间水平分区（按天），再在这个基础上用patition。

Answer 1

我用Range分区，貌似速度没啥改观，据说分区后需要把不需要的数据卸载？如何操作？？？

Answer 2

等待结果，我的数据库是Bigtable

Answer 3

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

Answer 4

如果可能，可以把热数据（经常查询的数据，比如当天的数据）加载到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等手段。

Answer 5

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

Answer 6

 关于这个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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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