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

文章目录

  • 0、group by 优化
  • 1、删除多余的索引
  • 2、查询 group by profession
  • 3、执行计划 group by profession
  • 4、创建联合索引 idx_user_pro_age_sta
  • 5、再次执行计划 group by profession
  • 6、执行计划 group by age
  • 7、执行计划 group by profession,age
  • 8、执行计划 where profession='软件工程' group by age

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 仅使用了索引中的信息来满足查询，而无需回表（即无需再读取表中的数据）。这是使用索引的理想情况，因为它避免了读取整个数据行，从而提高了查询效率。

在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/757681.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

2-requests模块（6节课学会爬虫）

2-requests模块（6节课学会爬虫）

2-requests模块（6节课学会爬虫） 1，安装requests2，发送get，post请求，获取响应3，response的方法方法一（Response.text）方法二（response.content.decode()&#…
阅读更多...
基于springboot实现学生用品采购系统项目【项目源码+论文说明】

基于springboot实现学生用品采购系统项目【项目源码+论文说明】

基于springboot实现学生用品采购系统演示 摘要 传统办法管理信息首先需要花费的时间比较多，其次数据出错率比较高，而且对错误的数据进行更改也比较困难，最后，检索数据费事费力。因此，在计算机上安装学生用品采购系统软…
阅读更多...
《昇思25天学习打卡营第6天|onereal》

《昇思25天学习打卡营第6天|onereal》

Vision Transformer（ViT）简介 近些年，随着基于自注意（Self-Attention）结构的模型的发展，特别是Transformer模型的提出，极大地促进了自然语言处理模型的发展。由于Transformers的计算效率和可扩…
阅读更多...
YOLOv10（7）：YOLOv10训练（以训练VOC数据集为例）

YOLOv10（7）：YOLOv10训练（以训练VOC数据集为例）

YOLOv10（1）：初探，训练自己的数据_yolov10 训练-CSDN博客 YOLOv10（2）：网络结构及其检测模型代码部分阅读_yolov10网络结构图-CSDN博客 YOLOv10（4）：损失&…
阅读更多...
MapReduce学习

MapReduce学习

目录 7.3 MapReduce工作流程 7.3.1 工作流程概述 7.3.2 MapReduce各个执行阶段 7.3.3 Shuffle过程详解 1. Shuffle过程简介（过程分为Map端的操作和Reduce端的操作） 2、Map端的Shuffle过程： 3、在Reduce端的Shuffle过程 7.4 实例分析&am…
阅读更多...
mysql是什么

mysql是什么

mysql是什么 是DBMS软件系统，并不是一个数据库，管理数据库 DBMS相当于用户和数据库之间的桥梁，有超过300种不同的dbms系统 mysql是关系型数据库，关系型数据库存储模型很想excel，用行和列组织数据 sql是一门编程语言…
阅读更多...
LUA 语言中subtree 的使用教程

LUA 语言中subtree 的使用教程

在线编辑器： https://www.runoob.com/try/runcode.php?filenameHelloWorld&typelua 在Lua语言中，"subtree"通常指的是一个子表或者子树，它指的是一个Lua表（table）中的一个部分，可以是一个单…
阅读更多...
自定义注解+AOP形式监控接口调用日志

自定义注解+AOP形式监控接口调用日志

目的&#xff1a; 通过自定义注解&#xff0c;在需要监控接口调用输出日志的类或方法上&#xff0c;加上自定义注解&#xff0c;实现无侵入式接口监控。 实现&#xff1a; idea结构 1、导入pom <dependency><groupId>org.aspectj</groupId><artifactI…
阅读更多...
你的 Python 代码太慢了吗？协程和多线程来拯救！

你的 Python 代码太慢了吗？协程和多线程来拯救！

目录 协程&#xff08;Coroutine&#xff09; 多线程&#xff08;Multithreading&#xff09; 协程与多线程的原理 协程的原理 多线程的原理 实践案例 使用协程案例 使用多线程案例 大家好&#xff0c;在Python编程中&#xff0c;处理并发任务时&#xff0c;我们经常会…
阅读更多...
LLM大模型：如何选择合适的 Embedding 模型？

LLM大模型：如何选择合适的 Embedding 模型？

检索增强生成&#xff08;RAG&#xff09;是生成式 AI &#xff08;GenAI&#xff09;中的一类应用&#xff0c;支持使用自己的数据来增强 LLM 模型&#xff08;如 ChatGPT&#xff09;的知识。 RAG 通常会用到三种不的AI模型&#xff0c;即 Embedding 模型、Rerankear模型以及…
阅读更多...
数据质量管理-时效性管理

数据质量管理-时效性管理

