mysql自动选择了错的索引？

一个抢楼帖内容页今晚突然出现数据库查询超时导致连接数过多，mysql锁表、php超时、整站挂掉。
之前有至少三个抢楼帖超过100w回帖均未出现任何问题，而这次发现这个帖子只有12w回帖。

导致锁表的语句为：

1. SELECT p.* FROM pre_forum_post p WHERE p.tid='*' AND p.invisible='0' ORDER BY p.dateline LIMIT 0, 20;

复制代码

经过explain后发现，索引使用了dateline：

1. mysql> explain SELECT p.* FROM pre_forum_post p WHERE p.tid='*' AND p.invisible='0' ORDER BY p.dateline LIMIT 0, 20;
   
2. +----+-------------+-------+-------+--------------------------------------------+----------+---------+------+-------+-------------+
   
3. | id | select_type | table | type  | possible_keys                              | key      | key_len | ref  | rows  | Extra       |
   
4. +----+-------------+-------+-------+--------------------------------------------+----------+---------+------+-------+-------------+
   
5. |  1 | SIMPLE      | p     | index | invisible,displayorder,first | dateline | 4       | NULL | 20294 | Using where |
   
6. +----+-------------+-------+-------+--------------------------------------------+----------+---------+------+-------+-------------+

复制代码

在该条语句位置加入ignore index(dateline)后，索引使用了displayorder，秒查：

1. mysql> explain SELECT p.* FROM pre_forum_post p ignore index(dateline) WHERE p.tid='*' AND p.invisible='0' ORDER BY p.dateline LIMIT 0, 20;
   
2. +----+-------------+-------+-------+--------------------------------------------+--------------+---------+------+-------+-------------+
   
3. | id | select_type | table | type  | possible_keys                              | key          | key_len | ref  | rows  | Extra       |
   
4. +----+-------------+-------+-------+--------------------------------------------+--------------+---------+------+-------+-------------+
   
5. |  1 | SIMPLE      | p     | range | invisible,displayorder,first| displayorder | 4       | NULL | 32612 | Using where |
   
6. +----+-------------+-------+-------+--------------------------------------------+--------------+---------+------+-------+-------------+

复制代码

生产环境，不敢重现bug。
有个比较二的问题想请教康胜的技术人员：在建表的时候将上述两个索引位置调换一下能否避免此类情况发生？

问题已经收到，稍后我们核查后再给您答复。

DZ连SQL都写不好，还不用说索引优化了

真正的抢楼帖不该出现这样SQL才对，应该会用到position字段。检查一下吧。

mysql会根据SQL和数据大小自作主张选择索引。

m.king 发表于 2012-11-29 09:07
真正的抢楼帖不该出现这样SQL才对，应该会用到position字段。检查一下吧。

mysql会根据SQL和数据大小 ...

mysql会根据SQL和数据大小自作主张选择索引

“自作主张”这个词含有主观色彩～
准确说是mysql的查询优化器会根据查询条件，索引情况来选择合适的CBO方案
当然实际上mysql的这个优化器还是比较弱智的——绝大多数情况只会选择PK索引而忽视其他高效索引

m.king 发表于 2012-11-29 09:07
真正的抢楼帖不该出现这样SQL才对，应该会用到position字段。检查一下吧。

mysql会根据SQL和数据大小 ...

回头看了一下，这个帖子确实不是抢楼帖，最近抢楼帖比较多，两个帖子加起来300w+回复，我把这个帖子搞混了。

那就确定是mysql自己的问题。
将索引的顺序改那么一下会不会尽量减少此类问题的发生？

mark35 发表于 2012-11-29 13:54
“自作主张”这个词含有主观色彩～
准确说是mysql的查询优化器会根据查询条件，索引情况来选择合适的C ...

这么多年头一次碰到这样莫名其妙的问题。本来不想说啥，这个真不是pk索引。

sanalex 发表于 2012-11-29 17:58
回头看了一下，这个帖子确实不是抢楼帖，最近抢楼帖比较多，两个帖子加起来300w+回复，我把这个帖子搞混了 ...

