两张表內连查询,一张表1W+数据,一张表2000+数据,count一下居然花了10秒
这条语句: SELECT COUNT(a.id) FROM t_owneroffice a INNER JOIN t_member b ON (a.posterId = b.id) WHERE 1=1 AND a.type=1;
先看下索引情况吧
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t_owneroffice | 0 | PRIMARY | 1 | id | A | 13249 | NULL | NULL | | BTREE | |
| t_owneroffice | 1 | FKE946FE4F2D5E109 | 1 | posterId | A | 1 | NULL | NULL | YES | BTREE | |
+---------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
t_owneroffice 两个索引,一个主键索引,一个普通索引
t_member 就一个主键索引
来看下语句解释情况:
mysql> explain SELECT COUNT(a.id) FROM t_owneroffice a INNER JOIN t_member b ON (a.posterId = b.id) WHERE 1=1 AND a.type=1;
+----+-------------+-------+-------+-------------------+---------+---------+------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+------+-------+--------------------------------+
| 1 | SIMPLE | b | index | NULL | PRIMARY | 102 | NULL | 2045 | Using index |
| 1 | SIMPLE | a | ALL | FKE946FE4F2D5E109 | NULL | NULL | NULL | 13343 | Using where; Using join buffer |
+----+-------------+-------+-------+-------------------+---------+---------+------+-------+--------------------------------+
a表用了全表查询,没使用索引啊,之前是ref不是all,貌似插了几百条数据之后就变成了all
看下执行情况
+--------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------+----------+----------+------------+
| starting | 0.000082 | 0.000000 | 0.000000 |
| Opening tables | 0.000018 | 0.000000 | 0.000000 |
| System lock | 0.000007 | 0.000000 | 0.000000 |
| Table lock | 0.000009 | 0.000000 | 0.000000 |
| init | 0.000039 | 0.000000 | 0.000000 |
| optimizing | 0.000020 | 0.000000 | 0.000000 |
| statistics | 0.000020 | 0.000000 | 0.000000 |
| preparing | 0.000029 | 0.000000 | 0.000000 |
| executing | 0.000006 | 0.000000 | 0.000000 |
| Sending data | 9.379993 | 9.376574 | 0.000000 |
| end | 0.000010 | 0.000000 | 0.000000 |
| query end | 0.000002 | 0.000000 | 0.000000 |
| freeing items | 0.000328 | 0.000000 | 0.000000 |
| logging slow query | 0.000004 | 0.000000 | 0.000000 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 |
+--------------------+----------+----------+------------+
时间全花在了Sending data上,为什么呢? 一条count语句花了9秒啊,亲,快来帮我看看啊
问题补充:
7454103 写道
是否能使用上索引 和数据量没太多的关系,
多看下你的查询条件,
引用
WHERE 1=1
这样写貌似就不会使用索引的!
不是where的原因,貌似跟mysql配置有关系
你看时间全花在 Sending data上,但是这种情况一天会间歇的出现几次
大部分时间是没有问题的,可能高峰期间会出现这种问题