MySQL SQL 优化:优化 select count(*)

小七学习网,助您升职加薪,遇问题可联系:客服微信【1601371900】 备注:来自网站

提高 MySQL 能力,优化 MySQL 性能。 我们经常会遇到业务反馈系统按钮点了没有反应,等很久没有结果,开发人员来找你排查问题。有的时候可能是代码中的 SQL 写的不够优秀,所以系统调用后查询很…

提高 MySQL 能力,优化 MySQL 性能。

我们经常会遇到业务反馈系统按钮点了没有反应,等很久没有结果,开发人员来找你排查问题。有的时候可能是代码中的 SQL 写的不够优秀,所以系统调用后查询很慢,优化SQL即可处理;有的时候确是非常简单的 SQL,根本无法优化了,这时怎么办?其实简单的 SQL 我们要用“不简单”的方法优化。本次我们聊聊 MySQL SQL 优化,一个再普通不过的 select count(*) 是如何被优化的。

再优化一点点,这次我们聊聊数据库 SQL 优化:

  • 业务反馈,开发崩溃
  • 一个效果显著的简单操作
  • 问题复现,优化处理
  • 扒一扒 count(*) 的原理
  • 显而易见的验证


业务反馈,开发崩溃

某天,项目中业务人员反馈系统中某功能查询非常的慢,几乎等待了几十秒才有反应。于是联系了开发,开发人员调取日志,排查应用,定位 SQL,其实只是一个 select count(*),这可急坏了开发人员,非常困惑,最简答的一个统计 SQL,为什么查询这么慢!不知道到如何优化与处理。

一个效果显著的简单操作

在我知道这个问题,询问了开发人员哪张表后,做了一定分析后,增加索引,无需开发做任何修改,性能得到大幅提升。为什么一个索引会有如此的性能提升,我先卖个关子,后面一一道来。

问题复现,优化处理

首先我们造一个 500W 数据量的表,别结构如下:

