MySQL中聚合函数count的使用和性能优化技巧

本文的环境是Windows 10，MySQL版本是5.7.12-log

一、 基本使用

count的基本作用是有两个：

  • 统计某个列的数据的数量；
  • 统计结果集的行数；

用来获取满足条件的数据的数量。但是其中有一些与使用中印象不同的情况，比如当count作用一列、多列、以及使用*来表达整行产生的效果是不同的。

示例表如下：

CREATE TABLE `NewTable` (
`id` int(11) NULL DEFAULT NULL ,
`name` varchar(30) NULL DEFAULT NULL ,
`country` varchar(50) NULL DEFAULT NULL ,
`province` varchar(30) NULL DEFAULT NULL ,
`city` varchar(30) NULL DEFAULT NULL
)ENGINE=InnoDB

1.1 不计算NULL的值

如果有NULL值，在返回的结果中会被过滤掉

select count(country) from person;

返回结果如下：

如果满足条件的数据项不存在，则结构返回0，经常通过这种方式判断是否有满足条件的数据存在；返回的数据类型是bigint。

1.2 对count(*)的处理

count(*)的处理是有点不同的，它会返回所有数据的数量，但是不会过滤其中的NULL值，它也并不是相当于展开成所有的列，而是直接会忽略所有的列而直接统计所有的行数。语句如下：

select count(*) from person;

返回结果如下：

当想要返回所有的数据的数量的时候，但是又不想包括全部是NULL的列，使用count(*)是不可能做到的，但是在1.1中说到count作用于列的时候会过滤NULL，那么直接这么写是不是对？

select count(id, `name`, country, province, city) from person;

那就错了，count只能作用于单列，不能作用于多列 ，所以上面的写法是错误的。

另外针对count(*)语句，在MyISAM存储引擎中做了优化，每个表的数据行数都会存储在存储引擎中，可以很快拿到；但是在事务性的存储引擎中，比如InnoDB中，因为会涉及到多个事务；

1.3 对count(distinct …)的处理

count(distinct …)会返回彼此不同但是非NULL的数据的行数。这一点和只使用distinct是有区别的，因为distinct是不过滤NULL值的，详见MySQL中distinct的使用方法 。

- 如果没有符合条件的数据则返回0；
- 该语句可以作用于多列，是当各个列之间有一个不同，就认为整行数据不同，与distinct作用于多列时效果相同；

select count(DISTINCT country) from person;

返回结果如下：

但是对于count(*)和count(distinct )两者的结合，如下：

select count(DISTINCT *) from person;

该语句是错误的，无法执行，因此与select count(DISTINCT *) from person 还是有区别的。

二、 性能优化

通常情况下，count(*)操作需要大量扫描数据表中的行，如果避免扫描大量的数据就成为优化该语句的关键所在。针对这个问题可以从如下两个角度考虑。

2.1 在数据库的层次上优化

2.1.1 针对count(*)

在MySQL内部已经针对count(*)进行了优化，使用explain查询如下：

EXPLAIN select count(*) from person;

从中可以看出该查询没有使用全表扫描也没有使用索引，甚至不需要查询数据表，在上面的示例数据库中得知，该库的存储引擎是InnoDB ，而且其中既没有主键也没有索引。

2.2 针对单个列进行count

查询如下：

EXPLAIN select count(country) from person where id > 2;

发现在没有主键和索引的情况下，对全表进行了扫描。在数据中避免大量扫描数据行，一个最直接的方法使用索引：

当对id设置为一般索引 ：INDEX abc (id) USING BTREE 。

执行查询如下：

EXPLAIN select count(country) from person where id > 2;

结果如下：

此时发现并没有使用索引，仍然进行的是全表扫描，当执行如下时：

EXPLAIN select count(country) from person where id > 4;

结果如下：

这是使用了索引进行了范围查询，显然比上面的要好。

但是问题来了，为什么有时候使用索引，有时候不用索引？在上面的第一次查询中已经能够检测出可能的key但是并没有使用？如果有知道的大神给解读一下！

对id设置为主键，执行查询如下：

EXPLAIN select count(country) from person where id > 2;

结果如下：

2.2 在应用的层次上优化

在应用的层次上优化，可以考虑在系统架构中引入缓存子系统，比如在过去中常用的Memcached，或者现在非常流行的Redis， 但是这样会增加系统的复杂性。

mysql group by与聚合函数(sum,count等)实例

首先我们先来了解一下mysql聚合函数

mysql中一种特殊的函数:聚合函数，SUM, COUNT, MAX, MIN, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。例如：

SELECT SUM(score) FROM table

这个sql的意思是查询表table里面所有score列的总和。

接着我们通过一个实例来讲解group by语句中如何使用聚合函数。

book表如下：

id first_name last_name city
1 Jason Martin Toronto
2 Alison Mathews Vancouver
3 James Mathews Vancouver
4 Celia Rice Vancouver
5 David Larry New York

现在我们要对city进行分组查询，并获取每个分组有多少条数据，我们需要count聚合函数。

SELECT *,count(*) FROM book GROUP BY city

结果为：

id first_name last_name city count(*)
1 Jason Martin Toronto 1
2 Alison Mathews Vancouver 3
5 David Larry New York 1

先以city把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段(一或多条记录)作运算。

赞 (0)

