百度360必应搜狗淘宝本站头条
当前位置:网站首页 > 技术分析 > 正文

关于mysql的innodb 800w+数据不带条件count性能优化的思考

liebian365 2025-03-11 17:39 6 浏览 0 评论

关于mysql的innodb 800w+数据不带条件count性能优化的思考

1、查看mysql版本

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.32                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 5.7.32                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+

2、建表并初始化数据

2.1、建表语句如下

mysql> desc user;
mysql> show create table user;

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `birthday` date NOT NULL,
  `no` int(11) NOT NULL DEFAULT '0',
  `remark` varchar(255) NOT NULL,
  `modify_date` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `create_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2.2、初始化500w+数据

insert into user(name,birthday,no,remark,modify_date,create_date) values("test-user-name",'2000-01-01',0,"测试count性能",'2019-01-01','2019-01-01');

反复执行以下语句直至数据量超过500w+
insert into user(name,birthday,no,remark,modify_date,create_date) select name,birthday,no,remark,modify_date,create_date from user ;

执行一些更新操作
update user set no=id+1000000;

3、开启profiling参数并查看耗时

3.1、开启profiling参数

mysql> show variables like '%profiling%'; 查看配置情况
mysql> set profiling=1; 开启配置

3.2、查看count语句执行耗时

使用count(*)或count(1)耗时均超过4秒

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  8388608 |
+----------+

mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00172175 | show variables like '%profiling%' |
|        2 | 4.09369675 | select count(*) from user         |
+----------+------------+-----------------------------------+

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 4.7E-5   |
| checking permissions | 4E-6     |
| Opening tables       | 1.4E-5   |
| init                 | 8E-6     |
| System lock          | 5E-6     |
| optimizing           | 3E-6     |
| statistics           | 9E-6     |
| preparing            | 7E-6     |
| executing            | 2E-6     |
| Sending data         | 4.093506 |
| end                  | 8E-6     |
| query end            | 2E-5     |
| closing tables       | 7E-6     |
| freeing items        | 4.8E-5   |
| cleaning up          | 9E-6     |
+----------------------+----------+

在user表没有辅助索引,仅有一个主键索引的情况下,可以看到耗时超过4秒

3.3、创建no字段的辅助索引再进行count统计

create index index_no on user(no);

3.4、查看当前索引情况

mysql> show index from user;

3.5、增加辅助索引后再次执行count可以看到查询速度有明显的提升

可通过show profiles查看最新的queryid,观察实际的耗时


4、通过explain执行计划分析一下有辅助索引和无辅助索引的区别

mysql> explain select count(*) from user;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | index_no | 4       | NULL | 8159175 |      100 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+-------------+
mysql> drop index index_no on user;
mysql> explain select count(*) from user;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 8159175 |      100 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

通过explain执行计划可以看到count统计查询在有辅助索引时,选择了走辅助索引,没有时选择了PRIMARY主键索引,从结果可以看到使用了主键索引反而比较慢,这是为什么呢?通常mysql普通的数据检索时主键索引会比普通索引快,原因是主键索引不需要回表,而count是什么原因会造成有如此大的差异呢?

5、继续通过optimizer trace跟踪优化器过程

5.1、查看optimizer_trace状态

mysql> show variables like '%optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name                | Value                                                                      |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace              | enabled=off,one_line=off                                                   |
| optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit        | 1                                                                          |
| optimizer_trace_max_mem_size | 16384                                                                      |
| optimizer_trace_offset       | -1                                                                         |
+------------------------------+----------------------------------------------------------------------------+

5.2、开启并查看执行情况

  • 开启optimizer_trace
mysql> show variables like '%optimizer_trace%'; 查看参数状态
mysql> set optimizer_trace="enabled=on";
mysql> set end_markers_in_json=on; 
  • 在有索引与无索引情况下执行count查询后均使用以下查询语句获取优化器优化过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;

可以观察到在有辅助索引及无辅助索引index_no的两种情况下,优化器的执行过程一样,并没有差异,没有体现出性能的差异,不过在optimizer_trace结果中可以看到是scan;

            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`user`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 8159175,
                      "access_type": "scan",
                      "resulting_rows": 8.16e6,
                      "cost": 1.67e6,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 8.16e6,
                "cost_for_plan": 1.67e6,
                "chosen": true
              }
            ]

6、既然都是scan那应该就应该是和本身的结构有关了

6.1、聚簇索引(clustered index)和非聚簇索引(secondary index)的区别

innodb的clustered index是把primary key以及row data保存在一起的,而secondary index则是单独存放,然后有个指针指向primary key,所以二级索引树比主键索引树小。因此优化器基于成本的考虑,优先选择的是二级索引。

6.2、验证

  • 创建新表user_no仅保留idno字段
CREATE TABLE `user_no` (
  `id` int(10) unsigned NOT NULL,
  `no` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  • 插入所有数据
insert into user_no select id,no from user

基于上面的结论,如果表的主键索引树与二级索引树差别不大的话应该可以获得相近的结果,默认count(*)时会选择二级索引(辅助索引)index_no,可以通过explain检查

mysql> explain select count(*) from user_no;
mysql> explain select count(*) from user_no force index(primary);
  • 开启profiling参数并执行统计,查看耗时的差异

将分别统计user表(此表无辅助索引)、user_no表的两种情况(默认走辅助索引index_no以及主键索引)

mysql> set profiling=1;
mysql> select count(*) from user_no;
mysql> select count(*) from user_no force index(primary);
mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 |   4.862296 | select count(*) from user                         |
|        2 |  1.8102375 | select count(*) from user_no                      |
|        3 | 1.98250375 | select count(*) from user_no force index(primary) |
+----------+------------+---------------------------------------------------+

可以看到当表user_no只剩下2个字段时,使用主键索引或辅助索引时略有差异但并不大,按照这个结论,尝试在大表(较多字段)中使用了like操作,同样能够得到不错的性能提升select id from user force index(index_name) where name like '%33D0EB9%' ;

不同的机器性能或不同的mysql版本可能表现不一样

相关推荐

看黑客是如何获取你电脑最高权限的,一定要看

在渗透过程中,通过各种方式获取到一枚cmdshell,但是这个shell的权限比较低,无法让我们做我们想要做的一些操作,比如说获取系统密码,获取数据库信息,又或者比如说拿到服务器中的另一个站点的权限,...

是50个常用的Visual Basic代码示例:

以下是50个常用的VisualBasic代码示例:1.声明变量```vb...

