mysql-count优化

count是统计数据的行数的，一般分页都会用到，常见的有count(1)，count(*)写法，很多人会认为count(*)比count(1)的效率会高，用数据说话

count(*）：

mysql> SELECT count(*) FROM `job_instance`;
+----------+
| count(*) |
+----------+
|  2345399 |
+----------+
1 row in set (35.19 sec)

mysql> EXPLAIN SELECT count(*) FROM `job_instance`;
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | job_instance | NULL       | index | NULL          | PRIMARY | 8       | NULL | 2497645 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.04 sec)

mysql> show WARNINGS;
+-------+------+----------------------------------------------------------------------------+
| Level | Code | Message                                                                    |
+-------+------+----------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `my_test`.`job_instance` |
+-------+------+----------------------------------------------------------------------------+
1 row in set (0.04 sec)

 count(1):

mysql> SELECT count(1) FROM `job_instance`;
+----------+
| count(1) |
+----------+
|  2345399 |
+----------+
1 row in set (35.18 sec)

mysql> EXPLAIN SELECT count(1) FROM `job_instance`;
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | job_instance | NULL       | index | NULL          | PRIMARY | 8       | NULL | 2497645 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set (0.06 sec)

mysql> show WARNINGS;
+-------+------+----------------------------------------------------------------------------+
| Level | Code | Message                                                                    |
+-------+------+----------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(1) AS `count(1)` from `my_test`.`job_instance` |
+-------+------+----------------------------------------------------------------------------+
1 row in set (0.07 sec)

执行计划几乎一样，执行耗时也差不多，所以性能是相等的，那怎样对它进行优化，减少查询时间呢？加二级索引

表结构如图所示：

现在对flow_id加一个二级索引 

mysql>  SELECT count(*) FROM `job_instance`;
+----------+
| count(*) |
+----------+
|  2345399 |
+----------+
1 row in set (0.11 sec)

 添加一个二级索引，性能提高了好几百倍，这到底是为什么呢？

如果一张表中只有一个主键，没有其他索引的话，走的是id索引，而id是主键，主键索引树的一个聚簇索引，id跟数据绑在一起，如果要统计id,必须要读数据，则效率就很慢

如果一张表中有一个二级索引，非主键索引树是非聚簇索引，则统计二级索引叶子节点的个数，叶子节点就是存着id，不需要回表查找，也就不需要读取数据，所以效率大大提高了

还有一各问题，如果一张表，多个二级索引，是走索引key_len最短的那一个，如果where条件中有了非索引属性条件，则不走索引