Mysql的强制索引（Force Index）都为我们做了哪些优化？

原本只是想验证一下选择不同索引对innodb count(*)查询速度的影响。
各位顺道可参考下这篇文章 [InnoDB系列] -- innodb表如何更快得到count(*)结果。

测试过程中没想到同样的一条sql语句仅仅是增加了force index后查询速度几乎快了一倍。
select count(*) from http_log_3 force index(time) where time >= 000000    //1 row in set (11 min 19.35 sec)
select count(*) from http_log_3 where time >= 000000    //1 row in set (20 min 5.86 sec)

但实际上通过explain分析可知其实这两条sql语句使用的都是time索引，完全一样！
在这个特例当中使用force index(time)后影响的并不是索引key的选择（优化器默认也使用time索引），而是type及rows.

很想知道这是为什么，rows是如何被估算出来的，可有公式？

测试环境：
数据库 mysql 5.1.34，innodb引擎，使用innodb_file_per_table选项。
使用表分区方式创建数据表（按日分区共十个），表中一共有5000万数据，即每个分区各500万。

测试输出：

--------------
explain partitions select count(*) from http_log_3 force index(time) where time >= 000000
--------------

+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
| id | select_type | table      | partitions                        | type  | possible_keys | key  | key_len | ref  | rows     | Extra                    |
+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | http_log_3 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10 | range | time          | time | 3       | NULL | 25000141 | Using where; Using index |
+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
1 row in set (0.01 sec)

--------------
explain partitions select count(*) from http_log_3 where time >= 000000
--------------

+----+-------------+------------+-----------------------------------+-------+--------------------------+------+---------+------+----------+--------------------------+
| id | select_type | table      | partitions                        | type  | possible_keys            | key  | key_len | ref  | rows     | Extra                    |
+----+-------------+------------+-----------------------------------+-------+--------------------------+------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | http_log_3 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10 | index | time_ip,time_domain,time | time | 3       | NULL | 50000291 | Using where; Using index |
+----+-------------+------------+-----------------------------------+-------+--------------------------+------+---------+------+----------+--------------------------+
1 row in set (0.01 sec)

--------------
explain partitions select count(*) from http_log_3
--------------

+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table      | partitions                        | type  | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | http_log_3 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10 | index | NULL          | time | 3       | NULL | 50000291 | Using index |
+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

--------------
select count(*) from http_log_3 force index(time) where time >= 000000
--------------

+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (11 min 19.35 sec)

--------------
select count(*) from http_log_3 where time >= 000000
--------------

+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (20 min 5.86 sec)

--------------
select count(*) from http_log_3
--------------

+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (20 min 6.32 sec)

 

 

 

评论

2 楼 jef 2009-05-15  

猫尾摆摆 写道

select count(*) from http_log_3
--------------

+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (20 min 6.32 sec)

这条语句也要20分钟来执行？？？是不是我看错了还是？？？？

没看错，确实是这样。当然如果使用的是myisam引擎的话，类似这种count(*)简单查询操作执行起来会非常快。

1 楼 猫尾摆摆 2009-05-15  

select count(*) from http_log_3
--------------

+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (20 min 6.32 sec)

这条语句也要20分钟来执行？？？是不是我看错了还是？？？？