需求來(lái)源

今天甲方這邊要查看一個(gè)機(jī)車的周時(shí)數(shù)據(jù)（就是一個(gè)機(jī)車從到我的管轄范圍內(nèi)，到出我的管轄內(nèi)所用的時(shí)間），那這個(gè)它會(huì)跑很多次，我們要查詢這一天的周時(shí)數(shù)據(jù)，錨定一個(gè)點(diǎn)比如出管轄區(qū)的時(shí)間，那么根據(jù)查詢到今天所有這個(gè)時(shí)間范圍內(nèi)出去的車信息，然后去數(shù)據(jù)表里找這個(gè)機(jī)車進(jìn)來(lái)的數(shù)據(jù)且時(shí)最新的一條就行了。

實(shí)現(xiàn)思路

分兩次查詢的第一次查詢出來(lái)所有的當(dāng)天出管轄區(qū)的機(jī)車信息，第二個(gè)查詢是根據(jù)第一個(gè)查詢小小的改動(dòng)，把時(shí)間范圍去掉就好，然后根據(jù)機(jī)車信息進(jìn)行組取時(shí)間每個(gè)機(jī)車時(shí)間最新的數(shù)據(jù)就好。直接使用group by，但是這個(gè)并不能取出其它的信息所以就pass掉了。GPTl了一下給的方案是使用partition這個(gè)功能。

實(shí)施

就不看項(xiàng)目數(shù)據(jù)了就看一下我寫(xiě)的小demo的結(jié)果吧。我有一個(gè)student表，這個(gè)表里有10個(gè)班的學(xué)生，每個(gè)班的學(xué)生有20個(gè)，我現(xiàn)在要取出每個(gè)班的學(xué)生id最大的這個(gè)記錄，就可以使用這個(gè)partition了。

select * from (SELECT *, ROW_NUMBER() over (partition by classes_id order by id desc) as rn FROM `student`) a where rn=1

解釋一下這個(gè)啊。

這條 SQL 語(yǔ)句使用了窗口函數(shù) ROW_NUMBER() 來(lái)為每個(gè) classes_id 組中的行編號(hào)，并在外部查詢中只選擇每個(gè) classes_id 組中的最新一行（根據(jù) id 倒序排序）。以下是對(duì)這條 SQL 語(yǔ)句的詳細(xì)解釋：

SQL 語(yǔ)句結(jié)構(gòu)

SELECT * 
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn 
    FROM `student`
) a 
WHERE rn = 1;

內(nèi)部查詢（子查詢）

SELECT *, 
       ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn 
FROM `student`

SELECT *：

選擇 student 表中的所有列。

ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn：

• ROW_NUMBER() 是一個(gè)窗口函數(shù)，它為結(jié)果集中的每一行分配唯一的行號(hào)。
  • OVER 子句定義了窗口的分區(qū)和排序規(guī)則： PARTITION BY classes_id：將結(jié)果集按 classes_id 列進(jìn)行分組。對(duì)于每個(gè) classes_id，將重新開(kāi)始編號(hào)。
  • ORDER BY id DESC：在每個(gè) classes_id 分區(qū)中，按照 id 列的降序排序。
• AS rn：將生成的行號(hào)列命名為 rn。

這部分查詢?yōu)槊總€(gè) classes_id 組中的行編號(hào)，編號(hào)從1開(kāi)始，按照 id 倒序排列。因此，rn 為1的行是每個(gè) classes_id 組中 id 最大的行。

外部查詢

SELECT * 
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn 
    FROM `student`
) a 
WHERE rn = 1;

FROM (...) a：

將內(nèi)部查詢的結(jié)果作為一個(gè)臨時(shí)表 a。

WHERE rn = 1：

篩選出臨時(shí)表 a 中 rn 等于 1 的行，即每個(gè) classes_id 組中 id 最大的行。 結(jié)果

整個(gè)查詢的作用是：

• 對(duì) student 表進(jìn)行分組（按 classes_id）。
• 在每個(gè) classes_id 組中，按 id 倒序排列，并為每行分配一個(gè)行號(hào) rn。
• 選擇每個(gè) classes_id 組中 rn 等于 1 的行（即每個(gè) classes_id 組中 id 最大的行）。

partition的升級(jí)使用

partition不僅僅可以在日常查詢中使用，還可以在表的數(shù)據(jù)結(jié)構(gòu)上進(jìn)行優(yōu)化，比如在建表的時(shí)候創(chuàng)建分區(qū)或者后期添加分區(qū)，這個(gè)分區(qū)操作是在物理上的操作，可以看我下面這張表的結(jié)構(gòu)，有一部分注釋說(shuō)明就是分區(qū)的設(shè)置，

對(duì)表進(jìn)行分區(qū)可以提升查詢性能和數(shù)據(jù)管理的效率。由于 ENGINE=MyISAM 不支持分區(qū)，我們需要將表的存儲(chǔ)引擎更改為 InnoDB，因?yàn)?InnoDB 支持分區(qū)。

假設(shè)我們要根據(jù) id 列進(jìn)行范圍分區(qū)，將數(shù)據(jù)劃分為四個(gè)分區(qū)：

• p0：包含 id 小于 10000的數(shù)據(jù)。
• p1：包含 id 小于 20000的數(shù)據(jù)。
• p2：包含 id 小于 50000的數(shù)據(jù)。
• p3：包含其余的數(shù)據(jù)。

PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (20000),
    PARTITION p2 VALUES LESS THAN (50000),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

解釋

• PARTITION BY RANGE (id): 根據(jù) id 列進(jìn)行范圍分區(qū)。
• PARTITION p0 VALUES LESS THAN (10000): 第一個(gè)分區(qū)，包含 id 小于 10000的數(shù)據(jù)。
• PARTITION p1 VALUES LESS THAN (20000): 第二個(gè)分區(qū)，包含 id 小于 20000的數(shù)據(jù)。
• PARTITION p2 VALUES LESS THAN (50000): 第三個(gè)分區(qū)，包含 id 小于 50000的數(shù)據(jù)。
• PARTITION p3 VALUES LESS THAN MAXVALUE: 第四個(gè)分區(qū)，包含 id 大于等于 50000的數(shù)據(jù)。

這樣，表 products 就被劃分為四個(gè)分區(qū)，每個(gè)分區(qū)包含一定范圍的 id 值的數(shù)據(jù)。

驗(yàn)證一下看看分區(qū)

上面說(shuō)了創(chuàng)建分區(qū)了，但是怎么才能確定我們的查詢sql使用到了分區(qū)呢？使用explain來(lái)查看執(zhí)行的sql有沒(méi)有在分區(qū)的范圍呢，
下面是使用了explain查看執(zhí)行的sql有沒(méi)有用到分區(qū)，partition的值為p0對(duì)應(yīng)了上面設(shè)置的分區(qū)。

分區(qū)的一些操作

創(chuàng)建分區(qū)后，數(shù)據(jù)庫(kù)管理系統(tǒng)會(huì)自動(dòng)處理分區(qū)的數(shù)據(jù)存儲(chǔ)和檢索，用戶在日常操作中并不需要特殊處理分區(qū)。不過(guò)，你可以通過(guò)一些特定的查詢和操作來(lái)利用分區(qū)的優(yōu)勢(shì)。以下是一些常見(jiàn)的用法示例：

1. 普通查詢

普通的查詢不需要特別處理分區(qū)，數(shù)據(jù)庫(kù)管理系統(tǒng)會(huì)自動(dòng)根據(jù)分區(qū)優(yōu)化查詢：

SELECT * FROM student WHERE id < 50;

2. 分區(qū)表上的查詢優(yōu)化

當(dāng)你的查詢條件包含分區(qū)鍵時(shí)，數(shù)據(jù)庫(kù)會(huì)自動(dòng)選擇相關(guān)的分區(qū)進(jìn)行查詢，從而提高查詢性能。例如：

SELECT * FROM student WHERE id BETWEEN 50 AND 100;

3. 插入數(shù)據(jù)

插入數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)會(huì)根據(jù)分區(qū)鍵自動(dòng)將數(shù)據(jù)插入到相應(yīng)的分區(qū)：

INSERT INTO student (name, classes_id) VALUES ('Alice', 1);

4. 刪除分區(qū)中的數(shù)據(jù)

可以通過(guò)分區(qū)鍵刪除特定分區(qū)中的數(shù)據(jù)：

DELETE FROM student WHERE id < 50;

5. 分區(qū)維護(hù)操作

你可以進(jìn)行一些特定的分區(qū)維護(hù)操作，例如合并分區(qū)、拆分分區(qū)、刪除分區(qū)等：

添加新的分區(qū)

ALTER TABLE student ADD PARTITION (
    PARTITION p4 VALUES LESS THAN (200)
);

刪除分區(qū)

ALTER TABLE student DROP PARTITION p0;

重組分區(qū)

可以將多個(gè)分區(qū)合并為一個(gè)分區(qū)：

ALTER TABLE student REORGANIZE PARTITION p1, p2 INTO (
    PARTITION p1_2 VALUES LESS THAN (150)
);

6. 檢查分區(qū)信息

你可以使用 SHOW 語(yǔ)句查看表的分區(qū)信息：

SHOW CREATE TABLE student;

總結(jié)

綜合示例展示了如何創(chuàng)建分區(qū)表、插入數(shù)據(jù)以及進(jìn)行查詢和維護(hù)操作：

-- 創(chuàng)建分區(qū)表
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `classes_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK4l5dnicegnvpmu0pv6vdvrmb6` (`classes_id`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb3
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (50),
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (150),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
-- 插入數(shù)據(jù)
INSERT INTO student (name, classes_id) VALUES ('Alice', 1);
INSERT INTO student (name, classes_id) VALUES ('Bob', 2);
-- 查詢數(shù)據(jù)
SELECT * FROM student WHERE id < 50;
-- 刪除分區(qū)中的數(shù)據(jù)
DELETE FROM student WHERE id < 50;
-- 添加新分區(qū)
ALTER TABLE student ADD PARTITION (
    PARTITION p4 VALUES LESS THAN (200)
);
-- 刪除分區(qū)
ALTER TABLE student DROP PARTITION p0;
-- 檢查分區(qū)信息
SHOW CREATE TABLE student;

目前先整理這么多，以后有深入學(xué)習(xí)使用了再繼續(xù)?。?！

到此這篇關(guān)于SQL中的partition分區(qū)功能使用的文章就介紹到這了,更多相關(guān)sql partition分區(qū)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論