MySQL select count(*)计数很慢优化方案

目录
  • 前言
  • 1. MyISAM存储引擎计数为什么这么快？
  • 2. 能不能手动实现统计总行数
  • 3. InnoDB引擎能否实现快速计数
  • 4. 四种计数方式的性能差别

前言

在日常开发工作中，我经常会遇到需要统计总数的场景，比如：统计订单总数、统计用户总数等。一般我们会使用MySQL 的count函数进行统计，但是随着数据量逐渐增大，统计耗时也越来越长，最后竟然出现慢查询的情况，这究竟是什么原因呢？本篇文章带你一下学习一下。

1. MyISAM存储引擎计数为什么这么快？

我们总有个错觉，就是感觉MyISAM引擎的count计数要比InnoDB引擎更快，实际这不是错觉。

MyISAM引擎把表的总行数单独记录在磁盘上，查询的时候可以直接返回，不需要再累加统计。

但是当SQL查询中有where条件的时候，就无法再使用表的总行数了，还是需要乖乖的进行累加统计，查询性能也就跟InnoDB相差无几了。

为什么MyISAM引擎能够记录表的总行数，InnoDB引擎却不行？

因为MyISAM引擎不支持事务，只有表锁，所以记录的总行数是准确的。

而InnoDB引擎支持事务和行锁，存在并发修改的情况。又由于事务的隔离性，会出现不可重复读和幻读，记录的总行数无法保证是准确的。

2. 能不能手动实现统计总行数

既然InnoDB引擎没有帮我们记录总行数，我们能不能手动记录总行数，比如使用Redis。

其实也是不行的，使用Redis记录总行数，至少有下面3个问题：

  • 无法实现事务之间的隔离
  • 更新丢失，因为i++不是原子操作，当然可以使用Lua脚本实现原子操作，更复杂。
  • Redis是非关系型缓存数据库，不能当作关系型持久化数据库使用，一般需要设置过期时间。

由上图中得知，虽然Redis计数加1操作放在了事务里面，但是不受事务控制的，在事务没有提交前，其他查询依然读到了最新的总行数，这就是脏读的情况。

3. InnoDB引擎能否实现快速计数

有一种办法，可以粗略估计表的总行数，就是使用MySQL命令：

show table status like 'user';

真实的总行数有100万行，预估有99万多行，误差在可接受的范围内。

部分场景适用，比如粗略估计网站的总用户数。

4. 四种计数方式的性能差别

常见的统计总行数的方式有以下四种：

count(*) 、 count(常量) 、 count(id) 、 count(字段)

InnoDB引擎对count计数做了优化，会选用数据量较小的非聚簇索引进行统计。

比如用户表中有三个索引，分别是主键索引、name索引和age索引，使用执行计划查看计数的时候用到了哪个索引？

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  `age` tinyint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB COMMENT='用户表';
explain select count(*) from user;

用到了数据量较小的age索引。

count(*) 、 count(常量) 是直接统计表中的总行数，效率较高。

而 count(id) 还需要把数据返回给MySQL Server端进行累加计数。

最后 count(字段)需要筛选不为null字段，效率最差。

四种计数的查询性能从高到低，依次是：

count(*) ≈ count(常量) > count(id) > count(字段)

对于大多数情况，得到计数结果，还是老老实实使用count(*)

所以推荐使用select count(*) ，别跟select * 搞混了，不推荐使用select * 的。

