數(shù)據(jù)庫核心操作解析：order by、group by、join、union排序分組連接合并原理詳析

一、Order 排序?qū)崿F(xiàn)方式

1. 常規(guī)排序

在數(shù)據(jù)庫操作里，當使用ORDER BY對查詢結(jié)果進行排序時，常規(guī)排序是一種常見的處理方式。下面以


select?col1,col2,col3?from?t1?where?id =?'xx'?order?by?col2;?

為例，詳細介紹其排序流程：

從上述流程可以看出，是否使用文件排序主要取決于緩沖區(qū)?sort_buffer能否容納需要排序的（id,col2）對，而sort_buffer的大小由sort_buffer_size參數(shù)控制。并且，一次常規(guī)排序需要進行兩次 I/O 操作：第一次是撈取（id,col2），第二次是回表撈取（col1,col2,col3）。由于返回的結(jié)果集是按照col2排序的，所以id是亂序的，通過亂序的id去撈?。?code>col1,col2,col3）時會產(chǎn)生大量的隨機 I/O，這會在一定程度上影響排序的性能。

所以，數(shù)據(jù)庫對于第二次 I/O 操作有一個優(yōu)化方法：在撈取數(shù)據(jù)之前，先將?id?進行排序，并把排序后的?id?放入緩沖區(qū)?read_rnd_buffer，然后按照有序的?id?去撈取記錄，這樣就可以將隨機 I/O 轉(zhuǎn)換為順序 I/O。

read_rnd_buffer?主要用于在使用行指針排序之后，將隨機讀轉(zhuǎn)換為順序讀。

2. sort_buffer 與排序優(yōu)化

數(shù)據(jù)庫在進行排序時，使用內(nèi)存緩沖區(qū)sort_buffer?，數(shù)據(jù)庫會把需要排序的數(shù)據(jù)先存放到這里，然后進行排序操作。如果排序操作能夠僅在?sort_buffer?中完成，就可以避免使用臨時表，從而提高排序的效率。

然而，如果需要排序的數(shù)據(jù)量過大，超出了?sort_buffer?的容量，數(shù)據(jù)庫就會借助磁盤文件來進行歸并排序。為了避免走磁盤排序，可以調(diào)大sort_buffer_size?參數(shù)來增大?sort_buffer?的容量，讓它能夠容納更多的數(shù)據(jù)，避免使用磁盤臨時文件。

排序優(yōu)化方式

常規(guī)排序除了排序本身的開銷外，還需要額外的兩次 I/O 操作。為了減少這些開銷，前面說過數(shù)據(jù)庫提供了一種優(yōu)化的排序方式。這種優(yōu)化方式與常規(guī)排序的主要區(qū)別在于，放入sort_buffer的不是（id,?col2），而是（col1,?col2,?col3），也就是?SELECT?語句需要返回的全量字段。這樣做的好處是可以避免一次回表操作，從而減少 I/O 開銷。

不過，這種優(yōu)化方式有一個前提條件：MySQL 提供了參數(shù)?max_length_for_sort_data，只有當SELECT?語句需要返回的字段的長度總和小于?max_length_for_sort_data?時，才能使用這種優(yōu)化排序方式；否則，就只能采用常規(guī)排序方式。

下面用一個表格來總結(jié)?sort_buffer?相關(guān)的參數(shù)和優(yōu)化要點：

3. 其他排序相關(guān)要點

避免排序操作

當我們使用?ORDER BY?進行排序時，數(shù)據(jù)庫通常會使用 文件排序（Using filesort）機制。但在某些場景下，我們可以顯式指定?ORDER BY NULL?來告訴數(shù)據(jù)庫不需要進行排序，從而消除排序操作帶來的額外開銷。

索引與排序

若?ORDER BY、GROUP BY?或?DISTINCT?是按照索引進行歸類，并且?SELECT?字段不需要回表（即可以直接從索引中獲取所需數(shù)據(jù)），那么數(shù)據(jù)庫就可以直接通過索引掃描來完成排序操作，這種方式的效率非常高。

例如，有一個表?employees，包含字段?id、name、department，并且在?department?字段上建立了索引。當我們執(zhí)行?


SELECT?department,?COUNT(*)?FROM?employees?GROUP?BY?department?ORDER?BY?department;?

時，department?索引能滿足查詢需求，數(shù)據(jù)庫就可以直接利用索引進行排序和分組操作，而無需額外的排序過程。

臨時表與排序

