Home Database Mysql Tutorial 【MySQL优化】MySQL GROUP BY 操作的优化_MySQL

【MySQL优化】MySQL GROUP BY 操作的优化_MySQL

Jun 01, 2016 pm 01:45 PM
optimization

bitsCN.com

MySQL GROUP BY 操作的优化
默认情况下, MySQL 在执行 GROUP BY col1 , col2.... 操作的时候,会按照 GROUP BY 字段的顺序进行排序。如果显式包括一个包含相同的列的 ORDER BY 子句,则对 MySQL 的实际执行性能没有什么额外的影响。

如果查询包括 GROUP BY 操作, 但是不需要对结果进行排序,或者对默认的排序结果不满意,希望获得结果后再由程序进一步处理的时候,可以指定 ORDER BY NULL 禁止排序,从而避免排序结果的消耗。

下面介绍的例子对比了开启 / 关闭 GROUP BY 排序的执行计划:

mysql> desc select dep,pos,avg(sal) from employee group by dep,pos G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employee

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using temporary; Using filesort

1 row in set (0.00 sec)

mysql> desc select dep,pos,avg(sal) from employee group by dep,pos order by null G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employee

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using temporary

1 row in set (0.00 sec)

从执行计划可以看到,使用了 ORDER BY NULL 的 SQL 减少了文件排序的步骤,当返回结果集很大时,对于 GROUP BY 的性能是有很大改善的。

bitsCN.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Discussion on Golang's gc optimization strategy Discussion on Golang's gc optimization strategy Mar 06, 2024 pm 02:39 PM

Discussion on Golang's gc optimization strategy

In-depth interpretation: Why is Laravel as slow as a snail? In-depth interpretation: Why is Laravel as slow as a snail? Mar 07, 2024 am 09:54 AM

In-depth interpretation: Why is Laravel as slow as a snail?

Decoding Laravel performance bottlenecks: Optimization techniques fully revealed! Decoding Laravel performance bottlenecks: Optimization techniques fully revealed! Mar 06, 2024 pm 02:33 PM

Decoding Laravel performance bottlenecks: Optimization techniques fully revealed!

C++ program optimization: time complexity reduction techniques C++ program optimization: time complexity reduction techniques Jun 01, 2024 am 11:19 AM

C++ program optimization: time complexity reduction techniques

How to optimize settings and improve performance after receiving a new Win11 computer? How to optimize settings and improve performance after receiving a new Win11 computer? Mar 03, 2024 pm 09:01 PM

How to optimize settings and improve performance after receiving a new Win11 computer?

How to optimize the startup items of WIN7 system How to optimize the startup items of WIN7 system Mar 26, 2024 pm 06:20 PM

How to optimize the startup items of WIN7 system

Laravel performance bottleneck revealed: optimization solution revealed! Laravel performance bottleneck revealed: optimization solution revealed! Mar 07, 2024 pm 01:30 PM

Laravel performance bottleneck revealed: optimization solution revealed!

Vivox100s parameter configuration revealed: How to optimize processor performance? Vivox100s parameter configuration revealed: How to optimize processor performance? Mar 24, 2024 am 10:27 AM

Vivox100s parameter configuration revealed: How to optimize processor performance?

See all articles