MySQL处理记录数的极限是多少

[dongsheng]

最近处理一个大学的课程管理系统数据库，他们的一个日志表达到2000万行记录的水平，已经有了严重的性能问题，mysql有很多死掉的查询，请问MySQL何时会在记录数上出现瓶颈，有什么合适的解决办法吗？（这里不可能使用NoSQL）

[flytwokites]

我想是索引没有用好，打开mysql的慢查询功能看看哪些sql有性能问题。

[sophon]

可以考虑做下拆表

[dongsheng]

@flytwokites 索引没问题，第一件事就是检查了索引

[virushuo]

我听说过的最大表里面有几个亿，每天增量还在百万级别

[dongsheng]

@virushuo 这种量级的数据库用的是哪种引擎？INNO DB能抗得住吗？

公司有个非常繁忙的网站是把数据库放到内存里了，这才抗得住频繁的查询。

[muxi]

2000w而已，不至于出现什么瓶颈，我所用过的表多的会有10亿+的记录，像你这种日志表，只要有足够的内存（我一般给MySQL 8G以上的内存）、查询能使用主键或者索引应该不会出现什么问题，或许你换成Innodb引擎会更快一点，而且大学的课程管理系统，应该不会有很大的并发量，超级大的表，一般慢在磁盘和CPU上，或许你可以换个好点的服务器，如果你熟悉MySQL的话， 最新的5.5版本应该能显著提升你系统的性能

[liwei]

@dongsheng 我觉得这个不是MySQL处理数据的极限限制，而是受你查询复杂程度的限制。单表带索引的查询，在上亿的数据量上还是很快的

[dongsheng]

@muxi 现在最大的问题是日志表的查询非常非常频繁，因为这个系统非常依赖日志表显示来显示课程的中最近的活动，大量的查询都卡死在这里，导致很多页面都打不开。数据量可能跟你说的10亿没法比，但这个大学对该系统依赖非常高，所有的授课和考试都通过这个系统进行，并发很大。

可能真的让他们升级硬件或者升级MySQL（现在是5.0），我个人和他们都不倾向升级软件，数据量极大，可能得折腾一星期，还可能耽误授课计划。还有个烦人的问题是升级mysql还要升级linux，mysql5.5用的是新glibc，升级系统要走的流程就更多了，烦。

多谢你的建议。

[dongsheng]

@liwei 全是简单查询，所以我才怀疑是不是到极限了

[Livid]

@dongsheng 如果不涉及什么特别敏感的信息的话，你可以把那张表的结构定义（包括所有的索引的定义）发到 gist 然后分享给大家看看。

[e6nian]

大学的选课系统都是悲剧。
某些得体的老湿基本上都是被"秒杀"

[dongsheng]

@Livid 谢谢

Gist: https://gist.github.com/907013

Indexes: http://cl.ly/0C2X3s3U2Z213J0e3b0Z

这个表完全没有join查询，都是直来直去的

[TheOnly92]

主要是做何种查询？

[dongsheng]

@TheOnly92
比如
SELECT COUNT(*) FROM log;
SELECT * FROM log WHERE course=1 AND module=2 AND action="view";

[Livid]

@dongsheng 先说一个我觉得奇怪的地方：

bigint(10)

你们的应用中会出现超过 32 位范围的 64 位整数么？如果不会的话，把所有的 bigint 改成 int 可以节约内存。

另外，bigint 的长度应该是 20 而不是 10。

[TheOnly92]

这个时候做些 alter table 会卡掉整个系统吧

那两个查询的执行时间是多少？

[Livid]

@TheOnly92 @dongsheng 嗯，如果在目前这样的数据规模下做 alter 确实会锁表至少一个小时以上，所以一定慎重。

[flytwokites]

象
SELECT * FROM log WHERE course=1 AND module=2 AND action="view";
这类语句应该做个组合索引。

[dongsheng]

@flytwokites 这个做了，你看我发的截图。

[dongsheng]

@TheOnly92 这个我用mysql客户端执行是瞬间完成的，问题是当并发大的时候，很多这种查询会死掉，用show processlist会看到很多SELECT COUNT(*) FROM log; 锁死在那里了。

[TheOnly92]

这个难道不能存入缓存吗？看到很多查询锁死，不表示该查询的问题，而是之前的查询还没完，接下来的查询都不能顺利进行。所以还得找出最根本的问题。

[dongsheng]

@Livid

现在这个表是myisam的，换成innodb会不会有后遗症？
我严重怀疑mysql不争气，同样的系统，有人用postgresql做存储，大致一个级别的网站，没有出现log表锁死的问题，不过它的服务器要稍好些。

[liwei]

@dongsheng 是MyISAM的引擎吧，我觉得是频繁的写操作导致大量读操作被锁住，MyISAM的表级锁粒度太大。可以试着分表，把旧的数据独立出来给读取用，新数据写入一个其他的表，然后定期批量写入。或者采用 InnoDB，它用行级锁，锁效率要好的多

[dongsheng]

@TheOnly92

其实最根本的原因是我不是mysql专家 :-) 实在是缺乏优化的经验。

据我所知mysql有内置的query cache吧，系统里有不少复杂查询，第一次很慢，之后会从缓存取，而不是再跑一遍查询。把SELECT COUNT(*) FROM log;缓存起来不太现实，这个表每分钟都能增长几千几万条记录，其他的查询也是在时时变化的。

[Livid]

@dongsheng 你的这张表有多个 bigint 的索引，按照目前的数据规模，所有的这些索引加起来可能就占用了数 G 的内存。服务器一共有多少内存？现在是否已经开始吃交换分区？这些都是需要看的。