臨時表在數(shù)據(jù)庫操作中也有重要作用，UNION?和?GROUP BY?操作是臨時表的典型使用場景。在使用?GROUP BY?進行分組統(tǒng)計時，數(shù)據(jù)庫可能會創(chuàng)建臨時表來存儲中間結(jié)果。

為了消除臨時表帶來的額外開銷，我們可以對?GROUP BY?列添加索引。這樣數(shù)據(jù)庫在進行分組操作時，就可以利用索引來快速定位和分組數(shù)據(jù)，減少臨時表的使用。對于大結(jié)果集，可以調(diào)大?SQL_BIG_RESULT?來優(yōu)化臨時表的使用。

內(nèi)存臨時表有一個默認的大小限制，通常是 16M。如果臨時表的數(shù)據(jù)量超過了?tmp_table_size?參數(shù)設(shè)置的值，內(nèi)存臨時表就會轉(zhuǎn)成磁盤臨時表。磁盤臨時表的讀寫速度相對較慢，可能會影響查詢性能，所以在處理大數(shù)據(jù)量時，可以調(diào)整?tmp_table_size?參數(shù)。

4. 優(yōu)先隊列排序

在處理?Order by limit M, N?這類語句時，可以使用優(yōu)先隊列排序，它采用堆排序算法實現(xiàn)。堆排序的特性正好適合解決?limit M, N?排序問題，雖然仍然需要所有元素參與排序，但只需要N個元組的?sort_buffer?空間，基本不會出現(xiàn)因為?sort_buffer?空間不足而需要使用臨時文件進行歸并排序的情況。

在升序排序時，使用大頂堆，最終堆中的元素組成了最小的?N?個元素；在降序排序時，使用小頂堆，最終堆中的元素組成了最大的?N?個元素。

需要注意的是，堆排序是非穩(wěn)定排序（對于相同的?key?值，無法保證排序后與排序前的位置一致），這可能會導致分頁重復的現(xiàn)象。為了避免這個問題，可以在排序條件中加上唯一值，比如主鍵id，確保參與排序的?key?值唯一。例如，可將 SQL 寫成：

select?*?from?t?order?by?time,id?asc?limit?0,3;

二、Group by

Group by一般會用臨時表進行統(tǒng)計。


select?country, count(*)?as?num?from?t?group?by?country;

執(zhí)行流程如下：

創(chuàng)建內(nèi)存臨時表，臨時表包含兩個字段?country 和?num。全表掃描表t?的記錄，依次取出country = 'X' 的記錄。判斷臨時表中是否有?country = 'X' 的行，沒有就插入一個記錄（X, 1）。如果臨時表中有?country = 'X' 的行，就將這一行的?num 值加 1。遍歷完成后，再根據(jù)字段?country 做排序，得到結(jié)果集返回給客戶端。如果不需要排序，可以使用?order by null。

1. Group by + where/having 的區(qū)別

group by + where：

select?country, count(*)?as?num?from?t?where?age >?18?group?by?country;

執(zhí)行流程如下：

創(chuàng)建內(nèi)存臨時表，臨時表包含兩個字段?country 和?num。掃描索引?age，找到年齡大于 18 的主鍵?ID。根據(jù)主鍵?ID，回表找到?country = 'X'。判斷臨時表中是否有country = 'X' 的行，沒有就插入一個記錄（X, 1）。如果臨時表中有?country = 'X' 的行，就將這一行的?num 值加 1。最后根據(jù)字段?country 做排序，得到結(jié)果集返回給客戶端。

group by + having

select?country, count(*)?as?num?from?t?group?by?country having num >=?2;

having?稱為分組過濾條件，它可以對返回的結(jié)果集操作。

同時有 where 和 having


select?country, count(*)?as?num?from?t?where?age >?18?group?by?country having num >=?2;

執(zhí)行流程如下：

1.執(zhí)行?where?子句查找符合年齡大于 19 的員工數(shù)據(jù)；
2.group by?子句對員工數(shù)據(jù)，根據(jù)國家分組；
3.對?group by?子句形成的國家組，運行聚集函數(shù)在臨時表計算每一組的員工數(shù)量值；
4.最后用?having?子句選出員工數(shù)量大于等于 3 的國家組；

因為排序會用到臨時表，如果數(shù)據(jù)量比較大時會用到磁盤臨時表，這種情況可以調(diào)大?tmp_table_size。

三、Join

對被驅(qū)動表的關(guān)聯(lián)字段建立索引，這樣每次搜索只需要在輔助索引樹上掃描一行就行了，性能比較高。

掃描次數(shù) = 外層表記錄數(shù) * 內(nèi)層表索引深度。

