一个执行计划使用覆盖索引的问题

[珠海-天堂]

mysql> analyze local table tw_wall_records;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| test.tw_wall_records | analyze | status   | OK       |
+----------------------+---------+----------+----------+
1 row in set (0.32 sec)
 
mysql> explain select count(*) from tw_wall_records where module='10086';
+----+-------------+-----------------+-------+---------------+------------+---------+------+--------+--------------------------+
| id | select_type | table           | type  | possible_keys | key        | key_len | ref  | rows   | Extra                    |
+----+-------------+-----------------+-------+---------------+------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | tw_wall_records | index | NULL          | idx_title1 | 99      | NULL | 974289 | Using where; Using index |
+----+-------------+-----------------+-------+---------------+------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
 
mysql> explain select * from tw_wall_records where module='10086';
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | tw_wall_records | ALL  | NULL          | NULL | NULL    | NULL | 974289 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
 
mysql> show index from tw_wall_records;
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tw_wall_records |          0 | PRIMARY    |            1 | id          | A         |     1085925 |     NULL | NULL   |      | BTREE      |         |
| tw_wall_records |          1 | idx_title1 |            1 | user_name   | A         |       57153 |     NULL | NULL   |      | BTREE      |         |
| tw_wall_records |          1 | idx_title1 |            2 | module      | A         |       77566 |     NULL | NULL   |      | BTREE      |         |
| tw_wall_records |          1 | idx_all    |            1 | user_id     | A         |          20 |     NULL | NULL   |      | BTREE      |         |
| tw_wall_records |          1 | idx_all    |            2 | module      | A         |          20 |     NULL | NULL   |      | BTREE      |         |
| tw_wall_records |          1 | idx_all    |            3 | type        | A         |          20 |     NULL | NULL   |      | BTREE      |         |
| tw_wall_records |          1 | idx_all    |            4 | ns          | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |
| tw_wall_records |          1 | idx_all    |            5 | title       | A         |     1085925 |     NULL | NULL   |      | BTREE      |         |
| tw_wall_records |          1 | idx_all    |            6 | id          | A         |     1085925 |     NULL | NULL   |      | BTREE      |         |
| tw_wall_records |          1 | idx_type   |            1 | type        | A         |          20 |       11 | NULL   |      | BTREE      |         |
| tw_wall_records |          1 | idx_type_1 |            1 | type        | A         |        1057 |       11 | NULL   |      | BTREE      |         |
| tw_wall_records |          1 | idx_type_1 |            2 | user_name   | A         |       54296 |       19 | NULL   |      | BTREE      |         |
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
12 rows in set (0.35 sec)

问题：
在select count(*)时，执行计划using index,使用了覆盖索引，通过索引查找直接得到结果，不需要回到数据行。
看idx_title1索引，module不处于复合索引的最左位置，通过索引查找，它是不会使用到这个索引，而且全表扫描才对。

[rucypli]

因为读索引块的代价要比全表扫描小 所以走索引

即使第二个查询 也有可能是走索引 而不是全表扫描


所谓索引最左前缀 是指在多列索引中优先匹配最左列，而不是没有最左列就不用索引

[珠海-天堂]

[Quote=引用 1 楼 的回复:]

因为读索引块的代价要比全表扫描小 所以走索引

即使第二个查询 也有可能是走索引 而不是全表扫描


所谓索引最左前缀 是指在多列索引中优先匹配最左列，而不是没有最左列就不用索引
[/Quote]

MYSQL索引查找遵循最左原则，是由它的B-TREE数据结构决定的。
“所谓索引最左前缀 是指在多列索引中优先匹配最左列，而不是没有最左列就不用索引”这句话有问题。很容易就能够测试出来。

对于count(*)，MYSQL可能有做特殊处理，我上面的例子在5.1,5.5下都是一样的，在这里它确实用到了索引。但是不能作为你的结论的根据，这只是个特例。