优化数据统计的终极指南-MySQL-提升查询性能的秘诀 (优化数据统计工具)
在业务场景中,我们经常需要统计当前已有的业务数据,例如商品库内商品的数量、每天的用户订单数量等。此时,我们需要使用统计功能来实现。
对于不同的数据库引擎,count()的实现逻辑也不同。
InnoDB
InnoDB利用多版本控制机制支持事务,一行记录会记录多个MVCC。统计行数这一行为与隔离级别直接相关。在RR级别下,每一行记录都要判断自己是否对这个会话可见,每个会话也会执行增删改操作,导致每个事务统计的行数不一致。因此,对于count()来说,InnoDB只好把数据一行行读出来,对可见的行进行统计。因此,InnoDB不能像MyISAM引擎一样在磁盘保存数据行树。
以下是一个示例:
![提升查询性能的秘诀](https://tyye.cn/zb_users/theme/tpure/style/images/lazyload.png)
CREATE TABLE test (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
执行以下语句创建表并插入数据:
INSERT INTO test (name) VALUES ('test1');
INSERT INTO test (name) VALUES ('test2');
INSERT INTO test (name) VALUES ('test3');
执行count()统计行数:
SELECT COUNT() FROM test;
输出结果为:
3
在这个示例中,会话C没有显示开始事务,因此每条语句都是独立事务。由于AB会话都没有提交事务,因此,AB的修改对C不可见。事实上,InnoDB对count()做了一定优化,由于InnoDB是索引组织表,主键索引树的叶子节点是数据,普通索引树的叶子阶段是主键值,因此,普通索引树比主键索引树小很多。执行count()的逻辑就是遍历,因此,MySQL优化器会选择最小的索引树用于遍历,相对于每次都读取所有的数据行,只是遍历主键,自然IO开销要小的多。
因此,在保证逻辑正确的前提下,尽量减少扫描的数据量,是数据库系统设计的通用法则之一。
还有一个显示行数的命令为:showtablestatus;也会显示表的行数,但是这里的rows是预估值,这个预估值是根据随机采集计算出来的,MySQL随机取N页数据,计算出每页中不同记录数,求取平均值后乘以总页数得到的就是预估值。这个预估值是否接近真实值,取决于索引字段区分度、索引数据页紧凑程度、是否存在页分裂、索引空洞等元素。这个预估值也是造成MySQL选错索引的原因。
如何实现计数逻辑
使用缓存系统统计计数
如果是一般场景,使用缓存系统执行计数是满足需求的,即使说,由于服务集群异常重启导致数据丢失,但是可以再次扫描一次表获取表的总数。但是如果是非常严谨的场景(银行统计实际支付的订单数据等),那可能有如下的问题。
- 第一个是缓存可能会丢失数据,即使是开启持久化,还是存在丢失数据可能性。redis持久化有RDB和AOF两种方式;RDB按照备份策略,比如60秒1000个k-v被修改,备份过程中宕机,那么这个阶段的所有更新都会丢失;AOF按照备份策略,比如endfsyncalways策略,同步记录所执行的指令到日志文件,但是它的日志和mysql的WAL不同,它是写后日志,可能指令执行后写日之前宕机,那这个数据就丢失了,虽然丢失数据较少且概率较低,但依然存在这个可能。
- 第二个是数据一致性保证问题,Redis和MySQL是两个数据源,可以看成是一种分布式一致性的问题,而分布式一致性由于不能保证原子性,因此一般只能保证最终一致性,不能保证实时一致性。
数据一致性问题
数据一致性问题目前可分为三类:
- 主从不一。解决办法:半同步复制可以保证实时的一致性,因为写时写主和从之后才响应,只不过这样写的并发上不去;其他访问有强制读主、消息中间件路由读主和缓存是否失效读主;
- 数据库与缓存的不一。解决办法:读操作直接读缓存,写操作先更新到数据库,淘汰缓存(程序需要保证两个操作的原子性,如果淘汰失败,则发一条小实现异步淘汰).由于该key的缓存已经清理掉,那么下次读的时候需要先读数据库,在重建缓存.由于redis是单线程,保证了一个操作的原子性.可以通过设置appendfsyncalways来保证每次操作都把该操作记录并落盘到aof文件里(不过一般redis该值为everysec),毕竟使用redis的目的不是为了保证acid.还是要根据业务来选择。
- 一个事务跨多个节点或者多种数据库(分库分表和银行转账这种例子)。目前好像都是通过2pc,3pc来保
1.存储引擎的选择如果数据表需要事务处理,应该考虑使用InnoDB,因为它完全符合ACID特性。 如果不需要事务处理,使用默认存储引擎MyISAM是比较明智的。 并且不要尝试同时使用这两个存储引擎。 思考一下:在一个事务处理中,一些数据表使用InnoDB,而其余的使用MyISAM.结果呢?整个subject将被取消,只有那些在事务处理中的被带回到原始状态,其余的被提交的数据转存,这将导致整个数据库的冲突。 然而存在一个简单的方法可以同时利用两个存储引擎的优势。 目前大多数MySQL套件中包括InnoDB、编译器和链表,但如果你选择MyISAM,你仍然可以单独下载InnoDB,并把它作为一个插件。 很简单的方法,不是吗?2.计数问题如果数据表采用的存储引擎支持事务处理(如InnoDB),你就不应使用COUNT(*)计算数据表中的行数。 这是因为在产品类数据库使用COUNT(*),最多返回一个近似值,因为在某个特定时间,总有一些事务处理正在运行。 如果使用COUNT(*)显然会产生bug,出现这种错误结果。 3.反复测试查询查询最棘手的问题并不是无论怎样小心总会出现错误,并导致bug出现。 恰恰相反,问题是在大多数情况下bug出现时,应用程序或数据库已经上线。 的确不存在针对该问题切实可行的解决方法,除非将测试样本在应用程序或数据库上运行。 任何数据库查询只有经过上千个记录的大量样本测试,才能被认可。 4.避免全表扫描通常情况下,如果MySQL(或者其他关系数据库模型)需要在数据表中搜索或扫描任意特定记录时,就会用到全表扫描。 此外,通常最简单的方法是使用索引表,以解决全表扫描引起的低效能问题。 然而,正如我们在随后的问题中看到的,这存在错误部分。 5.使用“EXPLAIN”进行查询当需要调试时,EXPLAIN是一个很好的命令,下面将对EXPLAIN进行深入探讨。
免责声明:本文转载或采集自网络,版权归原作者所有。本网站刊发此文旨在传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及版权、内容等问题,请联系本网,我们将在第一时间删除。同时,本网站不对所刊发内容的准确性、真实性、完整性、及时性、原创性等进行保证,请读者仅作参考,并请自行核实相关内容。对于因使用或依赖本文内容所产生的任何直接或间接损失,本网站不承担任何责任。