[dongsheng]

@liwei 这个也考虑过了，两个月前把所有新的log写入log_temp表，然后让cron把log_temp里的东西转移到log表里，这样算是解决了insert锁死的问题，没想到现在连读都能锁死了

[AntiGameZ]

请问一下，moodle有没有什么特殊含义，是系统在moodle上改的，还是随便取的？

[dongsheng]

@Livid 这个竟然有这么大的区别？我现在没法进produciton server，那边的管理员在另一个半球睡觉，等他们上线让他们查查mysql吃掉的内存。

[Livid]

bigint 是 8 个字节，int 是 4 个字节。

[TheOnly92]

当时就应该考虑换成 innodb 了吧，如果只是要搞定 COUNT(*) 的问题，可以建立另一个列表在每次 insert 时记录下来，然后之后需要的时候从这个列表读取而不是 COUNT(*) 就行了吧。

但是 myisam 问题确实很大啊。。。

[dongsheng]

@AntiGameZ moodle是个开源的课程管理系统，这个表是moodle系统里的表

[liwei]

@dongsheng 如果这个表只有读取线程的话是不会锁的，读锁又不是互斥的
@AntiGameZ 你遇到 moodle 的开发者了，哈哈

[dongsheng]

@TheOnly92 这个系统有很久的历史了，大概05年开始的，那时候innodb还籍籍无名呢 :-)

[dongsheng]

@liwei 是我用词不对，读的query就是死在那里永远的waiting。。。

[muxi]

囧，看了表的定义，全都是bigint，连time都是bigint，浪费的不止一点点内存啊
具体你要看服务器本身的负责，io延迟等参数，内存使用量

[dongsheng]

@muxi mysql处理bigint(10)和int(10)分配的是不同的内存？

[Livid]

@dongsheng bigint 比 int 要消耗双倍的存储，无论是磁盘上还是内存里。

[muxi]

@dongsheng bigint 和int 都是定长的类型啊，10 只是表示显示宽度，并不能影响存储的大小

[Platinum]

05 年我面试一人，临走前最后一个问题是你用 slowlog 么，那哥们说 恩，我从来都是设成1

先 slowlog，再抓出语句 explain

又及，你的 key_buffer 用了多少

网上优化的文章一大堆，碰到问题先考虑自己会不会用 MySQL，然后再去考虑 MySQL 的极限是多少

推荐所有想用 MySQL 的人都买本《高性能 MySQL》 http://book.douban.com/subject/4241826/

[dongsheng]

@Platinum 只是用这么标题引出优化问题而已，何必又是面试某某又是批评别人不会用mysql？

[areless]

SELECT * FROM log WHERE time>* && time<* && ...。
每个查询都提前缩小要查询的范围。

定期OPTIMIZE TABLE。

my.cnf
concurrent_insert=2

[Platinum]

@dongsheng 说的就是你这标题，再配合提问内容，感觉有点不知天高地厚了。碰到问题先想的是不是自己的问题，而是 MySQL 的问题，习惯做这种假设的人实在是不着人喜欢

你要是写成“现在有2000万条记录的表，该如何优化”，我自然也不会提会不会用 MySQL

[areless]

一般来说索引跟SQL语句都没问题的话。MySQL的瓶颈在磁盘IO上。可以用RAID 0来提升磁盘读写速度。或者，设从服务在第二块第三块硬盘。通过mysql proxy分别用不同的从服务去处理查询。当mysql操作swap了，那查询效率肯定下来了，所以内存首先要富裕。

[dongsheng]

@Platinum 您不喜欢就不劳您指点了。

[kingwkb]

争吵没意思，按时间分区应该管用

[TheOnly92]

目前楼主能做的是什么？可以暂时让网站的作业停顿下来吗？4～5 小时？

[bigbrother]

根据业务对某个字段进行分区就OK了。一般是时间。

我处理过一个亿级的表，分区后查起来飕飕的，而且还有连表查询

[lianghai]

@e6nian 想到了 P 大的选课系统……这个星球上有没有让人舒心一点的选课系统可以瞻仰呢？

[dongsheng]

@kingwkb @areless 不吵，只是有点不好意思我这些愚笨且不知天高地厚的问题惊动了上面的大神，以后问问题一定先读上一斗书再考虑是否该问 :D

@areless：程序里面的query都是有条件，我上面举的select例子其实也不完全，不光是有course module action的约束也有时间的约束，因为这个表的主要功能就是显示最近的课程活动。刚才跟联系上那个大学的sysadmin，那个服务器的配置确实不佳，内存就挺紧张，以至于不得不用脚本监控进程，达到一定数量就需要重启。我再多研究下数据库，也得建议他们对内存进行升级，谢谢。

我并不是质疑mysql处理不了2000万的数据，而是怀疑我可能错过某些可能很简单的问题导致mysql表现如此槽糕。不管怎么说，感谢楼上所有提出建设性意见的朋友。

[yiyang]

@Platinum apache 和 缓存方面的看什么书？

[magic22cn]

大表好解决：Partitioning，单表2000万不算什么，选择好合适的键分区就好了。不过mysql的分区功能很弱，不是所有想法都能实现的

[no2x]

Try this: SELECT COUNT(id) FROM log;

[darasion]

唉。这样的问题太像上次要我回答的面试题了。。。可惜我只知道原理，不知道术语以及具体如何去做。没说好。

[bruce]

按时间分表分库，分磁盘

[xmbaozi]

这个全表 count 可以单独用计数器

[xmbaozi]

刚刚在处理一个 5 亿条记录的表。。