先使用子查詢把驅(qū)動表執(zhí)行排序?order、篩選?limit，去掉多余數(shù)據(jù)，然后再與被驅(qū)動表做?join，可以減少對比的數(shù)據(jù)。

1. Index Nested-Loop Join（NLJ）

如果被驅(qū)動表上有索引，可以利用索引類似嵌套查詢。

select?*?from?t1?join?t2?on?(t1.a = t2.a);

從驅(qū)動表?t1?取數(shù)據(jù)時，可以不一行行取，批量取，放入內(nèi)存?join_buffer，然后排序，然后再跟被驅(qū)動表t2?的索引進行比較。這里a字段上需要有索引。

2. Block Nested-Loop Join（BNL）
當被驅(qū)動表用于判斷的列沒有索引時，就是?BNL（Block Nested-Loop Join），會用到?join_buffer。可以增大?join_buffer_size?調(diào)節(jié)?buffer?大小，減少掃描次數(shù)。

如果表t2的比較字段無索引，那么會使用BNL取數(shù)，流程為：把表t1（小表，驅(qū)動表）的數(shù)據(jù)分段讀入內(nèi)存緩沖區(qū)join_buffer中，掃描表t2，把表t2中的每一行取出來，跟join_buffer中的數(shù)據(jù)做對比，滿足join條件的，作為結(jié)果集的一部分返回。循環(huán)將表t1的數(shù)據(jù)放入緩沖區(qū)，直至對比完成。


select?*?from?t1 straight_join t2?on?(t1.a = t2.b);

這里a?字段有索引，b?字段無索引。

3. 驅(qū)動表的選擇

如果要使用?join，使用小表作為驅(qū)動表。小表是指在過濾完成之后，計算參與過濾?join?條件的各個字段的總數(shù)據(jù)量，數(shù)據(jù)量小的那個表。

4. 優(yōu)化方法

NLJ可以使用緩沖區(qū)?join_buffer，根據(jù)索引?a?一次性撈取足夠數(shù)量的主鍵?id，然后將主鍵?id?回主表查詢。BNL在原表上對查找字段加索引；如果被驅(qū)動表特別大加索引不劃算，可以建立臨時表，將where滿足條件的數(shù)據(jù)單獨撈取出來，比如如下sql：

select?*?from?t1?join?t2?on?(t1.b = t2.b)?where?t2.b >=?1?and?t2.b <=?2000;

如果表t2數(shù)據(jù)量很大，而where條件過濾后滿足條件的數(shù)據(jù)量不大，可以將過濾后的數(shù)據(jù)放入臨時表tmp_t中，給臨時表的b字段加上索引，然后再將臨時表和t1做join?操作：

create temporary table tmp_t(id?int?primary key, a?int, b?int, index(b)); --b字段建索引insert?into?tmp_t?select?*?from?t2?where?b >=?1?and?b <=?2000;select?*?from?t1?join?tmp_t?on?(t1.b = tmp_t.b);

其次，對于大表還可以在業(yè)務(wù)層使用hash進行快速查找：

1. select * from t1 where ...; --獲取驅(qū)動表表t1的滿足條件數(shù)據(jù)，存入hash map。


2. select * from t2 where b >= 1 and b <= 2000; --獲取表 t2?中滿足條件的 X 行數(shù)據(jù)，返回給業(yè)務(wù)層。

3. 對這 X 行數(shù)據(jù)，從 hash map 中查找，返回符合條件的結(jié)果集。

四、Union

union：對兩個結(jié)果集進行并集操作，不包括重復行，相當于distinct，同時進行默認規(guī)則的排序。

union all：對兩個結(jié)果集進行并集操作，包括重復行，即所有的結(jié)果全部顯示，不管是不是重復。union?會自動將完全重復的數(shù)據(jù)去除掉，也就是兩行完全相同會去掉；union all?會保留那些完全重復的數(shù)據(jù)。union all只是合并查詢結(jié)果，并不會進行去重和排序操作，因此在沒有去重的需求下，使用?union all?的執(zhí)行效率要比?union?高。

通過?union?連接的 SQL 它們分別單獨取出的列數(shù)必須相同。被?union?連接的 sql 子句，單個子句中不用寫?order by，因為不會有排序的效果。但可以對最終的結(jié)果集進行排序，例如：

綜上所述，order by、group by、join?和?union?是數(shù)據(jù)庫中非常重要的操作，理解它們的原理和優(yōu)化方法，能夠幫助我們寫出更高效的 SQL 語句，提升數(shù)據(jù)庫的性能。