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

meizhe1981

个人观点如下:

1.问题定义

  查询类型属于:range + order by.
  mysql手册中有如下文字:
  在某些情况下，MySQL不能使用索引来解决ORDER BY，尽管它仍然使用索引来找到匹配WHERE子句的行。
  这些情况包括：
  1.对不同的关键字使用ORDER BY：
    SELECT * FROM t1 ORDER BY key1, key2；
  2.对关键字的非连续元素使用ORDER BY：
    SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2；
  3.混合ASC和DESC：
    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
  4.用于查询行的关键字与ORDER BY中所使用的不相同：
    SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
  5.你正联接许多表，并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表.
  6.有不同的ORDER BY和GROUP BY表达式。
  7.使用的表索引的类型不能按顺序保存行。例如，对于HEAP表的HASH索引情况即如此.
  
  总结:此问题属于手册中定义的第二点和第三点，所以可得结论是用SQL有化的方式无解。

2.变通方法
   可以参照搜索引擎的办法，采用倒排表类似的机制，每个用户的所有好友作为一个key来存储好友，存储介质不限。取出数据时直接取出即可。

   存在问题:
   1.数据的更新维护相对复杂，每个操作对应的更新次数如下:
     增加好友          2
     删除好友          2
     增加Entry        n(好友个数)
     删除Entry        n(好友个数)
   
   2.数据冗余相对较大
     这个问题可以忽略不计，因为这个只是记录id一类的东西

   总结:这种办法最关键的问题点在问题一上，如果一个人的好友众多就很麻烦了，比如这个人有上万个好友。....实际情况也不是没有可能，
            比如某个明星什么的


以上是我个人的一些看法，欢迎拍砖！&_^


   

yueliangdao0608

分析的不错。

yueliangdao0608

原帖由 meizhe1981 于 2008-12-2 09:33 发表
这个方法依然无法避免filesort,不信你可以试试

sunnyfun 兄弟的方法是可以避免FILESORT的。


修改SQL为：
(select * from test where userid = 1 order by inverse asc)
union
(select * from test where userid = 2 order by inverse asc)


加一个索引：

alter table test add key idx_userid_inverse (userid,inverse);

如果单纯的要走索引的话，完全可以用。
不过缺点就是要扫描两次表，所以不可取。

meizhe1981

这样修改是不可以的，因为有个总的limit,而且即便没有limit排序的顺序也不对，查询取出的应该是所有数据的排序顺序

修改前
(select * from test where userid = 2000010000)
union
(select * from test where userid = 2000010022)
order by inverse asc

对应结果
2000010022  -20081126165312
2000010022  -20081126163618
2000010022  -20081125184230
2000010022  -20081125180718
2000010022  -20081121150810
2000010022  -20081121100156
2000010022  -20081120113305
2000010000  -20081120102813
2000010000  -20081120102600
2000010022  -20081120101429
2000010022  -20081120095446
2000010000  -20081120091249
2000010000  -20081120001628
2000010000  -20081120001027
2000010022  -20081118121044

修改后
(select * from test where userid = 2000010000 order by inverse asc)
union
(select * from test where userid = 2000010022 order by inverse asc)

对应结果
2000010000  -20081120102813
2000010000  -20081120102600
2000010000  -20081120091249
2000010000  -20081120001628
2000010000  -20081120001027
2000010022  -20081126165312
2000010022  -20081126163618
2000010022  -20081125184230
2000010022  -20081125180718
2000010022  -20081121150810
2000010022  -20081121100156
2000010022  -20081120113305
2000010022  -20081120101429
2000010022  -20081120095446
2000010022  -20081118121044

[ 本帖最后由 meizhe1981 于 2008-12-2 10:27 编辑 ]

ruochen

原帖由 meizhe1981 于 2008-12-2 10:04 发表
个人观点如下:

1.问题定义

  查询类型属于:range + order by.
  mysql手册中有如下文字:
  在某些情况下，MySQL不能使用索引来解决ORDER BY，尽管它仍然使用索引来找到匹配WHERE子句的行。
  这些情况 ...

学习

gogo407

原帖由 meizhe1981 于 2008-12-2 10:04 发表
个人观点如下:

1.问题定义

  查询类型属于:range + order by.
  mysql手册中有如下文字:
  在某些情况下，MySQL不能使用索引来解决ORDER BY，尽管它仍然使用索引来找到匹配WHERE子句的行。
  这些情况 ...

分析的不错，但应该根据应用有更合适的优化方法：）

talen-t

原帖由 meizhe1981 于 2008-12-2 09:33 发表
这个方法依然无法避免filesort,不信你可以试试

那是你没有强制索引!

root:test> explain select * from test force index(i_inverse) where userid in (1,2) order by inverse asc;   
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | i_inverse | 8       | NULL |    7 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+

就这个SQL原型而言,需要分开建,不可能又兼顾filesort的压力,又兼顾查询结果集的压力,只能看需求取其一!
否则就是改业务,改SQL

[ 本帖最后由 talen-t 于 2008-12-2 13:05 编辑 ]

meizhe1981

是呀，这个我已经总结过了，欢迎提出其他的解决方案，非mysql的，广而言之 非关系型数据的其他解决方案

sunnyfun

原帖由 meizhe1981 于 2008-12-2 09:33 发表
这个方法依然无法避免filesort,不信你可以试试

我的前提是搜索出的数据集比较小的情况下

对 userid 和 inverse 建组合索引的话

select * from test where userid = 1 order by inverse asc
不会扫表

select * from test where userid = 2 order by inverse asc
也不会扫表

扫的是合并两者的临时表(系统自动生成)，在数据集比较小的时候，临时表在内存中，扫表也是非常快的（万把条应该没问题，何况不可能人人万把条）。

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

sunnyfun

其实搞来搞去是楼主的数据模型设计的有问题，完全可以独立建个好友关系表，将in用表连接替换掉，简单的多对多罢了。