两个主题共300W的回帖？  不慢才怪～

sanalex 发表于 2012-11-29 17:59
这么多年头一次碰到这样莫名其妙的问题。本来不想说啥，这个真不是pk索引。

我的意思是说：如果存在使用PK索引的机会那么mysql就只会使用它而（基本）不会考虑使用其他索引；如果没机会使用PK索引，那么mysql查询优化器在面对多个可选索引情况下的所做选择往往是机械而性能较差的

就你这个例子看，共有2条索引可选

因为ORDER BY操作也可以通过索引来提高效率，所以mysql选择了ORDER BY需要的dateline字段索引而不是可能更高效（以楼主这个例子是更加高效）的displayorder复合索引。

那么为啥它不选择displayorder 索引呢？
这是因为displayorder是tid, invisible, dateline三个字段的复合索引。虽然查询条件可以使用索引中tid和invisible，但是这个结果可能会因为结果集很大而导致后续的ORDER BY dateline操作在无索引可查的情况下去访问数据页于是性能恶化。mysql没有足够充分的table/index statistics来提供计划抉择，所以为了避免这种情况下的性能恶化，mysql选择了简单中庸的方式：以ORDER BY的字段作为抉择索引字段。

这就是mysql的查询优化器的弱点：它没有一个表、索引统计机制来提供足够的统计数据供程序分析计算出各种查询规划方案的开销并以此选择出最优路径。所以称之为查询优化器(query optimizer)而不是查询规划器(query planner)。

当然，这也不完全是mysql的问题。因为这个SQL本来就存在一定问题：如果不人工介入做SQL优化或者索引优化那么在大表上查询的查询效率是比较低

我来演示下在大数据情况下不能选择合适索引的性能差别

dz72, 找了回帖数量为15661的主题执行楼主的SQL

1. SELECT p.* FROM cdb_posts p WHERE p.tid='149851' AND p.invisible='0' ORDER BY p.dateline LIMIT 20;

复制代码

1、只有dateline索引，无displayorder复合索引


第一次查询时间是 3900毫秒, 采用了dateline索引
第二次查询计划截图，时间是2400毫秒：


给部分数据量大的分表添加复合索引

1. 
   
2. CREATE INDEX p122_1 ON posts_122_1 (tid, invisible, dateline);
   
3. CREATE INDEX p122_2 ON posts_122_2 (tid, invisible, dateline);
   
4. CREATE INDEX p117_1 ON posts_117_1 (tid, invisible, dateline);
   
5. CREATE INDEX p117_2 ON posts_117_2 (tid, invisible, dateline);
   
6. CREATE INDEX p230_1 ON posts_230_1 (tid, invisible, dateline);
   
7. CREATE INDEX p230_2 ON posts_230_2 (tid, invisible, dateline);
   
8. CREATE INDEX p24_1 ON posts_24_1 (tid, invisible, dateline);
   
9. CREATE INDEX p24_2 ON posts_24_2 (tid, invisible, dateline);
   
10. CREATE INDEX p25_1 ON posts_25_1 (tid, invisible, dateline);
    
11. CREATE INDEX p25_2 ON posts_25_2 (tid, invisible, dateline);
    
12. CREATE INDEX p26_1 ON posts_26_1 (tid, invisible, dateline);
    
13. CREATE INDEX p26_2 ON posts_26_2 (tid, invisible, dateline);
    
14. CREATE INDEX p27_1 ON posts_27_1 (tid, invisible, dateline);
    
15. CREATE INDEX p27_2 ON posts_27_2 (tid, invisible, dateline);
    

复制代码

索引截图


第一次查询计划截图，时间是 7.6毫秒 ，规划器采用了displayorder索引


第二次查询结果，时间是 0.7毫秒


测试数据来自这个例子 Discuz能有多快？ 见识下postgreSQL的强悍，对比下mysql的低能

这不过是在1万条记录上的差别，虽然都走了索引但不同索引的效率差别实在太大。如果数据量更大，并且是大OFFSET分页情况，差距会更加显著。