mysql count(*) count(1) count(id) 讲解 千万数据测试

最新推荐文章于 2024-09-20 07:00:00 发布
置顶 最新推荐文章于 2024-09-20 07:00:00 发布
阅读量609 收藏
点赞数
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fashionbrot/article/details/106478986
版权

数据100万增加到 1000万测试数据

环境 win10 8g内存 mysql5.7.27
mysql my.ini 配置 注意（innodb_flush_log_at_trx_commit=0）配置
[Client]
#设置3306端口
port = 3306
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=E:devmysql-5.7.27-winx64
# 设置mysql数据库的数据的存放目录
datadir=E:devmysql-5.7.27-winx64data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 插入数据时设置为0提高插入数据,插入数据完成修改为1
innodb_flush_log_at_trx_commit=0  

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

1、创建表

CREATE TABLE `test_count` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `test1` varchar(100) DEFAULT NULL,
  `test2` varchar(100) DEFAULT NULL,
  `test3` varchar(100) DEFAULT NULL,
  `test4` varchar(100) DEFAULT NULL,
  `test5` varchar(100) DEFAULT NULL,
  `test6` varchar(100) DEFAULT NULL,
  `test10` varchar(100) DEFAULT NULL,
  `test11` varchar(100) DEFAULT NULL,
  `test12` varchar(100) DEFAULT NULL,
  `test13` varchar(200) DEFAULT NULL,
  `test14` varchar(200) DEFAULT NULL,
  `test15` varchar(200) DEFAULT NULL,
  `test16` varchar(200) DEFAULT NULL,
  `test17` varchar(200) DEFAULT NULL,
  `test18` varchar(200) DEFAULT NULL,
  `test19` varchar(200) DEFAULT NULL,
  `test20` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

2、创建存储过程

CREATE  PROCEDURE `test_count_dure`(IN n int)
BEGIN  
  DECLARE i INT DEFAULT 1;
    WHILE (i <= n ) DO
      INSERT into test_count  (name,age,create_time,test1,test2,test3,test4,test5,test6,test10,test11,test12,test13,test14,test15,test16,test17,test18,test19,test20 ) VALUEs 
															(i,100 ,now(),'213dfdsjfdasfdsdafdffsd',
'213dfdsjfdasfdsdafdffsd','213dfdsjfdasfdsdafdffsd','213dfdsjfdasfdsdafdffsd'
,'213dfdsjfdasfdsdafdffsd','213dfdsjfdasfdsdafdffsd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd','afdasfjdlajlfkalfdakfjdlajfkalfdasfd' );
			set i=i+1;
    END WHILE;
END

3、创建100万数据

CALL test_count_dure(1000000);

4、查看表占用磁盘大小

SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size,
CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size,
CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free,
CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.tables WHERE TABLE_NAME = 'test_count';

5、执行count

count(1) count(*) count(1) 基本都在 8秒

在这里插入图片描述

强制使用 主键当做索引 时间还是 8秒

6、增加test_count 二级索引

给name 加一个普通索引
CREATE TABLE `test_count` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `test1` varchar(100) DEFAULT NULL,
  `test2` varchar(100) DEFAULT NULL,
  `test3` varchar(100) DEFAULT NULL,
  `test4` varchar(100) DEFAULT NULL,
  `test5` varchar(100) DEFAULT NULL,
  `test6` varchar(100) DEFAULT NULL,
  `test10` varchar(100) DEFAULT NULL,
  `test11` varchar(100) DEFAULT NULL,
  `test12` varchar(100) DEFAULT NULL,
  `test13` varchar(200) DEFAULT NULL,
  `test14` varchar(200) DEFAULT NULL,
  `test15` varchar(200) DEFAULT NULL,
  `test16` varchar(200) DEFAULT NULL,
  `test17` varchar(200) DEFAULT NULL,
  `test18` varchar(200) DEFAULT NULL,
  `test19` varchar(200) DEFAULT NULL,
  `test20` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

7、再次执行 count(1) count(*) count(1)

通过多次执行
count(*) count(1) 执行时间 在 0.2~0.3秒
count(id) 执行时间 在 0.3~0.4秒
通过explain 执行计划查看 使用的是二级索引 name索引 从而提高了查询速度

在这里插入图片描述

8、通过网上查询得知为什么加入一个二级索引速度就快了的原因

1）普通查询

普通查询使用主键索引是最快的，整体IO消耗也最少，使用二级索引时，由于先要根据二级索引定位到主健索引，再根据主健索引查询数据文件，IO消耗比主键索引大

2）统计查询

