WHERE + IN + ORDER BY + LIMIT组合的索引优化

langwang0070

WHERE + IN + ORDER BY + LIMIT组合的索引优化:

sql形式：

SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX] IN ([value1],[value2],...) ORDER BY[sort] LIMIT [offset],[LIMIT];


此sql一般会出现 using filesort，怎样优化可以避免呢？

ruochen

show create table
in的列表多不多？

limit一般用子查询来优化

langwang0070

回复 2# ruochen


    in最多4项，是int型。

   limit 用子查询？愿闻其详

ruochen

google "MySQL 分页优化"应该能找到几篇很不错的文章

ruochen

CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`b` int(11) NOT NULL,
`c` datetime NOT NULL,
`d` varchar(1000) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_b_c` (`b`,`c`),
KEY `idx_b_id` (`b`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


SQL1：select SQL_NO_CACHE * from test force index(idx_b_c) where b=1 order by c desc limit 2000,10

------------->

SQL2：select SQL_NO_CACHE * from test, (select SQL_NO_CACHE id from test force index(idx_b_c_id) where b=1 order by c desc limit 2000,10) temp where test.id=temp.id

cenalulu

同意ruochen，这语句和分页的需求差不多。大概有这么几种办法

1。 把 in 转做成 join
2。 把 in 拆分成4个语句
3。 先把 Limit n,m 的 primary id 通过语句查出来，筛选出正确结果集后再order by
4。 末页的选取不要用asc 改用desc
等等

ruochen

同意ruochen，这语句和分页的需求差不多。大概有这么几种办法

1。 把 in 转做成 join
2。 把 in 拆分成 ...
cenalulu 发表于 2010-08-11 20:52

     嗯，这都是工作中常用的技巧
   另外补充一点，如果有datetime类的字段，有时候还可以用这个做相当于“等值”的替换，但是要对具体的业务

langwang0070

上面提到的sql语句的思路是这样的吧：先获取分页的id然后根据id再查数据。   这个可能还是会有 using filesort的，后面我测试下。


将in的东西拆分，这个我测试过，效果不是很理想，容易造成大量的索引扫描。

对于大分页的情况需要和程序结合来做，光靠sql应该是不行的。

ruochen

先按sql2的模式将你的改写下
看看性能怎么样---注意index的情况

langwang0070

测试了下，用子查询效果不是很理想啊。不过我用内联查询，分摊排序，然后重建索引效果还可以。不过只是本机测试，线上环境不一样可能结果也会有差异的。