我有一个自己的网站,网站上有大量比赛排名数据,差不多几十万条,而且还在不断加入,每当有新的比赛,就会有很多(差不多5000左右)会员来访问成绩数据,当他们同时访问数据就会出现服务器cpu使用率100%的情况,服务器经常会卡死,就得重启,后来查看了一下,是mysql程序占用cpu高,我想问下有没有什么好的方法可以优化一下数据库,比如会员刷新数据时,如果没有新数据加入,就不要再次查询数据库,减轻服务器负荷,麻烦高手集思广益,帮助一下小弟
解决方案 »
- 如何把表里的数据分到多个表中去.
- mysql存储过程数据引起的死循环如何定位
- 有什么方法可以让select email from user group by email having count(*)>1 区分大小写
- mysql的数据存储在哪?
- insert问题
- 通过MYSQL命令行或者PHPMYADMIN插入或显示中文数据时乱码!!
- MySQL中如何在cmd或shell中显示出某个数据库的ER图
- 帮帮我啊,本是小问题,对我却是大问题(resin3.0语mysql3.23的驱动jdbc怎么连接)
- 两级级菜单一次性查询出来的问题
- 求助mysql id自动增长的问题
- 急问: mysql commandline 无法显示中文问题
- mysql远程连接问题???求高人指教(在线等)
将查询业务放入另外一台数据库服务器,用master-slave方案来解决。
CPU高有两个主要原因:一是数据量大,读写都要走CPU
二是排序及函数计算可以看看show processlist看看哪些进程执行时间长
建议查看具体sql语句,查看慢查询查看 是否索引合适。比如会员刷新数据时,如果没有新数据加入,就不要再次查询数据库,减轻服务器负荷
可以加cache进行缓存数据
反复调用此命令(每秒刷两次),发现网站 A 的两个 SQL 语句经常在 process list 中出现,其语法如下: SELECT t1.pid, t2.userid, t3.count, t1.date FROM _mydata AS t1LEFT JOIN _myuser AS t3 ON t1.userid=t3.useridLEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid ORDER BY t1.pid LIMIT0,15
调用 show columns 检查这三个表的结构 : mysql>show columns from _myuser;mysql>show columns from _mydata;mysql>show columns from _mydata_body;
终于发现了问题所在:_mydata 表,只根据 pid 建立了一个 primary key,但并没有为 userid 建立索引。而在这个 SQL 语句的第一个 LEFT JOIN ON 子句中: LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid
_mydata 的 userid 被参与了条件比较运算。于是我为给 _mydata 表根据字段 userid 建立了一个索引: mysql>ALTER TABLE `_mydata` ADD INDEX ( `userid` )
建立此索引之后,CPU 马上降到了 80% 左右。看到找到了问题所在,于是检查另一个反复出现在 show processlist 中的 sql 语句: SELECT COUNT(*)FROM _mydata AS t1, _mydata_key AS t2WHERE t1.pid=t2.pid and t2.keywords=
'孔雀'
经检查 _mydata_key 表的结构,发现它只为 pid 建了了 primary key, 没有为 keywords 建立 index。_mydata_key 目前有 33 万条记录,在没有索引的情况下对33万条记录进行文本检索匹配,不耗费大量的 cpu 时间才怪。看来就是针对这个表的检索出问题了。于是同样为 _mydata_key 表根据字段 keywords 加上索引: mysql>ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )
建立此索引之后,CPU立刻降了下来,在 50%~70%之间震荡。 再次调用 show prosslist,网站A 的sql 调用就很少出现在结果列表中了。但发现此主机运行了几个 Discuz 的论坛程序, Discuz论坛的好几个表也存在着这个问题。于是顺手一并解决,cpu占用再次降下来了。 至此,问题解决。
1. 增加 tmp_table_size 值。mysql 的配置文件中,tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY 查询,增加 tmp_table_size 值。 这是 mysql 官方关于此选项的解释: tmp_table_size
This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory. 2. 对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的条件判断中用到的字段,应该根据其建立索引 INDEX。 索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000行,这比顺序读取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。 根据 mysql 的开发文档: 索引 index 用于: o 快速找出匹配一个WHERE子句的行
o 当执行联结(JOIN)时,从其他表检索行。
o 对特定的索引列找出MAX()或MIN()值
o 如果排序或分组在一个可用键的最左面前缀上进行(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有键值部分跟随DESC,键以倒序被读取。
o 在一些情况中,一个查询能被优化来检索值,不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀,为了更快,值可以从索引树被检索出来。 假定你发出下列SELECT语句: mysql>SELECT*FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一个多列索引存在于col1和col2上,适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上,优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。 开发人员做 SQL 数据表设计的时候,一定要通盘考虑清楚。