一个MySQL服务器,A库里面,某表有记录一千万条,B库里,某表有记录三万多条。
A库select count(1) from 表,查询时间为0.00sec
B库select count(1) from 表,查询时间为18 min 28.44 sec
有高手告诉我这是怎么回事么?
A库select count(1) from 表,查询时间为0.00sec
B库select count(1) from 表,查询时间为18 min 28.44 sec
有高手告诉我这是怎么回事么?
show create table b;show index from a;
show index from b;explain select count(*) form a;
explain select count(*) form b;
CREATE TABLE `jieqi_article_chapter` (
`chapterid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`siteid` smallint(6) unsigned NOT NULL DEFAULT '0',
`articleid` int(11) unsigned NOT NULL DEFAULT '0',
`articlename` varchar(50) CHARACTER SET gbk COLLATE gbk_bin NOT NULL DEFAULT '',
`volumeid` int(11) unsigned NOT NULL DEFAULT '0',
`posterid` int(11) unsigned NOT NULL DEFAULT '0',
`poster` varchar(30) CHARACTER SET gbk COLLATE gbk_bin NOT NULL DEFAULT '',
`postdate` int(11) unsigned NOT NULL DEFAULT '0',
`lastupdate` int(11) unsigned NOT NULL DEFAULT '0',
`chaptername` varchar(100) CHARACTER SET gbk COLLATE gbk_bin NOT NULL DEFAULT'',
`chapterorder` smallint(6) unsigned NOT NULL DEFAULT '0',
`size` int(11) unsigned NOT NULL DEFAULT '0',
`saleprice` int(11) unsigned NOT NULL DEFAULT '0',
`salenum` int(11) unsigned NOT NULL DEFAULT '0',
`totalcost` int(11) unsigned NOT NULL DEFAULT '0',
`attachment` text NOT NULL,
`isvip` tinyint(1) unsigned NOT NULL DEFAULT '0',
`chaptertype` tinyint(1) unsigned NOT NULL DEFAULT '0',
`power` tinyint(1) unsigned NOT NULL DEFAULT '0',
`display` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`chapterid`),
KEY `articleid` (`articleid`),
KEY `volumeid` (`volumeid`),
KEY `chapterorder` (`chapterorder`),
KEY `display` (`display`),
KEY `articlename` (`articlename`,`chaptername`),
KEY `lastupdate` (`lastupdate`)
) ENGINE=MyISAM AUTO_INCREMENT=2201100 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)mysql> show index in jieqi_article_chapter;
+-----------------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| jieqi_article_chapter | 0 | PRIMARY | 1 | chapterid | A | 1979003 | NULL | NULL | | BTREE | |
| jieqi_article_chapter | 1 | articleid | 1 | articleid | A | NULL | NULL | NULL | | BTREE | |
| jieqi_article_chapter | 1 | volumeid | 1 | volumeid | A | NULL | NULL | NULL | | BTREE | |
| jieqi_article_chapter | 1 | chapterorder | 1 | chapterorder | A | NULL | NULL | NULL | | BTREE | |
| jieqi_article_chapter | 1 | display | 1 | display | A | NULL | NULL | NULL | | BTREE | |
| jieqi_article_chapter | 1 | articlename | 1 | articlename | A | NULL | NULL | NULL | | BTREE | |
| jieqi_article_chapter | 1 | articlename | 2 | chaptername | A | NULL | NULL | NULL | | BTREE | |
| jieqi_article_chapter | 1 | lastupdate | 1 | lastupdate | A | NULL | NULL | NULL | | BTREE | |+-----------------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.00 sec)mysql> explain select count(*) from jieqi_article_chapter;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)mysql>
mysql> show create table wapmo_content;
CREATE TABLE `wapmo_content` (
`SEQID` int(11) NOT NULL AUTO_INCREMENT,
`STAPLEID` int(11) NOT NULL,
`STAPLETITLE` varchar(50) NOT NULL,
`TITLE` varchar(255) NOT NULL,
`PRICE` varchar(50) DEFAULT NULL,
`CONTENT` longtext,
`MATTER` int(11) NOT NULL,
`PLACE` int(11) NOT NULL,
`LENGTH` int(11) NOT NULL,
`DOWNLOAD` int(11) NOT NULL,
`HIT` int(11) NOT NULL DEFAULT '0',
`CATEGORY` int(11) NOT NULL,
`COMMEND` int(11) NOT NULL,
`HOT` int(11) NOT NULL,
`HIDDEN` int(11) NOT NULL DEFAULT '0',
`TEMPLET` varchar(50) DEFAULT NULL,
`ISWML` int(11) NOT NULL DEFAULT '0',
`REMARKTOTAL` int(11) NOT NULL DEFAULT '0',
`EXAMINE` int(11) NOT NULL DEFAULT '0',
`EXTIME` int(11) NOT NULL DEFAULT '0',
`USERID` int(11) DEFAULT NULL,
`USERNAME` varchar(32) DEFAULT NULL,
`MARK` varchar(255) NOT NULL,
`PAGEMARK` varchar(255) DEFAULT NULL,
`GROUPID` int(11) NOT NULL,
`TEAMID` int(11) NOT NULL,
`ADMINID` int(11) NOT NULL,
`CATE` int(11) DEFAULT NULL,
`INTIME` int(11) NOT NULL,
`OUTIME` int(11) DEFAULT NULL,
`CENT_FOR_BROWSE` int(11) DEFAULT NULL,
`CENT_FOR_READ` int(11) DEFAULT NULL,
PRIMARY KEY (`SEQID`),
KEY `PK_WAPMO_CONTENT_INDEX` (`STAPLEID`,`TITLE`,`HIT`,`HOT`,`COMMEND`,`MARK`,
`HIDDEN`,`EXAMINE`,`INTIME`,`USERID`)
) ENGINE=InnoDB AUTO_INCREMENT=31637 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)mysql> show index from wapmo_content;
+---------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+---------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| wapmo_content | 0 | PRIMARY | 1 | SEQID | A | 43781 | NULL | NULL | | BTREE | |
| wapmo_content | 1 | PK_WAPMO_CONTENT_INDEX | 1 | STAPLEID | A | 497 | NULL | NULL | | BTREE | |
| wapmo_content | 1 | PK_WAPMO_CONTENT_INDEX | 2 | TITLE | A | 43781 | NULL | NULL | | BTREE | |
| wapmo_content | 1 | PK_WAPMO_CONTENT_INDEX | 3 | HIT | A | 43781 | NULL | NULL | | BTREE | |
| wapmo_content | 1 | PK_WAPMO_CONTENT_INDEX | 4 | HOT | A | 43781 | NULL | NULL | | BTREE | |
| wapmo_content | 1 | PK_WAPMO_CONTENT_INDEX | 5 | COMMEND | A | 43781 | NULL | NULL | | BTREE | |
| wapmo_content | 1 | PK_WAPMO_CONTENT_INDEX | 6 | MARK | A | 43781 | NULL | NULL | | BTREE | |
| wapmo_content | 1 | PK_WAPMO_CONTENT_INDEX | 7 | HIDDEN | A | 43781 | NULL | NULL | | BTREE | |
| wapmo_content | 1 | PK_WAPMO_CONTENT_INDEX | 8 | EXAMINE | A | 43781 | NULL | NULL | | BTREE | |
| wapmo_content | 1 | PK_WAPMO_CONTENT_INDEX | 9 | INTIME | A | 43781 | NULL | NULL | | BTREE | |
| wapmo_content | 1 | PK_WAPMO_CONTENT_INDEX | 10 | USERID | A | 43781 | NULL | NULL | YES | BTREE | |
+---------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+11 rows in set (0.75 sec)mysql> explain select count(*) from wapmo_content;
+----+-------------+---------------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | wapmo_content | index | NULL | PRIMARY | 4 | NULL | 43781 | Using index |
+----+-------------+---------------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.03 sec)mysql>
你
check table jieqi_article_chapter;
check table wapmo_content;或者 analyze tale tablename 一下,然后再试试。mysql> explain select count(*) from jieqi_article_chapter;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)mysql> explain select count(*) from wapmo_content;
+----+-------------+---------------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | wapmo_content | index | NULL | PRIMARY | 4 | NULL | 43781 | Using index |
+----+-------------+---------------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.03 sec)mysql>