急,我做的出售房源的数据库,速度慢的不行,老是打不开,首页打开一半就停了,什么也没有,再刷一下有时候可以打开。里面数据有2-3十万,会频繁的更新,添加,删除,想请教一个高手,给看一下数据库结构是否合理,索引是否正确?请那位大侠帮助一下,万分感谢!

解决方案 »

  1.   

    可是楼主什么信息都没提供啊。这样基本上没办法对你的设计进行评论。提供你的建表语句,查询语句。你可以到MYSQL的slow query 日志中查看一下执行花时间比较长的SQL语句都是哪些。show create table tabName;
    show index from tabName;
    explain select ......;请至少提供以上信息。
      

  2.   

    对了再问一下,my.ini的配置使用的内存设置成多少,适合这么大的数据量?
      

  3.   

    还有你说慢的语句的explain...贴出来看看吧
      

  4.   

    1 SQL 弄出来看看
    2 SQL 关联表数据量也说说。
      

  5.   

    楼主是否能直接贴出文本,这样方便分析。show create table tabName;
    show index from tabName;
    explain select ......;把你慢的语句贴出来。
      

  6.   

    楼主是否能直接贴出文本,这样方便分析。show create table tabName;
    show index from tabName;
    explain select ......;把你慢的语句贴出来。
      

  7.   

    SELECT id,xiaoqu,househx,jz_area,housepay FROM esf_sell WHERE ifpass=1 AND  (address like'%东丹庭院%') OR (xiaoqu like'%东丹庭院%') ORDER BY id desc LIMIT 10;SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30;可多语句呢,我不知道该怎么说,你有QQ吗?
      

  8.   

    建议楼主能看一下1楼,17楼,贴你的这些文本出来。贴图的话,别人并不容易在本机创建相同的表。当然某些愿意帮你的人愿意一个字一个字去敲也有可能。
    至于慢的SQL语句,先一个一个解决。 比如你 18楼这个语句,现在执行花了多少时间?
    然后用
    explain select ...看一下,贴出结果。
      

  9.   

    SELECT id,xiaoqu,househx,jz_area,housepay 
    FROM esf_sell 
    WHERE ifpass=1 AND  (address like'%东丹庭院%') 
    OR (xiaoqu like'%东丹庭院%') 
    ORDER BY id desc 
    LIMIT 10;这个语句,没什么办法,你的 OR (xiaoqu like'%东丹庭院%')  一定会导致全表扫描。唯可利用的索引是 (ID) ,不知道你的表中有没有。
    SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian 
    FROM esf_sell 
    WHERE ifpass='1'  
    ORDER BY updatetime DESC LIMIT 0, 30;
    这个查询看你有没有(ifpass,updatetime)的索引。如果没有要看你ifpass的数值分布。
      

  10.   

    好的,CREATE TABLE IF NOT EXISTS `esf_sell_1` (
      `id` mediumint(8) NOT NULL auto_increment,
      `IsPublished` tinyint(1) NOT NULL default '1',
      `systemcode` varchar(100) NOT NULL default '' COMMENT '系统编号',
      `Internalcode` varchar(100) NOT NULL default '' COMMENT '内部编号',
      `district` varchar(20) NOT NULL,
      `TradeCircle` varchar(50) NOT NULL,
      `housefcz` int(4) NOT NULL,
      `zxcd` varchar(30) NOT NULL,
      `xiaoqu` varchar(100) NOT NULL,
      `address` varchar(100) NOT NULL,
      `househx` varchar(50) NOT NULL COMMENT '户型',
      `househx_s` tinyint(1) NOT NULL default '0',
      `househx_t` tinyint(1) NOT NULL default '0',
      `househx_w` tinyint(1) NOT NULL default '0',
      `househx_c` tinyint(1) NOT NULL default '0',
      `househx_y` tinyint(1) NOT NULL default '0',
      `houselx` varchar(30) NOT NULL COMMENT '房屋类型',
      `jz_area` float NOT NULL default '0' COMMENT '建筑面积',
      `housepay` float NOT NULL default '0' COMMENT '交易价格',
      `man` varchar(50) NOT NULL,
      `zjname` varchar(30) NOT NULL,
      `tel` varchar(100) NOT NULL,
      `UserID` mediumint(8) NOT NULL default '0' COMMENT '发布信息会员ID号',
      `UserType` varchar(2) NOT NULL default '个',
      `posttime` int(11) NOT NULL default '0' COMMENT '添加时间',
      `updatetime` int(11) NOT NULL default '0' COMMENT '更新时间',
      `iftuijian` tinyint(1) NOT NULL default '0' COMMENT '是否推荐',
      `admintuijian` tinyint(1) NOT NULL default '0' COMMENT '管理员推荐',
      `ifpass` tinyint(1) NOT NULL default '0' COMMENT '是否通过验证',
      `ifchengjiao` tinyint(1) NOT NULL default '0',
      `ifsetautoupdate` tinyint(1) NOT NULL default '0',
      `postip` varchar(15) NOT NULL,
      `pageviews` int(11) NOT NULL default '1',
      `ifdaikuan` varchar(10) NOT NULL,
      `chaoxiang` varchar(4) NOT NULL,
      `housejg` varchar(30) NOT NULL,
      `sy_area` float NOT NULL,
      `sum_floor` int(3) NOT NULL,
      `do_floor` int(3) NOT NULL,
      `jg_time` varchar(20) NOT NULL,
      `zawujian` varchar(50) NOT NULL,
      `ifxiaofang` varchar(20) NOT NULL,
      `kanfangtime` varchar(20) NOT NULL,
      `chanquan` varchar(30) NOT NULL,
      `peitao` varchar(255) NOT NULL,
      `tese` varchar(255) NOT NULL,
      `bus` varchar(100) NOT NULL,
      `email` varchar(100) NOT NULL,
      `qq` varchar(100) NOT NULL,
      `house_ms` mediumtext NOT NULL,
      `imageurl` char(200) NOT NULL,
      `imagenum` smallint(4) NOT NULL,
      `old_id` int(11) NOT NULL,
      PRIMARY KEY  (`id`),
      KEY `district` (`district`),
      KEY `TradeCircle` (`TradeCircle`),
      KEY `househx` (`househx`),
      KEY `updatetime` (`updatetime`),
      KEY `UserType` (`UserType`),
      KEY `UserID` (`UserID`),
      KEY `housepay` (`housepay`),
      KEY `jz_area` (`jz_area`),
      KEY `posttime` (`posttime`),
      KEY `ifpass` (`ifpass`)
    ) ENGINE=MyISAM DEFAULT CHARSET=gbk AUTO_INCREMENT=1 
      

  11.   

    Query_time: 13  Lock_time: 0  Rows_sent: 30  Rows_examined: 225950
    SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30;
    # Query_time: 17  Lock_time: 4  Rows_sent: 30  Rows_examined: 225950
    SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30;
    # Query_time: 17  Lock_time: 4  Rows_sent: 30  Rows_examined: 225950
    SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30;
      

  12.   


    到底是表 esf_sell_1 还是表 esf_sell ?已经讲过很多遍了。
    请您贴一下
    SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30; 
    的时间。及 explain SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30;的结果。
      

  13.   


    到底是表 esf_sell_1 还是表 esf_sell ?已经讲过很多遍了。
    请您贴一下
    SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30; 
    的时间。及 explain SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30;的结果。
      

  14.   

    那个数据结构能用不,能建上表吗?我是刚用mysql对这个不太懂,
      

  15.   

    CREATE  TABLE  `table`.`esf_sell` (  `id` mediumint( 8  )  NOT  NULL  auto_increment ,
     `IsPublished` tinyint( 1  )  NOT  NULL default  '1',
     `systemcode` varchar( 100  )  NOT  NULL default  '' COMMENT  '系统编号',
     `Internalcode` varchar( 100  )  NOT  NULL default  '' COMMENT  '内部编号',
     `district` varchar( 20  )  NOT  NULL ,
     `TradeCircle` varchar( 50  )  NOT  NULL ,
     `housefcz` int( 4  )  NOT  NULL ,
     `zxcd` varchar( 30  )  NOT  NULL ,
     `xiaoqu` varchar( 100  )  NOT  NULL ,
     `address` varchar( 100  )  NOT  NULL ,
     `househx` varchar( 50  )  NOT  NULL  COMMENT  '户型',
     `househx_s` tinyint( 1  )  NOT  NULL default  '0',
     `househx_t` tinyint( 1  )  NOT  NULL default  '0',
     `househx_w` tinyint( 1  )  NOT  NULL default  '0',
     `househx_c` tinyint( 1  )  NOT  NULL default  '0',
     `househx_y` tinyint( 1  )  NOT  NULL default  '0',
     `houselx` varchar( 30  )  NOT  NULL  COMMENT  '房屋类型',
     `jz_area` float NOT  NULL default  '0' COMMENT  '建筑面积',
     `housepay` float NOT  NULL default  '0' COMMENT  '交易价格',
     `man` varchar( 50  )  NOT  NULL ,
     `zjname` varchar( 30  )  NOT  NULL ,
     `tel` varchar( 100  )  NOT  NULL ,
     `UserID` mediumint( 8  )  NOT  NULL default  '0' COMMENT  '发布信息会员ID号',
     `UserType` varchar( 2  )  NOT  NULL default  '个',
     `posttime` int( 11  )  NOT  NULL default  '0' COMMENT  '添加时间',
     `updatetime` int( 11  )  NOT  NULL default  '0' COMMENT  '更新时间',
     `iftuijian` tinyint( 1  )  NOT  NULL default  '0' COMMENT  '是否推荐',
     `admintuijian` tinyint( 1  )  NOT  NULL default  '0' COMMENT  '管理员推荐',
     `ifpass` tinyint( 1  )  NOT  NULL default  '0' COMMENT  '是否通过验证',
     `ifchengjiao` tinyint( 1  )  NOT  NULL default  '0',
     `ifsetautoupdate` tinyint( 1  )  NOT  NULL default  '0',
     `postip` varchar( 15  )  NOT  NULL ,
     `pageviews` int( 11  )  NOT  NULL default  '1',
     `ifdaikuan` varchar( 10  )  NOT  NULL ,
     `chaoxiang` varchar( 4  )  NOT  NULL ,
     `housejg` varchar( 30  )  NOT  NULL ,
     `sy_area` float NOT  NULL ,
     `sum_floor` int( 3  )  NOT  NULL ,
     `do_floor` int( 3  )  NOT  NULL ,
     `jg_time` varchar( 20  )  NOT  NULL ,
     `zawujian` varchar( 50  )  NOT  NULL ,
     `ifxiaofang` varchar( 20  )  NOT  NULL ,
     `kanfangtime` varchar( 20  )  NOT  NULL ,
     `chanquan` varchar( 30  )  NOT  NULL ,
     `peitao` varchar( 255  )  NOT  NULL ,
     `tese` varchar( 255  )  NOT  NULL ,
     `bus` varchar( 100  )  NOT  NULL ,
     `email` varchar( 100  )  NOT  NULL ,
     `qq` varchar( 100  )  NOT  NULL ,
     `house_ms` mediumtext NOT  NULL ,
     `imageurl` char( 200  )  NOT  NULL ,
     `imagenum` smallint( 4  )  NOT  NULL ,
     `old_id` int( 11  )  NOT  NULL ,
     PRIMARY  KEY (  `id`  ) ,
     KEY  `district` (  `district`  ) ,
     KEY  `TradeCircle` (  `TradeCircle`  ) ,
     KEY  `househx` (  `househx`  ) ,
     KEY  `updatetime` (  `updatetime`  ) ,
     KEY  `UserType` (  `UserType`  ) ,
     KEY  `UserID` (  `UserID`  ) ,
     KEY  `housepay` (  `housepay`  ) ,
     KEY  `jz_area` (  `jz_area`  ) ,
     KEY  `posttime` (  `posttime`  ) ,
     KEY  `ifpass` (  `ifpass` ,  `district` ( 4  ) ,  `UserType`  )  ) ENGINE  =  MyISAM  DEFAULT CHARSET  = gbk;
      

  16.   

    show index from tabName;这个我弄成图片你看行吗?
      

  17.   

    show index from esf_sell;然后贴出结果!SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30; 贴出运行时间的结果!explain SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30;
    然后贴出的结果。
      

  18.   

    show index from esf_sell; 
      

  19.   

    SHOW INDEX FROM esf_sell;执行结果:Table  Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  
    esf_sell 0 PRIMARY 1 id A 226604 NULL NULL   BTREE   
    esf_sell 1 district 1 district A 20 NULL NULL   BTREE   
    esf_sell 1 TradeCircle 1 TradeCircle A 197 NULL NULL   BTREE   
    esf_sell 1 househx 1 househx A 25 NULL NULL   BTREE   
    esf_sell 1 updatetime 1 updatetime A 113302 NULL NULL   BTREE   
    esf_sell 1 UserType 1 UserType A 3 NULL NULL   BTREE   
    esf_sell 1 UserID 1 UserID A 4046 NULL NULL   BTREE   
    esf_sell 1 housepay 1 housepay A 2360 NULL NULL   BTREE   
    esf_sell 1 jz_area 1 jz_area A 5035 NULL NULL   BTREE   
    esf_sell 1 posttime 1 posttime A 226604 NULL NULL   BTREE   
    esf_sell 1 ifpass 1 ifpass A 2 NULL NULL   BTREE   
    esf_sell 1 ifpass 2 district A 21 4 NULL   BTREE   
    esf_sell 1 ifpass 3 UserType A 48 NULL NULL   BTREE   
      

  20.   

    EXPLAIN SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian
    FROM esf_sell
    WHERE ifpass = '1'
    ORDER BY updatetime DESC 
    LIMIT 0 , 30;执行结果: id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
    1 SIMPLE esf_sell range ifpass ifpass 1 NULL 226546 Using where; Using filesort 
      

  21.   

    SELECT id,xiaoqu,district,address,househx,housepay,jz_area,updatetime,UserType,iftuijian FROM esf_sell WHERE ifpass='1'  ORDER BY updatetime DESC LIMIT 0, 30; 执行时间:显示行 0 - 29 (30 总计, 查询花费 1.5111 秒)
      

  22.   

    贴出如下语句的输出!
    EXPLAIN 
    SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian
    FROM esf_sell FORCE INDEX (updatetime) IGNORE INDEX (ifpass) 
    WHERE ifpass = '1'
    ORDER BY updatetime DESC 
    LIMIT 0 , 30;贴出如下语句的执行时间。SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian
    FROM esf_sell FORCE INDEX (updatetime) IGNORE INDEX (ifpass) 
    WHERE ifpass = '1'
    ORDER BY updatetime DESC 
    LIMIT 0 , 30;
      

  23.   

    用like'%东丹庭院%'这样的形式在这列建立的索引也没有加快速度的作用,因为索引只对一个字段的前几个字符建立索引查找时用like'东丹庭院%'才能加快速度。如果你要想快速的使用你的那种形式的查询,只能建立全文检索,但是MySQL还不支持中文的全文索引,要想建立全文索引只能利用现有的全文检索技术进行。所以要解决很多数据量和多字段的快速查找,不是那么简单的
      

  24.   

    SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian
    FROM esf_sell FORCE INDEX (updatetime) IGNORE INDEX (ifpass) 
    WHERE ifpass = '1'
    ORDER BY updatetime DESC 
    LIMIT 0 , 30;执行不了,出现错误:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE INDEX (ifpass) 
    WHERE ifpass = '1'
    ORDER BY updatetime DESC 
    LIMIT 0 ,' at line 2 
      

  25.   

    SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatetime, UserType, iftuijian
    FROM esf_sell
    FORCE INDEX ( updatetime ) 
    WHERE ifpass = '1'
    ORDER BY updatetime DESC 
    LIMIT 0 , 30;显示行 0 - 29 (30 总计, 查询花费 0.2504 秒)
      

  26.   

    你的mysql 版本到是多少?mysql> SELECT id, xiaoqu, district, address, househx, housepay, jz_area, updatet
    ime, UserType, iftuijian
        -> FROM esf_sell_1 FORCE INDEX (updatetime) IGNORE INDEX (ifpass)
        -> WHERE ifpass = '1'
        -> ORDER BY updatetime DESC
        -> LIMIT 0 , 30;
    Empty set (0.00 sec)mysql>另外  查询花费 0.2504 秒 ,应该可以满足你一般要求了。
      

  27.   

    我建的索引是不是有问题呀,会不会太多呀,更新,添加,删除的比较频繁,就会慢,主要更新updatetime这个字段
      

  28.   

    我建的索引是不是有问题呀,会不会太多呀这个要根据你的操作来定。如果你的查询只有这么一种 当然只需要这个(updatetime) 就行了。
    很多问题没有办法直接回答你,除非你能提供更多,更详细的信息。更新,添加,删除的比较频繁,就会慢,主要更新updatetime这个字段
    索引多会对update/insert/delete有影响,但会提高select / update / delete 的速度。这个在不知道你的具体语句的时候,没办法做什么评论。
    问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧
      

  29.   

    Select id,district,xiaoqu,househx_s,househx_t,househx_w,jz_area,housepay,man,zjname,UserID,updatetime From esf_sell WHERE ifpass=1 AND UserType='中' AND district='新华区' GROUP BY UserID ORDER BY updatetime DESC LIMIT 30;
    还有,下面这些是搜索的条件:
    if ($uid) {
    $sqlsrch.=" AND UserID='$uid'";
    }
    if ($sq) {
    $sqlsrch.=" AND TradeCircle='$tradecircle'";
    }
    if ($zj) {
    $sqlsrch.=" AND UserType='$zj'";
    }
    if ($hx) {
    if ($hx>5) {
    $sqlsrch.=" AND househx_s>'$hx'";
    }else {
    $sqlsrch.=" AND househx_s='$hx'";
    }
    }
    if ($fwlx) {
    $sqlsrch.=" AND houselx='$fwlx'";
    }
    if ($min_jg) {
    $sqlsrch.=" AND housepay>=$min_jg";
    }
    if ($max_jg) {
    $sqlsrch.=" AND housepay<=$max_jg";
    }
    if ($min_area) {
    $sqlsrch.=" AND jz_area>=$min_area";
    }
    if ($max_area) {
    $sqlsrch.=" AND jz_area<=$max_area";
    }
    if ($addr) {
    $sqlsrch.=" AND (address  LIKE '%$addr%' OR xiaoqu LIKE '%$addr%')";
    }
      

  30.   

    UPDATE esf_sell SET updatetime={$timestamp} WHERE id IN ($id);
      

  31.   

    楼主啊,如果优化一个查询语句的步骤。1。分析你的SQL语句,你自己认为什么样的执行计划是最优的。
    2。用EXPALN来查看MYSQL的实际执行计划。
    3。对比分析,找出不正确的部分,然后进行人为调整,比如添加索引或优化你的SQL语句。不可能通过这个贴子这种方式帮你一个一个语句去分析,判断。关键是你自己要通过类似的方法找到解决问题的途径。
      

  32.   

    你的问题我曾经碰到过,数据表里面维护大约400W的数据,也是需要频率的插入、更新、删除,刚开始的时候,SQL执行的速度、性能都很不错,用了些时候呢,发现很简单的SQL执行也很慢,最后得出来的结论是因为频率的对数据表进行删除操作,导致磁盘空间产生了大量的碎片,每次进行插入操作的时候,需要花费大量的时间来找一个空白的地方把数据写进去,进而影响了MYSQL的性能。如果现有的表数据操作已经很慢了,可以考虑重新做一份数据出来,以后的数据,不要单条记录进行删除操作,不用的数据,先更新下标记,当作废弃状态,以后做数据整理的时候,一次性将废弃数据清理掉。