using filesort 效率比 using index 效率更高一例

原始的 slow log 中的 sql：

mysql> select fans_id , pfrom from sns_fans_hash_15  where `user_id` = 'q928841616' and (`type` = 2 or `type`=4) ORDER BY ctime DESC  limit 0 , 15;

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

| fans_id         | pfrom |

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

| miao554844      |     0 |

| a22998720       |     1 |

| yapei520008176  |     1 |

| sady1343868908  |     0 |

| yujk26311       |     1 |

| xiaoyu721520    |     0 |

| qq-68482051     |     0 |

| r401006662      |     0 |

| ayyyyy962464    |     1 |

| hzeshi          |     1 |

| qq-38779715     |     0 |

| wangting123789  |     1 |

| silinger1996    |     0 |

| serein0108      |     1 |

| lijiayuan410410 |     1 |

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

15 rows in set (2.76 sec)

explain 看到的结果：

mysql> explain select fans_id , pfrom from sns_fans_hash_15  where `user_id` = 'q928841616' and (`type` = 2 or `type`=4) ORDER BY ctime DESC  limit 0, 15;

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

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

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

|  1 | SIMPLE      | sns_fans_hash_15 | index | PRIMARY       | ctime | 8       | NULL |  347 | Using where |

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

1 row in set (0.00 sec)

这个索引用到的是 ctime， 而并非是 primary， 通过 select 看看 where 字句中的集合数量：

mysql> select count(*) from sns_fans_hash_15  where `user_id` = 'q928841616' and (`type` = 2 or `type`=4);

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

| count(*) |

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

|      225 |

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

1 row in set (0.04 sec)

mysql>

很显然，这个 where 字句的 select 后的集合很小，强制索引为  primary 试试：

mysql> select fans_id , pfrom from sns_fans_hash_15 force index(`primary`) where `user_id` = 'q928841616' and (`type` = 2 or `type`=4) ORDER BY ctime DESC  limit 0 , 15;

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

| fans_id         | pfrom |

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

| miao554844      |     0 |

| a22998720       |     1 |

| yapei520008176  |     1 |

| sady1343868908  |     0 |

| yujk26311       |     1 |

| xiaoyu721520    |     0 |

| qq-68482051     |     0 |

| r401006662      |     0 |

| ayyyyy962464    |     1 |

| hzeshi          |     1 |

| qq-38779715     |     0 |

| wangting123789  |     1 |

| silinger1996    |     0 |

| serein0108      |     1 |

| lijiayuan410410 |     1 |

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

15 rows in set (0.03 sec)

explain 的解释结果如下：

mysql> explain select fans_id , pfrom from sns_fans_hash_15 force index(`primary`) where `user_id` = 'q928841616' and (`type` = 2 or `type`=4) ORDER BY ctime DESC  limit 0 , 15;

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

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

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

|  1 | SIMPLE      | sns_fans_hash_15 | ref  | PRIMARY       | PRIMARY | 62      | const | 76038 | Using where; Using filesort |

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

1 row in set (0.00 sec)

再来看看  explain 中的 ref ，从最好到最差的结果分别是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

整体上看，使用 primary 还是占优势。另外发现，sql 中不使用 limit ，查询优化器选择的仍然是 primary 

mysql> explain select * from sns_fans_hash_15  where `user_id` = 'q928841616' and (`type` = 2 or `type`=4) order by ctime desc;

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

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

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

|  1 | SIMPLE      | sns_fans_hash_15 | ref  | PRIMARY       | PRIMARY | 62      | const | 76038 | Using where; Using filesort |

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

1 row in set (0.00 sec)

参数  max_length_for_sort_data 决定 filesort 的内存排序大小

总之，要根据具体的数据来定位。