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不处于复合索引的最左位置,通过索引查找,它是不会使用到这个索引,而且全表扫描才对。
MYSQL索引查找遵循最左原则,是由它的B-TREE数据结构决定的。
“所谓索引最左前缀 是指在多列索引中优先匹配最左列,而不是没有最左列就不用索引”这句话有问题。很容易就能够测试出来。对于count(*),MYSQL可能有做特殊处理,我上面的例子在5.1,5.5下都是一样的,在这里它确实用到了索引。但是不能作为你的结论的根据,这只是个特例。