统计类需求，如COUNT之类的，反而使用二级索引比主键索引性能更高，因为MYSQL使用B+树，主健索引与数据共同存放在B+树叶子结点，如果使用主健索引进行统计时，每次都要扫描数据文件，当数据字段及行数较多时（数据文件较大），扫描时IO消耗较高。而二级索引只存放索引数据，索引文件比较小，不需要搜索数据文件，整体IO消耗低

count(*)=count(1)>=count(primarykey)>=count(非空字段)>=count(可为空字段)

数据增加1000万测试用例

首先看下数据磁盘占用空间 单表6G

在这里插入图片描述

test_count表增加字段
# 增加x1字段
ALTER TABLE `test_count`
ADD COLUMN `x1`  char(1) NOT NULL DEFAULT 'a';
# 增加x1字段索引
ALTER TABLE `test_count`
ADD INDEX `x1` (`x1`) USING BTREE ;
查看count（* ） 3.7秒 3~4秒之间

在这里插入图片描述

查看count（1） 3.8秒 3~4秒之间

在这里插入图片描述

查看count (id) 4.058 秒 3.9~4 秒以上

在这里插入图片描述

查看表索引情况

mysql> show index from test_count;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_count |          0 | PRIMARY  |            1 | id          | A         |     9441071 | NULL     | NULL   |      | BTREE      |         |               |
| test_count |          1 | name     |            1 | name        | A         |     7791336 | NULL     | NULL   | YES  | BTREE      |         |               |
| test_count |          1 | x1       |            1 | x1          | A         |           1 | NULL     | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set
在上面测试的时候发现默认走的是name 索引，这次为什么走了x1 索引
mysql> EXPLAIN
SELECT count(*) from test_count;
+----+-------------+------------+------------+-------+---------------+-----+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-----+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_count | NULL       | index | NULL          | x1  | 3       | NULL | 9606635 |      100 | Using index |
+----+-------------+------------+------------+-------+---------------+-----+---------+------+---------+----------+-------------+
1 row in set
查看强制使用 name当索引执行时间 15秒
x1 char(1) NO MUL a （char(1)长度）
name varchar(60) YES MUL （varchar(60)长度）

强制使用x1索引
强制使用name索引

