请问这条语句怎么样写才能更大的利用索引，而不是全表扫描

Question

我有一个联合索引是这么建立的： typeid,verify,id,hits现在遇到一个问题，如果我在 where 条件中对 typeid 使用 in 的话，效率很慢，要全表扫描比如 select * from table where verify = 1 and typeid in (1,2,3) order by id desc limit 10非常慢，尤其是分页的时候
select * from table where verify = 1 and typeid in (1,2,3) order by id desc limit 100000,10
的时候，简直是场灾难，这个问题应该怎么解决呢？这条sql应该怎么写最好

Answer 1

explain了一把，发现会用到索引。坐等其他同学回答。

Answer 2

把你EXPLAIN的结果用文本贴出来
还有你的 show index from table 

Answer 3

explain的结果mysql> explain select * from tiyuxiu_art where verify=1 and typeid in (7,17,18)  order by id desc limit 9;
+----+-------------+-------------+-------+---------------+--------+---------+------+-------+-----------------------------+
| id | select_type | table       | type  | possible_keys | key    | key_len | ref  | rows  | Extra                       |
+----+-------------+-------------+-------+---------------+--------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | tiyuxiu_art | range | typeid        | typeid | 5       | NULL | 81072 | Using where; Using filesort |
+----+-------------+-------------+-------+---------------+--------+---------+------+-------+-----------------------------+
1 row in set (16.64 sec)
以下是索引：
mysql> show index from tiyuxiu_art
    -> ;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tiyuxiu_art |          0 | PRIMARY  |            1 | id          | A         |      153122 |     NULL | NULL   |      | BTREE      |         |
| tiyuxiu_art |          0 | url      |            1 | url         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| tiyuxiu_art |          0 | initial  |            1 | initial     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| tiyuxiu_art |          1 | img      |            1 | img         | A         |      153122 |     NULL | NULL   | YES  | BTREE      |         |
| tiyuxiu_art |          1 | hot      |            1 | hot         | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| tiyuxiu_art |          1 | typeid   |            1 | typeid      | A         |          10 |     NULL | NULL   |      | BTREE      |         |
| tiyuxiu_art |          1 | typeid   |            2 | verify      | A         |          10 |     NULL | NULL   |      | BTREE      |         |
| tiyuxiu_art |          1 | typeid   |            3 | id          | A         |      153122 |     NULL | NULL   |      | BTREE      |         |
| tiyuxiu_art |          1 | typeid   |            4 | hits        | A         |      153122 |     NULL | NULL   |      | BTREE      |         |
| tiyuxiu_art |          1 | hits     |            1 | hits        | A         |         195 |     NULL | NULL   |      | BTREE      |         |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
用到索引了，但是还是要Using filesort
我在网上看资料说 in 很费资源，像我这个能用什么语句来替代？

Answer 4

没什么可以优化的了。导致速度慢的原因主要是order by id desc你的表中153122条记录的typeid只有10种不同的值，verify则根本不起作用，所有记录都是一样的。然后查询出符合的结果有81072之多，显然需要用Using filesort

Answer 5

半数以上的记录符合条件，这样你的limit 9;只需要9条记录的话，不如不用索引。
试一下不用索引时的速度。

Answer 6

谢谢你的回复是不是我对索引的理解有误？ ID 也在索引里啊
MYSQL不应该这样处理吗：先通过 typeid 和 verify 进行筛选，然后 id 也在索引里，直接按索引里的id进行排序不就行了？还有并不是每条sql都有半数以上的记录符合条件，有少的，但是碰到多的的时候，会卡住，硬盘太烂了还有一点，就是如果我不用 in ，而是用 typeid=的时候，就直接用索引
比如mysql> explain select * from tiyuxiu_art where verify=1 and  typeid = 2 order by id DESC limit 10 ;
+----+-------------+-------------+------+---------------+--------+---------+-------------+------+-------------+
| id | select_type | table       | type | possible_keys | key    | key_len | ref         | rows | Extra       |
+----+-------------+-------------+------+---------------+--------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tiyuxiu_art | ref  | typeid        | typeid | 5       | const,const | 8178 | Using where |
+----+-------------+-------------+------+---------------+--------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

Answer 7

 verify=1 and  typeid = 2 order by id 这个可以完全用你的索引啊。你的索引中
| tiyuxiu_art |          1 | typeid   |            1 | typeid      | A         |          10 |     NULL | NULL   |      | BTREE      |         |
| tiyuxiu_art |          1 | typeid   |            2 | verify      | A         |          10 |     NULL | NULL   |      | BTREE      |         |
| tiyuxiu_art |          1 | typeid   |            3 | id          | A         |      153122 |     NULL | NULL   |      | BTREE      |         |
| tiyuxiu_art |          1 | typeid   |            4 | hits        | A         |      153122 |     NULL | NULL   |      | BTREE      |         |
按 verify=1 and  typeid = 2找到记录后，它的排序正好就是ID。
但如果你提  verify=1 and (  typeid = 2 or   typeid = 3) 则你必须把2和3的都取出后，进行统一排序才能得出结果。

Answer 8

你这问题是： 偶尔结果集太大怎么办？
1 别用大结果集，别用in。或在in的那条sql语句前面加上，增大 排序缓冲区 的语句。sort_buffer_size，read_rnd_buffer_size  ---试着调整这两个变量，看看效果如何
2 想别的办法，如 加大内存临时表，或把sql结果缓存起来。