原本只是想验证一下选择不同索引对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)