相关推荐

  • MySQL笔记之函数查询的使用

    MySQL笔记之函数查询的使用

    参考表 count()用来统计记录的条数 复制代码 代码如下: mysql> select count(*) from score;mysql> select count(*) from score where stu_id=901; sum()求和函数 复制代码 代码如下: mysql> select sum(grade) from score;mysql> select id,sum(grade) from score where stu_id=901; avg()求平均值函数

  • MySql 中聚合函数增加条件表达式的方法

    Mysql 与聚合函数在一起时候where条件和having条件的过滤时机 where 在聚合之前过滤 当一个查询包含了聚合函数及where条件,像这样的情况 select max(cid) from t where t.id<999 这时候会先进行过滤,然后再聚合.先过滤出ID<999的记录,再查找最大的cid返回. having 在聚合之后过滤 having在分组的时候会使用,对分组结果进行过滤,通常里面包含聚合函数. SELECT ip,MAX(id) FROM app GROUP BY

  • MySQL中对查询结果排序和限定结果的返回数量的用法教程

    MySQL Order By 查询结果排序 ORDER BY SQL 语法中 ORDER BY 关键字用于对查询结果进行排序. 排序分为升序(ASC)和降序(DESC)两种,当不使用 ORDER BY 指定排序方式时,默认为升序. 语法: SELECT column,- FROM tb_name ORDER BY column1,column2,- DESC(ASC) ORDER BY 后面必须列出排序的字段名,可以是多个字段. 对 user 表 uid 进行降序查询: SELECT uid,u

  • 将MySQL查询结果按值排序的简要教程

    MySQL查询结果如何排序呢?这是很多人都提过的问题,下面就教您如何对MySQL查询结果按某值排序,如果您感兴趣的话,不妨一看. 之前有一个功能修改,要求MySQL查询结果中: id name * * * 1 lucy ... 3 lucy ... 2 lily ... 4 lucy ... 名字为lucy的优先排在前面,百思不得其解,可能有人会说简单 union嘛 或者弄个临时表什么的,其实我也想过,但是本身SQL逻辑就很多了(上面只是简例),再union的话或者临时表可能绕很大的弯路,后来看

  • 浅谈Mysql指定顺序排序查询

    最近做一个大屏展示项目,类似于机场,火车站那种展示班次信息的那种,不过展示的内容要复杂的多,其中部分数据如下: 前端主要用Vue框架,要求后端数据在前端展示, 第一:进厂,出厂指标不固定(可能6,也可能以后7,8个): 第二:无"数值"值的用斜杠代替: 第三:进出厂指标名字一定要符合如上图指定的名字(后端数据库查询出来的名字和前端指定的名字不一样,以及单位中带有 ">1",">0.3"之类的,所以不能直接从后端查询 ,名字+单位 放在

  • MySQL常用聚合函数详解

    一.AVG AVG(col) 返回指定列的平均值 二.COUNT COUNT(col) 返回指定列中非NULL值的个数 三.MIN/MAX MIN(col):返回指定列的最小值 MAX(col):返回指定列的最大值 四.SUM SUM(col) 返回指定列的所有值之和 五.GROUP_CONCAT GROUP_CONCAT([DISTINCT] expr [,expr ...]              [ORDER BY {unsigned_integer | col_name | expr}

  • 数据库查询排序使用随机排序结果示例(Oracle/MySQL/MS SQL Server)

    Oracle查询结果集,随机排序 复制代码 代码如下: select * from table1 order by dbms_random.value(); MySQL随机查询出一条记录: 复制代码 代码如下: -- 下面的查询语句效率高,不要使用 SELECT * FROM table1 ORDER BY rand() LIMIT 1 来查询 SELECT * FROM table1 WHERE id=(SELECT id FROM table1 ORDER BY rand() LIMIT 1

  • MySQL查询优化:连接查询排序浅谈

    情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team(id int primary key,tname varchar(100)); create table t_people(id int primary key,pname varchar(100),team_id int,foreign key (team_id) referen

  • Mysql数据库使用concat函数执行SQL注入查询

    SQL注入语句有时候会使用替换查询技术,就是让原有的查询语句查不到结果出错,而让自己构造的查询语句执行,并把执行结果代替原有查询语句查询结果显示出来. 例如:原本查询语句是 复制代码 代码如下: select username,email,content from test_table where user_id=uid; 其中uid,是用户输入的.正常显示结果会出现用户名,用户邮箱,用户留言内容.但是如果uid过滤不严,我们可以构造如下SQL语句来获得任意数据表信息. 复制代码 代码如下: u

  • mysql 查询数据库中的存储过程与函数的语句

    方法一: select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE' //存储过程 select `name` from mysql.proc where db = 'your_db_name' and `type` = 'FUNCTION' //函数 方法二: show procedure status; //存储过程 SHOW PROCEDURE STATUS WHERE db='serva

  • MySQL查询排序与查询聚合函数用法分析

    本文实例讲述了MySQL查询排序与查询聚合函数用法.分享给大家供大家参考,具体如下: 排序 为了方便查看数据,可以对数据进行排序 语法: select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...] 说明 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推 默认按照列值从小到大排列(asc) asc从小到大排列,即升序 desc从大到小排序,即降序 例1:查询未删除男生信息,按学号降序 select * from st

  • MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍

    不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低. 情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team ( id int primary key, tname varchar(100) ); create table t_people (

随机推荐