您当前位于: 首页 » 我爱PHP, 网管日志 » 同事今天下午的MYSQL性能优化分享

同事今天下午的MYSQL性能优化分享10/09/2010

下午的分享,同事讲了下关于mysql性能优化方面几个心得,很有意义,贴出来

1、分库分表

很明显,一个主表(也就是很重要的表,例如用户表)无限制的增长势必严重影响性能,分库与分表是一个很不错的解决途径,也就是性能优化途径,现在的案例是我们有一个1000多万条记录的用户表members,查询起来非常之慢,同事的做法是将其散列到100个表中,分别从members0到members99,然后根据mid分发记录到这些表中,牛逼的代码大概是这样子:

<?php
for($i=0;$i< 100; $i++ ){
	//echo "CREATE TABLE db2.members{$i} LIKE db1.members<br>";
	echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}<br>";
}
?>

2、不停机修改mysql表结构

同样还是members表,前期设计的表结构不尽合理,随着数据库不断运行,其冗余数据也是增长巨大,同事使用了下面的方法来处理:

先创建一个临时表:

/*创建临时表*/
CREATE TABLE members_tmp LIKE members

然后修改members_tmp的表结构为新结构,接着使用上面那个for循环来导出数据,因为1000万的数据一次性导出是不对的,mid是主键,一个区间一个区间的导,基本是一次导出5万条吧,这里略去了
接着重命名将新表替换上去:

/*这是个颇为经典的语句哈*/
RENAME TABLE members TO members_bak,members_tmp TO members;

就是这样,基本可以做到无损失,无需停机更新表结构,但实际上RENAME期间表是被锁死的,所以选择在线少的时候操作是一个技巧。经过这个操作,使得原先8G多的表,一下子变成了2G多

另外还讲到了mysql中float字段类型的时候出现的诡异现象,就是在pma中看到的数字根本不能作为条件来查询.感谢zj同学的新鲜分享。

| 17条评论 标签:  

17条评论
  1. mtmt说道:

    member用mid做標準進行分表方便查询么?一般的业务需求中基本上都是以username为查询依据,正常应当是username做hash取模来分表吧。

    • gently说道:

      实际上同事分享的是另一个表用来做分表的,被我替换成members表了,从查询方便来说,是做到了业务需求的。这里只是总结他讲的内容,呵呵~让您受精了

  2. 2ig2ag说道:

    分表的话 mysql 的partition功能就是干这个的,对代码是透明的;在代码层面去实现貌似是不合理的

    • msyze说道:

      但是如果mysql版本过旧就只能使用楼主这种方法了

      • mtmt说道:

        升级mysql相比大幅度改动代码来说要好得多,所以这个理由不太站得住脚。

  3. istoez说道:

    你的这个语法加亮很清爽啊,用的什么编辑器呢,还是自己调色的呢?能不能分享一下?

  4. wayswang说道:

    那两个sql语法确实牛逼

  5. 老W说道:

    可以用存储过程来代替

  6. mark35说道:

    mysql前途黯淡 http://www.javaeye.com/news/18425
    我都开始往pgsql上面转移了。研究了下pgsql发现实在是强大很多,mysql相比只是个玩具也。

  7. loki说道:

    那如果需要按照mid降序的列表,如何,比如后台管理会员的列表页面

  8. bandit说道:

    这是在数据库容量增大的情况下一种有效的优化方法:垂直分表。
    但是会出现查询命中率的问题,如果有其他的数据库服务器宕机了。。。。

  9. polarbear说道:

    学习了

  10. perfgeeks说道:

    INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}

    这条语句子查询,最好加上一条order by mid 另外应该加上limit,否则对于1000w记录,这条子查询应该会失败。

发表评论