建表语句为:CREATETABLE`viewers_0`(`id`int(11)unsignedNOTNULLAUTO_INCREMENTCOMMENT'自增id',`uid`int(11)unsignedNOTNULLDEFAULT'0'COMMENT'uid',`fid`int(11)unsignedNOTNULLDE...
建表语句为:
CREATE TABLE `viewers_0` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'uid',
`fid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'fid',
`create_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uid_fid` (`uid`,`fid`),
KEY `u_c` (`uid`,`create_time`)
) ENGINE=InnoDB
查询语句为:
mysql> explain select fid from viewers_0 where uid=111 order by create_time desc limit 10;
在mysql5.6.16下:
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | viewers_0 | ALL | uid_fid,u_c | NULL | NULL | NULL | 2 | Using where; Using filesort |
在mysql5.0.18下:
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | viewers_0 | ref | uid_fid,u_c | u_c | 4 | const | 1 | Using where |
为什么这样呢?应该如何优化以适应5.6.1?
展开