MySQL高级-SQL优化- group by 优化（通过索引来提高效率）

已于 2024-06-28 18:06:07 修改
阅读量453 收藏 5
点赞数 4
分类专栏： MySQL高级篇 文章标签： mysql sql android 通过索引来提高效率
于 2024-06-28 17:39:46 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_65152767/article/details/140047867
版权

文章目录

0、group by 优化

  • 分组操作时，可以通过索引来提高效率。
  • 分组操作时，索引的使用也是满足最左前缀法则的。
  • group by 即使没有过滤条件用到索引，也可以直接使用索引。
  • group by 先排序再分组，遵照索引建的最佳左前缀法则
  • 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。
  • where效率高于having，能写在where限定的条件就不要写在having中了
  • 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
  • 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

1、删除多余的索引

mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_email_5          |            1 | email       | A         |          23 |        5 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_age_phone   |            1 | age         | A         |          19 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_age_phone   |            2 | phone       | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_age_pho_ad  |            1 | age         | A         |          19 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_age_pho_ad  |            2 | phone       | D         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
9 rows in set (0.00 sec)

mysql> drop index idx_user_pro_age_sta on tb_user;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_user_age_phone on tb_user;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_user_age_pho_ad on tb_user;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_email_5 on tb_user;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from tb_user;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY  |            1 | id          | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

mysql>

2、查询 group by profession

mysql> select profession,count(*) from tb_user group by profession;
+----------------------+----------+
| profession           | count(*) |
+----------------------+----------+
| 软件工程             |        4 |
| 通讯工程             |        1 |
| 英语                 |        1 |
| 工程造价             |        3 |
| 舞蹈                 |        1 |
| 应用数学             |        2 |
| 化工                 |        2 |
| 金属材料             |        1 |
| 机械工程及其自动
化 |        1 |
| 无机非金属材料工
程 |        1 |
| 会计                 |        1 |
| 工业经济             |        1 |
| 国际贸易             |        1 |
| 城市规划             |        2 |
| 土木工程             |        1 |
| 城市园林             |        1 |
+----------------------+----------+
16 rows in set (0.00 sec)

mysql>

3、执行计划 group by profession

mysql> explain select profession,count(*) from tb_user group by profession;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   24 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql>

MySQL 没有使用任何索引，而是执行了全表扫描（type: ALL），并且使用了临时表（Extra: Using temporary）来完成分组操作。
Extra字段显示了"Using temporary"，表示MySQL需要创建一个临时表来存储中间结果。

4、创建联合索引 idx_user_pro_age_sta

mysql> create index idx_user_pro_age_sta on tb_user(profession,age,status);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

mysql>

5、再次执行计划 group by profession

mysql> explain select profession,count(*) from tb_user group by profession; 
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

6、执行计划 group by age

mysql> explain select age,count(*) from tb_user group by age;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | NULL |   24 |   100.00 | Using index; Using temporary |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

Extra: 额外的信息。Using index 表示 MySQL 仅使用了索引中的信息来满足查询，但没有实际读取表中的数据。但是，Using temporary 表示 MySQL 需要使用一个临时表来存储中间结果，这通常会增加查询的开销

7、执行计划 group by profession,age

mysql> explain select profession,age,count(*) from tb_user group by profession,age;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | NULL |   24 |   100.00 | Using index |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

8、执行计划 where profession=‘软件工程’ group by age

mysql> explain select age,count(*) from tb_user where profession='软件工程' group by age;
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 47      | const |    4 |   100.00 | Using index |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

Extra: 这次只显示了 Using index，表示 MySQL 仅使用了索引中的信息来满足查询，而无需回表（即无需再读取表中的数据）。这是使用索引的理想情况，因为它避免了读取整个数据行，从而提高了查询效率。

在这里插入图片描述

