› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
这是一个创建于 5014 天前的主题，其中的信息可能已经有所发展或是发生改变。
最近处理一个大学的课程管理系统数据库，他们的一个日志表达到2000万行记录的水平，已经有了严重的性能问题，mysql有很多死掉的查询，请问MySQL何时会在记录数上出现瓶颈，有什么合适的解决办法吗？（这里不可能使用NoSQL）
 |
1
flytwokites 2011-04-07 10:36:33 +08:00
我想是索引没有用好，打开mysql的慢查询功能看看哪些sql有性能问题。
|
 |
2
sophon 2011-04-07 10:39:11 +08:00
可以考虑做下拆表
|
 |
3
dongsheng OP @flytwokites 索引没问题，第一件事就是检查了索引
|
 |
4
virushuo 2011-04-07 10:59:52 +08:00
我听说过的最大表里面有几个亿，每天增量还在百万级别
|
|
5
dongsheng OP |
 |
6
muxi 2011-04-07 11:10:16 +08:00
2000w而已，不至于出现什么瓶颈，我所用过的表多的会有10亿+的记录，像你这种日志表，只要有足够的内存（我一般给MySQL 8G以上的内存）、查询能使用主键或者索引应该不会出现什么问题，或许你换成Innodb引擎会更快一点，而且大学的课程管理系统，应该不会有很大的并发量，超级大的表，一般慢在磁盘和CPU上，或许你可以换个好点的服务器，如果你熟悉MySQL的话， 最新的5.5版本应该能显著提升你系统的性能
|
 |
7
liwei 2011-04-07 11:15:30 +08:00
@dongsheng 我觉得这个不是MySQL处理数据的极限限制，而是受你查询复杂程度的限制。单表带索引的查询，在上亿的数据量上还是很快的
|
|
8
dongsheng OP @muxi 现在最大的问题是日志表的查询非常非常频繁，因为这个系统非常依赖日志表显示来显示课程的中最近的活动，大量的查询都卡死在这里，导致很多页面都打不开。数据量可能跟你说的10亿没法比，但这个大学对该系统依赖非常高，所有的授课和考试都通过这个系统进行，并发很大。
可能真的让他们升级硬件或者升级MySQL（现在是5.0），我个人和他们都不倾向升级软件，数据量极大，可能得折腾一星期，还可能耽误授课计划。还有个烦人的问题是升级mysql还要升级linux，mysql5.5用的是新glibc，升级系统要走的流程就更多了，烦。 多谢你的建议。 |
 |
10
Livid MOD @dongsheng 如果不涉及什么特别敏感的信息的话，你可以把那张表的结构定义（包括所有的索引的定义）发到 gist 然后分享给大家看看。
|
 |
11
e6nian 2011-04-07 12:05:09 +08:00
大学的选课系统都是悲剧。
某些得体的老湿基本上都是被"秒杀" |
|
12
dongsheng OP @Livid 谢谢
Gist: https://gist.github.com/907013 Indexes: http://cl.ly/0C2X3s3U2Z213J0e3b0Z 这个表完全没有join查询，都是直来直去的 |
 |
13
TheOnly92 2011-04-07 12:09:31 +08:00
主要是做何种查询？
|
|
14
dongsheng OP @TheOnly92
比如 SELECT COUNT(*) FROM log; SELECT * FROM log WHERE course=1 AND module=2 AND action="view"; |
|
15
Livid MOD @dongsheng 先说一个我觉得奇怪的地方：
bigint(10) 你们的应用中会出现超过 32 位范围的 64 位整数么？如果不会的话，把所有的 bigint 改成 int 可以节约内存。 另外，bigint 的长度应该是 20 而不是 10。 |
|
16
TheOnly92 2011-04-07 12:14:09 +08:00
这个时候做些 alter table 会卡掉整个系统吧
那两个查询的执行时间是多少？ |
|
17
Livid MOD |
|
18
flytwokites 2011-04-07 12:18:52 +08:00
象
SELECT * FROM log WHERE course=1 AND module=2 AND action="view"; 这类语句应该做个组合索引。 |
|
19
dongsheng OP @flytwokites 这个做了，你看我发的截图。
|
|
20
dongsheng OP @TheOnly92 这个我用mysql客户端执行是瞬间完成的，问题是当并发大的时候，很多这种查询会死掉，用show processlist会看到很多SELECT COUNT(*) FROM log; 锁死在那里了。
|
|
21
TheOnly92 2011-04-07 12:25:39 +08:00
这个难道不能存入缓存吗？看到很多查询锁死，不表示该查询的问题，而是之前的查询还没完，接下来的查询都不能顺利进行。所以还得找出最根本的问题。
|
|
22
dongsheng OP @Livid
现在这个表是myisam的，换成innodb会不会有后遗症？ 我严重怀疑mysql不争气，同样的系统，有人用postgresql做存储，大致一个级别的网站，没有出现log表锁死的问题，不过它的服务器要稍好些。 |
|
23
liwei 2011-04-07 12:30:03 +08:00
@dongsheng 是MyISAM的引擎吧，我觉得是频繁的写操作导致大量读操作被锁住，MyISAM的表级锁粒度太大。可以试着分表，把旧的数据独立出来给读取用，新数据写入一个其他的表，然后定期批量写入。或者采用 InnoDB，它用行级锁，锁效率要好的多
|
|
24
dongsheng OP @TheOnly92
其实最根本的原因是我不是mysql专家 :-) 实在是缺乏优化的经验。 据我所知mysql有内置的query cache吧，系统里有不少复杂查询，第一次很慢，之后会从缓存取，而不是再跑一遍查询。把SELECT COUNT(*) FROM log;缓存起来不太现实，这个表每分钟都能增长几千几万条记录，其他的查询也是在时时变化的。 |
|
25
Livid MOD @dongsheng 你的这张表有多个 bigint 的索引，按照目前的数据规模，所有的这些索引加起来可能就占用了数 G 的内存。服务器一共有多少内存？现在是否已经开始吃交换分区？这些都是需要看的。
|
|
26
dongsheng OP @liwei 这个也考虑过了，两个月前把所有新的log写入log_temp表，然后让cron把log_temp里的东西转移到log表里，这样算是解决了insert锁死的问题，没想到现在连读都能锁死了
|
 |
