请教关于MySQL索引优化的问题

Question

表oso_user_ratings的结构如下
CREATE TABLE `oso_user_ratings` (
  `user_id` varchar(32) NOT NULL,
  `item_id` int(11) NOT NULL,
  `rating` decimal(14,4) NOT NULL DEFAULT '0.0000',
  KEY `item_id` (`item_id`),
  KEY `user_id` (`user_id`,`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8在应用中经常有这样的查询：
for ($i = 1; $i < 100000; $i++) {
    SELECT a.item_id AS item_id1,b.item_id AS item_id2,COUNT(*) AS times, SUM(a.rating-b.rating) AS rating FROM oso_user_ratings a,oso_user_ratings b WHERE 
a.item_id = '$i' AND b.item_id != a.item_id AND a.user_id=b.user_id GROUP BY a.item_id,b.item_id;
}EXPLAIN SELECT a.item_id AS item_id1,b.item_id AS item_id2,COUNT(*) AS times, SUM(a.rating-b.rating) AS rating FROM oso_user_ratings a,oso_user_ratings b WHERE a.item_id = '1' AND b.item_id != a.item_id AND a.user_id=b.user_id GROUP BY a.item_id,b.item_id;
explain上述的sql语句输出如下：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ALL user_id \N \N \N 2589649 Using temporary; Using filesort
1 SIMPLE a ref item_id,user_id user_id 102 music.b.user_id,const 10 Using where请问大家怎样对表oso_user_ratings建立合理的索引，才能使查询更加快(或者索引保持不变，改变sql语句的写法也可以)。目前表oso_user_ratings中有2百多万条记录，以后还会一直增加。我现在是每天晚上定时将sql语句中的写到缓存中(用的是Tokyo Tyrant+Tokyo Cabinet)，但是每次执行完毕需要好几个小时(往tt中保存很快，问题主要在MySQLP这儿)，对服务器压力很大，尽管是在晚上执行，但是压力仍然还是不小的，而且晚上也会有用户在使用，肯定还有优化余地的。希望大家帮帮我，谢谢了！

Answer 1

AND b.item_id != a.item_id这个条件去掉会不会快？
oso_user_ratings的user_id设置了索引吧？

Answer 2

那个条件不能去掉，否则满足不了需求。user_id有索引，是组合索引KEY `user_id` (`user_id`,`item_id`)

Answer 3

for ($i = 1; $i < 100000; $i++) {
SELECT a.item_id AS item_id1,b.item_id AS item_id2,COUNT(*) AS times, SUM(a.rating-b.rating) AS rating FROM oso_user_ratings a,oso_user_ratings b WHERE
a.item_id = '$i' AND b.item_id != a.item_id AND a.user_id=b.user_id GROUP BY a.item_id,b.item_id;
}有一点，我一直想搞清楚。
你居然要循环1w次
item 的数据是多大。
如果item 的数据量不是特别大。
把把item 读取出来。
然后在结果集上去写算法处理，不用每次都去查一次数据库。
10000次， 我的天！！！

Answer 4

我说的10万是保守数据了(是歌曲的数量)，已经十几万了，而且会一直增长的。item_id就是歌曲的id，是数字，比如1、650、20358等。我是每天晚上定时把sql语句中的数据写到Tokyo Tyrant里面，然后用户请求的时候是不读数据库的，这样可以减轻服务器的负担。

Answer 5

你不知道 MySQL 有一个 mysqldump 吗？

Answer 6

第一感觉:
需要建一个 a.user_id 的独立索引 第二感觉:
AND b.item_id != a.item_id
可以改成
AND b.item_id != '$i'第3感觉:
GROUP BY a.item_id,b.item_id;
可以改成
GROUP BY b.item_id;  (因为你同一次查询,a.item_id都一样)SELECT a.item_id AS item_id1,b.item_id AS item_id2,COUNT(*) AS times, SUM(a.rating-b.rating) AS rating FROM oso_user_ratings a,oso_user_ratings b WHERE 
a.item_id = '$i' AND b.item_id != a.item_id AND a.user_id=b.user_id GROUP BY a.item_id,b.item_id;第4感觉:
这样的一个sql是不是可以代替你上面的循环?
SELECT a.item_id AS item_id1, b.item_id AS item_id2,
   COUNT(*) AS times,  SUM(a.rating-b.rating) AS rating 
FROM oso_user_ratings a,   oso_user_ratings b 
WHERE a.user_id=b.user_id
  and b.item_id != a.item_id 
  and a.item_id between 1 and 100000  
GROUP BY a.item_id,b.item_id;
当然这个sql不会太快,但是和100000次循环比起来可能还好?

Answer 7

同意5楼：做个中间结果集的表 select 。 WHERE
 b.item_id != a.item_id AND a.user_id=b.user_id 
每次循环从中间结果集操作 应该快很多了，有！=索引用不上的
 

Answer 8

1.KEY `user_id` (`user_id`,`item_id`)，已经有一个组合索引包括user_id了，所以不需要再建了
2.不能那么改，那样改肯定是错误的。
3.这样也是不行的。
4.也不行，这么查询估计服务器的压力更大了，而且还要将所有的结果分开，因为我是根据变量$i的值来分另做缓存的。

Answer 9

关于2,3,4就不多说了，我差不多知道怎么解决了。关于组合索引，详细可以看下面的。
--------------------------------------肯定有人要问了，如果分别在vc_Name,vc_City,i_Age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率相同吧？嘿嘿，大不相同，远远低于我们的组合索引~~虽然此时有了三个索引，但MySQL只能用到其中的那个他认为似乎是最有效率的单列索引。    建立这样的组合索引，其实是相当于分别建立了
        vc_Name,vc_City,i_Age
        vc_Name,vc_City
        vc_Name
    这样的三个组合索引！http://www.sudu.cn/info/html/edu/20071219/114726.html
----------------------------------------
所以我建立的组合索引user_id，其实是相当于分另建立了
user_id,item_id
user_id
这样的两个索引
KEY `user_id` (`user_id`,`item_id`)如果你觉得我说得不对，你可以上网查查资料或问问朋友。