小丁学Java
关注
专栏目录
博客
史上最快在IDEA中创建类，只需要 ctrl + 鼠标左键双击 就可以调出创建类的窗口（全网首创）
08-02 1189
需要首先鼠标右键一次，点击新建，再点击 Java类，过于麻烦。我们只需要自定义设置创建类的快捷键即可。
博客
在docker中安装 zendesk/maxwell 失败，解决方法
09-13 545
在docker中安装 zendesk/maxwell 失败，解决方法。
博客
Java100万字面试题资料
09-13 147
密码是：vner。
博客
mysql-搭建主从复制
09-13 1179
mysql集群：单台设备的负载压力：主从复制集群：分摊访问压力和存储压力需求：使用3306mysql当作主，3316mysql 当作从，在3306中对数据库所有的操作，希望能够主从复制同步到3316，其他的数据库操作不同步。
博客
写的一致性问题之双删模式
09-13 184
异步双删的改进版，在子线程或者消息的消费中延时一段时间再进行删除。在事务提交之后异步删除redis，性能得到一定的优化。实现思路：多线程、kafka。开启定时任务进行删除。
博客
写的一致性问题之失效模式
09-12 414
写的一致性问题之失效模式。
博客
Java中List集合去重
09-11 382
反问问题：为什么不直接使用 Set 或者 LinkedHashSet 呢实际场景：实际的业务开发中遇到的情况会更复杂。比如，List 集合可能是历史遗留问题，也有可能是调用接口返回的类型限制，只能使用 List 接收，又或者是代码写了一半，在做多个集合合并的时候才发现了这个问题。
博客
写的一致性问题之双写模式
09-04 438
写的一致性问题之双写模式
博客
redis缓存的目的、场景、实现、一致性问题
09-04 578
专辑详情和声音详情属于并发量较高的数据，如果每次访问都实时到数据库获取数据，数据库的访问压力太大。而这些信息一般更新的频率比较低，短时间内不会发生改变。因此，我们可以考虑在前台系统中，增加一层缓存，把这些数据缓存起来，请求到来时，不再调用数据接口，而是直接读取缓存中的数据。
博客
搭建 xxl-job 执行器项目
09-04 538
copy案例工程的配置文件：xxl-job\xxl-job-executor-samples\xxl-job-executor-sample-springboot\src\main\java\com\xxl\job\executor\core\config。复制到 scheduled-demo 这个模块中。到我们的工程中去，不需要作任何修改。
博客
搭建 xxl-job 调度中心
09-03 562
启动调度中心：java -jar xxl-job-admin-xxx.jar 或者 java -jar -Dserver.port=8088 xxl-job-admin-xxx.jar。调度中心配置文件：/xxl-job/xxl-job-admin/src/main/resources/application.properties。在浏览器访问：http://localhost:10010/xxl-job-admin/切换到xxl-job\xxl-job-admin项目目录下。
博客
xxl-job介绍
09-03 447
分布式任务调度框架几乎是每个大型应用必备的工具。XXL-JOB是一个分布式任务调度平台，其核心设计目标是开发迅速、学习简单、轻量级、易扩展。现已开放源代码并接入多家公司线上产品线，开箱即用。目前已有多家公司接入xxl-job，包括比较知名的大众点评，京东，优信二手车，北京尚德，360金融 (360)，联想集团 (联想)，易信 (网易)等等…
博客
直播相关概念
09-03 322
腾讯云地址：https://cloud.tencent.com/login
博客
好用的国内中文大模型
08-29 535
好用的国内中文大模型
博客
使用kafka完成数据的实时同步，同步到es中。(使用kafka实现自动上下架 upper、lower)
08-28 1325
公开的同步公开的同步私有的删除。
博客
添加专辑失败，获取 userId 为空
08-27 470
我们是在切面类 GuiGuAspect 里面获取的 userId ，所以我们需要在指定的方法上面加上 切面注解@GuiGuLogin。点击 getUserId() 进入方法内部。这样问题就可以得到解决。
博客
SpringData-ElasticSearch入门
08-27 757
SpringData-ElasticSearch入门。
博客
es映射配置（_mapping）
08-26 615
已经存在，并且已经有了不同的映射配置，那么直接发送这个 POST 请求可能会导致错误，因为 Elasticsearch 不允许更改已存在字段的类型或某些其他属性。在这种情况下，您可能需要先删除索引（如果数据允许的话），或者创建一个新的索引。的索引（如果该索引尚不存在，则创建它）设置映射（mapping）的。请根据您的实际 Elasticsearch 配置（如主机名、端口和可能的认证要求）调整此命令。您提供的 POST 请求是用于在 Elasticsearch 中为名为。
博客
es相关概念、索引操作（相当于mysql中的数据库操作）
08-26 1243
【代码】es相关概念、索引操作（相当于mysql中的数据库操作）
博客
es、kibana及分词器的安装
08-26 1209
又称反向索引，分为两部分：文档列表和倒排索引区。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值