电脑系统型号怎么看版本(如何看电脑系统型号)

有时候我们会需要进行查看电脑上安装的windows系统版本及系统版本号,但对于不懂电脑知识的小白来说要怎么查看电脑系统版本信息呢?别着急,有小编在接下来,就将查看电脑系统版本的教程来分享给你们,希望对...

dos命令systeminfo,查看系统启动时间。电脑卡慢,win10怎么了?

最近一段时间,有几个反应电脑卡慢的,都是windows10的系统。询问得知每天电脑有关机,打开任务管理器,内存使用量达到百分之九十多,而程序只打开微信、wps、360浏览器。cmd窗口运行命令syst...

systeminfo命令:全面解析系统信息!

你是否曾想过,仅凭一条简单的命令,就能深入了解计算机的"内心世界"?是不是有点不可思议?那么,让我们一起探寻这个神奇的命令,揭开它背后的奥秘吧!它能提供的信息超乎你的想象,从操作系统到硬件配置,再到驱...

电脑序列号怎么查询?只需两行命令一键查询

当我们的电脑出问题需要保修的时候,需要查询到电脑的型号和序列号才更便于进行下一步的操作,有包装盒的朋友还可以在包装盒上查询,笔记本用户可以在电脑底部标签上查询,没有包装盒和标签破损的用户就无从下手了。...

快速显示系统信息:Systeminfo命令详解

Systeminfo命令是windows系统中显示系统信息的命令,此命令可以显示出计算机的操作系统的详细配置信息,包括操作系统配置、安全信息、产品ID和硬件属性(如RAM、磁盘空间和网卡)。使用...

dos命令systeminfo图文教程,显示操作系统配置信息msinfo32

大家好,我是老盖,首先感谢观看本文,本篇文章做的有视频,视频讲述的比较详细,也可以看我发布的视频。今天我们学习systeminfo命令,该工具显示本地或远程机器(包括服务包级别)的操作系统配置的信息,...

基于uniapp+vue3跨端仿制chatgpt实例uniapp-chatgpt

#夏日生活打卡季#...

原创新作uniapp+vue3+pinia2高仿微信App聊天

前段时间有给大家分享一个flutter3.x桌面端os系统。今天再分享一款最新原创之作uniapp-vue3-wechat聊天实例。uni-vue3-wechat采用...

UniApp开发的设备适配(uniapp服务器配置)

UniApp是一个跨平台开发框架,支持多端应用(如H5、小程序、iOS、Android等)。由于不同设备的屏幕尺寸、分辨率、操作系统等存在差异,设备适配是开发过程中需要重点关注的问题。以下是Uni...

如何用服务器搭建自己的个人网站(自己服务器怎么做网站)

这篇教程主要是告诉大家如何利用TCP和HTTP协议来完成网站的搭建。首先你需要有C/C++语言基础,且有服务器、客户端概念,如果你了解TCP或者HTTP协议的话,那么将会帮助你更快的学会如何搭建个人网...

大话C语言:字符数组(c语言字符数组教学视频)

1字符数组概述C语言中没有字符串这种数据类型,可以通过char的数组来替代。数字0(和字符'\0'等价)结尾的char数组就是一个字符串,字符串是一种特殊的char的数组。...

源码分享:在pdf上加盖电子签章(pdf怎么加电子签章)

在pdf上加盖电子签章,并不是只是加个印章图片,。而是要使用一对密钥中的私钥对文件进行签字。为啥要用私钥呢?很简单,因为公钥是公开的,其他人才可以用公钥为你证明,这个文件是你签的。这就是我们常说的:私...

C语言wcstombs函数详解:宽字符字符串到多字节的「翻译官」

核心定位wcstombs是C语言中用于将宽字符字符串转换为多字节字符串的「翻译官」,它能将宽字符(wchar_t)转换为多字节字符(如UTF-8编码的中文)。就像一位翻译官,它能将一种语言(宽字符...

取消回复欢迎 发表评论: