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

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

1. 常規(guī)排序

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


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

為例，詳細(xì)介紹其排序流程：

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

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

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

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

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

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

排序優(yōu)化方式

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

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

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

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

避免排序操作

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

索引與排序

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

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


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

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

臨時(shí)表與排序

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

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

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

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

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

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

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

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

二、Group by

Group by一般會(huì)用臨時(shí)表進(jìn)行統(tǒng)計(jì)。


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

執(zhí)行流程如下：

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

group by + having

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

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

同時(shí)有 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?子句對(duì)員工數(shù)據(jù)，根據(jù)國(guó)家分組；
3.對(duì)?group by?子句形成的國(guó)家組，運(yùn)行聚集函數(shù)在臨時(shí)表計(jì)算每一組的員工數(shù)量值；
4.最后用?having?子句選出員工數(shù)量大于等于 3 的國(guó)家組；

因?yàn)榕判驎?huì)用到臨時(shí)表，如果數(shù)據(jù)量比較大時(shí)會(huì)用到磁盤(pán)臨時(shí)表，這種情況可以調(diào)大?tmp_table_size。

三、Join

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

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

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

1. Index Nested-Loop Join（NLJ）

如果被驅(qū)動(dòng)表上有索引，可以利用索引類(lèi)似嵌套查詢(xún)。

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

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

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

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


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

這里a?字段有索引，b?字段無(wú)索引。

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

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

4. 優(yōu)化方法

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

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

如果表t2數(shù)據(jù)量很大，而where條件過(guò)濾后滿足條件的數(shù)據(jù)量不大，可以將過(guò)濾后的數(shù)據(jù)放入臨時(shí)表tmp_t中，給臨時(shí)表的b字段加上索引，然后再將臨時(shí)表和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);

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

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


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

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

四、Union

union：對(duì)兩個(gè)結(jié)果集進(jìn)行并集操作，不包括重復(fù)行，相當(dāng)于distinct，同時(shí)進(jìn)行默認(rèn)規(guī)則的排序。

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

通過(guò)?union?連接的 SQL 它們分別單獨(dú)取出的列數(shù)必須相同。被?union?連接的 sql 子句，單個(gè)子句中不用寫(xiě)?order by，因?yàn)椴粫?huì)有排序的效果。但可以對(duì)最終的結(jié)果集進(jìn)行排序，例如：

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