百万级数据查询、排序使用索引遇到的问题，高手指点下

lichail

100万+条数据，执行如下SQL语句

SELECT * FROM [TABLE]

WHERE

[column1] = [value1] AND  

[column2] = [value2] AND

[column3] = [value3] AND

ORDER BY [id] DESC

LIMIT 0, 10;

其中列column1,column2,column3,id的值都是数字，id是主键，取出0,10共10条数据，按照主键降序排列。

创建了索引myindex(column1,column2,column3,id)后，这个sql语句的运行时间是0.00x秒级，速度理想。

但实际中还需要用到以下查询

1.SELECT * FROM [TABLE] WHERE [column1] = [value1] AND [column2] = [value2] ORDER BY [id] DESC LIMIT0, 10;

2.SELECT * FROM [TABLE] WHERE [column1] = [value1]  ORDER BY [id] DESC LIMIT0, 10;

1的执行时间在0.x秒级，速度明显慢了；2的执行时间在x秒级，速度慢，已经进了mysql的slow-log日志了。

explain sql发现查询的时候都用到了索引myindex(column1,column2,column3,id)怎么执行速度差别这么大？

真不知道怎么解决了，高手帮忙啊！

horizonhyg

能把每条语句的explain结果发来看下么

horizonhyg

回复 1# lichail


    第一条语句是覆盖索引，所以很快，不建议索引加上主键，本来这种二级索引就是包含主键的。
    第二条和第三条现在还看不出来，估计是扫描次数太多?发一下explain结果

lichail

我又创建了2个索引，myindex2(column1,column2,id) myindex3(column1,id)
执行这条语句的时候，强制使用myindex2结果执行速度快了 SELECT * FROM [TABLE] FORCE INDEX (myindex2) WHERE [column1] = [value1] AND [column2] = [value2] ORDER BY [id] DESC LIMIT0, 10;
同样的这个执行速度也快了SELECT * FROM [TABLE] FORCE INDEX (myindex3) WHERE [column1] = [value1]  ORDER BY [id] DESC LIMIT0, 10;

但是这样索引多了，对性能有影响，也增加了sql语句的复杂度，不理想

lichail

这里附上我explain的实验记录，一些地方p掉了，不影响查看。1 2 3 分别对应帖子里的三条sql语句

horizonhyg

看了sql语句，有一些想法，仅作讨论：
1、这几个字段的选择性都非常低，就像第三条，扫描90多w行，这个索引是没有必要建的，没有用，请确认user_id有索引，我觉得使用这个索引比你那个好
2、即便是第一条，见了索引，也不是覆盖索引，这几个索引效果都不好，既然按照user_id排序，不防将user_id放在前面，mysql使用最左索引。
3、第二条使用了file-sort,不慢都不行了，总觉的很奇怪，我觉得这几个都用user_id会比你那些快，试试将user-id放在最左边建索引。

lichail

我把所有的索引去掉了，只留下主索引user_id然后运行1 2 3条语句，结果有点复杂了。三条sql语句都有快和慢的时候。根据mysql使用最左索引的原则，将user_id放在前面，结果和只留下主索引user_id一样。

如果查询结果集多，速度就快，0.00x秒；如果查询结果集少，速度就慢，0.x秒。

从explain结果看，从100多万条数据里找几十万数据，快，但是找几千，几百条就慢。

如果查找的结果集只有几十个，时间甚至达到了x秒级，进入了slow-log日志。

explain的结果：

id        select_type        table        type                possible_keys        key                key_len        ref                rows                Extra
1        SIMPLE                xxxx         index        NULL                PRIMARY        3                NULL        1273856        Using where

这样肯定不行，扫描100多W去找，如果搜索结果集很小，速度会很慢，如果以后数据更多了，就更不行。

还是要创建索引，或者改变表的结构，真是愁死了

horizonhyg

回复 7# lichail


    对于这种选择性很低的字段，本来就不适合建立索引，但是如果使用，尽量将选择性最高的放在前面，使用覆盖索引，我想了一下，如果将user_id放在前面确实不行，因为查询条件没有，他会遍历所有记录，即便是在内存里。你可以打开profiling看一下时间究竟是消耗在哪。

RogerZhuo

回复 7# lichail
1)确认你的表使用的engine, 因为myisam与innodb的index处理，差别还是很大的；如果是mysiam而且是fixed类型，速度应该还是不错的。
2)还有我觉得你的三个字段应该选择率不高， 不同值数/行总数,就像前面那位兄弟所说的那样，这样创建索引意义不大
3)如果是inodb,你的三个查询都不是覆盖索引，都会回表，看能不能去掉不要的字段，
4）没有必要把id加入到那个组合索引中，

RogerZhuo

比较的值最好写成数字，否则可能导致转化，有时不会使用index.
row =1不要写成row='1'