mysql> show create table sbtest1\\G;*************************** 1. row ***************************       Table: sbtest1Create Table: CREATE TABLE `sbtest1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `k` int(11) NOT NULL DEFAULT \'0\',  `c` char(120) NOT NULL DEFAULT \'\',  `pad` char(60) NOT NULL DEFAULT \'\',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=latin1

直接来 count(*) 一下,看看执行时间吧。

mysql> select count(*) from sbtest1;+----------+| count(*) |+----------+|  5000000 |+----------+1 row in set (2.42 sec)

执行时间 2.42 秒。

现在看看执行计划:

mysql> explain select count(*) from sbtest1;+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | PRIMARY | 4       | NULL | 4808163 |   100.00 | Using index |+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)

结果显示:走 PRIMARY 索引。

是不是有疑惑了,查询走了主键索引,看着没什么问题呀,500W 的数据 2 秒执行完,还可以。那你就错了,我这里只是一个简单的测试表,这才几个字段,测试表造的数据又是多么的简单,倘若在真实生产环境,这 500W 的数据可不是 2 秒就能返回结果的。

稍等片刻,我来优化……
……
……
……

mysql> select count(*) from sbtest1;+----------+| count(*) |+----------+|  5000000 |+----------+1 row in set (0.68 sec)

执行时间明显缩短,我到底做了怎样的操作,为什么执行效率大幅提高,想知道其中的奥妙吗?干货即将送达。

扒一扒 count(*) 的原理

为了解开这其中的疑惑,首先我们不得不说一下 MySQL 的 count(*) 原理。

在 MySQL 中存在两种索引:

  • 聚簇索引:MySQL 中的每个 InnoDB 存储引擎的表,都有一个特殊索引来保存每行记录,这个索引就是聚簇索引。通常情况下其实就是主键。聚簇索引保存的是行记录和 b-tree 索引,这个索引所占用的空间大小和行记录总数差不多大。
  • 二级索引:另外一类索引就是二级索引,它保存的只是本身索引列和主键列。占用的空间明显小很多了。

介绍完 MySQL 的两种索引,我们继续说 MySQL 的 count(*) 执行过程。

在 MySQL 的 InnoDB 存储引擎中,count(*) 会从内存中读取数据到缓冲区中,如果内存中没有,会提前一步在磁盘中把数据读取到内存中,然后在缓存区中完成记录数的统计。MySQL 会先通过 ken_len 最小的那个二级索引计算,如果没有二级索引就通过主键计算,如果连主键都没有那就要通过全表扫描来完成计算了。

主键索引也就是聚簇索引,会把行记录和 b-tree 索引都读取出来,所占用的空间大小和行记录总数差不多大;二级索引保存的只是本身索引列和主键列,占用的空间会小的多,当然读取出来就节省了很多时间。

我举个通俗易懂的例子吧,比如学校给你一个任务,让你统计一下高三年级一共有多少学生?利用上面说的两种索引来统计。

  • 聚簇索引:从学号 1,一个一个数到 600,有 600 人。
  • 二级索引:直接看学号 600,那不就是 600 人吗!

通过这些就不难说明了,上述演示的过程,第一次是通过主键来计算统计数据量,而第二次其实我做的也是创建了一个二级索引,通过二级索引来计算统计,速度快了很多,我们可以看看其对应的执行过程。

mysql> explain select count(*) from sbtest1;+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | k_1  | 4       | NULL | 4808163 |   100.00 | Using index |+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.01 sec)

执行过程 key 这一列内容为:k_1,这个就是二级索引的索引名称。

显而易见的验证

验证聚簇索引

查询 MySQL 缓存区,确保缓冲区中不存在测试表的缓存:

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = \'sbtest\';Empty set (0.05 sec)

执行 select count(*)

mysql> select count(*) from sbtest1;+----------+| count(*) |+----------+|  5000000 |+----------+1 row in set (2.42 sec)

再次查看 MySQL 缓存区,查询测试表的缓存:

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = \'sbtest\';+---------------+-------------+------------+------------+-------+--------------+-----------+-------------+| object_schema | object_name | allocated  | data       | pages | pages_hashed | pages_old | rows_cached |+---------------+-------------+------------+------------+-------+--------------+-----------+-------------+| sbtest        | sbtest1     | 125.84 MiB | 115.46 MiB |  8054 |            0 |      3021 |      588845 |+---------------+-------------+------------+------------+-------+--------------+-----------+-------------+1 row in set (0.13 sec)

结果显示:缓存了 100M+ 的数据。

查看执行计划:

mysql> explain select count(*) from sbtest1;+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | PRIMARY | 4       | NULL | 4808163 |   100.00 | Using index |+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)

结果显示 select count(*) 走的是主键索引。

验证二级索引

创建一个二级索引:

mysql> create index k_1 on sbtest1(k);Query OK, 0 rows affected (53.61 sec)Records: 0  Duplicates: 0  Warnings: 0

为了验证本次过程,我们重启一下 MySQL,目的是清空 MySQL 的缓存。

......  MySQL 重启完成  ......  

查询 MySQL 缓存区,确保缓冲区中不存在测试表的缓存:

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = \'sbtest\';Empty set (0.05 sec)

执行 select count(*)

mysql> select count(*) from sbtest1;+----------+| count(*) |+----------+|  5000000 |+----------+1 row in set (0.73 sec)

再次查看 MySQL 缓存区,查询测试表的缓存:

mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = \'sbtest\';+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+| object_schema | object_name | allocated | data      | pages | pages_hashed | pages_old | rows_cached |+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+| sbtest        | sbtest1     | 65.09 MiB | 62.06 MiB |  4166 |            0 |      1459 |     2501935 |+---------------+-------------+-----------+-----------+-------+--------------+-----------+-------------+1 row in set (0.08 sec)

结果显示:缓存了 60M+ 的数据。

查看执行计划:

mysql> explain select count(*) from sbtest1;+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | sbtest1 | NULL       | index | NULL          | k_1  | 4       | NULL | 4808163 |   100.00 | Using index |+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.01 sec)

结果显示 select count(*) 走的是二级索引 k_1。

更加直观看一下两种索引占用的空间大小

首先我们查看一下 MySQL 的 innodb_page_size 的大小:

mysql> show variables like \'Innodb_page_size\';+------------------+-------+| Variable_name    | Value |+------------------+-------+| innodb_page_size | 16384 |+------------------+-------+1 row in set (0.01 sec)

大小是 16K。

统计一下聚簇索引和二级索引占用的空间大小:

mysql> select     ->   sum(stat_value) pages,    ->   index_name index_name,    ->   (round((sum(stat_value) * @@innodb_page_size)/1024/1024)) as MB    ->   from mysql.innodb_index_stats     ->   where table_name = \'sbtest1\' AND database_name = \'sbtest\' AND stat_description = \'Number of pages in the index\'     -> group by index_name;+-------+------------+------+| pages | index_name | MB   |+-------+------------+------+| 67456 | PRIMARY    | 1054 ||  4774 | k_1        |   75 |+-------+------------+------+2 rows in set (0.01 sec)

这样结果太明显了,主键索引占用了几乎 1 个 G 的空间,而 k_1 这个二级索引值占用了 75M 的空间。

总结与思考

MySQL 的 select count(*) 在底层实现统计的过程中通过二级索引优于主键索引优于全表扫描,这是因为二级索引只缓存主键列和索引列,主键索引几乎缓存了所有的行记录,前者势必比后者缓存的内容少的多,当然计算的效率肯定要快的多。

我们再思考一下,假如数据量不是 500W,而二级索引占用的空间都 1G、10G,甚至几十 G 了,速度也就不可接受了,怎么办?

这个时候我们不能局限于二级索引了,而可考虑:

  1. 单纯的统计,我们可以考虑用 MyISAM 引擎,它自带计数器,当然了,局限性就不一一列举了,此方案了解即可吧。
  2. 数据仓库等其他可接入的系统来完成此工作。
  3. 缓存中间件也不失一个好的建议。
  4. 做一个类似触发器计数的功能?
  5. MySQL 8.0 的并行查询,嗯,好功能。
  6. 历史数据迁移,就不让你查询那么多数据了,这个有点霸道了,可以换着说法,根据业务需求,历史数据迁移,只保留某些数据(按规则)。
  7. 分库分表,不多说什么,还是物理上的优化。
  8. 服务器硬件资源提升,比如 SSD 硬盘等(治标不治本)。
  9. 其他(肯定不止以上 8 种,如果你还有其他想法,请尊留言)。

好了,至此我们基本学习完 select count(*)相关内容了,内容比较多,当然也有不足之处,欢迎朋友们指正补充。

小七学习网,助您升职加薪,遇问题可联系:客服微信【1601371900】 备注:来自网站

免责声明: 1、本站信息来自网络,版权争议与本站无关 2、本站所有主题由该帖子作者发表,该帖子作者与本站享有帖子相关版权 3、其他单位或个人使用、转载或引用本文时必须同时征得该帖子作者和本站的同意 4、本帖部分内容转载自其它媒体,但并不代表本站赞同其观点和对其真实性负责 5、用户所发布的一切软件的解密分析文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。 6、您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。 7、请支持正版软件、得到更好的正版服务。 8、如有侵权请立即告知本站(邮箱:1099252741@qq.com,备用微信:1099252741),本站将及时予与删除 9、本站所发布的一切破解补丁、注册机和注册信息及软件的解密分析文章和视频仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。如有侵权请邮件与我们联系处理。