数据库 关于数据库索引的一个问题: order by...limit 类型 [已解决]

daqing · 2012年03月16日 · 最后由 daqing 回复于 2012年03月16日 · 3163 次阅读

我的query是这样的: SELECT * FROM topics ORDER BY updated_at DESC LIMIT 15

现在已经对updated_at字段加了索引,但是今天分别在MySQL 5.5 和 PostgreSQL 9.1上面测试时发现,PostgreSQL会用到索引,而MySQL却不会,这个怎么回事呢?

MySQL:

mysql> explain select * from topics order by updated_at desc limit 15;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | topics | ALL  | NULL          | NULL | NULL    | NULL |  991 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)



PostgreSQL:

development=# explain analyze select * from topics  order by updated_at DESC limit 15;                                                                                                    QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.57 rows=15 width=52) (actual time=0.049..0.059 rows=15 loops=1)
   ->  Index Scan Backward using index_topics_on_updated_at on topics  (cost=0.00..377.30 rows=10003 width=52) (actual time=0.048..0.053 rows=15 loops=1)
 Total runtime: 0.096 ms
(3 rows)



共收到 1 条回复

发现问题了,是我的测试数据不一致导致的。上面的例子中,pg有一万条样本数据,mysql只有1千条,估计这时候mysql觉得数据量太小了,不用索引也很快?

刚刚也怀疑数据量的问题,重新测了下,在mysql填充一万条样本数据的情况下,也会用到索引:

mysql> explain select * from topics order by updated_at desc limit 15;
+----+-------------+--------+-------+---------------+----------------------------+---------+------+------+-------+
| id | select_type | table  | type  | possible_keys | key                        | key_len | ref  | rows | Extra |
+----+-------------+--------+-------+---------------+----------------------------+---------+------+------+-------+
|  1 | SIMPLE      | topics | index | NULL          | index_topics_on_updated_at | 8       | NULL |   15 |       |
+----+-------------+--------+-------+---------------+----------------------------+---------+------+------+-------+
1 row in set (0.00 sec)

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册