表结构
| v9_news | CREATE TABLE `v9_news` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`catid` smallint(5) unsigned NOT NULL DEFAULT '0',
`typeid` smallint(5) unsigned NOT NULL,
`title` varchar(80) NOT NULL DEFAULT '',
`style` char(24) NOT NULL DEFAULT '',
`thumb` varchar(255) NOT NULL DEFAULT '',
`keywords` varchar(255) NOT NULL DEFAULT '',
`description` mediumtext NOT NULL,
`posids` tinyint(3) unsigned NOT NULL DEFAULT '0',
`url` char(100) NOT NULL,
`listorder` tinyint(3) unsigned NOT NULL DEFAULT '0',
`status` varchar(2) NOT NULL DEFAULT '',
`sysadd` tinyint(1) unsigned NOT NULL DEFAULT '0',
`islink` tinyint(1) unsigned NOT NULL DEFAULT '0',
`username` char(20) NOT NULL,
`inputtime` int(10) unsigned NOT NULL DEFAULT '0',
`updatetime` int(10) unsigned NOT NULL DEFAULT '0',
`shorttitle` varchar(255) NOT NULL DEFAULT '',
`list_description` mediumtext NOT NULL,
PRIMARY KEY (`id`),
KEY `inputtime` (`inputtime`),
KEY `catid` (`catid`,`inputtime`),
KEY `keywords` (`catid`,`keywords`),
KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=1997500 DEFAULT CHARSET=gbk表索引:
--------+---------+
| | 0 | PRIMARY | 1 | id | A | 906171 | NULL | NULL | | BTREE | |
| | 1 | inputtime | 1 | inputtime | A | 906171 | NULL | NULL | | BTREE | |
| | 1 | catid | 1 | catid | A | 347 | NULL | NULL | | BTREE | |
| | 1 | catid | 2 | inputtime | A | 906171 | NULL | NULL | | BTREE | |
| | 1 | keywords | 1 | catid | A | 347 | NULL | NULL | | BTREE | |
| | 1 | keywords | 2 | keywords | A | 453085 | NULL | NULL | | BTREE | |
| | 1 | title | 1 | title | A | 906171 | NULL | NULL | | BTREE | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+------
问题:看两条语句的执行结果以及语句
1、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by id asc limit 1;
Empty set (2.13 sec)
2、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by id asc ;
Empty set (0.08 sec)
3、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' limit 1;
Empty set (0.11 sec)
4、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by catid asc limit 1;
Empty set (0.08 sec)
为什么前两个的结果相差那么大?
第4个更换排序字段后,速度明显变快,该字段有索引。
然后在另一库中的同样一张表中,执行第一条查询语句结果相差那么大 这是为什么?
select title,url from dzwww.v9_news where catid='1642' and username='duoduo' order by id asc limit 1;
Empty set (0.00 sec)
| v9_news | CREATE TABLE `v9_news` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`catid` smallint(5) unsigned NOT NULL DEFAULT '0',
`typeid` smallint(5) unsigned NOT NULL,
`title` varchar(80) NOT NULL DEFAULT '',
`style` char(24) NOT NULL DEFAULT '',
`thumb` varchar(255) NOT NULL DEFAULT '',
`keywords` varchar(255) NOT NULL DEFAULT '',
`description` mediumtext NOT NULL,
`posids` tinyint(3) unsigned NOT NULL DEFAULT '0',
`url` char(100) NOT NULL,
`listorder` tinyint(3) unsigned NOT NULL DEFAULT '0',
`status` varchar(2) NOT NULL DEFAULT '',
`sysadd` tinyint(1) unsigned NOT NULL DEFAULT '0',
`islink` tinyint(1) unsigned NOT NULL DEFAULT '0',
`username` char(20) NOT NULL,
`inputtime` int(10) unsigned NOT NULL DEFAULT '0',
`updatetime` int(10) unsigned NOT NULL DEFAULT '0',
`shorttitle` varchar(255) NOT NULL DEFAULT '',
`list_description` mediumtext NOT NULL,
PRIMARY KEY (`id`),
KEY `inputtime` (`inputtime`),
KEY `catid` (`catid`,`inputtime`),
KEY `keywords` (`catid`,`keywords`),
KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=1997500 DEFAULT CHARSET=gbk表索引:
--------+---------+
| | 0 | PRIMARY | 1 | id | A | 906171 | NULL | NULL | | BTREE | |
| | 1 | inputtime | 1 | inputtime | A | 906171 | NULL | NULL | | BTREE | |
| | 1 | catid | 1 | catid | A | 347 | NULL | NULL | | BTREE | |
| | 1 | catid | 2 | inputtime | A | 906171 | NULL | NULL | | BTREE | |
| | 1 | keywords | 1 | catid | A | 347 | NULL | NULL | | BTREE | |
| | 1 | keywords | 2 | keywords | A | 453085 | NULL | NULL | | BTREE | |
| | 1 | title | 1 | title | A | 906171 | NULL | NULL | | BTREE | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+------
问题:看两条语句的执行结果以及语句
1、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by id asc limit 1;
Empty set (2.13 sec)
2、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by id asc ;
Empty set (0.08 sec)
3、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' limit 1;
Empty set (0.11 sec)
4、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by catid asc limit 1;
Empty set (0.08 sec)
为什么前两个的结果相差那么大?
第4个更换排序字段后,速度明显变快,该字段有索引。
然后在另一库中的同样一张表中,执行第一条查询语句结果相差那么大 这是为什么?
select title,url from dzwww.v9_news where catid='1642' and username='duoduo' order by id asc limit 1;
Empty set (0.00 sec)
建议贴出的所有测试结果。
这应该跟limit没太多的关系,第一条和第二语句不可能执行时间相差这么大。
+----+-------------+---------+-------+----------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+----------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | v9_news | index | catid,keywords | PRIMARY | 3 | NULL | 51 | Using where |
+----+-------------+---------+-------+----------------+---------+---------+------+------+-------------+
你这个SQL是通过走聚集索引来查询数据,然后再利用where条件,所以会比较慢,贴下你第二个SQl的执行计划看看
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | v9_news | ref | catid,keywords | keywords | 2 | const | 1 | Using where; Using filesort |
+----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+
这个是第二条语句的
explain select title,url from dzwww.v9_news where catid='1642' and username='duoduo' order by id asc;
+----+-------------+---------+------+----------------+-------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+-------+---------+-------+------+-------------+
| 1 | SIMPLE | v9_news | ref | catid,keywords | catid | 2 | const | 1 | Using where |
+----+-------------+---------+------+----------------+-------+---------+-------+------+-------------+
+----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | v9_news | ref | catid,keywords | keywords | 2 | const | 1 | Using where; Using filesort |
+----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+
这个是第二个库中相同表的执行计划
PRIMARY | 3 | NULL | 51 | Using where
第二个表的
keywords | 2 | const | 1 | Using where; Using filesort |
语句相同,难道还有其他条件限制了?
PRIMARY | 3 | NULL | 51 | Using where
第二个表的
keywords | 2 | const | 1 | Using where; Using filesort |
语句相同,难道还有其他条件限制了?问题库的数据量90W左右
第二库的数据量为206W左右