table_rows查询优化

121次阅读

共计 5102 个字符，预计需要花费 13 分钟才能阅读完成。

日常应用运维工作中，Dev 或者 db 本身都需要统计表的行数，以此作为应用或者维护的一个信息参考。也许很多人会忽略 select count(*) from table_name 类似的 sql 对数据库性能的影响，可当你在慢日志平台看到执行了数千次，每次执行 4 秒左右的查询，你还会无动于衷吗？作为一个有担当敢于挑战的 dba，你们应该勇于说 no，我觉得类似的需求不可避免但不应该是影响数据库性能的因素，如果连这个都摆不平公司还能指望你干什么。经过几番深思总结，我根据查询的需求，分为模糊查询和精确查询，可以通过下面的三种方式来择优选择。下面测试是线上一个日志表，表大小在 6 个 G 左右。

1、精确查询知晓表中数据行数，这个时候我们就要使用 count（）函数来统计表中行数的大小了。在 innodb 存储引擎中 count（*）函数是先从内存中读取表中的数据到内存缓冲区，然后全表扫描获得记录行数的。但是这种方式过于简单、直接暴力，对于小表查询比较合适，对于频繁的大表查询就不适用了。尤其是在生产中表很大，且表除了聚集索引（主键索引）外，没有其他非聚集索引（二级索引）的时候，无疑是一种巨大的灾难。

mysql> select count(*) from operation_log;
+———-+
| count(*) |
+———-+
| 21049180 |
+———-+
row in set (10.92 sec)

mysql> drop index user_key on rule_ceshi.operation_log;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select count(*) from rule_ceshi.operation_log;
+———-+
| count(*) |
+———-+
| 21049180 |
+———-+
row in set (23.39 sec)

上面的测试结果表明，count（*）走聚集索引和非聚集索引都是索引全扫描，但是走非聚集索引比走聚集索引获取记录数更快，这是为什么呢？我们通常不是说走主键索引是最快，难道这个原则在这里不适用还是优化器出现 bug。当我产生这个疑问的时候，也曾这样怀疑，经过几次度娘和 FQ 后，排除错误答案，终于可以很遗憾的告诉你主键索引确实是最快的，只是主键索引查询是有前提条件的，至于什么条件烦请查看我下一篇关于 count（*）怎么走索引，走那种索引分析。

2、上面的方式对单次查询，在足够配置的物理机上，显然我们还是可以接受的。然而很多次的类似 sql 出现，对数据库的性能也是一种不必要的损耗，因为这对业务发展并没有很深的意义。我们知道对于 select count（*）from table_name 这样的 sql 是没有办法通过索引优化的，那么只能通过改写 sql 进行优化了，这也是一个精通 sql 优化高手必备的技能。

如果你也想精确查询表中的行数，又想查询的时间能尽可能短，这个时候我们就要想到 max（）和 min（）函数了，通常我们统计最大值和最小值都是很快返回结果的。

mysql> select ifnull(max(id),0)-ifnull(min(id),0)+1 as rows from rule_ceshi.operation_log;
+———-+
| rows |
+———-+
| 21124162 |
+———-+
1 row in set (0.02 sec)

当然使用这种优化改写的前提是你的上产中表中有主键且是整数类型的，主键还需是连续的，也就是你的上产中没有进行过 delete from table where xxx=xxx 的删除行记录操作，否则这样统计还是不精准的。

3、我们知道 MySQL 自带一个统计信息，平时我们的 show 命令之类的都来源数据库中的统计表。如果我们的 Dev 告诉我们，只需要模糊查询知晓表中数据行数呢？这个时候，你就可以通过 MySQL 自带的 information_schema.tables 表的统计信息，初步判断表的数据行大小。

上面这种方式对于 dba 日常维护，判断一个表的行数大小很有作用，必需知晓。

正文完

星哥说事-微信公众号