mysql索引自动优化问题之[同一查询时而使用索引时而不用索引分析]

mysql> show index from user_data;

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| user_data |          0 | PRIMARY  |            1 | user_id     | A         |       36297 |     NULL | NULL   |      | BTREE      |         |

| user_data |          1 | i1       |            1 | player_id   | A         |       36297 |     NULL | NULL   |      | BTREE      |         |

| user_data |          1 | i2       |            1 | mod_date    | A         |       36297 |     NULL | NULL   |      | BTREE      |         |

| user_data |          1 | i3       |            1 | lv          | A         |         150 |     NULL | NULL   |      | BTREE      |         |

| user_data |          1 | i3       |            2 | mod_date    | A         |       36297 |     NULL | NULL   |      | BTREE      |         |

| user_data |          1 | i4       |            1 | reg_date    | A         |       36297 |     NULL | NULL   |      | BTREE      |         |

| user_data |          1 | i5       |            1 | nickname    | A         |       36297 |     NULL | NULL   |      | BTREE      |         |

| user_data |          1 | i6       |            1 | device      | A         |           3 |     NULL | NULL   |      | BTREE      |         |

| user_data |          1 | i6       |            2 | school      | A         |           3 |     NULL | NULL   |      | BTREE      |         |

| user_data |          1 | i6       |            3 | reg_date    | A         |       36297 |     NULL | NULL   |      | BTREE      |         |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

10 rows in set (0.01 sec)

mysql> explain select * from user_data where reg_date < 1348853528;

+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+

| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra       |

+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+

|  1 | SIMPLE      | user_data | ALL  | i4            | NULL | NULL    | NULL | 36298 | Using where |

+----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.03 sec)

mysql> explain select * from user_data where reg_date < 1346253528;

+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+

| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | user_data | range | i4            | i4   | 4       | NULL |    4 | Using where |

+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

说明：

二叉树索引本来最适合的就是点查询，和小范围的range查询，

当预估返回的数据量超过一定比例( 貌似当预估的查询量达到总量的30% )的时候，

再根据索引一条一条去查就慢了，反而不如全表扫描快了。Mysql有自己内部自动优化机制，

但有些自动优化机制可能不是最优的。这时候就需要人工去干预。

比如长期不优化表，Mysql判断出索引不优，就会不使用索引。

有时候就要人工强制使用真正高效的索引(FORCE INDEX)。

其实当本身的查询就约等于一个全表查询的时候，强不强制使用索引基本上没什么效果。

以上第二个查询的符合条件结果数为rows=4比第一个查询的符合条件结果数rows=36298明显小很多，属于小范围查询，故mysql优化器选择使用了索引。

当使用MysqlQueryProfiler对第一个查询的查询细节进行查看时发现如下：mysql> show profiles;

+----------+------------+----------------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                                      |

+----------+------------+----------------------------------------------------------------------------+

|        1 | 0.00061300 | show variables like '%profiling%'                                          |

|        2 | 0.01138800 | select count(*) from user_data force index(i4) where reg_date < 1348853528 |

|        3 | 0.00017600 | select min(reg_date) from user_data                                        |

|        4 | 0.00026400 | select count(*) from user_data force index(i4) where reg_date < 1346245990 |

|        5 | 0.00024800 | select count(*) from user_data force index(i4) where reg_date < 1346246990 |

|        6 | 0.00036800 | select count(*) from user_data force index(i4) where reg_date < 1346346990 |

|        7 | 0.01128700 | select count(*) from user_data force index(i4) where reg_date < 1348853528 |

|        8 | 0.03356500 | select user_id from user_data force index(i4) where reg_date < 1348853528  |

|        9 | 0.02871400 | select user_id from user_data where reg_date < 1348853528                  |

|       10 | 0.00007400 | set profilling=0                                                           |

+----------+------------+----------------------------------------------------------------------------+

10 rows in set (0.00 sec)

可看出：

不强制使用索引情况下，第一个查询(queryID:9)耗时28ms，

强制使用索引情况下，第一个查询(queryID:8)耗时33ms，

由此可知：大范围查询时全表扫描的性能反而可能比索引扫描好