虚心求教，mysql 查询优化方案，难道无解？

yueliangdao0608

原帖由 sunnyfun 于 2008-12-3 10:18 发表
其实搞来搞去是楼主的数据模型设计的有问题，完全可以独立建个好友关系表，将in用表连接替换掉，简单的多对多罢了。

这个才是正解。

meizhe1981

原帖由 sunnyfun 于 2008-12-3 10:18 发表
其实搞来搞去是楼主的数据模型设计的有问题，完全可以独立建个好友关系表，将in用表连接替换掉，简单的多对多罢了。

这个问题是多对多关系没错，我这个系统有好友关系表呀，要不怎么取出某个用户的好友呢，望能详细描述您认为的设计模型，多谢指教

sunnyfun

原帖由 meizhe1981 于 2008-12-3 11:00 发表



这个问题是多对多关系没错，我这个系统有好友关系表呀，要不怎么取出某个用户的好友呢，望能详细描述您认为的设计模型，多谢指教

假设
用户表为 users, 主键为 userid
好友关系表为 friends 联合主键为 userid, friend_userid
则可以写作

select b.* from friends a, users b where a.userid = 某人id and a.friend_userid = b.userid order by b.inverse asc

然后优化，没啥特殊的东西吧

[ 本帖最后由 sunnyfun 于 2008-12-3 14:05 编辑 ]

meizhe1981

假设
用户表为 users, 主键为 userid
好友关系表为 friends 联合主键为 userid, friend_userid
则可以写作

select b.* from
friends a, users b
where a.userid = 某人id
and a.friend_userid = b ...

按照您说的办法我构建测试数据表结构如下:

1. 
   
2. /*
   
3. SQLyog Enterprise - MySQL GUI v6.0
   
4. Host - 5.1.26-rc-community : Database - test
   
5. *********************************************************************
   
6. Server version : 5.1.26-rc-community
   
7. */
   
8. 
   
9. /*!40101 SET NAMES utf8 */;
   
10. /*!40101 SET SQL_MODE=''*/;
    
11. create database if not exists `test`;
    
12. USE `test`;
    
13. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    
14. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
15. /*Table structure for table `friend` */
    
16. DROP TABLE IF EXISTS `friend`;
    
