兄弟们。浅薄地炫富。你可能不相信。你有手机吗？我有。短信，你知道吗？一毛钱一张，我天天寄。你敢想吗？所以，年轻人，有钱真的很好。今天，我们将讨论短信。短信，也叫短信。比如你有一个短信表（sms），里面包含了各种需要发送的短信信息。sms表创建sqlsms表需要注意state字段。当为0时，表示此时没有发送短信。这时候还会有一个异步线程，不断的去拾取未发送的（state=0）短信数据，执行短信操作。发送成功后，状态字段会被设置为1（发送）。也就是说，未发送的数据会持续减少。异步线程发短信假设现在因为某些原因需要做一些监控，比如监控的内容是你的sms数据表中是否有state=0（未发送）的短信，这样你就可以判断未发送短信的积累大约是一个什么样的量级。为了得到满足一定条件的行数，我们一般使用count()方法。这时候，为了获取未发送的短信数据，我们自然而然的想到了使用下面的sql语句来查询。从状态=0的短信中选择计数(*)；然后将获取的数据作为打点发送给监控服务。当数据表较小的时候，这是没有问题的，但是当数据量很大的时候，比如当未发送的短信达到百万量级时，你会发现上面的SQL查询时间会变得很长，在end，timeout报错，结果找不到。为什么？先从count()方法的原理说起。count()的原理count()方法的目的是计算当前SQL语句查询得到的非NULL行数。我们知道mysql分为server层和存储引擎层。在Mysql架构的存储引擎层，可以选择各种引擎进行存储，最常见的有innodb和myisam。可以通过建表sql中的ENGINE字段指定使用哪种存储引擎。比如本文开头的建表SQL中使用ENGINE=InnoDB，那么这张表使用的是innodb引擎。count()方法虽然是在server层调用的，但是在不同的存储引擎下它们的实现方式是不一样的。例如，它还读取了整个表数据selectcount(*)fromsms;陈述。在使用myisam引擎的数据表中，有一个字段记录了当前表中有多少行数据。直接读取这个字段返回即可，所以速度这么快。对于使用innodb引擎的数据表，会选择尺寸最小的索引树，然后通过遍历叶子节点个数，逐一累加，这样也可以获得全表数据。那么回到文章开头的问题，当数据表的行数变大时，单次计数需要扫描大量的数据，所以很可能会出现超时错误。那么问题来了。为什么innodb不能像myisam一样实现count()方法呢？myisam和innodb引擎之间有几个明显的区别。这是千篇一律的作文的常见测试。最大的区别是myisam不支持事务，而innodb支持事务。对于事务，有四种隔离级别，其中默认的隔离级别是可重复读隔离级别（RR）。四层隔离级别的InnoDB引擎通过MVCC实现了可重复的隔离级别。开启事务后，多次执行同一个selectsnapshotread，才能读到同一个数据。那么让我们看一个例子。为什么innodb不单独记录表行数？对于两个事务A和B，sms表假设一开始只有2条数据，所以事务A一开始确实读取了2条数据。事务B在此期间插入了1条数据，而数据库实际上有3条数据，但是由于可重复读隔离级别，事务A仍然只能读取2条数据。所以，由于事务隔离级别的存在，不同事务同时看到的表中的数据行数是不一致的，所以innodb，没办法，也不需要简单的加一个count字段数据表中的信息类似于myisamsuperior。那么如果count()是不可避免的，有没有办法让它更快？各种count()方法的原理在count()的括号里，可以放各种奇奇怪怪的东西。你一定见过，比如放个星号*，放个1，放个索引列什么的。我们来分析一下他们的执行过程。count方法的一般原理是server层会从InnoDB存储引擎中读取一行数据，只累加非空值。但是根据count()方法中括号中传递的参数不同，这个过程略有不同。count(*)server层获取innodb返回的行数据，不对里面的行数据做任何分析判断。默认取回的值肯定不为null，行数直接+1。count(1)server层获取innodb返回的行数据，每行置1。默认不可能为null，行数+1。count(某列字段)表示要统计某字段，innodb取数据时会解析该字段返回给server层，所以比count(1)和count(*)多了一个解析字段的过程。如果列字段为主键id，则主键不能为null，所以server层不需要判断是否为null。innodb每返回一行，行号的结果就会+1。如果这一列是普通索引字段，innodb一般会走普通索引，每返回一行数据，server层会判断该字段是否为null，不为null则+1。当然，如果表sql中的字段定义不为null，则不需要做这一步判断，直接+1。如果该列还没有被索引，那么InnoDB可能会扫描全表，对于返回的每一行数据，server层都会判断该字段是否为null，不为null则+1。如上例，在字段中加上notnull就省去了这一步判断。了解原理后，我们大概可以知道他们的性能排序是count(*)≈count(1)>count(primarykeyid)>count(commonindexcolumn)>count(unindexedcolumn)，所以count(*),已经是最快的了。知道真相后，我泪流满面。还有其他更好的方法吗？允许粗略估计行数的场景我们回头看看文章开头的需求。我们只想知道数据库中累积了多少条未发送到那里的SMS消息。是1k还是2k其实差不多一个档次等等，等到了百万以上，具体的数值已经不重要了。我们知道现在堆得可笑，这就够了。因此，在这种情况下，实际上允许进行粗略估计。那么我们如何才能得到一个粗略的值呢？还记得我们平时用来查看sql执行计划的explain命令吗。其中有rows，会用来预估接下来要执行这条sql需要扫描检查多少行。它是通过抽样计算的。虽然会有一定的偏差，但可以反映出一定的数量级。Rowsinexplain某些语言的ORM可能没有专门的explain语法，但肯定有执行rawsql的功能。可以将explain语句作为rawsql传入，从返回结果中读出rows列使用。一般来说，如果explain的SQL可以使用索引，会比不使用索引的情况更准确。单个字段上的索引会比多个字段组成的复合索引更准确。索引区分度越高，行的值就越准确。这种情况几乎可以满足大部分监控场景。但是总有一些场景需要获取准确的行数。在这种情况下我该怎么办？必须准确估计行数的场景很头疼，但也不是不可以。我们可以单独拉一个新的数据库表，只是为了保存各种场景下的计数。CREATETABLE`count_table`(`id`intNOTNULLAUTO_INCREMENTCOMMENT'主键',`cnt_what`char(20)NOTNULLDEFAULT''COMMENT'需要计算的各种指标',`cnt`tinyintNOTNULLCOMMENT'cnt指标值',PRIMARYKEY(`id`),KEY`idx_cnt_what`(`cnt_what`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;count_table表保存了各种场景下的count当某个场景需要获取cout值时，可以直接使用下面的sql读取，速度非常快。selectcntfromcount_tablewherecnt_what="未发送短信的数量";这些计数的结果值从何而来？这里有两种情况。实时性要求高的场景如果对cnt的计算结果实时性要求高，需要在变更行数对应的事务中添加更新cnt的SQL。比如我们有两个事务A和B，分别是增加未发送短信和减少未发送短信。将改变表行数的操作放到事务中的好处是事务中的cnt行数仍然满足隔离级别。当事务回滚时，cnt的值也会回滚。缺点也很明显。当多个线程写入同一个cnt时，会触发悲观锁，需要多个线程互相等待。对于高频写入场景，性能会有所打折扣。在实时性要求不高的场景下，如果对实时性要求不高，比如一天一次，那么可以扫描全表后再进行计算。比如上面的短信表，可以按照id排序，每次取出10000条数据，记录本批最大id，下次从最大id开始取出10000条数据，和继续循环。对于未发送的短信，只需要在捞出的1w条数据中过滤掉state=0的条数即可。Batch批量获取短信表。当然，如果条件允许，这种场景最好的方式是消费binlog将数据导入到hive中，然后在hive中进行查询。许多公司已经有了可以做这种事情的现成组件。不用自己编写脚本不是很好吗？Mysql同步hive总结Mysql采用count方式对全表数据进行校验，在不同的存储引擎中实现方式不同。Myisam有个专门的字段记录整个表的行数，直接读取这个字段即可。而innodb需要逐行计算。从性能上来说，count(*)≈count(1)>count(primarykeyid)>count(commonindexcolumn)>count(unindexedcolumn)，但即使是性能最好的count(*)也需要计算行byline，所以在数据量大的时候功能不强。如果确实需要获取行数，并且可以接受不太精确的行数（只需要判断大概的大小），可以使用explain中的行，可以满足大部分监控场景，容易实现实施。如果要求行数准确，可以新建一个表，表中包含表的行数信息。如果实时性要求比较高，可以把更新行数的sql放到对应的事务中，这样既可以满足事务隔离，又可以快速读取行数信息。如果实时性要求不高，可以接受一小时或一天的更新频率，那么可以自己写脚本遍历整张表，更新行号信息。也可以通过监控binlog将数据导入hive，需要数据的时候直接通过hive计算。参考《丁奇mysql45讲》