前情提要 根据GB/T 36344-2018《信息技术 数据质量评价指标》的标准文档&#xff0c;当前数据质量评价指标框架中包含6评价指标&#xff0c;在实际的数据治理过程中&#xff0c;存在一个关联性指标。7个指标中存在4个定性指标&#xff0c;3个定量指标&#xff1b; 定性指标&am…
阅读更多...
视频号视频怎么保存到手机，视频号视频怎么保存到手机相册里，苹果手机电脑都可以用

视频号视频怎么保存到手机，视频号视频怎么保存到手机相册里，苹果手机电脑都可以用

随着数字媒体的蓬勃发展&#xff0c;视频已成为我们日常生活中不可或缺的一部分。视频号作为众多视频分享平台中的一员&#xff0c;吸引了大量用户上传和分享各类精彩视频。然而&#xff0c;有时我们可能希望将视频号上的视频下载下来,以下将详细介绍如何将视频号的视频。 方法…
阅读更多...
[DASP]玩机！在组织一套音频系统之前，我们先要知道这套系统里面有什么东西。

[DASP]玩机！在组织一套音频系统之前，我们先要知道这套系统里面有什么东西。

前言 现在不是搞音频嘛&#xff0c;正好自己买了无源音箱&#xff0c;买了套DSP芯片玩一下 流程 上图是我们组织一套音频系统的流程&#xff0c;首先我们需要知道各个元件是做什么的 1. 音源&#xff08;例如麦克风、音乐播放器等&#xff09;&#xff1a; 产生模拟音频信号…
阅读更多...
无忧易售新功能：一键白底转换，升级产品图片质感

无忧易售新功能：一键白底转换，升级产品图片质感

在电商领域不断追求卓越与效率的今天&#xff0c;无忧易售ERP推出一键白底转换功能&#xff0c;为卖家们提供前所未有的便捷与高效&#xff0c;改变了商品图片处理的传统模式&#xff0c;革新了卖家们的图片处理体验&#xff0c;让商品展示焕然一新&#xff0c;助力商家在激烈的…
阅读更多...
Java--常用类APl(复习总结)

Java--常用类APl(复习总结)

前言: Java是一种强大而灵活的编程语言&#xff0c;具有广泛的应用范围&#xff0c;从桌面应用程序到企业级应用程序都能够使用Java进行开发。在Java的编程过程中&#xff0c;使用标准类库是非常重要的&#xff0c;因为标准类库提供了丰富的类和API&#xff0c;可以简化开发过…
阅读更多...
Softmax函数的作用

Softmax函数的作用

Softmax 函数主要用于多类别分类问题&#xff0c;它将输入的数值转换为概率分布。 具体来说&#xff0c;对于给定的输入向量 x [x_1, x_2,..., x_n] &#xff0c;Softmax 函数的输出为 y [y_1, y_2,..., y_n] &#xff0c;其中&#xff1a; 这样&#xff0c;Softmax 函数的输…
阅读更多...
python selenium 打开网页

python selenium 打开网页

selenium工具类 - 文件名 seleniumkit.py 代码如下 # -*- coding:utf-8 _*-from selenium import webdriverimport os import timefrom selenium.webdriver.support.ui import WebDriverWait from selenium.webdriver.support import expected_conditions as EC from seleniu…
阅读更多...
【Linux】解锁并发：多线程同步技术详解与应用实践

【Linux】解锁并发：多线程同步技术详解与应用实践

文章目录 前言&#xff1a;1. 同步概念2. 条件变量&#xff1a;实现线程间同步的&#xff01;2.1. 条件变量是什么&#xff1f;2.2. 认识条件变量接口 3. 写一个测试代码——验证线程的同步机制4. 生产消费模型5. 生产消费模型 条件变量6. 线程池7. 可重入 VS 线程安全7.1. 概…
阅读更多...
ModuleNotFoundError: No module named ‘_sysconfigdata_x86_64_conda_linux_gnu‘

ModuleNotFoundError: No module named ‘_sysconfigdata_x86_64_conda_linux_gnu‘

ModuleNotFoundError: No module named _sysconfigdata_x86_64_conda_linux_gnu 1.软件环境⚙️2.问题描述&#x1f50d;3.解决方法&#x1f421;4.结果预览&#x1f914; 1.软件环境⚙️ Ubuntu 20.04 Python 3.7.0 2.问题描述&#x1f50d; 今天发现更新conda之后&#xff0…
阅读更多...
【Python机器学习】分类向量——One-Hot编码（虚拟变量）

【Python机器学习】分类向量——One-Hot编码（虚拟变量）

为了学习分类特征&#xff0c;以某国成年人收入数据集&#xff08;adult&#xff09;为例&#xff0c;adult数据集的任务是预测一名工人的收入是高于50k还是低于50k&#xff0c;这个数据集的特征包括工人的年龄、雇佣方式、教育水平、性别、每周工作时长、职业等。 这个任务属于…
阅读更多...
最新文章

Copyright @ 2022~2023