这个查询为啥这么慢啊?里面有数据二十多万。SELECT COUNT(p.id)
FROM info AS p
WHERE p.is_show = 2
AND p.sex = '2'
AND p.pic_big > ''
EXPLAIN如下:
1 SIMPLE pic ref extend_class1,displaysex displaysex 2 const,const 126772 Using wheredisplaysex索引为:is_show字段和sex字段。。查询很耗时啊。为啥啊?单独
SELECT COUNT(*)
FROM info AS p 反而还快多了。
FROM info AS p
WHERE p.is_show = 2
AND p.sex = '2'
AND p.pic_big > ''
EXPLAIN如下:
1 SIMPLE pic ref extend_class1,displaysex displaysex 2 const,const 126772 Using wheredisplaysex索引为:is_show字段和sex字段。。查询很耗时啊。为啥啊?单独
SELECT COUNT(*)
FROM info AS p 反而还快多了。
pic_big字段的值。基本上都是有值的。。
最好以下面格式来贴,以方便分析阅读。
mysql> show index from book;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| book | 0 | PRIMARY | 1 | id | A | 11 | NULL | NULL | | BTREE | |
| book | 1 | asd | 1 | num | A | 11 | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)mysql>
info 1 bigclass1 1 bigclass1 A 27 \N \N BTREE
info 1 extend_class1 1 sex A 5 \N \N BTREE
info 1 extend_class1 2 extend_class1 A 36 \N \N BTREE
info 1 extend_class1 3 extend_class2 A 145 \N \N BTREE
info 1 pic_access 1 pic_access A 52 \N \N BTREE
info 1 class_brand 1 class_brand A 2194 \N \N BTREE
info 1 displaysex 1 is_show A 2 \N \N BTREE
info 1 displaysex 2 sex A 13 \N \N BTREE
info 1 input_time 1 input_time A 2969 \N \N BTREE
info 1 input_time 2 id A 201905 \N \N BTREE
info 1 idinputtime 1 id A 201905 \N \N BTREE
info 1 idinputtime 2 input_time A 201905 \N \N BTREE
info 1 pic_big 1 pic_big A 201905 \N \N BTREE
这就是比较悲惨的了。。以前这个数据库在一台服务器上。还都不会很慢。现在转移到另外一台数据库服务器上,服务器配置差不多,其他服务少了不少,反而还慢了不少。关键一点是还show processlist 时,有很多 locked
完全是把mysql的配置文件弄过去。还是一样的情况。呵。所以很郁闷。