mysql中文时好时坏_mysql索引的问题，时好时坏

两张表內连查询，一张表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上，但是这种情况一天会间歇的出现几次

大部分时间是没有问题的，可能高峰期间会出现这种问题