fashionbrot
关注
专栏目录
MySQL - count(字段)、count(主键)、count(1)、count(*)的区别
易编橙 · 终身成长社群，相遇已是上上签！
07-28 4万+
该章节内容很少，只是针对MySQL - count(字段)、count(主键)、count(1)、count(*)的区别，进行的一个简短的记录。
MySQL进阶【七】—— count（*）、count（1）、count（id）到底谁更快
shehuinidaye的博客
10-02 803
前言 一直以来MySQL查询数量到底谁最快，一直是一个比较热的话题，且很多人搞不懂真相，本文将带你从底层原理去了解，到底count（*）、count（1）、count（id）、count（字段）谁更快 示例表 +-----------+-----------------------------------------------------------------------------------------------------------------------------------------
MySQL 千万级数据表 partition 实战应用
weixin_34162695的博客
02-21 263
目前系统的 Stat 表以每天 20W 条的数据量增加，尽管已经把超过3个月的数据 dump 到其他地方，但表中仍然有接近 2KW 条数据，容量接近 2GB。 Stat 表已经加上索引，直接 select … where … limit 的话，速度还是很快的，但一旦涉及到 group by 分页，就会变得很慢。 据观察，7天内的 grou...
MySQL | 知识 | count(*) 和 count(1) 有什么区别？哪个性能最好
最新发布
蘑菇蘑菇不会开花的专栏
09-20 1014
当我们对一张数据表中的记录进行统计的时候，习惯都会使用 count 函数来统计，但是 count 函数传入的参数有很多种，比如count(1)count(*)count(字段)等。到底哪种效率是最好的呢？是不是count(*)效率最差？我曾经以为count(*)是效率最差的，因为认知上会读取所有表中的字段，所以凡事带有字符的就觉得会读取表中所有的字段，当时网上有很多博客也这么说。但是，当我深入 count 函数的原理后，被啪啪啪的打脸了！
mysql sql优化
每天积累一点，一年后你会发现，自己变化很大
07-31 466
第一阶段： 1，一定要正确设计索引 2，一定要避免SQL语句全表扫描，所以SQL一定要走索引（如：一切的 > < != 等等之类的写法都会导致全表扫描） 3，一定要避免 limit 10000000,20 这样的查询 4，一定要避免 LEFT JOIN 之类的查询，不把这样的逻辑处理交给数据库 5，每个表索引不要建太多，大数据时会增加数据库的写入压力 第二阶段： 1，采用分表技术（大表分小表）
【Mysql学习笔记】count(1)、count(*)、count(id)和count(字段)
qq_42961603的博客
10-12 1507
对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。)的时候需要把数据一行一行地从引擎里读出来，然后累积计数，但是mysql对count()，MyISAM引擎中直接在磁盘存了表的总行数，因此执行count(
MYSQL--count(*),count(Id),count(1)的区别和性能对比
weixin_43680337的博客
08-11 912
下面是官方文档的描述，我理解可能有问题，但是官方文档肯定靠谱的 COUNT(*)is somewhat different in that it returns a count of the number of rows retrieved, whether or not they containNULLvalues. For transactional storage engines such asInnoDB, storing an exact row count is problemat...
MySQL中count(*)、count(1)和count(col)的区别汇总
09-09
在MySQL中，COUNT()函数是用于统计满足特定条件的行数。这个函数在数据库查询中十分常见，尤其在数据分析和报表生成时。本篇文章将详细解释COUNT(*)、COUNT(1)以及COUNT(column)这三种不同用法的区别。 1. COUNT(*)...
聊聊MySQL的COUNT(*)的性能
01-19
前言 基本职场上的程序员用来统计数据库表的行数都会使用count(*)，count(1)...1、建表并且插入1000万条数据进行实验测试： # 创建测试表 CREATE TABLE `t6` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varcha
MySQL中count(*)、count(1)和count(字段)的区别
桐花思雨
08-30 3657
目录`count(*)`、`count(1)` 和 `count(字段)` 区别`count(主键id)` 与 `count(1)``count(*)` 与 `count(字段)` count(*)、count(1) 和 count(字段) 区别 count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值 count(*)、count(主键id) 和 count(1) 都表示返回满足条件的结果集的总行数；而count
MySql 执行count(1)、count(*) 与 count(列名) 区别
qq_31455841的博客
11-14 2084
返回语句检索的行中的值不为NULL的数量。结果是一个值。如果查询结果没有命中任何记录，则返回的统计结果中，会包含值为的行数。除了和以外，还可以使用（如）来统计行数。
MySQL 中 count(*) 千万级别数据查询
lixiaoyu123456的博客
02-15 6526
MySQL中count(*)千万级别数据查询优化
mysql千万级别的数据使用count(*)查询比较慢怎么解决？
Harry的博客
08-04 9184
当MySQL中的数据量达到千万级别时，使用COUNT()查询可能会变得比较慢。一些优化COUNT(*)查询的方法：使用索引、使用近似值、使用缓存、分批查询、数据分片等技术来解决问题
MySQL简单技巧(一)：count(*)、count(1)、count(id)等效率功能比较
weixin_42681866的博客
02-08 956
文章目录前文效率不同count的原理说明如何优化总结 前文   最早在用MySQL count的时候，一直不懂count(*)、count(1)、count(id)这些用法的差别，所以每次就用count(*)，当有统计字段需求的时候就用count(字段)，所幸也大多无碍，不过了解和掌握这些区别也是非常重要的！ 效率   先说结论，单从效率上讲： 效率：count(字段)<count(主键 i...
MySQL数据库中常用的 count(字段) ，count(主键 id) ，count(1)和count(*)的区别
不秃头的程鲜森的博客
09-22 423
需要注意的是，下面的讨论还是基于加粗样式 InnoDB 引擎的。 这里，首先你要弄清楚 count() 的语义。count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。 所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数； 而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。 至于分析性能差别的时候，你可以记住这么几个
mysql 计数最好是count（id）以免查漏
qq_50616415的博客
10-14 159
数据库如果有count（）函数的查询那么返回类型resultType最好是int类型不能反回实体类。因为会查询到空值那么
MySQL中count是怎样执行的？———count(1)，count(id)，count(非索引列)，count(二级索引列)的分析
热门推荐
卓越无关环境，保持空杯心态——靡不有初，鲜克有终
10-19 3万+
MySQL中count是怎样执行的？———count(1)，count(id)，count(非索引列)，count(二级索引列)的分析
Mysql - count(字段)＜count(主键 id)＜count(1)≈count(*)
Fupengyao的博客
07-26 297
Mysql - count(字段)＜count(主键 id)＜count(1)≈count(*)
mysql 千万级数据 select count_【MySql】记一次千万级数据处理的优化过程
weixin_39931923的博客
12-05 1051
1.需求从mongodb数据库的A表(或者叫集合)读取数据经过处理后导入MySQL的B表中其中A表有近2000万条的数据需要对每条数据进行分析处理，分析处理过程就不细说了每条A表数据分析处理后可提取生成数条B表数据B表数据的字段中有school、speciality和post三个字段，和一个字段number导入B表中的数据需要通过这三个字段联合去重，并统计重复的条数number最终将生成...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值