MySQL: 版本14.12分发5.0.51b,适用于redhat-linux-gnu(x86_64),使用EditLine封装器
SQL_1:
SELECT SQL_NO_CACHE COUNT(*) FROM `ting_song_info`;
结果:
+----------+
| COUNT(*) |
+----------+
| 2637447 |
+----------+
1 row in set (0.42 sec)
explain :
+----+-------------+----------------+-------+---------------+-------------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+-------------------+---------+------+---------+-------------+
| 1 | SIMPLE | ting_song_info | index | NULL | total_listen_nums | 4 | NULL | 2769410 | Using index |
+----+-------------+----------------+-------+---------------+-------------------+---------+------+---------+-------------+
sql_1使用关键字total_listen_nums
。
然后指定该关键字。
sql_2:
SELECT SQL_NO_CACHE COUNT(*) FROM `ting_song_info` USE KEY(`album_id`);
结果:
+----------+
| COUNT(*) |
+----------+
| 2637447 |
+----------+
1 row in set (5.21 sec)
explain:
+----+-------------+----------------+-------+---------------+----------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+----------+---------+------+---------+-------------+
| 1 | SIMPLE | ting_song_info | index | NULL | album_id | 8 | NULL | 2769410 | Using index |
+----+-------------+----------------+-------+---------------+----------+---------+------+---------+-------------+
total_listen_nums的key_len比album_id短。
这就是为什么sql_1使用total_listen_nums吗?
SHOW CREATE TABLE ting_song_info
的输出。 - jeremycole