17. CREATE TABLE `friend` (
    
18.   `uid` bigint(20) NOT NULL,
    
19.   `fid` bigint(20) NOT NULL,
    
20.   PRIMARY KEY (`uid`,`fid`)
    
21. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
22. /*Data for the table `friend` */
    
23. insert  into `friend`(`uid`,`fid`) values (1,2),(1,3),(1,4),(1,5),(1,6);
    
24. /*Table structure for table `track` */
    
25. DROP TABLE IF EXISTS `track`;
    
26. CREATE TABLE `track` (
    
27.   `uid` bigint(20) NOT NULL,
    
28.   `trackid` bigint(20) NOT NULL,
    
29.   `inverse` bigint(20) DEFAULT NULL,
    
30.   PRIMARY KEY (`uid`,`trackid`)
    
31. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
32. /*Data for the table `track` */
    
33. insert  into `track`(`uid`,`trackid`,`inverse`) values (2,1,-10),(2,2,-11),(2,3,-12),(3,1,-13),(3,4,-14),(3,6,-15),(4,1,-8),(5,6,-7);
    
34. /*Table structure for table `user` */
    
35. DROP TABLE IF EXISTS `user`;
    
36. CREATE TABLE `user` (
    
37.   `uid` bigint(20) NOT NULL AUTO_INCREMENT,
    
38.   PRIMARY KEY (`uid`)
    
39. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
    
40. /*Data for the table `user` */
    
41. insert  into `user`(`uid`) values (1),(2),(3),(4),(5),(6);
    
42. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    
43. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

复制代码

查询语句如下:

select t.uid,t.trackid from user as u, friend as f, track as t where u.uid = f.uid and f.fid = t.uid order by t.inverse;

explain结果如下:

1. 
   
2.    id  select_type  table   type    possible_keys  key      key_len  ref           rows  Extra                                       
   
3. ------  -----------  ------  ------  -------------  -------  -------  ----------  ------  --------------------------------------------
   
4.      1  SIMPLE       f       index   PRIMARY        PRIMARY  16       (NULL)          5  Using index; Using temporary; Using filesort
   
5.      1  SIMPLE       u       eq_ref  PRIMARY       PRIMARY  8        test.f.uid       1  Using index                                 
   
6.      1  SIMPLE       t       ref     PRIMARY         PRIMARY  8        test.f.fid        1                                             
   

复制代码

如何优化掉那个filesort？请指教

simeiren

explain结果中friend as f 表的 ref 列是NULL 啊；
因为你只建了PRIMARY KEY (`uid`,`fid`)这个组合key，
所以f.fid = t.uid 这里的f.fid 用不到索引；

[ 本帖最后由 simeiren 于 2008-12-3 18:19 编辑 ]

meizhe1981

原帖由 simeiren 于 2008-12-3 18:17 发表
explain结果中friend as f 表的 ref 列是NULL 啊；
因为你只建了PRIMARY KEY (`uid`,`fid`)这个组合key，
所以f.fid = t.uid 这里的f.fid 用不到索引；

那您有何高见?

sunnyfun

1. 
   
2. --
   
3. -- 数据库: `test`
   
4. --
   
5. 
   
6. -- --------------------------------------------------------
   
7. 
   
8. --
   
9. -- 表的结构 `friend`
   
10. --
    
11. 
    
12. DROP TABLE IF EXISTS `friend`;
    
13. CREATE TABLE `friend` (
    
14.   `uid` bigint(20) NOT NULL default '0',
    
15.   `fid` bigint(20) NOT NULL default '0',
    
16.   PRIMARY KEY  (`uid`,`fid`),
    
17.   KEY `fid` (`fid`)
    
18. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
19. 
    
20. --
    
21. -- 表中的数据 `friend`
    
22. --
    
23. 
    
24. INSERT INTO `friend` VALUES (1, 2);
    
25. INSERT INTO `friend` VALUES (1, 3);
    
26. INSERT INTO `friend` VALUES (1, 4);
    
27. INSERT INTO `friend` VALUES (1, 5);
    
28. INSERT INTO `friend` VALUES (1, 6);
    
29. 
    
30. -- --------------------------------------------------------
    
31. 
    
32. --
    
33. -- 表的结构 `track`
    
34. --
    
35. 
    
36. DROP TABLE IF EXISTS `track`;
    
37. CREATE TABLE `track` (
    
38.   `uid` bigint(20) NOT NULL default '0',
    
39.   `trackid` bigint(20) NOT NULL default '0',
    
40.   `inverse` bigint(20) default NULL,
    
41.   KEY `inverse` (`inverse`)
    
42. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
43. 
    
44. --
    
45. -- 表中的数据 `track`
    
46. --
    
47. 
    
48. INSERT INTO `track` VALUES (2, 1, -10);
    
49. INSERT INTO `track` VALUES (2, 2, -11);
    
50. INSERT INTO `track` VALUES (2, 3, -12);
    
51. INSERT INTO `track` VALUES (3, 1, -13);
    
52. INSERT INTO `track` VALUES (3, 4, -14);
    
53. INSERT INTO `track` VALUES (3, 6, -15);
    
54. INSERT INTO `track` VALUES (4, 1, -8);
    
55. INSERT INTO `track` VALUES (5, 6, -7);
    
56. 
    
57. -- --------------------------------------------------------
    
58. 
    
59. --
    
60. -- 表的结构 `user`
    
61. --
    
62. 
    
63. DROP TABLE IF EXISTS `user`;
    
64. CREATE TABLE `user` (
    
65.   `uid` bigint(20) NOT NULL auto_increment,
    
66.   PRIMARY KEY  (`uid`)
    
67. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
    
68. 
    
69. --
    
70. -- 表中的数据 `user`
    
71. --
    
72. 
    
73. INSERT INTO `user` VALUES (1);
    
74. INSERT INTO `user` VALUES (2);
    
75. INSERT INTO `user` VALUES (3);
    
76. INSERT INTO `user` VALUES (4);
    
77. INSERT INTO `user` VALUES (5);
    
78. INSERT INTO `user` VALUES (6);
    
79. 
    
80. -- --------------------------------------------------------
    
81. 
    
82. EXPLAIN SELECT t.uid, t.trackid
    
83. FROM user AS u, friend AS f, track AS t
    
84. WHERE u.uid = f.uid
    
85. AND f.fid = t.uid
    
86. ORDER BY t.inverse;
    

复制代码

+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | t     | index  | NULL          | inverse |       9 | NULL       |    8 |             |
|  1 | SIMPLE      | f     | ref    | PRIMARY,fid   | fid     |       8 | test.t.uid |    1 | Using index |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY |       8 | test.f.uid |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
3 rows in set (0.00 sec)


[ 本帖最后由 sunnyfun 于 2008-12-4 09:13 编辑 ]

tomac_cu

原帖由 meizhe1981 于 2008-12-1 14:29 发表
这张表里存储了所有用户的所有entryid,inverse 记录了一个时间按戳乘 -1 的整数值，也就是一个倒排索引字段，每个用户都要取出自己好友的entryid,并按时间倒排序

inverse 的值既然是有序的，为什么不用一个自增的字段来实现呢？用这个自增的字段来作主键，排序用时就很少了。

bai2006

select * from t where id=1
union
select * from t where id=2;
union写错没？
再针对union的结果集进行order by a
建立复合索引（a,id）

nepalese

用USD_INDEX强制mysql 用index看看？