27
AntiGameZ 2011-04-07 12:38:05 +08:00
请问一下，moodle有没有什么特殊含义，是系统在moodle上改的，还是随便取的？
|
|
28
dongsheng OP @Livid 这个竟然有这么大的区别？我现在没法进produciton server，那边的管理员在另一个半球睡觉，等他们上线让他们查查mysql吃掉的内存。
|
|
29
Livid MOD bigint 是 8 个字节，int 是 4 个字节。
|
|
30
TheOnly92 2011-04-07 12:41:09 +08:00
当时就应该考虑换成 innodb 了吧，如果只是要搞定 COUNT(*) 的问题，可以建立另一个列表在每次 insert 时记录下来，然后之后需要的时候从这个列表读取而不是 COUNT(*) 就行了吧。
但是 myisam 问题确实很大啊。。。 |
|
32
liwei 2011-04-07 12:43:32 +08:00
|
|
35
muxi 2011-04-07 12:54:07 +08:00
囧，看了表的定义，全都是bigint，连time都是bigint，浪费的不止一点点内存啊
具体你要看服务器本身的负责，io延迟等参数，内存使用量 |
 |
39
Platinum 2011-04-07 13:44:50 +08:00
05 年我面试一人，临走前最后一个问题是你用 slowlog 么，那哥们说 恩，我从来都是设成1
先 slowlog，再抓出语句 explain 又及，你的 key_buffer 用了多少 网上优化的文章一大堆，碰到问题先考虑自己会不会用 MySQL，然后再去考虑 MySQL 的极限是多少 推荐所有想用 MySQL 的人都买本《高性能 MySQL》 http://book.douban.com/subject/4241826/ |
 |
41
areless 2011-04-07 14:06:18 +08:00
SELECT * FROM log WHERE time>* && time<* && ...。
每个查询都提前缩小要查询的范围。 定期OPTIMIZE TABLE。 my.cnf concurrent_insert=2 |
|
42
Platinum 2011-04-07 14:17:29 +08:00
@dongsheng 说的就是你这标题，再配合提问内容，感觉有点不知天高地厚了。碰到问题先想的是不是自己的问题，而是 MySQL 的问题，习惯做这种假设的人实在是不着人喜欢
你要是写成“现在有2000万条记录的表，该如何优化”，我自然也不会提会不会用 MySQL |
|
43
areless 2011-04-07 14:21:12 +08:00
一般来说索引跟SQL语句都没问题的话。MySQL的瓶颈在磁盘IO上。可以用RAID 0来提升磁盘读写速度。或者，设从服务在第二块第三块硬盘。通过mysql proxy分别用不同的从服务去处理查询。当mysql操作swap了，那查询效率肯定下来了，所以内存首先要富裕。
|
 |
45
kingwkb 2011-04-07 14:34:28 +08:00
争吵没意思，按时间分区应该管用
|
|
46
TheOnly92 2011-04-07 14:37:55 +08:00
目前楼主能做的是什么？可以暂时让网站的作业停顿下来吗？4～5 小时？
|
 |
47
bigbrother 2011-04-07 14:43:02 +08:00
根据业务对某个字段进行分区就OK了。一般是时间。
我处理过一个亿级的表，分区后查起来飕飕的，而且还有连表查询 |
|
49
dongsheng OP @kingwkb @areless 不吵，只是有点不好意思我这些愚笨且不知天高地厚的问题惊动了上面的大神，以后问问题一定先读上一斗书再考虑是否该问 :D
@areless：程序里面的query都是有条件，我上面举的select例子其实也不完全，不光是有course module action的约束也有时间的约束，因为这个表的主要功能就是显示最近的课程活动。刚才跟联系上那个大学的sysadmin，那个服务器的配置确实不佳，内存就挺紧张，以至于不得不用脚本监控进程，达到一定数量就需要重启。我再多研究下数据库，也得建议他们对内存进行升级，谢谢。 我并不是质疑mysql处理不了2000万的数据，而是怀疑我可能错过某些可能很简单的问题导致mysql表现如此槽糕。不管怎么说，感谢楼上所有提出建设性意见的朋友。 |
 |
51
magic22cn 2011-04-07 17:19:20 +08:00
大表好解决：Partitioning，单表2000万不算什么，选择好合适的键分区就好了。不过mysql的分区功能很弱，不是所有想法都能实现的
|
 |
52
no2x 2011-04-07 18:16:10 +08:00
Try this: SELECT COUNT(id) FROM log;
|
 |
53
darasion 2011-04-07 18:22:59 +08:00
唉。这样的问题太像上次要我回答的面试题了。。。可惜我只知道原理，不知道术语以及具体如何去做。没说好。
|
 |
54
bruce 2011-04-07 19:29:09 +08:00 via Android
按时间分表分库，分磁盘
|
 |
55
xmbaozi 2017-06-02 15:43:03 +08:00
这个全表 count 可以单独用计数器
|
|
56
xmbaozi 2017-06-02 15:44:23 +08:00
刚刚在处理一个 5 亿条记录的表。。
|
Select Language