对mysql性能优化的看法(聊聊MySQL的COUNT的性能,看看怎么最快?)
对mysql性能优化的看法
聊聊MySQL的COUNT的性能,看看怎么最快?
前言
基本职场上的程序员用来统计数据库表的行数都会使用count(*),count(1)或者count(主键),那么它们之间的区别和性能你又是否了解呢?
其实程序员在开发的过程中,在一张大表上统计总行数是非常耗时的一个操作,那么我们应该用哪个方法统计会更快呢?
接下来我们就来聊一聊MySQL中统计总行数的方法和性能。
count(*),count(1),count(主键)哪个更快?
1、建表并且插入1000万条数据进行实验测试:
- # 创建测试表
- CREATE TABLE `t6` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `name` varchar(50) NOT NULL,
- `status` tinyint(4) NOT NULL,
- PRIMARY KEY (`id`),
- KEY `idx_status` (`status`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- # 创建存储过程插入1000w数据
- CREATE PROCEDURE insert_1000w()
- BEGIN
- DECLARE i INT;
- SET i=1;
- WHILE i<=10000000 DO
- INSERT INTO t6(name,status) VALUES('god-jiang-666',1);
- SET i=i+1;
- END WHILE;
- END;
- #调用存储过程,插入1000万行数据
- call insert_1000w();
2、分析实验结果
- # 花了0.572秒
- select count(*) from t6;
在这里插入图片描述
- # 花了0.572秒
- select count(1) from t6;
- # 花了0.580秒
- select count(id) from t6;
- # 花了0.620秒
- select count(*) from t6 force index (primary);
从上面的实验我们可以得出,count(*)和count(1)是最快的,其次是count(id),最慢的是count使用了强制主键的情况。
下面我们继续测试一下它们各自的执行计划:
- explain select count(*) from t6;
- show warnings;
- explain select count(1) from t6;
- show warnings;
- explain select count(id) from t6;
- show warnings;
- explain select count(*) from t6 force index (primary);
- show warnings;
从上面的实验可以得出这三点:
- count(*)被MySQL查询优化器改写成了count(0),并选择了idx_status索引
- count(1)和count(id)都选择了idx_statux索引
- 加了force index(primary)之后,走了强制索引
这个idx_status就是相当于是二级辅助索引树,目的就是为了说明:InnoDB在处理count(*)的时候,有辅助索引树的情况下,会优先选择辅助索引树来统计总行数。
为了验证count(*)会优先选择辅助索引树这个结论,我们继续来看看下面的实验:
- # 删除idx_status索引,继续执行count(*)
- alter table t6 drop index idx_status;
- explain select count(*) from t6;
从以上实验可以得出,删除了idx_status这个辅助索引树,count(*)就会选择走主键索引。所以结论:count(*)会优先选择辅助索引,假如没有辅助索引的存在,就会走主键索引。
为什么count(*)会优先选择辅助索引?
在MySQL5.7.18之前,InnoDB通过扫描聚集索引来处理count(*)语句。
从MySQL5.7.18开始,InnoDB通过遍历最小的可用二级索引来处理count(*)语句。如果不存在二级索引,则扫描聚集索引。
新版本为何会使用二级索引来处理count(*)呢?
因为InnoDB二级索引树的叶子节点上存放的是主键,而主键索引树的叶子节点存放的是整行数据,所以二级索引树比主键索引树小。因此查询优化器基于成本考虑,优先选择的是二级索引。所以索引count(*)快于count(主键)。
总结
这篇文章的结论就是count(*)=count(1)>count(id)。
为什么count(id)走了主键索引还会更慢呢?因为count(id)需要取出主键,然后判断不为空,再累加,代价更高。
count(*)是会总计出所有NOT NULL和NULL的字段,而count(id)是不会统计NULL字段的,所以我们在建表的尽量使用NOT NULL并且给它一个默认是空即可。
最后,在以后总计数据库表的总行数的时候,可以大胆的使用count(*)或者count(1)。
参考资料
- 《高性能MySQL》(第三版)第六章优化COUNT()查询
- 《MySQL实战45讲》林晓斌
原文链接:https://mp.weixin.qq.com/s/5sUojFHkq9oBMuuhVGkc-Q
- mysql面试题及答案100题(几个MySQL高频面试题的解答)
- mysql xml转换json(Mysql将查询结果集转换为JSON数据的实例代码)
- mysql查看视图注释(详解MySQL的sql_mode查询与设置)
- mysql拼接多字段作为查询条件(Mysql 实现字段拼接的三个函数)
- mysql的日志怎么清除(MySQL删除了记录不生效的原因排查)
- MySQL执行事务的语法与流程详解(MySQL执行事务的语法与流程详解)
- docker如何搭建mysql(docker容器访问宿主机的MySQL操作)
- mysql中查询数据合并(Mysql合并结果接横向拼接字段的实现步骤)
- 升级mysql客户端版本(超详细教你怎么升级Mysql的版本)
- mysql拆分成多行(mysql单字段多值分割和合并的处理方法)
- mysql innodb存储原理(mysql innodb的重要组件汇总)
- mysqlshell日常运维脚本(监控MySQL主从状态的shell脚本)
- mysql 死锁查询(MySQL slave 延迟一列 外键检查和自增加锁)
- idea关联mysql数据库(IDEA无法连接mysql数据库的6种解决方法大全)
- 设置mysql字符集语句(MySQL修改字符集的实战教程)
- 将SQL Server数据迁移到MySQL的方法
- 七夕的寓意(七夕的寓意)
- 苏志燮赵恩静结婚,韩国四大公共财产变三人,这么快就有替补了(苏志燮赵恩静结婚)
- 《内在美》后,一大波新韩剧来袭,李钟硕朴信惠宋慧乔玄彬回归(一大波新韩剧来袭)
- 给孩子选购保温杯,注意这4个步骤,比颜值更重要(给孩子选购保温杯)
- 保温好 容量大 颜值高 保温杯你给娃娃买对了吗(保温好容量大颜值高)
- 《道德经》 人生避开骄狂,才能免去祸患(道德经人生避开骄狂)
热门推荐
- mysql的存储方法(MySQL中的binary类型使用操作)
- python图片识别文字代码(python批量识别图片指定区域文字内容)
- dedecms修改教程(还原dedecms数据后系统基本参数空白的解决方法)
- iview 全局初始化组件(iview 权限管理的实现)
- ftp主动模式和被动模式设置(FTP主动模式和被动模式区别详解)
- centos上docker的部署(CentOS8下的Docker使用详解)
- 推荐一款好用的项目管理系统:禅道项目管理系统
- vue组件方法里如何修改data(vue项目中使用rem替换px的实现示例)
- python字符串之基本操作小练习(Python数据类型之String字符串实例详解)
- 使用border-collapse设置table边框