送分!100分,求助海量数据的mysql索引问题!

我有一数据表上百万条记录,用的是mysql数据库,我建的索引查询需要3-5秒时间,太长了,请求优化..数据表为两打关联!一个是用户表user,一个是记录表list!user表与list表以 username关联,执行查询条件的是list表的字段!表的格式如下:user表id        username
================================
1         seasea
2         cccccccc
................
list表id username classa classb title city show  image time
=========================================================
1  seasea   1      10     ....  12   1     图片  发布时间
...................
然后所执行的sql语句最简单的查询也需要user表与list表的username关联与list表的show字段为1
select a.*,b.* From user a,list b where a.username = b.username and b.show = 1 order by b.time desc limit 0,20以上为最基本都要执行的sql查询语句当用户选字条件进行查询时,有可能出现的情况如下:1. 选择了list表的大类与小类classa,classb,选中关键字查询title,选择了地区city
select a.*,b.* From user a,list b where a.username = b.username and b.show = 1 and b.classa = 1 and b.classb = 10 and b.title like '%ss%' and b.city = 12 order by b.time desc limit 0,202.只选择了其它的一个字段进行过滤,如大类,地区等....
这个时候上百万的记录,如果索引建不好真是太慢了,请求各位大虾帮忙,需要建哪些索引,怎么样的优化....
万分感激!

解决方案 »

  1.   

    以下Where条件感觉需要优化
    where a.username = b.username and b.show = 1 and b.classa = 1 and b.classb = 10 and b.title like '%ss%' and b.city = 12 1. 使用username作为关联效率不高, 如果可以的话改为使用userid, 符合数据库设计的范式标准. 如果不可改, 需要将两个username字段都建立索引, 加快关联时间2. 对数据分布比较广的而且条件使用也比较频繁的字段加上索引3. 如果list表更新很频繁, 可以使用定期更新统计信息(类似UPDATE STATISTICS)
      

  2.   

    b.title like '%ss%'   一下就完了，模糊查询，数据量大了效率就是不行。试试全文检索吧 lucene http://www.google.com/search?hl=zh-CN&newwindow=1&rls=XNLA%2CXNLA%3A2006-31%2CXNLA%3Azh-CN&q=php+lucene&lr=
      

  3.   

    b.title like '%sss%'这个一定会有用户用到的,但是经常用到的肯定是大类,小类,地区因为原来的数据库设计不是我设计的,用的是username作为关联,现在数据都几十万了,当然没法改,只能用username,我两个表的username都建了关联了...to:TinyJimmy
       2. 对数据分布比较广的而且条件使用也比较频繁的字段加上索引
       你的意思是对每个字段做索引还是取条件用得比较多的做组合索引..关键是这个索引要如何建为更好....
      

  4.   

    1.
    不要使用 select a.*,b.* 
    而是根据需要列举出所需字段2.
    b.title like '%ss%'  不要固化这样用,把需求拆成四种情况,在界面上用开关让用户选择:
       b.title ='ss'
       b.title like '%ss'
       b.title like 'ss%'
       b.title like '%ss%'
      

  5.   

    1. 使用username作为关联效率不高, 如果可以的话改为使用userid, 符合数据库设计的范式标准. 如果不可改, 需要将两个username字段都建立索引, 加快关联时间2. 对数据分布比较广的而且条件使用也比较频繁的字段加上索引3. 如果list表更新很频繁, 可以使用定期更新统计信息(类似UPDATE STATISTICS)