如何让80万数据的表select count更快？

Question

本帖最后由 hellotiny 于 2010-08-04 18:16:40 编辑

Answer 1

刚测试了一下，改成联合索引+把ID改成*
两个操作要都做，才可以把速度提高
但是带来了另外一个问题：SELECT m.username,m.space_name AS userid,m.membergroup_id,m.credits,Product.cache_companyname AS companyname,Product.* FROM pb_products AS Product LEFT JOIN pb_members m ON m.id=Product.member_id WHERE Product.status=1 AND Product.industry_id1=700 ORDER BY Product.ifcommend desc,Product.id desc LIMIT 0,9;这个就变得到特别慢了，把ORDER BY Product.ifcommend desc去掉也仍然慢
附：pb_members  
CREATE TABLE `pb_members` (
  `id` int(10) NOT NULL auto_increment,
  `space_name` varchar(255) NOT NULL default '',
  `templet_id` smallint(3) NOT NULL default '0',
  `username` varchar(25) NOT NULL default '',
  `userpass` varchar(50) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  `points` smallint(6) NOT NULL default '0',
  `credits` smallint(6) NOT NULL default '0',
  `balance_amount` float(7,2) NOT NULL default '0.00',
  `trusttype_ids` varchar(25) NOT NULL default '',
  `status` enum('3','2','1','0') NOT NULL default '0',
  `photo` varchar(100) NOT NULL default '',
  `membertype_id` smallint(3) NOT NULL default '0',
  `membergroup_id` smallint(3) NOT NULL default '0',
  `last_login` varchar(11) NOT NULL default '0',
  `last_ip` varchar(25) NOT NULL default '0',
  `service_start_date` varchar(11) NOT NULL default '0',
  `service_end_date` varchar(11) NOT NULL default '0',
  `office_redirect` smallint(6) NOT NULL default '0',
  `created` varchar(10) NOT NULL default '0',
  `modified` varchar(10) NOT NULL default '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `space_name` (`space_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=101259 ;

Answer 2

贴出你的explain SELECT count(ID) AS amount FROM pb_products AS Product WHERE Product.status=1 AND Product.industry_id1=583 LIMIT 1;show index from pb_products 

Answer 3

我教你个方法你试试看可以么？
SELECT SQL_CALC_FOUND_ROWS ID 
FROM pb_products AS Product 
WHERE Product.status=1 AND Product.industry_id1=583 LIMIT 1;
SELECT FOUND_ROWS();

Answer 4

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  Product  ref  industry_id1_2  industry_id1_2  2  const  31593  Using where
Table  Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment
pb_products  0  PRIMARY  1  id  A  834513  NULL  NULL     BTREE   
pb_products  1  company_id  1  company_id  A  104314  NULL  NULL     BTREE   
pb_products  1  member_id  1  member_id  A  104314  NULL  NULL     BTREE   
pb_products  1  industry_id2  1  industry_id2  A  1165  NULL  NULL     BTREE   
pb_products  1  industry_id3  1  industry_id3  A  2425  NULL  NULL     BTREE   
pb_products  1  industry_id1_2  1  industry_id1  A  44  NULL  NULL     BTREE   

Answer 5

create index xxxx on pb_products(status,industry_id1);创建这个复合索引。

Answer 6

SELECT count(ID) AS amount FROM pb_products AS Product WHERE Product.status=1 AND Product.industry_id1=583 LIMIT 1
你这个语句为什么后面要跟一个LIMIT 1？按理说只要只要增加Product.status和Product.industry_id1应该就可以提高效率了，count(ID)和count(*)应该是一样的吧，因为ID本身就是唯一主键

Answer 7

我之前是创建过复合索引的，但会带来另外一个副作用：SELECT m.username,m.space_name AS userid,m.membergroup_id,m.credits,Product.cache_companyname AS companyname,Product.* FROM pb_products AS Product LEFT JOIN pb_members m ON m.id=Product.member_id WHERE Product.status=1 AND Product.industry_id1=700 ORDER BY Product.ifcommend desc,Product.id desc LIMIT 0,9;这个就变得到特别慢了，把ORDER BY Product.ifcommend desc去掉也仍然慢
附：pb_members   
CREATE TABLE `pb_members` (
  `id` int(10) NOT NULL auto_increment,
  `space_name` varchar(255) NOT NULL default '',
  `templet_id` smallint(3) NOT NULL default '0',
  `username` varchar(25) NOT NULL default '',
  `userpass` varchar(50) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  `points` smallint(6) NOT NULL default '0',
  `credits` smallint(6) NOT NULL default '0',
  `balance_amount` float(7,2) NOT NULL default '0.00',
  `trusttype_ids` varchar(25) NOT NULL default '',
  `status` enum('3','2','1','0') NOT NULL default '0',
  `photo` varchar(100) NOT NULL default '',
  `membertype_id` smallint(3) NOT NULL default '0',
  `membergroup_id` smallint(3) NOT NULL default '0',
  `last_login` varchar(11) NOT NULL default '0',
  `last_ip` varchar(25) NOT NULL default '0',
  `service_start_date` varchar(11) NOT NULL default '0',
  `service_end_date` varchar(11) NOT NULL default '0',
  `office_redirect` smallint(6) NOT NULL default '0',
  `created` varchar(10) NOT NULL default '0',
  `modified` varchar(10) NOT NULL default '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `space_name` (`space_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=101259 ;

Answer 8

贴你的explain 结果出来看看。

Answer 9

创建（status,industry_id1,ifcommend ,id）索引。

Answer 10

创建这个索引后，导致以前很快的下面这条语句变得奇慢：
SELECT p.id as productid,p.id,p.name as productname,p.clicked AS hits,p.name,p.price,picture,created,cache_companyname as companyname FROM pb_products p  WHERE p.status=1 AND p.state=1 AND p.ifcommend>0 ORDER BY id desc LIMIT 10;
于是我把上面那个索引中增加了state，在id之前，结果全都变慢了
，现在这个问题变成了一个恶循环了，解决了上面的问题，下面会带来另外一个问题.真是苦恼呀

Answer 11

创建（status,state,ifcommend ,id）索引。

Answer 12

索引只是针对特定查询的，你还有些什么查询建议一次性贴出来！另外贴出你的 explain, 否则别人只能大致写个方案。

Answer 13

EXPLAIN SELECT p.id AS productid, p.id, p.name AS productname, p.clicked AS hits, p.name, p.price, picture, created, cache_companyname AS companyname
FROM pb_products p
WHERE p.status =1
AND p.ifcommend >0
ORDER BY id DESC
LIMIT 10 ;1   SIMPLE   p   ref   status   status   1   const   834510   Using where; Using filesort下面这个增加state字段后explain的结果一样explain SELECT p.id as productid,p.id,p.name as productname,p.clicked AS hits,p.name,p.price,picture,created,cache_companyname as companyname FROM pb_products p  WHERE p.status=1 AND p.state=1 AND p.ifcommend>0 ORDER BY id desc LIMIT 10;1   SIMPLE   p   ref   status   status   1   const   834510   Using where; Using filesort

Answer 14

有两点想不通的，
（1）为什么创建了新的联合索引之后，以前跑得很快的查询却变得很慢了？按理说应该只会影响到insert,update之类才对呀
（2）(a,b,c,d,e)这样的索引对a,b,c,e或者a,b,d,e的查询无效吗？

Answer 15

第二个问题 有效 ~ 只要是从最左开始那列的才能用到组合索引  
(a,b,c,d,e)这样的索引 能用的组合是如下：
a b c d e
a b c d
a b c 
a b 
a

Answer 16

a,b,c,d,e)这样的索引对a,b,c,e或者a,b,d,e的查询无效吗？
==》
回答 无效~
自己可以试试

Answer 17

a b c d e
a b c d
a b c  
a b  
a都试过么？应该都有效果 有时候不用索引也跟数据分布有关系

Answer 18

MYSQL有没有看执行计划的地方？