到此这篇关于MySQL select count(*)计数很慢优化方案的文章就介绍到这了,更多相关MySQL 优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • mysql技巧之select count的区别分析

    mysql技巧之select count的区别分析

    1.测试环境 OS:LinuxDB:mysql-5.5.18table:innodb存储引擎 表定义如下: 2. 测试场景与分析[统计表group_message的记录数目] (1)select count(*)方式 (2)select count(1)方式 (3)select count(col_name)方式 分别使用 select count(group_id) select count(user_id) select count(col_null) 通过上述测试结果可以看到,select

  • 为什么MySQL不建议使用SELECT *

    为什么MySQL不建议使用SELECT *

    目录 1. 不必要的磁盘I/O 2. 加重网络时延 3. 无法使用覆盖索引 4. 可能拖慢JOIN连接查询 “不要使用SELECT *”几乎已经成为了MySQL使用的一条金科玉律,就连<阿里Java开发手册>也明确表示不得使用*作为查询的字段列表,更是让这条规则拥有了权威的加持. 不过我在开发过程中直接使用SELECT *还是比较多的,原因有两个: 因为简单,开发效率非常高,而且如果后期频繁添加或修改字段,SQL语句也不需要改变: 我认为过早优化是个不好的习惯,除非在一开始就能确定你最终实际需

  • MySQL运行报错:“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”解决方法

    MySQL运行报错:“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”解决方法

    目录 项目场景: 问题描述 原因分析: 解决方案: 总结 项目场景: 最近在部署项目之后,运行出现报错: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘grades.order_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible wi

  • MySQL之select、distinct、limit的使用

    MySQL之select、distinct、limit的使用

    目录 1.简介 2.select 2.1 查询单个列 2.2 查询多个列 2.3 查询所有列 3.distinct 4.limit 1.简介 这篇博客将会非常基础,如果有MySQL经验的可以跳过,写这篇博客的原因是给初学者看的.下面将会讲解如何使用select查看指定表的单个列.多个列以及全部列. 首先准备一张表,表结构如下所示: SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- T

  • 解决MySQL读写分离导致insert后select不到数据的问题

    MySQL设置独写分离,在代码中按照如下写法,可能会出现问题 // 先录入 this.insert(obj); // 再查询 Object res = this.selectById(obj.getId()); res: null; 线上的一个坑,做了读写分离以后,有一个场景因为想方法复用,只传入一个ID就好,直接去库里查出一个对象再做后续处理,结果查不出来,事务隔离级别各种也都排查了,最后发现是读写分离的问题,所以换个思路去实现吧. 补充知识:MySQL INSERT插入条件判断:如果不存在则

  • MySQL将select结果执行update的实例教程

    MySQL将select结果执行update的实例教程

    一.单表查询->更新 UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] 二.多表联合查询->更新 UPDATE a INNER JOIN (SELECT yy FROM b) c ON a.id = c.id SET a.xx = c.yy [WHERE Clause] 上面的 INNER JOIN ,可以换为 LEFT JOIN . RIGHT JOIN 等联合查询. SET 后的字段必须为 a

  • MySQL select、insert、update批量操作语句代码实例

    项目中经常的需要使用批量操作语句对数据进行操作,使用批量语句的时候需要注意MySQL数据库的条件限制,这里主要是数据的大小限制,这里对批量插入.批量查找及批量更新的基础操作进行下简单的汇总. 1.批量insert插入语句,如下的语句在测试环境尝试过1W+的批量插入,具体数量跟表结构及字段内容有关系. <insert id="addTextPushList"paramerterType = "java.util.List"> INSERT INTO SYS

  • MySQL select count(*)计数很慢优化方案

    MySQL select count(*)计数很慢优化方案

    目录 前言 1. MyISAM存储引擎计数为什么这么快? 2. 能不能手动实现统计总行数 3. InnoDB引擎能否实现快速计数 4. 四种计数方式的性能差别 前言 在日常开发工作中,我经常会遇到需要统计总数的场景,比如:统计订单总数.统计用户总数等.一般我们会使用MySQL 的count函数进行统计,但是随着数据量逐渐增大,统计耗时也越来越长,最后竟然出现慢查询的情况,这究竟是什么原因呢?本篇文章带你一下学习一下. 1. MyISAM存储引擎计数为什么这么快? 我们总有个错觉,就是感觉MyIS

  • 详解 MySQL中count函数的正确使用方法

    1. 描述 在MySQL中,当我们需要获取某张表中的总行数时,一般会选择使用下面的语句 select count(*) from table; 其实count函数中除了*还可以放其他参数,比如常数.主键id.字段,那么它们有什么区别?各自效率如何?我们应该使用哪种方式来获取表的行数呢? 当搞清楚count函数的运行原理后,相信上面几个问题的答案就会了然于胸. 2. 表结构 为了解决上述的问题,我创建了一张 user 表,它有两个字段:主键id和name,后者可以为null,建表语句如下. CRE

  • 详细解读MySQL中COUNT函数的用法

    MySQL的COUNT函数是最简单的功能,非常有用的计算,预计由一个SELECT语句返回的记录数. 要了解COUNT函数考虑的EMPLOYEE_TBL的的表具有以下记录: mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+----

  • 浅谈MySQL之select优化方案

    目录 生活中的例子 慢查询 如何去优化 count limit 最大值最小值min&max 生活中的例子 我们是否看到过在公司中许多查询语句都是select * xxxx 心中的想法肯定是,别人写了select *,那我写吧,省去了不少麻烦事儿 慢查询 首先去思考,最基本的,是否我们使用的数据库插查询语句存在了访问的数据太多 其实大部分性能低的查询往往都可以通过减少访问的数据量来优化的 因为select * 会给服务器带来额外的I/O.内存和cpu的消耗 数据库中慢查询开销的三个指标 相应时间

  • mysql查询优化之100万条数据的一张表优化方案

    1.两种查询引擎查询速度(myIsam 引擎 ) InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行. MyISAM只要简单的读出保存好的行数即可. 注意的是,当count(*)语句包含 where条件时,两种表的操作有些不同,InnoDB类型的表用count(*)或者count(主键),加上where col 条件.其中col列是表的主键之外的其他具有唯一约束索引的列.这样查询时速度会很快.就是可

  • MySQL优化方案之开启慢查询日志

    MySQL优化方案之开启慢查询日志

    目录 前言 设置慢查询日志 测试 附:日志分析工具mysqldumpslow 总结 前言 本方案只适应于小的项目.项目未上线或者紧急情况下可采用这种方式,一旦开启慢日志查询会增加数据库的压力,所以一般采用后台对数据操作时间写入日志文件中,每一周定时进行清除日志 mysql优化方案:开启慢查询日志(查询sql执行超过一秒以上sql等等) 开启慢查询日志:可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能. 参数说明: slow_query_log

  • Mysql性能优化方案分享

    网上有不少mysql 性能优化方案,不过,mysql的优化同sql server相比,更为麻烦,同样的设置,在不同的环境下 ,由于内存,访问量,读写频率,数据差异等等情况,可能会出现不同的结果,因此简单地根据某个给出方案来配置mysql是行不通的,最好能使用status信息对mysql进行具体的优化. mysql> show global status; 可以列出MySQL服务器运行各种状态值,另外,查询MySQL服务器配置信息语句: mysql> show variables; 一.慢查询

  • mysql 查询重复的数据的SQL优化方案

    在mysql中查询不区分大小写重复的数据,往往会用到子查询,并在子查询中使用upper函数来将条件转化为大写.如: 复制代码 代码如下: select * from staticcatalogue WHERE UPPER(Source) IN (SELECT UPPER(Source) FROM staticcatalogue GROUP BY UPPER(Source) having count(UPPER(Source))>1) ORDER BY upper(Source) DESC; 这条

  • 浅谈MySQL大表优化方案

    背景 阿里云RDS FOR MySQL(MySQL5.7版本)数据库业务表每月新增数据量超过千万,随着数据量持续增加,我们业务出现大表慢查询,在业务高峰期主业务表的慢查询需要几十秒严重影响业务 方案概述 一.数据库设计及索引优化 MySQL数据库本身高度灵活,造成性能不足,严重依赖开发人员的表设计能力以及索引优化能力,在这里给几点优化建议 时间类型转化为时间戳格式,用int类型储存,建索引增加查询效率 建议字段定义not null,null值很难查询优化且占用额外的索引空间 使用TINYINT类

  • MySQL limit分页大偏移量慢的原因及优化方案

    在 MySQL 中通常我们使用 limit 来完成页面上的分页功能,但是当数据量达到一个很大的值之后,越往后翻页,接口的响应速度就越慢. 本文主要讨论 limit 分页大偏移量慢的原因及优化方案,为了模拟这种情况,下面首先介绍表结构和执行的 SQL. 场景模拟 建表语句 user 表的结构比较简单,id.sex 和 name,为了让 SQL 的执行时间变化更加明显,这里有9个姓名列. CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREME

随机推荐